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