Top 25 SQL Interview Question and Answer

SQL stands for Structured Query Language. SQL is used to communicate with databases. It is standard language for relational database management system. With the help of SQL you can access and manipulate databases.

2. What is Database?

Database Management Systems (DBMS) is software systems used to store data into database tables, retrieve data from tables and we can manipulate data. DBMS can be termed as File Manager that manages data in a database rather than saving it in file systems.

Database management systems can be classified based on a different criteria such as the data model, the database distribution. The various types of DBMS software are :

1. Relational database management system

2. Distributed database management system

3. Hierarchical database management system

4. Object-Oriented database management system

5. Network database management system.

3. What is RDBMS?

RDBMS stands for Relational Database Management System. The RDBMS used to store the data into the collection of tables, manage the data, query data and retrieve data from tables is known as RDBMS. The RDBMS provides an interface between users and applications.            
Example: MySql, Oracle, SQL Server, IBM DB2.

4. Find The Employee With Second Highest Salary?

Consider below EMPLOYEE table as the source data. I am using MySql Database.

Create table Employee(
     EMPLOYEE_ID int not null,
     NAME varchar(50),
     SALARY int,
     PRIMARY KEY (EMPLOYEE_ID)
);

INSERT INTO EMPLOYEE (EMPLOYEE_ID,NAME, SALARY) VALUES (101, 'Raj' , 4400) ;
INSERT INTO EMPLOYEE (EMPLOYEE_ID,NAME, SALARY) VALUES (102, 'Jack' , 13000) ;
INSERT INTO EMPLOYEE (EMPLOYEE_ID,NAME, SALARY) VALUES (103, 'Jill' , 10000) ;
INSERT INTO EMPLOYEE (EMPLOYEE_ID,NAME, SALARY) VALUES (104, 'Michael' , 14000) ;
INSERT INTO EMPLOYEE (EMPLOYEE_ID,NAME, SALARY) VALUES (105, 'Michael' , 15000) ;

select * from Employee;

There are several ways of finding the nth highest salary. 
Query 1:

SELECT Max(Salary) As Salary
FROM Employee where Salary < (Select Max(Salary) FROM Employee);

Result:

Query 2:

SELECT EMPLOYEE_ID,Name,SALARY from(
SELECT EMPLOYEE_ID,Name,Salary, DENSE_RANK() over(ORDER BY salary DESC) as rnk
FROM Employee) result
WHERE result.rnk=2;

Result:

5. Write Query To Create A Table With Same Structure With Data Of Another Table?

CREATE Table Emp SELECT * FROM Employee;
SELECT * FROM Emp;

Result:

6. Write Query To Fetch Alternate Record From Table?

For Even Number Record:

SELECT * FROM Employee WHERE EMPLOYEE_ID%2=0;

Result:

For Odd Number Record:

SELECT * FROM Employee WHERE EMPLOYEE_ID%2=1;

Result:

7. Write SQL Query To Find First 2 And Last 2 Records From Table?

First 2 Records:

SELECT * FROM Employee LIMIT 2;

Result:

Last 2 Records:

(SELECT * FROM Employee ORDER BY EMPLOYEE_ID DESC LIMIT 2) ORDER BY EMPLOYEE_ID ASC;

Result:

8. Fetch first 2 characters of the string?

Query:

SELECT SUBSTRING(Name,1,2) AS empName FROM Employee;

9. How Many Aggregate Functions Are Available In SQL?

The SQL Aggregate Functions calculates the values from multiple columns and returns a single value.

There are 7 aggregate functions :

AVG() : Returns the average value from specified columns

COUNT() : Returns number of table rows

MAX() : Returns largest value from all the records

MIN() : Returns smallest value from all the records

SUM() : Returns the sum of specified column values from records

FIRST() : Returns the first value

LAST() : Returns last value

10. What is Primary Key?

A primary key is used to uniquely identify each record from database table. It can not have NULL value and it must have unique values. A table can have only one primary key that consists of single or multiple fields.

11. What is Foreign Key?

A Foreign key is a field in one table that references to the primary key in other table. Basically, it is used to link two tables.

12. What is Unique Key?

The key which can accept only one null value and cannot accept the duplicate values is called Unique Key. The role of the unique key is to make sure that each row and column should be unique. Both the Unique key and primary key constraints provide a guarantee for uniqueness for a columns.

13. What Is Difference Between Primary Key And Unique Key?

1. We can have only one Primary Key in a table whereas we can have more than one Unique Key in a table.
2. The Primary Key can not have a NULL value whereas a Unique Key can have one null value.
3. Primary key is a Clustered Index whereas a Unique Key is a non clustered index.
4. Primary Key supports an Auto Increment value in case of a Unique Key, it doesn’t support an Auto Increment.

14. What Is Difference Between Delete, Drop And Truncate?

Delete: The DELETE statement in SQL is a Data Manipulation Language (DML) Statement. It is used to delete records from an existing table. We can delete a single record or multiple records depending on the condition specified in the query.

The conditions are specified in the WHERE clause of the DELETE statement. If we not provide the WHERE clause then all of the records will be deleted and the table will be empty.

Delete is DML command it can be rolled back.

Syntax:

          DELETE FROM table_name (WHERE conditions);
Example:

DELETE FROM Employee WHERE EMPLOYEE_ID = 5;

Drop: DROP statement is a Data Definition Language (DDL) Command which is used to delete existing databases, tables, views, triggers. DROP is a DDL Command. Objects deleted using DROP are permanently lost and it cannot be rolled back.

Syntax:

DROP table table_name;
Example:

DROP TABLE Employee;

Truncate: TRUNCATE Command is a Data Definition Language(DDL). It is used to remove all the records from a table. It deletes all the records from an existing table but not the table structure. The schema of the table is not deleted.

TRUNCATE TABLE statement is a DDL command so it cannot be rolled back.

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE Employee;

This above query will remove all the records from the Employee table.

15. What is ISNULL() Operator?

ISNULL function is used to check whether given value is NULL or not NULL.

16. What Are JOINS And It’s Types?

Joins are used to retrieve data from multiple tables into meaningful result set. The SQL Joins is used to combine records from two or more tables. A JOIN will do combining fields from two tables by using common values.

There are 4 Types of Joins:

1. Inner Join
2. Outer Join(Left, Right, Full)
3. Cross Join
4. Self Join

Inner Join: Inner Join basically returns records that have matching values in both the tables.

Syntax:

          Select * from Table1 INNER JOIN Table2;

Outer Join: There are 3 types of outer Join

1. Left Outer Join
2. Right Outer Join
3. Full Outer Join

Left Outer Join: Left join return rows which are common between the tables and all rows of left hand side table.

Syntax:

     Select * from Table1 A LEFT JOIN Table2 B ON A.col = B.col;

Right Outer Join: Right join return rows which are common between the tables and all rows of right hand side table.

Syntax:

     Select * from Table1 A RIGHT JOIN Table2 B ON A.col = B.col;

Full Outer Join: The FULL JOIN combines the results of both left outer join and right outer join. The joined table will having all records from both the tables and fill in NULLs for missing matches on either side.

Syntax:

Select * from Table1 A FULL JOIN Table2 B ON A.col = B.col;

Cross Join: Cross Join can be defined as cartesian product of the two tables included in join.

Syntax:

          SELECT column_name(s) FROM table1 CROSS JOIN table2;

Self Join: A self join is used to join a table with itself.

Syntax:

          SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition;

17. What is Normalization?

Normalization is a database design technique. Normalization is the process of minimizing redundant data and dependency by organizing fields and table. The aim of Normalization is to add, delete and modify field that can be made in a single table.

18. What Are Different Type of Normalization?

1. First Normal Form (1NF) : This will remove all the duplicate columns from the database tables. Creation of the table for the related data and identify unique column. Create the separate table for each group of related data. Each table must contain have a primary key.
2. Second Normal Form (2NF) : Meets all the requirements of the first normal form. Placing the subsets of data in separate tables and Creation of relationship between the tables using primary keys and foreign keys. Foreign key is used to identify table relationship.
3. Third Normal Form (3NF) : Meets all the requirements of of second normal form. Removes the columns which are not dependent on primary key constraints.
4. Fourth Normal Form (4NF) : Meeting all the requirements of third normal form and it should not have multivalued dependencies.

19. What is Denormalization In SQL?

Denormalization is the reverse process of normalization where the normalized schema is converted into a schema with redundant information. The performance is improved by using redundancy and keep the redundant data consistent. Denormalization is a database optimization technique where we add redundant data in database to get rid of the join operations.

20. What is CHECK Constraint?

A CHECK constraint is used for limit the values that can be placed in a column. The check constraint is used to enforce domain integrity.

21. What Is SQL Injection And It’s Types?

SQL Injection is a type of database attack technique where malicious SQL statements are inserted into an entry field of database such that once it is executed the database is opened for an attacker. This technique is used for attacking Applications to have an access to sensitive information and perform administrative tasks on databases.
Types of SQL Injections:

1. In-band SQLi (Classic)
2. Inferential SQLi
3. Out-of-band SQLi

22. What Is Datawarehouse?

Datawarehouse refers to a central repository of data where the data is Gathered from multiple sources like files, databases, excel, csv file etc. A Data Warehousing is process for collecting and managing data from multiple sources to provide meaningful business insights. The data warehouse is built for data analysis and reporting.
Goals of Data Warehousing:

• For reporting as well as analysis
• Maintain the organization’s historical information
• For decision making

23. What is Acid Property?

The ACID Property is Atomicity, Consistency, Isolation, and Durability. To check the reliability of the transactions we use ACID properties.

1. Atomicity refers to completed transactions or failed transactions where transaction is referred as a single logical operation on data. This state that if any aspect of a transaction fails, the whole transaction fails and the database state remains unchanged.
2. Consistency is that the data should meets all of the validity guidelines. The transaction never leaves the database without finishing its state.
3. Primary objective of isolation is the Concurrency management.
4. Durability ensures that once a transaction is committed, it will occur regardless of what happens in between, such as a power outage disturbance, a fire disturbance, or some other kind of disturbance.

24. Why do we use SQL Constraints?

Constraints are used to set the rules for all records in the table. If any constraints get violated then it can abort the action that caused it.

Constraints are defined while creating the database itself with CREATE TABLE statement or even after the table is created once with ALTER

These are major constraints are used in SQL:

NOT NULL CONSTRAINT: This indicates that the column data must have some value and cannot have null records.

UNIQUE KEY CONSTRAINT: This constraint is used to ensure that each row and column has unique value and no value is being repeated in any other row or column, In Unique key constraint column can have one null value.

PRIMARY KEY CONSTRAINT: This Constraint is used in association with NOT NULL CONSTRAINT and UNIQUE KEY CONSTRAINTS such as on one or the combination of more than one column to identify the each record uniquely.

FOREIGN KEY CONSTRAINT: It is used to ensure the referential integrity of data in the table and also matches the value in one table with another using Primary Key .

CHECK CONSTRAINT: It is used to ensure whether the given value in columns fulfills the condition or not.

25. What do you mean by data integrity?

Data Integrity defined as the accuracy and the consistency of the data that is stored in a database. Data integrity is the accuracy, completeness, and consistency of data.
There are four types of Data Integrity:

• Domain Integrity
• Entity Integrity
• Referential Integrity
• User-Defined Integrity