Для авиакомпаний, самолеты которой выполнили хотя бы один рейс, вычислить с точностью до двух десятичных знаков средние величины времени нахождения самолетов в воздухе (в минутах). Также рассчитать указанные характеристики по всем летавшим самолетам (использовать слово '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
Сгруппировать все окраски по дням, месяцам и годам. Идентификатор каждой группы должен иметь вид "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
Выборы Директора музея ПФАН проводятся только в високосный год, в первый вторник апреля после первого понедельника апреля. Для каждой даты из таблицы 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
Вывод в три столбца: - страна; - максимальное значение; - слово `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)
Считая, что каждая окраска длится ровно секунду, определить непрерывные интервалы времени с длительностью более 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
Рассмотрим равнобочные трапеции, в каждую из которых можно вписать касающуюся всех сторон окружность. Кроме того, каждая сторона имеет целочисленную длину из множества значений 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
Вывод: имя и количество полетов на одном и том же месте.
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)
Вывод: количество каждой краски в порядке (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
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
Найти НЕ белые и НЕ черные квадраты, которые окрашены разными цветами в пропорции 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
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
)
Вывести: 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
Реставрация экспонатов секции "Треугольники" музея ПФАН проводилась согласно техническому заданию. Для каждой записи таблицы 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)))
Для пятого по счету пассажира из числа вылетевших из Ростова в апреле 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
Пусть 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
Статистики Алиса, Белла, Вика и Галина нумеруют строки у таблицы 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
Для каждого класса крейсеров, число орудий которого известно, пронумеровать (последовательно от единицы) все орудия. Вывод: имя класса, номер орудия в формате '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
Выбрать три наименьших и три наибольших номера рейса. Вывести их в шести столбцах одной строки, расположив в порядке от наименьшего к наибольшему. Замечание: считать, что таблица 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
Определить имена разных пассажиров, которые летали только между двумя городами (туда и/или обратно).
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
)
сУпорядоченные строки составляют группы: первая группа начинается с первой строки, каждая строка со значением 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
Написать запрос, который выводит все операции прихода и расхода из таблиц 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
Рассматриваются только таблицы 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"
Вывод: код модели, скорость процессора, объем памяти.
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
Отобрать из таблицы 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]
)
Вывести название баллончика
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
На основании информации из таблицы 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
Вывод: дата, количество рейсов
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
Вывод: название компании, время в минутах.
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
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)))
Используя таблицу Product, определить количество производителей, выпускающих по одной модели.
select count(maker)
from product
where maker in
(
Select maker from product
group by maker
having count(model) = 1
)
Вывести все строки из таблицы 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
Вывод: 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)
Вывести: имя пассажира, число полетов и название компании.
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
Вывод: имя пассажира, количество полетов в Москву
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
Для каждого производителя перечислить в алфавитном порядке с разделителем "/" все типы выпускаемой им продукции. Вывод: 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
Найти производителей, которые выпускают только принтеры или только 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))
Для каждой компании подсчитать количество перевезенных пассажиров (если они были в этом месяце) по декадам апреля 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
Определить названия всех кораблей из таблицы 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
Вывод: значение 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
Из таблицы Outcome получить все записи за тот месяц (месяцы), с учетом года, в котором суммарное значение расхода (out) было максимальным.
SELECT O.*
FROM outcome O
INNER JOIN
(
SELECT TOP 1 WITH TIES YEAR(date) AS Y, MONTH(date) AS M, SUM(out) AS ALL_TOTAL
FROM outcome
GROUP BY YEAR(date), MONTH(date)
ORDER BY ALL_TOTAL DESC
) R ON YEAR(O.date) = R.Y AND MONTH(O.date) = R.M
Найти производителей компьютерной техники, у которых нет моделей ПК, не представленных в таблице PC.
SELECT DISTINCT maker
FROM product
WHERE maker NOT IN (
SELECT maker
FROM product
WHERE type='PC' AND model NOT IN (
SELECT model
FROM PC))
Вывод: имя пассажира, общее время в минутах, проведенное в полетах
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
Для каждого сражения определить первый и последний день месяца, в котором оно состоялось. Вывод: сражение, первый день месяца, последний день месяца. Замечание: даты представить без времени в формате "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
Определить дни, когда было выполнено максимальное число рейсов из Ростова ('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
Определить время, проведенное в полетах, для пассажиров, летавших всегда на разных местах. Вывод: имя пассажира, время в минутах.
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
Для каждого корабля из таблицы 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
)
