数据库上机2

发表于 | 分类于 ， ， |
字数统计: | 阅读时长 ≈

数据库上机2

第一次上机代码如下

代码如下

12题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- 12.1
SELECT DISTINCT Cno from grade,student WHERE grade.Sno = student.Sno;

-- 12.2
SELECT * from student where Clno = 00311 AND Ssex = '女';

-- 12.3
SELECT Sname,Sage,Sno from student where Clno in ('01311','01312');

-- 12.4
SELECT * from student where Sname LIKE '李%';

-- 12.5
SELECT Number AS '李勇班级人数' from class WHERE Clno IN (SELECT Clno from student WHERE Sname = '李勇');
-- 方法一

SELECT COUNT(*) AS '李勇班级人数' from student where Clno in (select Clno from student where Sname = '李勇');
-- 方法二

-- 12.6
SELECT MAX(Gmark) AS '最高分', MIN(Gmark) AS '最低分' , AVG(Gmark) AS '平均成绩' FROM Grade g, Course c WHERE g.Cno IN(SELECT c.Cno FROM Course 
WHERE c.Cname = '操作系统' );

-- 12.7
SELECT COUNT(DISTINCT Sno,Sno) AS '选修了课程的学生人数' from grade where Sno IN (SELECT Sno from student);

-- 12.8
SELECT COUNT(*) AS '选修操作系统人数' FROM grade WHERE Cno IN (SELECT Cno from course WHERE Cname = '操作系统');

-- 12.9
SELECT Sname from Student where Sno IN (SELECT Sno from grade where Gmark is NULL AND Clno IN (SELECT Clno from class where Speciality = '计算机软件' AND Inyear = '2000'));

13题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

-- 13.1
SELECT * 
from student 
where Clno in (
	SELECT Clno 
	from student 
	WHERE Sname = '李勇') AND Sname not in ('李勇');

-- 13.2
SELECT * from student 
where Sno in (
	SELECT DISTINCT Sno 
	from grade 
	where Cno in (
		SELECT Cno 
		from grade 
		where Sno IN (
			SELECT Sno 
			from student 
			where Sname = '李勇'))) AND Sname <> '李勇';

-- 13.3
SELECT * 
from student 
where Sage BETWEEN (SELECT Sage from student where Sname = '李勇') AND 25;

-- 13.4
SELECT Sno,Sname 
from student 
where Sno IN (
	SELECT Sno 
	from grade 
	where Cno IN(
		SELECT Cno 
		from course
		WHERE Cname IN('操作系统')));

-- 13.5
SELECT Sname 
from student 
where Sno NOT IN (
	SELECT Sno from grade where Cno IN (1));
	
-- 13.6
SELECT Sname 
from student 
where Sno IN (
	SELECT Sno 
	from grade 
	GROUP BY Sno 
	HAVING COUNT(*) = (SELECT COUNT(*) FROM course));

14题

1
2
3
4
5
6
7
8
9
10
11
-- 14.1
SELECT Sno,Gmark from grade where Cno = '3' ORDER BY Gmark DESC;

-- 14.2
SELECT * from student ORDER BY Clno ASC, Sage DESC;

-- 14.3
SELECT Cno AS '课程号', COUNT(*) AS '选修人数' from grade GROUP BY Cno;

-- 14.4
SELECT Sno, COUNT(sno) AS '选修课程数' from grade GROUP BY Sno HAVING(COUNT(Sno) > 3);

15题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

-- 15.1
UPDATE grade SET Gmark = 0 where Sno IN (SELECT Sno from student where Clno = '01311');
SELECT * from grade where Sno IN (SELECT Sno from student WHERE Clno = '01311');

-- 15.2
DELETE from grade where Sno LIKE '2001%' AND Sno IN (
	SELECT Sno 
	from student 
	where Clno IN (select Clno from class where Speciality = '计算机软件'));
SELECT * from grade;

-- 15.3
UPDATE class set Monitor = null where Monitor IN (SELECT Sno FROM student WHERE Sname = '李勇');
DELETE from Grade where Sno IN (SELECT Sno FROM student WHERE Sname = '李勇');
DELETE from student where Sname = '李勇';

-- 15.4
CREATE VIEW AvgAge AS (SELECT Clno AS '班级号',AVG(Sage)  AS '平均年龄' from student GROUP BY Clno);

上机补充题

1
2
3
4
5
6
7
8
9
-- 第二次上机补充题1
SELECT s.Sno AS '学号', s.Sname AS '姓名',COUNT(g.Sno) AS '选课数' from student s LEFT JOIN grade g ON s.Sno = g.Sno GROUP BY s.Sno,s.Sname;

-- 第二次上机补充题2
SELECT cl.Clno AS '班级号', Cno AS '课程号', COUNT(g.Sno) AS '选课人数', AVG(Gmark) AS '该班级的平均分' 
from grade g 
LEFT JOIN student s ON g.Sno = s.Sno 
LEFT JOIN class cl ON s.Clno = cl.Clno 
GROUP BY cl.Clno,g.Cno;