SQL exercises solving

Knowledge of SQL is essential for any back-end developer, regardless of the programming language. This issue shows a list of options for solving problems of the popular sql-ex.ru website.

Before performing tasks, you need to understand the structure of the sql query:

SELECT columns
FROM table
[WHERE row_filtration_condition]
[GROUP BY column_to_grouping]
[HAVING group_filtration_condition]
[ORDER BY columns to sort]

The HAVING clause allows you to filter groups, that is, determines which groups will be included in the output.

Using HAVING is much the same as using WHERE. Only if WHERE is used to filter rows, then HAVING is used to filter groups.

1. Find the model number, speed and hard drive capacity for all the PCs with prices below $500. Result set: model, speed, hd.

select model, speed, hd from pc where price < 500

2. List all printer makers. Result set: maker.

SELECT maker FROM Product WHERE type = 'Printer' GROUP BY maker

3. Find the model number, RAM and screen size of the laptops with prices over $1000.

Select model, ram, screen from laptop where price > 1000

4. Find all records from the Printer table containing data about color printers.

Select * from printer where color = 'y'

5. Find the model number, speed and hard drive capacity of PCs cheaper than $600 having a 12x or a 24x CD drive.

Select model, speed, hd from pc where (cd = '12x' or cd = '24x') and price < 600

6. For each maker producing laptops with a hard drive capacity of 10 Gb or higher, find the speed of such laptops. Result set: maker, speed.

Select distinct p.maker as maker, l.speed as speed from laptop l join product p on l.model = p.model where l.hd >= 10

7. Get the models and prices for all commercially available products (of any type) produced by maker B.

Select distinct p.model, pc.price from product p join pc on p.model = pc.model where maker = 'B' union Select distinct p.model, l.price from product p join laptop l on p.model = l.model where maker = 'B' union Select distinct p.model, pr.price from product p join printer pr on p.model = pr.model where maker = 'B'

8. Find the makers producing PCs but not laptops.

select maker from product where type = 'pc' except select maker from product where type = 'laptop' или select distinct maker from product where type = 'pc' and maker not in (SELECT maker from product where type = 'laptop')

9. Find the makers of PCs with a processor speed of 450 MHz or more. Result set: maker.

Select distinct p.maker from product p join pc pc on p.model = pc.model where pc.speed >= '450'

10. Find the printer models having the highest price. Result set: model, price.

SELECT DISTINCT model, price FROM printer where price = (SELECT MAX(price) FROM printer)

11. Find out the average speed of PCs.

SELECT AVG(speed) FROM PC

12. Find out the average speed of the laptops priced over $1000.

Select AVG(speed) from laptop where price > '1000'

13. Find out the average speed of the PCs produced by maker A.

Select avg(pc.speed) from pc join product p on pc.model = p.model where maker = 'A'

14. For the ships in the Ships table that have at least 10 guns, get the class, name, and country.

Select s.class, s.name, c.country from classes c join ships s on c.class = s.class where numguns >= '10'

15. Get hard drive capacities that are identical for two or more PCs.
Result set: hd.

SELECT hd FROM PC group by hd having count(model) >= 2

16. Get pairs of PC models with identical speeds and the same RAM capacity. Each resulting pair should be displayed only once, i.e. (i, j) but not (j, i).
Result set: model with the bigger number, model with the smaller number, speed, and RAM.

SELECT DISTINCT A.model AS model, B.model AS model, A.speed As speed, A.ram As ram FROM PC AS A, PC B WHERE A.speed = B.speed AND A.ram = B.ram AND A.model > B.model

17. Get the laptop models that have a speed smaller than the speed of any PC.
Result set: type, model, speed.

SELECT DISTINCT type, model, speed
FROM Laptop, (SELECT type FROM Product) AS Prod(type) WHERE speed < ALL (SELECT speed FROM PC) and type = 'laptop' или SELECT DISTINCT p.type, l.model, l.speed from laptop l join product p on p.model = l.model where l.speed < ALL (select speed from pc)

18. Find the makers of the cheapest color printers.
Result set: maker, price.

Select distinct p.maker, pr.price from product p join printer pr on p.model = pr.model where pr.price = (SELECT MIN(price)
FROM printer where color = 'y') and pr.color = 'y'

19. For each maker having models in the Laptop table, find out the average screen size of the laptops he produces.
Result set: maker, average screen size.

Select p.maker, AVG(l.screen) from product p join laptop l on p.model = l.model group by p.maker

20. Find the makers producing at least three distinct models of PCs.
Result set: maker, number of PC models.

Select maker, count(model) as Count_Model from product WHERE type = 'pc' group by maker having count(model) >= 3

21. Find out the maximum PC price for each maker having models in the PC table. Result set: maker, maximum price.

Select p.maker, max(pc.price) as max_price from product p join pc pc on p.model = pc.model group by maker

22. For each value of PC speed that exceeds 600 MHz, find out the average price of PCs with identical speeds.
Result set: speed, average price.

Select speed, avg(price) from pc where speed > '600' group by speed

23. Get the makers producing both PCs having a speed of 750 MHz or higher and laptops with a speed of 750 MHz or higher.
Result set: maker

Select p.maker from product p join pc pc on p.model = pc.model where pc.speed >= '750' intersect Select p.maker from product p join laptop l on p.model = l.model where l.speed >= '750'

24. List the models of any type having the highest price of all products present in the database.

WITH all_model AS (
SELECT model, price FROM pc
UNION ALL
SELECT model, price FROM printer
UNION ALL
SELECT model, price FROM laptop )
SELECT distinct model
FROM all_model WHERE price = ALL ( SELECT max(price) FROM all_model)

25. Find the printer makers also producing PCs with the lowest RAM capacity and the highest processor speed of all PCs having the lowest RAM capacity.
Result set: maker.

select distinct p.maker from product p join pc on p.model = pc.model where pc.ram = (select min(ram) from pc) and pc.speed = (SELECT MAX(speed) FROM pc WHERE ram = (SELECT MIN(ram) FROM pc)) and p.maker in (SELECT maker FROM product WHERE type = 'printer')

26. Find out the average price of PCs and laptops produced by maker A.
Result set: one overall average price for all items.

SELECT AVG(price) as Avg_price FROM (SELECT price
FROM PC WHERE model IN (SELECT model FROM product WHERE maker='A' AND type='PC') UNION ALL SELECT price
FROM Laptop
WHERE model IN (SELECT model FROM product WHERE maker='A' AND
type='Laptop')
) AS prods

27. Find out the average hard disk drive capacity of PCs produced by makers who also manufacture printers.
Result set: maker, average HDD capacity.

SELECT p.maker, avg(pc.hd) from product p join pc pc on p.model = pc.model WHERE pc.model IN (SELECT model FROM pc) AND maker IN (
SELECT maker FROM product WHERE type='printer') group by maker или select p.maker, avg(pc.hd) as avg_hd from product p join pc on p.model = pc.model where p.maker in (select maker from product where type = 'printer') group by p.maker

28. Using Product table, find out the number of makers who produce only one model.

select count(maker) as qty from (SELECT distinct maker
FROM product group by maker having count(model) = 1) AS prod или select distinct count(maker) as qty from product where maker in (Select maker from product group by maker having count(model) = 1)

29. Under the assumption that receipts of money (inc) and payouts (out) are registered not more than once a day for each collection point [i.e. the primary key consists of (point, date)], write a query displaying cash flow data (point, date, income, expense).
Use Income_o and Outcome_o tables.

Select i.point, i.date, inc, out from income_o i left join outcome_o o on i.point = o.point and i.date = o.date
union
Select o.point, o.date, inc, out from income_o i right join outcome_o o on i.point = o.point and i.date = o.date

30. Under the assumption that receipts of money (inc) and payouts (out) can be registered any number of times a day for each collection point [i.e. the code column is the primary key], display a table with one corresponding row for each operating date of each collection point.
Result set: point, date, total payout per day (out), total money intake per day (inc).
Missing values are considered to be NULL.

select point, date, SUM(sum_out), SUM(sum_inc)
from( select point, date, SUM(inc) as sum_inc, null as sum_out from Income Group by point, date
Union
select point, date, null as sum_inc, SUM(out) as sum_out from Outcome Group by point, date ) as t
group by point, date order by point

31. For ship classes with a gun caliber of 16 in. or more, display the class and the country.

Select class, country from classes where bore >= '16'

32. One of the characteristics of a ship is one-half the cube of the calibre of its main guns (mw).
Determine the average ship mw with an accuracy of two decimal places for each country having ships in the database.

Select country, cast(avg((power(bore,3)/2)) as numeric(6,2)) as weight
from (select country, classes.class, bore, name from classes left join ships on classes.class=ships.class
union all
select distinct country, class, bore, ship from classes t1 left join outcomes t2 on t1.class=t2.ship
where ship=class and ship not in (select name from ships) ) a
where name!='null' group by country

33. Get the ships sunk in the North Atlantic battle.
Result set: ship.

Select ship from outcomes where result = 'sunk' and battle = 'North Atlantic'

34. In accordance with the Washington Naval Treaty concluded in the beginning of 1922, it was prohibited to build battle ships with a displacement of more than 35 thousand tons.
Get the ships violating this treaty (only consider ships for which the year of launch is known).
List the names of the ships.

Select distinct name from classes, ships where launched >=1922 and displacement>35000 and type='bb' and
ships.class = classes.class

35. Find models in the Product table consisting either of digits only or Latin letters (A-Z, case insensitive) only.
Result set: model, type.

Select model, type from product where model NOT LIKE '%[^0-9]%' OR model NOT LIKE '%[^a-z]%'

36. List the names of lead ships in the database (including the Outcomes table).

Select distinct c.class from classes c join outcomes o on c.class = o.ship
union
Select distinct c.class from classes c join ships s on c.class = s.class where s.class = s.name

37. Find classes for which only one ship exists in the database (including the Outcomes table).

select class from (Select distinct c.class, o.ship as name from classes c join outcomes o on c.class = o.ship
union
Select distinct c.class,s.name from classes c join ships s on c.class = s.class) as main_class group by class having count(main_class.name) = 1

or

Select class from(select class, name from ships
union
select class, ship as name from outcomes join classes on classes.class = outcomes.ship) as A
group by class having count(A.name)=1

38. Find countries that ever had classes of both battleships (‘bb’) and cruisers (‘bc’).

SELECT country FROM classes where type = 'bb'
INTERSECT
SELECT country FROM classes where type = 'bc'

39. Find the ships that `survived for future battles`; that is, after being damaged in a battle, they participated in another one, which occurred later.

select distinct B.ship
from(select * from outcomes left join battles on battle=name where result='damaged') as B
where exists (select ship from outcomes left join battles on battle=name
where ship=B.ship and B.date<date) или select distinct o.ship from outcomes o join battles b on o.battle = b.name where o.result = 'damaged' AND
EXISTS (SELECT battles.date
FROM battles join outcomes on outcomes.battle = battles.name
WHERE battles.date > b.date and outcomes.ship = o.ship)

or

select distinct o.ship from outcomes o join battles b on o.battle = b.name where o.result = 'damaged' AND
EXISTS (SELECT battles.date
FROM battles join outcomes on outcomes.battle = battles.name
WHERE battles.date > b.date and outcomes.ship = o.ship)

40. Get the makers who produce only one product type and more than one model. Output: maker, type.

select maker, type from product
where maker in (SELECT maker FROM
(SELECT maker, type FROM Product GROUP BY maker, type) Alias
group by maker having count(maker) = 1) group by maker, type having count(type)>1

or

SELECT distinct maker, max(type) as type
FROM product
GROUP BY maker
HAVING COUNT(distinct type) = 1 AND COUNT(model) > 1

41. For each maker who has models at least in one of the tables PC, Laptop, or Printer, determine the maximum price for his products.
Output: maker; if there are NULL values among the prices for the products of a given maker, display NULL for this maker, otherwise, the maximum price.

with D as
(select model, price from PC
union
select model, price from Laptop
union
select model, price from Printer)

Select distinct P.maker,
CASE WHEN MAX(CASE WHEN D.price IS NULL THEN 1 ELSE 0 END) = 0 THEN
MAX(D.price) END
from Product P
right join D on P.model=D.model
group by P.maker

42. Find the names of ships sunk at battles, along with the names of the corresponding battles.

Select ship, battle from outcomes where result = 'sunk'

43. Get the battles that occurred in years when no ships were launched into water.

select name from battles where DATEPART(yy, date) not in (select DATEPART(yy, date)
from battles join ships on DATEPART(yy, date)=launched)

or

SELECT distinct b.name
FROM Outcomes o right JOIN
battles b ON o.ship = b.name where DATEPART(yy, b.date) not in (select launched from ships)

44. Find all ship names beginning with the letter R.

Select name from ships where name like 'R%'
union
Select ship from outcomes where ship like 'R%'

45. Find all ship names consisting of three or more words (e.g., King George V).
Consider the words in ship names to be separated by single spaces, and the ship names to have no leading or trailing spaces.

Select name from ships where name like '% % %'
union
Select ship from outcomes where ship like '% % %'

46. For each ship that participated in the Battle of Guadalcanal, get its name, displacement, and the number of guns.

SELECT DISTINCT ship, displacement, numguns
FROM classes LEFT JOIN ships ON classes.class=ships.class RIGHT JOIN outcomes ON classes.class=ship OR ships.name=ship
WHERE battle='Guadalcanal'

47. Find the countries that have lost all their ships in battles.

WITH out AS (SELECT *
FROM outcomes JOIN (SELECT ships.name s_name, classes.class s_class, classes.country s_country
FROM ships FULL JOIN classes
ON ships.class = classes.class
) u
ON outcomes.ship=u.s_class
UNION
SELECT *
FROM outcomes JOIN (SELECT ships.name s_name, classes.class s_class, classes.country s_country
FROM ships FULL JOIN classes
ON ships.class = classes.class
) u
ON outcomes.ship=u.s_name)

SELECT fin.country
FROM (
SELECT DISTINCT t.country, COUNT(t.name) AS num_ships
FROM (
select distinct c.country, s.name
from classes c
inner join Ships s on s.class= c.class
union
select distinct c.country, o.ship
from classes c
inner join Outcomes o on o.ship= c.class) t
GROUP BY t.country

INTERSECT

SELECT out.s_country, COUNT(out.ship) AS num_ships
FROM out
WHERE out.result='sunk'
GROUP BY out.s_country) fin

48. Find the ship classes having at least one ship sunk in battles.

select class
from classes t1 left join outcomes t2 on t1.class=t2.ship where result='sunk'
union
select class
from ships left join outcomes on ships.name=outcomes.ship where result='sunk'

49. Find the names of the ships having a gun caliber of 16 inches (including ships in the Outcomes table).

select s.name from ships s join classes c on s.name=c.class or s.class = c.class where c.bore = 16
union
select o.ship from outcomes o join classes c on o.ship=c.class where c.bore = 16

50. Find the battles in which Kongo-class ships from the Ships table were engaged.

Select distinct o.battle from ships s join outcomes o on s.name = o.ship where s.class = 'kongo'

51. Find the names of the ships with the largest number of guns among all ships having the same displacement (including ships in the Outcomes table).

select NAME from(select name as NAME, displacement, numguns from ships inner join classes on ships.class = classes.class union select ship as NAME, displacement, numguns from outcomes inner join classes on outcomes.ship= classes.class) as d1 inner join (select displacement, max(numGuns) as numguns from ( select displacement, numguns from ships inner join classes on ships.class = classes.class union select displacement, numguns from outcomes inner join classes on outcomes.ship= classes.class) as f group by displacement) as d2 on d1.displacement=d2.displacement and d1.numguns =d2.numguns

52. Determine the names of all ships in the Ships table that can be a Japanese battleship having at least nine main guns with a caliber of less than 19 inches and a displacement of not more than 65 000 tons.

select s.name from ships s join classes c on s.class = c.class where country = 'japan' and (numGuns >= '9' or numGuns is null) and (bore < '19' or bore is null) and (displacement <= '65000' or displacement is null) and type='bb'

53. With a precision of two decimal places, determine the average number of guns for the battleship classes

Select CAST(AVG(numguns*1.0) AS NUMERIC(6,2)) as Avg_nmg from classes where type = 'bb'

54. With a precision of two decimal places, determine the average number of guns for all battleships (including the ones in the Outcomes table).

select CAST(AVG(numguns*1.0) AS NUMERIC(6,2)) as AVG_nmg from (select ship, numguns, type from Outcomes join classes on ship = class
union
select name, numguns, type from ships s join classes c on c.class = s.class) as x where type = 'bb'

55. For each class, determine the year the first ship of this class was launched. If the lead ship’s year of launch is not known, get the minimum year of launch for the ships of this class.
Result set: class, year.

Select c.class, min(s.launched) from classes c left join ships s on c.class = s.class group by c.class

56. For each class, find out the number of ships of this class that were sunk in battles.
Result set: class, number of ships sunk.

SELECT c.class, COUNT(s.ship)
FROM classes c
LEFT JOIN (SELECT o.ship, sh.class
FROM outcomes o
LEFT JOIN ships sh ON sh.name = o.ship
WHERE o.result = 'sunk') AS s ON s.class = c.class OR s.ship = c.class
GROUP BY c.class

Leave a Reply