高级程序员必修课--sql思维举重训练 .

lijuanabc

浏览: 126289 次

最近访客更多访客>>

njdccy

henryxlx

yokoboy

zy_783095565

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

写sql是程序员基本功课,找工作面试一般必有sql题,实际工作中对sql的需求更是千变万化,所以掌握好sql对于一个程序员来说是件非常重要的事情.本文通过一个简单易懂的关系(学生/课程/成绩关系)尽量构造出各种各样的需求来提高我们写sql的功底,其中有些sql还是有一些难度的,大多数sql我都给出了思考思路,希望本人N天的劳动成果对您有所帮助.

限于本人水平有限,有些地方的sql写的并不完美,希望大家不吝赐教.最后提一点小小的要求,希望大家对本文积极进行评论,大家的评论是我改进和提高的动力,我希望在和大家的交流中得到提高.

表关系:

建表和初始化sql(本例子使用oralce数据库):

[sql] view plain copy print ?

--创建表
createtableT_STUDENT(snoNUMBERnotnull,snameVARCHAR2(30),sdreeVARCHAR2(50),sageNUMBER,ssexCHAR(2));
altertableT_STUDENTaddprimarykey(SNO);
createtableT_SCORE(snoNUMBER,cnoNUMBER,gradeNUMBER(4,1),tnoNUMBER,idNUMBERnotnull);
altertableT_SCOREaddprimarykey(ID);
createtableT_COURSE(cnoNUMBERnotnull,cnameVARCHAR2(30));
altertableT_COURSEaddprimarykey(CNO);
--初始化学生表
insertintoT_STUDENT(SNO,SNAME,SDREE,SAGE,SSEX)values(1,'李坤','天融信',26,'男');
insertintoT_STUDENT(SNO,SNAME,SDREE,SAGE,SSEX)values(2,'曹贵生','中银',26,'男');
insertintoT_STUDENT(SNO,SNAME,SDREE,SAGE,SSEX)values(3,'柳波','淘宝',27,'男');
insertintoT_STUDENT(SNO,SNAME,SDREE,SAGE,SSEX)values(4,'纪争光','IBM',23,'男');
insertintoT_STUDENT(SNO,SNAME,SDREE,SAGE,SSEX)values(5,'李学宇','微软',25,'女');
insertintoT_STUDENT(SNO,SNAME,SDREE,SAGE,SSEX)values(6,'李雪琪','文思',25,'女');
insertintoT_STUDENT(SNO,SNAME,SDREE,SAGE,SSEX)values(7,'陈绪','百度',26,'男');
insertintoT_STUDENT(SNO,SNAME,SDREE,SAGE,SSEX)values(8,'韩正阳','中海油',24,'男');
insertintoT_STUDENT(SNO,SNAME,SDREE,SAGE,SSEX)values(9,'陈伟东','腾讯',24,'男');
insertintoT_STUDENT(SNO,SNAME,SDREE,SAGE,SSEX)values(10,'刘兵','华为',24,'男');
insertintoT_STUDENT(SNO,SNAME,SDREE,SAGE,SSEX)values(11,'丁成云','联想',25,'女');
insertintoT_STUDENT(SNO,SNAME,SDREE,SAGE,SSEX)values(12,'王鹏','中兴',25,'男');
commit;
--初始化课程表
insertintoT_COURSE(CNO,CNAME)values(1,'JAVA程序设计');
insertintoT_COURSE(CNO,CNAME)values(2,'ORACLE开发');
insertintoT_COURSE(CNO,CNAME)values(3,'C++程序设计');
insertintoT_COURSE(CNO,CNAME)values(4,'C#程序设计');
insertintoT_COURSE(CNO,CNAME)values(5,'Windows实战');
insertintoT_COURSE(CNO,CNAME)values(6,'CenterOS教程');
insertintoT_COURSE(CNO,CNAME)values(7,'Jsp/Servlet开发');
insertintoT_COURSE(CNO,CNAME)values(8,'J2EE从入门到精通');
insertintoT_COURSE(CNO,CNAME)values(9,'EJB及设计模式');
insertintoT_COURSE(CNO,CNAME)values(10,'Javascript/jQuery实战');
insertintoT_COURSE(CNO,CNAME)values(11,'Flash设计');
insertintoT_COURSE(CNO,CNAME)values(12,'HTML/CSS/JAVASCRIPT实战');
insertintoT_COURSE(CNO,CNAME)values(13,'精通ASP.NET');
insertintoT_COURSE(CNO,CNAME)values(14,'JBoss入门');
insertintoT_COURSE(CNO,CNAME)values(15,'Spring开发');
commit;
--初始化成绩表
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(1,2,90.0,2,1);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(1,3,80.0,3,2);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(1,4,90.0,4,3);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(11,2,70.0,2,4);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(2,11,66.0,11,5);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(2,15,77.0,15,6);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(2,8,87.0,8,7);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(2,6,96.0,6,8);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(3,2,89.0,2,9);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(3,1,91.0,1,10);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(4,2,83.0,2,11);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(5,4,73.0,4,12);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(5,1,60.0,1,13);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(5,8,82.0,8,14);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(6,8,90.5,10,15);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(8,2,58.0,2,16);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(11,3,80.0,3,17);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(9,11,65.0,11,18);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(9,12,67.0,12,19);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(9,15,95.0,15,20);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(9,13,59.0,13,21);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(10,4,98.0,4,22);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(10,6,97.0,6,23);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(10,7,96.0,7,24);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(11,7,95.0,7,25);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(12,8,69.0,8,26);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(12,9,85.0,9,27);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(10,14,100.0,14,28);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(6,9,100.0,9,29);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(11,1,59.0,1,30);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(11,4,90.0,4,31);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(11,5,91.0,5,32);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(11,6,58.0,6,33);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(11,8,93.0,8,34);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(11,9,57.0,9,35);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(11,10,95.0,10,36);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(11,11,96.0,11,37);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(11,12,97.0,12,38);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(11,13,98.0,13,39);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(11,14,99.0,14,40);
insertintoT_SCORE(SNO,CNO,GRADE,TNO,ID)values(11,15,89.0,15,41);
commit;

--创建表
create table T_STUDENT(sno NUMBER not null,  sname VARCHAR2(30),  sdree VARCHAR2(50),  sage  NUMBER,  ssex  CHAR(2));
alter table T_STUDENT add primary key (SNO);
create table T_SCORE(sno   NUMBER,  cno   NUMBER,  grade NUMBER(4,1), tno NUMBER,  id NUMBER not null);
alter table T_SCORE add primary key (ID);
create table T_COURSE(cno NUMBER not null,  cname VARCHAR2(30));
alter table T_COURSE add primary key (CNO);

--初始化学生表
insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (1, '李坤', '天融信', 26, '男');
insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (2, '曹贵生', '中银', 26, '男');
insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (3, '柳波', '淘宝', 27, '男');
insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (4, '纪争光', 'IBM', 23, '男');
insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (5, '李学宇', '微软', 25, '女');
insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (6, '李雪琪', '文思', 25, '女');
insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (7, '陈绪', '百度', 26, '男');
insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (8, '韩正阳', '中海油', 24, '男');
insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (9, '陈伟东', '腾讯', 24, '男');
insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (10, '刘兵', '华为', 24, '男');
insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (11, '丁成云', '联想', 25, '女');
insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (12, '王鹏', '中兴', 25, '男');
commit;

--初始化课程表
insert into T_COURSE (CNO, CNAME) values (1, 'JAVA程序设计');
insert into T_COURSE (CNO, CNAME) values (2, 'ORACLE开发');
insert into T_COURSE (CNO, CNAME) values (3, 'C++程序设计');
insert into T_COURSE (CNO, CNAME) values (4, 'C#程序设计');
insert into T_COURSE (CNO, CNAME) values (5, 'Windows实战');
insert into T_COURSE (CNO, CNAME) values (6, 'Center OS教程');
insert into T_COURSE (CNO, CNAME) values (7, 'Jsp/Servlet开发');
insert into T_COURSE (CNO, CNAME) values (8, 'J2EE从入门到精通');
insert into T_COURSE (CNO, CNAME) values (9, 'EJB及设计模式');
insert into T_COURSE (CNO, CNAME) values (10, 'Javascript/jQuery实战');
insert into T_COURSE (CNO, CNAME) values (11, 'Flash设计');
insert into T_COURSE (CNO, CNAME) values (12, 'HTML/CSS/JAVASCRIPT实战');
insert into T_COURSE (CNO, CNAME) values (13, '精通ASP.NET');
insert into T_COURSE (CNO, CNAME) values (14, 'JBoss入门');
insert into T_COURSE (CNO, CNAME) values (15, 'Spring开发');
commit;

--初始化成绩表
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (1, 2, 90.0, 2, 1);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (1, 3, 80.0, 3, 2);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (1, 4, 90.0, 4, 3);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 2, 70.0, 2, 4);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (2, 11, 66.0, 11, 5);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (2, 15, 77.0, 15, 6);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (2, 8, 87.0, 8, 7);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (2, 6, 96.0, 6, 8);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (3, 2, 89.0, 2, 9);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (3, 1, 91.0, 1, 10);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (4, 2, 83.0, 2, 11);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (5, 4, 73.0, 4, 12);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (5, 1, 60.0, 1, 13);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (5, 8, 82.0, 8, 14);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (6, 8, 90.5, 10, 15);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (8, 2, 58.0, 2, 16);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 3, 80.0, 3, 17);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (9, 11, 65.0, 11, 18);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (9, 12, 67.0, 12, 19);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (9, 15, 95.0, 15, 20);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (9, 13, 59.0, 13, 21);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (10, 4, 98.0, 4, 22);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (10, 6, 97.0, 6, 23);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (10, 7, 96.0, 7, 24);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 7, 95.0, 7, 25);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (12, 8, 69.0, 8, 26);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (12, 9, 85.0, 9, 27);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (10, 14, 100.0, 14, 28);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (6, 9, 100.0, 9, 29);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 1, 59.0, 1, 30);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 4, 90.0, 4, 31);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 5, 91.0, 5, 32);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 6, 58.0, 6, 33);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 8, 93.0, 8, 34);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 9, 57.0, 9, 35);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 10, 95.0, 10, 36);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 11, 96.0, 11, 37);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 12, 97.0, 12, 38);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 13, 98.0, 13, 39);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 14, 99.0, 14, 40);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 15, 89.0, 15, 41);
commit;

在思维举重前,先来一个热身运动吧:

[sql] view plain copy print ?

--(一)查询选修课程名称为'JAVA程序设计'的学员学号和姓名
--方法一:
--1.查询'JAVA程序设计'这门课程的课程号
--2.在成绩表中查询课程号为步骤1的课程号的成绩信息..
--3.在学生表中查询学号为步骤2结果的学生信息
selectst.sno,st.sname
fromstudentst
wherest.snoin
(selectsno
fromscoresc
wheresc.cno=
(selectco.cnofromcoursecowhereco.cname='JAVA程序设计'));
--方法二:
--1.查询'JAVA程序设计'这门课程的课程号
--2.在成绩表和学生表关联结果中查询课程号等于步骤1的课程号的成绩信息.
--sql86
selectst.sno,st.sname
fromstudentst,scoresc
wherest.sno=sc.sno
andsc.cno=
(selectco.cnofromcoursecowhereco.cname='JAVA程序设计');
--sql92
selectst.sno,st.sname
fromstudentst
joinscoresc
onst.sno=sc.sno
wheresc.cno=
(selectco.cnofromcoursecowhereco.cname='JAVA程序设计');
--方法三:
--1.在成绩表和课程表关联结果中查询选修了'JAVA程序设计'这门课的学生的学号
--2.在学生表中查询步骤1中的学号的学生的详细信息.
--sql86
selectst.sno,st.sname
fromstudentst
wherest.snoin(selectsc.sno
fromscoresc,courseco
wheresc.cno=co.cno
andco.cname='JAVA程序设计');
--sql92
selectst.sno,st.sname
fromstudentst
wherest.snoin(selectsc.sno
fromscoresc
joincourseco
onsc.cno=co.cno
whereco.cname='JAVA程序设计');
--方法四:
--在成绩表和课程表和学生表三表关联的结果中过滤得到选修了'JAVA程序设计'的学生基本信息
--sql86
selectst.sno,st.sname
fromstudentst,scoresc,courseco
wherest.sno=sc.sno
andco.cno=sc.cno
andco.cname='JAVA程序设计';
--sql92(1)用where过滤
selectst.sno,st.sname
fromstudentst
joinscoresc
onst.sno=sc.sno
joincourseco
onco.cno=sc.cno
whereco.cname='JAVA程序设计';
--sql92(2)在关联条件中过滤
selectst.sno,st.sname
fromstudentst
joinscoresc
onst.sno=sc.sno
joincourseco
onco.cno=sc.cno
andco.cname='JAVA程序设计';
注:1.对于sql86和sql92的区别见这篇文章:http://blog.csdn.net/lk_blog/article/details/7580300
2.如果您在看的过程中觉得不熟悉的地方很多,建议您先看此文:http://blog.csdn.net/lk_blog/article/details/7585501

--(一)查询选修课程名称为'JAVA程序设计'的学员学号和姓名 
--方法一:
--1.查询'JAVA程序设计'这门课程的课程号
--2.在成绩表中查询课程号为步骤1的课程号的成绩信息..
--3.在学生表中查询学号为步骤2结果的学生信息
select st.sno, st.sname
  from student st
 where st.sno in
       (select sno
          from score sc
         where sc.cno =
               (select co.cno from course co where co.cname = 'JAVA程序设计'));

--方法二:
--1.查询'JAVA程序设计'这门课程的课程号
--2.在成绩表和学生表关联结果中查询课程号等于步骤1的课程号的成绩信息.
--sql86
select st.sno, st.sname
  from student st, score sc
 where st.sno = sc.sno
   and sc.cno =
       (select co.cno from course co where co.cname = 'JAVA程序设计');
       
--sql92
select st.sno, st.sname
  from student st
  join score sc
    on st.sno = sc.sno
 where sc.cno =
       (select co.cno from course co where co.cname = 'JAVA程序设计');
       
--方法三:
--1.在成绩表和课程表关联结果中查询选修了'JAVA程序设计'这门课的学生的学号
--2.在学生表中查询步骤1中的学号的学生的详细信息.
--sql86
select st.sno, st.sname
  from student st
 where st.sno in (select sc.sno
                         from score sc, course co
                        where sc.cno = co.cno
                          and co.cname = 'JAVA程序设计');
 
--sql92 
 select st.sno, st.sname
   from student st
  where st.sno in (select sc.sno
                          from score sc
                          join course co
                            on sc.cno = co.cno  
                      where co.cname = 'JAVA程序设计');
                      
 --方法四:                     
 --在成绩表和课程表和学生表三表关联的结果中过滤得到选修了 'JAVA程序设计'的学生基本信息   
 --sql86
  select st.sno, st.sname
   from student st, score sc, course co
  where st.sno = sc.sno
    and co.cno = sc.cno
    and co.cname = 'JAVA程序设计';
             
 --sql92(1) 用where过滤
 select st.sno, st.sname
  from student st
  join score sc
    on st.sno = sc.sno
  join course co
    on co.cno = sc.cno
 where co.cname = 'JAVA程序设计';

--sql92(2) 在关联条件中过滤
 select st.sno, st.sname
   from student st
   join score sc
     on st.sno = sc.sno
   join course co
     on co.cno = sc.cno
    and co.cname = 'JAVA程序设计';
 
注: 1.对于sql86 和sql92的区别见这篇文章: http://blog.csdn.net/lk_blog/article/details/7580300
    2.如果您在看的过程中觉得不熟悉的地方很多,建议您先看此文: http://blog.csdn.net/lk_blog/article/details/7585501

进入正文:

[sql] view plain copy print ?

--(一)查询不选修课程编号为'1'的学员姓名和所属单位
--1.在成绩表中查询课程号为'1'的所有学生学号
--2.在学生表中查询学号不在步骤1中的学生的基本信息.
selectst.sname,st.sdree
fromt_studentst
wherest.snonotin(selectsc.snofromt_scorescwheresc.cno='1');
--(二)查询平均成绩大于85的所有学生的学号、姓名和平均成绩?
selectsc.sno,st.sname
fromt_scoresc
joint_studentst
onsc.sno=st.sno
groupbysc.sno,st.sname
havingavg(sc.grade)>85;
--(三)查询课程名称为"JAVA程序设计",且分数低于60的学生姓名和分数
select*
fromt_scoresc
joint_courseco
onsc.cno=co.cno
wheresc.grade<60
andco.cname='JAVA程序设计'
select*
fromt_scoresc
joint_courseco
onsc.cno=co.cno
andsc.grade<60
andco.cname='JAVA程序设计'
--(四)查询任何一门课程成绩全部都在70分以上的姓名、课程名称和分数?
--1.查询出成绩小于70分的学生的学号.
--2.将学生,成绩,课程三张表作关联.
--3.在关联表中过滤出不在步骤1查询结果中的学生信息.
selectst.sname,co.cname,sc.grade
fromt_studentst
joint_scoresc
onst.sno=sc.sno
joint_courseco
onsc.cno=co.cno
wherest.snonotin(selectsc1.cnofromt_scoresc1wheresc1.grade<70);
--(五)查询出选了课的学生的人数.
selectcount(distinct(sc.sno))fromt_scoresc;
--(六)查询每门课程被选课的学生数
selectsc.cno,count(distinct(sc.sno))fromt_scorescgroupbysc.cno;
--(七)查询选了全部课程的学员姓名和所属单位
--1.在课程表中查询出所有课程的数量
--2.在成绩表中查询出学生选课数等于步骤1中总选课数的学生的学号,注意要用distinct,having中可以使用count,where中不能使用count.
--3.在学生表中查出步骤2中学生的基本信息.
selectst.sname,st.sdree
fromt_studentst
wherest.snoin
(selectsc.sno
fromt_scoresc
groupbysc.sno
havingcount(distinctsc.cno)=(selectcount(distinctsc1.cno)
fromt_coursesc1));
--(八)查询没有学全所有课的同学的学号、姓名
selectst.sname,st.sdree
fromt_studentst
wherest.snoin
(selectsc.sno
fromt_scoresc
groupbysc.sno
havingcount(distinctsc.cno)!=(selectcount(*)fromt_course));
--(九)查询选修课程超过5门的学员学号和所属单位
--1.在成绩表中查询出选课程超过5门的学生学号.
--2.在学生表中查询步骤1中学号的学生的基本信息.
selectst.sname,st.sdree
fromt_studentst
wherest.snoin(selectsc.sno
fromt_scoresc
groupbysc.sno
havingcount(distinctsc.cno)>5);
--(十)查询出没有选课的学生基本信息
--1.在成绩表中查询出所有选过课的学生的学号.
--2.在学生表中查询出步骤1中学生的基本信息.
select*
fromt_studentst
wherest.snonotin(selectsc.snofromt_scoresc);
--下面的两个sql等价,在成绩表中数据量很大时使用下面的sql
select*
fromt_studentst
wherest.snonotin(selectdistinct(sc.sno)fromt_scoresc);
select*
fromt_studentst
wherest.snonotin(selectsc.snofromt_scorescgroupbysc.sno);
--(十一)列出有二门以上不及格课程的学生姓名及其平均成绩
--方法一
--1.在成绩表中查询出2门不及格学生的学号,结果记作t1
--2.将学生表和t1和成绩表三表作关联得到关联表,在关联表中取学生基本信息和平均成绩.
--sql92
selectst.sno,st.sname,avg(sc.grade)
fromt_studentst
join(selectsc.sno
fromt_scoresc
wheresc.grade<60
groupbysc.sno
havingcount(distinctsc.cno)>2)t1
onst.sno=t1.sno
joint_scoresc
onsc.sno=t1.sno
groupbyst.sno,st.sname;
--sql86
selectst.sno,st.sname,avg(sc.grade)
fromt_studentst,
t_scoresc,
(selectsc.sno
fromt_scoresc
wheresc.grade<60
groupbysc.sno
havingcount(distinctsc.cno)>2)t1
wherest.sno=t1.sno
andsc.sno=t1.sno
groupbyst.sno,st.sname;
--方法二:
--1.在成绩表中查询出2门不及格学生的学号
--2.将学生表和成绩表通过学号作关联并根据步骤1中的结果作过滤,在关联结果中取出学生基本信息和平均成绩
selectst.sno,st.sname,avg(sc.grade)
fromt_studentst
joint_scoresc
onst.sno=sc.sno
wherest.snoin(selectsc.sno
fromt_scoresc
wheresc.grade<60
groupbysc.sno
havingcount(distinctsc.cno)>2)
groupbyst.sno,st.sname;
--(十二)查询平均成绩大于60分的同学的学号和平均成绩
--学生表和课程表关联,在having子句中过滤平均成绩大于60分.
selectst.sno,avg(sc.grade)
fromt_studentst,t_scoresc
wherest.sno=sc.sno
groupbyst.sno
havingavg(sc.grade)>60;
--1.学生表和课程表关联,将关联的结果记作t1
--2.在t1中过滤平均成绩大于60的学生学号.
selectt1.sno,t1.avg_grade
from(selectst.sno,avg(sc.grade)avg_grade
fromt_studentst,t_scoresc
wherest.sno=sc.sno
groupbyst.sno)t1
wheret1.avg_grade>60;
--(十三)查询出只选修了一门课程的全部学生的学号和姓名
--方法一:
--1.将学生表和成绩表作关联,在分组函数中使用having子句过滤出只选了一门课程的学生基本信息.
selectsc.sno,st.sname
fromt_scoresc
joint_studentst
onsc.sno=st.sno
groupbysc.sno,st.sname
havingcount(distinctsc.cno)=1;
--方法二:
--1.在成绩表中查找学号,分组函数的过滤条件判断只选择了一门课程的学生.
--2.在学生表中查找学号在步骤1中的值的学生的基本信息
selectst.sno,st.sname
fromt_studentst
wherest.snoin(selectsc.sno
fromt_scoresc
groupbysc.sno
havingcount(distinctsc.cno)=1);
--(十四)查询至少有一门课与学号为"1"的同学所学相同的同学的学号和姓名
selectst.sno,st.sname
fromt_studentst
joint_scoresc1
onst.sno=sc1.sno
wheresc1.cnoin(selectsc.cnofromt_scorescwheresc.sno='1')
groupbyst.sno,st.sname;
--(十五)列出既学过"1"号课程,又学过"2"号课程的所有学生姓名
--1.将成绩表和课程表作关联,在关联条件中作过滤查询出既选过课程'1'又选过课程'2'的学生的学号,注意看co.cnoin('1','2')和havingcount(distinctsc.cno)=2的位置.
--2.在学生表中根据步骤1的结果作过滤查询出学生的基本信息.
--方法一:
--sql86
selectst.sno,st.sname
fromt_studentst,
(selectsc.sno
fromt_scoresc,t_courseco
wheresc.cno=co.cno
andco.cnoin('1','2')
groupbysc.sno
havingcount(distinctsc.cno)=2)t1
wherest.sno=t1.sno;
--sql92
selectst.sno,st.sname
fromt_studentstjoin
(selectsc.sno
fromt_scorescjoint_courseco
onsc.cno=co.cno
andco.cnoin('1','2')
groupbysc.sno
havingcount(distinctsc.cno)=2)t1
onst.sno=t1.sno;
--方法二:
--sql86
selectst.sno,st.sname
fromt_studentst
wherest.snoin(selectsc.sno
fromt_scoresc,t_courseco
wheresc.cno=co.cno
andco.cnoin('1','2')
groupbysc.sno
havingcount(distinctsc.cno)=2);
--sql92
selectst.sno,st.sname
fromt_studentst
wherest.snoin(selectsc.sno
fromt_scoresc
joint_courseco
onsc.cno=co.cno
andco.cnoin('1','2')
groupbysc.sno
havingcount(distinctsc.cno)=2);
--(十六)查询至少学过学号为"1"的同学所有门课的同学学号和姓名
--1.查询出'1'号同学学习的全部课程.
--2.查询出'1'号同学学习全部课程的数量.
--3.将课程表和成绩表做关联,在关联表中查询出学生的学号,关联条件中加入过滤条件[课程号在步骤1查询结果范围内],过滤条件中加入数量等级步骤2中得到的数量.
--4.在学生表中查询步骤3中的学号的学生的基本信息.
selectst.sno,st.sname
fromt_studentst
wherest.snoin
(selectsc.sno
fromt_scoresc
joint_courseco
onsc.cno=co.cno
andco.cnoin(selectsc.cnofromt_scorescwheresc.sno='1')
groupbysc.sno
havingcount(distinctsc.cno)=(selectcount(distinctsc.cno)
fromt_scoresc
wheresc.sno='1'))
--(十七)查询和"6"号同学学习的课程完全相同的同学的学号和姓名
--分析:要查询与6号同学完全相同的课程的学生信息,等价于学过6号同学的学过的所有课程并且选课数量与6同学选课数量相等.
--方法一:
--1.查询出'1'号同学学习的全部课程.
--2.查询出'1'号同学学习全部课程的数量.
--3.将课程表和成绩表做关联,在关联表中查询出学生的学号和选课数量,记作t2,关联条件中加入过滤条件[课程号在步骤1查询结果范围内],过滤条件中加入数量等级步骤2中得到的数量.
--4.在成绩表中查询出学号和每个学生选课数量.得到结果记作:t1
--5.将步骤3中的t2和步骤4中的t1通过学生学号关联,添加过滤条件,t1中的选课数量等于t2中的选课数量.
--6.在学生表中查询不步骤5的学生学号的基本信息.
selectst.sno,st.sname
fromt_studentst
wherest.snoin
(selectt1.sno
from(selectsc_a.sno,count(distinctsc_a.cno)num_outer
fromt_scoresc_a
groupbysc_a.sno)t1
join(selectsc.sno,count(distinctsc.cno)num_inner
fromt_scoresc
joint_courseco
onsc.cno=co.cno
andco.cnoin
(selectsc.cnofromt_scorescwheresc.sno='6')
groupbysc.sno
havingcount(distinctsc.cno)=(selectcount(distinctsc.cno)
fromt_scoresc
wheresc.sno='6'))t2
ont1.sno=t2.sno
wheret1.num_outer=t2.num_inner);
--(十八)列出"1"号课成绩比"2"号课成绩高的所有学生的学号及其"1"号课和"2"号课的成绩
--1.将学生表和课程表作两次关联,一次关联用于取该学生课程'1'的成绩,另一次关联用于取该学生课程'2'的成绩.
--sql86
selectst.sno,st.sname,sc_a.grade,sc_b.grade
fromt_studentst,t_scoresc_a,t_scoresc_b
wheresc_a.cno='1'
andsc_b.cno='2'
andst.sno=sc_a.sno
andst.sno=sc_b.sno
andsc_a.grade>sc_b.grade
--sql92
selectst.sno,st.sname,sc_a.grade,sc_b.grade
fromt_studentst
joint_scoresc_a
onst.sno=sc_a.sno
joint_scoresc_b
onst.sno=sc_b.sno
wheresc_a.cno='1'
andsc_b.cno='2'
andsc_a.grade>sc_b.grade
--(十九)查询所有同学的学号、姓名、选课数、总成绩
selectst.sno,st.sname,count(sc.cno),sum(sc.grade)
fromt_studentst,t_scoresc
wherest.sno=sc.sno
groupbyst.sno,st.sname;
--(二十)查询课程成绩小于60分的同学的学号,姓名,课程名,成绩
--下面两条sql虽然结果相同,但意义不同,注意理解一下哦.
--1.将学生表,课程表,成绩表作关联
--2.对关联后的结果作过滤,过滤出成绩小于60的学生基本信息.
selectst.sno,st.sname,co.cname,sc.grade
fromt_scoresc
joint_studentst
onsc.sno=st.sno
joint_courseco
onsc.cno=co.cno
wheresc.grade<60
--1.将学生表,课程表,成绩表作关联,在关联条件中过滤成绩小于60.
selectst.sno,st.sname,co.cname,sc.grade
fromt_scoresc
joint_studentst
onsc.sno=st.sno
joint_courseco
onsc.cno=co.cno
andsc.grade<60
--(二十一)按平均成绩从到低显示所有学生的"JAVA程序设计"、"J2EE从入门到精通"、"EJB及设计模式"三门的课程成绩,
--并按如下形式显示:学生ID,姓名,JAVA程序设计,J2EE从入门到精通,EJB及设计模式,有效课程数,有效课程平均分
--1.将成绩表和课程表关联得到结果记作:t1,关联时的条件选择只统计以上三门课程.
--2.按题目中的要求组织统计结果.
selectst.sno,
st.sname,
sum(decode(t1.cname,'JAVA程序设计',t1.grade))JAVA程序设计,
sum(decode(t1.cname,'J2EE从入门到精通',t1.grade))J2EE从入门到精通,
sum(decode(t1.cname,'EJB及设计模式',t1.grade))EJB及设计模式,
count(distinctt1.grade)有效课程数,
avg(t1.grade)有效课程平均分
fromt_studentst
join(select*
fromt_scoresc
joint_courseco
onsc.cno=co.cno
andco.cnamein
('JAVA程序设计','J2EE从入门到精通','EJB及设计模式'))t1
onst.sno=t1.sno
groupbyst.sno,st.sname
--将decode可以换成casewhen第一种形式
selectst.sno,
st.sname,
sum(caset1.cname
when'JAVA程序设计'then
t1.grade
end)JAVA程序设计,
sum(caset1.cname
when'J2EE从入门到精通'then
t1.grade
end)J2EE从入门到精通,
sum(caset1.cname
when'EJB及设计模式'then
t1.grade
end)EJB及设计模式,
count(distinctt1.grade)有效课程数,
avg(t1.grade)有效课程平均分
fromt_studentst
join(select*
fromt_scoresc
joint_courseco
onsc.cno=co.cno
andco.cnamein
('JAVA程序设计','J2EE从入门到精通','EJB及设计模式'))t1
onst.sno=t1.sno
groupbyst.sno,st.sname
--将decode可以换成casewhen第二种形式
selectst.sno,
st.sname,
sum(case
whent1.cname='JAVA程序设计'then
t1.grade
end)JAVA程序设计,
sum(case
whent1.cname='J2EE从入门到精通'then
t1.grade
end)J2EE从入门到精通,
sum(case
whent1.cname='EJB及设计模式'then
t1.grade
end)EJB及设计模式,
count(distinctt1.grade)有效课程数,
avg(t1.grade)有效课程平均分
fromt_studentst
join(select*
fromt_scoresc
joint_courseco
onsc.cno=co.cno
andco.cnamein
('JAVA程序设计','J2EE从入门到精通','EJB及设计模式'))t1
onst.sno=t1.sno
groupbyst.sno,st.sname
--(二十二)查询各科成绩最高和最低的分:以如下形式显示:课程ID,课程名,最高分,最低分
selectsc.cno,co.cname,max(grade),min(grade)
fromt_scoresc
joint_courseco
onsc.cno=co.cno
groupbysc.cno,co.cname
--(二十三)按各科平均成绩从低到高和及格率的百分数从高到低顺序
--1.在成绩表中查出课程号,平均成绩,课程人数,记作:t1
--2.在成绩表中查出课程号,及格的课程人数,记作:t2
--3.将步骤1中的成绩和步骤2中的课程2关联,查出所要的结果并排序.
selectt1.cno,t1.avg_num平均成绩,(count_num1/count_num)*100及格率
from(selectsc.cno,avg(grade)avg_num,count(distinctsc.sno)count_num
fromt_scoresc
groupbysc.cno)t1
join(selectsc1.cno,count(distinctsc1.sno)count_num1
fromt_scoresc1
wheresc1.grade>60
groupbysc1.cno)t2
ont1.cno=t2.cno
orderbyt1.avg_numasc,及格率desc
--(二十四)统计各科成绩,各分数段人数:课程ID,课程名称,[100-90]优,[90-80]良,[80-70]中,[70-60]一般,[<60]不及格
--1.在成绩表中根据成绩值分段
--2.将步骤1中的结果与课程表关联.
selectsc.cno,co.cname,
sum(case
whensc.grade>90then
1
end)优,
sum(case
whensc.grade>80andsc.grade<90then
1
end)良,
sum(case
whensc.grade>70andsc.grade<80then
1
end)中,
sum(case
whensc.grade>60andsc.grade<70then
1
end)一般,
sum(case
whensc.grade<60then
1
end)不及格
fromt_scorescjoint_coursecoonsc.cno=co.cno
groupbysc.cno,co.cname
--(二十五)查询学生平均成绩及其名次
selectst.sno,st.sname,avg(sc.grade)avg_num
fromt_scoresc
joint_studentst
onsc.sno=st.sno
groupbyst.sno,st.sname
orderbyavg_numdesc
--(二十六)查询课程号分别为1,2,3的课程,成绩前三名的学生基本信息:(不考虑成绩并列情况)
--方法一:
--1.分别查出1,2,3各自的前3名的学生的学号,并用unionall将结果集关联.
--2.在学生表中查询步骤1中查到的id的学生的基本信息.
select*
fromt_student
wheresnoin(selectt1.sno
from(selectsc1.*
fromt_scoresc1
wheresc1.cno=1
orderbysc1.gradedesc)t1
whererownum<4
unionall
selectt1.sno
from(selectsc1.*
fromt_scoresc1
wheresc1.cno=2
orderbysc1.gradedesc)t1
whererownum<4
unionall
selectt1.sno
from(selectsc1.*
fromt_scoresc1
wheresc1.cno=3
orderbysc1.gradedesc)t1
whererownum<4)
--方法二:
--rank()over(Partition..orderby...)是按照某个字段的值进行分组并编号
selectt1.cno,t1.sno,t1.grade,r
from(selectsc.sno,
sc.cno,
sc.grade,
rank()over(partitionbysc.cnoorderbygradedesc)r
fromt_scoresc)t1
wherer<4
andt1.cnoin(1,2,3)
orderbyt1.cno,t1.sno,r;
--(二十七)查询各科成绩前三名的记录(不考虑成绩并列情况)
--rank()over(Partition..orderby...)是按照某个字段的值进行分组并编号
selectt1.cno,t1.sno,t1.grade,r
from(selectsc.sno,
sc.cno,
sc.grade,
rank()over(partitionbysc.cnoorderbygradedesc)r
fromt_scoresc)t1
wherer<4
orderbyt1.cno,t1.sno,r;