数据库学习笔记（2）——练习题 1

1、涉及的三张表

???? 1)学员表(学号、姓名、单位、年龄)????? S(sno,sname,sdepart,sage)

???? 2)课程（课程编号、课程名）?????????????? C(cno,cname)

???? 3)成绩表（学号、课程编号、成绩）??? SC(sno,sname,grade)

2、表中的数据

???? 1）SELECT * FROM S

???? 2)SELECT * FROM C

?

???? 3)SELECT * FROM SC

??

?

3、练习题。

?

1）查询选修课程名称为'java’的学员学号和姓名

?

???? SELECT sno,sname From S WHERE sno in(SELECT sno From C,SC WHERE C.cno=SC.cno AND? cname='java')

?

2) 查询选修课程编号为'1’的学员姓名和所属单位

???? SELECT sname,sdepart From S,SC WHERE S.sno=SC.sno AND SC.cno=1

?

3)查询不选修课程编号为'3’的学员姓名和所属单位

???? SELECT sname,sdepart From S WHERE sno NOT IN(SELECT sno FROM SC WHERE cno='3')

?

4)查询选修全部课程的学员姓名和所属单位

分步完成：

??? --1.查询课程的数量

?????????? SELECT COUNT(*) FROM C

?

??? --2.查看每个学员分别学了多少课程

?

????????? ?SELECT COUNT(cno)FROM SC GROUP BY sno

???--3.处理一个学员同一门课程可能有多条成绩记录

?

??????????? SELECT COUNT(DISTINCT(cno))FROM SC GROUP BY sno

?

???--4.组合
?????????SELECT sno,sname, sdepart FROM S
???????????????? ?WHERE sno
????????????????????????????? IN (SELECT sc.sno
????????????????????????????????????????????????????? FROM SC RIGHT JOIN C
?????????????????????????????????????????????????????????????????? ON SC.cno = C.cno
????????????????????????????????????????????????????? GROUP BY SC.sno
????????????????????????????????????????????????????? HAVING?? COUNT(distinct(SC.cno))
????????????????????????????????????????????????????????????????????????? = ( select count(*) from C )
?????????????????????????????????????? )

?

5）查询选修了课程的学员人数　

???????? SELECT 选修课程学员人数=COUNT(DISTINCT(sno))FROM SC

?

6）查询选修课程超过2门的学员学号和所属单位

??????? SELECT sno,sdepart FROM S WHERE sno IN (SELECT sno From SC Group BY sno HAVING count(cno)>2)

　

?

?参考：http://www.cnblogs.com/finejob/articles/974900.html

?

?