Framist's Little House

◇ 自顶而下 - 面向未来 ◇

0%

【数据库原理】实验报告

发表于 更新于 分类于

【数据库原理】实验报告

客户端安装

Navicat for PostgreSQL 可以通过以下链接申请 http://www.navicat.com.cn/sponsorship/education/student 依照说明安装对应 OS 的版本

连接并登录数据库服务器

在 Navicat 界面中输入数据库服务器信息，如下：

IP： x.x.x.x:xxxx

用户名/密码： xxxx

连接数据库时初始数据库为 schemas，连接数据库后确保在 schemas 数据库中创建对应的表，并加载数据集。

可以通过 Navicat UI 组件创建对应的表（.zip 中有部分语句可以参考）并加载数据；

也可通过 Navicat 提供的 SQL 命令行工具用 PostgreSQL 的数据集导入语句输入 (如：COPY \[tablename\] FROM \[filepath\]...)，但使用前必须创建好对应的表

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
53
-- 创建表

--
-- 学生 表
-- 学号 姓名 性别 

CREATE TABLE student (
	sno CHAR ( 8 ),
	sname CHAR ( 6 ) NOT NULL,
	ssex CHAR ( 2 ) NOT NULL,
	PRIMARY KEY ( sno ),
	CHECK ( ssex IN ( '男', '女' ) ) 
);


--
-- 课程 表
-- 课程号 课程 学分

CREATE TABLE course ( 
cno CHAR ( 9 ), 
cname CHAR ( 48 ) NOT NULL, 
ccredit INT, 
PRIMARY KEY ( cno ) 
);

--
-- 先修课程 表
-- 课程 先修课程

CREATE TABLE pcourse (
	cno CHAR ( 9 ),
	cpno CHAR ( 9 ),
	PRIMARY KEY ( cno, cpno ),
	FOREIGN KEY ( cno ) REFERENCES course ( cno ),
	FOREIGN KEY ( cpno ) REFERENCES course ( cno ) 
);

--
-- 成绩 表
-- 学号 课程号 成绩

CREATE TABLE sc (
	sno CHAR ( 8 ),
	cno CHAR ( 9 ),
	grade INT,
	PRIMARY KEY ( sno, cno ),
	FOREIGN KEY ( sno ) REFERENCES student ( sno ),
	FOREIGN KEY ( cno ) REFERENCES course ( cno ),
	CHECK ( grade IS NULL OR grade >= 0 AND grade <= 100 ) 
);


针对以下各具体任务，编写对应的 SQL，提交并查看返回结果

1.查询选修了 CS3121014 课程的学生学号和成绩

1
2
3
4
5
6
7
SELECT
	sc.sno,
	sc.grade 
FROM
	sc 
WHERE
	sc.cno = 'CS3121014';

2.查询选修了 CS3121014 课程的学生学号和姓名

1
2
3
4
5
6
7
8
SELECT
	student.sno,
	student.sname
FROM
	sc, student
WHERE
	sc.sno = student.sno AND
	sc.cno = 'CS3121014';

3.查询选修数据库系统课程的学生学号、姓名和成绩，查询结果按分数降序排列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT
	sc.sno,
	student.sname,
	sc.grade
FROM
	sc
	INNER JOIN
	course
	ON 
		sc.cno = course.cno
	INNER JOIN
	student
	ON 
		sc.sno = student.sno
WHERE
	course.cname = '数据库系统'

ORDER BY
	sc.grade DESC;

4.查询选修了 CS3121014 或 CS3221018 课程的学生学号

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
SELECT
	sno 
FROM
	student 
WHERE
	sno IN (
	SELECT
		"first".sno 
	FROM
		sc AS "first"
		INNER JOIN sc AS "second" ON "first".sno = "second".sno 
	WHERE
		"first".cno = 'CS3121014' 
	OR "second".cno = 'CS3221018' 
	);
	
	-- 更简洁的写法：
SELECT
	sno 
FROM
	sc 
WHERE
	cno = 'CS3121014' UNION
SELECT
	sno 
FROM
	sc 
WHERE
	cno = 'CS3221018';

5.查询选修了 CS3121014 和 CS3221018 课程的学生学号

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
SELECT
	sno 
FROM
	student 
WHERE
	sno IN (
	SELECT
		"first".sno 
	FROM
		sc AS "first"
		INNER JOIN sc AS "second" ON "first".sno = "second".sno 
	WHERE
		"first".cno = 'CS3121014' 
		AND "second".cno = 'CS3221018' 
	);
	
-- 更简洁的写法：
SELECT
	sno 
FROM
	sc 
WHERE
	cno = 'CS3121014' INTERSECT
SELECT
	sno 
FROM
	sc 
WHERE
	cno = 'CS3221018';

6.查询不学 CS3121014 课的学生学号

1
2
3
4
5
6
7
SELECT
	sno 
FROM
	student 
WHERE
	sno != ANY ( SELECT sno FROM sc WHERE cno = 'CS3121014' AND sno = student.sno );
-- 一定要连接表  sno=student.sno

7.查询 CS3121014 的先修课的课程号

1
2
3
4
5
6
7
SELECT
	pcourse.cpno
FROM
	pcourse
WHERE
	pcourse.cno = 'CS3121014';

8.查询选修了全部课程的学生学号

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
	sno 
FROM
	student 
WHERE
	sno IN ( 
	SELECT sno 
	FROM sc 
	GROUP BY sno 
	HAVING COUNT ( * ) = ( 
		SELECT COUNT ( * ) 
		FROM course ) 
	);

9.查询选修了学号为“03051066”的学生所选全部课程的学生学号和姓名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
	sno,sname
FROM
	student 
WHERE
	NOT EXISTS (
	SELECT
		* 
	FROM
		sc AS sc_1 
	WHERE
		sno = '03051066' 
	AND NOT EXISTS ( SELECT * FROM sc AS sc_2 WHERE sc_2.sno = student.sno AND sc_2.cno = sc_1.cno ) 
	);

10.查询所有姓刘学生的姓名、学号和性别。

1
2
3
4
5
6
SELECT
	sname,sno,ssex -- 注意顺序，错了没有分（比如写 * ）555
FROM
	student
WHERE
	student.sname LIKE '刘%';

11.查询姓名中第二个字为“阳”字的学生姓名和学号。

1
2
3
4
5
6
7
SELECT
	student.sno, 
	student.sname
FROM
	student
WHERE
	student.sname LIKE '_阳%';

12.查询选修了课程的学生人数

1
2
3
4
SELECT
	COUNT(DISTINCT sno)
FROM
	sc;

13.查询选修 CS3121014 课程的学生最高分数

1
2
3
4
5
6
SELECT
	MAX(grade)
FROM
	sc
WHERE
	sc.cno = 'CS3121014';

14.查询学生 03051014 选修课程的总学分数

1
2
3
4
5
SELECT SUM(ccredit) 
FROM
	course 
WHERE
	cno IN ( SELECT cno FROM sc WHERE sno = '03051014' );

15.查询选修了 3 门以上 (>=) 课程的学生学号

1
2
3
4
5
6
7
8
SELECT
	sno 
FROM
	sc 
GROUP BY
	sno 
HAVING
	COUNT ( * ) > 3;

16.查询有 4 门以上 (>=) 课程是 90 分以上的学生的学号及 (90 分以上的) 课程数

1
2
3
4
5
6
7
8
9
10
11
SELECT
	sno,
	COUNT ( * ) 
FROM
	sc 
WHERE
	grade >= 90 
GROUP BY
	sno 
HAVING
	COUNT ( * ) >= 4;

确认每一个任务的返回结果无误后，将你确认的每一个 SQL 提交

  1. 注意 提交 SQL 前务必在数据库 schemas 中进行了表的创建并导入了对应数据！！！
  2. 注意 每个同学只可提交一次，请务必确认所有 SQL 无误后进行提交！！！（这条必须重视，在这 -1qaq）
  3. 注意 提交时，务必注意结果中各列的顺序与题目要求严格一致！，否则会影响成绩判定（这条必须重视，在这 -1qaq）