数据库系统原理作业（实验五、六）

实验内容：

---

实验五

验证习题三第13、14题中的各项操作的SQL语句。

---

习题三 第13题：

• 找出与李勇在同一个班级的学生信息；
• 找出与李勇有相同选修课程的学生信息；
• 找出年龄介于学生李勇和25岁之间的学生信息（已知李勇年龄小于25岁）；
• 找出选修了课程操作系统的学生学号和姓名；
• 找出所有没有选修1号课程的学生姓名；
• 找出选修了全部课程的学生姓名。

---

习题三第14题：

• 查询选修了3号课程的学生学号及其成绩，并按成绩的降序排列；
• 查询全体学生信息，要求查询结果按班级号升序排列，同一班级学生按年龄降序排列；
• 求每个课程号及相应的选课人数；
• 查询选修了3门以上课程的学生学号。

---

实验六

验证习题三第15题中的各项操作的SQL语句；

---

• 将01311班的全体学生的成绩置零；
• 删除01311班全体学生的选课记录；
• 学生李勇已退学，从数据库中删除有关他的记录；
• 对每个班，求学生的平均年龄，并把结果存入数据库。
• 把所有工程师的基本工资（Basepay）增加100，试一试以下的UPDATE语句对不对：

UPDATE salary
SET basepay=basepay+100
WHERE eno in 
	(SELECT eno
	 FROM employee
WHERE title=’工程师’

---

代码如下

很可能有错的，还请多多包涵b（￣▽￣）d

实验五

USE grademanager

--1.找出和李勇在同一个班级的学生信息
SELECT * FROM Student 
WHERE Sname <> '李勇' and Clno in
(SELECT Clno FROM Student 
WHERE Sname = '李勇');

--2.找出所有与学生李勇有相同选修课程的学生信息
SELECT * FROM Student
WHERE Sname <> '李勇' and exists
(SELECT * FROM Grade 
WHERE Student.Sno = Grade.Sno and Grade.Cno in
(SELECT Cno FROM Grade
WHERE Sno in
(SELECT Sno FROM Student
WHERE Sname = '李勇')));

--3.找出年龄介于学生李勇和25岁之间的学生信息
SELECT * FROM Student
WHERE Student.Sage < 25 and Student.Sage >
(SELECT Sage FROM Student
WHERE Sname = '李勇');

--4.找出选修了课程是操作系统的学生学号和姓名
SELECT Sno,Sname FROM Student
WHERE Sno in  
(SELECT Sno FROM Grade
WHERE Cno in
(SELECT Cno FROM Course
WHERE Cname = '操作系统'));

--5.找出没有选修1号课程的所有学生姓名
SELECT Sname FROM Student
WHERE not exists
(SELECT * FROM Grade
WHERE Student.Sno = Grade.Sno and Cno = 1);

--SELECT * FROM Grade,Student
--WHERE Student.Sno = Grade.Sno and Cno = 1

--6.找出选修了全部课程的学生姓名
SELECT Sname FROM Student
WHERE not exists
(SELECT Cno FROM Course 
EXCEPT SELECT Cno FROM Grade
WHERE Student.Sno = Grade.Sno);


/*SELECT Cno FROM Course except
SELECT Cno FROM Grade,Student
WHERE Student.Sno = Grade.Sno*/

--1.查询选修了3号课程的学生学号及成绩，并按成绩的降序排列
SELECT Sno,Gmark FROM Grade
WHERE Cno = 3
ORDER BY Gmark DESC

--2.查询全体学生信息，要求查询结果按班级号升序排列，
--同一班级学生按年龄降序排列；
SELECT * FROM Student 
ORDER BY Clno ASC, Sage DESC

--3.求每个课程号及相应的选课人数
SELECT Grade.Cno AS '课程号',COUNT(Grade.Cno) AS '选课人数'
FROM Grade FULL outer join Course 
ON Grade.Cno = Course.Cno
GROUP BY Grade.Cno 
--课程号7显示为null
/*SELECT Cno AS '课程号',COUNT(Cno) AS '选课人数'
FROM Grade GROUP BY Cno*/
--

--4.查询选修了3门以上课程的学生学号
SELECT Sno FROM Grade 
GROUP BY Sno 
HAVING COUNT(Sno) > 3;
 

---

实验六

--1.将01311班的全体学生的成绩置零
UPDATE Grade
SET Gmark = 0
WHERE Sno in
(SELECT Sno FROM Student
WHERE Clno = '01311');
 
 --2.删除2001级计算机软件的全体学生的选课记录
 DELETE  FROM Grade
 WHERE Sno in
 (SELECT Sno FROM Student
 WHERE Clno in
 (SELECT Clno FROM Class
 WHERE Speciality = '计算机软件'));

 --3.学生李勇已退学，从数据库重中删除他的有关记录
 DELETE FROM Grade
 WHERE Sno in
 (SELECT Sno FROM Student
 WHERE Sname = '李勇');

 UPDATE Class
 SET Number = Number - 1
 WHERE Clno in 
 (SELECT Clno FROM Student
 WHERE Sname = '李勇');

 UPDATE Class 
 SET Monitor = CASE
 WHEN Monitor =(
                  SELECT Sno FROM Student 
                  WHERE Sname = '李勇')
 THEN ''
 END
 FROM Class
 WHERE Clno in
 (SELECT Clno FROM Student
 WHERE Sname = '李勇') 

 DELETE FROM Student
 WHERE Sname = '李勇';


 --对每个班，求学生的平均年龄，并把结果存入数据库
 ALTER TABLE Class
 ADD Avgage Smallint null

 UPDATE Class
 SET Avgage = CASE
 WHEN Clno = '00311' 
 THEN (SELECT AVG(Sage)
        FROM Student
		WHERE Clno = '00311')
 WHEN Clno = '00312'
 THEN (SELECT AVG(Sage)
        FROM Student
		WHERE Clno = '00312')
 WHEN Clno = '01311'
 THEN (SELECT AVG(Sage)
        FROM Student
		WHERE Clno = '01311')
END
FROM Class

---

相关资料

SQL的各种连接Join详解

等老师讲完作业后会更新/(ㄒoㄒ)/~~