转SQL习题

一、
 1、查询7号课程没有考试成绩的学生学号。
    Select snofrom sc where cno='7' and grade is null
 2、查询7号课程成绩在90分以上或60分以下的学生学号。
    Select snofrom sc where cno='7' and grade not between 60 and 90
 3、查询课程名以“数据”两个字开头的所有课程的课程号和课程名。
    Selectcno,cname from c where cname like '数据%'
 4、查询每个学生所有课程的平均成绩，输出学生学号和平均成绩。
    Selectsno,avg(grade) from sc group by sno
 5、查询每门课程的选修人数，输出课程号和选修人数。
    Selectcno,count(*) from sc group by cno
 6、查询选修7号课程的学生的学号、姓名、性别。
    Selects.sno,sname,ssex from s,sc where s.sno=sc.sno and cno='7'
    或： Selectsno,sname,ssex from s where sno in
             ( Select sno from sc where cno='7' )
 7、查询选修7号课程的学生的平均年龄。
    Selectavg(sage) from s,sc where s.sno=sc.sno and cno='7'
    或： Selectavg(sage) from s where sno in
             ( Select sno from sc where cno='7' )
 8、查询有30名以上学生选修的课程号。
    Select cnofrom sc group by cno having count(*)>30
 9、查询至今没有考试不及格的学生学号。
    Selectdistinct sno from sc where sno not in
  ( Select sno from sc wheregrade<60 )
    或： Selectsno from sc group by sno having min(grade)>=60

二、
 1、找出选修课程号为C2的学生学号与成绩。
    Selectsno,grade from sc where cno='C2'
 2、找出选修课程号为C4的学生学号与姓名。
    Selects.sno,sname from s,sc where s.sno=sc.sno and cno='C4'
   注意本题也可以用嵌套做
   思考本题改为“找出选修课程号为C4的学生学号、姓名与成绩”后还能用嵌套做吗？
 3、找出选修课程名为 Maths 的学生学号与姓名。
    Selects.sno,sname from s,sc,c
   where  s.sno=sc.sno and c.cno=sc.cno andcname='Maths'
   注意本题也可以用嵌套做
 4、找出选修课程号为C2或C4的学生学号。
    Selectdistinct sno from sc where cno in ('C2','C4')
    或： Selectdistinct sno from sc where cno='C2' or cno='C4'
 5、找出选修课程号为C2和C4的学生学号。
    Select snofrom sc where cno='C2' and sno in
  ( Select sno from sc where cno='C4' )
   注意本题也可以用连接做
    思考：Selectdistinct sno from sc where cno='C2' and cno='C4'正确吗？
 6、找出不学C2课程的学生姓名和年龄。
    Selectsname,sage from s where sno not in
        ( Select sno from sc where cno='C2' )
    或： Selectsname,sage from s where not exists
             ( Select * from sc where sno=s.sno and cno='C2') 
 7、找出选修了数据库课程的所有学生姓名。(同3)
    Select snamefrom s,sc,c
   where  s.sno=sc.sno and c.cno=sc.cno andcname='数据库'
 8、找出数据库课程不及格的女生姓名。
    连接：Selectsname from s,sc,c
         where  s.sno=sc.sno and c.cno=sc.cno andcname='数据库'
                and grade<60 and ssex='女'
    嵌套：Selectsname from s where ssex='女' and  sno in
              ( Select sno from sc where grade<60 and cno in
                     ( Select cno from c where cname='数据库' )
              )
 9、找出各门课程的平均成绩，输出课程名和平均成绩。
    Selectcname,avg(grade) from sc,c
    wherec.cno=sc.cno  group by c.cno,cname
   思考本题也可以用嵌套做吗？
10、找出各个学生的平均成绩，输出学生姓名和平均成绩。
    Selectsname,avg(grade) from s,sc
    wheres.sno=sc.sno group by s.sno,sname
   思考本题也可以用嵌套做吗？
11、找出至少有30个学生选修的课程名。
    Select cnamefrom c where cno in
        ( Select cno from sc group by cno havingcount(*)>=30 )
   注意本题也可以用连接做
12、找出选修了不少于3门课程的学生姓名。
    Select snamefrom s where sno in
        ( Select sno from sc group by sno having count(*)>=3)
   注意本题也可以用连接做
13、找出各门课程的成绩均不低于90分的学生姓名。
    Select snamefrom s where sno not in
        ( Select sno from sc where grade<90 )
    或： Selectsname from s,sc where s.sno=sc.sno
        group by s.sno,sname having min(grade)>=90
14、找出数据库课程成绩不低于该门课程平均分的学生姓名。
    Select snamefrom s,sc,c
   where  s.sno=sc.sno and sc.cno=c.cno andcname='数据库' and grade>
        ( Select avg(grade) from sc,c
          where sc.cno=c.cno and cname='数据库'
        )
15、找出各个系科男女学生的平均年龄和人数。
    Selectsdept,ssex,avg(sage),count(*) from s group by sdept,ssex
16、找出计算机系(JSJ)课程平均分最高的学生学号和姓名。
    Selects.sno,sname from s,sc where s.sno=sc.sno and sdept='JSJ'
    group bys.sno,sname
    havingavg(grade) >=ALL
        ( Select avg(grade) from s,sc
          where s.sno=sc.sno and sdept='JSJ'
          group by s.sno
        )
17、(补充)查询每门课程的及格率。
   本题可以分三步做：
   
   第1步：得到每门课的选修人数
    create view  v_all(cno,cnt)
        as select cno, count(*) from sc group by cno
   第2步：得到每门课及格人数
    create view  v_pass(cno,cnt_pass)
        as select cno, count(*) from sc where grade>=60group by cno
   第3步：每门课的及格人数/每门课选修人数
    select v_all.cno, cnt_pass*100/cnt from  v_all, v_pass
    where v_all.cno = v_pass.cno

三、
 1、查询工资在1000到3000元之间的男性业务员的姓名和办公室编号。
    SelectYname,Ono from YWY
    where Salarybetween 1000 and 3000 and Ysex='男'
 2、查询各个办公室的业务员人数，输出办公室编号和对应的人数。
    SelectOno,count(*) from YWY group by Ono
 3、查询每个客户在2002年5月购买的总金额，输出客户号和相应的总金额。
    SelectKno,sum(Fmoney) from FP
    where Fdatebetween '2002.5.1' and '2002.5.31'
    group byKno
 4、查询2002年5月购买次数超过5次的所有客户号，且按客户号升序排序。 
    Select Knofrom FP
    where Fdatebetween '2002.5.1' and '2002.5.31'
    group byKno
    havingcount(*)>5
    order by KnoASC
 5、查询各办公室男性和女性业务员的平均工资。
    SelectOno,Ysex,avg(Salary) from YWY group by Ono,Ysex
 6、查询2002年5月曾经在王海亮业务员手中购买过商品的客户号、
           客户姓名和联系电话。
    SelectKno,Kname,Phone from KH where Kno in
        ( Select Kno from FP
          where Fdate between '2002.5.1' and '2002.5.31' and Yno in
                      ( Select Yno from YWY where Yname='王海亮' )
        )
   注意本题也可以用连接做
 7、查询所有工资比1538号业务员高的业务员的编号、姓名和工资。
    SelectYno,Yname,Salary from YWY where Salary >
        ( Select Salary from YWY where Yno='1538' )
 8、查询所有与1538号业务员在同一个办公室的其他业务员的编号和姓名。
    SelectYno,Yname from YWY where Yno!='1538' and Ono in
        ( Select Ono from YWY where Yno='1538' )
 9、查询销售总金额最高的业务员的编号。
    Select Ynofrom FP group by Yno having sum(Fmoney) >=ALL
        ( Select sum(Fmoney) from FP group by Yno )
10、查询所有业务员的编号、姓名、工资以及工资比他高的其他业务员的平均工资。
    利用自连接
    SelectY1.Yno,Y1.Yname,Y1.Salary,avg(Y2.Salary)
   from   YWY Y1, YWY Y2
   where  Y1.Salary < Y2.Salary
    groupby  Y1.Yno  

四、
 1、找出每个班级的班级代码、学生人数、平均成绩。
    SelectBJDM,count(*),avg(CJ) from SC group by BJDM
 2、找出每个学生的班级代码、学生姓名、考试科目数、总成绩。
    SelectBJDM,XSXM,count(*),sum(CJ) from SC
    group byBJDM,BNXH,XSXM
 3、输出一张表格，每位学生对应一条记录，包括字段：
         班级代码、学生姓名、语文成绩、数学成绩、外语成绩。
    SelectSC1.BJDM,SC1.XSXM,SC1.CJ,SC2.CJ,SC3.CJ
   from  SC SC1, SC SC2, SC SC3
    whereSC1.BJDM=SC2.BJDM and SC1.BNXH=SC2.BNXH and
         SC2.BJDM=SC3.BJDM and SC2.BNXH=SC3.BNXH and
         SC1.KM='语文' and SC2.KM='数学' and SC3.KM='外语'
 4、输出一张表格，有成绩低于60分的每位学生对应一条记录，包括字段：
         班级代码、学生姓名、最低成绩。
    SelectBJDM,XSXM,min(CJ) from SC
   where  CJ<60 group byBJDM,BNXH,XSXM
   或：  Select BJDM,XSXM,min(CJ) from SC
         group by BJDM,BNXH,XSXM
         having min(CJ)<60
 5、输出一张表格，有成绩低于60分的每位学生对应一条记录，包括字段：
         班级代码、学生姓名、最高成绩、平均成绩。
    SelectBJDM,XSXM,max(CJ) from SC
    group byBJDM,BNXH,XSXM
    havingmin(CJ)<60
   请思考下列做法是否正确：
         Select BJDM,XSXM,max(CJ),avg(CJ) from SC
         where  CJ<60 group byBJDM,BNXH,XSXM
 6、输出一张表格，所有成绩都不低于60分的每位学生对应一条记录，包括字段：
         班级代码、学生姓名、平均成绩。
    SelectBJDM,XSXM,avg(CJ) from SC
    group byBJDM,BNXH,XSXM
    havingmin(CJ)>=60
 7、输出一张表格，每一位学生对应一条记录，包括字段：
         班级代码、学生姓名、去掉一个最低分后的平均成绩。
    SelectBJDM,XSXM,(sum(CJ)-min(CJ))/(count(*)-1) from SC
    group byBJDM,BNXH,XSXM
 8、输出一张表格，每门科目对应一条记录，包括字段：
         科目、去掉一个最低分后的平均成绩。
    SelectKM,(sum(CJ)-min(CJ))/(count(*)-1) from SC
    group byKM

 

 实验指导中“八 SQL查询语句” 的答案

 1、查询年龄在19至21岁之间的女生的学号，姓名，年龄，按年龄从大到小排列。
    Selectsno,sname,sage from student
    where sagebetween 19 and 21 and ssex='女'
    order bysage desc
 2、查询姓名中有“明”字的学生人数。
    Selectcount(*) from student
    where snamelike "%明%"
 3、查询1001课程没有成绩的学生的学号。
    Select snofrom sc where cno='1001' and grade is null
 4、查询JSJ、SX、WL系的学生学号，姓名，结果按系及学号排列。
    Selectsno,sname,sdept from student
    where sdeptin ( 'JSJ', 'SX', 'WL' )
    order bysdept,sno
 5、计算每一门课的总分、平均分，最高分、最低分。
    Selectcno,sum(grade),avg(grade),max(grade),min(grade)
    fromsc
    group bycno
 6、查询平均分大于90分的男学生学号及平均分。
    连接：
    selectsc.sno,avg(grade) from student,sc
    wherestudent.sno=sc.sno and ssex=’男’
    group bysc.sno
    havingavg(grade)>90
    嵌套：
    selectsno,avg(grade) from sc
    where sno in( select sno from student where ssex='男' )
    group bysno
    havingavg(grade)>90
 7、查询选修课程超过2门的学生姓名。
    select snamefrom student,sc
    wherestudent.sno=sc.sno
    group bysc.sno,sname
    havingcount(*)>2
   本题也可以用嵌套做
 8、查询 JSJ 系的学生选修的课程号。
    Selectdistinct cno from student,sc
   where  student.sno=sc.sno and sdept='JSJ'
   本题也可以用嵌套做
 9、查询选修1002课程的学生的学生姓名(用连接和嵌套2种方法)
    连接：Selectsname from student,sc
         where student.sno=sc.sno and cno='1002'
    嵌套：Selectsname from student where sno in
             ( select sno from sc where cno='1002' )
10、查询学生姓名以及他选修课程的课程号及成绩。
    Selectsname,cno,grade from student,sc
    wherestudent.sno=sc.sno
   思考本题也可以用嵌套做吗？
11、查询选修“数据库原理”课且成绩 80 以上的学生姓名(用连接和嵌套2种方法)
    连接：Selectsname from student,sc,course
         where student.sno=sc.sno and sc.cno=course.cno and
               cname='数据库原理' and grade>80
    嵌套：Selectsname from student where sno in 
              ( select sno from sc where grade>80 and cnoin 
                    ( select cno from course where cname='数据库原理' )
              )
12、查询平均分不及格的学生的学号，姓名，平均分。
    Selectsc.sno,sname,avg(grade) from student,sc
    wherestudent.sno=sc.sno
    group bysc.sno,sname
    havingavg(grade)<60
   思考本题也可以用嵌套做吗？
13、查询平均分不及格的学生人数。
    Selectcount(*) from student
    where snoin
        ( select sno from sc group by sno havingavg(grade)<60 )
   下面是一个典型的错误
    Selectcount(*) from sc group by sno havingavg(grade)<60
14、查询没有选修1002课程的学生的学生姓名。
    Select snamefrom student
    where snonot in ( select sno from sc where cno='1002')
    或： selectsname from student
        where not exists
             ( select * from sc where cno='1002' and sno=student.sno)
   思考本题也可以用一般的连接做吗？
15、查询平均分最高的学生学号及平均分。
    Selectsno,avg(grade)
    fromsc
    group bysno
    havingavg(grade) >=ALL ( Select avg(grade)
                             from sc
                             group by sno
                           )
16、查询每门课程成绩都高于该门课程平均分的学生学号。
   可以先计算每门课程平均分
    create viewc_avg(cno,avg_grade)
        as select cno,avg(grade) from sc group by cno
    再查询
    Selectdistinct sno from sc
    where snonot in ( Select sno from sc,c_avg
                      where sc.cno=c_avg.cno and grade<avg_grade
                    )
   ===========================================
    SELECTDISTINCT Sno
    FROM SCSC1
    WHERESC1.Sno NOT IN
   ( SELECT SC2.Sno
    FROM SC SC2
    WHERE SC2.Grade <=
    ( SELECTAVG(SC3.Grade)
     FROM SC SC3
     WHERE  SC3.Cno=SC2.Cno
    )
   )
    或：
    SELECTDISTINCT Sno
    FROM SCSC1
    WHERE NOTEXISTS
      ( SELECT *
    FROM SC SC2
    WHERE SC2.Sno=SC1.Sno AND SC2.Grade <=
   ( SELECTAVG(SC3.Grade)
    FROM SC SC3
    WHERE  SC3.Cno=SC2.Cno
   )
   )