92 SQL EXERCISES

In this article you will find 92 SQL exercises. The necessary data sets are available on the page as a drive link below.

Click here for data sets.

This work includes following commands;

· SELECT · WHERE · UPPER · CHARINDEX · RIGHT TRIM, LEFT TRIM
· REPLACE · CONCAT · LIKE · GROUP BY HAVING · JOIN · MAX, MIN, AVG
· SUBQUERIES · CROSS APPLY

* Questions 1–49, data set, worker99.xlsx

select FIRST_NAME as WORKER_NAME from worker

2. Write an SQL query to fetch “FIRST_NAME” from Worker table in upper case.

select upper(FIRST_NAME) from worker

3. Write an SQL query to fetch unique values of DEPARTMENT from Worker table

select distinct DEPARTMENT from worker

4. Write an SQL query to print the first three characters of FIRST_NAME from Worker table.

select LEFT(FIRST_NAME,3) from worker

5. Write an SQL query to find the position of the alphabet (‘t’) in the first name column ‘Amitabh’ from Worker table.

SELECT CHARINDEX(‘t’, ‘Amitabh’) AS MatchPosition

6. Write an SQL query to print the FIRST_NAME from Worker table after removing white spaces from the right side.

Select RTRIM(FIRST_NAME) from Worker;

7.Write an SQL query that fetches the unique values of DEPARTMENT from Worker table and prints its length.

select DEPARTMENT, len(DEPARTMENT) as length from worker

8. Write an SQL query to print the FIRST_NAME from Worker table after replacing ‘a’ with ‘A’.

select FIRST_NAME, REPLACE(FIRST_NAME, ‘a’, ‘A’) AS NEW_NAME from worker

9. Write an SQL query to print the FIRST_NAME and LAST_NAME from Worker table into a single column COMPLETE_NAME.

select FIRST_NAME, LAST_NAME, CONCAT(FIRST_NAME, ‘ ‘, LAST_NAME) from worker

10. Write an SQL query to print all Worker details from the Worker table order by FIRST_NAME Ascending.

select * from worker order by FIRST_NAME asc

11. Write an SQL query to print all Worker details from the Worker table order by FIRST_NAME Ascending and DEPARTMENT Descending.

select * from worker order by FIRST_NAME asc, DEPARTMENT desc

12. Write an SQL query to print details for Workers with the first name as “Vipul” and “Satish” from Worker table.

select * from worker where FIRST_NAME in (‘Vipul’ , ‘Satish’)

13. Write an SQL query to print details of Workers with DEPARTMENT name as “Admin”.

select * from worker where DEPARTMENT = ‘Admin’

14. Write an SQL query to print details of the Workers whose FIRST_NAME contains ‘a’.

select * from worker where FIRST_NAME like ‘%a’

15. Write an SQL query to print details of the Workers whose FIRST_NAME ends with ‘a’.

select * from worker where FIRST_NAME like ‘%a’

16.Write an SQL query to print details of the Workers whose FIRST_NAME ends with ‘h’ and contains six alphabets.

select * from worker where FIRST_NAME like ‘_____h’

17. Write an SQL query to print details of the Workers whose SALARY lies between 100000 and 500000.

select * from worker where SALARY between 500000 and 100000

18. Write an SQL query to print details of the Workers who have joined in Feb’2014.

SELECT * from worker where year(JOINING_DATE) = 2014 and month(JOINING_DATE) = 02

19. Write an SQL query to fetch the count of employees working in the department ‘Admin’.

select COUNT(*) from worker where DEPARTMENT = ‘Admin’

20. Write an SQL query to fetch worker names with salaries >= 50000 and <= 100000.

select FIRST_NAME from worker where SALARY between 50000 and 100000

21. Write an SQL query to fetch the no. of workers for each department in the descending order.

select DEPARTMENT, count(WORKER_ID) no_of_workers from worker group by DEPARTMENT

22. Write an SQL query to print details of the Workers who are also Managers.

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’)

23. Write an SQL query to fetch duplicate records having matching data in some fields of a table(for title table).

select WORKER_TITLE, AFFECTED_FROM , count(*) as ‘count_’ from title

group by WORKER_TITLE, AFFECTED_FROM

having count(*) > 1

24. Write an SQL query to show only odd rows from worker table.

select * from worker where (WORKER_ID %2) <> 0

25. Write an SQL query to clone a new table from worker table who is FIRST_NAME is Monika.

SELECT * INTO WorkerClone2 FROM Worker WHERE FIRST_NAME = ‘Monika’

26. Write an SQL query to fetch intersecting records of two tables.

select WORKER_REF_ID from bonus intersect

select WORKER_REF_ID from title

27. Write an SQL query to show records from one table that another table does not have.

select WORKER_REF_ID from bonus EXCEPT

select WORKER_REF_ID from title

28. Write an SQL query to show the current date and time.

29. Write an SQL query to show the top n (say 5) records of worker table.

select * from worker where WORKER_ID < 6

30. Write an SQL query to determine the nth (say n=5) highest salary from worker table.

select top 5 * from worker order by SALARY DESC

31. Write an SQL query to fetch the list of employees with the same salary.

select w.FIRST_NAME, w.SALARY from worker w, worker w1

where w.SALARY = w1.SALARY

and w.WORKER_ID != w1.WORKER_ID

32. Write an SQL query to show the second highest salary from a table.

Select max(SALARY) from worker

where SALARY not in (Select max(SALARY) from worker)

33. Write an SQL query to show one row twice in results from a table.

select FIRST_NAME, DEPARTMENT from worker w where w.DEPARTMENT = ‘HR’

union all

select FIRST_NAME, DEPARTMENT from worker w1 where w1.DEPARTMENT = ‘HR’

34. Write an SQL query to fetch the first 50% records from a table.

select * from worker where WORKER_ID <= (select COUNT(worker_id) / 2 from worker)

OR

SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY salary ASC) AS rownumber,* FROM worker) AS foo WHERE rownumber <5

35. Write an SQL query to fetch the departments that have less than five people in it.

select DEPARTMENT, COUNT(*) as ‘Number of Workers’ from worker group by DEPARTMENT having count(*) < 5

36. Write an SQL query to show all departments along with the number of people in there.

select DEPARTMENT, COUNT(FIRST_NAME) as ‘count’ from worker group by DEPARTMENT

37. Write an SQL query to show the last record from a table.

select * from worker where WORKER_ID = (select max(worker_id) from worker)

38. Write an SQL query to fetch the first row of a table.

select * from worker where worker_id = (select min(WORKER_ID) from worker)

39. Write an SQL query to fetch the last five records from a table.

SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY salary ASC) AS rownumber,* FROM worker) AS foo WHERE rownumber > 3

40. Write an SQL query to print the name of employees having the highest salary in each department.

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;

41. Write an SQL query to fetch three max salaries from a table.

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;

42. Write an SQL query to fetch three min salaries from a table.

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;

43. Write an SQL query to fetch nth max salaries from a table.

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

44. Write an SQL query to fetch departments along with the total salaries paid for each of them

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

45. Write an SQL query to fetch the names of workers who earn the highest salary.

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

46 .Query to find Second Highest Salary of Employee

select max(salary) from worker where SALARY < (select max(salary) from worker)

* Questions 47 — 87 data set, salesman99.xlsx

47. write a SQL query to select all the salespeople. Return salesman_id, name, city, commission with the percent sign (%).

select salesman_id as ‘salesman_id%’ , name as ‘name%’ from salesman

48. Write a SQL query to find all the customers. Sort the result-set in descending order on 3rd field. Return customer name, city and grade.

SELECT customer_id, city, grade FROM customer order by 3 desc

49. Write a SQL query to count the unique orders, highest purchase amount for each customer. Sort the result-set in descending order on 2nd field. Return customer ID, number of distinct orders and highest purchase amount by each customer.

SELECT customer_id, COUNT(DISTINCT ord_no), MAX(purch_amt) FROM orders GROUP BY customer_id ORDER BY 2 DESC

50. Write a SQL query to calculate summation of purchase amount, total commission (15% for all salesmen) by each order date. Sort the result-set on order date. Return order date, summation of purchase amount and commission.

select ord_date, SUM(purch_amt), SUM(purch_amt)*0.15 as ‘comission’ from orders group by ord_date

51. Write a SQL query to find the salespersons and customers who live in same city. Return customer name, salesperson name and salesperson city.

select s.name, c.cust_name, c.city from customer c , salesman s where c.city = s.city

52. write a SQL query to find all the customers along with the salesperson who works for them. Return customer name, and salesperson name.

select c.cust_name, s.name from customer c, salesman s where s.salesman_id = c.salesman_id

53. Write a SQL query to find those sales people who generated orders for their customers but not located in the same city.Return ord_no, cust_name, customer_id (orders table), salesman_id (orders table).

SELECT ord_no, cust_name, orders.customer_id, orders.salesman_id FROM salesman, customer, orders WHERE

customer.city <> salesman.city AND

orders.customer_id = customer.customer_id AND orders.salesman_id = salesman.salesman_id

54. Write a SQL query to find those orders made by customers. Return order number, customer name.

select o.ord_no, c.cust_name from orders o, customer c where o.customer_id = c.customer_id

55. Write a SQL query to find those customers where each customer has a grade and served by at least a salesperson who belongs to a city. Return cust_name as “Customer”, grade as “Grade”.

SELECT customer.cust_name AS “Customer”, customer.grade AS “Grade” FROM orders, salesman, customer WHERE

orders.customer_id = customer.customer_id AND orders.salesman_id = salesman.salesman_id AND

salesman.city IS NOT NULL AND customer.grade IS NOT NULL;

56. Write a SQL query to find those customers who served by a salesperson and the salesperson works at the commission in the range 12% to 14% (Begin and end values are included.). Return cust_name AS “Customer”, city AS “City”.

select c.cust_name, c.city from customer c, salesman s

where c.salesman_id = s.salesman_id and s.commission between 0.12 and 0.14

57. Write a SQL query to find those orders executed by the salesperson, ordered by the customer whose grade is greater than or equal to 200. Compute purch_amt*commission as “Commission”. Return customer name, commission as “Commission%” and Commission.

select c.cust_name, S.commission as ‘Commission%’ from customer c, salesman s, orders o

where c.customer_id = o.customer_id AND o.salesman_id = s.salesman_id and c.grade > 200

58. Write a SQL query to find those customers who made orders on October 5, 2012. Return customer_id, cust_name, city, grade, salesman_id, ord_no, purch_amt, ord_date, customer_id and salesman_id.

SELECT * FROM customer a,orders b

WHERE a.customer_id=b.customer_id

AND b.ord_date=’2012–10–05'

59. Write a SQL query to find the salesperson(s) and the customer(s) he handle. Return Customer Name, city, Salesman, commission.

SELECT a.cust_name AS “Customer Name”, a.city, b.name AS “Salesman”, b.commission

FROM customer a INNER JOIN salesman b ON a.salesman_id=b.salesman_id

60. Write a SQL query to find those orders where order amount exists between 500 and 2000. Return ord_no, purch_amt, cust_name, city.

select o.ord_no, o.purch_amt, c.cust_name, c.city from customer c, orders o

where o.salesman_id = c.salesman_id and o.purch_amt between 500 and 2000

61.Write a SQL query to find the salesperson(s) and the customer(s) he handle. Return Customer Name, city, Salesman, commission.

select S.name, S.city, S.salesman_id, s.commission from customer c, salesman S WHERE c.salesman_id = s.salesman_id

62. Write a SQL query to find those salespersons who received a commission from the company more than 12%. Return Customer Name, customer city, Salesman, commission.

select c.cust_name, C.city, c.salesman_id, s.commission from customer c, salesman s where c.salesman_id = s.salesman_id and s.commission > 0.12

63. Write a SQL query to find those salespersons do not live in the same city where their customers live and received a commission from the company more than 12%. Return Customer Name, customer city, Salesman, salesman city, commission.

select c.cust_name, c.city, s.city, S.commission from customer c, salesman s

where c.salesman_id = s.salesman_id and c.city != s.city and s.commission > 0.12

64. From the following tables write a SQL query to find the details of an order. Return ord_no, ord_date, purch_amt, Customer Name, grade, Salesman, commission.

select o.ord_no, o.ord_date, o.purch_amt, c.cust_name, c.grade, o.salesman_id, s.commission

from customer c, orders o, salesman s

where o.customer_id = c.customer_id and s.salesman_id = c.salesman_id

65. From the following tables write a SQL query to display the cust_name, customer city, grade, Salesman, salesman city. The result should be ordered by ascending on customer_id.

select c.cust_name, c.grade, o.salesman_id, s.city

from customer c join salesman s on c.salesman_id = s.salesman_id

join orders o on c.customer_id = o.customer_id

order by c.customer_id

66. Write a SQL statement to make a report with customer name, city, order number, order date, and order amount in ascending order according to the order date to find that either any of the existing customers have placed no order or placed one or more orders.

select c.cust_name, c.city, o.ord_no, o.ord_date, o.purch_amt from orders o

left outer join customer c on c.customer_id = o.customer_id

67. Write a SQL statement to make a report with customer name, city, order number, order date, order amount salesman name and commission to find that either any of the existing customers have placed no order or placed one or more orders by their salesman or by own.

SELECT a.cust_name,a.city, b.ord_no, b.ord_date,b.purch_amt AS “Order Amount”, c.name,c.commission FROM customer a

LEFT OUTER JOIN orders b ON a.customer_id=b.customer_id LEFT OUTER JOIN salesman c ON c.salesman_id=b.salesman_id;

68. Write a SQL statement to make a report with customer name, city, order no. order date, purchase amount for those customers from the existing list who placed one or more orders or which order(s) have been placed by the customer who is not on the list.

SELECT a.cust_name,a.city, b.ord_no, b.ord_date,b.purch_amt AS “Order Amount” FROM customer a

FULL OUTER JOIN orders b

ON a.customer_id=b.customer_id;

69. From the following tables, write a SQL query to find all the orders issued by the salesman ‘Paul Adam’. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.

select * from orders where salesman_id =

(select salesman_id from salesman where name = ‘Paul Adam’)

70. Write a SQL query to find all the orders, which are generated by those salespeople, who live in the city of London.Return ord_no, purch_amt, ord_date, customer_id, salesman_id.

select * from orders where salesman_id =

(select salesman_id from salesman where city = ‘London’)

71 . Write a SQL query to find the orders generated by the salespeople who may work for customers whose id is 3007. Return ord_no, purch_amt, ord_date, customer_id, salesman_id.

select * from salesman where salesman_id =

(select o.salesman_id from orders o where o.customer_id = 3007)

72. Write a SQL query to find the order values greater than the average order value of 10th October 2012. Return ord_no, purch_amt, ord_date, customer_id, salesman_id.

SELECT * FROM orders

WHERE purch_amt > (SELECT AVG(purch_amt)

FROM orders WHERE ord_date =’10/10/2012');

73. Write a SQL query to find all the orders generated in New York city. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.

SELECT * from orders where salesman_id =(

select salesman_id from salesman where city = ‘New York’)

74. Write a SQL query to count number of customers with grades above the average grades of New York City. Return grade and count.

SELECT grade, COUNT (*) as count FROM customer GROUP BY grade

HAVING grade > (SELECT AVG(grade) FROM customer

WHERE city = ‘New York’);

75. Write a SQL query to find those salespeople who earned the maximum commission. Return ord_no, purch_amt, ord_date, and salesman_id.

select s.commission ,o.ord_no, o.purch_amt, o.ord_date, o.salesman_id from orders o join salesman s on o.salesman_id = s.salesman_id

where s.commission = ( select max(commission) from salesman)

76. Write a SQL query to find the customers whose orders issued on 17th August, 2012. Return ord_no, purch_amt, ord_date, customer_id, salesman_id and cust_name.

select c.cust_name, o.ord_no, o.purch_amt, o.ord_date, o.salesman_id from orders o join customer c

on

o.salesman_id = c.salesman_id where ord_date = ‘2012–08–17’

77. Write a SQL query to find the salespeople who had more than one customer. Return salesman_id and cust_name.

SELECT salesman_id,name FROM salesman a

WHERE 1 <(SELECT COUNT(*)

FROM customer WHERE salesman_id=a.salesman_id);

78. Write a SQL query to find those orders, which are equal or higher than average amount of the orders. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.

select * from orders

where purch_amt <= ( select AVG(purch_amt) from orders)

79. Write a query to find the sums of the amounts from the orders table, grouped by date, eliminating all those dates where the sum was not at least 1000.00 above the maximum order amount for that date.

select ord_date, sum(purch_amt) from orders

group by ord_date having sum(purch_amt) > 1000

80. Write a query to extract all data from the customer table if and only if one or more of the customers in the customer table are located in London.

select cust_name, count(customer_id) as count from customer

where city = ‘London’

group by cust_name having count(customer_id) > 0

81. Write a SQL query to find the salespeople who deals those customers who live in the same city. Return salesman_id, name, city and commission.

select distinct(s.salesman_id), s.name, s.city, s.commission from salesman s

join customer c on s.city = c.city

82. Write a SQL query to find all those orders whose order amount greater than at least one of the orders of September 10th 2012. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.

select * from orders where purch_amt >

(select min(purch_amt) from orders where ord_date = ‘2012–09–10’ )

83. Write a SQL query to find those orders where an order amount less than any order amount of a customer lives in London City. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.

SELECT * FROM orders

WHERE purch_amt < ANY

(SELECT purch_amt FROM orders a, customer b

WHERE a.customer_id=b.customer_id

AND b.city=’London’)

84. Write a SQL query to find those customers whose grade are higher than customers living in New York City. Return customer_id, cust_name, city, grade and salesman_id.

select * from customer

where grade > (select max(grade) from customer where city = ‘New York’)

85. Write a SQL query to calculate the total order amount generated by a salesman. The salesman should belong to the cities where any of the customer living. Return salesman name, city and total order amount.

select s.name, s.city, subquery1.total_amt from salesman s,

(select o.salesman_id, sum(o.purch_amt) as total_amt from orders o

group by o.salesman_id ) subquery1

where subquery1.salesman_id = s.salesman_id and

s.city IN (SELECT DISTINCT city FROM customer)

86. Write a SQL query to find those customers whose grade doesn’t same of those customers live in London City. Return customer_id, cust_name, city, grade and salesman_id.

select * from customer

where grade != (select grade from customer where city =’London’ and grade is not NULL)

87. Write a SQL query to find those customers whose grade are not same of those customers living in Paris. Return customer_id, cust_name, city, grade and salesman_id.

select * from customer

where grade != (select grade from customer where city = ‘Paris’)

* Questions 88–89 data set, nobel_win.xlsx

88. Write a SQL query to combine the winners in Physics, 1970 and in Economics, 1971. Return year, subject, winner, country, and category.

SELECT * FROM nobel_win WHERE (subject =’Physics’ AND year=1970)

UNION

(SELECT * FROM nobel_win WHERE (subject =’Economics’ AND year=1971))

89. From the following table, write a SQL query to find the details of 1970 Nobel Prize winners. Order the result by subject, ascending except ‘Chemistry’ and ‘Economics’ which will come at the end of result set. Return year, subject, winner, country, and category.

SELECT * FROM nobel_win WHERE year=1970 ORDER BY CASE WHEN subject IN (‘Economics’,’Chemistry’) THEN 1 ELSE 0 END ASC, subject, winner

* Questions 90- 92 , data set, scenario99.xlsx

90. Query showing how many men and women in which city

select CITY, count(kadin) kadin_s, count(erkek) erkek_s from

( select CITY, case

when GENDER = ‘K’ then 0

end as kadin, case

when GENDER = ‘E’ then 1

end as erkek

from CUSTOMERS

join CITIES on CITIES.ID = CUSTOMERS.CITYID

)t

group by CITY

91. Write the query to return the phone number as the X operator starting with 50 or 55 , the Y operator starting with 54 and Z operator starting with 53.

select SUM(XOP+TEL2XOP) AS XOPCOUNT, SUM(YOP+TEL2YOP) AS YOPCOUNT, SUM(ZOP+TEL2ZOP) AS ZOPCOUNT FROM

(SELECT

case

when TELNR1 like ‘(50%’ or TELNR1 like ‘(55%’ then 1

else 0 end as XOP,

case

when TELNR1 like ‘(54%’then 1 else 0

end as YOP,

case

when TELNR1 like ‘(53%’then 1 else 0 end as ZOP,

case

when TELNR2 like ‘(50%’ or TELNR1 like ‘(55%’ then 1

else 0 end as TEL2XOP,

case

when TELNR2 like ‘(54%’then 1 else 0

end as TEL2YOP,

case

when TELNR2 like ‘(53%’then 1 else 0

end as TEL2ZOP, * from CUSTOMERS

) T

2. WAY

SELECT (XOP+XOP2) AS XOP, YOP+YOP2 AS YOP, ZOP + ZOP2 ZOP FROM

(

SELECT TOP 1

(SELECT COUNT(*) AS XOP FROM CUSTOMERS

WHERE TELNR1 LIKE ‘(50%’ OR TELNR1 LIKE ‘(55%’) XOP,

(SELECT COUNT(*) AS YOP FROM CUSTOMERS

WHERE TELNR1 LIKE ‘(54%’ ) YOP,

(SELECT COUNT(*) AS ZOP FROM CUSTOMERS

WHERE TELNR1 LIKE ‘(53%’ ) ZOP,

(SELECT COUNT(*) AS XOP FROM CUSTOMERS

WHERE TELNR2 LIKE ‘(50%’ OR TELNR1 LIKE ‘(55%’) XOP2,

(SELECT COUNT(*) AS YOP FROM CUSTOMERS

WHERE TELNR2 LIKE ‘(54%’ ) YOP2,

(SELECT COUNT(*) AS ZOP FROM CUSTOMERS

WHERE TELNR2 LIKE ‘(53%’ ) ZOP2

FROM CUSTOMERS

)T

92. Write a query that returns the top 5 selling category of each city. Return CITY, CATECORY1, TOTAL SALE

SELECT S.CITY, C1.CATEGORY1, SUM(C1.TOTAL) AS TOTAL FROM SALEORDERS S

CROSS APPLY (SELECT TOP 5 CATEGORY1,SUM(LINETOTAL) AS TOTAL FROM SALEORDERS WHERE CITY = S.CITY

GROUP BY CATEGORY1 ORDER BY 2) C1

GROUP BY S.CITY, C1.CATEGORY1

ORDER BY CITY ASC

[1] https://www.w3resource.com/sql-exercises/sql-fromatting-output-exercises.php

[2] https://www.udemy.com/course/alistirmalarla-sql-ogreniyorum/learn/lecture/20229928?start=15

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store