Упражнение 2
Найдите производителей принтеров. Вывести: maker.
Здесь впервые встречается ошибка, характерная для нескольких задач (например, 20, 27, 28 ). Причина в невнимательном изучении схемы данных. Неправильное решение:
Таким образом, для каждой строки из таблицы Product проверяется, есть ли такая модель в таблице Printer. Связь между этими таблицами (один-ко-многим) допускает наличие модели в таблице Product, которая отсутствовала бы в таблице Printer.
Пусть, например, фирма занимается ремонтом принтеров. При этом в таблице Product содержится информация обо всех известных моделях принтеров, а в таблице Printer только о тех, которые обслуживает фирма. Например, если фирма не занимается ремонтом принтеров Sharp, то модели Sharp будут находиться в таблице Product, а в таблице Printer — нет.
В результате мы можем потерять производителя принтеров, если его моделей нет среди обслуживаемых (в таблице Printer). Как уже говорилось при обсуждении схемы данных, тип продукции в таблице Product, задается атрибутом type, который и упускается из виду.
Если вам еще не ясно, как решить эту задачу, загляните в главу 4 «Подсказки и решения».
Если к данной задаче имеется пояснение или приведен вариант правильного решения, в конце анализа задачи будет ставиться ссылка на соответствующую страницу этой главы — ПиР.
Источник
Как найти производителей принтеров?
Доброго времени суток, тостермены!!
Возникла проблема с выполнением задачи на sql-ex.ru.
Есть схема БД :
Схема БД состоит из четырех таблиц:
Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, price, screen)
Printer(code, model, color, type, price)
Таблица Product представляет производителя (maker), номер модели (model) и тип (‘PC’ — ПК, ‘Laptop’ — ПК-блокнот или ‘Printer’ — принтер). Предполагается, что номера моделей в таблице Product уникальны для всех производителей и типов продуктов. В таблице PC для каждого ПК, однозначно определяемого уникальным кодом – code, указаны модель – model (внешний ключ к таблице Product), скорость — speed (процессора в мегагерцах), объем памяти — ram (в мегабайтах), размер диска — hd (в гигабайтах), скорость считывающего устройства — cd (например, ‘4x’) и цена — price. Таблица Laptop аналогична таблице РС за исключением того, что вместо скорости CD содержит размер экрана -screen (в дюймах). В таблице Printer для каждой модели принтера указывается, является ли он цветным — color (‘y’, если цветной), тип принтера — type (лазерный – ‘Laser’, струйный – ‘Jet’ или матричный – ‘Matrix’) и цена — price.
Вопрос:
Как найти производителей принтеров?
Источник
ArtVk & Bugtrack
Информационный блог, SEO, софт, баги, интернет делишки
Задачи по базам данных. Решение задач по SQL [1]
Rainbow Circles для Twitter
Rainbow Circles. Нескучная радуга для TwitterAnother fun rainbow interaction circle generator for Twitter!
🌈Rainbow Circles — This is a program for generating your social circle on Twitter Find your best friends on Twitter Create your own Twitter social circle and post it. How do I post a circle on Twitter? — On the main screen of the application, enter your Twitter username, you can also specify the name of another user; — Click on the «Generate» button; — If the user exists, as well as there is access to the Internet, the program will start collecting information on the specified user: user activity history, likes, reply, retweets The program does not require Twitter authorization! — Official Twitter API is used — Note: This tool is not directly related to the Twitter service. Please do not use the program if you do not understand its purpose.
Еще один нескучный генератор радужного круга взаимодействия для твиттер!
🌈RainbowCircles — Это программа для генерации вашего круг общения в социальной сети Twitter
Найдите своих лучших друзей в Twitter
Создайте свой собственный круг общения в Twitter и опубликуйте его.
Как опубликовать круг взаимодействия в Twitter?
— На главном экране приложения укажите свой никнейм пользователя из сети Twitter, также можно указать имя другого пользователя;
— Нажмите на кнопку «Generate»;
— Если пользователь существует, а так же есть доступ к сети интернет, программа начнет собирать информацию по указанному пользователю:
историю пользовательской активности, лайки, reply, ретвиты
Программа не требует авторизации в Twitter!
— Используются официальный Twitter API
— Внимание: этот инструмент не имеет прямого отношения к сервису Twitter.
Пожалуйста, не используйте программу, если вы не понимаете ее назначения.
Если наше приложение окажется для вас полезным, поставьте нам оценку 🌟🌟🌟🌟🌟
3 комментария:
Этот комментарий был удален автором.
VarangaOfficial — купить мазь варанга — все, что бы хотели знать об этом препарате. Воспользовавшись данным ресурсом, вы получите возможность узнать обстоятельную, полную информацию касательно этого натурального лекарственного комплекса. Увидеть данные о проведенных клинических исследований, прочитать отзывы реальных покупателей и врачей. Ознакомиться с инструкцией по применению, прочесть особенности и методы работы комплекса, уяснить, почему крем Варанга настолько эффективен, где необходимо заказывать оригинальный сертифицированный препарат и, как избежать покупки подделки. Мы тщательно проверяем публикуемые данные. Предоставляем пользователям нашего ресурса сведения, почерпнутые только из надежных источников. Если вы нашли признаки развития грибка или же долго и безрезультатно пытаетесь излечиться от этого неприятного недуга, на нашем сайте вы отыщете быстрый и простой способ решения проблемы. Присоединяетесь и живите здоровой полноценной жизнью. Мы собрали ответы на все вопросы на одном информационном ресурсе.
Бесплатный секс-видеочат с кисками 35 — это порно-контент для хобби, онлайн-портал, который позволяет посетителям общаться с женщинами, и, кроме того, это возможность полностью заняться виртуальным сексом с помощью аудио или видео, которые передаются через веб-камеру. В этом видеочате взрослые дамы готовы выслушать и реализовать любое желание, любую прихоть или стремление, которые не каждая женщина в реальной жизни готова реализовать для постоянных посетителей в ритме реальной жизни (онлайн в своих интимных видео-шоу. Хотите отличных впечатлений, от которых у вас мурашки пойдут по коже? Свяжитесь с нашей платформой продаж и выберите женщин старше 35 лет, которые только и ждут, чтобы порадовать и удивить слушателей своим чувственным телом, которое доставит вам бездну удовольствия!, Интимный просмотр дарит не только положительные впечатления, но и они все время задерживаются в жизни, что открывает сладкие ощущения на долгие годы. Порно видео со зрелыми женщинами, может стать хитом в какой-нибудь коллекции!, Бунтарская и страстная модель, которая просто хочет произвести впечатление на пользователя эстетичным телом, чтобы не дать вам остаться равнодушными! Захватывающий фантастический секс или нежность, маршруты с личными секс-аксессуарами или завораживающие позы, развратные взрослые дамы могут делать все, что вы хотите. После вирта с этой очаровашкой, в то же время вы никогда не потеряете шанс устоять и вернуться за большим! Ведь стильные девушки с лучшими дойками рады подарить вашим требованиям поистине неподдельное удовольствие, которое проникнет клиента до кончиков пальцев. Времяпровождение, которое вы запомните надолго, — это изучение секс-видео с женщинами в возрасте! Девушки старшего поколения готовы предоставить вам только самые точные представления об отличном сексе, неизмеримом кайфе и оправданных нормальных оргазмах. Девушки всегда готовы продемонстрировать это практически, что вы можете не только возбуждаться, параллельно они будут наслаждаться самим процессом. Часто клиенты сервиса чата для взрослых будут принадлежать к этой категории представителей сильного пола, они категорически не проявляют к ним большого внимания в своем повседневном существовании, как бы нам всем ни хотелось. В нашем каталоге таких разделов нет, любой пользователь получит внимание. Любой клиент будет не только выслушан одновременно, он даст великолепное эротическое представление!, Для людей, которые устали от стандартов красоты, и тех, кто хочет отдохнуть один день среди группы девушек пышных форм, в этом случае вам нужно зайти в «рунетки». Версии с объемными телами готовы показать вам некоторые варианты и эстетику, создавая невероятные эротические шоу во время прямой трансляции. Они будут рады пообщаться с вами и оставить яркие впечатления о нашем сайте. Вы поймете множество бонусов за онлайн-общение для больших. Когда вы дорожили этой цифрой и, естественно, хотели бы получить больше данных о зрелое порно чат Пожалуйста, посетите нашу собственную страницу.
Источник
Упражнения по SQL
четверг, 23 февраля 2017 г.
SELECT (обучающий этап) задачи по SQL запросам
Задачи по SQL запросам
SELECT model, speed, hd FROM PC WHERE price Задание: 2 (Serge I: 2002-09-21)
SELECT maker FROM Product WHERE type = ‘Printer’ GROUP BY maker
SELECT model,ram,screen FROM Laptop WHERE price > 1000
SELECT * FROM Printer WHERE color = ‘y’
SELECT model,speed,hd FROM PC WHERE ( cd = ’12x’ OR cd = ’24x’ ) AND price Для каждого производителя, выпускающего ПК-блокноты c объёмом жесткого диска не менее 10 Гбайт, найти скорости таких ПК-блокнотов. Вывод: производитель, скорость.
SELECT DISTINCT p.maker, l.speed
FROM laptop l
JOIN product p ON p.model = l.model
WHERE l.hd >= 10
SELECT DISTINCT product.model, pc.price
FROM Product JOIN pc ON product.model = pc.model WHERE maker = ‘B’
UNION
SELECT DISTINCT product.model, laptop.price
FROM product JOIN laptop ON product.model=laptop.model WHERE maker=’B’
UNION
SELECT DISTINCT product.model, printer.price
FROM product JOIN printer ON product.model=printer.model WHERE maker=’B’;
SELECT DISTINCT maker
FROM product
WHERE type = ‘pc’
EXCEPT
SELECT DISTINCT product.maker
FROM product
Where type = ‘laptop’
SELECT DISTINCT product.maker
FROM pc
INNER JOIN product ON pc.model = product.model
WHERE pc.speed >= 450
SELECT model, price
FROM printer
WHERE price =
(SELECT MAX(price)
FROM printer )
SELECT AVG(speed)
FROM laptop
WHERE price > 1000
SELECT AVG(pc.speed)
FROM pc, product
WHERE pc.model = product.model AND product.maker = ‘A’
SELECT maker, MAX(type)
FROM product
GROUP BY maker
HAVING COUNT(DISTINCT type) = 1 AND COUNT(model) > 1
SELECT hd FROM pc GROUP BY (hd) HAVING COUNT(model) >= 2
SELECT DISTINCT p1.model, p2.model, p1.speed, p1.ram
FROM pc p1, pc p2
WHERE p1.speed = p2.speed AND p1.ram = p2.ram AND p1.model > p2.model
select distinct p.type,p.model,l.speed
from laptop l
join product p on l.model=p.model
where l.speed Найдите производителей самых дешевых цветных принтеров. Вывести: maker, price
SELECT DISTINCT product.maker, printer.price
FROM product, printer
WHERE product.model = printer.model
AND printer.color = ‘y’
AND printer.price = (
SELECT MIN(price) FROM printer
WHERE printer.color = ‘y’
)
SELECT
product.maker, AVG(screen)
FROM laptop
LEFT JOIN product ON product.model = laptop.model
GROUP BY product.maker
SELECT maker, COUNT(model)
FROM product
WHERE type = ‘pc’
GROUP BY product.maker
HAVING COUNT (DISTINCT model) >= 3
SELECT product.maker, MAX(pc.price)
FROM product, pc
WHERE product.model = pc.model
GROUP BY product.maker
SELECT pc.speed, AVG(pc.price)
FROM pc
WHERE pc.speed > 600
GROUP BY pc.speed
SELECT DISTINCT maker
FROM product t1 JOIN pc t2 ON t1.model=t2.model
WHERE speed>=750 AND maker IN
( SELECT maker
FROM product t1 JOIN laptop t2 ON t1.model=t2.model
WHERE speed>=750 )
SELECT model
FROM (
SELECT model, price
FROM pc
UNION
SELECT model, price
FROM Laptop
UNION
SELECT model, price
FROM Printer
) t1
WHERE price = (
SELECT MAX(price)
FROM (
SELECT price
FROM pc
UNION
SELECT price
FROM Laptop
UNION
SELECT price
FROM Printer
) t2
)
SELECT DISTINCT maker
FROM product
WHERE model IN (
SELECT model
FROM pc
WHERE ram = (
SELECT MIN(ram)
FROM pc
)
AND speed = (
SELECT MAX(speed)
FROM pc
WHERE ram = (
SELECT MIN(ram)
FROM pc
)
)
)
AND
maker IN (
SELECT maker
FROM product
WHERE type=’printer’
)
SELECT sum(s.price)/sum(s.kol) as sredn FROM
(SELECT price,1 as kol FROM pc,product
WHERE pc.model=product.model AND product.maker=’A’
UNION all
SELECT price,1 as kol FROM laptop,product
WHERE laptop.model=product.model AND product.maker=’A’) as s
SELECT product.maker, AVG(pc.hd)
FROM pc, product WHERE product.model = pc.model
AND product.maker IN ( SELECT DISTINCT maker
FROM product
WHERE product.type = ‘printer’)
GROUP BY maker
SELECT AVG(pc.hd)
FROM pc, product
WHERE product.model = pc.model
AND product.maker IN (SELECT DISTINCT maker
FROM product WHERE product.type = ‘printer’)
SELECT t1.point, t1.date, inc, out
FROM income_o t1 LEFT JOIN outcome_o t2 ON t1.point = t2.point
AND t1.date = t2.date
UNION
SELECT t2.point, t2.date, inc, out
FROM income_o t1 RIGHT JOIN outcome_o t2 ON t1.point = t2.point
AND t1.date = t2.date
select point, date, SUM(sum_out), SUM(sum_inc)
from( select point, date, SUM(inc) as sum_inc, null as sum_out from Income Group by point, date
Union
select point, date, null as sum_inc, SUM(out) as sum_out from Outcome Group by point, date ) as t
group by point, date order by point
SELECT DISTINCT class, country
FROM classes
WHERE bore >= 16
Select country, cast(avg((power(bore,3)/2)) as numeric(6,2)) as weight
from (select country, classes.class, bore, name from classes left join ships on classes.class=ships.class
union all
select distinct country, class, bore, ship from classes t1 left join outcomes t2 on t1.class=t2.ship
where ship=class and ship not in (select name from ships) ) a
where name IS NOT NULL group by country
SELECT o.ship FROM
BATTLES b
LEFT join outcomes o ON o.battle = b.name
WHERE b.name = ‘North Atlantic’ AND o.result = ‘sunk’
Select name from classes,ships where launched >=1922 and displacement>35000 and type=’bb’ and
ships.class = classes.class
SELECT model, type
FROM product
WHERE upper(model) NOT like ‘%[^A-Z]%’
OR model not like ‘%[^0-9]%’
Select name from ships where/> union
select ship as name from classes,outcomes where classes.class = outcomes.ship
SELECT c.class
FROM classes c
LEFT JOIN (
SELECT class, name
FROM ships
UNION
SELECT ship, ship
FROM outcomes
) AS s ON s.class = c.class
GROUP BY c.class
HAVING COUNT(s.name) = 1
SELECT country
FROM classes
GROUP BY country
HAVING COUNT(DISTINCT type) = 2
WITH b_s AS
(SELECT o.ship, b.name, b.date, o.result
FROM outcomes o
LEFT JOIN battles b ON o.battle = b.name )
SELECT DISTINCT a.ship FROM b_s a
WHERE UPPER(a.ship) IN
(SELECT UPPER(ship) FROM b_s b
WHERE b.date Найдите класс, имя и страну для кораблей из таблицы Ships, имеющих не менее 10 орудий.
SELECT s.class, s.name, c.country
FROM ships s
LEFT JOIN classes c ON s.class = c.class
WHERE c.numGuns >= 10
Найдите класс, имя и страну для кораблей из таблицы Ships, имеющих не менее 10 орудий.
Select Classes.class, Ships.name, Classes.country
from Ships inner join Classes
on Ships.class = Classes.class
where Classes.numGuns > = 10
Для ПК с максимальным кодом из таблицы PC вывести все его характеристики (кроме кода) в два столбца:
— название характеристики (имя соответствующего столбца в таблице PC);
— значение характеристики
select fields,A from
(
Select
cast(model as NVARCHAR(10)) as model
, cast (speed as NVARCHAR(10)) as speed
, cast(ram as NVARCHAR(10)) as ram
,cast(hd as NVARCHAR(10)) as hd
, cast(cd as NVARCHAR(10)) as cd
, cast(price as NVARCHAR(10)) as price from PC
where code = (Select max(code) from PC)
) as t
unpivot
(
A for fields in (model, speed, ram, hd, cd, price)
) as unpvt
Найдите названия кораблей, потопленных в сражениях, и название сражения, в котором они были потоплены.
SELECT ship, battle FROM Outcomes WHERE result = ‘sunk’
Укажите сражения, которые произошли в годы, не совпадающие ни с одним из годов спуска кораблей на воду.
select name
from battles
where year(date) not in
(select launched
from ships
where launched is not null)
Найдите названия всех кораблей в базе данных, начинающихся с буквы R.
select name from Ships
where name LIKE ‘R%’
UNION
SELECT Ship From Outcomes
where Ship LIKE ‘R%’
Найдите названия всех кораблей в базе данных, состоящие из трех и более слов (например, King George V).
Считать, что слова в названиях разделяются единичными пробелами, и нет концевых пробелов.
select name from ships
where name like ‘% % %’
union
select ship from outcomes
where ship like ‘% % %’
Для каждого корабля, участвовавшего в сражении при Гвадалканале (Guadalcanal), вывести название, водоизмещение и число орудий.
SELECT o.ship, displacement, numGuns FROM
(SELECT name AS ship, displacement, numGuns
FROM Ships s JOIN Classes c ON c.class=s.class
UNION
SELECT class AS ship, displacement, numGuns
FROM Classes c) AS a
RIGHT JOIN Outcomes o
ON o.ship=a.ship
WHERE battle = ‘Guadalcanal’
Пронумеровать строки из таблицы Product в следующем порядке: имя производителя в порядке убывания числа производимых им моделей (при одинаковом числе моделей имя производителя в алфавитном порядке по возрастанию), номер модели (по возрастанию).
Вывод: номер в соответствии с заданным порядком, имя производителя (maker), модель (model)
SELECT count(*) OVER (order by c.countM DESC, c.maker,c.model) no, c.maker, c.model
FROM (SELECT count(*) OVER(partition by maker) countM, maker,model
FROM product) c
Найдите классы кораблей, в которых хотя бы один корабль был потоплен в сражении.
SELECT cl.class
FROM Classes cl
LEFT JOIN Ships s ON s.class = cl.class
WHERE cl.class IN (SELECT ship FROM Outcomes WHERE result = ‘sunk’) OR
s.name IN (SELECT ship FROM Outcomes WHERE result = ‘sunk’)
GROUP BY cl.class
Найдите названия кораблей с орудиями калибра 16 дюймов (учесть корабли из таблицы Outcomes).
SELECT Ships.name
FROM Classes JOIN
Ships ON Classes.class = ships.class
WHERE bore = 16
UNION
SELECT Outcomes.ship
FROM Outcomes JOIN
Classes ON Classes.class = Outcomes.ship
WHERE bore = 16
Найдите сражения, в которых участвовали корабли класса Kongo из таблицы Ships.
select distinct battle from outcomes
where ship in (select name
from ships
where )
SELECT name
FROM (SELECT O.ship AS name, numGuns, displacement
FROM Outcomes O INNER JOIN
Classes C ON O.ship = C.class AND
O.ship NOT IN (SELECT name
FROM Ships
)
UNION
SELECT S.name AS name, numGuns, displacement
FROM Ships S INNER JOIN
Classes C ON S.class = C.class
) OS INNER JOIN
(SELECT MAX(numGuns) AS MaxNumGuns, displacement
FROM Outcomes O INNER JOIN
Classes C ON O.ship = C.class AND
O.ship NOT IN (SELECT name
FROM Ships
)
GROUP BY displacement
UNION
SELECT MAX(numGuns) AS MaxNumGuns, displacement
FROM Ships S INNER JOIN
Classes C ON S.class = C.class
GROUP BY displacement
) GD ON OS.numGuns = GD.MaxNumGuns AND
OS.displacement = GD.displacement
SELECT DISTINCT s.name
FROM ships s
JOIN classes c ON c.class = s.class
WHERE UPPER(c.country) = ‘JAPAN’
and (numguns>=9 or numguns is NULL)
AND (c.bore Определите среднее число орудий для классов линейных кораблей.
Получить результат с точностью до 2-х десятичных знаков.
select round(avg(numGuns),2)
from classes where type=’bb’;
select round(avg(numguns),2)
from (
Select numguns, name
from classes left join ships using(class)
where type=’bb’ and name!=’null’ and class!=’null’
union all
select distinct numguns, ship
from classes left join outcomes on classes.class=outcomes.ship
where ship not in (select name from ships) and class!=’null’ and type=’bb’ )a;
SELECT c.class, t.y
FROM classes c
LEFT JOIN
(SELECT class, MIN(launched) AS y
FROM ships
GROUP BY class
) AS t ON c.class = t.class
SELECT c.class, COUNT(s.ship)
FROM classes c
LEFT JOIN
(
SELECT o.ship, sh.class
FROM outcomes o
LEFT JOIN ships sh ON sh.name = o.ship
WHERE o.result = ‘sunk’
) AS s ON s.class = c.class OR s.ship = c.class
GROUP BY c.class
SELECT c.class, SUM(sh.sunked)
FROM classes c
LEFT JOIN (
SELECT t.name AS name, t.class AS class,
CASE WHEN o.result = ‘sunk’ THEN 1 ELSE 0 END AS sunked
FROM
(
SELECT name, class
FROM ships
UNION
SELECT ship, ship
FROM outcomes
)
AS t
LEFT JOIN outcomes o ON t.name = o.ship
) sh ON sh.class = c.class
GROUP BY c.class
HAVING COUNT(DISTINCT sh.name) >= 3 AND SUM(sh.sunked) > 0
SELECT m, t,
CAST(100.0*cc/cc1 AS NUMERIC(5,2))
from
(SELECT m, t, sum(c) cc from
(SELECT distinct maker m, ‘PC’ t, 0 c from product
union all
SELECT distinct maker, ‘Laptop’, 0 from product
union all
SELECT distinct maker, ‘Printer’, 0 from product
union all
SELECT maker, type, count(*) from product
group by maker, type) as tt
group by m, t) tt1
JOIN (
SELECT maker, count(*) cc1 from product group by maker
) tt2
ON m=maker
SELECT c1, c2-
(CASE
WHEN o2 is null THEN 0
ELSE o2
END)
from
(SELECT point c1, sum(inc) c2 FROM income_o
group by point) as t1
left join
(SELECT point o1, sum(out) o2 FROM outcome_o
group by point) as t2
on c1=o1
SELECT c1, c2-
(CASE
WHEN o2 is null THEN 0
ELSE o2
END)
from
(SELECT point c1, sum(inc) c2 FROM income_o
where date Посчитать остаток денежных средств на всех пунктах приема для базы данных с отчетностью не чаще одного раза в день.
SELECT sum(i) FROM
(SELECT point, sum(inc) as i FROM
income_o
group by point
SELECT point, -sum(out) as i FROM
outcome_o
group by point
) as t
SELECT
(SELECT sum(inc) FROM Income_o WHERE date Определить имена разных пассажиров, когда-либо летевших на одном и том же месте более одного раза.
SELECT name FROM Passenger
WHERE ID_psg in
(SELECT ID_psg FROM Pass_in_trip
GROUP BY place, ID_psg
HAVING count(*)>1)
SELECT i1.point, i1.date, ‘inc’, sum(inc) FROM Income,
(SELECT point, date FROM Income
EXCEPT
SELECT Income.point, Income.date FROM Income
JOIN Outcome ON (Income.point=Outcome.point) AND
(Income.date=Outcome.date)
) AS i1
WHERE i1.point=Income.point AND i1.date=Income.date
GROUP BY i1.point, i1.date
UNION
SELECT o1.point, o1.date, ‘out’, sum(out) FROM Outcome,
(SELECT point, date FROM Outcome
EXCEPT
SELECT Income.point, Income.date FROM Income
JOIN Outcome ON (Income.point=Outcome.point) AND
(Income.date=Outcome.date)
) AS o1
WHERE o1.point=Outcome.point AND o1.date=Outcome.date
GROUP BY o1.point, o1.date
SELECT row_number() over(ORDER BY maker,s),t, type FROM
(SELECT maker,type,
CASE
WHEN type=’PC’
THEN 0
WHEN type=’Laptop’
THEN 1
ELSE 2
END AS s,
CASE
WHEN type=’Laptop’ AND (maker in (SELECT maker FROM Product WHERE
type=’PC’))
THEN
WHEN type=’Printer’ AND ((maker in (SELECT maker FROM Product WHERE
type=’PC’)) OR (maker in (SELECT maker FROM Product WHERE
type=’Laptop’)))
THEN »
ELSE maker
END AS t
FROM Product
GROUP BY maker,type) AS t1
ORDER BY maker, s
SELECT date, max(c) FROM
(SELECT date,count(*) AS c FROM Trip,
(SELECT trip_no,date FROM Pass_in_trip WHERE date>=’2003-04-01′ AND date Найти количество маршрутов, которые обслуживаются наибольшим числом рейсов.
Замечания.
1) A — B и B — A считать РАЗНЫМИ маршрутами.
2) Использовать только таблицу Trip
select count(*) from
(SELECT TOP 1 WITH TIES count(*) c, town_from, town_to from trip
group by town_from, town_to
order by c desc) as t
select count(*) from (
select TOP 1 WITH TIES sum(c) cc, c1, c2 from (
SELECT count(*) c, town_from c1, town_to c2 from trip
where town_from>=town_to
group by town_from, town_to
union all
SELECT count(*) c,town_to, town_from from trip
where town_to>town_from
group by town_from, town_to
) as t
group by c1,c2
order by cc desc
) as tt
with t as
(
select point, «date», inc, 0 AS «out» from income
union all
select point, «date», 0 AS inc, «out» from outcome
)
SELECT t.point, TO_CHAR ( t.»date», ‘DD/MM/YYYY’) AS day,
( select SUM(i.inc) from t i
where i.»date» Укажите сражения, в которых участвовало по меньшей мере три корабля одной и той же страны.
SELECT DISTINCT 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
HAVING COUNT(o.ship) >= 3
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)
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
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
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’ )
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
)
Определить время, проведенное в полетах, для пассажиров, летавших всегда на разных местах. Вывод: имя пассажира, время в минутах.
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 Определить дни, когда было выполнено максимальное число рейсов из
Ростова (‘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
Для каждого сражения определить первый и последний день
месяца,
в котором оно состоялось.
Вывод: сражение, первый день месяца, последний
день месяца.
Замечание: даты представить без времени в формате «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
Определить пассажиров, которые больше других времени провели в полетах.
Вывод: имя пассажира, общее время в минутах, проведенное в полетах
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
Найти производителей компьютерной техники, у которых нет моделей ПК, не представленных в таблице 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))
Из таблицы 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, отсортированном по столбцу code (по возрастанию) найти среднее значение цены для каждой шестерки подряд идущих ПК.
Вывод: значение 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) =0
GROUP BY CTE.number,CTE.code
HAVING COUNT(CTE.number)=6
Определить названия всех кораблей из таблицы 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
Для каждой компании подсчитать количество перевезенных пассажиров (если они были в этом месяце) по декадам апреля 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) 10 AND 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
Найти производителей, которые выпускают только принтеры или только 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))
Для каждого производителя перечислить в алфавитном порядке с разделителем «/» все типы выпускаемой им продукции.
Вывод: 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
Считая, что пункт самого первого вылета пассажира является местом жительства, найти не москвичей, которые прилетали в Москву более одного раза.
Вывод: имя пассажира, количество полетов в Москву
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
Среди тех, кто пользуется услугами только одной компании, определить имена разных пассажиров, летавших чаще других.
Вывести: имя пассажира, число полетов и название компании.
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
Найти производителей, у которых больше всего моделей в таблице Product, а также тех, у которых меньше всего моделей.
Вывод: 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) Вывести все строки из таблицы 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
Используя таблицу Product, определить количество производителей, выпускающих по одной модели.
select count(maker)
from product
where maker in
(
Select maker from product
group by maker
having count(model) = 1
)
Выбрать все белые квадраты, которые окрашивались только из баллончиков,
пустых к настоящему времени. Вывести имя квадрата
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) Для каждой компании, перевозившей пассажиров, подсчитать время, которое провели в полете самолеты с пассажирами.
Вывод: название компании, время в минутах.
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) Для семи последовательных дней, начиная от минимальной даты, когда из Ростова было совершено максимальное число рейсов, определить число рейсов из Ростова.
Вывод: дата, количество рейсов
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 ) На основании информации из таблицы 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
При условии, что баллончики с красной краской использовались более одного раза, выбрать из них такие, которыми окрашены квадраты, имеющие голубую компоненту.
Вывести название баллончика
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
Отобрать из таблицы 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 Вывести список ПК, для каждого из которых результат побитовой операции ИЛИ, примененной к двоичным представлениям скорости процессора и объема памяти, содержит последовательность из не менее четырех идущих подряд единичных битов.
Вывод: код модели, скорость процессора, объем памяти.
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 0
Рассматриваются только таблицы 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»
Написать запрос, который выводит все операции прихода и расхода из таблиц 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
Таблица Printer сортируется по возрастанию поля code.
Упорядоченные строки составляют группы: первая группа начинается с первой строки, каждая строка со значением 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
Определить имена разных пассажиров, которые летали
только между двумя городами (туда и/или обратно).
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 Выбрать три наименьших и три наибольших номера рейса. Вывести их в шести столбцах одной строки, расположив в порядке от наименьшего к наибольшему.
Замечание: считать, что таблица 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
Для каждого класса крейсеров, число орудий которого известно, пронумеровать (последовательно от единицы) все орудия.
Вывод: имя класса, номер орудия в формате ‘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
Статистики Алиса, Белла, Вика и Галина нумеруют строки у таблицы 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
Пусть 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 Для пятого по счету пассажира из числа вылетевших из Ростова в апреле 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
Реставрация экспонатов секции «Треугольники» музея ПФАН проводилась согласно техническому заданию. Для каждой записи таблицы 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 SQRT( SQUARE(b1.B_VOL) + SQUARE(b2.B_VOL)))
Вывести:
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
Определить имена разных пассажиров, когда-либо летевших рейсом, который вылетел в субботу, а приземлился в воскресенье.
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 Найти НЕ белые и НЕ черные квадраты, которые окрашены разными цветами в пропорции 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) Какое максимальное количество черных квадратов можно было бы окрасить в белый цвет
оставшейся краской
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
Сколько каждой краски понадобится, чтобы докрасить все Не белые квадраты до белого цвета.
Вывод: количество каждой краски в порядке (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) Определить имена разных пассажиров, которым чаще других доводилось лететь на одном и том же месте. Вывод: имя и количество полетов на одном и том же месте.
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)
Рассмотрим равнобочные трапеции, в каждую из которых можно вписать касающуюся всех сторон окружность. Кроме того, каждая сторона имеет целочисленную длину из множества значений 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 Считая, что каждая окраска длится ровно секунду, определить непрерывные интервалы времени с длительностью более 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) 0
По таблице 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)
Выборы Директора музея ПФАН проводятся только в високосный год, в первый вторник апреля после первого понедельника апреля.
Для каждой даты из таблицы 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 0 or Year(dt)%400=0)
and DATEPART(dw,dt)=DATEPART(dw,’20140106′)
GROUP BY name, date
Сгруппировать все окраски по дням, месяцам и годам. Идентификатор каждой группы должен иметь вид «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
Для авиакомпаний, самолеты которой выполнили хотя бы один рейс, вычислить с точностью до двух десятичных знаков средние величины времени нахождения самолетов в воздухе (в минутах). Также рассчитать указанные характеристики по всем летавшим самолетам (использовать слово ‘TOTAL’).
Вывод: компания, среднее арифметическое, среднее геометрическое, среднее квадратичное, среднее гармоническое.
Источник
