Sql Interview Tasks Basic

Consider the two tables below. Write a query that retrieves all employees recruited by John Do. How would you write a second query to retrieve all employees that were not recruited by any recruiter?

Employee Table

Id	Name	RecruitedBy
1	Jean Grayson	NULL
2	Paul Smith	1
3	John Do	NULL
4	Alex Lee	3
5	Lisa Kim	3
6	Bob Thompson	NULL

Recruiter Table

Id	Name
1	Bob Smith
2	Paul Allen
3	John Do

A

The following query will retrieve all recruiters recruited by John Do.
SELECT Employee.

Name FROM Employee
JOIN Recruiter ON Employee.RecruitedBy = Recruiter.Id
WHERE RecruitedBy = 3

To retrieve all employees who were not recruited by anyone in the recruiter table, you could use the following query:

SELECT Employee.Name FROM Employee
JOIN Recruiter ON Employee.RecruitedBy = Recruiter.Id
WHERE RecruitedBy Is Null

Given two tables created in the code block below, how would you write a query to fetch values in table “fibonacci” that are not in table “prime” without using the “NOT” keyword? Can you name a database technology where this is not possible?

create table fibonacci(id numeric);
create table prime(id numeric);

insert into fibonacci(id) values
    (2),
    (3),
    (5),
    (8),
    (13),
    (21);

insert into prime(id) values
    (2),
    (3),
    (5),
    (13);

A

SQLite, PostgreSQL, and SQL Server all support the ever useful “except” keyword which can be employed as detailed below

select * from fibonacci
except
select * from prime;

A popular database technology that does not support “except” is MySQL, which is why it must use the “not in” keyword. Note that for Oracle, the “minus” keyword must be used instead.

Prepare Sample Data To Practice SQL Skill.

Sample Table – Worker

WORKER_ID	FIRST_NAME	LAST_NAME	SALARY	JOINING_DATE	DEPARTMENT
001	Monika	Arora	100000	2014-02-20 09:00:00	HR
002	Niharika	Verma	80000	2014-06-11 09:00:00	Admin
003	Vishal	Singhal	300000	2014-02-20 09:00:00	HR
004	Amitabh	Singh	500000	2014-02-20 09:00:00	Admin
005	Vivek	Bhati	500000	2014-06-11 09:00:00	Admin
006	Vipul	Diwan	200000	2014-06-11 09:00:00	Account
007	Satish	Kumar	75000	2014-01-20 09:00:00	Account
008	Geetika	Chauhan	90000	2014-04-11 09:00:00	Admin

Sample Table – Bonus

WORKER_REF_ID	BONUS_DATE	BONUS_AMOUNT
1	2016-02-20 00:00:00	5000
2	2016-06-11 00:00:00	3000
3	2016-02-20 00:00:00	4000
1	2016-02-20 00:00:00	4500
2	2016-06-11 00:00:00	3500

Sample Table – Title

WORKER_REF_ID	WORKER_TITLE	AFFECTED_FROM
1	Manager	2016-02-20 00:00:00
2	Executive	2016-06-11 00:00:00
8	Executive	2016-06-11 00:00:00
5	Manager	2016-06-11 00:00:00
4	Asst. Manager	2016-06-11 00:00:00
7	Executive	2016-06-11 00:00:00
6	Lead	2016-06-11 00:00:00
3	Lead	2016-06-11 00:00:00

SQL Script To Seed Sample Data.

CREATE DATABASE ORG;
SHOW DATABASES;
USE ORG;

CREATE TABLE Worker (
 WORKER_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 FIRST_NAME CHAR(25),
 LAST_NAME CHAR(25),
 SALARY INT(15),
 JOINING_DATE DATETIME,
 DEPARTMENT CHAR(25)
);

INSERT INTO Worker 
 (WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) VALUES
  (001, 'Monika', 'Arora', 100000, '14-02-20 09.00.00', 'HR'),
  (002, 'Niharika', 'Verma', 80000, '14-06-11 09.00.00', 'Admin'),
  (003, 'Vishal', 'Singhal', 300000, '14-02-20 09.00.00', 'HR'),
  (004, 'Amitabh', 'Singh', 500000, '14-02-20 09.00.00', 'Admin'),
  (005, 'Vivek', 'Bhati', 500000, '14-06-11 09.00.00', 'Admin'),
  (006, 'Vipul', 'Diwan', 200000, '14-06-11 09.00.00', 'Account'),
  (007, 'Satish', 'Kumar', 75000, '14-01-20 09.00.00', 'Account'),
  (008, 'Geetika', 'Chauhan', 90000, '14-04-11 09.00.00', 'Admin');

CREATE TABLE Bonus (
 WORKER_REF_ID INT,
 BONUS_AMOUNT INT(10),
 BONUS_DATE DATETIME,
 FOREIGN KEY (WORKER_REF_ID)
  REFERENCES Worker(WORKER_ID)
        ON DELETE CASCADE
);

INSERT INTO Bonus 
 (WORKER_REF_ID, BONUS_AMOUNT, BONUS_DATE) VALUES
  (001, 5000, '16-02-20'),
  (002, 3000, '16-06-11'),
  (003, 4000, '16-02-20'),
  (001, 4500, '16-02-20'),
  (002, 3500, '16-06-11');

CREATE TABLE Title (
 WORKER_REF_ID INT,
 WORKER_TITLE CHAR(25),
 AFFECTED_FROM DATETIME,
 FOREIGN KEY (WORKER_REF_ID)
  REFERENCES Worker(WORKER_ID)
        ON DELETE CASCADE
);

INSERT INTO Title 
 (WORKER_REF_ID, WORKER_TITLE, AFFECTED_FROM) VALUES
 (001, 'Manager', '2016-02-20 00:00:00'),
 (002, 'Executive', '2016-06-11 00:00:00'),
 (008, 'Executive', '2016-06-11 00:00:00'),
 (005, 'Manager', '2016-06-11 00:00:00'),
 (004, 'Asst. Manager', '2016-06-11 00:00:00'),
 (007, 'Executive', '2016-06-11 00:00:00'),
 (006, 'Lead', '2016-06-11 00:00:00'),
 (003, 'Lead', '2016-06-11 00:00:00');

Once above SQL would run, you’ll see a result similar to the one attached below.

Creating Sample Data to Practice SQL Skill.

50 SQL Query Questions And Answers For Practice.

Q-1. Write An SQL Query To Fetch “FIRST_NAME” From Worker Table Using The Alias Name As <WORKER_NAME>.

Ans.

The required query is:

Select FIRST_NAME AS WORKER_NAME from Worker;

Q-2. Write An SQL Query To Fetch “FIRST_NAME” From Worker Table In Upper Case.

Ans.

The required query is:

Select upper(FIRST_NAME) from Worker;

Q-3. Write An SQL Query To Fetch Unique Values Of DEPARTMENT From Worker Table.

Ans.

The required query is:

Select distinct DEPARTMENT from Worker;

Q-4. Write An SQL Query To Print First Three Characters Of  FIRST_NAME From Worker Table.

Ans.

The required query is:

Select substring(FIRST_NAME,1,3) from Worker;

Q-5. Write An SQL Query To Find The Position Of The Alphabet (‘A’) In The First Name Column ‘Amitabh’ From Worker Table.

Ans.

The required query is:

Select INSTR(FIRST_NAME, BINARY'a') from Worker where FIRST_NAME = 'Amitabh';

Notes.

• The INSTR method is in case-sensitive by default.
• Using Binary operator will make INSTR work as the case-sensitive function.

Q-6. Write An SQL Query To Print The FIRST_NAME From Worker Table After Removing White Spaces From The Right Side.

Ans.

The required query is:

Select RTRIM(FIRST_NAME) from Worker;

Q-7. Write An SQL Query To Print The DEPARTMENT From Worker Table After Removing White Spaces From The Left Side.

Ans.

The required query is:

Select LTRIM(DEPARTMENT) from Worker;

Q-8. Write An SQL Query That Fetches The Unique Values Of DEPARTMENT From Worker Table And Prints Its Length.

Ans.

The required query is:

Select distinct length(DEPARTMENT) from Worker;

Q-9. Write An SQL Query To Print The FIRST_NAME From Worker Table After Replacing ‘A’ With ‘A’.

Ans.

The required query is:

Select REPLACE(FIRST_NAME,'a','A') from Worker;

Q-10. Write An SQL Query To Print The FIRST_NAME And LAST_NAME From Worker Table Into A Single Column COMPLETE_NAME. A Space Char Should Separate Them.

Ans.

The required query is:

Select CONCAT(FIRST_NAME, ' ', LAST_NAME) AS 'COMPLETE_NAME' from Worker;

Q-11. Write An SQL Query To Print All Worker Details From The Worker Table Order By FIRST_NAME Ascending.

Ans.

The required query is:

Select * from Worker order by FIRST_NAME asc;

Q-12. Write An SQL Query To Print All Worker Details From The Worker Table Order By FIRST_NAME Ascending And DEPARTMENT Descending.

Ans.

The required query is:

Select * from Worker order by FIRST_NAME asc,DEPARTMENT desc;

Q-13. Write An SQL Query To Print Details For Workers With The First Name As “Vipul” And “Satish” From Worker Table.

Ans.

The required query is:

Select * from Worker where FIRST_NAME in ('Vipul','Satish');

Q-14. Write An SQL Query To Print Details Of Workers Excluding First Names, “Vipul” And “Satish” From Worker Table.

Ans.

The required query is:

Select * from Worker where FIRST_NAME not in ('Vipul','Satish');

Q-15. Write An SQL Query To Print Details Of Workers With DEPARTMENT Name As “Admin”.

Ans.

The required query is:

Select * from Worker where DEPARTMENT like 'Admin%';

Q-16. Write An SQL Query To Print Details Of The Workers Whose FIRST_NAME Contains ‘A’.

Ans.

The required query is:

Select * from Worker where FIRST_NAME like '%a%';

Q-17. Write An SQL Query To Print Details Of The Workers Whose FIRST_NAME Ends With ‘A’.

Ans.

The required query is:

Select * from Worker where FIRST_NAME like '%a';

Q-18. Write An SQL Query To Print Details Of The Workers Whose FIRST_NAME Ends With ‘H’ And Contains Six Alphabets.

Ans.

The required query is:

Select * from Worker where FIRST_NAME like '_____h';

Q-19. Write An SQL Query To Print Details Of The Workers Whose SALARY Lies Between 100000 And 500000.

Ans.

The required query is:

Select * from Worker where SALARY between 100000 and 500000;

Q-20. Write An SQL Query To Print Details Of The Workers Who Have Joined In Feb’2014.

Ans.

The required query is:

Select * from Worker where year(JOINING_DATE) = 2014 and month(JOINING_DATE) = 2;

Q-21. Write An SQL Query To Fetch The Count Of Employees Working In The Department ‘Admin’.

Ans.

The required query is:

SELECT COUNT(*) FROM worker WHERE DEPARTMENT = 'Admin';

Q-22. Write An SQL Query To Fetch Worker Names With Salaries >= 50000 And <= 100000.

Ans.

The required query is:

SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) As Worker_Name, Salary
FROM worker 
WHERE WORKER_ID IN 
(SELECT WORKER_ID FROM worker 
WHERE Salary BETWEEN 50000 AND 100000);

 

Q-23. Write An SQL Query To Fetch The No. Of Workers For Each Department In The Descending Order.

Ans.

The required query is:

SELECT DEPARTMENT, count(WORKER_ID) No_Of_Workers 
FROM worker 
GROUP BY DEPARTMENT 
ORDER BY No_Of_Workers DESC;

 

Q-24. Write An SQL Query To Print Details Of The Workers Who Are Also Managers.

Ans.

The required query is:

SELECT DISTINCT W.FIRST_NAME, T.WORKER_TITLE
FROM Worker W
INNER JOIN Title T
ON W.WORKER_ID = T.WORKER_REF_ID
AND T.WORKER_TITLE in ('Manager');

Q-25. Write An SQL Query To Fetch Duplicate Records Having Matching Data In Some Fields Of A Table.

Ans.

The required query is:

SELECT WORKER_TITLE, AFFECTED_FROM, COUNT(*)
FROM Title
GROUP BY WORKER_TITLE, AFFECTED_FROM
HAVING COUNT(*) > 1;

 

Q-26. Write An SQL Query To Show Only Odd Rows From A Table.

Ans.

The required query is:

SELECT * FROM Worker WHERE MOD (WORKER_ID, 2) <> 0;

Q-27. Write An SQL Query To Show Only Even Rows From A Table.

Ans.

The required query is:

SELECT * FROM Worker WHERE MOD (WORKER_ID, 2) = 0;

 

Q-28. Write An SQL Query To Clone A New Table From Another Table.

Ans.

The general query to clone a table with data is:

SELECT * INTO WorkerClone FROM Worker;

The general way to clone a table without information is:

SELECT * INTO WorkerClone FROM Worker WHERE 1 = 0;

An alternate way to clone a table (for MySQL) without is:

CREATE TABLE WorkerClone LIKE Worker;

Q-29. Write An SQL Query To Fetch Intersecting Records Of Two Tables.

Ans.

The required query is:

(SELECT * FROM Worker)
INTERSECT
(SELECT * FROM WorkerClone);

Q-30. Write An SQL Query To Show Records From One Table That Another Table Does Not Have.

Ans.

The required query is:

SELECT * FROM Worker
MINUS
SELECT * FROM Title;

Q-31. Write An SQL Query To Show The Current Date And Time.

Ans.

Following MySQL query returns the current date:

SELECT CURDATE();

Following MySQL query returns the current date and time:

SELECT NOW();

Following SQL Server query returns the current date and time:

SELECT getdate();

Following Oracle query returns the current date and time:

SELECT SYSDATE FROM DUAL;

Q-32. Write An SQL Query To Show The Top N (Say 10) Records Of A Table.

Ans.

Following MySQL query will return the top n records using the LIMIT method:

SELECT * FROM Worker ORDER BY Salary DESC LIMIT 10;

Following SQL Server query will return the top n records using the TOP command:

SELECT TOP 10 * FROM Worker ORDER BY Salary DESC;

Following Oracle query will return the top n records with the help of ROWNUM:

SELECT * FROM (SELECT * FROM Worker ORDER BY Salary DESC)
WHERE ROWNUM <= 10;

Q-33. Write An SQL Query To Determine The Nth (Say N=5) Highest Salary From A Table.

Ans.

The following MySQL query returns the nth highest salary:

SELECT Salary FROM Worker ORDER BY Salary DESC LIMIT n-1,1;

The following SQL Server query returns the nth highest salary:

SELECT TOP 1 Salary
FROM (
 SELECT DISTINCT TOP n Salary
 FROM Worker 
 ORDER BY Salary DESC
 )
ORDER BY Salary ASC;

Q-34. Write An SQL Query To Determine The 5th Highest Salary Without Using TOP Or Limit Method.

Ans.

The following query is using the correlated subquery to return the 5th highest salary:

SELECT Salary
FROM Worker W1
WHERE 4 = (
 SELECT COUNT( DISTINCT ( W2.Salary ) )
 FROM Worker W2
 WHERE W2.Salary >= W1.Salary
 );

Use the following generic method to find nth highest salary without using TOP or limit.

SELECT Salary
FROM Worker W1
WHERE n-1 = (
 SELECT COUNT( DISTINCT ( W2.Salary ) )
 FROM Worker W2
 WHERE W2.Salary >= W1.Salary
 );

Q-35. Write An SQL Query To Fetch The List Of Employees With The Same Salary.

Ans.

The required query is:

Select distinct W.WORKER_ID, W.FIRST_NAME, W.Salary 
from Worker W, Worker W1 
where W.Salary = W1.Salary 
and W.WORKER_ID != W1.WORKER_ID;

Q-36. Write An SQL Query To Show The Second Highest Salary From A Table.

Ans.

The required query is:

Select max(Salary) from Worker 
where Salary not in (Select max(Salary) from Worker);

Q-37. Write An SQL Query To Show One Row Twice In Results From A Table.

Ans.

The required query is:

select FIRST_NAME, DEPARTMENT from worker W where W.DEPARTMENT='HR' 
union all 
select FIRST_NAME, DEPARTMENT from Worker W1 where W1.DEPARTMENT='HR';

Q-38. Write An SQL Query To Fetch Intersecting Records Of Two Tables.

Ans.

The required query is:

(SELECT * FROM Worker)
INTERSECT
(SELECT * FROM WorkerClone);

Q-39. Write An SQL Query To Fetch The First 50% Records From A Table.

Ans.

The required query is:

SELECT *
FROM WORKER
WHERE WORKER_ID <= (SELECT count(WORKER_ID)/2 from Worker);

Q-40. Write An SQL Query To Fetch The Departments That Have Less Than Five People In It.

Ans.

The required query is:

SELECT DEPARTMENT, COUNT(WORKER_ID) as 'Number of Workers' FROM Worker GROUP BY DEPARTMENT HAVING COUNT(WORKER_ID) < 5;

Q-41. Write An SQL Query To Show All Departments Along With The Number Of People In There.

Ans.

The following query returns the expected result:

SELECT DEPARTMENT, COUNT(DEPARTMENT) as 'Number of Workers' FROM Worker GROUP BY DEPARTMENT;

Q-42. Write An SQL Query To Show The Last Record From A Table.

Ans.

The following query will return the last record from the Worker table:

Select * from Worker where WORKER_ID = (SELECT max(WORKER_ID) from Worker);

Q-43. Write An SQL Query To Fetch The First Row Of A Table.

Ans.

The required query is:

Select * from Worker where WORKER_ID = (SELECT min(WORKER_ID) from Worker);

Q-44. Write An SQL Query To Fetch The Last Five Records From A Table.

Ans.

The required query is:

SELECT * FROM Worker WHERE WORKER_ID <=5
UNION
SELECT * FROM (SELECT * FROM Worker W order by W.WORKER_ID DESC) AS W1 WHERE W1.WORKER_ID <=5;

Q-45. Write An SQL Query To Print The Name Of Employees Having The Highest Salary In Each Department.

Ans.

The required query is:

SELECT t.DEPARTMENT,t.FIRST_NAME,t.Salary from(SELECT max(Salary) as TotalSalary,DEPARTMENT from Worker group by DEPARTMENT) as TempNew 
Inner Join Worker t on TempNew.DEPARTMENT=t.DEPARTMENT 
 and TempNew.TotalSalary=t.Salary;

Q-46. Write An SQL Query To Fetch Three Max Salaries From A Table.

Ans.

The required query is:

SELECT distinct Salary from worker a WHERE 3 >= (SELECT count(distinct Salary) from worker b WHERE a.Salary <= b.Salary) order by a.Salary desc;

Q-47. Write An SQL Query To Fetch Three Min Salaries From A Table.

Ans.

The required query is:

SELECT distinct Salary from worker a WHERE 3 >= (SELECT count(distinct Salary) from worker b WHERE a.Salary >= b.Salary) order by a.Salary desc;

Q-48. Write An SQL Query To Fetch Nth Max Salaries From A Table.

Ans.

The required query is:

SELECT distinct Salary from worker a WHERE n >= (SELECT count(distinct Salary) from worker b WHERE a.Salary <= b.Salary) order by a.Salary desc;

Q-49. Write An SQL Query To Fetch Departments Along With The Total Salaries Paid For Each Of Them.

Ans.

The required query is:

 SELECT DEPARTMENT, sum(Salary) from worker group by DEPARTMENT;

Q-50. Write An SQL Query To Fetch The Names Of Workers Who Earn The Highest Salary.

Ans.

The required query is:

SELECT FIRST_NAME, SALARY from Worker WHERE SALARY=(SELECT max(SALARY) from Worker);

SQL Query Interview Questions and Answers

Question 1: 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.

Question 2: SQL Query to find Max Salary from each department.

Answer: You can find the 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. 

These 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 used RIGHT OUTER JOIN because we need the name of the department from Department table which is on the right side of JOIN clause, even if there is no reference of dept_id on Employee table. 

Question 3: Write SQL Query to display the current date.

Answer: SQL has built-in function called GetDate() which returns the current timestamp. This will work in Microsoft SQL Server, other vendors like Oracle and MySQL also has equivalent functions.

SELECT GetDate(); 

Question 4: Write an SQL Query to check whether date passed to Query is the date of given format or not.

Answer: SQL has IsDate() function which is used to check passed value is a date or not of specified format, it returns 1(true) or 0(false) accordingly. Remember ISDATE() is an MSSQL function and it may not work on Oracle, MySQL or any other database but there would be something similar.

SELECT  ISDATE('1/08/13') AS "MM/DD/YY"; 

It will return 0 because passed date is not in correct format.

Question 5: Write an SQL Query to print the name of the 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’;

Question 6: 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;

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

Answer : 

SELECT EmpName FROM  Employees WHERE  Salary>=10000;

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

Answer : 

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

Question 9: 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%';

Question 10: Write an SQL Query to find  the year from date.

Answer:  Here is how you can find Year from a Date in SQL Server 2008 

SELECT YEAR(GETDATE()) as "Year";

Question 11: Write SQL Query to find duplicate rows in a database? and then write SQL query to delete them?
Answer: You can use the 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);

Question 12: 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 subquery as shown below:

SELECT student, marks from table where marks > SELECT AVG(marks) from table)

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

Answer: You need to know about self-join to solve this problem. In Self Join, you can join two instances of the 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 manager name in two column e.g.

name  manager_name
John   David

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



Question 14: You have a composite index of three columns, and you only provide the 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 the index will not invoke, but if the given 2 columns contain the primary index(first column while creating index) then the index will invoke. In this case, Index will be used because EmpId and EmpFirstName are primary columns.