数据库课设 实验报告

6 WHERE Sno=S040930504.Sno AND EXISTS 7 (SELECT *

8 FROM C040930504

9 WHERE Cname='数据结构'));

SNO SNAME

--------- ---------------------------------------- 200215122 刘晨 200215121 李勇

3．查询不选1号课程的学生学号与姓名。 SQL> SELECT Sno,Sname 2 FROM S040930504 3 WHERE NOT EXISTS 4 (SELECT *

5 FROM SC040930504

6 WHERE Sno=S040930504.Sno AND Cno='1');

SNO SNAME

--------- ---------------------------------------- 200215122 刘晨 20204 王敏 30203 张立

4．查询学习全部课程学生姓名。 SQL> SELECT Sname 2 FROM S040930504 3 WHERE NOT EXISTS 4 (SELECT *

5 FROM C040930504 6 WHERE NOT EXISTS 7 (SELECT *

8 FROM SC040930504

9 WHERE Sno=S040930504.Sno AND Cno=C040930504.Cno));

SNAME

---------------------------------------- 李勇

5．查询所有学生除了选修1号课程外所有成绩均及格的学生的学号和平均成绩，其结果按平均成绩的降序排列。

create view dot (Sno,Cno,Grade) AS

select S040930504.Sno,Cno,Grade from S040930504,SC040930504

where S040930504.Sno=SC040930504.Sno and Cno!=1;

select Sno,avg(Grade) average from dot where 60<=

(select min(Grade) from dot xdot

where dot.Sno=xdot.Sno )

group by Sno

order by average desc;

6．查询选修数据库原理成绩第2名的学生姓名。

select Sname,Sno from S040930504 where Sno in

(select Sno from SC040930504 where Cno in

(select Cno from C040930504 where Cname='数据库') and Grade in (select max(Grade) from SC040930504 where Grade not in (select max(Grade) from SC040930504 where Cno in

(select Cno from C040930504 where Cname='数据库')) and Cno in (select Cno from C040930504 where Cname='数据库')));

7. 查询所有3个学分课程中有3门以上（含3门）课程获80分以上（含80分）的学生的姓名。

create view xdot (Sno,Cno,Grade) AS

select Sno,SC040930504.Cno,Grade from SC040930504,C040930504

where Grade>=80 and ccredit=2 and SC040930504.Cno=C040930504.Cno

select Sname from S040930504 where Sno in (select Sno from xdot group by Sno

having count(*)>=3);

8. 查询选课门数唯一的学生的学号。 create view dot (Sno,Ccount)

AS

select Sno,count(*) from SC040930504 group by Sno;

select Sno

from SC040930504 group by Sno

having count(*)<>all (select Ccount from dot

where dot.Sno!=SC040930504.Sno);

实验三：数据修改、删除

1．把1号课程的非空成绩提高10％。 SQL> UPDATE SC040930504 2 SET Grade=Grade*(1+0.1)

3 WHERE GRADE IS NOT NULL AND GRADE<=100 AND CNO='1';

已更新 1 行。

SQL> SELECT * FROM SC040930504;

SNO CNO GRADE --------- ---- ----------

200215121 1 101 200215121 2 85 200215121 3 88 200215122 2 90 200215122 3 80

2．在SC表中删除课程名为数据结构的成绩的元组。 SQL> DELETE

2 FROM SC040930504 3 WHERE CNO IN 4 (SELECT CNO

5 FROM C040930504

6 WHERE C040930504.CNO=SC040930504.CNO AND CNAME='数据库');

已删除 1 行。

SQL> SELECT * FROM SC040930504;

SNO CNO GRADE --------- ---- ----------

200215121 2 85 200215121 3 88 200215122 2 90 200215122 3 80

3．在S和SC表中删除学号为95002的所有数据。 SQL> DELETE

2 FROM S040930504

3 WHERE SNO ='200215122';

已删除 1 行。

SQL> DELETE

2 FROM SC040930504

3 WHERE SNO ='200215122';

已删除2行。

SQL> SELECT * FROM S040930504;

SNO SNAME --------- ---------------------------------------- -- ---------- SDEPT

--------------------

200215121 李勇 CS

20204 王敏 MA

30203 张立 IS

SQL> SELECT * FROM S040930504;

SNO SNAME --------- ---------------------------------------- -- ---------- SDEPT

--------------------

200215121 李勇 CS

20204 王敏 MA

30203 张立 IS

SS SAGE 男 20 女 18 男 19 SS SAGE 男 20 女 18 男 19