SQL Interview Questions

What is the difference between inner and outer join? Explain 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

Notice in our record set that there is no employee in the department 5 (Logistics). Because of this if we perform inner join, then Department 5 does not appear in the above result. However in the below query we perform an outer join (dept left outer join emp), and we can see this department.

SELECT dept.name DEPARTMENT, emp.name EMPLOYEE 

FROM DEPT dept, EMPLOYEE emp

WHERE dept.id = emp.dept_id (+)

What is the difference between JOIN and UNION?

SQL JOIN allows us to “lookup” records on other table based on the given conditions between two tables. For example, if we have the department ID of each employee, then we can use this department ID of the employee table to join with the department ID of department table to lookup department names.

UNION operation allows us to add 2 similar data sets to create resulting data set that contains all the data from the source data sets. Union does not require any condition for joining. For example, if you have 2 employee tables with same structure, you can UNION them to create one result set that will contain all the employees from both of the tables.

SELECT * FROM EMP1

UNION

SELECT * FROM EMP2;

What is the difference between UNION and UNION ALL?

UNION and UNION ALL both unify for add two structurally similar data sets, but UNION operation returns only the unique records from the resulting data set whereas UNION ALL will return all the rows, even if one or more rows are duplicated to each other.

In the following example, I am choosing exactly the same employee from the emp table and performing UNION and UNION ALL. Check the difference in the result.

SELECT * FROM EMPLOYEE WHERE ID = 5

UNION ALL

SELECT * FROM EMPLOYEE WHERE ID = 5

ID	MGR_ID	DEPT_ID	NAME	SAL	DOJ
5.0	2.0	2.0	Anno	80.0	01-Feb-2012
5.0	2.0	2.0	Anno	80.0	01-Feb-2012

SELECT * FROM EMPLOYEE WHERE ID = 5

UNION

SELECT * FROM EMPLOYEE WHERE ID = 5

ID	MGR_ID	DEPT_ID	NAME	SAL	DOJ
5.0	2.0	2.0	Anno	80.0	01-Feb-2012

What is the difference between WHERE clause and HAVING clause?

WHERE and HAVING both filters out records based on one or more conditions. The difference is, WHERE clause can only be applied on a static non-aggregated column whereas we will need to use HAVING for aggregated columns.

To understand this, consider this example.
Suppose we want to see only those departments where department ID is greater than 3. There is no aggregation operation and the condition needs to be applied on a static field. We will use WHERE clause here:

SELECT * FROM DEPT WHERE ID > 3

ID	NAME
4	Sales
5	Logistics

Next, suppose we want to see only those Departments where Average salary is greater than 80. Here the condition is associated with a non-static aggregated information which is “average of salary”. We will need to use HAVING clause here:

SELECT dept.name DEPARTMENT, avg(emp.sal) AVG_SAL

FROM DEPT dept, EMPLOYEE emp

WHERE dept.id = emp.dept_id (+)

GROUP BY dept.name

HAVING AVG(emp.sal) > 80

DEPARTMENT	AVG_SAL
Engineering	90

As you see above, there is only one department (Engineering) where average salary of employees is greater than 80.

What is the difference among UNION, MINUS and INTERSECT?

UNION combines the results from 2 tables and eliminates duplicate records from the result set.

MINUS operator when used between 2 tables, gives us all the rows from the first table except the rows which are present in the second table.

INTERSECT operator returns us only the matching or common rows between 2 result sets.

To understand these operators, let’s see some examples. We will use two different queries to extract data from our emp table and then we will perform UNION, MINUS and INTERSECT operations on these two sets of data.

UNION

SELECT * FROM EMPLOYEE WHERE ID = 5

UNION

SELECT * FROM EMPLOYEE WHERE ID = 6

ID	MGR_ID	DEPT_ID	NAME	SAL	DOJ
5	2	2.0	Anno	80.0	01-Feb-2012
6	2	2.0	Darl	80.0	11-Feb-2012

MINUS

SELECT * FROM EMPLOYEE

MINUS

SELECT * FROM EMPLOYEE WHERE ID > 2

ID	MGR_ID	DEPT_ID	NAME	SAL	DOJ
1		2	Hash	100.0	01-Jan-2012
2	1	2	Robo	100.0	01-Jan-2012

INTERSECT

SELECT * FROM EMPLOYEE WHERE ID IN (2, 3, 5)

INTERSECT

SELECT * FROM EMPLOYEE WHERE ID IN (1, 2, 4, 5)

ID	MGR_ID	DEPT_ID	NAME	SAL	DOJ
5	2	2	Anno	80.0	01-Feb-2012
2	1	2	Robo	100.0	01-Jan-2012

What is a primary key?

A primary key is a combination of fields which uniquely specify a row. This is a special kind of unique key, and it has implicit NOT NULL constraint. It means, Primary key values cannot be NULL.

What is a unique key?

A Unique key constraint uniquely identified each record in the database. This provides uniqueness for the column or set of columns.

A Primary key constraint has automatic unique constraint defined on it. But not, in the case of Unique Key.

There can be many unique constraint defined per table, but only one Primary key constraint defined per table.

What is a foreign key?

A foreign key is one table which can be related to the primary key of another table. Relationship needs to be created between two tables by referencing foreign key with the primary key of another table.

What is a join?

This is a keyword used to query data from more tables based on the relationship between the fields of the tables. Keys play a major role when JOINs are used.

What are the types of join and explain each?

There are various types of join which can be used to retrieve data and it depends on the relationship between tables.

Inner join.

Inner join return rows when there is at least one match of rows between the tables.

Right Join.

Right join return rows which are common between the tables and all rows of Right hand side table. Simply, it returns all the rows from the right hand side table even though there are no matches in the left hand side table.

Left Join.

Left join return rows which are common between the tables and all rows of Left hand side table. Simply, it returns all the rows from Left hand side table even though there are no matches in the Right hand side table.

Full Join.

Full join return rows when there are matching rows in any one of the tables. This means, it returns all the rows from the left hand side table and all the rows from the right hand side table.

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 field 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 database. It is also process of introducing redundancy into a table by incorporating data from the related tables.

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 (3NF):.

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

What is a View?

A view is a virtual table which consists of a subset of data contained in a table. Views are not virtually present, and it takes less space to store. View can have data of one or more tables combined, and it is depending on the relationship.

What is an Index?

An index is performance tuning method of allowing faster retrieval of records from the table. An index creates an entry for each value and it will be faster to retrieve data.

What is the difference between DELETE and TRUNCATE commands?

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

TRUNCATE removes all rows from the table. Truncate operation cannot be rolled back.

What is the difference between TRUNCATE and DROP statements?

TRUNCATE removes all the rows from the table, and it cannot be rolled back. DROP command removes a table from the database and operation cannot be rolled back.

Get all employee details from the employee table

 Select * from employee

Get First_Name,Last_Name from employee table

 Select first_name, Last_Name from employee

Get First_Name from employee table using alias name “Employee Name”

 Select first_name Employee Name from employee

Get First_Name from employee table in upper case

 Select upper(FIRST_NAME) from EMPLOYEE

Get First_Name from employee table in lower case

Select lower(FIRST_NAME) from EMPLOYEE

Get unique DEPARTMENT from employee table

select distinct DEPARTMENT from EMPLOYEE

Select first 3 characters of FIRST_NAME from EMPLOYEE

Oracle Equivalent of SQL Server SUBSTRING is SUBSTR, Query : select substr(FIRST_NAME,0,3) from employee



SQL Server Equivalent of Oracle SUBSTR is SUBSTRING, Query : select substring(FIRST_NAME,0,3) from employee



MySQL Server Equivalent of Oracle SUBSTR is SUBSTRING. In MySQL start position is 1, Query : select substring(FIRST_NAME,1,3) from employee

Get position of 'o' in name 'John' from employee table

Oracle Equivalent of SQL Server CHARINDEX is INSTR, Query : Select instr(FIRST_NAME,'o') from employee where first_name='John'



SQL Server Equivalent of Oracle INSTR is CHARINDEX, Query: Select CHARINDEX('o',FIRST_NAME,0) from employee where first_name='John'



MySQL Server Equivalent of Oracle INSTR is LOCATE, Query: Select LOCATE('o',FIRST_NAME) from employee where first_name='John'

Get FIRST_NAME from employee table after removing white spaces from right side

select RTRIM(FIRST_NAME) from employee

Get FIRST_NAME from employee table after removing white spaces from left side

select LTRIM(FIRST_NAME) from employee

Get length of FIRST_NAME from employee table

Oracle,MYSQL Equivalent of SQL Server Len is Length , Query :select length(FIRST_NAME) from employee



SQL Server Equivalent of Oracle,MYSQL Length is Len, Query :select len(FIRST_NAME) from employee

Get First_Name from employee table after replacing 'o' with '$'

select REPLACE(FIRST_NAME,'o','$') from employee

Get First_Name and Last_Name as single column from employee table separated by a '_'

Oracle Equivalent of MySQL concat is '||', Query : Select FIRST_NAME|| '_' ||LAST_NAME from EMPLOYEE



SQL Server Equivalent of MySQL concat is '+', Query : Select FIRST_NAME + '_' +LAST_NAME from EMPLOYEE



MySQL Equivalent of Oracle '||' is concat, Query : Select concat(FIRST_NAME,'_',LAST_NAME) from EMPLOYEE

 Get all employee details from the employee table order by First_Name Ascending

Select * from employee order by FIRST_NAME asc

Get all employee details from the employee table order by First_Name descending

Select * from employee order by FIRST_NAME desc

Get all employee details from the employee table order by First_Name Ascending and Salary descending

Select * from employee order by FIRST_NAME asc,SALARY desc

Get employee details from employee table whose employee name is “John”

Select * from EMPLOYEE where FIRST_NAME='John'

Get employee details from employee table whose employee name are “John” and “Roy”

Select * from EMPLOYEE where FIRST_NAME in ('John','Roy')

Get employee details from employee table whose employee name are not “John” and “Roy”

Select * from EMPLOYEE where FIRST_NAME not in ('John','Roy')

Get employee details from employee table whose first name starts with 'J'

Select * from EMPLOYEE where FIRST_NAME like 'J%'

Get employee details from employee table whose first name contains 'o'

Select * from EMPLOYEE where FIRST_NAME like '%o%'

Get employee details from employee table whose first name ends with 'n'

Select * from EMPLOYEE where FIRST_NAME like '%n'

Get employee details from employee table whose first name ends with 'n' and name contains 4 letters

Select * from EMPLOYEE where FIRST_NAME like '___n' (Underscores)

Get employee details from employee table whose first name starts with 'J' and name contains 4 letters

Select * from EMPLOYEE where FIRST_NAME like 'J___' (Underscores)

Get employee details from employee table whose Salary greater than 600000

Select * from EMPLOYEE where Salary >600000

Get employee details from employee table whose Salary less than 800000

Select * from EMPLOYEE where Salary <800000

Get employee details from employee table whose Salary between 500000 and 800000

Select * from EMPLOYEE where Salary between 500000 and 800000

Get employee details from employee table whose name is 'John' and 'Michael'

Select * from EMPLOYEE where FIRST_NAME in ('John','Michael')

Get database date

SQL Queries in Oracle, select sysdate from dual



SQL Queries in SQL Server, select getdate()



SQL Query in MySQL, select now()

Get department,total salary with respect to a department from employee table.

Select DEPARTMENT,sum(SALARY) Total_Salary from employee group by department

Get department,total salary with respect to a department from employee table order by total salary descending

Select DEPARTMENT,sum(SALARY) Total_Salary from employee group by DEPARTMENT order by Total_Salary descending

Get department,no of employees in a department,total salary with respect to a department from employee table order by total salarydescending

Select DEPARTMENT,count(FIRST_NAME),sum(SALARY) Total_Salary from employee group by DEPARTMENT order by Total_Salary descending

Get department wise average salary from employee table order by salaryascending

select DEPARTMENT,avg(SALARY) AvgSalary from employee group by DEPARTMENT order by AvgSalary asc

Get department wise maximum salary from employee table order by salaryascending

select DEPARTMENT,max(SALARY) MaxSalary from employee group by DEPARTMENT order by MaxSalary asc

Get department wise minimum salary from employee table order by salary ascending

select DEPARTMENT,min(SALARY) MinSalary from employee group by DEPARTMENT order by MinSalary asc

Select department,total salary with respect to a department from employee table where total salary greater than 800000 order by Total_Salary descending

Select DEPARTMENT,sum(SALARY) Total_Salary from employee group by DEPARTMENT having sum(SALARY) >800000 order by Total_Salary desc

Select TOP 2 salary from employee table

SQL Queries in Oracle, select * from (select * from employee order by SALARY desc) where rownum <3



SQL Queries in SQL Server, select top 2 * from employee order by salary desc



SQL Queries in MySQL, select * from employee order by salary desc limit 2

Select TOP N salary from employee table

SQL Queries in Oracle, select * from (select * from employee order by SALARY desc) where rownum <N + 1



SQL Queries in SQL Server, select top N * from employee



SQL Queries in MySQL, select * from employee order by salary desc limit N

Select 2nd Highest salary from employee table

SQL Queries in Oracle, select min(salary) from (select * from (select * from employee order by SALARY desc) where rownum <3)



SQL Queries in SQL Server, select min(SALARY) from (select top 2 * from employee) a



SQL Queries in MySQL, select min(SALARY) from (select * from employee order by salary desc limit 2) a

Select Nth Highest salary from employee table

SQL Queries in Oracle, select min(salary) from (select * from (select * from employee order by SALARY desc) where rownum <N + 1)



SQL Queries in SQL Server, select min(SALARY) from (select top N * from employee) a



SQL Queries in MySQL, select min(SALARY) from (select * from employee order by salary desc limit N) a