Решение задач по SQL

Знание SQL необходимо любому back-end разработчику, независимо от языка программирования. В данном выпуске продемонстрирован список вариантов решений задач популярного сайта sql-ex.ru.

Перед выполнением задач, необходимо понимать структуру sql запроса:

SELECT столбцы
FROM таблица
[WHERE условие_фильтрации_строк]
[GROUP BY столбцы_для_группировки]
[HAVING условие_фильтрации_групп]
[ORDER BY столбцы_для_сортировки]

Оператор HAVING позволяет выполнить фильтрацию групп, то есть определяет, какие группы будут включены в выходной результат.

Использование HAVING во многом аналогично применению WHERE. Только если WHERE применяется для фильтрации строк, то HAVING — для фильтрации групп.

1. Найдите номер модели, скорость и размер жесткого диска для всех ПК стоимостью менее 500 дол. Вывести: model, speed и hd

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

2. Найдите производителей принтеров. Вывести: maker

SELECT maker FROM Product WHERE type = ‘Printer’ GROUP BY maker

3. Найдите номер модели, объем памяти и размеры экранов ПК-блокнотов, цена которых превышает 1000 дол.

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

4. Найдите все записи таблицы Printer для цветных принтеров.

Select * from printer where color = ‘y’

5. Найдите номер модели, скорость и размер жесткого диска ПК, имеющих 12x или 24x CD и цену менее 600 дол.

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

6. Для каждого производителя, выпускающего ПК-блокноты c объёмом жесткого диска не менее 10 Гбайт, найти скорости таких ПК-блокнотов. Вывод: производитель, скорость.

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. Найдите номера моделей и цены всех имеющихся в продаже продуктов (любого типа) производителя 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. Найдите производителя, выпускающего ПК, но не ПК-блокноты.

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. Найдите производителей ПК с процессором не менее 450 Мгц. Вывести: Maker

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

10. Найдите модели принтеров, имеющих самую высокую цену. Вывести: model, price

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

11. Найдите среднюю скорость ПК.

SELECT AVG(speed) FROM PC

12. Найдите среднюю скорость ПК-блокнотов, цена которых превышает 1000 дол.

Select AVG(speed) from laptop where price > ‘1000’

13. Найдите среднюю скорость ПК, выпущенных производителем A

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

14. Найдите класс, имя и страну для кораблей из таблицы Ships, имеющих не менее 10 орудий.

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

15. Найдите размеры жестких дисков, совпадающих у двух и более PC. Вывести: HD

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

16. Найдите пары моделей PC, имеющих одинаковые скорость и RAM. В результате каждая пара указывается только один раз, т.е. (i,j), но не (j,i), Порядок вывода: модель с большим номером, модель с меньшим номером, скорость и 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. Найдите модели ПК-блокнотов, скорость которых меньше скорости каждого из ПК. Вывести: 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. Найдите производителей самых дешевых цветных принтеров. Вывести: 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. Для каждого производителя, имеющего модели в таблице Laptop, найдите средний размер экрана выпускаемых им ПК-блокнотов.
Вывести: maker, средний размер экрана.

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

20. Найдите производителей, выпускающих по меньшей мере три различных модели ПК. Вывести: Maker, число моделей ПК.

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

21. Найдите максимальную цену ПК, выпускаемых каждым производителем, у которого есть модели в таблице PC.
Вывести: maker, максимальная цена.

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

22. Для каждого значения скорости ПК, превышающего 600 МГц, определите среднюю цену ПК с такой же скоростью. Вывести: speed, средняя цена.

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

23. Найдите производителей, которые производили бы как ПК
со скоростью не менее 750 МГц, так и ПК-блокноты со скоростью не менее 750 МГц. Вывести: 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. Перечислите номера моделей любых типов, имеющих самую высокую цену по всей имеющейся в базе данных продукции.

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. Найдите производителей принтеров, которые производят ПК с наименьшим объемом RAM и с самым быстрым процессором среди всех ПК, имеющих наименьший объем RAM. Вывести: 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. Найдите среднюю цену ПК и ПК-блокнотов, выпущенных производителем A (латинская буква). Вывести: одна общая средняя цена.

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. Найдите средний размер диска ПК каждого из тех производителей, которые выпускают и принтеры. Вывести: maker, средний размер HD.

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. Используя таблицу Product, определить количество производителей, выпускающих по одной модели.

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. В предположении, что приход и расход денег на каждом пункте приема фиксируется не чаще одного раза в день [т.е. первичный ключ (пункт, дата)], написать запрос с выходными данными (пункт, дата, приход, расход). Использовать таблицы Income_o и Outcome_o.

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. В предположении, что приход и расход денег на каждом пункте приема фиксируется произвольное число раз (первичным ключом в таблицах является столбец code), требуется получить таблицу, в которой каждому пункту за каждую дату выполнения операций будет соответствовать одна строка.
Вывод: point, date, суммарный расход пункта за день (out), суммарный приход пункта за день (inc). Отсутствующие значения считать неопределенными (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. Для классов кораблей, калибр орудий которых не менее 16 дюймов, укажите класс и страну.

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

32. Одной из характеристик корабля является половина куба калибра его главных орудий (mw). С точностью до 2 десятичных знаков определите среднее значение mw для кораблей каждой страны, у которой есть корабли в базе данных.

33. Укажите корабли, потопленные в сражениях в Северной Атлантике (North Atlantic). Вывод: ship.

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

34. По Вашингтонскому международному договору от начала 1922 г. запрещалось строить линейные корабли водоизмещением более 35 тыс.тонн. Укажите корабли, нарушившие этот договор (учитывать только корабли c известным годом спуска на воду). Вывести названия кораблей.

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

35. В таблице Product найти модели, которые состоят только из цифр или только из латинских букв (A-Z, без учета регистра).
Вывод: номер модели, тип модели.

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

36. Перечислите названия головных кораблей, имеющихся в базе данных (учесть корабли в Outcomes).

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. Найдите классы, в которые входит только один корабль из базы данных (учесть также корабли в Outcomes).

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

или

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. Найдите страны, имевшие когда-либо классы обычных боевых кораблей (‘bb’) и имевшие когда-либо классы крейсеров (‘bc’).

SELECT country FROM classes where type = ‘bb’
INTERSECT
SELECT country FROM classes where type = ‘bc’

39. Найдите корабли, `сохранившиеся для будущих сражений`; т.е. выведенные из строя в одной битве (damaged), они участвовали в другой, произошедшей позже.

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)

или

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. Найти производителей, которые выпускают более одной модели, при этом все выпускаемые производителем модели являются продуктами одного типа. Вывести: 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

или

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

41. Для каждого производителя, у которого присутствуют модели хотя бы в одной из таблиц PC, Laptop или Printer,
определить максимальную цену на его продукцию.
Вывод: имя производителя, если среди цен на продукцию данного производителя присутствует NULL, то выводить для этого производителя NULL,
иначе максимальную цену.

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. Найдите названия кораблей, потопленных в сражениях, и название сражения, в котором они были потоплены.

Select ship, battle from outcomes where result = ‘sunk’

43. Укажите сражения, которые произошли в годы, не совпадающие ни с одним из годов спуска кораблей на воду.

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

или

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. Найдите названия всех кораблей в базе данных, начинающихся с буквы R.

Select name from ships where name like ‘R%’
union
Select ship from outcomes where ship like ‘R%’

45. Найдите названия всех кораблей в базе данных, состоящие из трех и более слов (например, King George V).
Считать, что слова в названиях разделяются единичными пробелами, и нет концевых пробелов.

Select name from ships where name like ‘% % %’
union
Select ship from outcomes where ship like ‘% % %’

46. Для каждого корабля, участвовавшего в сражении при Гвадалканале (Guadalcanal), вывести название, водоизмещение и число орудий.

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. Определить страны, которые потеряли в сражениях все свои корабли.

48. Найдите классы кораблей, в которых хотя бы один корабль был потоплен в сражении.

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. Найдите названия кораблей с орудиями калибра 16 дюймов (учесть корабли из таблицы Outcomes).

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. Найдите сражения, в которых участвовали корабли класса Kongo из таблицы Ships.

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

51. Найдите названия кораблей, имеющих наибольшее число орудий среди всех имеющихся кораблей такого же водоизмещения (учесть корабли из таблицы Outcomes).

52. Определить названия всех кораблей из таблицы Ships, которые могут быть линейным японским кораблем, имеющим число главных орудий не менее девяти, калибр орудий менее 19 дюймов и водоизмещение не более 65 тыс.тонн

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. Определите среднее число орудий для классов линейных кораблей. Получить результат с точностью до 2-х десятичных знаков.

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

54. С точностью до 2-х десятичных знаков определите среднее число орудий всех линейных кораблей (учесть корабли из таблицы Outcomes).

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. Для каждого класса определите год, когда был спущен на воду первый корабль этого класса. Если год спуска на воду головного корабля неизвестен, определите минимальный год спуска на воду кораблей этого класса. Вывести: класс, год.

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

56. Для каждого класса определите число кораблей этого класса, потопленных в сражениях. Вывести: класс и число потопленных кораблей.

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

Добавить комментарий