Ответы на тесты sql-ex.ru 1-57

1.

SELECT model, speed, hd
FROM PC
where price<500

2. 


SELECT DISTINCT maker
FROM product
where type='Printer'

3.



SELECT model, ram, screen
from Laptop
where price>1000

4.



Select *from printer
where color='y'

5.



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

6.



SELECT maker, speed
FROM laptop l, Product p
WHERE l.model = p.model
AND l.hd >=10

7.



SELECT p.model, price
FROM Product p, PC pp
WHERE p.model=pp.model
AND maker='B'
UNION
SELECT p.model, price
FROM Product p, printer pp
WHERE p.model=pp.model
AND maker='B'
UNION
SELECT p.model, price
FROM Product p, laptop pp
WHERE p.model=pp.model
AND maker='B'

8.



select distinct product.maker
from product
where product.type='PC' and
product.maker not in (
select product.maker
from product
where product.type = 'Laptop')

9.



SELECT DISTINCT maker
FROM product p,PC
WHERE p.model=pc.model
AND pc.speed>449

10.



SELECT model, price
FROM Printer
WHERE price = (SELECT MAX(price)
FROM printer)

11.



SELECT AVG(speed)
FROM PC

12.



SELECT AVG(speed)
FROM laptop
WHERE price > 1000

13.



select sum(pc.speed) / count(*)
from pc
where pc.model in (select product.model from product
where product.maker = 'A')

14.



SELECT speed, AVG(price)
FROM PC
GROUP BY speed

15.



select distinct hd from pc
where exists(select 'x' from pc p
where p.code <> pc.code
and p.hd = pc.hd)

16.



SELECT DISTINCT a.model, b.model, a.speed, a.ram
FROM pc a, pc b
WHERE a.ram = b.ram
AND a.speed = b.speed
AND a.model > b.model

17.




SELECT DISTINCT p.type, l.model, l.speed
FROM laptop l, product p
WHERE speed < ALL (SELECT speed FROM PC)
AND l.model=p.model

18.



select distinct product.maker, printer.price
from product, printer
where product.model = printer.model
and printer.color = 'Y'
and printer.price = (select min(p.price)
from printer p
where p.color = 'Y')

19.



select product.maker, sum(laptop.screen) / count(laptop.model)
from product, laptop
where product.type = 'Laptop'
and product.model = laptop.model
group by product.maker

20.



select product.maker, count(*)
from product
where product.type = 'PC'
group by product.maker
having count(*) >= 3

21.



select product.maker, max(pc.price)
from product, pc
where product.model = pc.model
and product.type = 'PC'
group by product.maker

22.



Select speed, avg(price) from pc
group by pc.speed
having (speed>600)

23.



select distinct product.maker
from product, pc
where product.model = pc.model
and product.type = 'PC'
and pc.speed >= 750
and exists(select 'x' from laptop, product p
where p.model = laptop.model
and p.type = 'Laptop'
and p.maker = product.maker
and laptop.speed >= 750)

24.



select distinct product.model
from product, pc, laptop, printer
where /*product.model in(pc.model, laptop.model, printer.model)
and*/ pc.price = (select max(pcc.price) from pc pcc)
and laptop.price = (select max(l.price) from laptop l)
and printer.price = (select max(pr.price) from printer pr)
and (
(pc.price >= laptop.price and pc.price >= printer.price
and product.model = pc.model)
or
(laptop.price >= pc.price and laptop.price >= printer.price
and product.model = laptop.model)
or
(printer.price >= laptop.price and printer.price >= pc.price
and product.model = printer.model)
)

25.



select distinct product.maker
from product, pc
where product.type = 'PC'
and product.model = pc.model
and pc.ram = (select min(pcc.ram) from pc pcc where pcc.ram <> 0)
and pc.speed = (select max(pccc.speed) from pc pccc
where pccc.ram = (select min(pcc.ram) from pc pcc where pcc.ram <> 0))
and exists(select 'x' from product p
where p.type = 'Printer'
and p.maker = product.maker)

26.



SELECT AVG(price) FROM (
SELECT price FROM pc WHERE model IN
(SELECT model FROM product WHERE maker='A' AND type='PC')
UNION ALL
SELECT price FROM laptop WHERE model IN
(SELECT model FROM product WHERE maker='A' AND type='Laptop')
) as prod

27.



select product.maker, sum(pc.hd) / count(*)
from product, pc
where product.type = 'PC'
and product.model = pc.model
and exists(select 'x' from product p
where p.maker = product.maker
and p.type = 'Printer')
group by product.maker

28.



select sum(pc.hd) / count(*)
from product, pc
where product.type = 'PC'
and product.model = pc.model
and exists(select 'x' from product p
where p.maker = product.maker
and p.type = 'Printer')

29.



SELECT Income_o.point, Income_o.date, SUM(inc),SUM(out)
FROM Income_o LEFT JOIN
Outcome_o ON Income_o.point = Outcome_o.point AND
Income_o.date = Outcome_o.date
GROUP BY Income_o.point, Income_o.date
UNION
SELECT Outcome_o.point, Outcome_o.date, SUM(inc),SUM(out)
FROM Outcome_o LEFT JOIN
Income_o ON Income_o.point = Outcome_o.point AND
Income_o.date = Outcome_o.date
GROUP BY Outcome_o.point, Outcome_o.date

30.



SELECT DISTINCT point,date,SUM(out) AS out, SUM(inc) AS inc FROM (
SELECT Income.point, Income.date, out, inc
FROM Income LEFT JOIN
Outcome ON Income.point = Outcome.point AND
Income.date = Outcome.date AND Income.code= Outcome.code
UNION ALL
SELECT Outcome.point, Outcome.date, out, inc
FROM Outcome LEFT JOIN
Income ON Income.point = Outcome.point AND
Income.date = Outcome.date AND Income.code=Outcome.code) AS t1
GROUP BY point, date

31.



SELECT class, country
FROM Classes
WHERE bore>=16

32.



SELECT c.country, CAST(avg(c.bore*c.bore*c.bore/2) AS NUMERIC(6,2)) as weight
FROM
(
SELECT s.class, s.name
FROM Ships s
UNION
SELECT '' as 'class', o.ship
FROM Outcomes o
WHERE NOT EXISTS( SELECT * FROM Ships s WHERE s.name = o.ship)
) s
LEFT JOIN Classes c ON (c.class = s.class) OR (c.class = s.name)
WHERE c.country is not null
GROUP BY c.country

33.



SELECT ship
FROM Outcomes
WHERE battle='North Atlantic'
AND result='sunk'

34.



SELECT name
FROM Ships, Classes
WHERE launched >=1922
AND displacement >35000
AND Classes.class = Ships.class
AND type='bb'

35.



SELECT model, type, CONVERT(int,LEFT(model,1))*CONVERT(int,RIGHT(model, 1))as 'prod'
FROM Product
WHERE ((model LIKE '[02468]%') and (model LIKE '%[13579]')and (LEFT(model,1)<RIGHT(model,1)))

36.



SELECT distinct c.class
FROM
(
SELECT s.class, s.name
FROM Ships s


UNION


SELECT o.ship as 'class', o.ship
FROM Outcomes o
WHERE NOT EXISTS( SELECT * FROM Ships s WHERE s.name = o.ship)
) s
INNER JOIN Classes c ON (c.class = s.class) AND (c.class = s.name)

37.



SELECT c.class
FROM
(
SELECT s.class, s.name
FROM Ships s
UNION
SELECT o.ship as 'class', o.ship
FROM Outcomes o
WHERE NOT EXISTS( SELECT * FROM Ships s WHERE s.name = o.ship)
) s
INNER JOIN Classes c ON c.class = s.class
GROUP by c.class
HAVING count(*) = 1

38.



Select distinct country from Classes where type ='bb'
INTERSECT
Select distinct country from Classes where type ='bc'

39.



SELECT distinct o.ship
FROM outcomes o
LEFT JOIN Battles b ON b.name=o.battle
WHERE o.result = 'damaged'
and EXISTS(
SELECT *
FROM outcomes o2
LEFT JOIN Battles b2 ON b2.name=o2.battle
WHERE o2.ship=o.ship
and b2.date > b.date
)

40.



Select Ships.class, Ships.name, Classes.country
from Ships, Classes
where Ships.class=Classes.class and numGuns>=10

41.


SELECT 'model', cast(model as varchar(20))
FROM pc WHERE code = (SELECT max(code) FROM pc)
UNION ALL
SELECT 'speed', cast(speed as varchar(20))
FROM pc WHERE code = (SELECT max(code) FROM pc)
UNION ALL
SELECT 'ram', cast(ram as varchar(20))
FROM pc WHERE code = (SELECT max(code) FROM pc)
UNION ALL
SELECT 'hd', cast(hd as varchar(20))
FROM pc WHERE code = (SELECT max(code) FROM pc)
UNION ALL
SELECT 'cd', cast(cd as varchar(20))
FROM pc WHERE code = (SELECT max(code) FROM pc)
UNION ALL
SELECT 'price', cast(price as varchar(20))
FROM pc WHERE code = (SELECT max(code) FROM pc)

42.


SELECT o.ship, o.battle
FROM outcomes o
LEFT JOIN Battles b ON b.name=o.battle
WHERE o.result = 'sunk'

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.



Select name
from Ships
where name like '% % %'
union
Select ship
from Outcomes
where ship like '% % %'

46.





select name, displacement,numGuns
from outcomes
join (classes join ships on classes.class=ships.class) on ship=name
where battle='Guadalcanal'
union
select ship,displacement,numGuns
from outcomes
left join classes on ship=classes.class
where battle='Guadalcanal'
and ship not in (select name from ships)

48.





select class
from ships, outcomes
where outcomes.ship=Ships.name
and result='sunk'
union
select ship
from outcomes, classes
where classes.class=outcomes.ship
and result='sunk'

49.

select name

from ships, classes

where ships.class=classes.class

and bore=16

union

select ship

from outcomes, classes

where outcomes.ship=classes.class

and bore=16

50.


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.



Select name
from(Select name,numGuns,displacement
     from Ships JOIN Classes ON Classes.class=Ships.class
     union
     Select ship,numGuns,displacement
     from Outcomes JOIN Classes ON ship=class) as x
     where numGuns=(Select MAX(numGuns)
                   from(Select name,numGuns,displacement
                        from Ships JOIN Classes
                             ON Classes.class=Ships.class
                        union
                        Select ship,numGuns,displacement
                        from Outcomes JOIN Classes
                                      ON ship=class) AS y
                        where
                        x.displacement=y.displacement)

53.

select cast(avg(cast(NumGuns as numeric(5,2))) as numeric(5,2))

from classes

where type='bb'

54.

select cast(avg(numGuns*1.0) as numeric(6,2))

from (Select name,numGuns,type

      from Ships join Classes on Classes.class=Ships.class

union

select ship,numGuns,type

from Outcomes join Classes on ship=class) as xXx

where type='bb'

55.

select classes.class,min(launched)

from classes

full join ships on classes.class=ships.class

group by classes.class

56.

select class, sum(cast(sunks as numeric(6)))

from(select class,

         case

         when result='sunk' then '1' else '0'

         end as Sunks

from(select class,name,result

from outcomes join ships on ship=name

union

select class,ship,result

from classes left join outcomes on class=ship) as A) as B

group by class

57.

select class, count(*)

from (select class, name from ships

union

select ship as class, ship as name

from outcomes

where ship in (select class from classes)) as a

join outcomes b on name=ship

where result='sunk' and class in

(select class from

(select  class, name from ships

union

select ship as class, ship as name

from outcomes

where ship in (select class

from classes))  c

group by class

having count(*)>=3)

group by class