数据库应用练习题(含答案)

数据库SQL查询应用练习题

设数据库STUDENT有三个关系S(SNO,SNAME,AGE,SEX)、C(CNO,CNAME,TEACHER)和SC(SNO,CNO,GRADE)，试分析该数据库基本结构及数据关系，并使用SQL语言写出下列查询语句：

1) 检索某某老师所授课程的名称(CNAME)

Select cname from C where teacher=”某某”.

2) 检索20岁以上男生的姓名(SNAME)

Select sname from S where age>=20 AND sex=’boy’.

3) 检索至少选修某某老师所授全部课程的学生姓名(SNAME)

Select sname from s where not exists(select * from c where teacher=’***’ and not exists(select * from sc where s.sno=sc.sno and

https://www.360docs.net/doc/8e14211545.html,o=https://www.360docs.net/doc/8e14211545.html,o))

4) 检索”张三”同学不学课程的课程号(C#)

Select Cno from C where cno not in (select cno from sc where sno in (select sno from S where sname=”张三”))

5) 检索全部学生都选修的课程名称(CNAME)

Select cname from c where not exists(select * from s where not exists( select * from sc where s.sno=sc.sno and https://www.360docs.net/doc/8e14211545.html,o=https://www.360docs.net/doc/8e14211545.html,o))

6) 检索选修课程包含某某老师所授课程之一的学生学号(S#)

Select Sno from sc where cno in (select cno from C where teacher=”某某”)

7) 检索选修课程号为C1和C5的学生学号(S#)

Select sno from sc where cno=’c1’ and sno in (Select sno from sc where cno=’c5’) and sno not in (select sno from sc where cno <> ‘c1’ and cno <> ‘c5’)

8) 检索选修全部课程的学生姓名(SNAME)

Select sname from s where not exists(select * from c where not exists( select * from sc where s.sno=sc.sno and https://www.360docs.net/doc/8e14211545.html,o=https://www.360docs.net/doc/8e14211545.html,o))

9) 检索选修课程包含学号为2的学生所修课程的学生学号(S#) Select sno from sc sc1 where not exists(select * from sc sc2 where sno=’2’ and not exists( select * f rom sc sc3 where sc1.sno=sc3.sno and https://www.360docs.net/doc/8e14211545.html,o=https://www.360docs.net/doc/8e14211545.html,o))

10) 检索选修课程名为”管理信息系统”的学生姓名(SNAME) Select sname from s where sno in (select sno from sc where cno in (select cno from c where cname=’ 管理信息系统’))