02 ноября, 2011

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





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