Sunday, November 4, 2012

SQL Interview Question and Answer

What are  categories of SQL commands?
  • Data Definition Language (DDL)
  • Data Manipulation Language (DML)
  • Data Query Language (DQL)
  • Data Control Language (DCL)
  • Data Administration Commands (DAC)
  • Transactional Control Commands (TCC)
What Is Database is a collection of data. It contains information about  one particular enterprise. A database management system (DBMS) consists of a collection of interrelated data and a set of programs to access that data.
What are the advantages of database?
  • Redundancy can be reduced
  • The data can be shared
  • Inconsistency can be avoided
  • Standards can be enforced
  • Security can be enforced
  • Integrity can be maintaned
DDL stands for Data Definition Language. Data Definition Language is used to create, alter and delete database objects.
DML
  • DML stands for Data Manipulation Language.
  • Data Manipulation Language commands let users insert, modify and delete the data in the database.
  • Sql provides three data manipulation statements insert, update and delete
What is SQL
SQL is structured Query Language which is a computer language for storing, manipulating and retrieving data stored in relational database.
SQL is the standard language for Relation Database System. All relational database management systems like MySQL, MS Access, Oracle, Sybase, Informix, postgres and SQL Server uses SQL as standard database language.
Also they are using different dialects, Such as:
 MS SQL Server using T-SQL,
 Oracle using PL/SQL,
 MS Access version of SQL is called JET SQL (native format )etc
Why SQL?
 Allow users to access data in relational database management systems.
 Allow users to describe the data.
 Allow users to define the data in database and manipulate that data.
 Allow to embed within other languages using SQL modules, libraries & pre-compilers.
 Allow users to create and drop databases and tables.
 Allow users to create view, stored procedure, functions in a database.
 Allow users to set permissions on tables, procedures, and views

Explain the difference between a hot backup and a cold backup.
A cold backup is done when there is no user activity going on with the system. Also called as offline backup, is taken when the database is not running and no users are logged in. all files of the database are copied and no changes during the copy are made.
A hot backup is taken when the database needs to run all the time. It is an online backup. All files of the database are copied and there may be changes to the database during the copy.
How many memory layers are in the oracle shared pool? Explain them
Oracles shared pool consists of two layers namely, Library cache and Data dictionary cache.
Library cache: This layer has information about SQL statements that were parsed, information about cursors and any plan data.
Data Dictionary cache: this Layer has information about the accounts of the users, their privileges and segments information.
What is the cache hit ratio, what impact does it have on performance of an Oracle database and what is involved in tuning it?
When the cache client like a CPU, web browser etc finds a successful entry in the cache memory, it is called as a cache hit. The percentage of these successful cache hits is called as cache hit ratio. Higher the cache hit ratio better will be the performance because data read from the cache is faster than the memory.
Explain the concept of the DUAL table.
On installing Oracle database, DUAL table Is present by default. It is a special table with just one row. It has a single column called DUMMY. The data type of this column in VARCHAR2(1). It has a value “X”. It is most commonly used to select pseudo columns in Oracle like sysdate.
        Select sysdate from dual




0 comments:

Post a Comment

SQL BASIC

  • Sql-Overview
  • Sql-Sysntax
  • Sql-Normalization
  • RDBMS Concept
  • Sql-Data Type
  • Sql-Operator
  • Sql-Expression
  • Create database
  • Sql-Delete
  • Sql-Select
  • Sql-Create
  • Sql-Like
  • Sql-Join
  • Sql-Insert
  • Sql-Drop Table
  • Sql-Wild Card
  • Sql-Order By
  • Sql-Group By
  • Sql-Index
  • Not Null Constraints
  • Transaction Control
  • Sql-Transaction
  • Sql-In
  • Sql-Distinct
  • Check Constraint
  • Sql Alias
  • Sql-Primary
  • Sql-Where
  • Sql-Update
  • Sql-Alias
  • Sql-Top-Rownum
  • Primary key vs Unique key
  • SQL Interview Question
  • PL/SQL BASIC

  • Variable
  • Block Structure
  • Function
  • Procedure
  • Nested Blog
  • If Statement
  • While Loop
  • For Loop
  • SEO

  • Introduction Seo
  • Top Social Bookmarking List
  • Directory Submission List
  • Classified Ads
  • Key Word Research
  • Html

  • Introduction Html
  • Introduction Css
  • Introduction Java Script
  • Unix

  • Unix
  • Software Testing

  • Software Testing
  • Computer Network

  • Computer Network
  •