1.what is normalization?
Basically, it's the process of efficiently organizing data in a database.
2.There are two goals of the normalization process:
eliminate redundant data (for example, storing the same data in more than one table) and ensure data dependencies make sense (only storing related data in a table).
3.*First normal form (1NF)
--Eliminate duplicative columns from the same table.
--Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).
4.*Second normal form (2NF) further addresses the concept of removing duplicative data:
Meet all the requirements of the first normal form.
Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
Create relationships between these new tables and their predecessors through the use of foreign keys.
4.*Third normal form (3NF) goes one large step further:
Meet all the requirements of the second normal form.
Remove columns that are not dependent upon the primary key.
5.*Finally, fourth normal form (4NF) has one additional requirement:
Meet all the requirements of the third normal form.
A relation is in 4NF if it has no multi-valued dependencies.
6. What’s the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.
7. How to find 6th highest salary from Employee table
SELECT TOP 1 salary FROM (SELECT DISTINCT TOP 6 salary FROM employee
ORDER BY salary DESC) a ORDER BY salary
8. What is a join and List different types of joins.
Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table. Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.
9. What is sorting and what is the difference between sorting and clustered indexes?
The ORDER BY clause sorts query results by one or more columns up to 8,060 bytes. This will happen by the time when we retrieve data from database. Clustered indexes physically sorting data, while inserting/updating the table.
10. What are the differences between UNION and JOINS?
A join selects columns from 2 or more tables. A union selects rows.
11. What is the Referential Integrity?
Referential integrity refers to the consistency that must be maintained between primary and foreign keys, i.e. every foreign key value must have a corresponding primary key value
12. What is the row size in SQL Server 2000?
8060 bytes.
13. What is the purpose of UPDATE STATISTICS?
Updates information about the distribution of key values for one or more statistics groups (collections) in the specified table or indexed view.
No comments:
Post a Comment