к задачам SQL
.doc47. Найдите производителя, продающего ПК, но не ПК-блокноты.
select distinct p.maker
from product p
where p.type = 'PC'
and p.maker not in (select maker from product where type='Laptop')
48. Найдите размеры жестких дисков, совпадающих у двух и более PC. Вывести: HD
select p.hd
from pc p
group by hd
having count(model) >= 2
49. Найдите пары моделей PC, имеющих одинаковые скорость и RAM. В результате каждая пара указывается только один раз, т.е. (i,j), но не (j,i), Порядок вывода: модель с большим номером, модель с меньшим номером, скорость и RAM.
select distinct two.model, one.model, one.speed, one.ram
from pc one inner join pc two
on (one.ram = two.ram) and (one.speed = two.speed) and (one.model < two.model)
50. Найдите ПК-блокноты, скорость которых меньше скорости любого ПК. Вывести: type, model, speed
select p.type, l.model, l.speed
from laptop l inner join product p
on p.model = l.model and
l.speed < (select min(speed) from pc)
51. Найдите производителей самых дешевых цветных принтеров. Вывести: maker, price
select distinct p.maker, l.price
from product p, printer l
where (p.model = l.model) and
(l.price = (select min(price) from printer where color='y')) and
(l.color = 'y')
52. Для каждого производителя найдите средний размер экрана выпускаемых им ПК-блокнотов. Вывести: maker, средний размер экрана.
select p.maker, avg(l.screen)
from product p, laptop l
where (p.model = l.model)
group by p.maker
53. Найдите производителей, выпускающих по меньшей мере три различных модели ПК. Вывести: Maker, число моделей
select mm.maker, count(*)
from (select distinct p.maker maker, p.model model
from product p
where p.type='PC') as mm
group by mm.maker
having count(*) >= 3
54. Найдите максимальную цену ПК, выпускаемых каждым производителем. Вывести: maker, максимальная цена.
select p.maker, max(pk.price)
from product p inner join pc pk
on (p.model = pk.model)
group by p.maker
55. Для каждого значения скорости ПК, превышающего 600 МГц, определите среднюю цену компьютера с такой же скоростью. Вывести: speed, средняя цена.
select ss.speed, avg(pk.price)
from (select distinct speed as speed
from pc
where (speed> 600)) as ss, pc pk
where (pk.speed = ss.speed)
group by ss.speed
56. Перечислите названия головных кораблей, имеющихся в базе данных (учесть корабли в Outcomes).
select name
from ships s, classes c
where s.name = c.class
union
select o.ship
from outcomes o, classes c
where o.ship = c.class
57. Найдите классы, в которые входит только один корабль из базы данных (учесть также корабли в Outcomes).
select c.class
from (select class, name
from ships
union
select ship, ship
from outcomes
where ship in (select class
from classes)) as c
group by c.class
having count(c.class) = 1
58. Найдите страны, владевшие когда-либо как обычными кораблями, так и крейсерами.
select distinct c.country
from classes as c
where c.type = 'bb' and
c.country in (select distinct cс.country
from classes as cс
where cс.type = 'bc')
//убогий вариант:
select distinct c.country
from classes c
where (c.country in (select c.country
from classes c inner join ships s
on (s.class = c.class) and (c.type = 'bb')
union
select c.country
from classes c inner join outcomes o
on (o.ship = c.class) and (c.type = 'bb')
union
select c.country
from classes
where (c.type='bb')))
and (c.country in (select c.country
from classes c inner join ships s
on (s.class = c.class) and (c.type = 'bc')
union
select c.country
from classes c inner join outcomes o
on (o.ship = c.class) and (c.type = 'bc')
union
select c.country
from classes
where (c.type='bc')))
59. Для каждой страны определить год, когда на воду было спущено максимальное количество ее кораблей. В случае, если окажется несколько таких лет, взять минимальный из них. Вывод: страна, количество кораблей, год
//если хотите вынести моск, то вот:
select c.country, cc.qty, min(cc.launched)
from
classes c left join
(SELECT cl.country, sh.launched, case
when count(sh.name) = 0
then null
else count(sh.name)
end as qty
from classes cl
inner join ships sh
on sh.class = cl.class and sh.launched is not null
group by cl.country, sh.launched
having count(sh.name) = (select max(bb.qty)
from (SELECT cl.country, sh.launched,
case when count(sh.name) = 0
then null
else count(sh.name)
end as qty
from classes cl
inner join ships sh
on sh.class = cl.class and sh.launched is not null
group by cl.country, sh.launched) as bb
where (bb.country = cl.country))
) as cc
on c.country = cc.country
group by c.country, cc.qty
60. Найдите классы кораблей, в которых хотя бы один корабль был потоплен в сражении.
select c.class
from classes c, outcomes o, ships s
where (o.ship = s.name) and
(s.class = c.class) and
(o.result = 'sunk')
union
select c.class
from classes c, outcomes o
where (c.class = o.ship) and
(o.result = 'sunk')
61 Найдите названия кораблей с орудиями калибра 16 дюймов (учесть корабли из таблицы Outcomes).
select name
from ships s, classes c
where s.class = c.class and bore = 16
union
select ship
from outcomes o, classes c
where o.ship = c.class and bore = 16
62. С точностью до 2-х десятичных знаков определите среднее число орудий всех линейных кораблей (учесть корабли из таблицы Outcomes).
select cast(avg(cast(numGuns as decimal)) as numeric(4,2))
from (select name, numGuns
from ships s inner join classes c
on s.class = c.class and c.type='bb'
union
select ship, numGuns
from outcomes o inner join classes c
on o.ship = c.class and c.type='bb') as sh1
63. Посчитать остаток денежных средств на каждом пункте приема для базы данных с отчетностью не чаще одного раза в день. Вывод: пункт, остаток.
select i.point, i.inc-o.out
from
(select p.point, case when sum(i.inc) is null then 0 else sum(i.inc) end as inc
from (select point from income_o union select point from outcome_o) as p
left join income_o i
on i.point = p.point
group by p.point) as i
inner join
(select p.point, case when sum(o.out) is null then 0 else sum(o.out) end as out
from (select point from income_o union select point from outcome_o) as p
left join outcome_o o
on o.point = p.point
group by p.point) as o
on i.point = o.point
64. Посчитать остаток денежных средств на начало дня 15/04/01 на каждом пункте приема для базы данных с отчетностью не чаще одного раза в день. Вывод: пункт, остаток.
select i.point, i.inc-o.out
from
(select p.point, case when sum(i.inc) is null then 0 else sum(i.inc) end as inc
from (select point from income_o union select point from outcome_o) as p
left join income_o i
on (i.point = p.point) and (i.date < '20010415')
group by p.point) as i
inner join
(select p.point, case when sum(o.out) is null then 0 else sum(o.out) end as out
from (select point from income_o union select point from outcome_o) as p
left join outcome_o o
on (o.point = p.point) and (o.date < '20010415')
group by p.point) as o
on i.point = o.point
where i.inc-o.out<> 0
65. Предполагая, что среди идентификаторов квадратов имеются пропуски, найти минимальный и максимальный "свободный" идентификатор в диапазоне между имеющимися максимальным и минимальным идентификаторами. Если пропусков нет, выводить NULL. Например, для последовательности идентификаторов квадратов 1,2,5,7 результат должен быть 3 и 6.
select min(start), max(stop)
from (select l.q_id+1 as start, min(fr.q_id-1) as stop
from utq as l
left outer join utq as r on l.q_id = r.q_id - 1
left outer join utq as fr on l.q_id < fr.q_id
where
r.q_id is null and fr.q_id is not null
group by l.q_id) as z
66. Определить лидера по сумме выплат в соревновании между каждой парой пунктов с одинаковыми номерами из двух разных таблиц - outcome и outcome_o - на каждый день, когда осуществлялся прием вторсырья хотя бы на одном из них. Вывод: Номер пункта, дата, текст: - "once a day", если сумма выплат больше у фирмы с отчетностью один раз в день; - "more than once a day", если - у фирмы с отчетностью несколько раз в день; - "both", если сумма выплат одинакова.
select case when o1.point is null then o2.point else o1.point end,
case when o1.date is null then o2.date else o1.date end,
case
when o1.out is null and o2.out is not null
then 'more than once a day'
when o2.out is null and o1.out is not null
then 'once a day'
when o2.out is null and o1.out is null
then 'both'
when o1.out > o2.out then 'once a day'
when o1.out < o2.out then 'more than once a day'
when o1.out = o2.out then 'both'
else 'both' end
from
(select point, date, out from outcome_o
where (point in (select distinct point from outcome
intersect
select distinct point from outcome_o))) as o1
full join
(select point, left(convert(varchar, date, 121), 10) as date, sum(out) as out
from outcome
where (point in (select distinct point from outcome
intersect
select distinct point from outcome_o))
group by point, left(convert(varchar, date, 121), 10)) as o2
on left(convert(varchar, o1.date, 121), 10) = o2.date and (o1.point = o2.point)
67. Найдите среднюю цену ПК и ПК-блокнотов, выпущенных производителем A (латинская буква). Вывести: одна общая средняя цена.
select avg(price) from
(select k.code, k.model, k.price
from pc k inner join product p
on p.model = k.model and p.maker='A'
union
select k.code, k.model, k.price
from laptop k inner join product p
on p.model = k.model and p.maker='A') as prices
68. Найдите средний размер диска ПК каждого из тех производителей, которые выпускают и принтеры. Вывести: maker, средний размер HD
select p.maker, avg(k.hd)
from pc k inner join product p
on (k.model = p.model) and
(p.maker in (select distinct maker from product where (type='Printer')))
group by maker
69. Перечислите номера моделей любых типов, имеющих самую высокую цену по всей имеющейся в базе данных продукции
with c as (select price, model from pc
union all
select price, model from laptop
union all
select price, model from printer)
select c.model from c
where c.price = (select max(price) from c)
//как вариант:
select top 1 with ties model
from (select model, price from pc
union
select model, price from laptop
union
select model, price from printer) as prices
order by price desc
70. Укажите названия, водоизмещение и число орудий кораблей, участвовавших в сражении при Гвадалканале (Guadalcanal). Учтите, что название класса дается по первому кораблю этого класса.
select o.Ship, c.displacement, c.numguns
from outcomes o
inner join ships s on o.ship=s.Name
left join classes c on s.class=c.class
where (o.battle='Guadalcanal')
union
select o.ship, c.displacement, c.numguns
from outcomes o
inner join classes c
on (o.ship = c.class)
where (o.battle='Guadalcanal')
union
select o.ship, null, null
from outcomes o
where (o.ship not in (select name from ships union select class from classes)) and
(o.battle='Guadalcanal')