Randoms SQL Queries asked in the interview

Write SQL query to find the nth highest salary from the table.

Ans. Using Top keyword (SQL Server)-

SELECT TOP 1 Salary FROM (SELECT DISTINCT TOP N Salary FROM Employee ORDER BY Salary DESC ) ORDER BY Salary ASC

Using limit clause(MySQL)-

SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT N-1,1;

Write SQL query to find the 3rd highest salary from the table without using the TOP/limit keyword.

Ans. The below SQL query makes use of correlated subquery wherein to find the 3rd highest salary the inner query will return the count of till we find that two rows salary greater than other distinct salaries.

SELECT Salary FROM EmployeeSalary Emp1

WHERE 2 = (

                SELECT COUNT( DISTINCT ( Emp2.Salary ) )

                FROM EmployeeSalary Emp2

                WHERE Emp2.Salary > Emp1.Salary

            )

 

For the nth highest salary-

SELECT Salary FROM EmployeeSalary Emp1

WHERE N-1 = (

                SELECT COUNT( DISTINCT ( Emp2.Salary ) )

                FROM EmployeeSalary Emp2

                WHERE Emp2.Salary > Emp1.Salary

            )

Write a SQL query to remove duplicates from a table without using a temporary table.

Ans. Using Group By and Having clause-

DELETE FROM EmployeeSalary  WHERE EmpId IN (SELECT EmpId FROM EmployeeSalary       

GROUP BY Project, SalaryHAVING COUNT(*) > 1));

What is normalization?

Normalization is the process of minimizing redundancy and dependency by organizing fields and table of a database. The main aim of Normalization is to add, delete or modify fields that can be made in a single table.

What is Denormalization?

DeNormalization is a technique used to access the data from higher to lower normal forms of the database. It is also the process of introducing redundancy into a table by incorporating data from the related tables.

13. What are all the different normalizations?

The normal forms can be divided into 5 forms, and they are explained below -.

First Normal Form (1NF):.

This should remove all the duplicate columns from the table. Creation of tables for the related data and identification of unique columns.

Second Normal Form (2NF):.

Meeting all requirements of the first normal form. Placing the subsets of data in separate tables and Creation of relationships between the tables using primary keys.

Third Normal Form (3NF):.

This should meet all requirements of 2NF. Removing the columns which are not dependent on primary key constraints.

Fourth Normal Form (4NF):.

Meeting all the requirements of the third normal form and it should not have multi-valued dependencies.

What are all the different types of indexes?

There are three types of indexes -.

Unique Index.

This indexing does not allow the field to have duplicate values if the column is uniquely indexed. A unique index can be applied automatically when the primary key is defined.

Clustered Index.

This type of index reorders the physical order of the table and searches based on the key values. Each table can have only one clustered index.

NonClustered Index.

NonClustered Index does not alter the physical order of the table and maintains the logical order of data. Each table can have 999 nonclustered indexes.

What is a Cursor?

A database Cursor is a control that enables traversal over the rows or records in the table. This can be viewed as a pointer to one row in a set of rows. Cursor is very much useful for traversing such as retrieval, addition, and removal of database records.

 What is a trigger?

A DB trigger is a code or program that automatically executes a response to some event on a table or view in a database. Mainly, trigger helps to maintain the integrity of the database.

Example: When a new student is added to the student database, new records should be created in the related tables like Exam, Score, and Attendance tables.

What is the difference between Cluster and Non-Cluster Index?

A clustered index is used for easy retrieval of data from the database by altering the way that the records are stored. The database sorts out rows by the column which is set to be clustered index.

A nonclustered index does not alter the way it was stored but creates a completely separate object within the table. It points back to the original table rows after searching.

What is CLAUSE?

SQL clause is defined to limit the result set by providing a condition to the query. This usually filters some rows from the whole set of records.

Example – Query that has WHERE condition

A query that has HAVING condition.

What is a recursive stored procedure?

A stored procedure that calls by itself until it reaches some boundary condition. This recursive function or procedure helps programmers to use the same set of code any number of times.