SQL Query Interview Questions and Answers

      In this post, I am giving some examples of SQL queries which are asked in the interview.

1)  What is SQL ?

 SQL  is a Structured Query Language and is designed for managing data in a Relational Database Management System(RDBMS).

2) What are the common aggregate functions in SQL?

 Theses are the common aggregate functions in SQL ,

• AVG – calculates the average of a set of values
• COUNT – counts rows in a specified table or view.
• MIN – gets the minimum value in a set of values.
• MAX – gets the maximum value in a set of values.
• SUM – calculates the sum of values.

3)  What  are DDL, DML, DCL Commands ?

Refer : SQL Commands



4) Difference between Primary Key and Unique Key

     Both Primary and Unique key enforce the uniqueness of the column in the database. Primary key creates Clustered index on the column by default but unique column creates Non-Clustered index by default.
     Another difference between Primary and Unique key is that Primary key doesn't allow NULLs but Unique column allow one NULL value.


5) What is difference between DELETE and TRUNCATE Commands?

DELETE -  DELETE Command is used to remove rows from the table and WHERE command can be used for conditional set of parameters. Commit and Rollback can be performed after delete statement.


TRUNCATE -  Truncate removes all rows from the table. Truncate operation can not be Rollback.


6) What is a Constraint ?

         Constraint can be used to specify the limit on the data type of table. Constraint can be specified while creating or altering the table statement. Following are the some of the constraints,
NOT NULL,
CHECK,
DEFAULT,
UNIQUE,
PRIMARY KEY,
FOREIGN KEY


 

7)  Write a SQL Query to display the total number employees working in the Engineering team.

   Using count() aggregate function, we can write query as follows,

SELECT COUNT(*) FROM employee_details WHERE employee_team = 'ENGINEERING';

 

8) Write a query to find the second highest salary of Employee

   There are two ways to write the above query.  

select MAX(Salary) from Employee WHERE Salary

                                 NOT IN (select MAX(Salary) from Employee );  

Another way i.e to find the nth highest salary from employee,

SELECT * FROM Employee Emp1 
   WHERE (N-1) = ( SELECT COUNT(DISTINCT(Emp2.Salary))
                   FROM Employee Emp2
                   WHERE Emp2.Salary > Emp1.Salary);

 9) Write a SQL query to fetch the duplicate records from the table. 
   
     Using GROUP BY and HAVING Clause,

SELECT empid, project, salary, COUNT(*)
FROM employee_salary
GROUP BY empid, project, palary
HAVING COUNT(*) > 1;

10) Write a SQL query to display the Top  n records.

In MYSQL  & PostgreSQL,
   

SELECT * FROM employee_salary ORDER BY salary DESC LIMIT N

 
In Oracle,

SELECT TOP 10 * FROM employee;

11) Write a query to get the UNIQUE Department from EMPLOYEE Table

Use DISTINCT Clause,

SELECT DISTINCT department FROM employee;

12)  Select first 3 characters of name from employee table

For SQL Server, Use SUBSTR function,

SELECT substr(name, 0,3) FROM employee;

For MYSQL and PostgreSQL, Use SUBSTRING function,

SELECT substring(name, 1,3) FROM employee;

13)  Write an SQL Query to find name of employee whose name Start with ‘M’

SELECT * FROM Employees WHERE EmpName like 'M%';

14)  How do you find all employees which are also manager?

Using Self Join you can write query as follows,

SELECT e.name, m.name FROM Employee e, Employee m WHERE e.mgr_id = m.emp_id;

15) What is GROUP BY Clause ? Explain with examples

  It is a SQL Command used to group rows that have the same values.
Example:-

ID	GENDER	NAME
1 2 3 4 5	MALE FEMALE FEMALE MALE MALE	Mahesh Ashwini Priyanka Rajesh Anand

SELECT gender FROM employee GROUP BY gender;

Output :- gender
                MALE
                FEMALE



16) Write a SQL query to create a new empty table from an existing table.

 Example, using SELECT INTO

SELECT * INTO newTable FROM employee_details WHERE 1 = 2;

17) Write a SQL query to create a new table with data and structure copied from another table.

Using SELECT INTO Command, 

SELECT * INTO newTable FROM employee_details;

18) Difference between INNER and OUTER Join with example. 
 

Inner Join :--

        Inner join is the most common type of Join which is used to combine the rows from two tables and create a result set containing only such records that are present in both the tables based on the joining condition (predicate).
 Inner join returns rows when there is at least one match in both tables

      If none of the record matches between two tables, then INNER JOIN will return a NULL set. Below is an example of INNER JOIN and the resulting set.

SELECT dept.name DEPARTMENT, emp.name EMPLOYEE 
FROM DEPT dept, EMPLOYEE emp
WHERE emp.dept_id = dept.id

Outer Join

Outer Join, on the other hand, will return matching rows from both tables as well as any unmatched rows from one or both the tables (based on whether it is single outer or full outer join respectively).

Outer Join can be full outer or single outer

SELECT dept.name DEPARTMENT, emp.name EMPLOYEE 
FROM DEPT dept 
LEFT OUTER JOIN EMPLOYEE emp
ON dept.id = emp.dept_id  

19)  What is a CARTESIAN or CROSS JOIN ? Explain with example.

         The SQL CROSS JOIN produces a result set which is the number of  rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN.  This kind of result set is as Cartesian Product.

         An alternative way of achieving the same result is to use column names separated by commas after SELECT and mentioning the table names involved, after a FROM clause.

Syntax:
                

SELECT * FROM table1
CROSS JOIN table2

OR

SELECT * FROM table1, table2;

ID	Name	Department
1 2 3	Kiran Laxmi Mahesh	IT Support Engineering

Designation	Salary
SE SSE	20000 40000

 For the above table employee and salary,

SELECT id, name, salary FROM employee,salary;

The output as follows, total 3*2 = 6 rows.

ID	NAME	SALARY
1 1 2 2 3 3	Kiran Kiran Lakshmi Lakshmi Mahesh Mahesh	20000 40000 20000 40000 20000 40000

20) Difference between Clustered and Non-Clustered Index  

    Clustered Index

•  Only one per table
• Faster to read than non clustered as data is physically stored in index order

    Non Clustered Index

• Can be used many times per table
• Quicker for insert and update operations than a clustered index

         Both types of index will improve performance when select data with fields that use the index but will slow down update and insert operations.


21) PostgreSQL Database Case Expression and example.

     The PostgreSQL Case expression is the same as If/else statement in other programing languages.
The following illustrates the syntax of CASE expression.

CASE 
     WHEN condition_1  THEN result_1
     WHEN condition_2  THEN result_2
     [WHEN ...]
     [ELSE result_n]
END

Example:-

SELECT patient_name, 
CASE 
     WHEN gender='M'  THEN 'MALE'
     WHEN gender='F'  THEN 'FEMALE'
     ELSE 'TRANSGENDER'
END as patient_gender from patient_details;

22) What is SQL Injection? Explain with examples

          SQL injection is the technique to extract the database information through web application.
Scenario:
         We have one database server [MySQL] and web application server [Tomcat]. consider that database server is not connected to internet. but its connected with application server. Now we will see using web application how to extract the information using sql-injection method.

Refer this SQL Injection in Java Application


23) What is a Stored Procedure ?

            A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.


24) What are the advantages and disadvantages of Stored Procedure ?

        Advantage - Stored Procedure can be used as modular programming - means create once, store and call for several times whenever required.  This supports faster execution instead of executing multiple queries. This reduces network traffic and provides better security to the data.

       Disadvantage - It can be executed in the only in the database and utilizes more memory in the database server.