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

最新推荐文章于 2022-06-03 21:31:09 发布

slamdunkning1983

最新推荐文章于 2022-06-03 21:31:09 发布

阅读量3.6k

点赞数

分类专栏： Oracle 文章标签： insert table c 生物 null

本文链接：https://blog.youkuaiyun.com/slamdunkning1983/article/details/2071879

版权

Oracle 专栏收录该内容

3 篇文章

订阅专栏

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

关系模式描述：红色为主键

S (SNO,SNAME）          学生关系。SNO 为学号，SNAME 为姓名
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