selectstudent.*,course.*
from student join sc on student.sno=sc.sno join course on course.cno=sc.cno
wheresno in (select sno from sc group by sno having count(cno) >2) 派生关系(没调试，可以不讲)： selectstudent.*,course.*
from student join sc on student.sno=sc.sno join course on course.cno=sc.cno
join (select sno from sc group by sno having count(cno) >3) as tt(sno) on tt.sno=student.sno 39(方法可以有很多种)
select * from student where sno in (
selectsno from sc where grade=(
select max(grade) from sc where cno =(select cno from course where cname=’数据库原理’
) and cno =(select cno from course where cname=’数据库原理’)
) )
40select * from student where sno in ( selectsno from sc join (
select max(grade),cno from sc group by cno) as max_grade(m_grade,cno) onsc.cno=max_grade.cno and sc.grade=max_grade.m_grade ) 41
select * from student where sno in ( selectsno from sc group by sno having count(*) >=all (
select count(*) from sc group by sno
) ) 42 略 43 略
44 select top 3 with ties sname,sdept,grade from student join sc on Student.sno=sc.sno join course on course.cno=sc.cno where cname=’VB’ order by grade desc
45 select sname,sdept from student where sno not in (select sno from sc where cno=’c01’)
Select sname,sdept from student where not exists(select * from sc where sno=student.sno and cno=’c01’)
46 select Top 3 sname,sage,sdept from student order by sage desc 47 select cno,count(*) as total,avg(grade) as avggrade,max(grade) as maxgrade,min(grade)
as
mingrade
from
student
join
sc
on
student.sno=sc.sno where sdept=’计算机系’ group by cno
48 select c1.cname,c1.credit from course c1 join course c2 on c1.credit=c2.credit where c2.cname=’数据结构’
49 select c.cno,cname from course c left join sc on c.cno=sc.cno where sc.cno is null
50 select sname,sex from student where sno not in (
Select sno from sc join course on sc.cno=course.cno where cname=’VB’) And sdept=’计算机系’ 习题： 单选题：
B B A D A / C B A A D / B D D A B / C CC B B 简述题：
已知有顾客购买商品信息的三张表：顾客表Customer、定购表Order、商品表Commodity。
按要求创建三张表： 1． 表名：Customer
属性：ID 字符型 最大10个字符 ——顾客编号 NAME 字符型 最大16个字符 ——顾客姓名 SEX 字符型 最大2个字符 ——性别
MOBILE 字符型 最大11个字符 ——移动电话 ADDRESS 字符型 最大50个字符 ——家庭住址
约束： ID——主码； NAME——非空属性； SEX——取值“男”或“女”； MOBILE——唯一性； ADDRESS——默认为UNKOWN； 表名：OrderBook
属性：CSID 字符型 最大10个字符 ——顾客编号 CMID 字符型 最大12个字符 ——商品编号 COUNT 整型 ——定购数量 BOOKDATE 日期型 ——订货日期 TAKEDATE 日期型 ——交货日期
约束：CSID，CMID——主码； 定购数量要大于0； 订货日期要小于交货日期；
CSID——外码，引用Customer表的ID； CMID——外码，引用Commodity表的ID； 表名：Commodity
属性：ID 字符型 最大12个字符 ——商品编号 NAME 字符型 最大20个字符 ——商品名称 MANUFACTURE 字符型 最大20个字符 ——生产厂商
PRICE 小数型 最大不超过4位数，保留2位小数 ——商品单价 约束：ID——主码； NAME——非空； 针对上面的三个基本表做如下练习：
2．往基本表Customer中插入顾客元组（”0421F901”，”WU”，”女”，13980011001）
3．往基本表Commodity中插入一条商品记录（“03110408591”，“牙膏”，“保洁
公司”，5.00）
4．修改“WANGYAN”顾客定购商品的记录交货日期为2005-12-25。
Update order set taketime=’2005-12-25’ where csid in (select id from customer where name=’wangyan’) 5. 查询“ANAN”顾客的手机号和住址。
Select mobile, address from customer where name=’anan’ 6. 查询商品的平均价格高于75元钱的厂商名称。
Select manufacture from commodity group by manufacture having avg(price) >75
7. 查询顾客的定购信息，并按订货日期升序排列，若订货日期相同，则按定购数量降序排列。
Select * from orderbook order by bookdate,countdesc 8. 查询定购数量超过100的顾客姓名、电话和住址。
Select name, mobile, address from customer where id in(select csidfrom orderbook where count>100)
9. 查询没有订单的商品编号、名称和厂商。
Select id,name, manufacture from comodity where id not in(select csid from orderbook)
10. 查询定购了商品号为“M900532481”的顾客总人数和最高数量。 Select count(*) ,max(count) from order where cmid=’m900532481’ 11. 查询定购了“可比克”商品的顾客编号、姓名。
Select id,name from customer where id in(select csid from order where cmid in(select id from comodity where name=’ 可比克’))
12. 查询商品名中包含“糖”的所有商品的编号、厂商、单价。
Select id,manufacture ,price from comodity where name like ‘%糖%’ 13. 查询至少有两位顾客定购的商品的名称。
Select name from comodity where id in(select cmid from order group by cmid having count(*)>=2)
14. 查询截至2005年底所有商品订单最晚的交货日期。
SELECT taketime from order where taketime = (select max(taketime) from order where taketime<’2005-12-31’) 15. 查询住址中含有“软件园”三个字的顾客人数。
Select count(*) from customer where address like’%软件园%’ 16．查询订购了“保洁公司”生产的产品中价格最高的商品的客户信息。 Select
customer.*
from
customer,OrderBook,Commodity
where
customer.id=orderbook.CSID and Commodity.id=orderbook.cmid and Commodity.id in
(Select id from Commodity where MANUFACTURE =“保洁公司” and price=( Select max(price) from Commodity where MANUFACTURE =“保洁公司”)) 17．查询2007年3月份住址在“大连”的顾客对“上好佳”商品的定购信息，列出顾客姓名，商品数量和定购日期。
Select customer.name,count,BOOKDATE from customer,OrderBook,Commodity where
customer.id=orderbook.CSID and Commodity.id=orderbook.cmid and ADDRESS like ‘%大连%’ and Commodity.name=’上好佳’ and BOOKDATE between 2007-03-01 and 2007-03-31
18．查询订单最多的顾客姓名。
Select name from customer where id in(
Select CSID fromOrderBookgroup by csid having count(*)>=all(select Count(*) from orderbook group by csid))
19．查询定购了“保洁公司”的商品最多的客户信息。 Select * from customer where id in(
Select csid from orderbook where cmid in(Select id from Commodity where
MANUFACTURE =“保洁公司”) group by csid having count(*)>=all(select
count(*) from orderbook where cmid in(Select id from Commodity where
MANUFACTURE =“保洁公司”) group by csid ))
20．把“雀巢奶粉”的定购商品记录全部删去。
Delete from order where cmid in (select id from commodity where name=’雀巢奶粉’)