Упражнение 37 стр. 1
Найдите классы, в которые входит только один корабль из базы данных (учесть также корабли в Outcomes).
Вот один из запросов, которые отвергает система проверки:
Консоль
Первый запрос в объединении подсчитывает корабли каждого класса из таблицы Ships, оставляя в результирующем наборе только те классы, которые имеют в этой таблице только один корабль. Второй запрос определяет классы, у которых головной корабль находится в таблице Outcomes при условии, что кораблей такого класса нет в таблице Ships.
Рассмотрим следующий пример данных, для которых этот запрос будет давать неправильный результат.
Каждый, кто решал задачи по схеме данных «Корабли», знает, что такое «Бисмарк» (Bismarck). Это головной корабль, которого нет в таблице Ships. Теперь представим себе, что другой корабль класса «Бисмарк» имеется в таблице Ships, скажем, «Тирпиц» (Tirpitz).
Тогда первый запрос вернет класс «Бисмарк», так как в таблице Ships имеется один корабль этого класса. Второй запрос класс «Бисмарк» не вернет, так как предикат:
Упражнения по SQL
SELECT (обучающий этап) задачи по SQL запросам 120 штук, DML 10 шт. Дистанционное обучение языку баз данных SQL. Интерактивные упражнения и тестирование по операторам SELECT,INSERT,UPDATE,DELETE языка SQL. SQL remote education. SQL statements exercises. Подзапросы, Соединение таблиц, Функции SQL, Введение в SQL, Скачать книги по SQL. Команды SQL,CREATE SEQUENCE,CREATE SYNONYM,CREATE USER,CREATE VIEW,Create Table,DROP,GRANT,INSERT,REVOKE,SET ROLE,SET TRANSACTION,SQL ALTER TABLE,SQL команды.
к задачам SQL
47. Найдите производителя, продающего ПК, но не ПК-блокноты.
select distinct p.maker
where p.type = ‘PC’
and p.maker not in (select maker from product where type=’Laptop’)
48. Найдите размеры жестких дисков, совпадающих у двух и более PC. Вывести: 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
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
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
where (speed> 600)) as ss, pc pk
where (pk.speed = ss.speed)
group by ss.speed
56. Перечислите названия головных кораблей, имеющихся в базе данных (учесть корабли в Outcomes).
from ships s, classes c
where s.name = c.class
from outcomes o, classes c
where o.ship = c.class
57. Найдите классы, в которые входит только один корабль из базы данных (учесть также корабли в Outcomes).
from (select class, name
select ship, ship
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
where (c.country in (select c.country
from classes c inner join ships s
on (s.class = c.class) and (c.type = ‘bb’)
from classes c inner join outcomes o
on (o.ship = c.class) and (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’)
from classes c inner join outcomes o
on (o.ship = c.class) and (c.type = ‘bc’)
59. Для каждой страны определить год, когда на воду было спущено максимальное количество ее кораблей. В случае, если окажется несколько таких лет, взять минимальный из них. Вывод: страна, количество кораблей, год
//если хотите вынести моск, то вот:
select c.country, cc.qty, min(cc.launched)
classes c left join
(SELECT cl.country, sh.launched, case
when count(sh.name) = 0
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
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))
on c.country = cc.country
group by c.country, cc.qty
60. Найдите классы кораблей, в которых хотя бы один корабль был потоплен в сражении.
from classes c, outcomes o, ships s
where (o.ship = s.name) and
(s.class = c.class) and
from classes c, outcomes o
where (c.class = o.ship) and
61 Найдите названия кораблей с орудиями калибра 16 дюймов (учесть корабли из таблицы Outcomes).
from ships s, classes c
where s.class = c.class and bore = 16
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’
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
(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
(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
(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
(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
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
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,
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
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’
(select point, date, out from outcome_o
where (point in (select distinct point from outcome
select distinct point from outcome_o))) as o1
(select point, left(convert(varchar, date, 121), 10) as date, sum(out) as out
where (point in (select distinct point from outcome
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’
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’)))
69. Перечислите номера моделей любых типов, имеющих самую высокую цену по всей имеющейся в базе данных продукции
with c as (select price, model from pc
select price, model from laptop
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
select model, price from laptop
select model, price from printer) as prices
order by price desc
70. Укажите названия, водоизмещение и число орудий кораблей, участвовавших в сражении при Гвадалканале (Guadalcanal). Учтите, что название класса дается по первому кораблю этого класса.
Найдите классы в которые входит только один корабль из базы данных учесть также корабли в outcomes
Стань тестировщиком сегодня
Поддержи
Украину!
Практический тренинг по SQL
Думаю самое время применить на практике, все что ты выучил в уровне, время для настоящего SQL сражения! В этот уровне попадутся задания как из первой базы данных так и со второй, повторять их описание мы не будем, а только напомним схемы. Мы верим в тебя уже опытный джедай SQL, построй правильный запросы, и тебе откроются тайны древних сражений.
В этом задании запросы на голову сложнее чем в предыдущих двух уровнях, обрати внимание, что практически во всех запросах прйдеться употребить JOIN, LEFT JOIN или INNER JOIN, так что повтори эти темы еще раз перед началом практики.
Хорошенько разберись с устройством таблиц перед тем как начать писать запросы. В любом случае возвращайся к схеме как только она тебе понадобиться
Рассматривается БД кораблей, участвовавших во второй мировой войне. Имеются следующие отношения:
Classes (class, type, country, numGuns, bore, displacement)
Ships (name, class, launched)
Battles (name, date)
Outcomes (ship, battle, result)
Корабли в «классах» построены по одному и тому же проекту, и классу присваивается либо имя первого корабля, построенного по данному проекту, либо названию класса дается имя проекта, которое не совпадает ни с одним из кораблей в БД. Корабль, давший название классу, называется головным.
Отношение Classes содержит имя класса, тип (bb для боевого (линейного) корабля или bc для боевого крейсера), страну, в которой построен корабль, число главных орудий, калибр орудий (диаметр ствола орудия в дюймах) и водоизмещение ( вес в тоннах).
В отношении Ships записаны название корабля, имя его класса и год спуска на воду.
В отношение Battles включены название и дата битвы, в которой участвовали корабли, а в отношении Outcomes – результат участия данного корабля в битве (потоплен-sunk, поврежден — damaged или невредим — OK).
Замечания. 1) В отношение Outcomes могут входить корабли, отсутствующие в отношении Ships. 2) Потопленный корабль в последующих битвах участия не принимает.