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

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

Задание: 1 (Serge I: 2002-09-30)

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

SELECT model, speed, hd FROM PC WHERE price < 500

Задание: 2 (Serge I: 2002-09-21)

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

SELECT maker FROM Product WHERE type = 'Printer' GROUP BY maker

Задание: 3 (Serge I: 2002-09-30)

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

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

Задание: 4 (Serge I: 2002-09-21)

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

SELECT * FROM Printer WHERE color = 'y'

Задание: 5 (Serge I: 2002-09-30)

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

SELECT model,speed,hd FROM PC WHERE ( cd = '12x' OR cd = '24x' ) AND price < 600

Задание: 6 (Serge I: 2002-10-28)

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

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

Задание: 7 (Serge I: 2002-11-02)

Найдите номера моделей и цены всех продуктов (любого типа), выпущенных производителем B (латинская буква).

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';

Задание: 8 (Serge I: 2003-02-03)

Найдите производителя, выпускающего ПК, но не ПК-блокноты.

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

Задание: 9 (Serge I: 2002-11-02)

Найдите производителей ПК с процессором не менее 450 Мгц. Вывести: Maker

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

Задание: 10 (Serge I: 2002-09-23)

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

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

Задание: 11 (Serge I: 2002-11-02)

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

SELECT AVG(speed) FROM pc

Задание: 12 (Serge I: 2002-11-02)

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

SELECT AVG(speed)
FROM laptop
WHERE price > 1000

Задание: 13 (Serge I: 2002-11-02)

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

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

Задание: 14 (Serge I: 2012-04-20)

Найти производителей, которые выпускают более одной модели, при этом все выпускаемые производителем модели являются продуктами одного типа. Вывести: maker, type

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

Задание: 15 (Serge I: 2003-02-03)

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

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

Задание: 16 (Serge I: 2003-02-03)

Найдите пары моделей PC, имеющих одинаковые скорость и RAM. В результате каждая пара указывается только один раз, т.е. (i,j), но не (j,i), Порядок вывода: модель с большим номером, 
модель с меньшим номером, скорость и RAM.

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

Задание: 17 (Serge I: 2003-02-03)

Найдите модели ПК-блокнотов, скорость которых меньше скорости любого из ПК. 
Вывести: type, model, speed

select distinct p.type,p.model,l.speed
from laptop l
join product p on l.model=p.model
where l.speed<(select min(speed)
                      from pc) 

Задание: 18 (Serge I: 2003-02-03)

Найдите производителей самых дешевых цветных принтеров. Вывести: 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'
) 

Задание: 19 (Serge I: 2003-02-13)

Для каждого производителя, имеющего модели в таблице Laptop, найдите средний размер экрана выпускаемых им ПК-блокнотов. 
Вывести: maker, средний размер экрана.

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

Задание: 20 (Serge I: 2003-02-13)

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

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

Задание: 21 (Serge I: 2003-02-13)

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

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

Задание: 22 (Serge I: 2003-02-13)

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

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

Задание: 23 (Serge I: 2003-02-14)

Найдите производителей, которые производили бы как ПК со скоростью не менее 750 МГц, так и ПК-блокноты со скоростью не менее 750 МГц. 
Вывести: Maker

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 )

Задание: 24 (Serge I: 2003-02-03)

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

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
 )

Задание: 25 (Serge I: 2003-02-14)

Найдите производителей принтеров, которые производят ПК с наименьшим объемом RAM и с самым быстрым процессором среди всех ПК, имеющих наименьший объем RAM. Вывести: Maker

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'
)

Задание: 26 (Serge I: 2003-02-14)

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

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

Задание: 27 (Serge I: 2003-02-03)

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

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

Задание: 28 (Serge I: 2003-02-03)

Найдите средний размер диска ПК (одно значение для всех) тех производителей, которые выпускают и принтеры. Вывести: средний размер HD

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')

Задание: 29 (Serge I: 2003-02-14)

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

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

Задание: 30 (Serge I: 2003-02-14)

В предположении, что приход и расход денег на каждом пункте приема фиксируется произвольное число раз (первичным ключом в таблицах является столбец 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 (Serge I: 2002-10-22)

Для классов кораблей, калибр орудий которых не менее 16 дюймов, укажите класс и страну.

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

Задание: 32 (Serge I: 2003-02-17)

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

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

Задание: 33 (Serge I: 2002-11-02)

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

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

Задание: 34 (Serge I: 2002-11-04)

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

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

Задание: 35 (qwrqwr: 2012-11-23)

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

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

Задание: 36 (Serge I: 2003-02-17)

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

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

Задание: 37 (Serge I: 2003-02-17)

Найдите классы, в которые входит только один корабль из базы данных (учесть также корабли в Outcomes).

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

Задание: 38 (Serge I: 2003-02-19)

Найдите страны, имевшие когда-либо классы обычных боевых кораблей ('bb') и имевшие когда-либо классы крейсеров ('bc').

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

Задание: 39 (Serge I: 2003-02-14)

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

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 < a.date AND b.result = 'damaged')

Задание: 40 (Serge I: 2002-11-05)

Найдите класс, имя и страну для кораблей из таблицы 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

Задание: 40 (Serge I: 2002-11-05)

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

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

Задание: 41 (Serge I: 2008-08-30)

Для ПК с максимальным кодом из таблицы 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

Задание: 42 (Serge I: 2002-11-05)

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

SELECT ship, battle FROM Outcomes WHERE result = 'sunk'

Задание: 43 (qwrqwr: 2011-10-28)

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

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

Задание: 44 (Serge I: 2002-12-04)

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

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

Задание: 45 (Serge I: 2002-12-04)

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

select name from ships
where name like '% % %'
union
select ship from outcomes
where ship like '% % %'

Задание: 46 (Serge I: 2003-02-14)

Для каждого корабля, участвовавшего в сражении при Гвадалканале (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'

Задание: 47 (Serge I: 2011-02-11)

Пронумеровать строки из таблицы 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

Задание: 48 (Serge I: 2003-02-16)

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

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

Задание: 49 (Serge I: 2003-02-17)

Найдите названия кораблей с орудиями калибра 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

Задание: 50 (Serge I: 2002-11-05)

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

select distinct battle from outcomes
where ship in (select name
               from ships
               where class = 'kongo')

Задание: 51 (Serge I: 2003-02-17)

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

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

Задание: 52 (qwrqwr: 2010-04-23)

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

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 < 19 OR c.bore IS NULL)
AND (displacement <= 65000 OR c.displacement IS NULL)
AND c.type = 'bb'

Задание: 53 (Serge I: 2002-11-05)

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

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

Задание: 54 (Serge I: 2003-02-14)

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

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;

Задание: 55 (Serge I: 2003-02-16)

Для каждого класса определите год, когда был спущен на воду первый корабль этого класса. Если год спуска на воду головного корабля неизвестен, определите минимальный год спуска на воду кораблей этого класса. Вывести: класс, год.

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

Задание: 56 (Serge I: 2003-02-16)

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

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

Задание: 57 (Serge I: 2003-02-14)

Для классов, имеющих потери в виде потопленных кораблей и не менее 3 кораблей в базе данных, вывести имя класса и число потопленных кораблей.

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

Задание: 58 (Serge I: 2009-11-13)

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

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

Задание: 59 (Serge I: 2003-02-15)

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

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

Задание: 60 (Serge I: 2003-02-15)

Посчитать остаток денежных средств на начало дня 15/04/01 на каждом пункте приема для базы данных с отчетностью не чаще одного раза в день. Вывод: пункт, остаток. 
Замечание. Не учитывать пункты, информации о которых нет до указанной даты.

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<'2001-04-15'
group by point) as t1
left join
(SELECT point o1, sum(out) o2 FROM outcome_o
where date<'2001-04-15'
group by point) as t2
on c1=o1

Задание: 61 (Serge I: 2003-02-14)

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

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

UNION

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

Задание: 62 (Serge I: 2003-02-15)

Посчитать остаток денежных средств на всех пунктах приема на начало дня 15/04/01 для базы данных с отчетностью не чаще одного раза в день.

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 

Задание: 63 (Serge I: 2003-04-08)

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

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

Задание: 64 (Serge I: 2010-06-04)

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

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

Задание: 65 (Serge I: 2009-08-24)

Пронумеровать уникальные пары {maker, type} из Product, упорядочив их следующим образом: 
- имя производителя (maker) по возрастанию; 
- тип продукта (type) в порядке PC, Laptop, Printer. 
Если некий производитель выпускает несколько типов продукции, то выводить его имя только в первой строке; 
остальные строки для ЭТОГО производителя должны содержать пустую строку символов ().

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

Задание: 66 (Serge I: 2003-04-09)

Для всех дней в интервале с 01/04/2003 по 07/04/2003 определить число рейсов из Rostov. 
Вывод: дата, количество рейсов

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<='2003-04-07' GROUP BY trip_no, date) AS t1
WHERE Trip.trip_no=t1.trip_no AND town_from='Rostov'
GROUP BY date
UNION ALL
SELECT '2003-04-01',0
UNION ALL
SELECT '2003-04-02',0
UNION ALL
SELECT '2003-04-03',0
UNION ALL
SELECT '2003-04-04',0
UNION ALL
SELECT '2003-04-05',0
UNION ALL
SELECT '2003-04-06',0
UNION ALL
SELECT '2003-04-07',0) AS t2
GROUP BY date

Задание: 67 (Serge I: 2010-03-27)

Найти количество маршрутов, которые обслуживаются наибольшим числом рейсов. 
Замечания. 
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

Задание: 68 (Serge I: 2010-03-27)

Найти количество маршрутов, которые обслуживаются наибольшим числом рейсов. 
Замечания. 
1) A - B и B - A считать ОДНИМ И ТЕМ ЖЕ маршрутом. 
2) Использовать только таблицу Trip

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

Задание: 69 (Serge I: 2011-01-06)

По таблицам Income и Outcome для каждого пункта приема найти остатки денежных средств на конец каждого дня, 
в который выполнялись операции по приходу и/или расходу на данном пункте. 
Учесть при этом, что деньги не изымаются, а остатки/задолженность переходят на следующий день. 
Вывод: пункт приема, день в формате "dd/mm/yyyy", остатки/задолженность на конец этого дня.

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" <= t."date" and i.point = t.point )
-
( select SUM(i."out") from t i
  where i."date" <= t."date" and i.point = t.point ) AS rem
from t
group by t.point, t."date"

Задание: 70 (Serge I: 2003-02-14)

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

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

Задание: 71 (Serge I: 2008-02-23)

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

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)

Задание: 72 (Serge I: 2003-04-29)

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

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

Задание: 73 (Serge I: 2009-04-17)

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

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

Задание: 74 (dorin_larsen: 2007-03-23)

Вывести классы всех кораблей России (Russia). Если в базе данных нет классов кораблей России, вывести классы для всех имеющихся в БД стран. 
Вывод: страна, класс

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' )

Задание: 75 (Serge I: 2009-04-17)

Для каждого корабля из таблицы Ships указать название ближайшего по времени сражения из таблицы Battles, 
в котором корабль мог бы участвовать после спуска на воду. Если год спуска на воду неизвестен, взять последнее по времени сражение. 
Если нет сражения, произошедшего после спуска на воду корабля, вывести NULL вместо названия сражения. 
Замечание. Считать, что корабль может участвовать в сражении, которое произошло в год спуска на воду корабля. 
Вывод: имя корабля, год спуска на воду, название сражения

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
)

Задание: 76 (Serge I: 2003-08-28)

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

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<time_out,1,0),time_in)) AS timeFlight, ps.Id_psg, ps.name
FROM Pass_in_trip pit LEFT JOIN trip tr ON pit.trip_no = tr.trip_no
LEFT JOIN Passenger ps ON ps.ID_psg = pit.ID_psg -- Все рейсы
)
SELECT MAX(cte.name),SUM(timeFlight) FROM cte
GROUP BY cte.ID_psg
HAVING MAX(rowNumber) = 1

Задание: 77 (Serge I: 2003-04-09)

Определить дни, когда было выполнено максимальное число рейсов из 
Ростова ('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

Задание: 78 (Serge I: 2005-01-19)

Для каждого сражения определить первый и последний день 
месяца, 
в котором оно состоялось. 
Вывод: сражение, первый день месяца, последний 
день месяца. 
Замечание: даты представить без времени в формате "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

Задание: 79 (Serge I: 2003-04-29)

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

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

Задание: 80 (Baser: 2011-11-11)

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

Задание: 81 (Serge I: 2011-11-25)

Из таблицы 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

Задание: 82 (Serge I: 2011-10-08)

В наборе записей из таблицы 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)<6 AND (C.number-CTE.number)> =0
GROUP BY CTE.number,CTE.code
HAVING COUNT(CTE.number)=6

Задание: 83 (dorin_larsen: 2006-03-14)

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

Задание: 84 (Serge I: 2003-06-05)

Для каждой компании подсчитать количество перевезенных пассажиров (если они были в этом месяце) по декадам апреля 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) < 11 THEN 1 ELSE 0 END) AS N_1_10,
       SUM(CASE WHEN (DAY(P.date) > 10 AND DAY(P.date) < 21) THEN 1 ELSE 0 END) AS N_11_21,
       SUM(CASE WHEN 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

Задание: 85 (Serge I: 2012-03-16)

Найти производителей, которые выпускают только принтеры или только 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))

Задание: 86 (Serge I: 2012-04-20)

Для каждого производителя перечислить в алфавитном порядке с разделителем "/" все типы выпускаемой им продукции. 
Вывод: 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

Задание: 87 (Serge I: 2003-08-28)

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

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

Задание: 88 (Serge I: 2003-04-29)

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

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

Задание: 89 (Serge I: 2012-05-04)

Найти производителей, у которых больше всего моделей в таблице 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) <= ALL
(select count(distinct model) from Product
group by maker)

Задание: 90 (Serge I: 2012-05-04)

Вывести все строки из таблицы Product, кроме трех строк с наименьшими номерами моделей и трех строк с наибольшими номерами моделей. 

Select maker, model, type from
(
Select
row_number() over (order by model) p1,
row_number() over (order by model DESC) p2,

• from Product

) t1
where p1 > 3 and p2 > 3

Задание: 91 (Serge I: 2012-05-04)

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

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

Задание: 92 (ZrenBy: 2003-09-01)

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

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) < 255)))

Задание: 93 (Serge I: 2003-06-05)

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

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)<=0 then DATEDIFF(mi, t.time_out,t.time_in+1)
end vr
from pass_in_trip pt left join trip t on pt.trip_no=t.trip_no
) vr left join company c on vr.id_comp=c.id_comp
group by c.name

Задание: 94 (Serge I: 2003-04-09)

Для семи последовательных дней, начиная от минимальной даты, когда из Ростова было совершено максимальное число рейсов, определить число рейсов из Ростова. 
Вывод: дата, количество рейсов 

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 ) < 8) AS S,
       (SELECT MIN(A.date) AS date
        FROM (SELECT P.date,
                       COUNT(DISTINCT P.trip_no) AS Qty,
                       MAX(COUNT(DISTINCT P.trip_no)) OVER() AS M_Qty
                FROM Pass_in_trip AS P
                       JOIN Trip AS T
                         ON P.trip_no = T.trip_no
                            AND T.town_from = 'Rostov'
                GROUP BY P.date) AS A
        WHERE A.Qty = A.M_Qty) AS D

Задание: 95 (qwrqwr: 2013-02-08)

На основании информации из таблицы 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

Задание: 96 (ZrenBy: 2003-09-01)

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

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

Задание: 97 (qwrqwr: 2013-02-15)

Отобрать из таблицы 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 <= [2] and [2]*2 <= [3] and [3]*2 <= [4]
)

Задание: 98 (qwrqwr: 2010-04-26)

Вывести список ПК, для каждого из которых результат побитовой операции ИЛИ, примененной к двоичным представлениям скорости процессора и объема памяти, содержит последовательность из не менее четырех идущих подряд единичных битов. 
Вывод: код модели, скорость процессора, объем памяти. 

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 < 65536
)
select code, speed, ram from CTE
where n = 65536
and CHARINDEX('1111', bit_or )> 0

Задание: 99 (qwrqwr: 2013-03-01)

Рассматриваются только таблицы 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"

Задание: 100 ($erges: 2009-06-05)

Написать запрос, который выводит все операции прихода и расхода из таблиц 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

Задание: 101 (qwrqwr: 2013-03-29)

Таблица 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

Задание: 102 (Serge I: 2003-04-29)

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

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<=town_to then town_from+town_to else town_to+town_from end)=1
)

Задание: 103 (qwrqwr: 2013-05-17)

Выбрать три наименьших и три наибольших номера рейса. Вывести их в шести столбцах одной строки, расположив в порядке от наименьшего к наибольшему. 
Замечание: считать, что таблица 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

Задание: 104 (Serge I: 2013-07-19)

Для каждого класса крейсеров, число орудий которого известно, пронумеровать (последовательно от единицы) все орудия. 
Вывод: имя класса, номер орудия в формате '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

Задание: 105 (qwrqwr: 2013-09-11)

Статистики Алиса, Белла, Вика и Галина нумеруют строки у таблицы 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

Задание: 106 (Baser: 2013-09-06)

Пусть 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<=x.n)y

Задание: 107 (VIG: 2003-09-01)

Для пятого по счету пассажира из числа вылетевших из Ростова в апреле 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

Задание: 108 (Baser: 2013-10-16)

Реставрация экспонатов секции "Треугольники" музея ПФАН проводилась согласно техническому заданию. Для каждой записи таблицы 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 < b2.B_VOL AND b2.B_VOL < b3.B_VOL
AND NOT ( b3.B_VOL > SQRT( SQUARE(b1.B_VOL) + SQUARE(b2.B_VOL)))

Задание: 109 (qwrqwr: 2011-01-13)

Вывести: 
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

Задание: 110 (Serge I: 2003-12-24)

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

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 < time_out and datepart(dw, date) = 7
 )

Задание: 111 (Serge I: 2003-12-24)

Найти НЕ белые и НЕ черные квадраты, которые окрашены разными цветами в пропорции 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)<765
      and sum(vol) % 3=0

Задание: 112 (Serge I: 2003-12-24)

Какое максимальное количество черных квадратов можно было бы окрасить в белый цвет 
оставшейся краской 

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

Задание: 113 (Serge I: 2003-12-24)

Сколько каждой краски понадобится, чтобы докрасить все Не белые квадраты до белого цвета. 
Вывод: количество каждой краски в порядке (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) <765

Задание: 114 (Serge I: 2003-04-08)

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

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)

Задание: 115 (Baser: 2013-11-01)

Рассмотрим равнобочные трапеции, в каждую из которых можно вписать касающуюся всех сторон окружность. Кроме того, каждая сторона имеет целочисленную длину из множества значений 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<d.b_vol and 1.*u.b_vol+1.*d.b_vol=2.*s.b_vol

Задание: 116 (Velmont: 2013-11-19)

Считая, что каждая окраска длится ровно секунду, определить непрерывные интервалы времени с длительностью более 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)<=1,0,1)F
FROM utB
)q
)q
GROUP BY F
HAVING DATEDIFF(second,MIN(D),MAX(D))> 0

Задание: 117 (Serge I: 2013-11-29)

По таблице 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)

Задание: 118 (qwrqwr: 2013-12-11)

Выборы Директора музея ПФАН проводятся только в високосный год, в первый вторник апреля после первого понедельника апреля. 
Для каждой даты из таблицы 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<=dt and (Year(dt)%4=0 and Year(dt)%100> 0 or Year(dt)%400=0)
and DATEPART(dw,dt)=DATEPART(dw,'20140106')
GROUP BY name, date

Задание: 119 ($erges: 2008-04-25)

Сгруппировать все окраски по дням, месяцам и годам. Идентификатор каждой группы должен иметь вид "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

Задание: 120 (mslava: 2004-01-05)

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

With t as
(Select ID_comp, convert(numeric(18,2), Case when time_in > = time_out
    Then datediff(minute, time_out, time_in)
    Else datediff(minute, time_out, dateadd(day, 1, time_in))
   End) as trmin
From (Select trip_no
 From Pass_in_trip
 Group by trip_no, [date]) pt join Trip t on pt.trip_no = t.trip_no
)

Select Coalesce(c.name, 'TOTAL'), A_mean, G_mean, Q_mean, H_mean
From (
 Select Id_comp ,
  convert(numeric(18,2), avg(trmin)) A_mean,
  convert(numeric(18,2), Exp(avg(Log(trmin)))) G_mean,
  convert(numeric(18,2), sqrt(avg(trmin*trmin))) Q_mean,
  convert(numeric(18,2), count(*)/sum(1/trmin)) H_mean
 From t
 Group by ID_comp
 with cube) as a left join Company c on a.ID_comp = c.ID_comp