Sunday, 19 November 2017

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)  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';

 
4) 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);



 5) 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;


6) 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;


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

Use DISTINCT Clause,


SELECT DISTINCT department FROM employee;


8)  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;


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


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


10)  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;


11) 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 EmployeeDetails;


12) 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  


13)  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


14) 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.


15) 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;


16) 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