数据库实验2

--单表查询，最简单的查询

1、进行单表查询
1)查询所有speed大于2.8的PC信息
select * from pcs where speed>2.8

2)查询购买model为1007的购买记录信息
select * from sales where model=’1007’

3)统计2013-12-20购买记录的数量（count）
聚集函数

select count(*) from sales where day=’2013-12-20’

4)统计2013-12-20购买的总数量(sum)
select model,count(*),max(quantity),sum(quantity) from sales
group by model

5) 查询彩色激光打印机的产品编号
select model from printers where color=’TRUE’ and typeinfo=’laser’

6)查询硬盘大小出现在两种以上PC电脑上硬盘大小
select distinct hd
from pcs
group by hd
having count(hd)>=2

 


7)查询销售三种不同型号PC电脑的供应商
select distinct maker
from products
where type=’pc’
group by maker
having count(*)=3

 

2、连接查询、嵌套查询等
1) 查询制造笔记本电脑（laptop）硬盘最小是100GB的产品供应商maker信息
--1、做等值连接
select maker
from products,laptops
where products.model=laptops.model and hd>=100
--2、内连接
select maker
from products join laptops
on products.model=laptops.model
where hd>=100
--3、子查询实现
--不相关子查询（子查询的SQL语句可以单独执行）
select maker
from products
where model in (
select model from laptops where hd>=100
)
--相关子查询（子查询的SQL语句不能够单独执行，
必须嵌在父查询中，作为父查询的条件执行）
select maker
from products
where exists (
select * from laptops where model=products.model and hd>=100
)
--所有子查询都可以写成相关子查询
--有些相关子查询可以用不相关子查询来代替
--所有不相关子查询都可以用相关子查询来代替


2）查询供应商B所提供的所有产品的产品编号和产品价格
select products.model,price
from 
(
select model,price
from pcs
union
select model,price
from laptops
union
select model,price
from printers
) allType 
inner join products on products.model=allType.model
where maker=’B’

3)查询销售笔记本电脑而不销售PC电脑的供应商
select distinct maker
from products
where type=’laptop’ and maker not in (
select maker from products where type=’pc’
)

4)查询具有相同运行速度和内存的PC电脑编号对，并且电脑编号对只被列出一次，
即列出(i, j)后，(j, i)不被列出.
--典型的表的自身连接查询
select a.model,b.model
from pcs a,pcs b
where a.speed=b.speed and a.ram=b.ram
and a.model<b.model

5) 查询至少提供3种PC运行速度的供应商
select distinct maker
from products inner join pcs
on products.model=pcs.model
group by maker
having count(speed)>=3


6)查询提供的PC或laptop运行速度至少是2.80且有两种以上产品的供应商
--想让大家用视图解决问题
--from子句中应用别名方式给子查询命名名称也可以
select distinct maker
from products inner join 
(
select model,speed from pcs where speed>=2.8
union select model,speed from laptops where speed>=2.8
) atleastSpeed
on products.model=atleastSpeed.model
group by maker
having count(*)>=2

 

7)查询提供的电脑（PC or laptop）具有最高运行速度的供应商

--想让大家用视图解决问题

create view v_pcandlaptop(model,speed)
as
select model,speed from pcs
union select model,speed from laptops

select distinct maker
from products
where model in (
select model from
(
select model,speed from pcs
union select model,speed from laptops
) atleastSpeed where atleastSpeed.speed>=all(
select speed from pcs
union select speed from laptops
)
)
select distinct maker
from products
where model in (
select model from
v_pcandlaptop where speed>=(select max(speed) from v_pcandlaptop)
)