50 SQL Query Questions

Prepare Sample Data To Practice SQL Skill.

Sample Table – Worker
WORKER_ FIRST_NA LAST_NAM
ID ME E 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 BONUS_AMO
_ID BONUS_DATE UNT

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

To prepare the sample data, you can run the following queries in your database query
executor or on the SQL command line. We’ve tested them with MySQL Server 5.7 and
MySQL Workbench 6.3.8 query browser. You can also download these Softwares and
install them to carry on the SQL exercise.

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-1109.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 fromWorker;

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

In Upper Case.
Ans.
The required query is:

Selectupper(FIRST_NAME)fromWorker;

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

From Worker Table.
Ans.
The required query is:

Select distinct DEPARTMENT fromWorker;

Q-4. Write An SQL Query To Print The First Three Characters Of
FIRST_NAME From Worker Table.
Ans.
The required query is:

Selectsubstring(FIRST_NAME,1,3)fromWorker;

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:

SelectINSTR(FIRST_NAME,BINARY'a')fromWorkerwhere 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)fromWorker;

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

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 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*fromWorker 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*fromWorker 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*fromWorkerwhere FIRST_NAME WHERE notin('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*fromWorkerwhere 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*fromWorkerwhere 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*fromWorkerwhere 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*fromWorkerwhere SALARY between 100000and500000;

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

Select*fromWorkerwhereyear(JOINING_DATE)=2014and 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 FIRST_NAME ||' '|| LAST_NAME AS WORKER_NAME , SALARY

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_WorkersDESC;
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 SYSDATE FROM SYS.DUAL;

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 SalaryDESC;

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 1Salary

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

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

fromWorker W,Worker W1
whereW.Salary= W1.Salary
andW.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:

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 whereW.DEPARTMENT='HR'

union all
select FIRST_NAME, DEPARTMENT fromWorker 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)/2fromWorker);

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*fromWorkerwhere WORKER_ID =(SELECT max(WORKER_ID)fromWorker);

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

Ans.
The required query is:

Select*fromWorkerwhere WORKER_ID =(SELECT min(WORKER_ID)fromWorker);

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 byW.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.Salaryfrom(SELECT
max(Salary)asTotalSalary,DEPARTMENTfromWorkergroupby DEPARTMENT)asTempNew
InnerJoinWorkert onTempNew.DEPARTMENT=t.DEPARTMENT
andTempNew.TotalSalary=t.Salary;

Q-46. Write An SQL Query To Fetch Three Max Salaries From A Table.
Ans.
The required query is:
SELECT distinct Salaryfrom worker a WHERE 3>=(SELECT count(distinct
Salary)from worker b WHERE a.Salary<=b.Salary) order bya.Salary desc;

Q-47. Write An SQL Query To Fetch Three Min Salaries From A Table.
Ans.
The required query is:

SELECT distinct Salaryfrom worker a WHERE 3>=(SELECT count(distinct

Salary)from worker b WHERE a.Salary>=b.Salary) order bya.Salary desc;

Q-48. Write An SQL Query To Fetch Nth Max Salaries From A Table.
Ans.
The required query is:

SELECT distinct Salaryfrom worker a WHERE n >=(SELECT count(distinct

Salary)from worker b WHERE a.Salary<=b.Salary) order bya.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 groupby 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 fromWorker WHERE SALARY=(SELECT

max(SALARY)fromWorker);
Summary – 50 SQL Query Questions And Answers For
Practice.
Let us take a pause here. But we’ll come back with more challenging questions on SQL
queries in our next post.

Hope, you’d fun learning through the SQL exercises.

Q-51. How to fetch 2nd,3rd,4th or any Nth salary from table.?

select * from (select first_name,salary,dense_rank () over (partition by flag order by

salary desc) dr from

(select first_name,salary,(select 'y' from dual) as flag from employees))

where dr = 2

Q-52 . How to fetch Department wise 2nd,3rd,4th or any Nth salary from
table.?

select * from (select first_name,salary,department_id,dense_rank () over

(partition by department_id order by salary desc) dr from

(select first_name,salary,department_id from employees))

where dr = 3;

Q-53 . How will you change male to female and female to male in a
column ?

ANS :-

update aa

set num = decode(num,'male','female’, 'female','male') ;

Q-54 .
You might need to determine the retirement level assigned to each employee, based on
the
number of years employed at JustLee Books. The retirement levels are determined by
using ranges of years. The Level 1 retirement category includes employees employed
less
than 4 years, Level 2 is less than 8 years, and so forth. :-

ANS:-

select employee_id,first_name,last_name,

round(months_between('01-JUL-09',hire_date)/12,0) "Years",

case

when round(months_between('01-JUL-09',hire_date)/12,0) <= 4 then 'Level 1'

when round(months_between('01-JUL-09',hire_date)/12,0) <= 8 then 'Level 2'

when round(months_between('01-JUL-09',hire_date)/12,0) < 11 then 'Level 3'

when round(months_between('01-JUL-09',hire_date)/12,0) < 15 then 'Level 4'

else 'Level 5'

End "Retired Level"

from employees;

Q 55 :- Fectching Duplicate records in table :-

ANS :-

SELECT OrderID, ProductID, COUNT(*)

FROM OrderDetails
GROUP BY OrderID, ProductID
HAVING COUNT(*)>1
 Or
SELECT firstname,lastname,country,
COUNT(*) AS CNT
FROM employees1
GROUP BY firstname,lastname,country
HAVING COUNT(*) > 1;

Or to find unique rows :-

SELECT *
FROM hr.employees1
WHERE empID NOT IN
(
SELECT MAX(empID)
FROM hr.employees1
GROUP BY firstname,lastname,country
);

To Delete Dublicate rows :-

delete FROM hr.employees1

WHERE empID NOT IN
(
SELECT MAX(empID)
FROM hr.employees1
GROUP BY firstname,lastname,country
);

SQL delete duplicate Rows using Common Table

Expressions (CTE)
We can use Common Table Expressions commonly known as CTE to remove duplicate rows in SQL Server. It
is available starting from SQL Server 2005.

We use a SQL ROW_NUMBER function, and it adds a unique sequential row number for the row.
In the following CTE, it partitions the data using the PARTITION BY clause for the [Firstname], [Lastname] and
[Country] column and generates a row number for each row.
1 WITH CTE([firstname],

2 [lastname],

3 [country],
4 duplicatecount)

5 AS (SELECT [firstname],

6 [lastname],

7 [country],

8 ROW_NUMBER() OVER(PARTITION BY [firstname],

9 [lastname],

10 [country]

11 ORDER BY id) AS DuplicateCount

12 FROM [SampleDB].[dbo].[employee])

13 SELECT *

14 FROM CTE;

In the output, if any row has the value of [DuplicateCount] column greater than 1, it shows that it is a
duplicate row.

We can remove the duplicate rows using the following CTE.

1 WITH CTE([FirstName],

2 [LastName],

3 [Country],

4 DuplicateCount)

5 AS (SELECT [FirstName],

6 [LastName],
7 [Country],

8 ROW_NUMBER() OVER(PARTITION BY [FirstName],

9 [LastName],

10 [Country]

11 ORDER BY ID) AS DuplicateCount

12 FROM [SampleDB].[dbo].[Employee])

13 DELETE FROM CTE

14 WHERE DuplicateCount > 1;

It removes the rows having the value of [DuplicateCount] greater than 1

RANK function to SQL delete duplicate rows

We can use the SQL RANK function to remove the duplicate rows as well. SQL RANK function gives unique
row ID for each row irrespective of the duplicate row.

In the following query, we use a RANK function with the PARTITION BY clause. The PARTITION BY clause
prepares a subset of data for the specified columns and gives rank for that partition.

1 SELECT E.ID,

2 E.firstname,

3 E.lastname,

4 E.country,

5 T.rank

6 FROM [SampleDB].[dbo].[Employee] E

7 INNER JOIN

8 (

9 SELECT *,

10 RANK() OVER(PARTITION BY firstname,

11 lastname,

12 country
13 ORDER BY id) rank

14 FROM [SampleDB].[dbo].[Employee]

15 ) T ON E.ID = t.ID;

In the screenshot, you can note that we need to remove the row having a Rank greater than one. Let’s
remove those rows using the following query.

1 DELETE E

2 FROM [SampleDB].[dbo].[Employee] E

3 INNER JOIN

4 (

5 SELECT *,

6 RANK() OVER(PARTITION BY firstname,

7 lastname,

8 country

9 ORDER BY id) rank

10 FROM [SampleDB].[dbo].[Employee]

11 ) T ON E.ID = t.ID

12 WHERE rank > 1;