?．查询刘老师所授课程的课程号和课程名
SELECT Cno，Cname FROM Course WHERE Cteacher=‘LIU’ ?．查询平均成绩在75分以上的学生学号和其平均成绩
Select sno,avg(grade) from sc group by sno having avg(grade)>75 ?．查询其他系比信息系年龄最小的学生还小的学生姓名、年龄 select sname,sage from student
where sage< (select min(sage) from student where sdept='信息系') And sdept!=’信息系’ ?．查询所有缺考的学生的学号、姓名和系别 select student.sno,sname,sdept
from student join sc on student.sno=sc.sno where sc.grade is null
?．查询选修了“C01”课程的学生总人数和最高分数 select count(*),max(grade)
from sc where cno='c01'
⑴．查询在2000-8-31日入学的男学生的学号和姓名
select sno,sname from student where sdate=’2000-8-31’ and ssex=’男’ ⑵．查询与刘晨选修的一门课程相同的学生姓名
select sname from student where sno in(select sno from sc where cno in (select cno from sc where sno=(select sno from student where sname=’刘晨’))) ⑶．查询与QIAN老师在同一个办公室的其它老师的姓名
select cteacher from course where coffice=(select coffice from course where cteacher=’qian’) ⑷．查询至少选修LIU老师所授课程中一门课程的女学生姓名 SELECT Sname FROM Student WHERE Ssex=‘女’ AND Sno IN
(SELECT Sno FROM SC WHERE Cno IN
(SELECT Cno FROM Course WHERE Cteacher=‘LIU’) ) 也可用连接查询
⒂．查询哪些课程没有人选修
select cno,cname from course where not exists(select * from sc where
21
cno=course.cno)
⒃．查询选修了课程‘C02’且成绩高于此课程的平均成绩的学生的学号和成绩 select sno,grade from sc where cno=’c02’and grade>(select avg(grade) from sc where cno=’c02’) ⒄．求平均分最高的学号
select sno,avg(grade) from sc group by sno having avg(grade)>=all(select avg(grade) from sc group by sno)
⒅. 显示20岁学生的基本信息和选课信息（课程名和分数），若没有选课，也要将基本信息显示出来 select student.*,sc.cno,cname,grade from student left join sc on student.sno=sc.sno left join course on sc.cno=course.cno where sage=20
⒆. 建立计算机系选修了课程‘c01’的学生的视图
Create view v_cs_s1(sno,sname,grade) as select student.sno,sname,grade from student join sc on
student.sno=sc.sno where sdept=’计算机系’ and cno=’c01’
⒇．建立视图（SGrade），包含每个学生的学号（Sno），选课门数（Count_Cno），平均分（Avg_Grade） create view SGrade(Sno,count_Cno,Avg_Grade) as select Sno,count(Cno),avg(grade) from SC group
by sno;
(21). 利用上述视图进行查询：列出平均分大于80分的学生的学号及其选课门数 select Sno,cout_Cno from SGrade where Avg_Grade>80;
(22). 从视图中查询选修了3门以上课程的学生学号 select sno from sgrade where count_cno>=3
(23). 查询以DB开头且倒数第三个字母是i的课程的课程号、课程名 select cno,cname from course where cname like ‘DB%i_ _’ （24）求平均分最高的学号、学生姓名、平均成绩
select student.sno,sname,avg(grade) from student join sc on student.sno=sc.sno group by student.sno,sname having avg(grade)>=all(select avg(grade) from sc group by sno)
职工系统：
设有关系职工表（职工号，职工名，部门号，工资）和部门表（部门号，部门名，主任），用SQL语句完成下列要求：
（1）向职工表中插入行（‘025’，‘王芳’，‘03’，1000） _______insert into 职工表 values(‘025’，‘王芳’，‘03’，1000)_____ （2）从职工表中删除人事处的所有员工 _______delete from 职工表 where 部门号=（select 部门号 from 部门表
22
where 部门名=‘人事处’）_ ________ （3）将职工号为‘001’的员工工资改为700元钱
_______update 职工表 set 工资=700 where 职工号=’001’___________ （4）查询人事处的员工最高工资
_______select max(工资) from 职工表where 部门号=（select 部门号 from 部门表 where 部门名=‘人事处’）
（5）查询“王芳”所在部门的部门主任
select 主任 from 部门表where 部门号=(select 部门号 from 职工表 where 职工名=’王芳’) （6）查询与“王芳”在同一部门的其它员工信息
___select * from 职工表_ where 部门号=(select 部门号 from 职工表 where 职工名=’王芳’) （7）建立公司所有部门的公共视图——部门职工视图
create view 部门职工（职工号，职工名，部门名，工资）as select 职工表。职工号，职工表。职工名，部门表。部门名，职工表。工资 from 职工表 join 部门表 on 职工表。部门号=部门表。部门号
（8）从部门职工视图中查询财务处员工工资高于800元的员工信息 select * from 部门职工where 部门名=‘财务处’and 工资〉=800
商店供货系统：
按照要求用SQL语句创建下面三张表： ?． 商店表：表名——SHOP
属性：S# 字符型，最大7个字符 表示商店代号
SNAME 字符型，最大20个字符 表示商店名称 WQTY 整型 表示店员人数 CITY 字符型，最大10个字符 表示所在城市
约束：主码——S# 非空属性——Sname 店员人数要大于50人 CREATE TABLE SHOP (
S# CHAR(7) PRIMARY KEY, SNAME CHAR(20) NOT NULL, WQTY INT CHECK(WQTY>0), CITY CHAR(10) )
?． 商品表：表名：GOODS
属性：G# 字符型，最大8个字符 表示商品编号
GNAME 字符型，最大20个字符 表示商品名称
PRICE 数值型，精度4位，
小数保留2位 表示商品价格
23
约束：主码——G# 非空属性——GNAME CREATE TABLE GOODS (
G# CHAR(8) PRIMARY KEY, GNAME CHAR(20) NOT NULL, PRICE DECIMAL(4,2) )
?． 数量表：表名——STORAGE
属性：S# 字符型，最大7个字符 表示商店代号
G# 字符型，最大8个字符 表示商品编号
QTY 整型 表示该商店所储存的商品数量 约束：主码——S#，G#； S#，G#均为外码 QTY要大于等于0 CREATE TABLE STORAGE (
S# CHAR(7) NOT NULL, G# CHAR(8) NOT NULL, QTY INT CHECK(QTY>=0), PRIMARY KEY (S#,G#),
FOREIGN KEY (S#) REFERENCES SHOP(S#), FOREIGN KEY (G#) REFERENCES GOODS(G#) )
针对上面的三个基本表做如下的练习：
?．向基本表SHOP中插入商店元组（”350”，”家乐福超市”，200，”大连”） INSERT INTO SHOP VALUES(‘350’,’ 家乐福超市’,200,’大连’) ?．把家乐福超市储存收音机商品的记录删除
DELETE FROM STORAGE WHERE G# IN (SELECT G# FROM GOODS) ?．将编号为056的商品价格修改为750元
UPDATE GOODS SET PRICE=750 WHERE G#=’056’ ?．查询在北京的商店其平均人员数量
SELECT AVG(WQTY) FROM SHOP WHERE CITY =’ 北京’ ?．查询储藏笔记本数量最多的商店名称（较难）
SELECT SNAME FROM SHOP WHERE S# IN (SELECT S# FROM STORAGE WHERE G# IN (SELECT MAX(G#) FROM GOODS WHERE G# IN (SELECT G# FROM GOODS WHERE GNAME=’笔记本’)))
?．查询上海市所有供应手机的商店名
SELECT SNAME FROM SHOP WHERE CITY =’上海市’ AND S# IN (SELECT S# FROM STORAGE
WHERE G# IN(SELECT G# FROM GOODS WHERE GNAME=’ 手机’))
24
?．查询店员人数不超过100人或者在天津市的所有商店的代号和名称
SELECT S#,SNAME FROM SHOP WHERE WQTY <100 OR CITY = ‘天津市’ ⑴．查询至少供应代号为256的商店所供应的全部商品的商店名称和所在城市
SELECT SNAME,CITY FROM SHOP JOIN STORAGE ON SHOP.S#=STORAGE.S# JOIN GOODS ON GOODS.G#=STORAGE.G# WHERE S#=’256’
⑵．创建一个北京市的商店供应商品的视图S_G_View ( ShopNo, ShopName, GoodsName, Quantity) CREATE VIEW S_G_View( ShopNo, ShopName, GoodsName, Quantity) AS SELECT SHOP.S#,SNAME,GNAME,QTY FROM SHOP JOIN STORAGE ON SHOP.S#=STORAGE.S# JOIN GOODS ON STORAGE.G#=GOOD.G# WHERE CITY=’ 北京市’
(13). 利用上述视图进行查询：列出北京存储电视机超过800台的商店名称和商品数量 SELECT ShopName, Quantiry FROM S_G_View WHERE Quantity >800
顾客买货系统：
已知有顾客购买商品信息的三张表：顾客表Customer、定购表Order、商品表Commodity。 按要求创建三张表： 1． 表名：Customer
属性：ID 字符型 最大10个字符 ——顾客编号
NAME 字符型 最大16个字符 ——顾客姓名 SEX 字符型 最大2个字符 ——性别
MOBILE 字符型 最大11个字符 ——移动电话 ADDRESS 字符型 最大50个字符 ——家庭住址
约束： ID——主码； NAME——非空属性； SEX——取值“男”或“女”；
MOBILE——唯一性； ADDRESS——默认为UNKOWN；
2．表名：OrderBook
属性：CSID 字符型 最大10个字符 ——顾客编号 CMID 字符型 最大12个字符 ——商品编号 COUNT 整型 ——定购数量 BOOKDATE 日期型 ——订货日期 TAKEDATE 日期型 ——交货日期
约束：CSID，CMID——主码； 定购数量要大于0； 订货日期要小于交货日期； CSID——外码，引用Customer表的ID； CMID——外码，引用Commodity表的ID；
3．表名：Commodity
属性：ID 字符型 最大12个字符
25