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
Explanation : To filter out certain information we use MINUS command. What MINUS Command odes is that, it returns all the results from the first query, that are not part of the second query. In our example, first three employees received the incentives. So query will return employee id’s 4 to 8.
SELECT FIRST_NAME, CASE FIRST_NAME WHEN 'John' THEN SALARY * .2 WHEN 'Roy' THEN SALARY * .10 ELSE SALARY * .15 END "Deduced_Amount" FROM EMPLOYEE
Explanation : Here, we are using “SQL CASE” statement to achieve the desired results. After case statement, we had to specify the column on which filtering is applied. In our case it is “FIRST_NAME”. And in then condition, specify the name of filter like John, Roy etc. To handle conditions outside our filter, use else block where every one other than John and Roy enters.
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.
“SQL Join” Interview Questions
69. Select first_name, incentive amount from employee and incentives table for those employees who have incentives
Select FIRST_NAME,INCENTIVE_AMOUNT from employee a inner join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID
70. Select first_name, incentive amount from employee and incentives table for those employees who have incentives and incentive amount greater than 3000
Select FIRST_NAME,INCENTIVE_AMOUNT from employee a inner join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID and INCENTIVE_AMOUNT >3000
Select FIRST_NAME,INCENTIVE_AMOUNT from employee a left join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID
SQL Queries in Oracle, Select FIRST_NAME,nvl(INCENTIVE_AMOUNT,0) from employee a left join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID SQL Queries in SQL Server, Select FIRST_NAME, ISNULL(INCENTIVE_AMOUNT,0) from employee a left join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID
SQL Queries in Oracle, Select FIRST_NAME,nvl(INCENTIVE_AMOUNT,0) from employee a right join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID SQL Queries in SQL Server, Select FIRST_NAME, isnull(INCENTIVE_AMOUNT,0) from employee a right join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID
SQL Queries in Oracle, select DEPARTMENT,(select nvl(max(INCENTIVE_AMOUNT),0) from INCENTIVES where EMPLOYEE_REF_ID=EMPLOYEE_ID) Max_incentive from EMPLOYEE SQL Queries in SQL Server, select DEPARTMENT,(select ISNULL(max(INCENTIVE_AMOUNT),0) from INCENTIVES where EMPLOYEE_REF_ID=EMPLOYEE_ID) Max_incentive from EMPLOYEESQL Queries in SQL Server, select DEPARTMENT,(select IFNULL (max(INCENTIVE_AMOUNT),0) from INCENTIVES where EMPLOYEE_REF_ID=EMPLOYEE_ID) Max_incentive from EMPLOYEE
“Top N Salary” SQL Interview Questions and Answers
75. 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 76. 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
77. 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
78. 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 Union” Query Interview Questions
79. Select First_Name,LAST_NAME from employee table as separate rows
select FIRST_NAME from EMPLOYEE union select LAST_NAME from EMPLOYEE
80. What is the difference between UNION and UNION ALL ?
Both UNION and UNION ALL is used to select information from structurally similar tables. That means corresponding columns specified in the union should have same data type. For example, in the above query, if FIRST_NAME is DOUBLE and LAST_NAME is STRING above query wont work. Since the data type of both the columns are VARCHAR, union is made possible. Difference between UNION and UNION ALL is that , UNION query return only distinct values.
SQL Interview Questions on “SQL Table Scripts”
81. Write create table syntax for employee table
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)
82. Write syntax to delete table employee
DROP table employee;
83. Write syntax to set EMPLOYEE_ID as primary key in employee table
ALTER TABLE EMPLOYEE add CONSTRAINT EMPLOYEE_PK PRIMARY KEY(EMPLOYEE_ID)
84. Write syntax to set 2 fields(EMPLOYEE_ID,FIRST_NAME) as primary key in employee table
ALTER TABLE EMPLOYEE add CONSTRAINT EMPLOYEE_PK PRIMARY KEY(EMPLOYEE_ID,FIRST_NAME)
85. Write syntax to drop primary key on employee table
Alter TABLE EMPLOYEE drop CONSTRAINT EMPLOYEE_PK;
86. Write Sql Syntax to create EMPLOYEE_REF_ID in INCENTIVES table as foreign key with respect to EMPLOYEE_ID in employee table
ALTER TABLE INCENTIVES ADD CONSTRAINT INCENTIVES_FK FOREIGN KEY (EMPLOYEE_REF_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID)
87. Write SQL to drop foreign key on employee table
ALTER TABLE INCENTIVES drop CONSTRAINT INCENTIVES_FK;
88. What is SQL Injection ?
SQL Injection is one of the the techniques uses by hackers to hack a website by injecting SQL commands in data fields.
89.Given these contents of the Customers table:
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
Here is a query written to return the list of customers not referred by Jane Smith:
SELECT Name FROM Customers WHERE ReferredBy <> 2;
What will be the result of the query? Why? What would be a better way to write it?
Although there are 4 customers not referred by Jane Smith (including Jane Smith herself), the query will only return one: Pat Richards. All the customers who were referred by nobody at all (and therefore have NULL in their ReferredBy column) don’t show up. But certainly those customers weren’t referred by Jane Smith, and certainly NULL is not equal to 2, so why didn’t they show up?
SQL Server uses three-valued logic, which can be troublesome for programmers accustomed to the more satisfying two-valued logic (TRUE or FALSE) most programming languages use. In most languages, if you were presented with two predicates: ReferredBy = 2 and ReferredBy <> 2, you would expect one of them to be true and one of them to be false, given the same value of ReferredBy. In SQL Server, however, if ReferredBy is NULL, neither of them are true and neither of them are false. Anything compared to NULL evaluates to the third value in three-valued logic: UNKNOWN.
The query should be written:
SELECT Name FROM Customers WHERE ReferredBy IS NULL OR ReferredBy <> 2
Watch out for the following, though!
SELECT Name FROM Customers WHERE ReferredBy = NULL OR ReferredBy <> 2
This will return the same faulty set as the original. Why? We already covered that: Anything compared to NULL evaluates to the third value in the three-valued logic: UNKNOWN. That “anything” includes NULL itself! That’s why SQL Server provides the IS NULL and IS NOT NULL operators to specifically check for NULL. Those particular operators will always evaluate to true or false.
Even if a candidate doesn’t have a great amount of experience with SQL Server, diving into the intricacies of three-valued logic in general can give a good indication of whether they have the ability learn it quickly or whether they will struggle with it.
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.
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.
SELECT EmpName FROM Employees WHERE Salary>=10000;
93.: Write an SQL Query to find name of employee whose name Start with ‘M’
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.
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)
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.
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.