Задачи по SQL. Решебник SQL [3]


N
Вопрос
1
Найдите номер модели, скорость и размер жесткого диска для всех ПК стоимостью менее 500 дол. Вывести: model, speed и hd
Select model ,  speed, hd  From pc  Where price < 500 
2
Найдите производителей принтеров. Вывести: maker. (Примечание, ключевое слово distinct использовать запрещается.)
Select maker  from  product where product.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
Найдите номер модели, скорость и размер жесткого диска ПК, имеющих 12х или 24х CD и цену менее 600 дол.
Select model ,speed , hd  from pc where (cd = '12x' or cd = '24x') and price < 600
6
Укажите производителя и скорость ПК-блокнотов с жестким диском объемом не менее 10 Гбайт.
Select maker, speed  from Product inner join Laptop on Product.model = Laptop.model  
where hd >= 10
7
Найдите номера моделей и цены всех продуктов (любого типа) выпущенных производителем A (латинские буквы).
Select laptop.model , laptop.price  from laptop inner join product on laptop.model = product.model 
where product.maker= 'A'
union
Select pc.model , pc.price from pc inner join product on pc.model = product.model 
where product.maker= 'A'
union
Select printer.model , printer.price from printer inner join product on printer.model = product.model 
where product.maker= 'A'
8
Найдите производителя, продающего ПК, но не ПК-блокноты.
select maker from product where type='PC' and maker not in  
( select maker from product where type = 'Laptop') group by maker  
9
Найдите производителей ПК с процессором не менее 450 Мгц. Вывести: Maker
Select maker  from pc inner join product on pc.model = product.model where speed >= 450
group by maker
10
Найдите принтеры, имеющие самую высокую цену. Вывести: model, price
select 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(speed) from pc inner join product on pc.model= product.model where maker = 'A'  
group by maker
14
Для каждого значения скорости найдите среднюю стоимость ПК с такой же скоростью процессора. Вывести: скорость, средняя цена.
Select speed , avg(price) from pc group by speed 
15
Найдите размеры жестких дисков, совпадающих у двух и более PC. Вывести: HD
Select hd  from pc group by hd having count(model)>1 
16
Найдите производителей самых дешевых цветных принтеров. Вывести: maker, price
SELECT DISTINCT maker,price  FROM printer inner JOIN product ON printer.model= product.model 
WHERE price = (select min(price)from printer where color = 'y' ) and color = 'y' 
17
Для каждого производителя найдите средний размер экрана выпускаемых им ПК-блокнотов. Вывести: maker, средний размер экрана.
Select maker ,avg(screen)as Avg_screen
from laptop inner join product on laptop.model =  product.model group by maker 
18
Найдите максимальную цену ПК, выпускаемых каждым производителем. Вывести: maker, максимальная цена.
Select maker , max(price)as Max_price from pc inner join product on pc.model= product.model 
group by maker
19
Для каждого значения скорости ПК, превышающего 600 МГц, определите среднюю цену компьютера с такой же скоростью. Вывести: speed, средняя цена
Select speed , avg(price) as Avg_price from pc  where speed > 600 group by speed
20
Найдите средний размер диска ПК каждого из тех производителей, которые выпускают и принтеры. Вывести: maker, средний размер HD
select maker,avg(hd)  from product inner join pc on product.model=pc.model  
where maker in(select maker  from product  where type='printer')  group by maker 



22
Укажите имена и страны для классов кораблей, калибр орудий которых не менее 16 дюймов.
Select class , country
from classes
where bore >= 16
24
Укажите корабли, потопленные в сражениях в Северной Атлантике (North Atlantic). Вывод: ship
Select ship
from Outcomes
where battle = 'North Atlantic' and result='sunk';



25
По Вашингтонскому международному договору от начала 1922 г. запрещалось строить линейные корабли водоизмещением более 35 тыс.тонн. Укажите корабли, нарушившие этот договор. Вывести названия кораблей.
Select name 
from classes,ships 
where launched>=1922 and displacement>35000 and type='bb' and 
ships.class = classes.class
26
Укажите названия, водоизмещение и число орудий, кораблей участвовавших в сражении при Гвадалканале (Guadalcanal)

1.  SELECT Outcomes.ship, Classes.displacement, Classes.numGuns 
2.  FROM Classes RIGHT JOIN
3.   Outcomes ON Classes.class = Outcomes.ship 
4.  WHERE Outcomes.battle = 'Guadalcanal';
27
Перечислите названия головных кораблей, имеющихся в базе данных (учесть корабли в Outcomes)
Select name 
from ships 
where class = name 
union 
select ship as name 
from classes,outcomes 
where classes.class = outcomes.ship
28
Найдите класс, имя и страну для кораблей из таблицы Ships, имеющих не менее 10 орудий.
Select classes.class , name,country
from classes inner join ships on classes.class = ships.class
where numguns >= 10
29
Найдите названия кораблей, потопленных в сражениях, и название сражения, в котором они были потоплены.
Select ship,battle
from outcomes
where result ='sunk'
30
Найдите страны, корабли которых имеют наибольшее число орудий.
SELECT country, max(numguns)
                    FROM classes;
31
Найдите классы кораблей, в которых хотя бы один корабль был потоплен в сражении.
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'
32
Найдите сражения, в которых участвовали корабли класса Kongo
SELECT distinct battle
FROM Classes 
inner JOIN Ships  ON ships.class = classes.class
inner JOIN Outcomes  ON Classes.class=Outcomes.ship or Ships.name=Outcomes.ship
WHERE classes.class = 'Kongo'
33
Для классов, имеющих потери в виде потопленных кораблей и не менее 3 кораблей в базе данных, вывести имя класса и число потопленных кораблей.
select class as cls, count(class) as sunked from(
select C.class, O.ship from classes as C join outcomes as O on C.class=O.ship where O.result='sunk'
union
select S.class, O.ship from outcomes as O join ships as S on S.name=O.ship where O.result='sunk') as T
where class in ( select distinct X.class from  (select C.class, O.ship from classes as C join outcomes as O on C.class=O.ship
union
select C.class, S.name from classes as C join ships as S on C.class=S.class) as X group by X.class
having count(X.class)>=3 )  group by class
34
Для каждого класса определите число кораблей этого класса, потопленных в сражении. Вывести: класс и число потопленных кораблей.
1.  SELECT aa.class, SUM(aa.sunks) Sunks 
2.  FROM (
3.  -- 1
4.  SELECT c.class, COUNT(a.ship) sunks 
5.  FROM Outcomes a INNER JOIN 
6.  Ships b ON a.ship = b.name INNER JOIN 
7.  Classes c ON b.class = c.class
8.  WHERE a.result = 'sunk'
9.  GROUP BY c.class
10.UNION
11.-- 2
12.SELECT c.class, COUNT(a.ship)
13.FROM Outcomes a INNER JOIN 
14.Classes c ON a.ship = c.class
15.WHERE a.result = 'sunk'
16.GROUP BY c.class
17.UNION
18.-- 3
19.SELECT c.class, 0 
20.FROM Classes c
21.) aa
22.GROUP BY aa.class
35
Для каждого класса определите год, когда был спущен на воду первый корабль этого класса. Если год спуска на воду головного корабля неизвестен, определите минимальный год спуска на воду кораблей этого класса. Если для класса год спуска на воду кораблей данного класса неизвестен вывести значение NULL. Вывести: класс, год.
1.  SELECT C.class , launched
2.  FROM Classes C ,
3.   (SELECT name , class , launched
4.   FROM Ships
5.   UNION
6.   SELECT ship , ship , NULL
7.   FROM Outcomes O
8.   WHERE NOT EXISTS (SELECT *
9.   FROM Ships S
10. WHERE S.class = O.ship
11. )
12. UNION
13. SELECT ship , ship , MIN(launched)
14. FROM Ships S , 
15. Outcomes O
16. WHERE S.class = O.ship
17. GROUP BY ship 
18. ) S
19.WHERE C.class = S.name;
36
Определите среднее число орудий для классов линейных кораблей. Получить результат с точностью до 2-х десятичных знаков.
select cast(avg(numguns*1.0) as numeric(4,2)) as Avg_numGuns  from classes where type='bb'
37
Найдите названия кораблей, имеющих наибольшее число орудий среди всех кораблей такого же водоизмещения
(учесть корабли из таблицы Outcomes).
1.  SELECT name
2.  FROM (SELECT O.ship AS name, numGuns, displacement
3.   FROM Outcomes O INNER JOIN 
4.   Classes C ON O.ship = C.class AND 
5.   O.ship NOT IN (SELECT name 
6.   FROM Ships
7.   ) 
8.   UNION
9.   SELECT S.name AS name, numGuns, displacement 
10. FROM Ships S INNER JOIN 
11. Classes C ON S.class = C.class 
12. ) OS INNER JOIN 
13. (SELECT MAX(numGuns) AS MaxNumGuns, displacement
14. FROM Outcomes O INNER JOIN 
15. Classes C ON O.ship = C.class AND 
16. O.ship NOT IN (SELECT name 
17. FROM Ships
18. ) 
19. GROUP BY displacement
20. UNION
21. SELECT MAX(numGuns) AS MaxNumGuns, displacement
22. FROM Ships S INNER JOIN 
23. Classes C ON S.class = C.class
24. GROUP BY displacement
25. ) GD ON OS.numGuns = GD.MaxNumGuns AND 
26. OS.displacement = GD.displacement;
38
Вес снаряда (в фунтах), выпускаемого орудием, примерно равен половине куба его калибра (в дюймах). Определите средний вес снарядов для кораблей каждой страны.
SELECT DISTINCT Classes.country, ( SELECT AVG( pen.p ) FROM
   (
    SELECT (c1.bore*c1.bore*c1.bore)/2 AS p FROM Classes AS c1, Ships AS s1
         WHERE c1.class=s1.class AND c1.country = Classes.country
            AND c1.bore IS NOT NULL
    UNION ALL
    SELECT (c2.bore*c2.bore*c2.bore)/2 FROM Classes AS c2, Outcomes
        WHERE c2.country = Classes.country AND c2.class=Outcomes.ship
            AND c2.bore IS NOT NULL
            AND Outcomes.ship NOT IN ( SELECT ss.name FROM Ships AS ss )
    ) AS pen
    WHERE pen.p IS NOT NULL
             ) AS weight
FROM Classes
WHERE Classes.country IS NOT NULL
39
Найдите названия кораблей с орудиями калибра 16 дюймов
(учесть корабли из таблицы Outcomes)
select name from ships
join classes on classes.class=ships.class
where bore='16'
union
select ship from outcomes
join classes on classes.class=outcomes.ship
where bore='16'
40
Найдите классы кораблей, в которых хотя бы один корабль был потоплен в сражении
Select class as n from ships where name in(select ship from outcomes where result='sunk')  
union 
Select ship as n from outcomes 
where ship not in(Select name from ships) and ship in(Select class from classes) and result='sunk'  
41
Укажите сражения, в которых участвовало по меньшей мере три корабля одной и той же страны
1.  SELECT AA.name AS bat 
2.  FROM (SELECT O.battle AS name, C.country, COUNT(O.ship) AS cnt 
3.   FROM Outcomes O, Ships S, Classes C 
4.   WHERE O.ship = S.name AND 
5.   C.class = S.class 
6.   GROUP BY O.battle, C.country
7.   ) AA 
8.  WHERE AA.cnt >= 3;
42
Найдите названия всех кораблей в базе данных, начинающихся с буквы R
Select name
from ships
where name like 'R%'
union 
Select name
from battles
where name like 'R%'
union 
Select ship
from outcomes
where ship like 'R%'
43
Найдите пары моделей PC, имеющих одинаковые скорость и RAM. В результате каждая пара указывается только один раз, т.е. (i,j), но не (j,i), Порядок вывода: модель с большим номером, модель с меньшим номером, скорость и RAM.
SELECT distinct pc1.model, pc2.model, pc1.speed, pc1.ram
FROM pc AS pc1, pc AS pc2
WHERE pc1.model>pc2.model AND pc1.speed=pc2.speed AND pc1.ram=pc2.ram;
44
Найдите ПК-блокноты, скорость которых меньше скорости любого ПК. Вывести: ctype, model, speed
Select distinct type,laptop.model,speed from laptop inner join product on laptop.model= product.model 
where speed < (select MIN(speed) from pc) 
50
В предположении, что приход и расход денег на каждом пункте приема фиксируется не чаще одного раза в день [т.е. первичный ключ (пункт, дата)], написать запрос с выходными данными (пункт, дата, приход, расход). Использовать таблицы Income_o и Outcome_o.
51
В предположении, что приход и расход денег на каждом пункте приема фиксируется произвольное число раз [в обе таблицы добавлен первичный ключ code] , написать запрос с выходными данными (point, date, out, inc), в котором каждому пункту за каждую дату соответствует одна строка. Использовать таблицы Income и Outcome.

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
52
Подсчитать остаток денежных средств на каждом пункте приема для базы данных с отчетностью не чаще одного раза в день. Вывод: пункт, остаток. Использовать таблицы Income_o и Outcome_o.
select a.point, case when o is null then i else i-o end remain FROM  (select point, sum(inc) as i
from Income_o group by point) as A left join (select point, sum(out) as o from Outcome_o group by point) as B on A.point=B.point
53
Подсчитать остаток денежных средств на начало дня 15 апреля 2001 года на каждом пункте приема для базы данных с отчетностью не чаще одного раза в день. Вывод: пункт, остаток. Использовать таблицы Income_o и Outcome_o.
select a.point,  case when o is null  then i else i-o end remain FROM (select point, sum(inc) as i
from Income_o where '20010415' > date group by point) as A left join (select point, sum(out) as o
from Outcome_o  where '20010415' > date group by point) as B on A.point=B.point 
54
Посчитать остаток денежных средств на всех пунктах приема для базы данных с отчетностью не чаще одного раза в день. Использовать таблицы Income_o и Outcome_o.
SELECT sum(inc)-(SELECT sum(out) FROM Outcome_o) FROM Income_o
55
Посчитать остаток денежных средств на всех пунктах приема на начало дня 15 апреля 2001 года для базы данных с отчетностью не чаще одного раза в день. Использовать таблицы Income_o и Outcome_o.
SELECT
(SELECT sum(inc) FROM Income_o WHERE date<'2001-04-15')
-
(SELECT sum(out) FROM Outcome_o WHERE date<'2001-04-15')
AS remain
58
Найдите страны, владевшие когда-либо как обычными кораблями, так и крейсерами
SELECT COUNT(DISTINCT c.type) AS TypeCount, c.country

FROM classes c LEFT JOIN ships s ON s.class=c.class

WHERE

c.type='bb' OR c.type='bc'

ORDER BY c.country

GROUP BY c.country

HAVING TypeCount>1;
59
Найдите корабли, "сохранившиеся для будущих сражений"; т.е. выведенные из строя в одной битве (damaged), они участвовали в другой
SELECT s.name
FROM Ships s JOIN Outcomes o ON s.name = o.ship
   JOIN Battles b ON o.battle = b.name
GROUP BY s.name HAVING COUNT (s.name) = 2
    AND (MIN(result) = 'damaged' or MAX(result) = 'damaged')
UNION
SELECT o.ship
FROM Classes c JOIN Outcomes o ON c.class = o.ship
    JOIN Battles b ON o.battle = b.name
WHERE o.ship NOT IN(SELECT name FROM Ships)
GROUP BY o.ship HAVING COUNT (o.ship) = 2
    AND (MIN(result) = 'damaged' OR MAX(result) = 'damaged')
62
Определите среднее число орудий для классов линейных кораблей. Получить результат с точностью до 2-х десятичных знаков.
select cast(avg(numguns*1.0) as numeric(4,2)) as Avg_numGuns  from classes where type='bb'
66
Подсчитать количество записей в таблце STREET
SELECT COUNT(*) FROM STREET
78
Получить список из двух колонок: ФИО абонента, количество квитанций по оплате. Вывести только тех абонентов сумма оплаты которых находится в диапазоне от 100 грн до 300 грн

select a.fio, count(*)
from abon as a
inner join kvit b on b.shifrabo=a.shifrabo
group by 1
having sum(b.summa) between 100 and 300
158
Укажите корабли, потопленные в сражениях на Гвадалканале (Guadalcanal). Вывод: ship
select ship, displacement, numGuns from Outcomes o,ships s, classes c
where battle = 'Guadalcanal' and o.ship=s.name and s.class=c.class
union
select ship, displacement, numGuns from Outcomes o,Classes c
where o.ship=c.class and battle = 'Guadalcanal'
union
select ship, null as displacement, null as numGuns from Outcomes
where battle = 'Guadalcanal' and ship not in (select name from ships)
and ship not in (select class from classes)
272
Вести количество оценок из таблицы tb_ocenki, значение которых равно 10
SELECT count(*) AS kolichestvo FROM TB_OCENKI WHERE ocenka = 10

на
Ярлыки:

Комментариев нет:

Отправить комментарий

Подписаться на: Комментарии к сообщению (Atom)