Advanced SQL Queries Interview Questions and Answers

59. Select employee details from employee table if data exists in incentive table ?

select * from EMPLOYEE where exists (select * from INCENTIVES)

Explanation : Here “exists” statement helps us to do the job of If statement. Main query will get executed if the sub query returns at least one row. So we can consider the sub query as “If condition” and the main query as “code block” inside the If condition. We can use any SQL commands (Joins, Group By , having etc) in sub query. This command will be useful in queries which need to detect an event and do some activity.

60. How to fetch data that are common in two query results ?

select * from EMPLOYEE where EMPLOYEE_ID INTERSECT select * from EMPLOYEE where EMPLOYEE_ID < 4

Explanation : Here “INTERSECT” command is used to fetch data that are common in 2 queries. In this example, we had taken EMPLOYEE table in both the queries.We can apply INTERSECT command on different tables. The result of the above query will return employee details of “ROY” because, employee id of ROY is 3, and both query results have the information about ROY.

select EMPLOYEE_ID from EMPLOYEE
MINUS
select EMPLOYEE_REF_ID from INCENTIVES

SELECT FIRST_NAME, CASE FIRST_NAME WHEN 'John' THEN SALARY * .2 WHEN 'Roy' THEN SALARY * .10 ELSE SALARY * .15 END "Deduced_Amount" FROM EMPLOYEE

63. Select Banking as ‘Bank Dept’, Insurance as ‘Insurance Dept’ and Services as ‘Services Dept’ from employee table

SQL Queries in Oracle, SELECT distinct DECODE (DEPARTMENT, 'Banking', 'Bank Dept', 'Insurance', 'Insurance Dept', 'Services', 'Services Dept') FROM EMPLOYEE
SQL Queries in SQL Server and MySQL, SELECT case DEPARTMENT when 'Banking' then 'Bank Dept' when 'Insurance' then 'Insurance Dept' when 'Services' then 'Services Dept' end FROM EMPLOYEE

Explanation : Here “DECODE” keyword is used to specify the alias name. In oracle we had specify, Column Name followed by Actual Name and Alias Name as arguments. In SQL Server and MySQL, we can use the earlier switch case statements for alias names.

64. Delete employee data from employee table who got incentives in incentive table

delete from EMPLOYEE where EMPLOYEE_ID in (select EMPLOYEE_REF_ID from INCENTIVES)

Explanation : Trick about this question is that we can’t delete data from a table based on some condition in another table by joining them. Here to delete multiple entries from EMPLOYEE table, we need to use Subquery. Entries will get deleted based on the result of Subquery.

65. Insert into employee table Last Name with ” ‘ ” (Single Quote – Special Character)

Tip - Use another single quote before special character
Insert into employee (LAST_NAME) values ('Test''')

66. Select Last Name from employee table which contain only numbers

Select * from EMPLOYEE where lower(LAST_NAME)=upper(LAST_NAME)

Explanation : In order to achieve the desired result, we use “ASCII” property of the database. If we get results for a column using Lower and Upper commands, ASCII of both results will be same for numbers. If there is any alphabets in the column, results will differ.

67. Write a query to rank employees based on their incentives for a month

select FIRST_NAME,INCENTIVE_AMOUNT,DENSE_RANK() OVER (PARTITION BY INCENTIVE_DATE ORDER BY INCENTIVE_AMOUNT DESC) AS Rank from EMPLOYEE a, INCENTIVES b where a.EMPLOYEE_ID=b.EMPLOYEE_REF_ID

Explanation : In order to rank employees based on their rank for a month, “DENSE_RANK” keyword is used. Here partition by keyword helps us to sort the column with which filtering is done. Rank is provided to the column specified in the order by statement. The above query ranks employees with respect to their incentives for a given month.

68. Update incentive table where employee name is ‘John’

update INCENTIVES set INCENTIVE_AMOUNT='9000' where EMPLOYEE_REF_ID=(select EMPLOYEE_ID from EMPLOYEE where FIRST_NAME='John' )

Explanation : We need to join Employee and Incentive Table for updating the incentive amount. But for update statement joining query wont work. We need to use sub query to update the data in the incentive table. SQL Query is as shown below.

Oracle -CREATE TABLE EMPLOYEE (
EMPLOYEE_ID NUMBER,
FIRST_NAME VARCHAR2(20 BYTE),
LAST_NAME VARCHAR2(20 BYTE),
SALARY FLOAT(126),
JOINING_DATE TIMESTAMP (6) DEFAULT sysdate,
DEPARTMENT VARCHAR2(30 BYTE) )
SQL Server -CREATE TABLE EMPLOYEE(
EMPLOYEE_ID int NOT NULL,
FIRST_NAME varchar(50) NULL,
LAST_NAME varchar(50) NULL,
SALARY decimal(18, 0) NULL,
JOINING_DATE datetime2(7) default getdate(),
DEPARTMENT varchar(50) NULL)

DROP table employee;

ALTER TABLE EMPLOYEE add CONSTRAINT EMPLOYEE_PK PRIMARY KEY(EMPLOYEE_ID)

ALTER TABLE EMPLOYEE add CONSTRAINT EMPLOYEE_PK PRIMARY KEY(EMPLOYEE_ID,FIRST_NAME)

Alter TABLE EMPLOYEE drop CONSTRAINT EMPLOYEE_PK;

ALTER TABLE INCENTIVES ADD CONSTRAINT INCENTIVES_FK FOREIGN KEY (EMPLOYEE_REF_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID)

ALTER TABLE INCENTIVES drop CONSTRAINT INCENTIVES_FK;

SQL Injection is one of the the techniques uses by hackers to hack a website by injecting SQL commands in data fields.

Id	Name			ReferredBy
1	John Doe		NULL
2	Jane Smith		NULL
3	Anne Jenkins		2
4	Eric Branford		NULL
5	Pat Richards		1
6	Alice Barnes		2

SELECT Name FROM Customers WHERE ReferredBy <> 2;

SELECT Name FROM Customers WHERE ReferredBy IS NULL OR ReferredBy <> 2

SELECT Name FROM Customers WHERE ReferredBy = NULL OR ReferredBy <> 2

90.SQL Query to find Max Salary from each department.

Answer : You can find maximum salary for each department by grouping all records by DeptId and then using MAX() function to calculate maximum salary in each group or each department.

SELECT DeptID, MAX(Salary) FROM Employee GROUP BY DeptID.
This questions become more interesting if Interviewer will ask you to print department name instead of department id, in that case you need to join Employee table with Department using foreign key DeptID, make sure you do LEFT or RIGHT OUTER JOIN to include departments without any employee as well.  Here is the query

SELECT DeptName, MAX(Salary) FROM Employee e RIGHT JOIN Department d ON e.DeptId = d.DeptID GROUP BY DeptName;

In this query we have use RIGHT OUTER JOIN because we need name of department from Department table which is on right side of JOIN clause, even if there is no reference of dept_id on Employee table.

91.: Write a SQL Query to print the name of distinct employee whose DOB is between 01/01/1960 to 31/12/1975.

Answer : This SQL query is tricky but you can use BETWEEN clause to get all records whose date fall between two dates.

SELECT DISTINCT EmpName FROM Employees WHERE DOB BETWEEN ‘01/01/1960’ AND ‘31/12/1975’;

91. Write an SQL Query find number of employees according to gender  whose DOB is between 01/01/1960 to 31/12/1975.

Answer :
SELECT COUNT(*), sex from Employees WHERE DOB BETWEEN ’01/01/1960′ AND ’31/12/1975′ GROUP BY sex;

92. Write an SQL Query to find employee whose Salary is equal or greater than 10000.

Answer :
SELECT EmpName FROM Employees WHERE Salary>=10000;

93.: Write an SQL Query to find name of employee whose name Start with ‘M’

Answer :
SELECT * FROM Employees WHERE EmpName like ‘M%’;

94. find all Employee records containing the word “Joe”, regardless of whether it was stored as JOE, Joe, or joe.

Answer :
SELECT * from Employees WHERE UPPER(EmpName) like ‘%JOE%’;

95.Write a SQL Query to find  year from date.

Answer :  Here is how you can find Year from a Date in SQL Server 2008
SELECT YEAR(GETDATE()) as “Year”;

96.: Write SQL Query to find duplicate rows in a database? and then write SQL query to delete them?
Answer : You can use following query to select distinct records :
SELECT * FROM emp a WHERE rowid = (SELECT MAX(rowid) FROM EMP b WHERE a.empno=b.empno)
to Delete:
DELETE FROM emp a WHERE rowid != (SELECT MAX(rowid) FROM emp b WHERE a.empno=b.empno);

97. There is a table which contains two column Student and Marks, you need to find all the students, whose marks are greater than average marks i.e. list of above average students.
Answer : This query can be written using sub query as shown below :
SELECT student, marks from table where marks > SELECT AVG(marks) from table)

98. How do you find all employees which are also manager? .
You have given an standard employee table with an additional column mgr_id, which contains employee id of manager.

Answer : You need to know about self join to solve this problem. In Self Join, you can join two instances of same table to find out additional details as shown below

SELECT e.name, m.name FROM Employee e, Employee m WHERE e.mgr_id = m.emp_id;
this will show employee name and manger name in two column e.g.

name  manager_name
John   David

One follow-up is to modify this query to include employees which doesn’t have manager. To solve that, instead of using inner join, just use left outer join, this will also include employees without managers.

99.: You have a composite index of three columns, and you only provide value of two columns in WHERE clause of a select query? Will Index be used for this operation?

For example if Index is on EmpId, EmpFirstName and EmpSecondName and you write query like

SELECT * FROM Employee WHERE EmpId=2 and EmpFirstName=’Radhe’
If the given two columns are secondary index column then index will not invoke, but if the given 2 columns contain primary index(first col while creating index) then index will invoke. In this case Index will be used because EmpId and EmpFirstName are primary columns.

100. SQL Query to find second highest salary of Employee

Answer : There are many ways to find second highest salary of Employee in SQL, you can either use SQL Join or Subquery to solve this problem. Here is SQL query using Subquery :

select MAX(Salary) from Employee WHERE Salary NOT IN (select MAX(Salary) from Employee );
See How to find second highest salary in SQL for more ways to solve this problem.