Вывести
все строки из таблицы Product, кроме трех строк с наименьшими номерами
моделей и трех строк с наибольшими номерами моделей.
Select maker, model, type from
(
Select
row_number() over (order by model) p1,
row_number() over (order by model DESC) p2,
) t1
where p1 > 3 and p2 > 3
Все задачи и решения:
https://exercises-on-sql.blogspot.com/2017/02/select-sql.html
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
Вывод: название компании, время в минутах.
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
Вывод: дата, количество рейсов
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, для каждой авиакомпании определить:
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
Вывести название баллончика
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
