Database Testing Interview Questions

1. What do you mean by Normalization and What is the use of normalized database?
   Answer: Read definition of first normal form, second normal form, third normal form, fourth normal form, fifth normal form,
2. Give me the complete syntax of Select command?
   Answer:

SELECT [ DISTINCT | ALL ]

column_expression1, column_expression2, ....

[ FROM from_clause ]

[ WHERE where_expression ]

[ GROUP BY expression1, expression2, .... ]

[ HAVING having_expression ]

[ ORDER BY order_column_expr1, order_column_expr2, .... ]

column_expression ::= expression [ AS ] [ column_alias ]

from_clause ::= select_table1, select_table2, ...

from_clause ::= select_table1 LEFT [OUTER] JOIN select_table2 ON expr ...

from_clause ::= select_table1 RIGHT [OUTER] JOIN select_table2 ON expr ...

from_clause ::= select_table1 [INNER] JOIN select_table2 ...

select_table ::= table_name [ AS ] [ table_alias ]

select_table ::= ( sub_select_statement ) [ AS ] [ table_alias ]

order_column_expr ::= expression [ ASC | DESC ]

3. What is the basic difference between where and having clause?
   Answer: having is used with aggregate functions.
   
4. What are the outputs of the following operations: NULL*5 and NULL+5?
   Answer:
   
5. Can you give me the command to create a new table on the basis of an existing table?
   Answer:

CREATE TABLE table [ ( column [, ...] ) ]             AS select

6. You have employee table with a ‘joining date’ field. Can you give me a query which shows all the records which are sorted according to day wise; means all ‘Sunday’ records comes first then ‘Monday’ records and so on?
   Answer:
   
7. What is the difference between ‘Left outer join’, ‘Right outer join’, ‘Full outer join’ and ‘Self Join’? Explain them with example using emp & dept tables.
   Answer: Read Definitions
   
8. Give a query through which top 5 records can fetched on the basis of ‘Sal’ field of EMP table.
   Answer: select top 5 * from emp order by emp.sal desc?
   
9. Fetch the 5^th highest sal record from the emp table?
   Answer:
   
10. Suppose you have a table with 3 columns:
    Write a query through which you show the sum of amount of quarters of each year in the separate quarter columns?
    Answer:
    
11. What do you mean by D/W house?
    Answer: Read Definitions
    
12. What is difference between OLAP and OLTP?
    Answer: OLAP – Online analytical processing

OLTP – Online transaction Processing

13. Explain ETL process?
    Answer: ETL - extraction, transformation and loading
    
14. What do you mean by Star Schema?
    Answer:
    
15. Can you explain me DTS process in SQL server?
    Answer: DTS - Data Transformation Services
    
16. Can SQL Servers linked to other servers like Oracle?
    Answer: SQL Server can be lined to any server provided it has OLE-DB provider from Microsoft to allow a link. E.g. Oracle has a OLE-DB provider for oracle that Microsoft provides to add it as linked server to SQL Server group.