/*create table Z_COURSE
(
id NUMBER not null,
cour_code VARCHAR2(20),
cour_name VARCHAR2(20),
p_cour_code VARCHAR2(20)
);
comment on column Z_COURSE.cour_code
is '课程代码';
comment on column Z_COURSE.cour_name
is '课程名称';
comment on column Z_COURSE.p_cour_code
is '父级课程代码';
alter table Z_COURSE
add constraint PK_Z_COURSE primary key (ID);
alter table Z_COURSE
add constraint UK_Z_COURSE unique (COUR_CODE);
create table Z_STUDENT
(
id NUMBER not null,
name VARCHAR2(20),
code VARCHAR2(20),
sex CHAR(1),
birthday DATE,
major VARCHAR2(20),
note VARCHAR2(300)
);
comment on column Z_STUDENT.name
is '学生姓名';
comment on column Z_STUDENT.code
is '学生学号';
comment on column Z_STUDENT.sex
is '性别';
comment on column Z_STUDENT.birthday
is '生日';
comment on column Z_STUDENT.major
is '专业';
comment on column Z_STUDENT.note
is '备注';
alter table Z_STUDENT
add constraint PK_Z_STUDENT primary key (ID);
create table Z_STU_COUR
(
id NUMBER not null,
stu_code VARCHAR2(20),
cour_code VARCHAR2(20),
agree NUMBER(4,1)
);
comment on column Z_STU_COUR.stu_code
is '学生学号';
comment on column Z_STU_COUR.cour_code
is '课程代码';
comment on column Z_STU_COUR.agree
is '课程分数';
alter table Z_STU_COUR
add constraint PK_Z_STU_COURT primary key (ID);
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (1, 'LAU-100', '汉语言文学专业', null);
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (2, 'C-LAU-101', '语言学概论', 'LAU-001');
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (3, 'C-LAU-102', '现代汉语', 'LAU-001');
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (4, 'C-LAU-103', '中国当代文学史', 'LAU-001');
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (5, 'C-LAU-104', '大学英语', 'LAU-001');
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (6, 'NEWS-100', '国际新闻专业', null);
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (7, 'C-NEWS-101', '新闻采访', 'NEWS-100');
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (8, 'C-NEWS-102', '报纸编辑', 'NEWS-100');
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (9, 'C-NEWS-103', '电视新闻', 'NEWS-100');
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (10, 'HIS-121', '历史学专业', null);
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (11, 'C-HIS-335', '中国古代史', 'HIS-121');
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (12, 'C-HIS-336', '世界古代史', 'HIS-121');
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (13, 'C-HIS-337', '中国近代史', 'HIS-121');
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (14, 'ADV-609', '广告学专业', null);
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (15, 'C-ADV-239', '广告文案写作', 'ADV-609');
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (16, 'C-ADV-240', '基础美术', 'ADV-609');
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (17, 'C-ADV-241', '平面广告设计与制作', 'ADV-609');
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (18, 'C-ADV-242', '市场营销学', 'ADV-609');
insert into Z_COURSE (id, cour_code, cour_name, p_cour_code)
values (19, 'C-ADV-243', '大众传播学', 'ADV-609');
commit;
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (1, '陈迪', 'stu-1011', '1', to_date('14-04-1993', 'dd-mm-yyyy'), 'LAU-100', '1');
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (2, '肖东菁', 'stu-1014', '1', to_date('15-02-1992', 'dd-mm-yyyy'), 'HIS-121', '1');
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (3, '汪佳丽', 'stu-1017', '2', to_date('16-08-1990', 'dd-mm-yyyy'), 'NEWS-100', '1');
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (19, '车晓', 'stu-1042', '1', to_date('18-03-1990', 'dd-mm-yyyy'), 'ADV-609', '1');
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (5, '王聪', 'stu-1023', '1', to_date('18-03-1990', 'dd-mm-yyyy'), 'ADV-609', '1');
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (6, '李璇', 'stu-1026', '2', to_date('19-05-1991', 'dd-mm-yyyy'), 'HIS-121', '1');
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (7, '马舒滟', 'stu-1029', '1', to_date('20-01-1990', 'dd-mm-yyyy'), 'NEWS-100', '1');
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (20, '张光北', 'stu-1018', '1', to_date('15-02-1992', 'dd-mm-yyyy'), 'HIS-121', '1');
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (9, '徐丹', 'stu-1035', '2', null, 'NEWS-100', '1');
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (11, '田野', 'stu-1041', '1', null, 'ADV-609', '1');
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (12, '彭亚光', 'stu-1044', '2', to_date('25-11-1990', 'dd-mm-yyyy'), 'HIS-121', '1');
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (14, '黄欢', 'stu-1050', '1', to_date('27-06-1990', 'dd-mm-yyyy'), 'ADV-609', '1');
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (15, '庞琳', 'stu-1053', '1', to_date('28-05-1989', 'dd-mm-yyyy'), 'HIS-121', '1');
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (16, '张子腾', 'stu-1056', '2', to_date('18-03-1990', 'dd-mm-yyyy'), 'LAU-100', '1');
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (17, '姜春阳', 'stu-1059', '2', to_date('30-05-1988', 'dd-mm-yyyy'), 'HIS-121', '1');
insert into Z_STUDENT (id, name, code, sex, birthday, major, note)
values (18, '陈冰若', 'stu-1062', '1', to_date('31-10-1990', 'dd-mm-yyyy'), 'NEWS-100', '1');
commit;
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (1, 'stu-1011', 'C-LAU-101', 35);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (2, 'stu-1011', 'C-LAU-102', 65);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (3, 'stu-1011', 'C-LAU-103', 25);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (4, 'stu-1011', 'C-LAU-104', 97);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (5, 'stu-1014', 'C-HIS-335', 53);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (6, 'stu-1014', 'C-HIS-336', 35);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (7, 'stu-1014', 'C-HIS-337', 65);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (8, 'stu-1017', 'C-NEWS-101', 25);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (9, 'stu-1017', 'C-NEWS-102', 65);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (10, 'stu-1017', 'C-NEWS-103', 25);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (11, 'stu-1023', 'C-ADV-239', 33);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (12, 'stu-1023', 'C-ADV-240', 42);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (13, 'stu-1023', 'C-ADV-241', 66);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (14, 'stu-1023', 'C-ADV-242', 69);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (15, 'stu-1023', 'C-ADV-243', 82);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (16, 'stu-1026', 'C-HIS-335', 37);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (17, 'stu-1026', 'C-HIS-336', 77);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (18, 'stu-1026', 'C-HIS-337', 34);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (19, 'stu-1029', 'C-NEWS-101', 35);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (20, 'stu-1029', 'C-NEWS-102', 75);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (21, 'stu-1029', 'C-NEWS-103', 32);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (22, 'stu-1035', 'C-NEWS-101', 19);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (23, 'stu-1035', 'C-NEWS-102', 11);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (24, 'stu-1035', 'C-NEWS-103', 93);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (25, 'stu-1041', 'C-ADV-239', 99);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (26, 'stu-1041', 'C-ADV-240', 88);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (27, 'stu-1041', 'C-ADV-241', 89);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (28, 'stu-1041', 'C-ADV-242', 63);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (29, 'stu-1041', 'C-ADV-243', 44);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (30, 'stu-1044', 'C-HIS-335', 73);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (31, 'stu-1044', 'C-HIS-336', 65);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (32, 'stu-1044', 'C-HIS-337', 25);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (33, 'stu-1050', 'C-ADV-239', 33);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (34, 'stu-1050', 'C-ADV-240', 42);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (35, 'stu-1050', 'C-ADV-241', 25);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (36, 'stu-1050', 'C-ADV-242', 33);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (37, 'stu-1050', 'C-ADV-243', 42);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (38, 'stu-1053', 'C-HIS-335', 66);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (39, 'stu-1053', 'C-HIS-336', 69);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (40, 'stu-1053', 'C-HIS-337', 35);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (41, 'stu-1056', 'C-LAU-101', 65);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (42, 'stu-1056', 'C-LAU-102', 25);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (43, 'stu-1056', 'C-LAU-103', 97);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (44, 'stu-1056', 'C-LAU-104', 53);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (45, 'stu-1059', 'C-HIS-335', 35);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (46, 'stu-1059', 'C-HIS-336', 25);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (47, 'stu-1059', 'C-HIS-337', 97);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (48, 'stu-1062', 'C-NEWS-101', 32);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (49, 'stu-1062', 'C-NEWS-102', 19);
insert into Z_STU_COUR (id, stu_code, cour_code, agree)
values (50, 'stu-1062', 'C-NEWS-103', 11);
commit;*/
题目及答案：
--(一)简单查询
--查询学生表中的所有内容
select * from z_student;
--查询学生表中的姓名，专业
select s.name,s.major from z_student s;
--查询学生表中各种专业
SELECT DISTINCT (SELECT c.cour_name FROM z_course c WHERE c.cour_code=s.major) FROM z_student s
--查询表中前五个同学的姓名，专业
SELECT s.* FROM (SELECT * FROM z_student ORDER BY ID) s WHERE ROWNUM < 6
--查询各学生的学号和姓名以及截止到现在各学生的年龄
SELECT s.code, s.name, to_char(SYSDATE, 'yyyy')-to_char(s.birthday,'yyyy') 年龄 FROM z_student s
--查询专业为国际新闻的学生的所有信息
SELECT *
FROM Z_STUDENT S
WHERE S.MAJOR = (SELECT C.COUR_CODE
FROM Z_COURSE C
WHERE C.COUR_NAME LIKE '%国际新闻%')
SELECT s.*
FROM Z_STUDENT S, Z_COURSE C
WHERE S.MAJOR = C.COUR_CODE
AND C.COUR_NAME LIKE '%国际新闻%'
--查询1991年出生的学生姓名和专业
SELECT * FROM z_student s WHERE to_char(s.birthday, 'yyyy')='1991'
--查询历史, 广告, 国际新闻专业的所有学生信息
SELECT *
FROM Z_STUDENT S
WHERE S.MAJOR IN (SELECT C.COUR_CODE
FROM Z_COURSE C
WHERE C.COUR_NAME LIKE '%历史%'
OR C.COUR_NAME LIKE '%广告%'
OR C.COUR_NAME LIKE '%国际新闻%')
--查询姓名是两个字的姓王, 姓陈, 姓李的所有学生信息
SELECT *
FROM Z_STUDENT S
WHERE S.NAME LIKE '王_'
OR S.NAME LIKE '陈_'
OR S.NAME LIKE '李_'
--查询没有学分的学生信息
SELECT *
FROM Z_STUDENT S LEFT JOIN Z_STU_COUR SC
ON S.CODE = SC.STU_CODE
WHERE SC.DEGREE IS /*NOT*/ NULL
SELECT *
FROM Z_STUDENT S
WHERE S.CODE NOT IN (SELECT SC.STU_CODE FROM Z_STU_COUR SC)
--查询国际新闻专业的没有记录生日的学生信息
SELECT *
FROM Z_STUDENT S
WHERE S.MAJOR = (SELECT C.COUR_CODE
FROM Z_COURSE C
WHERE C.COUR_NAME LIKE '%国际新闻%')
AND S.BIRTHDAY IS NULL
--查询按照专业降序，学号升序排列所有学生信息
SELECT * FROM z_student s ORDER BY s.major DESC, s.code
--查询表中前三个的学生的姓名，专业，到现在的年龄并按照年龄降序排列
SELECT S.NAME,
S.MAJOR,
(TO_CHAR(SYSDATE, 'yyyy') - TO_CHAR(S.BIRTHDAY, 'yyyy')) 年龄
FROM Z_STUDENT S
WHERE ROWNUM < 4 ORDER BY 3 DESC
--（二）数据汇总
--******查询所有的课程代码和每个课程的平均成绩并按照课程号排序再剔除课程代码不是C-ADV-240的课程
SELECT SC.COUR_CODE, AVG(SC.DEGREE)
FROM Z_STU_COUR SC
GROUP BY SC.COUR_CODE
HAVING SC.COUR_CODE != 'C-ADV-240'
ORDER BY SC.COUR_CODE
SELECT SC.COUR_CODE, AVG(SC.DEGREE)
FROM Z_STU_COUR SC
WHERE SC.COUR_CODE != 'C-ADV-240'
GROUP BY SC.COUR_CODE
ORDER BY SC.COUR_CODE
--查询出每个课程代码的最高分和最低分
SELECT SC.COUR_CODE, MAX(SC.DEGREE), MIN(SC.DEGREE)
FROM Z_STU_COUR SC
GROUP BY SC.COUR_CODE
--查询学号为stu-1023的学生的各课成绩
SELECT sc.cour_code, sc.degree FROM z_stu_cour sc WHERE sc.stu_code='stu-1023'
--查询出历史学专业有多少人
SELECT COUNT(*)
FROM Z_STUDENT S
WHERE S.MAJOR =
(SELECT C.COUR_CODE FROM Z_COURSE C WHERE C.COUR_NAME LIKE '%历史%')
--查询各专业各有多少人
SELECT s.major, COUNT(*)
FROM Z_STUDENT S GROUP BY s.major
--查询出各专业里男女生各有多少人
SELECT S.MAJOR, S.SEX, COUNT(1) FROM Z_STUDENT S GROUP BY S.MAJOR, S.SEX
SELECT S.MAJOR,
SUM(DECODE(S.SEX, 1, 1, 2, 0)) 男,
SUM(DECODE(S.SEX, 1, 0, 2, 1)) 女
FROM Z_STUDENT S
GROUP BY S.MAJOR
--查询出学生所有课程的平均分在50分以上的学生学号
SELECT SC.STU_CODE
FROM Z_STU_COUR SC
GROUP BY SC.STU_CODE
HAVING AVG(SC.DEGREE) > 50
--查询每个学生有几门课成绩高于80分
SELECT SC.STU_CODE, COUNT(SC.DEGREE)
FROM Z_STU_COUR SC
WHERE SC.DEGREE > 80
GROUP BY SC.STU_CODE
SELECT SC.STU_CODE, SUM(CASE WHEN sc.degree>80 THEN 1 ELSE 0 END)
FROM Z_STU_COUR SC GROUP BY SC.STU_CODE
--（三）连接查询
--查询所有学生的学号，姓名，专业课程号，成绩
SELECT S.CODE, S.NAME, SC.COUR_CODE, SC.DEGREE
FROM Z_STUDENT S
LEFT JOIN Z_STU_COUR SC
ON S.CODE = SC.STU_CODE
--查询课程号为C-HIS-336的学生的姓名和成绩
--查询选修基础美术这门课程的学生学号，姓名，成绩
SELECT S.CODE, S.NAME, SC.COUR_CODE, SC.DEGREE
FROM Z_STUDENT S, Z_STU_COUR SC
WHERE S.CODE = SC.STU_CODE
AND S.MAJOR = (SELECT C.P_COUR_CODE
FROM Z_COURSE C
WHERE C.COUR_NAME LIKE '%基础美术%')
--查询选修课程号为C-NEWS-101这门课的所有学生信息和成绩
SELECT * FROM z_student s, z_stu_cour sc WHERE s.code=sc.stu_code AND sc.cour_code='C-NEWS-101'
--查询生日是同一天的学生信息
SELECT *
FROM Z_STUDENT T
WHERE T.NAME IN (SELECT DISTINCT (T.NAME)
FROM Z_STUDENT T, Z_STUDENT S
WHERE T.BIRTHDAY = S.BIRTHDAY
AND T.CODE != S.CODE)
ORDER BY T.BIRTHDAY
--查询各课的课程名，课程号，每门课所有学生的平均成绩
SELECT (SELECT C.COUR_NAME FROM Z_COURSE C WHERE C.COUR_CODE = SC.COUR_CODE) 课程名,
SC.COUR_CODE,
AVG(SC.DEGREE)
FROM Z_STU_COUR SC
GROUP BY SC.COUR_CODE
SELECT R.COUR_CODE, C.COUR_NAME, R.AVG
FROM (SELECT SC.COUR_CODE COUR_CODE, AVG(SC.degree) AVG
FROM Z_STU_COUR SC
GROUP BY SC.COUR_CODE) R
LEFT OUTER JOIN Z_COURSE C
ON R.COUR_CODE = C.COUR_CODE
ORDER BY R.COUR_CODE
select sc.cour_code, c.cour_name, avg(sc.degree)
from z_stu_cour sc, z_course c
where sc.cour_code = c.cour_code
group by sc.cour_code, c.cour_name
SELECT SC.COUR_CODE, C.COUR_NAME, AVG(SC.degree)
FROM Z_STU_COUR SC
LEFT JOIN Z_COURSE C
ON SC.COUR_CODE = C.COUR_CODE
GROUP BY SC.COUR_CODE, C.COUR_NAME;
--查询所有学生的平均成绩
SELECT S.CODE, S.NAME, AVG(nvl(SC.DEGREE, 0))
FROM Z_STUDENT S
LEFT JOIN Z_STU_COUR SC
ON S.CODE = SC.STU_CODE
GROUP BY S.CODE, S.NAME
--查询每个专业的每个课程的平均分是多少
SELECT AVG(sc.degree) FROM z_stu_cour sc GROUP BY sc.cour_code
--查询平均分大于40分的国际新闻专业的每个学生姓名，学号和各课的平均分
SELECT r.name, r.code, sc.cour_code, AVG(sc.degree)
FROM Z_STU_COUR SC
RIGHT JOIN (SELECT S.*
FROM Z_STUDENT S
WHERE S.MAJOR = (SELECT C.COUR_CODE
FROM Z_COURSE C
WHERE C.COUR_NAME LIKE '%国际新闻%')) R
ON SC.STU_CODE = R.CODE GROUP BY r.name, r.code, sc.cour_code HAVING AVG(sc.degree) > 40;
SELECT R.NAME, R.CODE, T.AVGDEGREE
FROM (SELECT *
FROM Z_STUDENT S
WHERE S.MAJOR = (SELECT C.COUR_CODE
FROM Z_COURSE C
WHERE C.COUR_NAME LIKE '%国际新闻%专业')) R
LEFT OUTER JOIN (SELECT SC.STU_CODE CODE, AVG(SC.degree) AVGDEGREE
FROM Z_STU_COUR SC WHERE sc.degree > 40
GROUP BY SC.STU_CODE) T
ON R.CODE = T.CODE
ORDER BY R.CODE
SELECT S.NAME, SC.STU_CODE, SC.COUR_CODE, AVG(SC.degree)
FROM Z_STUDENT S, Z_COURSE C, Z_STU_COUR SC
WHERE SC.STU_CODE IN (SELECT SC.STU_CODE
FROM Z_STU_COUR SC
GROUP BY SC.STU_CODE
HAVING AVG(SC.degree) > 40)
AND S.CODE = SC.STU_CODE
AND S.MAJOR = C.COUR_CODE
AND C.COUR_NAME = '国际新闻专业'
GROUP BY S.NAME, SC.STU_CODE, SC.COUR_CODE
--（四）子查询的使用
--查询平均分大于等于课程号为C-ADV-239的课程号和平均分
SELECT sc1.cour_code, AVG(sc1.degree)
FROM Z_STU_COUR SC1
GROUP BY SC1.COUR_CODE
HAVING AVG(SC1.DEGREE) >= (SELECT AVG(SC.DEGREE)
FROM Z_STU_COUR SC
WHERE SC.COUR_CODE = 'C-ADV-239'
GROUP BY SC.COUR_CODE)
--查询历史学专业下的课程的及格率(课程得分在50以上的人数除以总人数)
SELECT SC.COUR_CODE,
SUM(CASE
WHEN SC.degree > 50 THEN
1
ELSE
0
END) / COUNT(*) 及格率
FROM Z_STU_COUR SC
WHERE SC.COUR_CODE IN
(SELECT C.COUR_CODE
FROM Z_COURSE C
CONNECT BY PRIOR C.COUR_CODE = C.P_COUR_CODE
START WITH C.COUR_NAME LIKE '%历史%')
GROUP BY SC.COUR_CODE
SELECT SCC.COUR_CODE, SCCC.COUU / SCC.COU * 100
FROM (SELECT SC.COUR_CODE, COUNT(SC.COUR_CODE) COUU
FROM Z_STU_COUR SC
WHERE SC.DEGREE > 50
GROUP BY SC.COUR_CODE) SCCC,
(SELECT SC.COUR_CODE, COUNT(SC.COUR_CODE) COU
FROM Z_STU_COUR SC
GROUP BY SC.COUR_CODE) SCC
WHERE SCCC.COUR_CODE = SCC.COUR_CODE
AND SCC.COUR_CODE IN
(SELECT C.COUR_CODE
FROM Z_COURSE C
WHERE C.P_COUR_CODE =
(SELECT C.COUR_CODE
FROM Z_COURSE C
WHERE C.COUR_NAME = '历史学专业'))
SELECT ZP.COUR_NAME, 及格人数 / 总人数 及格率
FROM (SELECT C.COUR_NAME, COUNT(SC.DEGREE) 总人数
FROM Z_COURSE C
LEFT JOIN Z_STU_COUR SC
ON C.COUR_CODE = SC.COUR_CODE
WHERE C.P_COUR_CODE =
(SELECT C.COUR_CODE
FROM Z_COURSE C
WHERE C.COUR_NAME = '历史学专业')
GROUP BY C.COUR_NAME, SC.COUR_CODE) ZP
LEFT JOIN (SELECT C.COUR_NAME, COUNT(SC.DEGREE) 及格人数
FROM Z_COURSE C
LEFT JOIN Z_STU_COUR SC
ON C.COUR_CODE = SC.COUR_CODE
WHERE C.P_COUR_CODE =
(SELECT C.COUR_CODE
FROM Z_COURSE C
WHERE C.COUR_NAME = '历史学专业')
AND SC.DEGREE > 50
GROUP BY C.COUR_NAME, SC.COUR_CODE) JP
ON ZP.COUR_NAME = JP.COUR_NAME
--查询没有选修C-NEWS-101这门课程的学生信息和课程信息
SELECT S.*, SC.COUR_CODE
FROM Z_STUDENT S, Z_STU_COUR SC
WHERE S.CODE = SC.STU_CODE
AND SC.COUR_CODE != 'C-NEWS-101'