SQL QUESTIONS

SQL Interview Questions and Answers

1. What is Normalization?

Normalization is the process of organizing the columns, tables of a database to minimize the redundancy of data. Normalization involves in dividing large tables into smaller tables and defining relationships between them. Normalization is used in OLTP systems.

2. What are the different types of Normalization Levels or Normalization Forms?

The different types of Normalization Forms are:

• First Normal Form: Duplicate columns from the same table needs to be eliminated. We have to create separate tables for each group of related data and identify each row with a unique column or set of columns (Primary Key)
• Second Normal Form: First it should meet the requirement of first normal form. Removes the subsets of data that apply to multiple rows of a table and place them in separate tables. Relationships must be created between the new tables and their predecessors through the use of foreign keys.
• Third Normal Form: First it should meet the requirements of second normal form. Remove columns that are not depending upon the primary key.
• Fourth Normal Form: There should not be any multi-valued dependencies.

Most databases will be in Third Normal Form

3. What is De-normalization?

De-normalization is the process of optimizing the read performance of a database by adding redundant data or by grouping data. De-normalization is used in OLAP systems.

4. What is a Transaction?

A transaction is a logical unit of work performed against a database in which all steps must be performed or none.

5. What are ACID properties?

A database transaction must be Atomic, Consistent, Isolation and Durability.

• Atomic: Transactions must be atomic. Transactions must fail or succeed as a single unit.
• Consistent: The database must always be in a consistent state. There should not be any partial transactions
• Isolation: The changes made by a user should be visible only to that user until the transaction is committed.
• Durability: Once a transaction is committed, it should be permanent and cannot be undone.

6. Explain different storage models of OLAP?

• MOLAP: The data is stored in a multi - dimensional cube. The storage is not in the relational database, but in proprietary formats.
• ROLAP: ROLAP relies on manipulating the data stored in the RDBMS for slicing and dicing functionality.
• HOLAP: HOLAP combines the advantages of both MOLAP and ROLAP. For summary type information, HOLAP leverages on cube technology for faster performance. For detail information, HOLAP can drill through the cube.

7. Explain one-to-one relationship with an example?

One to one relationship is a simple reference between two tables. Consider Customer and Address tables as an example. A customer can have only one address and an address references only one customer.

8. Explain one-to-many relationship with an example?

One-to-many relationships can be implemented by splitting the data into two tables with a primary key and foreign key relationship. Here the row in one table is referenced by one or more rows in the other table. An example is the Employees and Departments table, where the row in the Departments table is referenced by one or more rows in the Employees table.

9. Explain many-to-many relationship with an example?

Many-to-Many relationship is created between two tables by creating a junction table with the key from both the tables forming the composite primary key of the junction table.

An example is Students, Subjects and Stud_Sub_junc tables. A student can opt for one or more subjects in a year. Similarly a subject can be opted by one or more students. So a junction table is created to implement the many-to-many relationship.

10. Write down the general syntax of a select statement?

The basic syntax of a select statement is

SELECT Columns | *
FROM   Table_Name
[WHERE  Search_Condition]
[GROUP BY Group_By_Expression]
[HAVING Search_Condition]
[ORDER BY Order_By_Expression [ASC|DESC]]