Exercise #6 (tips and solutions) | Interactive tutorial on SQL | SQL-tutorial.ru

Изучение программирования. SQL


Sql ex решения — Запросы SQL (10 и 1) — 2 ответа



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

Источник: http://artvk.blogspot.ru/2014/01/sql.html

Опубликовано: 02.01.2018 | Автор: Ариадна

Рейтинг статьи: 5

Всего 8 комментариев.


23.01.2018 Светлана:
SQL-EX.ru решения.Если у Вас возникли непредсказуемые трудности или Вы обнаружили ошибки в решениях - добро пожаловать в обсуждение обучающего этапа.

15.01.2018 Агния:
Помощь в решении заданий ресурса sql-ex.ru и многое другое.Ответы на тесты sql-ex.ru 1-57. 1. SELECT model, speed, hd FROM PC where price<500.

10.01.2018 Тит:
Задачи по SQL запросам. Задание: 1 (Serge I: 2002-09-30). Найдите номер модели, скорость и размер жесткого диска для всех ПК стоимостью менее 500 дол. Вывести: model, speed и hd.

10.01.2018 Эмма:
Задание: 1. Найдите номер модели, скорость и размер жесткого диска для всех ПК стоимостью менее 500 дол. Вывести: model, speed и hd. Select model, speed, hd From pc Where price < 500.

03.01.2018 Андрон:
Поддержать проект SQL-EX.RU.Наша книга: "SQL. Задачи и решения". Изд-во Питер Анализ характерных ошибок при решении задач обучающего этапа на сайте.

09.01.2018 Гостомысл:
Упражнение 6 (подсказки и решения). Соединение таблиц (а здесь необходимо внутреннееsql SQL Server SQL Server 2012 SQL-92 sql-ex.ru Substring SUM Trip Truncate Table Union.