常见经典关系模式及查询之学生选课模式

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);

正确写法一：
SELECTS.SNAME
FROM S
WHERE NOT EXISTS
     (SELECT * FROM SC,C
      WHERE SC.CNO=C.CNO
      AND SC.SNO=S.SNO
      AND C.CTEACHER='语文老师')

正确写法二：
SELECTS.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表的所有记录都取出，即使这个学生选了所有的课程，仍然会被选出，因为数学老师也不是语文老师

错误写法二：
SELECTS.SNAME
FROM S
WHERE SNO IN
     (SELECT SNO FROM SC,C
      WHERE SC.CNO=C.CNO
      AND C.CTEACHER<>'语文老师')

错误分析同上

正确写法一：
SELECTS.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

使用子查询

正确写法二：
SELECTS.SNAME,AVG(SC.SCGRADE) FROM SC, S
WHERE SC.SCGRADE<40
AND S.SNO = SC.SNO
GROUP BY S.SNAME
HAVING COUNT(*)>=2

不使用子查询的写法

正确写法三：
SELECTS.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

个人不是很推荐这种写法，觉得效率低了点，但是网上很多答案都是这个，关于效率下次讨论

正确写法一：
SELECTSNAME
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

使用子查询

正确写法二：
SELECTS.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

不使用子查询

正确写法一：
SELECTSNAME,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

使用子查询

正确写法二：
SELECTS.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

不使用子查询

正确写法一：
SELECTS.SNAME
FROM SC,S
WHERE S.SNO=SC.SNO
GROUP BY S.SNAME
HAVING COUNT(*)=(SELECT COUNT(*) FROM C)