Как найти среднюю скорость sql - Исправление недочетов и поиск решений вместе с Examum.ru

Задание: 13

Текст задания:
Найдите среднюю скорость ПК, выпущенных производителем A.

Решение:
SELECT AVG(speed) FROM pc LEFT JOIN product ON product.model=pc.model WHERE product.maker='A';

Анализ плана выполнения запроса:
Стоимость: 0.011982340365648.
Количество операций: 5.

Упражнения по SQL

SELECT (обучающий этап) задачи по SQL запросам 120 штук, DML 10 шт. Дистанционное обучение языку баз данных SQL. Интерактивные упражнения и тестирование по операторам SELECT,INSERT,UPDATE,DELETE языка SQL. SQL remote education. SQL statements exercises. Подзапросы, Соединение таблиц, Функции SQL, Введение в SQL, Скачать книги по SQL. Команды SQL,CREATE SEQUENCE,CREATE SYNONYM,CREATE USER,CREATE VIEW,Create Table,DROP,GRANT,INSERT,REVOKE,SET ROLE,SET TRANSACTION,SQL ALTER TABLE,SQL команды.

Источник

This is little bit complex, but will take care of 0 speed also. I have 2 similar queries to do it based on different scenarios.

Assume your source table like below.

+-------------+----------+-------+
| driver_lpr  | segment  | speed |
+-------------+----------+-------+
|    0000001  | A        |    30 |
|    0000002  | B        |    60 |
|    0000003  | A        |    50 |
|    0000004  | A        |   100 |
|    0000005  | B        |    60 |
|    0000006  | B        |     0 |
|    0000007  | C        |     0 |
+-------------+----------+-------+

I have added 2 new rows with 0 speed.

Case 1:

Addition of a 0 speed in segment B, will give average speed as
40 (60*2+0*1)/(2+1).
Addition of a new segment, C with 0
speed will give average speed as 0

So output would be

+----------+-------------------+
| segment  |   average_speed   |
+----------+-------------------+
| A        | 47.36842105263158 |
| B        | 40                |
| C        | 0                 |
+----------+-------------------+

SQLFiddle Demo CASE 1

Case 2:

There will be no change in average speed of B with addition of a
0.
However, a new segment C will have 0 average speed.

Output would be

+----------+-------------------+
| segment  |   average_speed   |
+----------+-------------------+
| A        | 47.36842105263158 |
| B        | 60                |
| C        | 0                 |
+----------+-------------------+

SQLFiddle Demo CASE 2

Query for Case 1:

with tbl1 as
    (SELECT segment,
    case when speed = 0 then cast(0 as float) else 
    cast(1 as float)/cast(speed as float)
    end as newspeed
    FROM T 
    ),
tbl2 as
    (
        select segment,cast(1 as float)/avg(newspeed) as avgspeed,count(*) as cnt 
        from tbl1
        where newspeed <> 0
        group by segment
    union
        select segment,0 as avgspeed,count(*) as cnt
        from tbl1
        where newspeed =0
        group by segment
    )
select segment,
    sum(avgspeed*cnt)/sum(cnt) as "average_speed" 
from tbl2
group by segment

Query for Case2

with tbl1 as
    (SELECT segment,
    case when speed = 0 then cast(0 as float) else 
    cast(1 as float)/cast(speed as float)
    end as newspeed
    FROM T 
    ),
tbl2 as
    (
        select segment,cast(1 as float)/avg(newspeed) as avgspeed,count(*) as cnt 
        from tbl1
        where newspeed <> 0
        group by segment
    union
        select segment,0 as avgspeed,count(*) as cnt
        from tbl1
        where newspeed =0
        group by segment
    )
select segment,
    sum(avgspeed) as "average_speed" 
from tbl2
group by segment

Источник

Задание: 1

Найдите номер модели, скорость и размер жесткого диска для всех
ПК стоимостью менее 500 дол. Вывести: model, speed и hd

Select model , speed, hd From
pc Where price < 500

Задание: 2

Найдите
производителей принтеров. Вывести: maker

Select maker from product
where product.type = ‘printer’ group by maker

Задание: 3

Найдите
номер модели, объем памяти и размеры экранов ПК-блокнотов, цена которых
превышает 1000 дол.

select model , ram ,
screen from laptop where price > 1000

Задание: 4

Найдите
все записи таблицы Printer для цветных принтеров.

select * from printer where color =
‘y’

Задание: 5

Найдите
номер модели, скорость и размер жесткого диска ПК, имеющих 12x или 24x CD и
цену менее 600 дол.

Select model ,speed , hd from
pc where (cd = ’12x’ or cd = ’24x’) and price < 600

Задание: 6

Укажите
производителя и скорость для тех ПК-блокнотов, которые имеют жесткий диск
объемом не менее 10 Гбайт.

Select maker, speed from Product
inner join Laptop on Product.model = Laptop.model
where hd >= 10

Задание: 7

Найдите
номера моделей и цены всех продуктов (любого типа), выпущенных производителем B
(латинская буква).

Select laptop.model , laptop.price
from laptop inner join product on laptop.model = product.model
where product.maker= ‘B’
union
Select pc.model , pc.price from pc inner join product on pc.model =
product.model
where product.maker= ‘B’
union
Select printer.model , printer.price from printer inner join product on
printer.model = product.model
where product.maker= ‘B’

Задание: 8

Найдите
производителя, выпускающего ПК, но не ПК-блокноты.

select maker from product where
type=’PC’ and maker not in
( select maker from product where type = ‘Laptop’) group by
maker

Задание: 9

Найдите
производителей ПК с процессором не менее 450 Мгц. Вывести: Maker

Select maker from pc inner join
product on pc.model = product.model where speed >= 450
group by maker

Задание: 10

Найдите
принтеры, имеющие самую высокую цену. Вывести: model, price

select model, price from printer
where price = (select max(price) from printer)

Задание: 11

Найдите
среднюю скорость ПК.

select avg (speed) from pc

Задание: 12

Найдите
среднюю скорость ПК-блокнотов, цена которых превышает 1000 дол.

Select avg(speed) from
laptop where price > 1000

Задание: 13

Найдите
среднюю скорость ПК, выпущенных производителем A

Select avg(speed) from pc inner join
product on pc.model= product.model where maker = ‘A’
group by maker

Задание: 14

Для
каждого значения скорости найдите среднюю стоимость ПК с такой же скоростью
процессора. Вывести: скорость, средняя цена

Select speed , avg(price) from
pc group by speed

Задание: 14

Найти
производителей, которые выпускают более одной модели, при этом все выпускаемые
производителем модели являются продуктами одного типа.Вывести: maker, type

select maker ,type from Product
where maker in ( select maker
from ( select maker,type from Product group by maker,type ) x
group by maker having count(*)=1 )
group by maker,type having count(*)>1

Задание: 15

Найдите
размеры жестких дисков, совпадающих у двух и более PC. Вывести: HD

Select hd from pc group by
hd having count(model)>1

Задание: 16

Найдите
пары моделей PC, имеющих одинаковые скорость и RAM. В результате каждая пара
указывается только один раз, т.е. (i,j), но не (j,i), Порядок вывода: модель с
большим номером, модель с меньшим номером, скорость и RAM.

SELECT DISTINCT B.model AS model, A.model
AS model, A.speed, A.ram
FROM PC AS A, PC B
WHERE A.speed = B.speed AND A.ram = B.ram and A.model < B.model

Задание: 17

Найдите
модели ПК-блокнотов, скорость которых меньше скорости любого из
ПК. Вывести:
type, model, speed

Select distinct
type,laptop.model,speed from laptop inner join product on laptop.model=
product.model
where speed < (select MIN(speed) from pc)

Задание: 18

Найдите
производителей самых дешевых цветных принтеров. Вывести: maker, price

SELECT DISTINCT maker,price FROM
printer inner JOIN product ON printer.model= product.model
WHERE price = (select min(price)from printer where color = ‘y’ ) and color
= ‘y’

Задание: 19

Для
каждого производителя найдите средний размер экрана выпускаемых им
ПК-блокнотов. Вывести:
maker, средний размер экрана.

Select maker ,avg(screen)as Avg_screen
from laptop inner join product on laptop.model = product.model group
by maker

Задание: 20

Найдите
производителей, выпускающих по меньшей мере три различных модели ПК. Вывести: Maker, число моделей

Select maker , count(model) as
Count_Model from product where type = ‘pc’ group by maker
having count(model) >= 3

Задание: 21

Найдите
максимальную цену ПК, выпускаемых каждым производителем. Вывести: maker, максимальная цена.

Select maker , max(price)as Max_price from
pc inner join product on pc.model= product.model
group by maker

Задание: 22

Для
каждого значения скорости ПК, превышающего 600 МГц, определите среднюю цену ПК
с такой же скоростью. Вывести: speed, средняя цена.

Select speed , avg(price) as Avg_price from
pc where speed > 600 group by speed

Задание: 23

Найдите
производителей, которые производили бы как ПКсо скоростью не менее 750 МГц, так
и ПК-блокноты со скоростью не менее 750 МГц.Вывести: Maker

select distinct maker from pc inner
join product on pc.model = product.model
where pc.speed >= 750 and maker in (select maker
from laptop inner join product on laptop.model = product.model where
laptop.speed >= 750)

Задание: 24

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

SELECT model FROM(
SELECT distinct model, price FROM laptop WHERE laptop.price = (SELECT
MAX(price) FROM laptop)
UNION
SELECT distinct model, price FROM pc WHERE pc.price = (SELECT MAX(price)
FROM pc)
UNION
SELECT distinct model, price FROM printer WHERE printer.price = (SELECT
MAX(price) FROM printer)
) as t
WHERE t.price=(SELECT MAX(price) FROM (
SELECT distinct price FROM laptop WHERE laptop.price = (SELECT MAX(price)
FROM laptop)
UNION
SELECT distinct price FROM pc WHERE pc.price = (SELECT MAX(price) FROM
pc)
UNION
SELECT distinct price FROM printer WHERE printer.price = (SELECT
MAX(price) FROM printer)
) as t1 )

Задание: 25

Найдите
производителей принтеров, которые производят ПК с наименьшим объемом RAM и с
самым быстрым процессором среди всех ПК,имеющих наименьший объем RAM. Вывести: Maker

SELECT distinct product.maker FROM
product WHERE product.type=’Printer’
INTERSECT
SELECT distinct product.maker FROM product INNER JOIN pc ON
pc.model=product.model
WHERE product.type=’PC’ AND pc.ram=(SELECT MIN(ram) FROM pc)
AND pc.speed = (SELECT MAX(speed) FROM (SELECT distinct speed FROM pc
WHERE pc.ram=(SELECT MIN(ram) FROM pc)) as t)

Задание: 26

Найдите
среднюю цену ПК и ПК-блокнотов, выпущенных производителем A (латинская буква).
Вывести: одна общая средняя цена.

SELECT t1.c/t1.d FROM( SELECT
SUM(t.a) as c, SUM(t.b) as d FROM(
SELECT SUM(pc.price) as a, COUNT(pc.code) as b FROM pc
INNER JOIN product ON pc.model=product.model WHERE product.maker=’A’
UNION
SELECT SUM(laptop.price) as a, COUNT(laptop.code) as b FROM laptop
INNER JOIN product ON laptop.model=product.model WHERE product.maker=’A’)
as t) as t1

Задание: 27

Найдите
средний размер диска ПК каждого из тех производителей, которые выпускают и
принтеры. Вывести:
maker, средний размер HD.

select maker,avg(hd) from product
inner join pc on product.model=pc.model
where maker in(select maker from product  where
type=’printer’)  group by maker

Задание: 28

Найдите
средний размер диска ПК (одно значение для всех) тех производителей, которые
выпускают и принтеры. Вывести:
средний размер HD

select avg(hd) from product inner
join pc on product.model = pc.model
where maker in(select maker from product where type=’printer’)

Задание: 29

В предположении, что приход и расход денег на каждом пункте
приема фиксируется не чаще одного раза в день [т.е. первичный ключ (пункт,
дата)], написать запрос с выходными данными (пункт, дата, приход,
расход). Использовать таблицы Income_o и Outcome_o.

select t.point, t.date, SUM(t.inc),
sum(t.out) from( select point, date, inc, null as out from Income_o
Union
select point, date, null as inc, Outcome_o.out from Outcome_o) as t group
by t.point, t.date

Задание: 30

В
предположении, что приход и расход денег на каждом пункте приема фиксируется
произвольное число раз (первичным ключом в таблицах является столбец code),
требуется получить таблицу, в которой каждому пункту за каждую дату выполнения
операций будет соответствовать одна строка.Вывод: point, date, суммарный расход
пункта за день (out), суммарный приход пункта за день (inc).Отсутствующие
значения считать неопределенными (NULL).

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

Задание: 31

Для
классов кораблей, калибр орудий которых не менее 16 дюймов, укажите класс и
страну.

Select class , country from
classes where bore >= 16

Задание: 32

Одной
из характеристик корабля является половина куба калибра его главных орудий (mw).
С точностью до 2 десятичных знаков определите среднее значение mw для кораблей
каждой страны, у которой есть корабли в базе данных.

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!=’null’ group by country

Задание: 32 > вариант 2

select country, cast(avg(bore*bore*bore/2)
AS NUMERIC(6,2)) as mw from (
select C.class, S.name, C.country, C.bore from classes as c join ships as
s on c.class=s.class
union
select C.class, O.ship, C.country, C.bore from classes as c join outcomes
as o on c.class=o.ship ) as G
group by country

Задание: 33

Укажите корабли, потопленные в сражениях в Северной Атлантике
(North Atlantic). Вывод:
ship.

Select ship from
outcomes,battles where result= ‘sunk’ and battle = ‘North
Atlantic’ group by ship

Задание: 34

По
Вашингтонскому международному договору от начала 1922 г. запрещалось строить
линейные корабли водоизмещением более 35 тыс.тонн. Укажите корабли, нарушившие
этот договор (учитывать только корабли c известным годом спуска на воду).
Вывести названия кораблей.

Select name from classes,shipswhere
launched>=1922 and displacement>35000 and type=’bb’ and
ships.class = classes.class

Задание: 35

В
таблице Product найти модели, которые состоят только из цифр или только из
латинских букв (A-Z, без учета регистра).Вывод: номер модели, тип модели.

SELECT model, type FROM product
WHERE model NOT LIKE ‘%[^0-9]%’ OR model NOT LIKE ‘%[^a-z]%’

Задание: 36

Перечислите
названия головных кораблей, имеющихся в базе данных (учесть корабли в
Outcomes).

Select name from
ships  where class = name
union
select ship as name from classes,outcomes  where classes.class
= outcomes.ship

Задание: 37

Найдите
классы, в которые входит только один корабль из базы данных (учесть также
корабли в Outcomes).

Select class from(select name,class
from ships
union
select class as name,class from classes,outcomes  where
classes.class=outcomes.ship) A
group by class having count(A.name)=1

Задание: 38

Найдите
страны, имевшие когда-либо классы обычных боевых кораблей (‘bb’) и имевшие
когда-либо классы крейсеров (‘bc’).

Select distinct country from
classes  where type=’bb’
intersect
Select distinct country from classes  where type=’bc’

Задание: 39 >

Найдите
корабли, «сохранившиеся для будущих сражений»; т.е. выведенные из
строя в одной битве (damaged), они участвовали в другой, произошедшей позже.

select distinct ccc.sh
from ( select aaa.ship as sh, aaa.[date] as d1, bbb.[date] as d2 from
(
select ship, [date] from outcomes as o inner join battles as b on
o.battle=b.name where result = ‘damaged’) as aaa inner join (select ship,
[date] from outcomes as o inner join battles as b on o.battle=b.name) as bbb on
aaa.ship=bbb.ship
where bbb.date > aaa.date) as ccc

Вариант 2 : Задание: 39

select distinct B.ship
from(select * from outcomes left join battles on battle=name where
result=’damaged’)as B
where exists (select shipfrom outcomes left join battles on battle=name
where ship=B.ship and B.date<date)

Задание: 40

Найдите
класс, имя и страну для кораблей из таблицы Ships, имеющих не менее 10 орудий.

Select classes.class ,
name,country from classes inner join ships on classes.class = ships.class
where numguns >= 10

Задание: 41

Для
ПК с максимальным кодом из таблицы PC вывести все его характеристики (кроме
кода) в два столбца:- название характеристики (имя соответствующего столбца в
таблице PC);- значение характеристики

select ‘speed’ as m, CAST(speed as char)
as a from pc where code >= all(select code from pc)
union
select ‘model’ as m, CAST(model as char) as a from pc where code >=
all(select code from pc)
union
select ‘ram’ as m, CAST(ram as char) as a from pc where code >= all(select
code from pc)
union
select ‘hd’ as m, CAST(hd as char) as a from pc where code >= all(select
code from pc)
union
select ‘cd’ as m, CAST(cd as char) as a from pc where code >= all(select
code from pc)
union
select ‘price’ as m, CAST(price as char) as a from pc where code >=
all(select code from pc)

Вариант 2 : Задание: 41

select characteristics, value
from (select
cast(model as varchar(max)) as model,
cast(speed as varchar(max)) as speed,
cast(ram as varchar(max)) as ram,
cast(hd as varchar(max)) as hd,
cast(cd as varchar(max)) as cd,
cast(price as varchar(max)) as price
from pc where code in (select max(code) from pc)) as A
unpivot(value for characteristics in (model, speed, ram, hd, cd, price)) as
unpvt

Задание: 42

Найдите
названия кораблей, потопленных в сражениях, и название сражения, в котором они
были потоплены.

Select ship,battle from
outcomes where result =’sunk’

Задание: 43

Укажите
сражения, которые произошли в годы, не совпадающие ни с одним из годов спуска
кораблей на воду.

select name from battles where
DATEPART(yy, date) not in (select DATEPART(yy, date)
from battles join ships on DATEPART(yy, date)=launched)

Задание: 44

Select name from ships where name like
‘R%’
union
Select name from battles where name like ‘R%’
union
Select ship from outcomes where ship like ‘R%’

Задание: 45

Найдите названия всех кораблей в базе данных, состоящие из трех
и более слов (например, King George V). Считать, что слова в названиях
разделяются единичными пробелами, и нет концевых пробелов.

Select name from ships where name like ‘%
% %’
union
Select ship from outcomes where ship like ‘% % %’

Задание: 46

Укажите
названия, водоизмещение и число орудий кораблей, участвовавших в сражении при
Гвадалканале (Guadalcanal).

select name as n, displacement as d,
numguns as ng from ships inner join classes on
ships.class=classes.class where name in (select ship from outcomes where
battle = ‘Guadalcanal’)
union

select ship as n, displacement as d,
numguns as ng from outcomes inner join classes on outcomes.ship=classes.class
where battle = ‘Guadalcanal’ and ship not in (select name from
ships)
union
select ship as n, null as d, null as ng from outcomes where battle =
‘Guadalcanal’ and ship not in (select name from ships) and ship not in
(select class from classes)

Задание: 47

Пронумеровать
строки из таблицы Product в следующем порядке: имя производителя в порядке
убывания числа производимых им моделей (при одинаковом числе моделей имя
производителя в алфавитном порядке по возрастанию), номер модели (по
возрастанию).Вывод: номер в соответствии с заданным порядком, имя производителя
(maker), модель (model)

select ROW_NUMBER() OVER(ORDER BY co desc,
m, model) no, m, model
from ( Select one.maker as m, model, co
from product as one join (Select maker, count(model) as co from product group
by maker) as two on one.maker=two.maker ) as ddd

Задание: 48

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

Select class as n from ships where
name in(select ship from outcomes where result=’sunk’)
union
Select ship as n from outcomes
where ship not in(Select name from ships) and ship in(Select class from
classes) and result=’sunk’

Задание: 49

Найдите
названия кораблей с орудиями калибра 16 дюймов (учесть корабли из таблицы
Outcomes).

select name from ships where class
in( Select class from classes where bore=16)
union
select ship from outcomes where ship in( Select class from classes where
bore=16)

Задание: 50

Найдите
сражения, в которых участвовали корабли класса Kongo из таблицы Ships.

SELECT distinct battle FROM Classes
inner JOIN Ships ON ships.class = classes.class
inner JOIN Outcomes ON Classes.class=Outcomes.ship or
Ships.name=Outcomes.ship
WHERE classes.class = ‘Kongo’

Задание: 51

Найдите
названия кораблей, имеющих наибольшее число орудий среди всех имеющихся
кораблей такого же водоизмещения (учесть корабли из таблицы Outcomes).

select NAME from(select name as NAME,
displacement, numguns
from ships inner join classes on ships.class = classes.class
union
select ship as NAME, displacement, numguns from outcomes inner join
classes on outcomes.ship= classes.class) as d1 inner join (select
displacement, max(numGuns) as numguns from ( select displacement,
numguns from ships inner join classes on ships.class = classes.class
union
select displacement, numguns from outcomes inner join classes on
outcomes.ship= classes.class) as f
group by displacement) as d2 on d1.displacement=d2.displacement and d1.numguns
=d2.numguns

Задание: 52

Определить
названия всех кораблей из таблицы Ships, которые могут быть линейным японским
кораблем, имеющим число главных орудий не менее девяти, калибр орудий
менее 19 дюймов и водоизмещение не более 65 тыс.тонн

Select distinct name from ships
inner join classes cl on ships.class=cl.class
where (numGuns>=9 or numguns is NULL) and (bore<19 or bore is NULL) and
(displacement<=65000 or displacement is NULL) and type=’bb’ and
country=’japan’

Задание: 53

Определите
среднее число орудий для классов линейных кораблей.Получить результат с
точностью до 2-х десятичных знаков.

select cast(avg(numguns*1.0) as
numeric(4,2)) as Avg_numGuns from classes where type=’bb’

Задание: 54

С
точностью до 2-х десятичных знаков определите среднее число орудий всех
линейных кораблей (учесть корабли из таблицы Outcomes).

SELECT CAST(AVG(numguns*1.0) AS NUMERIC
(4,2)) as AVG_nmg
FROM (SELECT ship, type, numguns   FROM Outcomes LEFT JOIN Classes ON
ship = class
UNION
SELECT name, type, numguns FROM Ships as S INNER JOIN Classes as C ON
c.class = s.class ) AS T
WHERE type = ‘bb’

Задание: 55

Для
каждого класса определите год, когда был спущен на воду первый корабль этого
класса. Если год спуска на воду головного корабля неизвестен, определите
минимальный год спуска на воду кораблей этого класса. Вывести: класс, год.

select C.class, min(launched) from
ships as S right join classes as C on s.class=c.class group by C.class

Задание: 56

Для
каждого класса определите число кораблей этого класса, потопленных в сражении.
Вывести: класс и число потопленных кораблей.

select classes.class, count(T.ship) from
classes left join(select ship, class from outcomes left join ships on
ship=name where result=’sunk’union select ship, class from outcomes
left join classes on ship=class where result=’sunk’) as T on classes.class=T.classgroup
by classes.class

Задание: 57

Для
классов, имеющих потери в виде потопленных кораблей и не менее 3 кораблей в
базе данных, вывести имя класса и число потопленных кораблей.

select class as cls, count(class) as
sunked from(
select C.class, O.ship from classes as C join outcomes as O on
C.class=O.ship where O.result=’sunk’
union
select S.class, O.ship from outcomes as O join ships as S on
S.name=O.ship where O.result=’sunk’) as T
where class in ( select distinct X.class from (select C.class,
O.ship from classes as C join outcomes as O on C.class=O.ship
union
select C.class, S.name from classes as C join ships as S on C.class=S.class) as
X group by X.class
having count(X.class)>=3 ) group by class

Задание: 58

Для каждого типа продукции и каждого производителя из таблицы
Product c точностью до двух десятичных знаков найти процентное отношение числа
моделей данного типа данного производителя к общему числу моделей этого
производителя. Вывод: maker, type, процентное отношение числа моделей данного
типа к общему числу моделей производителя

select main_maker ,main_type
,CONVERT(NUMERIC(6,2),((sub_num*100.00)/(total_num*100.00)*100.00))
from (select count(p5.model) total_num ,p5.maker main_maker
from product p5 group by p5.maker) p6 JOIN (select p3.maker
sub_maker ,p3.type main_type ,count(p4.model) sub_num
from (select p1.maker maker, p2.type type from product p1 cross join
product p2 group by p1.maker, p2.type) p3 left join product p4 on p3.maker
= p4.maker and p3.type = p4.type group by p3.maker,p3.type) p7 ON
p7.sub_maker = p6.main_maker

Задание: 59

select a.point, case when o is
null then i else i-o end remain FROM (select point,
sum(inc) as i
from Income_o group by point) as A left join (select point,
sum(out) as o from Outcome_o group by point) as B on
A.point=B.point

Задание:
59 > Вариант 2

Посчитать остаток
денежных средств на каждом пункте приема для базы данных с отчетностью не чаще
одного раза в день. Вывод: пункт, остаток.

select A.point, (COALESCE (si, 0) —
COALESCE (so, 0) ) from (select point, sum(inc) as si
from income_o as i group by point) as A full join (select point,
sum(out) as so from outcome_o as o
group by point) as B on A.point=B.point

Задание: 60

Посчитать
остаток денежных средств на начало дня 15/04/01 на каждом пункте приема для
базы данных с отчетностью не чаще одного раза в день. Вывод: пункт,
остаток. Замечание. Не учитывать пункты, информации о которых нет до
указанной даты.

select a.point, case when o is
null then i else i-o end remain FROM (select point,
sum(inc) as i
from Income_o where ‘20010415’ > date group by point) as
A left join (select point, sum(out) as o
from Outcome_o where ‘20010415’ > date group by point) as B on
A.point=B.point

Задание: 61

select (select sum(inc) from income_o) — (select
sum(out) from outcome_o) as remain

Задание:
62

Посчитать остаток денежных средств на всех пунктах приема на
начало дня 15/04/01 для базы данных с отчетностью не чаще одного раза в день.

select (select sum(inc) from
income_o where ‘20010415’ > date)
—
(select sum(out) from outcome_o where ‘20010415’ > date) as remain

Задание: 63

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

select name from Passenger where
ID_psg in(Select Left([ol],CHARINDEX ( ‘ ‘, ol)) from (
Select CAST(concat(ID_psg,’ ‘, place) AS VARCHAR(30)) as ol, trip_no as
o, ID_psg as psg
from Pass_in_trip ) as lll group by ol having count(o)>1)

Задание: 64

Используя
таблицы Income и Outcome, для каждого пункта приема определить дни, когда был
приход, но не было расхода и наоборот.Вывод: пункт, дата, тип операции
(inc/out), денежная сумма за день

Select income.point, income.date, ‘inc’ as
operation, sum(income.inc)
from income left join outcome on income.point=outcome.point
and income.date=outcome.date
where outcome.date is null group by income.point, income.date
union
Select outcome.point, outcome.date, ‘out’ as operation, sum(outcome.out)
from income right join outcome on income.point=outcome.point
and income.date=outcome.date
where income.date is null group by outcome.point, outcome.date

Задание: 67

Найти
количество маршрутов, которые обслуживаются наибольшим числом
рейсов. Замечания. 1) A — B и B — A считать РАЗНЫМИ
маршрутами. 2) Использовать только таблицу Trip

select count(qqq) as
qty from ( select town_from as qqq, town_to, count(plane) as
cp from Trip
group by town_from, town_to having count(plane) >= all(select
count(plane) from Trip
group by town_from, town_to) ) as tab

Задание: 71

Найти
тех производителей ПК, все модели ПК которых имеются в таблице PC.

select p.maker from product p where
p.type=’pc’ group by p.maker having count(DISTINCT p.model) =
( select count(DISTINCT pc.model) from pc where pc.model in
( select DISTINCT pr.model from product pr where
pr.maker=p.maker ))

Задание: 74

Вывести
классы всех кораблей России (Russia). Если в базе данных нет классов кораблей
России, вывести классы для всех имеющихся в БД стран. Вывод: страна, класс

select c.country, c.class from classes
c where c.country like (case when (select count(*) from
classes c
where c.country=’Russia’ group by c.country) is not null THEN (‘Russia’)
else (‘%’) end)

Задание: 80

Найти
производителей компьютерной техники, у которых нет моделей ПК, не
представленных в таблице PC.

select distinct maker from product
where maker not in ( select maker from product where
model in (
select model from product where type=’pc’ except select model from
pc ) )

Задание: 85

Найти производителей, которые выпускают только принтеры или
только PC. При этом искомые производители PC должны выпускать не менее 3
моделей.

select maker from ( select maker
from product where type=’printer’ except
select maker from product where type=’laptop’ except select maker
from product where type=’pc’ ) as T
union
select maker from ( select maker from product inner join pc on
pc.model=product.model group by maker
having count(maker)>=3 except select maker from product where
type=’laptop’ except
select maker from product where type=’printer’ ) as S

Задание: 89

Найти
производителей, у которых больше всего моделей в таблице Product, а также тех,
у которых меньше всего моделей. Вывод: maker, число моделей

select maker, count(maker) from
product group by maker having count(maker) in (
select max(D.cnt) from ( select maker, count(maker) as cnt from
product group by maker ) as D
union
select min(F.cnt) from ( select maker, count(maker) as cnt from
product group by maker ) as F )

Задание: 91

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

select count(*)
from ( select maker from product group by
maker having count(model)=1 ) as Q

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.

Пожалуйста, не используйте программу, если вы не понимаете ее назначения.

Если наше приложение окажется для вас полезным, поставьте нам оценку 🌟🌟🌟🌟🌟

Источник

Задачи по базам данных. Решение задач по SQL [1]

Задание: 1

Найдите номер модели, скорость и размер жесткого диска для всех ПК стоимостью менее 500 дол. Вывести: model, speed и hd

Select model , speed, hd From pc Where price < 500

Задание: 2

Найдите производителей принтеров. Вывести: maker

Select maker from product where product.type = ‘printer’ group by maker

Задание: 3

Найдите номер модели, объем памяти и размеры экранов ПК-блокнотов, цена которых превышает 1000 дол.

select model , ram , screen from laptop where price > 1000

Задание: 4

Найдите все записи таблицы Printer для цветных принтеров.

select * from printer where color = ‘y’

Задание: 5

Найдите номер модели, скорость и размер жесткого диска ПК, имеющих 12x или 24x CD и цену менее 600 дол.

Select model ,speed , hd from pc where (cd = ’12x’ or cd = ’24x’) and price < 600

Задание: 6

Укажите производителя и скорость для тех ПК-блокнотов, которые имеют жесткий диск объемом не менее 10 Гбайт.

Select maker, speed from Product inner join Laptop on Product.model = Laptop.model

where hd >= 10

Задание: 7

Найдите номера моделей и цены всех продуктов (любого типа), выпущенных производителем B (латинская буква).

Select laptop.model , laptop.price from laptop inner join product on laptop.model = product.model

where product.maker= ‘B’

union

Select pc.model , pc.price from pc inner join product on pc.model = product.model

where product.maker= ‘B’

union

Select printer.model , printer.price from printer inner join product on printer.model = product.model

where product.maker= ‘B’

Задание: 8

Найдите производителя, выпускающего ПК, но не ПК-блокноты.

select maker from product where type=’PC’ and maker not in

( select maker from product where type = ‘Laptop’) group by maker

Задание: 9

Найдите производителей ПК с процессором не менее 450 Мгц. Вывести: Maker

Select maker from pc inner join product on pc.model = product.model where speed >= 450

group by maker

Задание: 10

Найдите принтеры, имеющие самую высокую цену. Вывести: model, price

select model, price from printer where price = (select max(price) from printer)

Задание: 11

Найдите среднюю скорость ПК.

select avg (speed) from pc

Задание: 12

Найдите среднюю скорость ПК-блокнотов, цена которых превышает 1000 дол.

Select avg(speed) from laptop where price > 1000

Задание: 13

Найдите среднюю скорость ПК, выпущенных производителем A

Select avg(speed) from pc inner join product on pc.model= product.model where maker = ‘A’

group by maker

Задание: 14

Для каждого значения скорости найдите среднюю стоимость ПК с такой же скоростью процессора. Вывести: скорость, средняя цена

Select speed , avg(price) from pc group by speed

Задание: 14

Найти производителей, которые выпускают более одной модели, при этом все выпускаемые производителем модели являются продуктами одного типа.Вывести: maker, type

select maker ,type from Product

where maker in ( select maker

from ( select maker,type from Product group by maker,type ) x

group by maker having count(*)=1 )

group by maker,type having count(*)>1

Задание: 15

Найдите размеры жестких дисков, совпадающих у двух и более PC. Вывести: HD

Select hd from pc group by hd having count(model)>1

Задание: 16

Найдите пары моделей PC, имеющих одинаковые скорость и RAM. В результате каждая пара указывается только один раз, т.е. (i,j), но не (j,i), Порядок вывода: модель с большим номером, модель с меньшим номером, скорость и RAM.

SELECT DISTINCT B.model AS model, A.model AS model, A.speed, A.ram

FROM PC AS A, PC B

WHERE A.speed = B.speed AND A.ram = B.ram and A.model < B.model

Задание: 17

Найдите модели ПК-блокнотов, скорость которых меньше скорости любого из ПК. Вывести: type, model, speed

Select distinct type,laptop.model,speed from laptop inner join product on laptop.model= product.model

where speed < (select MIN(speed) from pc)

Задание: 18

Найдите производителей самых дешевых цветных принтеров. Вывести: maker, price

SELECT DISTINCT maker,price FROM printer inner JOIN product ON printer.model= product.model

WHERE price = (select min(price)from printer where color = ‘y’ ) and color = ‘y’

Задание: 19

Для каждого производителя найдите средний размер экрана выпускаемых им ПК-блокнотов. Вывести: maker, средний размер экрана.

Select maker ,avg(screen)as Avg_screen

from laptop inner join product on laptop.model = product.model group by maker

Задание: 20

Найдите производителей, выпускающих по меньшей мере три различных модели ПК. Вывести: Maker, число моделей

Select maker , count(model) as Count_Model from product where type = ‘pc’ group by maker

having count(model) >= 3

Задание: 21

Найдите максимальную цену ПК, выпускаемых каждым производителем. Вывести: maker, максимальная цена.

Select maker , max(price)as Max_price from pc inner join product on pc.model= product.model

group by maker

Задание: 22

Для каждого значения скорости ПК, превышающего 600 МГц, определите среднюю цену ПК с такой же скоростью. Вывести: speed, средняя цена.

Select speed , avg(price) as Avg_price from pc where speed > 600 group by speed

Задание: 23

Найдите производителей, которые производили бы как ПКсо скоростью не менее 750 МГц, так и ПК-блокноты со скоростью не менее 750 МГц.Вывести: Maker

select distinct maker from pc inner join product on pc.model = product.model

where pc.speed >= 750 and maker in (select maker

from laptop inner join product on laptop.model = product.model where laptop.speed >= 750)

Задание: 24

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

SELECT model FROM(

SELECT distinct model, price FROM laptop WHERE laptop.price = (SELECT MAX(price) FROM laptop)

UNION

SELECT distinct model, price FROM pc WHERE pc.price = (SELECT MAX(price) FROM pc)

UNION

SELECT distinct model, price FROM printer WHERE printer.price = (SELECT MAX(price) FROM printer)

) as t

WHERE t.price=(SELECT MAX(price) FROM (

SELECT distinct price FROM laptop WHERE laptop.price = (SELECT MAX(price) FROM laptop)

UNION

SELECT distinct price FROM pc WHERE pc.price = (SELECT MAX(price) FROM pc)

UNION

SELECT distinct price FROM printer WHERE printer.price = (SELECT MAX(price) FROM printer)

) as t1 )

Задание: 25

Найдите производителей принтеров, которые производят ПК с наименьшим объемом RAM и с самым быстрым процессором среди всех ПК,имеющих наименьший объем RAM. Вывести: Maker

SELECT distinct product.maker FROM product WHERE product.type=’Printer’

INTERSECT

SELECT distinct product.maker FROM product INNER JOIN pc ON pc.model=product.model

WHERE product.type=’PC’ AND pc.ram=(SELECT MIN(ram) FROM pc)

AND pc.speed = (SELECT MAX(speed) FROM (SELECT distinct speed FROM pc

WHERE pc.ram=(SELECT MIN(ram) FROM pc)) as t)

Задание: 26

Найдите среднюю цену ПК и ПК-блокнотов, выпущенных производителем A (латинская буква). Вывести: одна общая средняя цена.

SELECT t1.c/t1.d FROM( SELECT SUM(t.a) as c, SUM(t.b) as d FROM(

SELECT SUM(pc.price) as a, COUNT(pc.code) as b FROM pc

INNER JOIN product ON pc.model=product.model WHERE product.maker=’A’

UNION

SELECT SUM(laptop.price) as a, COUNT(laptop.code) as b FROM laptop

INNER JOIN product ON laptop.model=product.model WHERE product.maker=’A’) as t) as t1

Задание: 27

Найдите средний размер диска ПК каждого из тех производителей, которые выпускают и принтеры. Вывести: maker, средний размер HD.

select maker,avg(hd) from product inner join pc on product.model=pc.model

where maker in(select maker from product where type=’printer’) group by maker

Задание: 28

Найдите средний размер диска ПК (одно значение для всех) тех производителей, которые выпускают и принтеры. Вывести: средний размер HD

select avg(hd) from product inner join pc on product.model = pc.model

where maker in(select maker from product where type=’printer’)

Задание: 29

В предположении, что приход и расход денег на каждом пункте приема фиксируется не чаще одного раза в день [т.е. первичный ключ (пункт, дата)], написать запрос с выходными данными (пункт, дата, приход, расход). Использовать таблицы Income_o и Outcome_o.

select t.point, t.date, SUM(t.inc), sum(t.out) from( select point, date, inc, null as out from Income_o

Union

select point, date, null as inc, Outcome_o.out from Outcome_o) as t group by t.point, t.date

Задание: 30

В предположении, что приход и расход денег на каждом пункте приема фиксируется произвольное число раз (первичным ключом в таблицах является столбец code), требуется получить таблицу, в которой каждому пункту за каждую дату выполнения операций будет соответствовать одна строка.Вывод: point, date, суммарный расход пункта за день (out), суммарный приход пункта за день (inc).Отсутствующие значения считать неопределенными (NULL).

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

Задание: 31

Для классов кораблей, калибр орудий которых не менее 16 дюймов, укажите класс и страну.

Select class , country from classes where bore >= 16

Задание: 32

Одной из характеристик корабля является половина куба калибра его главных орудий (mw). С точностью до 2 десятичных знаков определите среднее значение mw для кораблей каждой страны, у которой есть корабли в базе данных.

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!=’null’ group by country

Задание: 32 > вариант 2

select country, cast(avg(bore*bore*bore/2) AS NUMERIC(6,2)) as mw from (

select C.class, S.name, C.country, C.bore from classes as c join ships as s on c.class=s.class

union

select C.class, O.ship, C.country, C.bore from classes as c join outcomes as o on c.class=o.ship ) as G

group by country

Задание: 33

Укажите корабли, потопленные в сражениях в Северной Атлантике (North Atlantic). Вывод: ship.

Select ship from outcomes,battles where result= ‘sunk’ and battle = ‘North Atlantic’ group by ship

Задание: 34

По Вашингтонскому международному договору от начала 1922 г. запрещалось строить линейные корабли водоизмещением более 35 тыс.тонн. Укажите корабли, нарушившие этот договор (учитывать только корабли c известным годом спуска на воду). Вывести названия кораблей.

Select name from classes,shipswhere launched>=1922 and displacement>35000 and type=’bb’ and

ships.class = classes.class

Задание: 35

В таблице Product найти модели, которые состоят только из цифр или только из латинских букв (A-Z, без учета регистра).Вывод: номер модели, тип модели.

SELECT model, type FROM product

WHERE model NOT LIKE ‘%[^0-9]%’ OR model NOT LIKE ‘%[^a-z]%’

Задание: 36

Перечислите названия головных кораблей, имеющихся в базе данных (учесть корабли в Outcomes).

Select name from ships where class = name

union

select ship as name from classes,outcomes where classes.class = outcomes.ship

Задание: 37

Найдите классы, в которые входит только один корабль из базы данных (учесть также корабли в Outcomes).

Select class from(select name,class from ships

union

select class as name,class from classes,outcomes where classes.class=outcomes.ship) A

group by class having count(A.name)=1

Задание: 38

Найдите страны, имевшие когда-либо классы обычных боевых кораблей (‘bb’) и имевшие когда-либо классы крейсеров (‘bc’).

Select distinct country from classes where type=’bb’

intersect

Select distinct country from classes where type=’bc’

Задание: 39 >

Найдите корабли, «сохранившиеся для будущих сражений»; т.е. выведенные из строя в одной битве (damaged), они участвовали в другой, произошедшей позже.

select distinct ccc.sh from ( select aaa.ship as sh, aaa.[date] as d1, bbb.[date] as d2 from (

select ship, [date] from outcomes as o inner join battles as b on o.battle=b.name where result = ‘damaged’) as aaa inner join (select ship,

[date] from outcomes as o inner join battles as b on o.battle=b.name) as bbb on aaa.ship=bbb.ship

where bbb.date > aaa.date) as ccc

Вариант 2 : Задание: 39

select distinct B.ship

from(select * from outcomes left join battles on battle=name where result=’damaged’)as B

where exists (select shipfrom outcomes left join battles on battle=name

where ship=B.ship and B.date<date)

Задание: 40

Найдите класс, имя и страну для кораблей из таблицы Ships, имеющих не менее 10 орудий.

Select classes.class , name,country from classes inner join ships on classes.class = ships.class

where numguns >= 10

Задание: 41

Для ПК с максимальным кодом из таблицы PC вывести все его характеристики (кроме кода) в два столбца:- название характеристики (имя соответствующего столбца в таблице PC);- значение характеристики

select ‘speed’ as m, CAST(speed as char) as a from pc where code >= all(select code from pc)

union

select ‘model’ as m, CAST(model as char) as a from pc where code >= all(select code from pc)

union

select ‘ram’ as m, CAST(ram as char) as a from pc where code >= all(select code from pc)

union

select ‘hd’ as m, CAST(hd as char) as a from pc where code >= all(select code from pc)

union

select ‘cd’ as m, CAST(cd as char) as a from pc where code >= all(select code from pc)

union

select ‘price’ as m, CAST(price as char) as a from pc where code >= all(select code from pc)

Вариант 2 : Задание: 41

select characteristics, value

from (select

cast(model as varchar(max)) as model,

cast(speed as varchar(max)) as speed,

cast(ram as varchar(max)) as ram,

cast(hd as varchar(max)) as hd,

cast(cd as varchar(max)) as cd,

cast(price as varchar(max)) as price

from pc where code in (select max(code) from pc)) as A

unpivot(value for characteristics in (model, speed, ram, hd, cd, price)) as unpvt

Задание: 42

Найдите названия кораблей, потопленных в сражениях, и название сражения, в котором они были потоплены.

Select ship,battle from outcomes where result =’sunk’

Задание: 43

Укажите сражения, которые произошли в годы, не совпадающие ни с одним из годов спуска кораблей на воду.

select name from battles where DATEPART(yy, date) not in (select DATEPART(yy, date)

from battles join ships on DATEPART(yy, date)=launched)

Задание: 44

Найдите названия всех кораблей в базе данных, начинающихся с буквы R.

Select name from ships where name like ‘R%’

union

Select name from battles where name like ‘R%’

union

Select ship from outcomes where ship like ‘R%’

Задание: 45

Найдите названия всех кораблей в базе данных, состоящие из трех и более слов (например, King George V). Считать, что слова в названиях разделяются единичными пробелами, и нет концевых пробелов.

Select name from ships where name like ‘% % %’

union

Select ship from outcomes where ship like ‘% % %’

Задание: 46

Укажите названия, водоизмещение и число орудий кораблей, участвовавших в сражении при Гвадалканале (Guadalcanal).

select name as n, displacement as d, numguns as ng from ships inner join classes on ships.class=classes.class where name in (select ship from outcomes where battle = ‘Guadalcanal’)

union

select ship as n, displacement as d, numguns as ng from outcomes inner join classes on outcomes.ship=classes.class where battle = ‘Guadalcanal’ and ship not in (select name from ships)

union

select ship as n, null as d, null as ng from outcomes where battle = ‘Guadalcanal’ and ship not in (select name from ships) and ship not in (select class from classes)

Задание: 47

Пронумеровать строки из таблицы Product в следующем порядке: имя производителя в порядке убывания числа производимых им моделей (при одинаковом числе моделей имя производителя в алфавитном порядке по возрастанию), номер модели (по возрастанию).Вывод: номер в соответствии с заданным порядком, имя производителя (maker), модель (model)

select ROW_NUMBER() OVER(ORDER BY co desc, m, model) no, m, model

from ( Select one.maker as m, model, co

from product as one join (Select maker, count(model) as co from product group by maker) as two on one.maker=two.maker ) as ddd

Задание: 48

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

Select class as n from ships where name in(select ship from outcomes where result=’sunk’)

union

Select ship as n from outcomes

where ship not in(Select name from ships) and ship in(Select class from classes) and result=’sunk’

Задание: 49

Найдите названия кораблей с орудиями калибра 16 дюймов (учесть корабли из таблицы Outcomes).

select name from ships where class in( Select class from classes where bore=16)

union

select ship from outcomes where ship in( Select class from classes where bore=16)

Задание: 50

Найдите сражения, в которых участвовали корабли класса Kongo из таблицы Ships.

SELECT distinct battle FROM Classes inner JOIN Ships ON ships.class = classes.class

inner JOIN Outcomes ON Classes.class=Outcomes.ship or Ships.name=Outcomes.ship

WHERE classes.class = ‘Kongo’

Задание: 51

Найдите названия кораблей, имеющих наибольшее число орудий среди всех имеющихся кораблей такого же водоизмещения (учесть корабли из таблицы Outcomes).

select NAME from(select name as NAME, displacement, numguns

from ships inner join classes on ships.class = classes.class

union

select ship as NAME, displacement, numguns from outcomes inner join classes on outcomes.ship= classes.class) as d1 inner join (select displacement, max(numGuns) as numguns from ( select displacement, numguns from ships inner join classes on ships.class = classes.class

union

select displacement, numguns from outcomes inner join classes on outcomes.ship= classes.class) as f

group by displacement) as d2 on d1.displacement=d2.displacement and d1.numguns =d2.numguns

Задание: 52

Определить названия всех кораблей из таблицы Ships, которые могут быть линейным японским кораблем, имеющим число главных орудий не менее девяти, калибр орудий менее 19 дюймов и водоизмещение не более 65 тыс.тонн

Select distinct name from ships inner join classes cl on ships.class=cl.class

where (numGuns>=9 or numguns is NULL) and (bore<19 or bore is NULL) and (displacement<=65000 or displacement is NULL) and type=’bb’ and country=’japan’

Задание: 53

Определите среднее число орудий для классов линейных кораблей.Получить результат с точностью до 2-х десятичных знаков.

select cast(avg(numguns*1.0) as numeric(4,2)) as Avg_numGuns from classes where type=’bb’

Задание: 54

С точностью до 2-х десятичных знаков определите среднее число орудий всех линейных кораблей (учесть корабли из таблицы Outcomes).

SELECT CAST(AVG(numguns*1.0) AS NUMERIC (4,2)) as AVG_nmg

FROM (SELECT ship, type, numguns FROM Outcomes LEFT JOIN Classes ON ship = class

UNION

SELECT name, type, numguns FROM Ships as S INNER JOIN Classes as C ON c.class = s.class ) AS T

WHERE type = ‘bb’

Задание: 55

Для каждого класса определите год, когда был спущен на воду первый корабль этого класса. Если год спуска на воду головного корабля неизвестен, определите минимальный год спуска на воду кораблей этого класса. Вывести: класс, год.

select C.class, min(launched) from ships as S right join classes as C on s.class=c.class group by C.class

Задание: 56

Для каждого класса определите число кораблей этого класса, потопленных в сражении. Вывести: класс и число потопленных кораблей.

select classes.class, count(T.ship) from classes left join(select ship, class from outcomes left join ships on ship=name where result=’sunk’union select ship, class from outcomes left join classes on ship=class where result=’sunk’) as T on classes.class=T.classgroup by classes.class

Задание: 57

Для классов, имеющих потери в виде потопленных кораблей и не менее 3 кораблей в базе данных, вывести имя класса и число потопленных кораблей.

select class as cls, count(class) as sunked from(

select C.class, O.ship from classes as C join outcomes as O on C.class=O.ship where O.result=’sunk’

union

select S.class, O.ship from outcomes as O join ships as S on S.name=O.ship where O.result=’sunk’) as T

where class in ( select distinct X.class from (select C.class, O.ship from classes as C join outcomes as O on C.class=O.ship

union

select C.class, S.name from classes as C join ships as S on C.class=S.class) as X group by X.class

having count(X.class)>=3 ) group by class

Задание: 58

Для каждого типа продукции и каждого производителя из таблицы Product c точностью до двух десятичных знаков найти процентное отношение числа моделей данного типа данного производителя к общему числу моделей этого производителя. Вывод: maker, type, процентное отношение числа моделей данного типа к общему числу моделей производителя

select main_maker ,main_type ,CONVERT(NUMERIC(6,2),((sub_num*100.00)/(total_num*100.00)*100.00))

from (select count(p5.model) total_num ,p5.maker main_maker

from product p5 group by p5.maker) p6 JOIN (select p3.maker sub_maker ,p3.type main_type ,count(p4.model) sub_num

from (select p1.maker maker, p2.type type from product p1 cross join product p2 group by p1.maker, p2.type) p3 left join product p4 on p3.maker = p4.maker and p3.type = p4.type group by p3.maker,p3.type) p7 ON p7.sub_maker = p6.main_maker

Задание: 59

Посчитать остаток денежных средств на каждом пункте приема для базы данных с отчетностью не чаще одного раза в день. Вывод: пункт, остаток.

select a.point, case when o is null then i else i-o end remain FROM (select point, sum(inc) as i

from Income_o group by point) as A left join (select point, sum(out) as o from Outcome_o group by point) as B on A.point=B.point

Задание: 59 > Вариант 2

select A.point, (COALESCE (si, 0) — COALESCE (so, 0) ) from (select point, sum(inc) as si

from income_o as i group by point) as A full join (select point, sum(out) as so from outcome_o as o

group by point) as B on A.point=B.point

Задание: 60

Посчитать остаток денежных средств на начало дня 15/04/01 на каждом пункте приема для базы данных с отчетностью не чаще одного раза в день. Вывод: пункт, остаток. Замечание. Не учитывать пункты, информации о которых нет до указанной даты.

select a.point, case when o is null then i else i-o end remain FROM (select point, sum(inc) as i

from Income_o where ‘20010415’ > date group by point) as A left join (select point, sum(out) as o

from Outcome_o where ‘20010415’ > date group by point) as B on A.point=B.point

Задание: 61

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

select (select sum(inc) from income_o) — (select sum(out) from outcome_o) as remain

Задание: 62

Посчитать остаток денежных средств на всех пунктах приема на начало дня 15/04/01 для базы данных с отчетностью не чаще одного раза в день.

select (select sum(inc) from income_o where ‘20010415’ > date)

—

(select sum(out) from outcome_o where ‘20010415’ > date) as remain

Задание: 63

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

select name from Passenger where ID_psg in(Select Left([ol],CHARINDEX ( ‘ ‘, ol)) from (

Select CAST(concat(ID_psg,’ ‘, place) AS VARCHAR(30)) as ol, trip_no as o, ID_psg as psg

from Pass_in_trip ) as lll group by ol having count(o)>1)

Задание: 64

Используя таблицы Income и Outcome, для каждого пункта приема определить дни, когда был приход, но не было расхода и наоборот.Вывод: пункт, дата, тип операции (inc/out), денежная сумма за день

Select income.point, income.date, ‘inc’ as operation, sum(income.inc)

from income left join outcome on income.point=outcome.point and income.date=outcome.date

where outcome.date is null group by income.point, income.date

union

Select outcome.point, outcome.date, ‘out’ as operation, sum(outcome.out)

from income right join outcome on income.point=outcome.point and income.date=outcome.date

where income.date is null group by outcome.point, outcome.date

Задание: 67

Найти количество маршрутов, которые обслуживаются наибольшим числом рейсов. Замечания. 1) A — B и B — A считать РАЗНЫМИ маршрутами. 2) Использовать только таблицу Trip

select count(qqq) as qty from ( select town_from as qqq, town_to, count(plane) as cp from Trip

group by town_from, town_to having count(plane) >= all(select count(plane) from Trip

group by town_from, town_to) ) as tab

Задание: 71

Найти тех производителей ПК, все модели ПК которых имеются в таблице PC.

select p.maker from product p where p.type=’pc’ group by p.maker having count(DISTINCT p.model) = ( select count(DISTINCT pc.model) from pc where pc.model in ( select DISTINCT pr.model from product pr where pr.maker=p.maker ))

Задание: 74

Вывести классы всех кораблей России (Russia). Если в базе данных нет классов кораблей России, вывести классы для всех имеющихся в БД стран. Вывод: страна, класс

select c.country, c.class from classes c where c.country like (case when (select count(*) from classes c

where c.country=’Russia’ group by c.country) is not null THEN (‘Russia’) else (‘%’) end)

Задание: 80

Найти производителей компьютерной техники, у которых нет моделей ПК, не представленных в таблице PC.

select distinct maker from product where maker not in ( select maker from product where model in (

select model from product where type=’pc’ except select model from pc ) )

Задание: 85

Найти производителей, которые выпускают только принтеры или только PC. При этом искомые производители PC должны выпускать не менее 3 моделей.

select maker from ( select maker from product where type=’printer’ except

select maker from product where type=’laptop’ except select maker from product where type=’pc’ ) as T

union

select maker from ( select maker from product inner join pc on pc.model=product.model group by maker

having count(maker)>=3 except select maker from product where type=’laptop’ except

select maker from product where type=’printer’ ) as S

Задание: 89

Найти производителей, у которых больше всего моделей в таблице Product, а также тех, у которых меньше всего моделей. Вывод: maker, число моделей

select maker, count(maker) from product group by maker having count(maker) in (

select max(D.cnt) from ( select maker, count(maker) as cnt from product group by maker ) as D

union

select min(F.cnt) from ( select maker, count(maker) as cnt from product group by maker ) as F )

Задание: 91

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

select count(*) from ( select maker from product group by maker having count(model)=1 ) as Q

[ ссылка на описание задач и тестовые БД ]

Источник