SDUT数据库原理——第三章作业参考

第3章 作业： 

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

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

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

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

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

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

1．答案

CREATE SCHEMA E-P-U AUTHORIZATION ADMIN

  CREATE TABLE Equipment

     ( Eno CHAR(9) PRIMARY KEY,

      Ename CHAR(20),

      Ebuyday DATE,

      Etypeid CHAR(9),

      Etypename CHAR(20)

);

  CREATE TABLE Person

     ( Pno CHAR(9) PRIMARY KEY,

      Pname CHAR(20),

      Psex CHAR(2),

      Pbirth DATE,

      Pposition CHAR(20)

);

  CREATE TABLE Use1

     ( Eno CHAR(9),

      Pno CHAR(9),

      Borrowday DATE,

      Usetime TIME,

      Fee SMALLINT,

      PRIMARY KEY (Eno,Pno),

      FOREIGN KEY (Eno) REFERENCES Equipment(Eno),

      FOREIGN KEY (Pno) REFERENCES Person(Pno)

);

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

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

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

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

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

2. 答案

(1) SELECT Eno,Fee

   FROM Use1

   WHERE Fee>200

(2) SELECT Pname,Borrowday,Fee

FROM Equipment,Person,Use1

   WHERE Ename=‘笔记本’ AND Equipment.Eno=Use1.Eno AND Person.Pno=Use1.Pno

(3) SELECT Pname, Eno

   FROM Person,Use1

   WHERE Usetime>31 AND Person.Pno=Use1.Pno

(4) SELECT Pname, Equipment.Eno, Ename

   FROM Equipment, Person,Use1

   WHERE Fee>1000 AND AND Equipment.Eno=Use1.Eno AND Person.Pno=Use1.Pno

第二大题：

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

1.CREATE table Item

( ino VARCHAR2(8) primary key ,

iname VARCHAR2(50),

type VARCHAR2(30),

idate ADTE CONSTRAINT check (idate >’2017-05-01’)

)

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

2.INSERT into Item(ino，iname，type，idate) VALUES (‘2017001’,’400M’,’ 田径’,’2017-5-22’)

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

3.UPDATE Participate set ranking = 1

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

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

4.Delete from Athlete where aname=’ 杨秀芳’

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

5.Select ano,anem  From Athlete  Where ano in(

select ano   from Participate   where ranking=’1’)

或：Select Athlete.ano,anem

From Athlete, Participate

Where Athlete.ano= Participate.ano and ranking=’1’

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

6.GRANT SELECT，UPDATE ON Participate TO zhu WITH GRANT OPTION

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

7.CREATE view v_Athlete

As

SELECT *

From Athlete

Where ano not in(

select ano from Participate )

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

8.Select aname,ranking

From Athlete, Participate,Item

Where Athlete.ano= Participate.ano and Item.ino= Participate.ino and iname=’400M接力’