写sql是程序员基本功课,找工作面试一般必有sql题,实际工作中对sql的需求更是千变万化,所以掌握好sql对于一个程序
员来说是件非常重要的事情.本文通过一个简单易懂的关系(学生/课程/成绩关系)尽量构造出各种各样的需求来提高我们写sql的功底
,其中有些sql还是有一些难度的,大多数sql我都给出了思考思路,希望本人N天的劳动成果对您有所帮助.
--创建表 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;
在思维举重前,先来一个热身运动吧:
--(一)查询选修课程名称为'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写的并不完美,希望大家不吝赐教.最后提一点小小的要求,希望大家对本文积极进行评论,大家的评论是我改进和提高的动力,我希望在和大家的交流中得到提高.
表关系:
建表和初始化sql(本例子使用oralce数据库):
- --创建表
- 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;
- --(一)查询选修课程名称为'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
进入正文:
- --(一)查询不选修课程编号为'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;