Половина куба калибра его главных орудий mw формула

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

Калибр орудий, как и страна, является атрибутом таблицы Classes. Таким образом, здесь нужно найти все корабли в базе данных, для которых известен класс. Замечание об учете кораблей из таблицы Outcomes означает, как обычно, что класс головного корабля может быть известен, даже если его нет в таблице Ships.

Затем следует добавить вычисляемый столбец для определения веса снаряда и посчитать среднее значение этого веса, сгруппировав корабли по странам.

Рассмотрим следующий запрос, отбраковываемый системой.

Запрос интересен тем, что в нем не используется группировка, а среднее значение по стране определяется с помощью коррелирующего подзапроса, выполняемого для каждой страны из таблицы Classes. Кроме того, он выполнен в полном соответствии со стандартом. Можно сразу сделать замечание относительно эффективности выполнения этого запроса, так как если у страны несколько классов кораблей (что не является для нас большой неожиданностью), то фактически подзапрос будет выполняться для каждого класса, что явно излишне. Появляющиеся при этом дубликаты записей устраняются при помощи DISTINCT , что тоже скажется на производительности. Но нас интересует другой вопрос, а именно, почему этот запрос неверен. Чтобы это понять, давайте рассмотрим его по частям.

Начнем с подзапроса, в котором объединяются ( UNION ALL ) два запроса:

В запросе (1) вычисляется вес снарядов кораблей из таблицы Ships для страны, передаваемой из внешнего запроса (коррелирующий подзапрос). Условие c1.bore IS NOT NULL , на наш взгляд, совершенно излишне, так как даже если и есть классы с неизвестным калибром, такие значения автоматически будут исключены при вычислении среднего значения с помощью функции AVG . Но это не ошибка в решении задачи.

В запросе (2) аналогичные вычисления делаются для головных кораблей из Outcomes, которые отсутствуют в Ships.

Далее объединение с помощью UNION ALL позволяет сохранить все дубликаты веса, что необходимо, так как, по крайней мере, корабли одного класса имеют снаряды одного калибра (веса).

Во внешнем запросе вычисляется среднее значение по стране, отфильтровывая случай, когда калибр неизвестен для всех кораблей некоторой страны ( WHERE pen.p IS NOT NULL ). Это объясняется тем, что если AVG применяется к пустому набору записей, то результат вычисления будет NULL .

Наконец, в основном запросе выводим требуемые по условиям задачи данные.

Вы уже нашли ошибку? Если нет, то нам помогут знания предметной области. Что за таблица Outcomes? Здесь хранятся данные об участии кораблей в сражениях. А корабль, если он не был потоплен, может принимать участие в нескольких сражениях. Таким образом, мы потенциально учитываем головной корабль несколько раз. Если же рассуждать формально, то первичный ключ на этой таблице допускает появление одного и того же корабля неоднократно.

При этом мы не можем вместо UNION ALL использовать UNION по описанным выше причинам, но, тем не менее, исправить теперь этот запрос вам будет несложно.

Разбирая ошибки наших посетителей, автор обычно указывает те варианты данных, на которых рассматриваемые запросы возвращают неверные данные. Советуем вам наполнять свою базу аналогичными данными, тогда тестирование ваших запросов также и на других задачах будет более эффективным.

Упражнение 32 стр. 1

Рассмотрим следующий запрос, отбраковываемый системой.

Начнем с подзапроса, в котором объединяются ( UNION ALL ) два запроса:

В запросе (2) аналогичные вычисления делаются для головных кораблей из Outcomes, которые отсутствуют в 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 команды.

четверг, 23 февраля 2017 г.

SELECT (обучающий этап) задачи по SQL запросам

Задачи по SQL запросам

SELECT model, speed, hd FROM PC WHERE price Задание: 2 (Serge I: 2002-09-21)

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

SELECT model,ram,screen FROM Laptop WHERE price > 1000

SELECT * FROM Printer WHERE color = ‘y’

SELECT model,speed,hd FROM PC WHERE ( cd = ’12x’ OR cd = ’24x’ ) AND price Для каждого производителя, выпускающего ПК-блокноты c объёмом жесткого диска не менее 10 Гбайт, найти скорости таких ПК-блокнотов. Вывод: производитель, скорость.

SELECT DISTINCT p.maker, l.speed
FROM laptop l
JOIN product p ON p.model = l.model
WHERE l.hd >= 10

SELECT DISTINCT product.model, pc.price
FROM Product JOIN pc ON product.model = pc.model WHERE maker = ‘B’
UNION
SELECT DISTINCT product.model, laptop.price
FROM product JOIN laptop ON product.model=laptop.model WHERE maker=’B’
UNION
SELECT DISTINCT product.model, printer.price
FROM product JOIN printer ON product.model=printer.model WHERE maker=’B’;

SELECT DISTINCT maker
FROM product
WHERE type = ‘pc’
EXCEPT
SELECT DISTINCT product.maker
FROM product
Where type = ‘laptop’

SELECT DISTINCT product.maker
FROM pc
INNER JOIN product ON pc.model = product.model
WHERE pc.speed >= 450

SELECT model, price
FROM printer
WHERE price =
(SELECT MAX(price)
FROM printer )

SELECT AVG(speed)
FROM laptop
WHERE price > 1000

SELECT AVG(pc.speed)
FROM pc, product
WHERE pc.model = product.model AND product.maker = ‘A’

SELECT maker, MAX(type)
FROM product
GROUP BY maker
HAVING COUNT(DISTINCT type) = 1 AND COUNT(model) > 1

SELECT hd FROM pc GROUP BY (hd) HAVING COUNT(model) >= 2

SELECT DISTINCT p1.model, p2.model, p1.speed, p1.ram
FROM pc p1, pc p2
WHERE p1.speed = p2.speed AND p1.ram = p2.ram AND p1.model > p2.model

select distinct p.type,p.model,l.speed
from laptop l
join product p on l.model=p.model
where l.speed Найдите производителей самых дешевых цветных принтеров. Вывести: maker, price

SELECT DISTINCT product.maker, printer.price
FROM product, printer
WHERE product.model = printer.model
AND printer.color = ‘y’
AND printer.price = (
SELECT MIN(price) FROM printer
WHERE printer.color = ‘y’
)

SELECT
product.maker, AVG(screen)
FROM laptop
LEFT JOIN product ON product.model = laptop.model
GROUP BY product.maker

SELECT maker, COUNT(model)
FROM product
WHERE type = ‘pc’
GROUP BY product.maker
HAVING COUNT (DISTINCT model) >= 3

SELECT product.maker, MAX(pc.price)
FROM product, pc
WHERE product.model = pc.model
GROUP BY product.maker

SELECT pc.speed, AVG(pc.price)
FROM pc
WHERE pc.speed > 600
GROUP BY pc.speed

SELECT DISTINCT maker
FROM product t1 JOIN pc t2 ON t1.model=t2.model
WHERE speed>=750 AND maker IN
( SELECT maker
FROM product t1 JOIN laptop t2 ON t1.model=t2.model
WHERE speed>=750 )

SELECT model
FROM (
SELECT model, price
FROM pc
UNION
SELECT model, price
FROM Laptop
UNION
SELECT model, price
FROM Printer
) t1
WHERE price = (
SELECT MAX(price)
FROM (
SELECT price
FROM pc
UNION
SELECT price
FROM Laptop
UNION
SELECT price
FROM Printer
) t2
)

SELECT DISTINCT maker
FROM product
WHERE model IN (
SELECT model
FROM pc
WHERE ram = (
SELECT MIN(ram)
FROM pc
)
AND speed = (
SELECT MAX(speed)
FROM pc
WHERE ram = (
SELECT MIN(ram)
FROM pc
)
)
)
AND
maker IN (
SELECT maker
FROM product
WHERE type=’printer’
)

SELECT sum(s.price)/sum(s.kol) as sredn FROM
(SELECT price,1 as kol FROM pc,product
WHERE pc.model=product.model AND product.maker=’A’
UNION all
SELECT price,1 as kol FROM laptop,product
WHERE laptop.model=product.model AND product.maker=’A’) as s

SELECT product.maker, AVG(pc.hd)
FROM pc, product WHERE product.model = pc.model
AND product.maker IN ( SELECT DISTINCT maker
FROM product
WHERE product.type = ‘printer’)
GROUP BY maker

SELECT AVG(pc.hd)
FROM pc, product
WHERE product.model = pc.model
AND product.maker IN (SELECT DISTINCT maker
FROM product WHERE product.type = ‘printer’)

SELECT t1.point, t1.date, inc, out
FROM income_o t1 LEFT JOIN outcome_o t2 ON t1.point = t2.point
AND t1.date = t2.date
UNION
SELECT t2.point, t2.date, inc, out
FROM income_o t1 RIGHT JOIN outcome_o t2 ON t1.point = t2.point
AND t1.date = t2.date

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

SELECT DISTINCT class, country
FROM classes
WHERE bore >= 16

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 IS NOT NULL group by country

SELECT o.ship FROM
BATTLES b
LEFT join outcomes o ON o.battle = b.name
WHERE b.name = ‘North Atlantic’ AND o.result = ‘sunk’

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

SELECT model, type
FROM product
WHERE upper(model) NOT like ‘%[^A-Z]%’
OR model not like ‘%[^0-9]%’

Select name from ships where/> union
select ship as name from classes,outcomes where classes.class = outcomes.ship

SELECT c.class
FROM classes c
LEFT JOIN (
SELECT class, name
FROM ships
UNION
SELECT ship, ship
FROM outcomes
) AS s ON s.class = c.class
GROUP BY c.class
HAVING COUNT(s.name) = 1

SELECT country
FROM classes
GROUP BY country
HAVING COUNT(DISTINCT type) = 2

WITH b_s AS
(SELECT o.ship, b.name, b.date, o.result
FROM outcomes o
LEFT JOIN battles b ON o.battle = b.name )
SELECT DISTINCT a.ship FROM b_s a
WHERE UPPER(a.ship) IN
(SELECT UPPER(ship) FROM b_s b
WHERE b.date Найдите класс, имя и страну для кораблей из таблицы Ships, имеющих не менее 10 орудий.

SELECT s.class, s.name, c.country
FROM ships s
LEFT JOIN classes c ON s.class = c.class
WHERE c.numGuns >= 10

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

Select Classes.class, Ships.name, Classes.country
from Ships inner join Classes
on Ships.class = Classes.class
where Classes.numGuns > = 10

Для ПК с максимальным кодом из таблицы PC вывести все его характеристики (кроме кода) в два столбца:
— название характеристики (имя соответствующего столбца в таблице PC);
— значение характеристики

select fields,A from
(
Select
cast(model as NVARCHAR(10)) as model
, cast (speed as NVARCHAR(10)) as speed
, cast(ram as NVARCHAR(10)) as ram
,cast(hd as NVARCHAR(10)) as hd
, cast(cd as NVARCHAR(10)) as cd
, cast(price as NVARCHAR(10)) as price from PC
where code = (Select max(code) from PC)
) as t

unpivot
(
A for fields in (model, speed, ram, hd, cd, price)
) as unpvt

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

SELECT ship, battle FROM Outcomes WHERE result = ‘sunk’

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

select name
from battles
where year(date) not in
(select launched
from ships
where launched is not null)

Найдите названия всех кораблей в базе данных, начинающихся с буквы R.

select name from Ships
where name LIKE ‘R%’
UNION
SELECT Ship From Outcomes
where Ship LIKE ‘R%’

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

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

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

SELECT o.ship, displacement, numGuns FROM
(SELECT name AS ship, displacement, numGuns
FROM Ships s JOIN Classes c ON c.class=s.class
UNION
SELECT class AS ship, displacement, numGuns
FROM Classes c) AS a
RIGHT JOIN Outcomes o
ON o.ship=a.ship
WHERE battle = ‘Guadalcanal’

Пронумеровать строки из таблицы Product в следующем порядке: имя производителя в порядке убывания числа производимых им моделей (при одинаковом числе моделей имя производителя в алфавитном порядке по возрастанию), номер модели (по возрастанию).
Вывод: номер в соответствии с заданным порядком, имя производителя (maker), модель (model)

SELECT count(*) OVER (order by c.countM DESC, c.maker,c.model) no, c.maker, c.model
FROM (SELECT count(*) OVER(partition by maker) countM, maker,model
FROM product) c

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

SELECT cl.class
FROM Classes cl
LEFT JOIN Ships s ON s.class = cl.class
WHERE cl.class IN (SELECT ship FROM Outcomes WHERE result = ‘sunk’) OR
s.name IN (SELECT ship FROM Outcomes WHERE result = ‘sunk’)
GROUP BY cl.class

Найдите названия кораблей с орудиями калибра 16 дюймов (учесть корабли из таблицы Outcomes).

SELECT Ships.name
FROM Classes JOIN
Ships ON Classes.class = ships.class
WHERE bore = 16
UNION
SELECT Outcomes.ship
FROM Outcomes JOIN
Classes ON Classes.class = Outcomes.ship
WHERE bore = 16

Найдите сражения, в которых участвовали корабли класса Kongo из таблицы Ships.

select distinct battle from outcomes
where ship in (select name
from ships
where )

SELECT name
FROM (SELECT O.ship AS name, numGuns, displacement
FROM Outcomes O INNER JOIN
Classes C ON O.ship = C.class AND
O.ship NOT IN (SELECT name
FROM Ships
)
UNION
SELECT S.name AS name, numGuns, displacement
FROM Ships S INNER JOIN
Classes C ON S.class = C.class
) OS INNER JOIN
(SELECT MAX(numGuns) AS MaxNumGuns, displacement
FROM Outcomes O INNER JOIN
Classes C ON O.ship = C.class AND
O.ship NOT IN (SELECT name
FROM Ships
)
GROUP BY displacement
UNION
SELECT MAX(numGuns) AS MaxNumGuns, displacement
FROM Ships S INNER JOIN
Classes C ON S.class = C.class
GROUP BY displacement
) GD ON OS.numGuns = GD.MaxNumGuns AND
OS.displacement = GD.displacement

SELECT DISTINCT s.name
FROM ships s
JOIN classes c ON c.class = s.class
WHERE UPPER(c.country) = ‘JAPAN’
and (numguns>=9 or numguns is NULL)
AND (c.bore Определите среднее число орудий для классов линейных кораблей.
Получить результат с точностью до 2-х десятичных знаков.

select round(avg(numGuns),2)
from classes where type=’bb’;

select round(avg(numguns),2)
from (
Select numguns, name
from classes left join ships using(class)
where type=’bb’ and name!=’null’ and class!=’null’
union all
select distinct numguns, ship
from classes left join outcomes on classes.class=outcomes.ship
where ship not in (select name from ships) and class!=’null’ and type=’bb’ )a;

SELECT c.class, t.y
FROM classes c
LEFT JOIN
(SELECT class, MIN(launched) AS y
FROM ships
GROUP BY class
) AS t ON c.class = t.class

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

SELECT c.class, SUM(sh.sunked)
FROM classes c
LEFT JOIN (
SELECT t.name AS name, t.class AS class,
CASE WHEN o.result = ‘sunk’ THEN 1 ELSE 0 END AS sunked
FROM
(
SELECT name, class
FROM ships
UNION
SELECT ship, ship
FROM outcomes
)
AS t
LEFT JOIN outcomes o ON t.name = o.ship
) sh ON sh.class = c.class
GROUP BY c.class
HAVING COUNT(DISTINCT sh.name) >= 3 AND SUM(sh.sunked) > 0

SELECT m, t,
CAST(100.0*cc/cc1 AS NUMERIC(5,2))
from
(SELECT m, t, sum(c) cc from
(SELECT distinct maker m, ‘PC’ t, 0 c from product
union all
SELECT distinct maker, ‘Laptop’, 0 from product
union all
SELECT distinct maker, ‘Printer’, 0 from product
union all
SELECT maker, type, count(*) from product
group by maker, type) as tt
group by m, t) tt1
JOIN (
SELECT maker, count(*) cc1 from product group by maker
) tt2
ON m=maker

SELECT c1, c2-
(CASE
WHEN o2 is null THEN 0
ELSE o2
END)
from
(SELECT point c1, sum(inc) c2 FROM income_o
group by point) as t1
left join
(SELECT point o1, sum(out) o2 FROM outcome_o
group by point) as t2
on c1=o1

SELECT c1, c2-
(CASE
WHEN o2 is null THEN 0
ELSE o2
END)
from
(SELECT point c1, sum(inc) c2 FROM income_o
where date Посчитать остаток денежных средств на всех пунктах приема для базы данных с отчетностью не чаще одного раза в день.

SELECT sum(i) FROM
(SELECT point, sum(inc) as i FROM
income_o
group by point

SELECT point, -sum(out) as i FROM
outcome_o
group by point
) as t

SELECT
(SELECT sum(inc) FROM Income_o WHERE date Определить имена разных пассажиров, когда-либо летевших на одном и том же месте более одного раза.

SELECT name FROM Passenger
WHERE ID_psg in
(SELECT ID_psg FROM Pass_in_trip
GROUP BY place, ID_psg
HAVING count(*)>1)

SELECT i1.point, i1.date, ‘inc’, sum(inc) FROM Income,
(SELECT point, date FROM Income
EXCEPT
SELECT Income.point, Income.date FROM Income
JOIN Outcome ON (Income.point=Outcome.point) AND
(Income.date=Outcome.date)
) AS i1
WHERE i1.point=Income.point AND i1.date=Income.date
GROUP BY i1.point, i1.date
UNION
SELECT o1.point, o1.date, ‘out’, sum(out) FROM Outcome,
(SELECT point, date FROM Outcome
EXCEPT
SELECT Income.point, Income.date FROM Income
JOIN Outcome ON (Income.point=Outcome.point) AND
(Income.date=Outcome.date)
) AS o1
WHERE o1.point=Outcome.point AND o1.date=Outcome.date
GROUP BY o1.point, o1.date

SELECT row_number() over(ORDER BY maker,s),t, type FROM
(SELECT maker,type,
CASE
WHEN type=’PC’
THEN 0
WHEN type=’Laptop’
THEN 1
ELSE 2
END AS s,
CASE
WHEN type=’Laptop’ AND (maker in (SELECT maker FROM Product WHERE
type=’PC’))
THEN
WHEN type=’Printer’ AND ((maker in (SELECT maker FROM Product WHERE
type=’PC’)) OR (maker in (SELECT maker FROM Product WHERE
type=’Laptop’)))
THEN »
ELSE maker
END AS t
FROM Product
GROUP BY maker,type) AS t1
ORDER BY maker, s

SELECT date, max(c) FROM
(SELECT date,count(*) AS c FROM Trip,
(SELECT trip_no,date FROM Pass_in_trip WHERE date>=’2003-04-01′ AND date Найти количество маршрутов, которые обслуживаются наибольшим числом рейсов.
Замечания.
1) A — B и B — A считать РАЗНЫМИ маршрутами.
2) Использовать только таблицу Trip

select count(*) from
(SELECT TOP 1 WITH TIES count(*) c, town_from, town_to from trip
group by town_from, town_to
order by c desc) as t

select count(*) from (
select TOP 1 WITH TIES sum(c) cc, c1, c2 from (
SELECT count(*) c, town_from c1, town_to c2 from trip
where town_from>=town_to
group by town_from, town_to
union all
SELECT count(*) c,town_to, town_from from trip
where town_to>town_from
group by town_from, town_to
) as t
group by c1,c2
order by cc desc
) as tt

with t as
(
select point, «date», inc, 0 AS «out» from income
union all
select point, «date», 0 AS inc, «out» from outcome
)
SELECT t.point, TO_CHAR ( t.»date», ‘DD/MM/YYYY’) AS day,
( select SUM(i.inc) from t i
where i.»date» Укажите сражения, в которых участвовало по меньшей мере три корабля одной и той же страны.

SELECT DISTINCT o.battle
FROM outcomes o
LEFT JOIN ships s ON s.name = o.ship
LEFT JOIN classes c ON o.ship = c.class OR s.class = c.class
WHERE c.country IS NOT NULL
GROUP BY c.country, o.battle
HAVING COUNT(o.ship) >= 3

SELECT p.maker
FROM product p
LEFT JOIN pc ON pc.model = p.model
WHERE p.type = ‘PC’
GROUP BY p.maker
HAVING COUNT(p.model) = COUNT(pc.model)

select TOP 1 WITH TIES name, c3 from passenger
join
(select c1, max(c3) c3 from
(
select pass_in_trip.ID_psg c1, Trip.ID_comp c2, count(*) c3 from pass_in_trip
join trip on trip.trip_no=pass_in_trip.trip_no
group by pass_in_trip.ID_psg, Trip.ID_comp
) as t
group by c1
having count(*)=1) as tt
on ID_psg=c1
order by c3 desc

SELECT DISTINCT c.country, b.name
FROM battles b, classes c
MINUS
SELECT c.country, o.battle
FROM outcomes o
LEFT JOIN ships s ON s.name = o.ship
LEFT JOIN classes c ON o.ship = c.class OR s.class = c.class
WHERE c.country IS NOT NULL
GROUP BY c.country, o.battle

SELECT c.country, c.class
FROM classes c
WHERE UPPER(c.country) = ‘RUSSIA’ AND EXISTS (
SELECT c.country, c.class
FROM classes c
WHERE UPPER(c.country) = ‘RUSSIA’ )
UNION ALL
SELECT c.country, c.class
FROM classes c
WHERE NOT EXISTS (SELECT c.country, c.class
FROM classes c
WHERE UPPER(c.country) = ‘RUSSIA’ )

select shipname,launched,batname
from
(select s.name as shipname,launched,b.name as batname,
row_number() over (partition by s.name order by «date») as num
from ships s,battles b
where to_char(«date»,’yyyy’)>=launched
and launched is not null)
where num = 1
union
(
select name,launched,(select name from battles
where «date» = (select max(«date») from battles)) as batname
from ships
where launched is null
)

Определить время, проведенное в полетах, для пассажиров, летавших всегда на разных местах. Вывод: имя пассажира, время в минутах.

WITH cte AS
(SELECT ROW_NUMBER() OVER (PARTITION BY ps.ID_psg,pit.place ORDER BY pit.date) AS rowNumber
,DATEDIFF (minute, time_out, DATEADD(DAY,IIF(time_in Определить дни, когда было выполнено максимальное число рейсов из
Ростова (‘Rostov’). Вывод: число рейсов, дата.

SELECT TOP 1 WITH TIES * FROM (
SELECT COUNT (DISTINCT P.trip_no) count, date
FROM Pass_in_trip P
JOIN Trip T ON T.trip_no = P.trip_no AND town_from = ‘Rostov’
GROUP BY P.trip_no, date) X
ORDER BY 1 DESC

Для каждого сражения определить первый и последний день
месяца,
в котором оно состоялось.
Вывод: сражение, первый день месяца, последний
день месяца.
Замечание: даты представить без времени в формате «yyyy-mm-dd».

SELECT name, REPLACE(CONVERT(CHAR(12), DATEADD(m, DATEDIFF(m,0,date),0), 102),’.’,’-‘) AS first_day,
REPLACE(CONVERT(CHAR(12), DATEADD(s,-1,DATEADD(m, DATEDIFF(m,0,date)+1,0)), 102),’.’,’-‘) AS last_day
FROM Battles

Определить пассажиров, которые больше других времени провели в полетах.
Вывод: имя пассажира, общее время в минутах, проведенное в полетах

SELECT Passenger.name, A.minutes
FROM (SELECT P.ID_psg,
SUM((DATEDIFF(minute, time_out, time_in) + 1440)%1440) AS minutes,
MAX(SUM((DATEDIFF(minute, time_out, time_in) + 1440)%1440)) OVER() AS MaxMinutes
FROM Pass_in_trip P JOIN
Trip AS T ON P.trip_no = T.trip_no
GROUP BY P.ID_psg
) AS A JOIN
Passenger ON Passenger.ID_psg = A.ID_psg
WHERE A.minutes = A.MaxMinutes

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

SELECT DISTINCT maker
FROM product
WHERE maker NOT IN (
SELECT maker
FROM product
WHERE type=’PC’ AND model NOT IN (
SELECT model
FROM PC))

Из таблицы Outcome получить все записи за тот месяц (месяцы), с учетом года, в котором суммарное значение расхода (out) было максимальным.

SELECT O.*
FROM outcome O
INNER JOIN
(
SELECT TOP 1 WITH TIES YEAR(date) AS Y, MONTH(date) AS M, SUM(out) AS ALL_TOTAL
FROM outcome
GROUP BY YEAR(date), MONTH(date)
ORDER BY ALL_TOTAL DESC
) R ON YEAR(O.date) = R.Y AND MONTH(O.date) = R.M

В наборе записей из таблицы PC, отсортированном по столбцу code (по возрастанию) найти среднее значение цены для каждой шестерки подряд идущих ПК.
Вывод: значение code, которое является первым в наборе из шести строк, среднее значение цены в наборе.

WITH CTE(code,price,number)
AS
(
SELECT PC.code,PC.price, number= ROW_NUMBER() OVER (ORDER BY PC.code)
FROM PC
)
SELECT CTE.code, AVG(C.price)
FROM CTE
JOIN CTE C ON (C.number-CTE.number) =0
GROUP BY CTE.number,CTE.code
HAVING COUNT(CTE.number)=6

Определить названия всех кораблей из таблицы Ships, которые удовлетворяют, по крайней мере, комбинации любых четырёх критериев из следующего списка:
numGuns = 8
bore = 15
displacement = 32000
type = bb
launched = 1915
class=Kongo
country=USA

SELECT name
FROM Ships AS s JOIN Classes AS cl1 ON s.class = cl1.class
WHERE
CASE WHEN numGuns = 8 THEN 1 ELSE 0 END +
CASE WHEN bore = 15 THEN 1 ELSE 0 END +
CASE WHEN displacement = 32000 THEN 1 ELSE 0 END +
CASE WHEN type = ‘bb’ THEN 1 ELSE 0 END +
CASE WHEN launched = 1915 THEN 1 ELSE 0 END +
CASE WHEN s.class = ‘Kongo’ THEN 1 ELSE 0 END +
CASE WHEN country = ‘USA’ THEN 1 ELSE 0 END > = 4

Для каждой компании подсчитать количество перевезенных пассажиров (если они были в этом месяце) по декадам апреля 2003. При этом учитывать только дату вылета.
Вывод: название компании, количество пассажиров за каждую декаду

SELECT C.name, A.N_1_10, A.N_11_21, A.N_21_30
FROM (SELECT T.ID_comp,
SUM(CASE WHEN DAY(P.date) 10 AND DAY(P.date) 20 THEN 1 ELSE 0 END) AS N_21_30
FROM Trip AS T JOIN
Pass_in_trip AS P ON T.trip_no = P.trip_no AND CONVERT(char(6), P.date, 112) = ‘200304’
GROUP BY T.ID_comp
) AS A JOIN
Company AS C ON A.ID_comp = C.ID_comp

Найти производителей, которые выпускают только принтеры или только PC.
При этом искомые производители PC должны выпускать не менее 3 моделей.

select maker
from product
group by maker
having count(distinct type) = 1 and
(min(type) = ‘pc’ or
(min(type) = ‘printer’ and count(model) > 2))

Для каждого производителя перечислить в алфавитном порядке с разделителем «/» все типы выпускаемой им продукции.
Вывод: maker, список типов продукции

SELECT maker,
CASE count(distinct type) when 2 then MIN(type) + ‘/’ + MAX(type)
when 1 then MAX(type)
when 3 then ‘Laptop/PC/Printer’ END
FROM Product
GROUP BY maker

Считая, что пункт самого первого вылета пассажира является местом жительства, найти не москвичей, которые прилетали в Москву более одного раза.
Вывод: имя пассажира, количество полетов в Москву

SELECT DISTINCT name, COUNT(town_to) Qty
FROM Trip tr JOIN Pass_in_trip pit ON tr.trip_no = pit.trip_no JOIN
Passenger psg ON pit.ID_psg = psg.ID_psg
WHERE town_to = ‘Moscow’ AND pit.ID_psg NOT IN(SELECT DISTINCT ID_psg
FROM Trip tr JOIN Pass_in_trip pit ON tr.trip_no = pit.trip_no
WHERE date+time_out = (SELECT MIN (date+time_out)
FROM Trip tr1 JOIN Pass_in_trip pit1 ON tr1.trip_no = pit1.trip_no
WHERE pit.ID_psg = pit1.ID_psg)
AND town_from = ‘Moscow’)
GROUP BY pit.ID_psg, name
HAVING COUNT(town_to) > 1

Среди тех, кто пользуется услугами только одной компании, определить имена разных пассажиров, летавших чаще других.
Вывести: имя пассажира, число полетов и название компании.

SELECT
(SELECT name FROM Passenger WHERE ID_psg = B.ID_psg) AS name,
B.trip_Qty,
(SELECT name FROM Company WHERE ID_comp = B.ID_comp) AS Company
FROM (SELECT P.ID_psg, MIN(T.ID_comp) AS ID_comp, COUNT(*) AS trip_Qty, MAX(COUNT(*)) OVER() AS Max_Qty
FROM Pass_in_trip AS P JOIN
Trip AS T ON P.trip_no = T.trip_no
GROUP BY P.ID_psg
HAVING MIN(T.ID_comp) = MAX(T.ID_comp)
) AS B
WHERE B.trip_Qty = B.Max_Qty

Найти производителей, у которых больше всего моделей в таблице Product, а также тех, у которых меньше всего моделей.
Вывод: maker, число моделей

select Maker , count(distinct model) Qty from Product
group by maker
having count(distinct model) > = ALL
(select count(distinct model) from Product
group by maker)
or
count(distinct model) Вывести все строки из таблицы Product, кроме трех строк с наименьшими номерами моделей и трех строк с наибольшими номерами моделей.

from Product

Используя таблицу Product, определить количество производителей, выпускающих по одной модели.

select count(maker)
from product
where maker in
(
Select maker from product
group by maker
having count(model) = 1
)

Выбрать все белые квадраты, которые окрашивались только из баллончиков,
пустых к настоящему времени. Вывести имя квадрата

SELECT Q_NAME
FROM utQ
WHERE Q_ID IN (SELECT DISTINCT B.B_Q_ID
FROM (SELECT B_Q_ID
FROM utB
GROUP BY B_Q_ID
HAVING SUM(B_VOL) = 765) AS B
WHERE B.B_Q_ID NOT IN (SELECT B_Q_ID
FROM utB
WHERE B_V_ID IN (SELECT B_V_ID
FROM utB
GROUP BY B_V_ID
HAVING SUM(B_VOL) Для каждой компании, перевозившей пассажиров, подсчитать время, которое провели в полете самолеты с пассажирами.
Вывод: название компании, время в минутах.

select c.name, sum(vr.vr)
from
(select distinct t.id_comp, pt.trip_no, pt.date,t.time_out,t.time_in,—pt.id_psg,
case
when DATEDIFF(mi, t.time_out,t.time_in)> 0 then DATEDIFF(mi, t.time_out,t.time_in)
when DATEDIFF(mi, t.time_out,t.time_in) Для семи последовательных дней, начиная от минимальной даты, когда из Ростова было совершено максимальное число рейсов, определить число рейсов из Ростова.
Вывод: дата, количество рейсов

SELECT DATEADD(day, S.Num, D.date) AS Dt,
(SELECT COUNT(DISTINCT P.trip_no)
FROM Pass_in_trip P
JOIN Trip T
ON P.trip_no = T.trip_no
AND T.town_from = ‘Rostov’
AND P.date = DATEADD(day, S.Num, D.date)) AS Qty
FROM (SELECT (3 * ( x — 1 ) + y — 1) AS Num
FROM (SELECT 1 AS x UNION ALL SELECT 2 UNION ALL SELECT 3) AS N1
CROSS JOIN (SELECT 1 AS y UNION ALL SELECT 2 UNION ALL SELECT 3) AS N2
WHERE (3 * ( x — 1 ) + y ) На основании информации из таблицы Pass_in_Trip, для каждой авиакомпании определить:
1) количество выполненных перелетов;
2) число использованных типов самолетов;
3) количество перевезенных различных пассажиров;
4) общее число перевезенных компанией пассажиров.
Вывод: Название компании, 1), 2), 3), 4).

SELECT name,
COUNT(DISTINCT CONVERT(CHAR(24),date)+CONVERT(CHAR(4),Trip.trip_no)),
COUNT(DISTINCT plane),
COUNT(DISTINCT ID_psg),
COUNT(*)
FROM Company,Pass_in_trip,Trip
WHERE Company.ID_comp=Trip.ID_comp and Trip.trip_no=Pass_in_trip.trip_no
GROUP BY Company.ID_comp,name

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

with r as (select v.v_name,
v.v_id,
count(case when v_color = ‘R’ then 1 end) over(partition by v_id) cnt_r,
count(case when v_color = ‘B’ then 1 end) over(partition by b_q_id) cnt_b
from utV v join utB b on v.v_id = b.b_v_id)
select v_name
from r
where cnt_r > 1
and cnt_b > 0
group by v_name

Отобрать из таблицы Laptop те строки, для которых выполняется следующее условие:
значения из столбцов speed, ram, price, screen возможно расположить таким образом, что каждое последующее значение будет превосходить предыдущее в 2 раза или более.
Замечание: все известные характеристики ноутбуков больше нуля.
Вывод: code, speed, ram, price, screen.

select code, speed, ram, price, screen
from laptop where exists (
select 1 x
from (
select v, rank()over(order by v) rn
from ( select cast(speed as float) sp, cast(ram as float) rm,
cast(price as float) pr, cast(screen as float) sc
)l unpivot(v for c in (sp, rm, pr, sc))u
)l pivot(max(v) for rn in ([1],[2],[3],[4]))p
where [1]*2 Вывести список ПК, для каждого из которых результат побитовой операции ИЛИ, примененной к двоичным представлениям скорости процессора и объема памяти, содержит последовательность из не менее четырех идущих подряд единичных битов.
Вывод: код модели, скорость процессора, объем памяти.

with CTE AS
(select
1 n, cast (0 as varchar(16)) bit_or,
code, speed, ram FROM PC
UNION ALL
select n*2,
cast (convert(bit,(speed|ram)&n) as varchar(1))+cast(bit_or as varchar(15))
, code, speed, ram
from CTE where n 0

Рассматриваются только таблицы Income_o и Outcome_o. Известно, что прихода/расхода денег в воскресенье не бывает.
Для каждой даты прихода денег на каждом из пунктов определить дату инкассации по следующим правилам:
1. Дата инкассации совпадает с датой прихода, если в таблице Outcome_o нет записи о выдаче денег в эту дату на этом пункте.
2. В противном случае — первая возможная дата после даты прихода денег, которая не является воскресеньем и в Outcome_o не отмечена выдача денег сдатчикам вторсырья в эту дату на этом пункте.
Вывод: пункт, дата прихода денег, дата инкассации.

select point,
«date» income_date,
«date» + nvl(
min(case when diff > cnt then cnt else null end),
max(cnt)+1
) incass_date
from (select i.point,
i.»date»,
(trunc(o.»date») — trunc(i.»date»)) diff, — разница дней
— количество запрещенных для инкассации дней после прихода и до текущего запрещенного дня
count(1) over (partition by i.point, i.»date» order by o.»date» rows between unbounded preceding and current row)-1 cnt
from income_o i
join (select point, «date», 1 disabled from outcome_o
union
select point, trunc(«date»+7,’DAY’), 1 disabled from income_o) o
on i.point = o.point
where o.»date» > = i.»date»)
group by point, «date»

Написать запрос, который выводит все операции прихода и расхода из таблиц Income и Outcome в следующем виде:
дата, порядковый номер записи за эту дату, пункт прихода, сумма прихода, пункт расхода, сумма расхода.
При этом все операции прихода по всем пунктам, совершённые в течение одного дня, упорядочены по полю code, и так же все операции расхода упорядочены по полю code.
В случае, если операций прихода/расхода за один день было не равное количество, выводить NULL в соответствующих колонках на месте недостающих операций.

Select distinct A.date , A.R, B.point, B.inc, C.point, C.out
From (Select distinct date, ROW_Number() OVER(PARTITION BY date ORDER BY code asc) as R From Income
Union Select distinct date, ROW_Number() OVER(PARTITION BY date ORDER BY code asc) From Outcome) A
LEFT Join (Select date, point, inc
, ROW_Number() OVER(PARTITION BY date ORDER BY code asc) as RI FROM Income
) B on B.date=A.date and B.RI=A.R
LEFT Join (Select date, point, out
, ROW_Number() OVER(PARTITION BY date ORDER BY code asc) as RO FROM Outcome
) C on C.date=A.date and C.RO=A.R

Таблица Printer сортируется по возрастанию поля code.
Упорядоченные строки составляют группы: первая группа начинается с первой строки, каждая строка со значением color=’n’ начинает новую группу, группы строк не перекрываются.
Для каждой группы определить: наибольшее значение поля model (max_model), количество уникальных типов принтеров (distinct_types_cou) и среднюю цену (avg_price).
Для всех строк таблицы вывести: code, model, color, type, price, max_model, distinct_types_cou, avg_price.

SELECT code, model, color, type, price,
MAX(model)OVER(PARTITION BY Grp)max_model,
MAX(CASE type WHEN’Laser’THEN 1 ELSE 0 END)OVER(PARTITION BY Grp)+
MAX(CASE type WHEN’Matrix’THEN 1 ELSE 0 END)OVER(PARTITION BY Grp)+
MAX(CASE type WHEN’Jet’THEN 1 ELSE 0 END)OVER(PARTITION BY Grp)distinct_types,
AVG(price)OVER(PARTITION BY Grp)
FROM(
SELECT *,
CASE color WHEN’n’THEN 0 ELSE ROW_NUMBER()OVER(ORDER BY code)END+
CASE color WHEN’n’THEN 1 ELSE-1 END*ROW_NUMBER()OVER(PARTITION BY color ORDER BY code)Grp
FROM Printer
)T

Определить имена разных пассажиров, которые летали
только между двумя городами (туда и/или обратно).

select name from passenger
where id_psg in
(
select id_psg from trip t,pass_in_trip pit
where t.trip_no=pit.trip_no
group by id_psg
having count(distinct case when town_from Выбрать три наименьших и три наибольших номера рейса. Вывести их в шести столбцах одной строки, расположив в порядке от наименьшего к наибольшему.
Замечание: считать, что таблица Trip содержит не менее шести строк.

Select min(t.trip_no),min(tt.trip_no),min(ttt.trip_no),max(t.trip_no),max(tt.trip_no),max(ttt.trip_no)
from trip t, trip tt, trip ttt
where tt.trip_no > t.trip_no and ttt.trip_no > tt.trip_no

Для каждого класса крейсеров, число орудий которого известно, пронумеровать (последовательно от единицы) все орудия.
Вывод: имя класса, номер орудия в формате ‘bc-N’.

with a as(
select x.class,x.numGuns,row_number()over(partition by x.class order by x.numguns)n
from Classes x,classes y
where x.type=’bc’)
select distinct class,’bc-‘+cast(n as char(2))
from a where numguns> =n

Статистики Алиса, Белла, Вика и Галина нумеруют строки у таблицы Product.
Все четверо упорядочили строки таблицы по возрастанию названий производителей.
Алиса присваивает новый номер каждой строке, строки одного производителя она упорядочивает по номеру модели.
Трое остальных присваивают один и тот же номер всем строкам одного производителя.
Белла присваивает номера начиная с единицы, каждый следующий производитель увеличивает номер на 1.
У Вики каждый следующий производитель получает такой же номер, какой получила бы первая модель этого производителя у Алисы.
Галина присваивает каждому следующему производителю тот же номер, который получила бы его последняя модель у Алисы.
Вывести: maker, model, номера строк получившиеся у Алисы, Беллы, Вики и Галины соответственно.

select maker, model,
row_number() over (order by maker, model),
dense_rank() over (order by maker),
rank() over (order by maker),
count(*) over (order by maker)
from product

Пусть v1, v2, v3, v4, . представляет последовательность вещественных чисел — объемов окрасок b_vol, упорядоченных по возрастанию b_datetime, b_q_id, b_v_id.
Найти преобразованную последовательность P1=v1, P2=v1/v2, P3=v1/v2*v3, P4=v1/v2*v3/v4, . где каждый следующий член получается из предыдущего умножением на vi (при нечетных i) или делением на vi (при четных i).
Результаты представить в виде b_datetime, b_q_id, b_v_id, b_vol, Pi, где Pi — член последовательности, соответствующий номеру записи i. Вывести Pi с 8-ю знаками после запятой.

with a as(
select *,row_number()over(order by b_datetime,b_q_id,b_v_id) n from utb)
select b_datetime,b_q_id,b_v_id,b_vol,
cast(exp(sm1)/exp(sm2) as numeric(12,8))k
from a x
cross apply
(select sum( iif(n%2<> 0,log(b_vol),0)) sm1,sum( iif(n%2=0,log(b_vol),0)) sm2 from a where n Для пятого по счету пассажира из числа вылетевших из Ростова в апреле 2003 года определить компанию, номер рейса и дату вылета.
Замечание. Считать, что два рейса одновременно вылететь из Ростова не могут.

Select name, trip_no, date
from(
select row_number() over(order by date+time_out,ID_psg) rn,name,Trip.trip_no,date
from Company,Pass_in_trip,Trip
where Company.ID_comp=Trip.ID_comp and Trip.trip_no=Pass_in_trip.trip_no
and town_from=’Rostov’ and year(date)=2003 and month(date)=4)_
where rn=5

Реставрация экспонатов секции «Треугольники» музея ПФАН проводилась согласно техническому заданию. Для каждой записи таблицы utb малярами подкрашивалась сторона любой фигуры, если длина этой стороны равнялась b_vol.
Найти окрашенные со всех сторон треугольники, кроме равносторонних, равнобедренных и тупоугольных.
Для каждого треугольника (но без повторений) вывести три значения X, Y, Z, где X — меньшая, Y — средняя, а Z — большая сторона.

SELECT DISTINCT b1.B_VOL, b2.b_vol, b3.b_vol FROM utb b1, utb b2, utb b3
WHERE b1.B_VOL SQRT( SQUARE(b1.B_VOL) + SQUARE(b2.B_VOL)))

Вывести:
1. Названия всех квадратов черного или белого цвета.
2. Общее количество белых квадратов.
3. Общее количество черных квадратов.

SELECT A.Q_NAME AS q_name,
A.Whites AS Whites,
A.Cnt — A.Whites AS Blacks
FROM (SELECT Q.Q_ID,
Q.Q_NAME,
(SUM(SUM(B.B_VOL)) OVER())/765 AS Whites,
COUNT(*) OVER() AS Cnt
FROM utQ AS Q
LEFT JOIN utB AS B
ON Q.Q_ID = B.B_Q_ID
GROUP BY Q.Q_ID,
Q.Q_NAME
HAVING SUM(B.B_VOL) = 765
OR SUM(B.B_VOL) IS NULL) AS A

Определить имена разных пассажиров, когда-либо летевших рейсом, который вылетел в субботу, а приземлился в воскресенье.

select name from passenger where id_psg in
(select id_psg
from pass_in_trip pit join trip t on pit.trip_no = t.trip_no
where time_in Найти НЕ белые и НЕ черные квадраты, которые окрашены разными цветами в пропорции 1:1:1. Вывод: имя квадрата, количество краски одного цвета

select B_Q_ID, sum(vol)/3 vol
from
(select B_Q_ID, V_COLOR, sum(B_VOL) vol
from utB, utV
where B_V_ID=V_ID
group by B_Q_ID, V_COLOR
) z
group by B_Q_ID
having count(v_color)=3
and sum(vol) Какое максимальное количество черных квадратов можно было бы окрасить в белый цвет
оставшейся краской

select min(Qty) from (select SUM(RemainPaint)/255 Qty FROM (select V_COLOR, V_ID,
CASE
WHEN SUM(B_VOL) IS NULL
THEN 255
ELSE 255-SUM(B_VOL)
END RemainPaint
from utB right join utV on B_V_ID = V_ID
group by V_COLOR, V_ID
) R
group by V_COLOR
) Q

Сколько каждой краски понадобится, чтобы докрасить все Не белые квадраты до белого цвета.
Вывод: количество каждой краски в порядке (R,G,B)

SELECT sum(255-ISNULL ([R],0) ) R , sum(255-isnull([G],0)) G, sum(255-isnull([B],0)) B
FROM
(
/*merging all tables to find paint filling and color for all squares*/
select ISNULL(B_Q_ID, Q_ID) ID, V_COLOR, B_VOL Vol from
utB RIGHT JOIN utQ on B_Q_ID=Q_ID
LEFT JOIN utV on B_V_ID=V_ID
) as SourceT
PIVOT
(
/*rotating table and calculating each paint volume for each square*/
SUM(Vol) For V_COLOR IN ([R], [G], [B])
) Pvt
/*excluding white squares*/
where ISNULL ([R],0) + isnull([G],0) + isnull([B],0) Определить имена разных пассажиров, которым чаще других доводилось лететь на одном и том же месте. Вывод: имя и количество полетов на одном и том же месте.

WITH b AS
(SELECT ID_psg, COUNT(*) as cnt FROM Pass_In_Trip GROUP BY ID_psg, place),
b1 AS
(SELECT DISTINCT ID_psg, cnt FROM b WHERE cnt =(SELECT MAX(cnt) FROM b))
SELECT name, cnt FROM b1 JOIN Passenger p ON (b1.ID_psg = p.ID_psg)

Рассмотрим равнобочные трапеции, в каждую из которых можно вписать касающуюся всех сторон окружность. Кроме того, каждая сторона имеет целочисленную длину из множества значений b_vol.
Вывести результат в 4 колонки: Up, Down, Side, Rad. Здесь Up — меньшее основание, Down — большее основание, Side — длины боковых сторон, Rad – радиус вписанной окружности (с 2-мя знаками после запятой).

select distinct Up=u.b_vol, Down=d.b_vol, Side=s.b_vol,
Rad=cast(POWER((POWER(s.b_vol,2)-POWER((1.*d.b_vol-1.*u.b_vol)/2,2)),1./2.)/2 as dec(15,2))
from utB u, utB d, utB s
where u.b_vol Считая, что каждая окраска длится ровно секунду, определить непрерывные интервалы времени с длительностью более 1 секунды из таблицы utB.
Вывод: дата первой окраски в интервале, дата последней окраски в интервале.

SELECT MIN(D)start, MAX(D)finish
FROM
(
SELECT D, SUM(F)OVER(ORDER BY D ROWS UNBOUNDED PRECEDING)F
FROM
(
SELECT B_DATETIME D, IIF(IsNull(DATEDIFF(second, LAG(B_DATETIME)OVER(ORDER BY B_DATETIME), B_DATETIME),0) 0

По таблице Classes для каждой страны найти максимальное значение среди трех выражений:
numguns*5000, bore*3000, displacement.
Вывод в три столбца:
— страна;
— максимальное значение;
— слово `numguns` — если максимум достигается для numguns*5000, слово `bore` — если максимум достигается для bore*3000, слово `displacement` — если максимум достигается для displacement.
Замечание. Если максимум достигается для нескольких выражений, выводить каждое из них отдельной строкой.

Select top 1 with ties country, x, n
from classes
cross apply(values(numguns*5000,’numguns’)
,(bore*3000,’bore’)
,(displacement,’displacement’))V(x,n)
group by country, x, n
order by rank()over(partition by country order by x desc)

Выборы Директора музея ПФАН проводятся только в високосный год, в первый вторник апреля после первого понедельника апреля.
Для каждой даты из таблицы Battles определить дату ближайших (после этой даты) выборов Директора музея ПФАН.
Вывод: сражение, дата сражения, дата выборов. Даты выводить в формате «yyyy-mm-dd».

Select name, convert(char(10),date,120) as battle_dt
,convert(char(10),MIN(Dateadd(dd,1,dt)),120) as election_dt
From
(Select name, date, Dateadd(yy,p,Dateadd(dd,n,Dateadd(mm,3,dateadd(yy,datediff(yy,0,date),0)))) as dt
From Battles
,(values(0),(1),(2),(3),(4),(5),(6),(7),(8)) T(p)
,(values(0),(1),(2),(3),(4),(5),(6)) W(n) ) X
Where date 0 or Year(dt)%400=0)
and DATEPART(dw,dt)=DATEPART(dw,’20140106′)
GROUP BY name, date

Сгруппировать все окраски по дням, месяцам и годам. Идентификатор каждой группы должен иметь вид «yyyy» для года, «yyyy-mm» для месяца и «yyyy-mm-dd» для дня.
Вывести только те группы, в которых количество различных моментов времени (b_datetime), когда выполнялась окраска, более 10.
Вывод: идентификатор группы, суммарное количество потраченной краски.

select to_char(trunc(b.b_datetime,’year’),’yyyy’) grp, sum(b.b_vol) qnt
from utB b
group by to_char(trunc(b.b_datetime,’year’),’yyyy’)
having count(distinct b.b_datetime) > 10
union
select to_char(trunc(b.b_datetime,’MM’),’yyyy-mm’) grp, sum(b.b_vol) qnt
from utB b
group by to_char(trunc(b.b_datetime,’MM’),’yyyy-mm’)
having count(distinct b.b_datetime) > 10
union
select to_char(trunc(b.b_datetime,’dd’),’yyyy-mm-dd’) grp, sum(b.b_vol) qnt
from utB b
group by to_char(trunc(b.b_datetime,’dd’),’yyyy-mm-dd’)
having count(distinct b.b_datetime) > 10

Для авиакомпаний, самолеты которой выполнили хотя бы один рейс, вычислить с точностью до двух десятичных знаков средние величины времени нахождения самолетов в воздухе (в минутах). Также рассчитать указанные характеристики по всем летавшим самолетам (использовать слово ‘TOTAL’).
Вывод: компания, среднее арифметическое, среднее геометрическое, среднее квадратичное, среднее гармоническое.