суббота, 18 апреля 2020 г.

Сгруппировать все окраски по дням, месяцам и годам. Идентификатор каждой группы должен иметь вид "yyyy" для года, "yyyy-mm" для месяца и "yyyy-mm-dd" для дня.

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

Выборы Директора музея ПФАН проводятся только в високосный год, в первый вторник апреля после первого понедельника апреля.

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

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

Считая, что каждая окраска длится ровно секунду, определить непрерывные интервалы времени с длительностью более 1 секунды из таблицы utB.

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

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

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

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

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

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

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

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

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

Найти НЕ белые и НЕ черные квадраты, которые окрашены разными цветами в пропорции 1:1:1.

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

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

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