Задачи и упражнения по SQL запросам

Задачи и упражнения по SQL запросам

SELECT (обучающий этап) задачи по SQL запросам -> https://exercises-on-sql.blogspot.com/
DML
Используя таблицу Product, определить количество производителей, выпускающих по одной модели
Как объединить данные из двух столбцов в один без использования UNION и JOIN?
Команды DML
Как выводить в запросе все столбцы кроме одного, не перечисляя их?
Тест знаний SQL - Основы
Проектирование Интернет-приложений
Найдите размеры жестких дисков, совпадающих у двух и более PC. Вывести: HD
Найти производителей, которые выпускают более одной модели, при этом все

Оператор SELECT
Предикаты (часть I)
Переименование столбцов и вычисления в результирую...
Получение итоговых значений
Использование в запросе нескольких источников записей
Традиционные операции над множествами и оператор S...
Использование ключевых слов SOME | ANY и ALL с пре...
Преобразование типов
Функции Transact-SQL для обработки даты/времени
Функции работы со строками в MS SQL SERVER 2005
Операторы модификации данных
Оператор UPDATE
Приложение 1. Описание учебных баз данных
Как объединить данные из двух столбцов в один без ...
Как добавить новый столбец в таблицу между существ...
Как вывести по N строк из каждой группы?
Как удалить дубликаты строк из таблицы?
Как удалить дубликаты строк при наличии первичного...
Как подсчитать накопительный итог?
Как выводить в запросе все столбцы кроме одного, н...

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

Задание: 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)

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

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

Задание: 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

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

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

Задание: 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

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

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

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

Вывод: страна, класс

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

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

Для каждого корабля из таблицы Ships указать название ближайшего по времени сражения из таблицы Battles,

Задание: 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
)

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

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

Задание: 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

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

Определить дни, когда было выполнено максимальное число рейсов из Ростова ('Rostov').

Задание: 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

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

Для каждого сражения определить первый и последний день месяца, в котором оно состоялось.

Задание: 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

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

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

Задание: 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

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

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

Задание: 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))

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

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

Задание: 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

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

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

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

Вывод: значение 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

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

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

Задание: 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

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

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

Задание: 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

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

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

Задание: 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))

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

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

Задание: 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

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

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

Задание: 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

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

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

Задание: 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

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

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

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

Вывод: 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)

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

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

Задание: 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

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

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

Задание: 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
)

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

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

Задание: 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)))

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

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

Задание: 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

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

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

Задание: 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

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

На основании информации из таблицы Pass_in_Trip, для каждой авиакомпании определить:

Задание: 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

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

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

Задание: 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

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

Отобрать из таблицы Laptop те строки, для которых выполняется следующее условие:

Задание: 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]
)

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

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

Задание: 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

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

Рассматриваются только таблицы Income_o и Outcome_o. Известно, что прихода/расхода денег в воскресенье не бывает.

Задание: 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"

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

Написать запрос, который выводит все операции прихода и расхода из таблиц Income и Outcome в следующем виде:

Задание: 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

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

Рассматриваются только таблицы Income_o и Outcome_o. Известно, что прихода/расхода денег в воскресенье не бывает.

Задание: 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"

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

Написать запрос, который выводит все операции прихода и расхода из таблиц Income и Outcome в следующем виде:

Задание: 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

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

Таблица Printer сортируется по возрастанию поля code.

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

с
Упорядоченные строки составляют группы: первая группа начинается с первой строки, каждая строка со значением 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

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

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

Задание: 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
)

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

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

Задание: 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

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

Для каждого класса крейсеров, число орудий которого известно, пронумеровать (последовательно от единицы) все орудия.

Задание: 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

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

Статистики Алиса, Белла, Вика и Галина нумеруют строки у таблицы Product. Все четверо упорядочили строки таблицы по возрастанию названий производителей.

Задание: 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

Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html