sql -高级程序员必修课

 

1. --创建表   
2. create table T_STUDENT(sno NUMBER not null,  sname VARCHAR2(30),  sdree VARCHAR2(50),  sage  NUMBER,  ssex  CHAR(2));  
3. alter table T_STUDENT add primary key (SNO);  
4. create table T_SCORE(sno   NUMBER,  cno   NUMBER,  grade NUMBER(4,1), tno NUMBER,  id NUMBER not null);  
5. alter table T_SCORE add primary key (ID);  
6. create table T_COURSE(cno NUMBER not null,  cname VARCHAR2(30));  
7. alter table T_COURSE add primary key (CNO);  
8.   
9. --初始化学生表   
10. insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (1, '李坤', '天融信', 26, '男');  
11. insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (2, '曹贵生', '中银', 26, '男');  
12. insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (3, '柳波', '淘宝', 27, '男');  
13. insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (4, '纪争光', 'IBM', 23, '男');  
14. insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (5, '李学宇', '微软', 25, '女');  
15. insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (6, '李雪琪', '文思', 25, '女');  
16. insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (7, '陈绪', '百度', 26, '男');  
17. insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (8, '韩正阳', '中海油', 24, '男');  
18. insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (9, '陈伟东', '腾讯', 24, '男');  
19. insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (10, '刘兵', '华为', 24, '男');  
20. insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (11, '丁成云', '联想', 25, '女');  
21. insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (12, '王鹏', '中兴', 25, '男');  
22. commit;  
23.   
24. --初始化课程表   
25. insert into T_COURSE (CNO, CNAME) values (1, 'JAVA程序设计');  
26. insert into T_COURSE (CNO, CNAME) values (2, 'ORACLE开发');  
27. insert into T_COURSE (CNO, CNAME) values (3, 'C++程序设计');  
28. insert into T_COURSE (CNO, CNAME) values (4, 'C#程序设计');  
29. insert into T_COURSE (CNO, CNAME) values (5, 'Windows实战');  
30. insert into T_COURSE (CNO, CNAME) values (6, 'Center OS教程');  
31. insert into T_COURSE (CNO, CNAME) values (7, 'Jsp/Servlet开发');  
32. insert into T_COURSE (CNO, CNAME) values (8, 'J2EE从入门到精通');  
33. insert into T_COURSE (CNO, CNAME) values (9, 'EJB及设计模式');  
34. insert into T_COURSE (CNO, CNAME) values (10, 'Javascript/jQuery实战');  
35. insert into T_COURSE (CNO, CNAME) values (11, 'Flash设计');  
36. insert into T_COURSE (CNO, CNAME) values (12, 'HTML/CSS/JAVASCRIPT实战');  
37. insert into T_COURSE (CNO, CNAME) values (13, '精通ASP.NET');  
38. insert into T_COURSE (CNO, CNAME) values (14, 'JBoss入门');  
39. insert into T_COURSE (CNO, CNAME) values (15, 'Spring开发');  
40. commit;  
41.   
42. --初始化成绩表   
43. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (1, 2, 90.0, 2, 1);  
44. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (1, 3, 80.0, 3, 2);  
45. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (1, 4, 90.0, 4, 3);  
46. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 2, 70.0, 2, 4);  
47. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (2, 11, 66.0, 11, 5);  
48. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (2, 15, 77.0, 15, 6);  
49. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (2, 8, 87.0, 8, 7);  
50. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (2, 6, 96.0, 6, 8);  
51. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (3, 2, 89.0, 2, 9);  
52. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (3, 1, 91.0, 1, 10);  
53. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (4, 2, 83.0, 2, 11);  
54. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (5, 4, 73.0, 4, 12);  
55. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (5, 1, 60.0, 1, 13);  
56. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (5, 8, 82.0, 8, 14);  
57. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (6, 8, 90.5, 10, 15);  
58. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (8, 2, 58.0, 2, 16);  
59. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 3, 80.0, 3, 17);  
60. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (9, 11, 65.0, 11, 18);  
61. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (9, 12, 67.0, 12, 19);  
62. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (9, 15, 95.0, 15, 20);  
63. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (9, 13, 59.0, 13, 21);  
64. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (10, 4, 98.0, 4, 22);  
65. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (10, 6, 97.0, 6, 23);  
66. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (10, 7, 96.0, 7, 24);  
67. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 7, 95.0, 7, 25);  
68. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (12, 8, 69.0, 8, 26);  
69. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (12, 9, 85.0, 9, 27);  
70. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (10, 14, 100.0, 14, 28);  
71. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (6, 9, 100.0, 9, 29);  
72. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 1, 59.0, 1, 30);  
73. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 4, 90.0, 4, 31);  
74. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 5, 91.0, 5, 32);  
75. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 6, 58.0, 6, 33);  
76. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 8, 93.0, 8, 34);  
77. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 9, 57.0, 9, 35);  
78. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 10, 95.0, 10, 36);  
79. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 11, 96.0, 11, 37);  
80. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 12, 97.0, 12, 38);  
81. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 13, 98.0, 13, 39);  
82. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 14, 99.0, 14, 40);  
83. insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 15, 89.0, 15, 41);  
84. 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 plaincopyprint?

1. --(一)查询选修课程名称为'JAVA程序设计'的学员学号和姓名    
2. --方法一:   
3. --1.查询'JAVA程序设计'这门课程的课程号   
4. --2.在成绩表中查询课程号为步骤1的课程号的成绩信息..   
5. --3.在学生表中查询学号为步骤2结果的学生信息   
6. select st.sno, st.sname  
7.   from student st  
8.  where st.sno in  
9.        (select sno  
10.           from score sc  
11.          where sc.cno =  
12.                (select co.cno from course co where co.cname = 'JAVA程序设计'));  
13.   
14. --方法二:   
15. --1.查询'JAVA程序设计'这门课程的课程号   
16. --2.在成绩表和学生表关联结果中查询课程号等于步骤1的课程号的成绩信息.   
17. --sql86   
18. select st.sno, st.sname  
19.   from student st, score sc  
20.  where st.sno = sc.sno  
21.    and sc.cno =  
22.        (select co.cno from course co where co.cname = 'JAVA程序设计');  
23.          
24. --sql92   
25. select st.sno, st.sname  
26.   from student st  
27.   join score sc  
28.     on st.sno = sc.sno  
29.  where sc.cno =  
30.        (select co.cno from course co where co.cname = 'JAVA程序设计');  
31.          
32. --方法三:   
33. --1.在成绩表和课程表关联结果中查询选修了'JAVA程序设计'这门课的学生的学号  
34. --2.在学生表中查询步骤1中的学号的学生的详细信息.   
35. --sql86   
36. select st.sno, st.sname  
37.   from student st  
38.  where st.sno in (select sc.sno  
39.                          from score sc, course co  
40.                         where sc.cno = co.cno  
41.                           and co.cname = 'JAVA程序设计');  
42.    
43. --sql92    
44.  select st.sno, st.sname  
45.    from student st  
46.   where st.sno in (select sc.sno  
47.                           from score sc  
48.                           join course co  
49.                             on sc.cno = co.cno    
50.                       where co.cname = 'JAVA程序设计');  
51.                         
52.  --方法四:                        
53.  --在成绩表和课程表和学生表三表关联的结果中过滤得到选修了 'JAVA程序设计'的学生基本信息     
54.  --sql86   
55.   select st.sno, st.sname  
56.    from student st, score sc, course co  
57.   where st.sno = sc.sno  
58.     and co.cno = sc.cno  
59.     and co.cname = 'JAVA程序设计';  
60.                
61.  --sql92(1) 用where过滤   
62.  select st.sno, st.sname  
63.   from student st  
64.   join score sc  
65.     on st.sno = sc.sno  
66.   join course co  
67.     on co.cno = sc.cno  
68.  where co.cname = 'JAVA程序设计';  
69.   
70. --sql92(2) 在关联条件中过滤   
71.  select st.sno, st.sname  
72.    from student st  
73.    join score sc  
74.      on st.sno = sc.sno  
75.    join course co  
76.      on co.cno = sc.cno  
77.     and co.cname = 'JAVA程序设计';  
78.    
79. 注: 1.对于sql86 和sql92的区别见这篇文章: http://blog.csdn.net/lk_blog/article/details/7580300  
80.     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的课程号的成绩信息.--sql86select 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程序设计'); --sql92select 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中的学号的学生的详细信息.--sql86select 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 plaincopyprint?

1. --(一)查询不选修课程编号为'1'的学员姓名和所属单位    
2. --1.在成绩表中查询课程号为'1'的所有学生学号   
3. --2.在学生表中查询学号不在步骤1中的学生的基本信息.   
4. select st.sname, st.sdree  
5.   from t_student st  
6.  where st.sno not in (select sc.sno from t_score sc where sc.cno = '1');  
7.    
8.    
9.  --(二)查询平均成绩大于85的所有学生的学号、姓名和平均成绩?  
10. select sc.sno, st.sname  
11.    from t_score sc  
12.    join t_student st  
13.      on sc.sno = st.sno  
14.   group by sc.sno, st.sname  
15.  having avg(sc.grade) > 85;  
16.    
17.    
18.  --(三)查询课程名称为"JAVA程序设计",且分数低于60的学生姓名和分数   
19. select st.sname, sc.grade  
20.   from t_score sc  
21.   join t_student st  
22.     on sc.sno = st.sno  
23.   join t_course co  
24.     on sc.cno = co.cno  
25.  where sc.grade < 60  
26.    and co.cname = 'JAVA程序设计';  
27.       
28.  select st.sname, sc.grade  
29.    from t_score sc  
30.    join t_student st  
31.      on sc.sno = st.sno  
32.    join t_course co  
33.      on sc.cno = co.cno  
34.     and sc.grade < 60  
35.     and co.cname = 'JAVA程序设计' ;  
36.        
37. select st.sname, sc.grade  
38.   from t_score sc  
39.   join t_student st  
40.     on sc.sno = st.sno  
41.  where sc.cno = (select cno from t_course where cname = 'JAVA程序设计')  
42.    and sc.grade < 60  
43.      
44.   
45. --(四)查询任何一门课程成绩全部都在70分以上的姓名、课程名称和分数?  
46. --1.查询出成绩小于70分的学生的学号.   
47. --2.将学生,成绩,课程三张表作关联.   
48. --3.在关联表中过滤出不在步骤1查询结果中的学生信息.   
49. select st.sname, co.cname, sc.grade  
50.   from t_student st  
51.   join t_score sc  
52.     on st.sno = sc.sno  
53.   join t_course co  
54.     on sc.cno = co.cno  
55.  where st.sno not in (select sc1.sno from t_score sc1 where sc1.grade < 70);  
56.    
57.    
58. --(五)查询出选了课的学生的人数.   
59. select count(distinct(sc.sno)) from t_score sc;  
60.   
61.   
62. --(六)查询每门课程被选课的学生数   
63. select sc.cno, count(distinct(sc.sno)) from t_score sc group by sc.cno;  
64.   
65.                
66. --(七)查询选了全部课程的学员姓名和所属单位   
67. --1.在课程表中查询出所有课程的数量   
68. --2.在成绩表中查询出学生选课数等于步骤1中总选课数的学生的学号,注意要用distinct,having中可以使用count,where中不能使用count.  
69. --3.在学生表中查出步骤2中学生的基本信息.   
70. select st.sname, st.sdree  
71.   from t_student st  
72.  where st.sno in  
73.        (select sc.sno  
74.           from t_score sc  
75.          group by sc.sno  
76.         having count(distinct sc.cno) = (select count(distinct sc1.cno)  
77.                                           from t_course sc1));  
78.                                             
79.                                                                                          
80. --(八) 查询没有学全所有课的同学的学号、姓名   
81. select st.sname, st.sdree  
82.   from t_student st  
83.  where st.sno in  
84.        (select sc.sno  
85.           from t_score sc  
86.          group by sc.sno  
87.         having count(distinct sc.cno) != (select count(*) from t_course));  
88.    
89.           
90. --(九)查询选修课程超过5门的学员学号和所属单位    
91. --1.在成绩表中查询出选课程超过5门的学生学号.   
92. --2.在学生表中查询步骤1中学号的学生的基本信息.   
93. select st.sname, st.sdree  
94.   from t_student st  
95.  where st.sno in (select sc.sno  
96.                          from t_score sc  
97.                         group by sc.sno  
98.                        having count(distinct sc.cno) > 5);  
99.                                                
100.                                                                                   
101. --(十)查询出没有选课的学生基本信息   
102. --1.在成绩表中查询出所有选过课的学生的学号.   
103. --2.在学生表中查询出步骤1中学生的基本信息.   
104. select *  
105.   from t_student st  
106.  where st.sno not in (select sc.sno from t_score sc);  
107.   
108. --下面的两个sql等价,在成绩表中数据量很大时使用下面的sql   
109. select *  
110.   from t_student st  
111.  where st.sno not in (select distinct (sc.sno) from t_score sc);  
112.    
113. select *  
114.   from t_student st  
115.  where st.sno not in (select sc.sno from t_score sc group by sc.sno);  
116.   
117.                                                   
118. --(十一) 列出有二门以上不及格课程的学生姓名及其平均成绩    
119. --方法一   
120. --1.在成绩表中查询出2门不及格学生的学号,结果记作t1   
121. --2.将学生表和t1和成绩表三表作关联得到关联表,在关联表中取学生基本信息和平均成绩.  
122. --sql92      
123. select st.sno, st.sname, avg(sc.grade)  
124.   from t_student st  
125.   join (select sc.sno  
126.           from t_score sc  
127.          where sc.grade < 60  
128.          group by sc.sno  
129.         having count(distinct sc.cno) > 2) t1  
130.     on st.sno = t1.sno  
131.   join t_score sc  
132.     on sc.sno = t1.sno  
133.  group by st.sno, st.sname;  
134.    
135.  --sql86   
136. select st.sno, st.sname, avg(sc.grade)  
137.   from t_student st,  
138.        t_score sc,  
139.        (select sc.sno  
140.           from t_score sc  
141.          where sc.grade < 60  
142.          group by sc.sno  
143.         having count(distinct sc.cno) > 2) t1  
144.  where st.sno = t1.sno  
145.    and sc.sno = t1.sno  
146.  group by st.sno, st.sname;  
147.   
148. --方法二:   
149. --1.在成绩表中查询出2门不及格学生的学号   
150. --2.将学生表和成绩表通过学号作关联并根据步骤1中的结果作过滤,在关联结果中取出学生基本信息和平均成绩                       
151. select st.sno, st.sname, avg(sc.grade)  
152.   from t_student st  
153.   join t_score sc  
154.     on st.sno = sc.sno  
155.  where st.sno in (select sc.sno  
156.                     from t_score sc  
157.                    where sc.grade < 60  
158.                    group by sc.sno  
159.                   having count(distinct sc.cno) > 2)  
160.  group by st.sno, st.sname;  
161.   
162.    
163.  --(十二) 查询平均成绩大于60分的同学的学号和平均成绩  
164. --学生表和课程表关联,在having子句中过滤平均成绩大于60分.   
165. select st.sno, avg(sc.grade)  
166.   from t_student st, t_score sc  
167.  where st.sno = sc.sno  
168.  group by st.sno  
169. having avg(sc.grade) > 60;  
170.   
171. --1.学生表和课程表关联,将关联的结果记作t1   
172. --2.在t1中过滤平均成绩大于60的学生学号.   
173. select t1.sno, t1.avg_grade  
174.   from (select st.sno, avg(sc.grade) avg_grade  
175.           from t_student st, t_score sc  
176.          where st.sno = sc.sno  
177.          group by st.sno) t1  
178.  where t1.avg_grade > 60;  
179.    
180.    
181. --(十三)查询出只选修了一门课程的全部学生的学号和姓名   
182.  --方法一:   
183.  --1.将学生表和成绩表作关联,在分组函数中使用having子句过滤出只选了一门课程的学生基本信息.  
184.  select sc.sno, st.sname  
185.    from t_score sc  
186.    join t_student st  
187.      on sc.sno = st.sno  
188.   group by sc.sno, st.sname  
189.  having count(distinct sc.cno) = 1;  
190.    
191.  --方法二:   
192.  --1.在成绩表中查找学号,分组函数的过滤条件判断只选择了一门课程的学生.   
193.  --2.在学生表中查找学号在步骤1中的值的学生的基本信息   
194. select st.sno,st.sname  
195.   from t_student st  
196.  where st.sno in (select sc.sno  
197.                     from t_score sc  
198.                    group by sc.sno  
199.                   having count(distinct sc.cno) = 1);  
200.                     
201.    
202.                     
203.  --(十四)查询至少有一门课与学号为"1"的同学所学相同的同学的学号和姓名  
204. select st.sno, st.sname  
205.   from t_student st  
206.   join t_score sc1  
207.     on st.sno = sc1.sno  
208.  where sc1.cno in (select sc.cno from t_score sc where sc.sno = '1')  
209.  group by st.sno, st.sname;  
210.   
211.   
212.   
213. --(十五)列出既学过"1"号课程,又学过"2"号课程的所有学生姓名  
214. --1.将成绩表和课程表作关联,在关联条件中作过滤查询出既选过课程'1'又选过课程'2'的学生的学号,注意看 co.cno in ('1', '2')和having count(distinct sc.cno) = 2 的位置.  
215. --2.在学生表中根据步骤1的结果作过滤查询出学生的基本信息.  
216. --方法一:   
217. --sql86   
218. select st.sno, st.sname  
219.   from t_student st,  
220.        (select sc.sno  
221.           from t_score sc, t_course co  
222.          where sc.cno = co.cno  
223.            and co.cno in ('1', '2')  
224.          group by sc.sno  
225.         having count(distinct sc.cno) = 2) t1  
226.  where st.sno = t1.sno;  
227.    
228.  --sql92   
229.  select st.sno, st.sname  
230.   from t_student st join   
231.        (select sc.sno  
232.           from t_score sc join t_course co  
233.          on sc.cno = co.cno  
234.            and co.cno in ('1', '2')  
235.          group by sc.sno  
236.         having count(distinct sc.cno) = 2) t1  
237.  on st.sno = t1.sno;  
238.    
239.  --方法二:   
240.  --sql86   
241.  select st.sno, st.sname  
242.    from t_student st  
243.   where st.sno in (select sc.sno  
244.                      from t_score sc, t_course co  
245.                     where sc.cno = co.cno  
246.                       and co.cno in ('1', '2')  
247.                     group by sc.sno  
248.                    having count(distinct sc.cno) = 2);  
249.    
250. --sql92    
251.   select st.sno, st.sname  
252.     from t_student st  
253.    where st.sno in (select sc.sno  
254.                       from t_score sc  
255.                       join t_course co  
256.                         on sc.cno = co.cno  
257.                        and co.cno in ('1', '2')  
258.                      group by sc.sno  
259.                     having count(distinct sc.cno) = 2);  
260.    
261.    
262.    
263.  --(十六)查询至少学过学号为"1"的同学所有门课的同学学号和姓名  
264.  --1.查询出'1'号同学学习的全部课程.   
265.  --2.查询出'1'号同学学习全部课程的数量.   
266.  --3.将课程表和成绩表做关联,在关联表中查询出学生的学号,关联条件中加入过滤条件[课程号在步骤1查询结果范围内],过滤条件中加入数量等级步骤2中得到的数量.  
267.  --4.在学生表中查询步骤3中的学号的学生的基本信息.   
268. select st.sno, st.sname  
269.   from t_student st  
270.  where st.sno in  
271.        (select sc.sno  
272.           from t_score sc  
273.           join t_course co  
274.             on sc.cno = co.cno  
275.            and co.cno in (select sc.cno from t_score sc where sc.sno = '1')  
276.          group by sc.sno  
277.         having count(distinct sc.cno) = (select count(distinct sc.cno)  
278.                                           from t_score sc  
279.                                          where sc.sno = '1'))  
280.                                            
281.     
282.                                            
283.  --(十七)查询和"6"号同学学习的课程完全相同的同学的学号和姓名  
284.  --分析:要查询与6号同学完全相同的课程的学生信息,等价于学过6号同学的学过的所有课程并且选课数量与6同学选课数量相等.  
285.  --方法一:    
286.  --1.查询出'1'号同学学习的全部课程.   
287.  --2.查询出'1'号同学学习全部课程的数量.   
288.  --3.将课程表和成绩表做关联,在关联表中查询出学生的学号和选课数量,记作 t2,关联条件中加入过滤条件[课程号在步骤1查询结果范围内],过滤条件中加入数量等级步骤2中得到的数量.  
289.  --4.在成绩表中查询出学号和每个学生选课数量.得到结果记作: t1  
290.  --5.将步骤3中的t2和步骤4中的t1通过学生学号关联,添加过滤条件,t1中的选课数量等于t2中的选课数量.  
291.  --6.在学生表中查询不步骤5的学生学号的基本信息.   
292.  select st.sno, st.sname  
293.    from t_student st  
294.   where st.sno in  
295.         (select t1.sno  
296.            from (select sc_a.sno, count(distinct sc_a.cno) num_outer  
297.                    from t_score sc_a  
298.                   group by sc_a.sno) t1  
299.            join (select sc.sno, count(distinct sc.cno) num_inner  
300.                   from t_score sc  
301.                   join t_course co  
302.                     on sc.cno = co.cno  
303.                    and co.cno in  
304.                        (select sc.cno from t_score sc where sc.sno = '6')  
305.                  group by sc.sno  
306.                 having count(distinct sc.cno) = (select count(distinct sc.cno)  
307.                                                   from t_score sc  
308.                                                  where sc.sno = '6')) t2  
309.              on t1.sno = t2.sno  
310.           where t1.num_outer = t2.num_inner);  
311.             
312.     
313.             
314.  --(十八)列出"1"号课成绩比"2"号课成绩高的所有学生的学号及其"1"号课和"2"号课的成绩  
315.  --1.将学生表和课程表作两次关联,一次关联用于取该学生课程'1'的成绩,另一次关联用于取该学生课程'2'的成绩.  
316.  --sql86   
317. select st.sno, st.sname, sc_a.grade, sc_b.grade  
318.   from t_student st, t_score sc_a, t_score sc_b  
319.  where sc_a.cno = '1'  
320.    and sc_b.cno = '2'  
321.    and st.sno = sc_a.sno  
322.    and st.sno = sc_b.sno  
323.    and sc_a.grade > sc_b.grade  
324.      
325.  --sql92   
326. select st.sno, st.sname, sc_a.grade, sc_b.grade  
327.   from t_student st  
328.   join t_score sc_a  
329.     on st.sno = sc_a.sno  
330.   join t_score sc_b  
331.     on st.sno = sc_b.sno  
332.  where sc_a.cno = '1'  
333.    and sc_b.cno = '2'  
334.    and sc_a.grade > sc_b.grade  
335.      
336.   
337.   
338. --(十九)查询所有同学的学号、姓名、选课数、总成绩   
339. select st.sno, st.sname, count(sc.cno), sum(sc.grade)  
340.   from t_student st, t_score sc  
341.  where st.sno = sc.sno  
342.  group by st.sno, st.sname;  
343.   
344.   
345.   
346. --(二十)查询课程成绩小于60分的同学的学号,姓名,课程名,成绩   
347. --下面两条sql虽然结果相同,但意义不同,注意理解一下哦.  
348. --1.将学生表,课程表,成绩表作关联   
349. --2.对关联后的结果作过滤,过滤出成绩小于60的学生基本信息.  
350. select st.sno, st.sname, co.cname, sc.grade  
351.   from t_score sc  
352.   join t_student st  
353.     on sc.sno = st.sno  
354.   join t_course co  
355.     on sc.cno = co.cno  
356.  where sc.grade < 60  
357.   
358. --1.将学生表,课程表,成绩表作关联,在关联条件中过滤成绩小于60.   
359.  select st.sno, st.sname, co.cname, sc.grade  
360.   from t_score sc  
361.   join t_student st  
362.     on sc.sno = st.sno  
363.   join t_course co  
364.     on sc.cno = co.cno  
365. and sc.grade <60  
366.   
367.   
368.   
369. --(二十一)按平均成绩从到低显示所有学生的"JAVA程序设计"、"J2EE从入门到精通"、"EJB及设计模式"三门的课程成绩,  
370. --并按如下形式显示: 学生ID,姓名,JAVA程序设计,J2EE从入门到精通,EJB及设计模式,有效课程数,有效课程平均分  
371. --1.将成绩表和课程表关联得到结果记作: t1, 关联时的条件选择只统计以上三门课程.  
372. --2.按题目中的要求组织统计结果.   
373. select st.sno,  
374.        st.sname,  
375.        sum(decode(t1.cname, 'JAVA程序设计', t1.grade)) JAVA程序设计,  
376.        sum(decode(t1.cname, 'J2EE从入门到精通', t1.grade)) J2EE从入门到精通,  
377.        sum(decode(t1.cname, 'EJB及设计模式', t1.grade)) EJB及设计模式,  
378.        count(distinct t1.grade) 有效课程数,  
379.        avg(t1.grade) 有效课程平均分  
380.   from t_student st  
381.   join (select *  
382.           from t_score sc  
383.           join t_course co  
384.             on sc.cno = co.cno  
385.            and co.cname in  
386.                ('JAVA程序设计', 'J2EE从入门到精通', 'EJB及设计模式')) t1  
387.     on st.sno = t1.sno  
388.  group by st.sno, st.sname  
389.      
390.  --将decode可以换成case when 第一种形式   
391.  select st.sno,  
392.         st.sname,  
393.         sum(case t1.cname  
394.               when 'JAVA程序设计' then  
395.                t1.grade  
396.             end) JAVA程序设计,  
397.          sum(case t1.cname  
398.               when 'J2EE从入门到精通' then  
399.                t1.grade  
400.             end) J2EE从入门到精通,  
401.              sum(case t1.cname  
402.               when 'EJB及设计模式' then  
403.                t1.grade  
404.             end) EJB及设计模式,  
405.         count(distinct t1.grade) 有效课程数,  
406.         avg(t1.grade) 有效课程平均分  
407.    from t_student st  
408.    join (select *  
409.            from t_score sc  
410.            join t_course co  
411.              on sc.cno = co.cno  
412.             and co.cname in  
413.                 ('JAVA程序设计', 'J2EE从入门到精通', 'EJB及设计模式')) t1  
414.      on st.sno = t1.sno  
415.   group by st.sno, st.sname  
416.     
417.     
418.  --将decode可以换成case when 第二种形式   
419.   select st.sno,  
420.          st.sname,  
421.          sum(case  
422.                when t1.cname = 'JAVA程序设计' then  
423.                 t1.grade  
424.              end) JAVA程序设计,  
425.          sum(case  
426.                when t1.cname = 'J2EE从入门到精通' then  
427.                 t1.grade  
428.              end) J2EE从入门到精通,  
429.          sum(case  
430.                when t1.cname = 'EJB及设计模式' then  
431.                 t1.grade  
432.              end) EJB及设计模式,  
433.          count(distinct t1.grade) 有效课程数,  
434.          avg(t1.grade) 有效课程平均分  
435.     from t_student st  
436.     join (select *  
437.             from t_score sc  
438.             join t_course co  
439.               on sc.cno = co.cno  
440.              and co.cname in  
441.                  ('JAVA程序设计', 'J2EE从入门到精通', 'EJB及设计模式')) t1  
442.       on st.sno = t1.sno  
443.    group by st.sno, st.sname  
444.    
445.    
446. --(二十二)查询各科成绩最高和最低的分:以如下形式显示:课程ID,课程名,最高分,最低分  
447. select sc.cno, co.cname, max(grade), min(grade)  
448.   from t_score sc  
449.   join t_course co  
450.     on sc.cno = co.cno  
451.  group by sc.cno, co.cname  
452.   
453.   
454. --(二十三)按各科平均成绩从低到高和及格率的百分数从高到低顺序    
455. --1.在成绩表中查出课程号,平均成绩,课程人数,记作 : t1  
456. --2.在成绩表中查出课程号,及格的课程人数,记作 : t2   
457. --3.将步骤1中的成绩和步骤2中的课程2关联,查出所要的结果并排序.  
458. select t1.cno, t1.avg_num 平均成绩, (count_num1 / count_num) * 100 及格率  
459.   from (select sc.cno, avg(grade) avg_num, count(distinct sc.sno) count_num  
460.           from t_score sc  
461.          group by sc.cno) t1  
462.   join (select sc1.cno, count(distinct sc1.sno) count_num1  
463.           from t_score sc1  
464.          where sc1.grade > 60  
465.          group by sc1.cno) t2  
466.     on t1.cno = t2.cno  
467.  order by t1.avg_num asc, 及格率 desc  
468.    
469.    
470. --(二十四)统计各科成绩,各分数段人数:课程ID,课程名称,[100-90]优,[90-80]良,[80-70]中,[70-60]一般,[<60]不及格  
471. --1.在成绩表中根据成绩值分段   
472. --2.将步骤1中的结果与课程表关联.   
473. select sc.cno,co.cname,  
474.        sum(case  
475.              when sc.grade > 90 then  
476.               1  
477.            end) 优,  
478.        sum(case  
479.              when sc.grade > 80 and sc.grade < 90 then  
480.               1  
481.            end) 良,  
482.        sum(case  
483.              when sc.grade > 70 and sc.grade < 80 then  
484.               1  
485.            end) 中,  
486.        sum(case  
487.              when sc.grade > 60 and sc.grade < 70 then  
488.               1  
489.            end) 一般,  
490.        sum(case  
491.              when sc.grade < 60 then  
492.               1  
493.            end) 不及格  
494.   from t_score sc join t_course co on sc.cno = co.cno  
495.  group by sc.cno,co.cname  
496.   
497. --(二十五)查询学生平均成绩及其名次   
498. select st.sno, st.sname, avg(sc.grade) avg_num  
499.   from t_score sc  
500.   join t_student st  
501.     on sc.sno = st.sno  
502.  group by st.sno, st.sname  
503.  order by avg_num desc  
504.   
505.   
506.   
507. --(二十六)查询课程号分别为1,2,3的课程,成绩前三名的学生基本信息:(不考虑成绩并列情况)  
508. --方法一:   
509. --1.分别查出1,2,3各自的前3名的学生的学号,并用union all将结果集关联.  
510. --2.在学生表中查询步骤1中查到的id的学生的基本信息.   
511. select *  
512.   from t_student  
513.  where sno in (select t1.sno  
514.                  from (select sc1.*  
515.                          from t_score sc1  
516.                         where sc1.cno = 1  
517.                         order by sc1.grade desc) t1  
518.                 where rownum < 4  
519.                union all  
520.                select t1.sno  
521.                  from (select sc1.*  
522.                          from t_score sc1  
523.                         where sc1.cno = 2  
524.                         order by sc1.grade desc) t1  
525.                 where rownum < 4  
526.                union all  
527.                select t1.sno  
528.                  from (select sc1.*  
529.                          from t_score sc1  
530.                         where sc1.cno = 3  
531.                         order by sc1.grade desc) t1  
532.                 where rownum < 4)  
533.   
534. --方法二:   
535.  --rank() over(Partition .. order by ...) 是按照某个字段的值进行分组并编号  
536. select t1.cno, t1.sno, t1.grade, r  
537.   from (select sc.sno,  
538.                sc.cno,  
539.                sc.grade,  
540.                rank() over(partition by sc.cno order by grade desc) r  
541.           from t_score sc) t1  
542.  where r < 4  
543.    and t1.cno in (1, 2, 3)  
544.  order by t1.cno, t1.sno, r;  
545.   
546.   
547.  --(二十七)查询各科成绩前三名的记录(不考虑成绩并列情况)  
548.  --rank() over(Partition .. order by ...) 是按照某个字段的值进行分组并编号  
549. select t1.cno, t1.sno, t1.grade, r  
550.   from (select sc.sno,  
551.                sc.cno,  
552.                sc.grade,  
553.                rank() over(partition by sc.cno order by grade desc) r  
554.           from t_score sc) t1  
555.  where r < 4  
556.  order by t1.cno, t1.sno, r;  
557.