Задачи и упражнения по 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 строк из каждой группы?
Как удалить дубликаты строк из таблицы?
Как удалить дубликаты строк при наличии первичного...
Как подсчитать накопительный итог?
Как выводить в запросе все столбцы кроме одного, н...
четверг, 31 декабря 2020 г.
среда, 22 апреля 2020 г.
Для авиакомпаний, самолеты которой выполнили хотя бы один рейс, вычислить с точностью до двух десятичных знаков средние величины времени нахождения самолетов в воздухе (в минутах).
Задание: 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
Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html
суббота, 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
Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html
Выборы Директора музея ПФАН проводятся только в високосный год, в первый вторник апреля после первого понедельника апреля.
Задание: 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
Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html
Задание: 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)
Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html
Считая, что каждая окраска длится ровно секунду, определить непрерывные интервалы времени с длительностью более 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
Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html
Рассмотрим равнобочные трапеции, в каждую из которых можно вписать касающуюся всех сторон окружность.
Задание: 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
Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html
Определить имена разных пассажиров, которым чаще других доводилось лететь на одном и том же месте.
Задание: 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)
Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html
Сколько каждой краски понадобится, чтобы докрасить все Не белые квадраты до белого цвета.
Задание: 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
Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html
Какое максимальное количество черных квадратов можно было бы окрасить в белый цвет оставшейся краской
Задание: 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
Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html
Найти НЕ белые и НЕ черные квадраты, которые окрашены разными цветами в пропорции 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
Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html
Определить имена разных пассажиров, когда-либо летевших рейсом, который вылетел в субботу, а приземлился в воскресенье.
Задание: 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
)
Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html
Вывести: 1. Названия всех квадратов черного или белого цвета.
Задание: 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
Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html
Реставрация экспонатов секции "Треугольники" музея ПФАН проводилась согласно техническому заданию.
Задание: 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)))
Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html
Для пятого по счету пассажира из числа вылетевших из Ростова в апреле 2003 года определить компанию, номер рейса и дату вылета.
Задание: 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
Все задачи и решения: https://exercises-on-sql.blogspot.com/2017/02/select-sql.html
Пусть v1, v2, v3, v4, ... представляет последовательность вещественных чисел - объемов окрасок b_vol, упорядоченных по возрастанию b_datetime, b_q_id, b_v_id.
Задание: 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
Все задачи и решения: 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
Для каждого класса крейсеров, число орудий которого известно, пронумеровать (последовательно от единицы) все орудия.
Задание: 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