数据库学习笔记（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
?
?
