SQL exercises练习答案汇总

此答案适用于oracle数据库

数据库链接：https://www.sql-ex.ru/help/select13.php#db_1
…持续更新…

13（1）

with a as(
	select class,country from classes where numguns >= 10
)
select a.class,s.name,a.country from ships s,a where s.class in a.class

14（2）

with a as(
	select class,country from classes where numguns >= 10
)
select a.class,s.name,a.country from ships s,a where s.class in a.class

15（2）

select hd
from pc
group by hd
having count(1) > 1

16（2）

with a as (
 select speed,ram,model from pc
)
, b as (
 select a.model, p.model,p.speed,p.ram 
 from pc p,a 
 where a.speed = p.speed and a.ram = p.ram
)
select b.* from b

16（2）

select a.model ,b.model,b.speed,b.ram
from pc a,pc b
where a.speed = b.speed and a.ram = b.ram and a.model > b.model
order by a.model
--使用 > 过滤掉model相等的情况

17（2）

with a as (
	select l.model,l.speed
	from laptop l
	where speed < (
		select min(speed)
		from pc   )
	)
select p.type, a.model, a.speed
from a,product p 
where p.model = a.model

18（2）

with a as(
	select model, price
	from printer
	where price = (select min(price) from printer where color = 'y')
) 
,b as (
	select c.model,c.price 
	from a,printer c
	where a.price = c.price
)
select distinct d.maker,b.price 
from b,product d
where b.model = d.model

19（2）

select product.maker, model, trunc(avg(screen))
from laptop
where model is not null
group by model
join product on laptop.model = product.model

20（2）

select maker,count(1) COUNT_MODEL
from product
where type = 'PC'
group by maker
having count(1) > 2

21（2）

with a as (
select model, price,row_number() over (partition by model order by price desc) sort
from pc
)
,b as (
select p.maker, max(a.price) MAX_PRICE
from a,product p
where a.sort = 1 and a.model = p.model
group by p.maker
)
select * from b

select pd.maker,max(p.price)
from pc p,product pd
where p.model = pd.model
group by pd.maker

22（2）

select speed,avg(price)
from pc
where speed > 600
group by speed

23（2）

with a as (
select model 
from pc p
where p.speed >= 750
union
select model
from laptop l
where l.speed >= 750
)
select distinct maker
from a,product p
where a.model = p.model and p.type in ('PC','Laptop')

24（2）

with a as (
		select model,price
		from pc
		where price >= (select max(price) from pc)
	union
		select model,price
		from laptop
		where price >= (select max(price) from laptop)
	union
		select model,price
		from printer
		where price >= (select max(price) from printer)
)
select model
from a
where price = (select max(price) from a)

25（2）

SELECT c.maker
FROM Product c,
     (SELECT b.model, MAX(b.speed) speed
      FROM PC b
      WHERE b.ram IN (SELECT MIN(a.ram) 
                      FROM PC a
                      )
      GROUP BY b.model
      ) t
WHERE c.model = t.model AND 
      EXISTS (SELECT d.model 
              FROM Printer d, Product e 
              WHERE d.model = e.model AND 
                    e.maker = c.maker
              );

16（2）

27（2）

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

28（2）

select count(1) qty
from product
group by maker
having count(1) = 1

15（2）

15（2）

15（2）

15（2）

15（2）

15（2）

15（2）

15（2）

15（2）

15（2）

15（2）

15（2）

15（2）

15（2）

15（2）

15（2）

15（2）

15（2）

15（2）