sql习题二

习题二

 

 

一、              一个简单的著作管理数据库，涉及如下数据：

作家Author：作家号Authorno、姓名Name、性别Sex和级别Grade

作品Book：书号Bookno、书名Title、种类Type、发行时间Date和出版社Publish

一位作家可以有多本著作，一本著作可同时多人合著，要求能描述作家出书的情况。

1）、设计简单的著作管理系统的E/R图；

 

 

2）转换成关系模型（请注明每个关系的键码）

 

 

二、              设有一个简单的经销商产品数据库如下：

     生产厂商关系：Producer（PrNo, Name, Addr, Tel），分别表示生产厂商号、

厂商名称、地址和联系电话

    产品关系：Product（TtNo, Brand, Model, Price），分别表示产品号、名称、

品牌型号和价格等

    供应关系：Supply（PrNo, PtNo, Quantity），分别表示生产厂商号、产品号以及数量

1）、查找生产厂商“方正科技公司”供应的产品的名称和价格，结果按价格的

降序排列；

 

 2）、求各类产品的最高价格、最低价格、平均价格和数量；

 

 3）、查询一种产品供应供应数量在500件以上的生产厂商名称。

 

 

三、              用SQL的有关语句定义：（共12分，每小题4分）

1)、航班关系Flight，包括航班号Number、机型Kind、起飞日期GoDate；

2）、乘客关系Customer，包括身份证号码Idno、姓名Name、性别Sex、地址Addr；

3）、搭乘关系Booking，包括身份证号码Idno、航班号Number、座位Seat；

要求说明每个关系的主键码和外键码（如果有的话），并在键码属性上建立索引。

4）、授予王飞对航班关系Flight的修改和删除的权限；

5）、删除2001年4月1日的所有航班记录；

6）、将航班号为KH3001的机型改为波音757；

7）、对航班关系的日期属性进行约束，使得其日期不得是2000年以前的。

 

 

 

四、按嵌入式SQL的格式在给出的产品关系Product中插入任意一个产品的产品号、

名称、品牌型号、价格等。）

 

 

一、

 

Member

Write

Book

Part

Name

Sex

Grade

Authorno

Bookno

Title

Publish

   注：Part表明作家是独立完成著作还是第一、第二作者等。

 

2）转换成关系模型为：

Author ( Authorno, Name, Sex, Grade)

Book ( Bookno, Title, Type, Date, Publish)

Write ( Authorno, Bookno, Part)

二、SQL查询语句：

   1）、SELECT Brand, Price

 FROM Product

 WHERE PtNo    IN

( SELECT PtNo

FROM Supply, Producer

WHERE Supply.PrNo=Producer.PrNo AND Name= “方正科技公司”)

ORDER BY Price DESC ;

或者：

SELECT Brand, Price

 FROM Product, Supply, Producer

 WHERE Product.PtNo=Supply.PtNo AND Supply.PrNo=Producer.PrNo

          AND Name== “方正科技公司”

 ORDER BY Price DESC ;

或者：

SELECT Brand, Price

 FROM Product

 WHERE PtNo    IN

( SELECT PtNo

FROM Supply

WHERE PrNo IN 

 ( SELECT PrNo

    FROM Producer

    WHERE Name== “方正科技公司”))

 ORDER BY Price DESC ;

 

   2)、SELECE Brand, max(Price) AS 最高价, min(Price) AS 最低价,

avg(Price) AS 平均价, count(Brand) AS 数量

FROM    Product

GROUP BY Brand ;

 

   3）、 SELECT DISTINCT Name

        FROM   Producer

        WHERE PrNo IN ( SELECT   PrNo

FROM Supply

WHERE Quantity>500) ;

       或者：

        SELECT DISTINCT Name

        FROM   Producer, Supply

        WHERE Producer.PrNo=Supply.PrNo   AND Quantity>500 ;

三、用SQL的有关语句定义：（共12分，每小题4分）

   1）、 CREATE TABLE Flight

          (Number CHAR(10) NOT NULL PRIMARY KEY ,

           Kind CHAR(10) ,

           GoDate   DATE ) ;

  

        CREATE INDEX Fkey ON Flight (Number);

 

   2）、 CREATE TABLE Customer

          ( Idno   VARCHAR(18)   NOT NULL PRIMARY KEY ,

           Name CHAR(10) NOT NULL , 

            Sex   BOOLEAN ,

           Addr   VARCHAR(30) ) ;

 

        CREATE INDEX Ckey ON Customer (Idno);

 

   3）、 CREATE TABLE Booking

          ( Idno VARCHAR(18) NOT NULL ,

           Number CHAR(10) NOT NULL ,

           Seat CHAR(4) ,

           PRIMARY KEY(Idno, Number) ,

     FOREIGN KEY(Idno) REFERENCES Customer(Idno ) ,

     FOREIGN KEY(Number) REFERENCES Flight ( Number) ) ;

 

   CREATE INDEX Bkey ON Booking (Idno, Number) ;

 

三 SQL的有关语句实现：（共12分，每小题3分）

1）、GRANT UPDATE, DELETE ON Flight    TO 王飞

 

2）、DELETE FROM Flight WHERE GoDate= “2001-04-01” ;

 

3)、UPDATE　 Flight SET Kind= “波音757”

       WHERE Number= “KH3001” ;

 

4)、 在定义Flight时，加入对属性GoDate的约束如下：

    GoDate DATE CHECK (GoDate>= “2001-01-01”) ,

 

四、（8分）

1）说明：      EXEC SQL BEGIN DECLARE SECTION;

                            char pno[10];

                            char b[20];

                char m[20];

                float p;

                char SQLSTATE[6];

                     EXEC SQL END DECLARE SECTION;

 

2）赋值：      pno:= “P300101” ;

            b:= “内存条” ;

            m:= “Kindmax 128” ;

            p:=136 ;

 

3）插入：      EXEC SQL INSERT INTO Product

                        VALUES(:pno, :b, :m, :p) ;