目录
10-4 6-4 查询厂商"D"生产的PC和便携式电脑的平均价格
10-6 6-6 查询各厂商生产的便携式电脑的显示器平均尺寸
10-9 6-9查询速度超过150MHZ的各种速度的PC的平均价格
10-10 6-10 查询所有生产打印机的厂商生产的PC的硬盘平均容量
10-11 7-1 将下述事实存入数据库:生产厂商C制造的型号为1100的PC机,速度240,内存32,硬盘2.5G,售价2499美元。
10-4 6-4 查询厂商"D"生产的PC和便携式电脑的平均价格
select avg(a.price) avg_price from (select pr.maker,pr.model,pr.type,p.price
from product pr join pc p on p.model=pr.model
union
select pr.maker,pr.model,pr.type,l.price
from product pr join laptop l on l.model=pr.model
) a
where a.maker='D'
10-5 6-5 查询各种不同速度的PC的平均价格
select distinct speed,avg(price) as avg_price from
pc group by speed
10-6 6-6 查询各厂商生产的便携式电脑的显示器平均尺寸
select maker,avg(screen) as avg_screen
from (select maker,screen
from product,laptop
where product.model = laptop.model) temp
group by maker;
10-7 6-7 查询生产三种不同型号的PC的厂商
select maker
from (
select maker,model
from product
where model in (
select model
from pc
)
)as a
group by maker
having count(maker)=3
10-8 6-8 查询各厂商生产的PC的最高价格
select product.maker,max(pc.price) as max_price
from product,pc
where product.model=pc.model
group by product.maker;
10-9 6-9查询速度超过150MHZ的各种速度的PC的平均价格
select speed,avg(price) as avg_price
from pc where speed>150 group by speed;
10-10 6-10 查询所有生产打印机的厂商生产的PC的硬盘平均容量
select avg(pc.hd) avg_hd from pc,product /*这个是生产打印机生产pc的model */
where
product.model=pc.model
and
product.maker in (
select maker from product
where type='打印机'
group by maker
)
10-11 7-1 将下述事实存入数据库:生产厂商C制造的型号为1100的PC机,速度240,内存32,硬盘2.5G,售价2499美元。
insert into product
values('C','1100','个人电脑');
insert into pc values('1100','240.00','32','2.50','null','2499');