【数据库原理】实验报告（西电网信院）

---

title: 【数据库原理】实验报告
categories:

• 杂项
  tags:
• 数据库
• 作业
• PostgreSQL

---

【数据库原理】实验报告

客户端安装

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\]...)，但使用前必须创建好对应的表

-- 创建表

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

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课程的学生学号和成绩

SELECT
	sc.sno,
	sc.grade 
FROM
	sc 
WHERE
	sc.cno = 'CS3121014';

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

SELECT
	student.sno,
	student.sname
FROM
	sc, student
WHERE
	sc.sno = student.sno AND
	sc.cno = 'CS3121014';

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

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课程的学生学号

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课程的学生学号

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课的学生学号

SELECT
	sno 
FROM
	student 
WHERE
	sno != ANY ( SELECT sno FROM sc WHERE cno = 'CS3121014' AND sno = student.sno );
-- 一定要连接表  sno=student.sno

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

SELECT
	pcourse.cpno
FROM
	pcourse
WHERE
	pcourse.cno = 'CS3121014';

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

SELECT
	sno 
FROM
	student 
WHERE
	sno IN ( 
	SELECT sno 
	FROM sc 
	GROUP BY sno 
	HAVING COUNT ( * ) = ( 
		SELECT COUNT ( * ) 
		FROM course ) 
	);

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

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.查询所有姓刘学生的姓名、学号和性别。

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

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

SELECT
	student.sno, 
	student.sname
FROM
	student
WHERE
	student.sname LIKE '_阳%';

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

SELECT
	COUNT(DISTINCT sno)
FROM
	sc;

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

SELECT
	MAX(grade)
FROM
	sc
WHERE
	sc.cno = 'CS3121014';

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

SELECT SUM(ccredit) 
FROM
	course 
WHERE
	cno IN ( SELECT cno FROM sc WHERE sno = '03051014' );

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

SELECT
	sno 
FROM
	sc 
GROUP BY
	sno 
HAVING
	COUNT ( * ) > 3;

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

SELECT
	sno,
	COUNT ( * ) 
FROM
	sc 
WHERE
	grade >= 90 
GROUP BY
	sno 
HAVING
	COUNT ( * ) >= 4;

---

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

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