SQL语句练习及参考答案

SQL语句练习

1.设学生选课数据库有关系S（sno,sname,age,sex）、SC（sno,cno,grade）和C

（cno,cname,teacher）,分别表示学生、选课和课程，sno代表学号，sname代表学生姓名，age代表年龄，sex代表性别，grade代表成绩，cno代表课程号，teacher代表任课教师。试完成表示下列查询。

S sno sname age sex 1 2 5 李强 23 男 刘丽 22 女 张友 22 男

C cno k1 k5 k8 cname C语言 teacher 王华 SC sno 1 2 5 2 5 5

（1）检索年龄大于21的男学生学号(sno)和姓名(sname)。 （2）建立性别只能为“男”、“女”的约束。

（3）创建一个视图v1，该视图用来查询学生的选课情况，要求包含：学生姓名(sname)，课程名(cname)，任课教师teacher和成绩grade。

（4）检索选修课程号为k1和k5的学生学号(sno)。 （5）检索全部学生都选修的课程的课程号(cno)和课程名(cname)。 （6）删除所有男同学的选课记录。

1.（1）select sno,snae from s where sex=’男’ and age>21 （2）alter table s add constraint c1 check sex in (‘男’,’女’)

（3）create view v1 as select sname,cname,teacher,grade from s,sc,c where s.sno=sc.sno and sc.cno=c.cno （4）select sno fromsc sc1 where cno=’k1’ and exists (select * fromsc sc2 where sc1.sno =sc2.sno and sc2.cno=’k5’)

（5）select cno,cnam from c where not exists (select * from s where not exists (select * from sc where sc.cno=c.cno and s.sno=sc.sno)

（6）delete from sc where sno in (select sno from s where sex=’男’)

cno k1 k1 k1 k5 k5 k8 grade 83 85 92 90 84 80 数据库原理 程军 编译原理 程军

或 delete sc from sc,s where s.sno=sc.sno and sex=’男’

2.设图书借阅数据库有关系图书（图书编号,书名,作者,出版社，出版时间，图书类别）、读者（读者编号,姓名,读者类别）和借阅（读者编号, 图书编号, 借阅日期，还期）。其中，读者类别为字符型，可以取值‘学生’和‘教师’，图书类别为字符型，取值为‘经济类’、‘管理类’、‘外语类’、‘计算机类’等。试用SQL语句完成下列查询。 对图书借阅数据库完成以下操作：

（1）查询借阅了作者为“张强”的图书的读者编号和图书编号。

（2）创建一个视图v1，该视图用来查询教师的借阅情况，要求包含：教师姓名、书名、借阅日期、还期。 （3）查询所借的书名中包含“程序设计”这四个字的读者的读者编号和借阅日期。 （4）根据图书类别统计各类图书的借书数量并按数量由大到小排序。 （5）删除出版社是“电子出版社”的借书记录。

（6）查询借阅了“电子出版社”和“机械出版社”出版的图书的读者编号。

3.现有关系数据库如下：

学生(学号，姓名，性别，专业、奖学金) 课程(课程号，课程名，学分) 选课(学号，课程号，分数)

（1）列出学号为“98001”的已有成绩的各门课名称与成绩。 （2）删除姓名为“陈红”的所有选课记录。

（3）检索获得奖学金、并且至少有一门课程成绩在90分以上的学生信息，包括学号、姓名和专业。 （4）将对选课表的全部权限授予用户王平，并且王平可以授权给其它用户。 （5）求学号为“785222”的学生所选课程的总学分。 （6）将课程名为“数据库”的成绩置为0。 （7）在选课情况表中插入各门课的选课信息。

参考答案：

1.（1）select sno,snae from s where sex=’男’ and age>21 （2）alter table s add constraint c1 check sex in (‘男’,’女’)

（3）create view v1 as select sname,cname,teacher,grade from s,sc,c where s.sno=sc.sno and sc.cno=c.cno （4）select sno fromsc sc1 where cno=’k1’ and exists (select * fromsc sc2 where sc1.sno =sc2.sno and sc2.cno=’k5’)

（5）select cno,cnam from c where not exists (select * from s where not exists (select * from sc where sc.cno=c.cno and s.sno=sc.sno)

（6）delete from sc where sno in (select sno from s where sex=’ 男’) 或 delete sc from sc,s where s.sno=sc.sno and sex=’ 男’

2.（1）select 读者编号，图书编号 from 借阅，图书 where 借阅.图书编号=图书.图书编号 and 作者 =’张强’

（2）create ciew v1 as select 教师姓名，书名，借阅日期，还期 from 读者，图书，借阅 where 读者.读者编号=借阅. 读者编号 and借阅.图书编号=图书.图书编号 and 读者类别=’教师’

（3）select读者编号, 借阅日期 from图书，借阅 where 借阅.图书编号=图书.图书编号 and 书名 like ‘%程序设计%’

（4）select 图书类别，count(图书编号) from 图书，借阅 where 借阅.图书编号=图书.图书编号 group by图书类别 order by count(图书编号) desc

（5）delete 借阅 from 图书，借阅 where 借阅.图书编号=图书.图书编号 and 出版社= ‘电子出版社’

或 delete from 借阅 where 图书编号 in （select 图书编号 from 图书 where出版社= ‘电子出版社’）

（6）select 读者编号 from 读者 a where exists (select * from借阅 w1 where a. 读者编号=w. 读者编号 and 图书编号 in (select图书编号 from图书 where出版社=‘电子出版社’) and exists (select * from 借阅 w2 where 图书编号 in (select图书编号 from图书 where出版社=‘机械出版社’ and a.读者编号=w2. 读者编号)

3.（1）SELECT 课程名，成绩 FROM 课程，选课 WHERE 课程.课程号＝选课.课程号 AND 学号＝“98001”

AND 成绩 IS NOT NULL

（2）DELETE FROM 选课 WHERE 学号 IN （SELECT 学号 FROM 学生 WHERE 姓名=’陈红’）

（3）SELECT 学号,姓名,专业 FROM 学生 WHERE 奖学金>0 AND EXISTS （SELECT * FROM 选课 WHERE 学生.学号=选课.学号 AND 成绩>95） （4）GRANT ALL PRIVILEGES ON 选课 TO 王平 WITHGRANTOPTION

（5）SELECT SUM（学分） FROM 学生，选课，课程 WHERE 学生.学号＝选课.学号 AND

选课.课程号＝课程.课程号 AND 学号＝“785222”

（6）UPDATE 选课 SET 成绩＝0 WHERE 课程号＝（SELECT 课程号 FROM 课程 WHERE 课程名＝“数据库”

（7）INSERT INTO选课情况 SELECT 课程号，课程名，COUNT(*) FROM 选课 GROUP BY 课程号