SDUT数据库原理第三章课后作业sql语句参考答案

目录

第一大题

第二大题

---

自己写的，在navicat中检验过没有什么问题，如有错误欢迎指正，答案仅供参考

利用SQL语句完成如下操作：

第一大题

第一大题：设有一个设备管理系统的模式（数据库）E-P-U，已知在数据库中有下列基本表，结构如下：

设备表Equipment（Eno, Ename, Ebuyday, Etypeid, Etypename）

人员表Person（Pno, Pname, Psex, Pbirth, Pposition）

设备使用表Use1（Eno, Pno, Borrowday, Usetime, Fee）

说明：

1. 设备表Equipment的属性为：设备编号，设备名称，购入日期，设备分类号，设备分类名；
2. 人员表Person的属性为：员工号，姓名，性别，出生日期，职位；
3. 设备使用表Use的属性为：设备编号，员工号，借出日期，使用时间，收费金额。

1. 为用户ADMIN创建模式（数据库）E-P-U，然后在其中创建Equipment、Person、Use1三个表。

CREATE SCHEMA E-P-U;

USE E-P-U;

CREATE TABLE Equipment(

Eno VARCHAR(10) NOT NULL,

Ename varchar(50) NOT NULL,

Ebuyday DATE NOT NULL,

Etypeid INT NOT NULL,

Etypename VARCHAR(50) NOT NULL,

PRIMARY KEY(Eno)

);

CREATE TABLE Person(

Pno VARCHAR(10) NOT NULL,

pname varchar(50) NOT NULL,

Psex CHAR(1) NOT NULL,

Pbirth DATE NOT NULL,

Pposition VARCHAR(50) NOT NULL,

PRIMARY KEY(Pno)

);

CREATE TABLE Use1(

Eno VARCHAR(10) NOT NULL,

Pno VARCHAR(10) NOT NULL,

Borrowday DATE NOT NULL,

Usetime INT NOT NULL,

Fee FLOAT NOT NULL,

PRIMARY KEY(Eno,Pno)

);

2.写出完成如下操作的SQL查询语句：

（1）查询设备使用表Use1中每种设备收费金额超过200的设备编号和收费金额。

SELECT Eno,Fee

FROM Use1

Group BY Eno

HAVING SUM(Fee)>200;

这里解释一下having 的作用，因为我写的时候也不知道......（源自chatgpt）

`HAVING` 关键字用于 SQL 中的 `GROUP BY` 子句，用于过滤聚合函数后的结果集。
具体来说，`HAVING` 关键字用于筛选组函数（如 `SUM`、`AVG`、`MAX`、`MIN`、`COUNT` 等）作用后的数据，类似地，可以看作是对聚合后的数据进行筛选过滤，筛选后的结果可以与 `WHERE` 条件结合使用。
使用 `HAVING` 时，可以用 `GROUP BY` 指定数据分组的依据，然后在 `HAVING` 中使用聚合函数过滤掉不符合条件的组，最终返回符合条件的聚合结果。

1. 检索借出设备名称（Ename）为“笔记本”的员工姓名，借出日期和收费金额。

SELECT Pname,Borrowday,Fee

FROM Person JOIN Use1 ON Person.Pno=Use1.Pno JOIN Equipment ON Use1.Eno=Equipment.Eno

WHERE Ename='笔记本';

1. 检索借出设备使用时间（Usetime）超过一个月的员工姓名、设备编号。

SELECT Pname,Eno

FROM Person JOIN Use1 ON Person.Pno=Use1.Pno

WHERE Usetime>30;

1. 检索借用设备的收费金额超过1000元的员工姓名、设备编号、设备名称。

SELECT Pname,Use1.Eno,Ename

FROM Person JOIN Use1 ON Person.Pno=Use1.Pno JOIN Equipment ON Use1.Eno=equipment.Eno

WHERE Fee>1000;

第二大题

第二大题：设有一个记录运动会的数据库，已知在数据库中有下列表：

运动员表Athlete（ano，aname，asex，aclassid, aclassname） 

项目表Item（ino，iname，type，idate)

参与项目表Participate (ano,ino,ranking)

其中运动员表Athlete的属性依次为运动员编号，运动员名称，性别，班级编号，班级名称；项目表Item的属性依次为项目号，项目名称，项目类型，比赛时间；参与项目表Participate的属性依次为运动员编号，项目号，名次。

1. 创建项目表item。（要求：包含主键约束和check约束idate >’2017-05-01’）。

CREATE TABLE item(

ino VARCHAR(20) PRIMARY KEY,

iname VARCHAR(20),

type VARCHAR(20),

idate DATE CHECK(idate>'2017-05-01')

);

2.添加一个项目记录，项目号为‘2017001’，项目名称为‘400M’，项目类型为‘田径’，比赛时间为‘2017-5-22’。

INSERT INTO item (ino,iname,type,idate) VALUES ('2017001','400M','田径','2017-5-22');

3.将运动员名称为‘张晓满’参与的项目名次修改为1。

UPDATE Participate SET ranking=1 WHERE ano=(SELECT ano FROM Athlete WHERE aname='张晓满')

这个SQL语句的作用是将参与项目表中某个运动员（姓名为'张晓满'）在某个项目中的名次修改为1。具体解释如下：

首先，选择参与项目表Participate，并使用UPDATE语句来更新其中的记录（即修改名次ranking的值）。语法形式为：

UPDATE Participate SET ranking=1 WHERE ...

然后，在WHERE子句中设置条件，这里用了一个子查询，该子查询从运动员表Athlete中筛选出姓名为'张晓满'的运动员，然后使用ano作为条件，选择参与项目表中的某行记录：

WHERE ano=(SELECT ano FROM Athlete WHERE aname='张晓满')

4.删除运动员名称为‘杨秀芳’的运动员信息。

DELETE FROM Athlete WHERE aname='杨秀芳';

5.查询参与项目表中名次为1的运动员编号和姓名。

SELECT p.ano,a.aname FROM Participate p JOIN Athlete a ON p.ano=a.ano WHERE p.ranking=1;

再插一句：（还是gpt老师说的）

FROM Participate p 是SQL中FROM子句的一种写法，表示需要从名为Participate的表中选取数据，并指定一个别名p来替代Participate这个表名，在后续的SELECT和WHERE等语句中使用p作为该表的表名。
使用表的别名的好处是可以简化SQL语句的书写。在FROM之后接表名时，如果该表名比较复杂或容易混淆，使用表的别名就可以让语句更加清晰易懂，并且在写复杂的联合查询时，指定别名可以让每个表的结构更加明显，方便程序员理解。
在这个SQL语句中，FROM Participate p表示我们需要从参与项目表Participate中选取数据，并指定别名为p，以便在后面的查询语句中使用p来代替Participate。这样，查询有关参与项目表中的信息时，就可以使用简洁的「p.列名」来代替长长的「Participate.列名」。

6.将参与项目表Participate的查询权限、修改权限授予用户zhu，并允许该用户将此权限授予其他用户。

GRANT SELECT,INSERT,UPDATE ON Participate TO zhu WITH GRANT OPTION;

7.定义视图，完成如下功能：输出没有运动员参加的项目信息。

CREATE VIEW ProjectWithoutAthlete AS

SELECT ino,iname,type,idate FROM item

where ino not in(select ino from Participate);

8.检索参与项目名称为“400M接力”的运动员名称(aname)和取得的名次。

SELECT a.aname,p.ranking FROM Athlete a JOIN Participate p ON a.ano=p.ano JOIN item i ON p.ino=i.ino where i.iname='400M接力';

 如有问题，欢迎指正哦