关系模式描述：红色为主键
S (SNO,SNAME） 学生关系。SNO 为学号，SNAME 为姓名
C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师
SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩
C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师
SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩
建表语句：
|
create table S
(
SNO VARCHAR2(3),
SNAME VARCHAR2(10)
);
insert into S (SNO, SNAME) values ('1', '赵一');
insert into S (SNO, SNAME) values ('2', '钱二');
insert into S (SNO, SNAME) values ('3', '孙三');
insert into S (SNO, SNAME) values ('4', '李四');
insert into S (SNO, SNAME) values ('5', '王五');
insert into S (SNO, SNAME) values ('6', '张六');
|
|
create table C
(
CNO VARCHAR2(3),
CNAME VARCHAR2(30),
CTEACHER VARCHAR2(10)
);
insert into C (CNO, CNAME, CTEACHER) values ('1', '语文', '语文老师');
insert into C (CNO, CNAME, CTEACHER) values ('2', '数学', '数学老师');
insert into C (CNO, CNAME, CTEACHER) values ('3', '英语', '英语老师');
insert into C (CNO, CNAME, CTEACHER) values ('4', '历史', '历史老师');
insert into C (CNO, CNAME, CTEACHER) values ('5', '地理', '地理老师');
insert into C (CNO, CNAME, CTEACHER) values ('6', '生物', '生物老师');
|
|
create table SC
(
SNO VARCHAR2(3),
CNO VARCHAR2(3),
SCGRADE NUMBER
);
insert into SC (SNO, CNO, SCGRADE) values ('1', '1', 11);
insert into SC (SNO, CNO, SCGRADE) values ('1', '2', 12);
insert into SC (SNO, CNO, SCGRADE) values ('1', '3', 13);
insert into SC (SNO, CNO, SCGRADE) values ('1', '4', 14);
insert into SC (SNO, CNO, SCGRADE) values ('1', '5', 15);
insert into SC (SNO, CNO, SCGRADE) values ('1', '6', 16);
insert into SC (SNO, CNO, SCGRADE) values ('2', '1', 21);
insert into SC (SNO, CNO, SCGRADE) values ('2', '2', 22);
insert into SC (SNO, CNO, SCGRADE) values ('2', '3', 23);
insert into SC (SNO, CNO, SCGRADE) values ('3', '4', 34);
insert into SC (SNO, CNO, SCGRADE) values ('3', '5', 35);
insert into SC (SNO, CNO, SCGRADE) values ('3', '6', 36);
insert into SC (SNO, CNO, SCGRADE) values ('4', '1', 41);
insert into SC (SNO, CNO, SCGRADE) values ('4', '2', 42);
insert into SC (SNO, CNO, SCGRADE) values ('5', '3', 53);
insert into SC (SNO, CNO, SCGRADE) values ('5', '4', 54);
|
1. 找出没有选修过“语文老师”老师讲授课程的所有学生姓名
|
正确写法一：
SELECT S.SNAME FROM S WHERE NOT EXISTS (SELECT * FROM SC,C WHERE SC.CNO=C.CNO AND SC.SNO=S.SNO AND C.CTEACHER='语文老师') |
|
正确写法二：
SELECT S.SNAME FROM S WHERE SNO NOT IN (SELECT SNO FROM SC,C WHERE SC.CNO=C.CNO AND C.CTEACHER='语文老师') 不推荐，如果子查询中有null值，结果将是错误的。
|
|
错误写法一：
SELECT S.SNO,S.SNAME FROM S,C,SC WHERE SC.CNO=C.CNO AND SC.SNO=S.SNO AND C.CTEACHER<>'语文老师' ORDER BY S.SNO 错误分析：这条语句是错误的，错在将sc表与c表做连接，这样做的结果就是会将sc表的所有记录都取出，即使这个学生选了所有的课程，仍然会被选出，因为数学老师也不是语文老师
|
|
错误写法二：
SELECT S.SNAME FROM S WHERE SNO IN (SELECT SNO FROM SC,C WHERE SC.CNO=C.CNO AND C.CTEACHER<>'语文老师') 错误分析同上
|
2. 列出有二门以上（含两门）课程低于40分的学生姓名及其平均成绩
|
正确写法一：
SELECT S.SNAME,A.SCORE FROM (SELECT SNO,AVG(SC.SCGRADE) AS SCORE FROM SC WHERE SCGRADE<40 GROUP BY SNO HAVING COUNT(*)>=2)A,S WHERE A.SNO=S.SNO 使用子查询
|
|
正确写法二：
SELECT S.SNAME,AVG(SC.SCGRADE) FROM SC, S WHERE SC.SCGRADE<40 AND S.SNO = SC.SNO GROUP BY S.SNAME HAVING COUNT(*)>=2 不使用子查询的写法
|
|
正确写法三：
SELECT S.SNO,S.SNAME,AVG(SC.SCGRADE) FROM S,SC,( SELECT SNO FROM SC WHERE SCGRADE<40 GROUP BY SNO HAVING COUNT(DISTINCT CNO)>=2 )A WHERE S.SNO=A.SNO AND SC.SNO=A.SNO GROUP BY S.SNO,S.SNAME 个人不是很推荐这种写法，觉得效率低了点，但是网上很多答案都是这个，关于效率下次讨论
|
3. 列出既学过语文课，又学过数学课的所有学生姓名
|
正确写法一：
SELECT SNAME FROM (SELECT SC.SNO FROM C,SC WHERE SC.CNO=C.CNO AND C.CNAME IN('语文’,’数学’) GROUP BY SC.SNO HAVING COUNT(*)=2)A,S WHERE A.SNO=S.SNO 使用子查询
|
|
正确写法二：
SELECT S.SNAME FROM C,SC, S WHERE SC.CNO=C.CNO AND SC.SNO = S.SNO AND C.CNAME IN('语文’,’数学') GROUP BY S.SNAME HAVING COUNT( * ) =2 不使用子查询
|
4. 列出1号课比2号课成绩低的所有学生的姓名及1号课和2号课的成绩
|
正确写法一：
SELECT SNAME,T.SCORE1,T.SCORE2 FROM (SELECT A.SNO,A.SCGRADE AS SCORE1,B.SCGRADE AS SCORE2 FROM SC A,SC B WHERE A.SNO=B.SNO AND A.CNO='1' AND B.CNO='2' AND A.SCGRADE<B.SCGRADE)T,S WHERE T.SNO=S.SNO 使用子查询
|
|
正确写法二：
SELECT S.SNAME,A.SCGRADE,B.SCGRADE FROM S,SC A,SC B WHERE S.SNO=A.SNO AND S.SNO=B.SNO AND A.CNO='1' AND B.CNO='2' AND A.SCGRADE<B.SCGRADE 不使用子查询
|
5. 列出选修了所有课程的学生的姓名
|
正确写法一：
SELECT S.SNAME FROM SC,S WHERE S.SNO=SC.SNO GROUP BY S.SNAME HAVING COUNT(*)=(SELECT COUNT(*) FROM C) |