sql语句查询（经典笔试题）

转载自：http://blog.csdn.net/zhangyj_315/article/details/2255995
题目 1:

问题描述 :

 S (SNO,SNAME) 学生关系。 SNO 为学号， SNAME 为姓名

 C (CNO,CNAME,CTEACHER) 课程关系。 CNO 为课程号， CNAME 为课程名， CTEACHER 为任课教师

 SC(SNO,CNO,SCGRADE) 选课关系。 SCGRADE 为成绩

 1. （ 1 ）找出选修过 “ 李明 ” 老师讲授课程的所有学生姓名

 -- 实现代码 : 法一

Select *

FROM SC,C,S

Where SC.CNO=C.CNOAND SC.SNO=S.SNOAND CTEACHER=' 李明 '

--- 法二： -------------

Select *

FROM S

Where SnoIN( Select Sno

              FROM C,SC

              Where C.[Cno]=SC.[Cno]AND CTEACHER=' 李明 ' )

 

（ 2 ）找出没有选修过 “ 李明 ” 老师讲授课程的所有学生姓名

 -- 实现代码 : 法一

Select SName

FROM S

Where [Sno]NOT IN(Select SC.[Sno]

                    FROM SC,C

                    Where SC.CNO=C.CNOAND CTEACHER=' 李明 ' )

 

（ 3 ）找出没有选修过课程号为" 1" 的所有学生姓名

 -- 实现代码

Select SName

FROM S

Where [Sno]NOT IN( Select [Sno] FROM SC Where CNO='1')

 

------------------------------------------------------------------------------------------------------------------------------------------ 

 2. 列出有二门以上 ( 含两门 ) 不及格课程的学生姓名及其平均成绩

 -- 实现代码 :

Select S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE)

FROM S,SC,(Select SNO

                     FROM SC

                     Where SCGRADE<60

                     GROUP BY SNO

                     HAVING COUNT(DISTINCT CNO)>=2)A

Where S.SNO=A.SNOAND SC.SNO=A.SNO

GROUP BY S.SNO,S.SNAME

 --------------------------------------------------------------------------------------------------------------------------------------

 3. (1) 列出既学过 “ 语文 ” 课程，又学过 “ 数学 ” 课程的所有学生姓名

 -- 实现代码 : 法一

Select S.SNO,S.SNAME

FROM S,(

   Select SC.SNO

   FROM SC,C

   Where SC.CNO=C.CNOAND C.CNAME IN(' 语文 ' , ' 数学 ' )

   GROUPBY SNO

   HAVINGCOUNT(DISTINCT c.CNO)=2)SC

Where S.SNO=SC.SNO

--- 法二： --------------------------  

select*

from(select s.snofrom s,sc,cwhere s.sno=sc.snoand sc.cno=c.cnoand c.cname='语文')t1,

     (select s.snofrom s,sc,cwhere s.sno=sc.snoand sc.cno=c.cnoand c.cname='数学')t2,

      s

where t1.sno=t2.snoand t1.sno=s.sno

------------------

(2)使用标准SQL嵌套语句查询选修全部课程的学员姓名

法一：一句SQL语句即可。注意有下划线的部分！

 

Select S.SNO,S.SNAME

FROM S,(

       Select SNO

       FROM SC

       GROUP BY SNO

       HAVING COUNT(*)=(selectcount(*) from c) )SC

Where S.SNO=SC.SNO

  

(3)查询选修课程超过2门的学员学号

 --实现代码:

                     Select Sno  FROM SCGROUP BY Sno HAVINGCOUNT(Cno)>2

查询选修课程超过2门的学员学号及姓名

 -- 实现代码 :

Select SNo,SNAME

FROM S

Where SnoIN(Select Sno   FROM SC GROUP BY Sno HAVINGCOUNT(Cno)>2)

 

（4）列出人数大于3的各科最高成绩的列表，显示科目号、成绩两个字段

--- 正解如下： ( 科目号为2、3的选课人数大于3人 )

Select cno,max(scgrade)as'最高分'

FROM SC

Where cnoIN (Select cnoFROM SC GROUP BY cno HAVING COUNT(sno)>3)

groupby cno

orderby cno

-----------------------------------------------------------------------------------------------------------------------------------

4. 列出 “ 语文 ” 课比 “ 数学 ” 课同学该门课成绩高的所有学生的学号、姓名及分数

 -- 实现代码 :法一  

select*

from(select s.sno,s.sname,sc.SCGRADEfrom s,sc,c

      where s.sno=sc.snoand sc.cno=c.cnoand c.cname='语文')t1,

       (select s.sno,s.sname,sc.SCGRADEfrom s,sc,c

      where s.sno=sc.snoand sc.cno=c.cnoand c.cname='数学')t2

where t1.sno=t2.snoand t1.SCGRADE>t2.SCGRADE

------------------------------------------------------------------------------------------------------------------------------------ 

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

 -- 实现代码 :

 Select 学员人数 = COUNT ( DISTINCT [Sno]) FROM SC

 

总结：

1 、 select * from a,bwhere a.id=b.id 和

     select* from a inner join b on a.id=b.id 结果集相同；

2 、在作 “ 找出没有选修过 “ 李明 ” 老师讲授课程的所有学生姓名 ” 这种题时，

   -- 用 Where [Sno] NOT IN( 在此可以是 SQL 语句生成的一列值 ) 比较好。

   Select SName

   FROM S

   Where [Sno]NOT IN( Select SC.[Sno]

                                   FROM SC,C

                                  Where SC.CNO=C.CNOAND CTEACHER=' 李明 ' )

3 、可以将 select count(*) from c 直接代入 SQL 语句，而不是非要写成存储过程；

    Select S.SNO,S.SNAME

    FROM S,(

           Select SC.SNO

           FROM SC,C

           Where SC.CNO=C.CNO

           GROUP BY SNO

           HAVING COUNT(*)=(selectcount(*) from c) )SC

    Where S.SNO=SC.SNO

转载自：http://blog.csdn.net/zhangyj_315/article/details/2255995