Database testing Interview Question and answers

Database testing Interview Question and answers
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?
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?
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 5th highest sal record from the emp table?
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?
11. What do you mean by D/W house?
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?
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.