河南大学数据库实验5

分别给出对应题目的代码及运行结果截图：

1. 找出读者所在城市是“shanghai”的身份是“professor”的读者，或所在城市名包含“jing”的身份为“student”的读者的读者号及身份，按读者号的降序排列

SELECT Rno,STATUS

FROM r

WHERE (city='shanghai' AND STATUS='professor') OR (city LIKE '%jing' AND STATUS='student')

ORDER BY rno DESC

1. 对每一种有读者订购的图书，找出书号及有读者订购该书读者所在的所有城市，输出结果包括：书号和城市（输出的列取别名）

SELECT bno AS '书号',city '城市'

FROM od,r

WHERE od.rno=r.Rno 

1. 找出订购了书号为B2的图书的读者姓名及所在城市(用两种方法做：连接、嵌套)

SELECT rname,city

FROM od,r

WHERE od.rno=r.rno AND bno='B2'

SELECT rname,city

FROM r

WHERE rno IN (

 SELECT rno

 FROM od

 WHERE bno='B2'

 )

1. 找出有一个以上读者订购的图书书号和图书名

SELECT bname,bno

FROM b

WHERE bno IN(

 SELECT DISTINCT bno

 FROM od

 )

1. 求至少订购了一本《Pascal》的读者姓名

SELECT DISTINCT rno

FROM od

WHERE bno IN(

 SELECT bno

 FROM b

 WHERE Bname='Pascal'

 )

1. 找出没有订购B1号图书的读者号

SELECT DISTINCT o1.Rno

FROM od o1

WHERE rno NOT IN (

 SELECT DISTINCT o2.rno

 FROM od o2

 WHERE o2.bno='B1'

 );

1. 找出价格大于等于15.00或者读者R2最近订购的图书的书号及书名

SELECT bno,bname

FROM b

WHERE price>=15 OR bno IN (

 SELECT bno

 FROM od

 WHERE rno='R2'

 )

1. 求图书B2的订购数

SELECT bno,SUM(qty)

FROM od

GROUP BY bno

HAVING bno='B2'

1. 查询订购的书的数量不确定的(qty是空值)，读者编号和书号

SELECT rno,bno

FROM od

WHERE qty=NULL

1. 查询图书单价不是15,17,10的图书的图书号和图书名及价格

SELECT bno,bname,price

FROM b

WHERE price NOT IN (15,17,10)

1. 查询所有订购了图书的读者的读者号和姓名

SELECT rno,rname

FROM r

WHERE rno IN(

 SELECT distinct rno

 FROM od

 )

1. 查询单价小于等于10的所有图书被读者订购的数量，输出书名和订购总数量

SELECT bname,SUM(qty)

FROM od

JOIN b ON b.Bno=od.bno

WHERE price<=10

GROUP BY bname

1. 查询订购了至少两种书的读者姓名和书的种类数

SELECT rname,COUNT(bno)

FROM r

JOIN od ON od.rno=r.rno

GROUP BY rname

HAVING COUNT(bno)>=2;

1. 查询书价比同一出版社的平均书价高的图书的书号和书名

SELECT b1.bno,b1.bname

FROM b b1

WHERE b1.price>(

 SELECT AVG(b2.price)

 FROM b b2

 WHERE b2.Pub = b1.pub

 GROUP BY b2.pub

 )

1. 查询每个出版社书价最高的图书是哪些, 输出出版社,书号,书名

SELECT pub,bno,bname

FROM b b1

WHERE price=(

 SELECT MAX(price)

 FROM b b2

 WHERE b2.Pub = b1.Pub

 )

1. 找出没有订购B2号图书的读者号，读者的姓名（用存在谓词查询） 

SELECT rno,rname

FROM r

WHERE  NOT EXISTS(

 SELECT rno

 FROM od

 WHERE bno='B2' AND r.rno=od.rno

 ) 

1. 查询读者R4订购的图书的书号和书名（用存在谓词查询）

SELECT bno,bname

FROM b 

WHERE EXISTS(

 SELECT rno

 FROM od

 WHERE rno='R4' AND od.bno=b.Bno

 )

1. 查询订购了全部书籍的读者的姓名

SELECT R.Rname

FROM R

JOIN OD ON R.Rno = OD.Rno

GROUP BY R.Rno, R.Rname

HAVING COUNT(DISTINCT OD.Bno) = (

           SELECT COUNT(*) 

           FROM B);

1. 查询至少订购了R1所订购的书籍的读者的编号

SELECT DISTINCT rno

FROM od s1

WHERE not EXISTS(

 SELECT bno

 FROM od s2

 WHERE s2.rno='R1' AND not EXISTS(

  SELECT *

  FROM od s3

  WHERE s3.rno = s1.Rno AND s3.bno = s2.bno)

)AND s1.Rno <> 'R1'

1. 把所有Commerce出版社的图书的价格都增加1

UPDATE B

SET Price = Price + 1

WHERE Pub = 'Commerce';

1. 把读者R2订购的所有图书的数量改为0

UPDATE od

SET qty = 0

WHERE rno='R2'

1. 查询读者表中是否有一个Beijing的读者WangWei,如果有,把他删除

DELETE FROM r

WHERE city='Beijing' AND rname ='DingWei'

1. 查出每个出版社的图书的平均价格,保存到数据库中

CREATE TABLE Pub_Avg_Price

(

Pub VARCHAR(20),

Avg_Price numeric(5,2)

);

INSERT INTO Pub_Avg_Price (Pub, Avg_Price)

SELECT Pub, AVG(Price)

FROM B

GROUP BY Pub;

1. 将所有Beijing读者订购的图书数量改为一本

UPDATE od

SET qty = 1

WHERE rno IN (

 SELECT rno

 FROM r

 WHERE city = 'Beijing')

1. 删去Shanghai的所有读者的订书单

DELETE FROM od

WHERE rno IN(

 SELECT rno

 FROM r

 WHERE city='shanghai'

 )

总结：通过这次实验让我了解了in和exists的区别在本次实验中的一些例题中需要用两个no exists来查询所有的信息就好像c中的for的功能，并且在该练习中不断加深了对exists的了解，除了存在语句还了解了嵌套查询和分组查询的一些共同性和差异性，并且也对group的一些用法更加牢固，通过group的语法可以将一些成员进行分组，并在其中进行avg，count的时候是对分组内的进行分析，该实验还进行了对insert，update，delete的用法，需要指定了表然后选定一些条件进行操作，其中有着一些嵌套操作，将条件复杂化，所以需要用到嵌套的查询。该实验很好的将之前的知识点进行了融合和表达，让我更加综合的运用此次实验的知识点和内容。

运用：这个图书馆的数据库可能包含以下几个表格：Books（图书）、Orders（订单详情）、Readers（读者）、Publishers（出版社）。

1.找出没有订购B1号图书的读者号

SELECT R.Rno

FROM Readers R

WHERE NOT EXISTS (

    SELECT *

    FROM Orders O

    WHERE O.Bno = 'B1' AND R.Rno = O.Rno

);

找出价格大于等于15.00或者读者R2最近订购的图书的书号及书名

SELECT B.Bno, B.Bname

FROM Books B

WHERE B.Price >= 15.00

OR B.Bno IN (

    SELECT O.Bno

    FROM Orders O

    WHERE O.Rno = 'R2'

    ORDER BY O.OrderDate DESC

    LIMIT 1

);

求图书B2的订购数

SELECT COUNT(*)

FROM Orders

WHERE Bno = 'B2';

查询订购的书的数量不确定的（qty是空值），读者编号和书号

SELECT Rno, Bno

FROM Orders

WHERE Qty IS NULL;

查询图书单价不是15,17,10的图书的图书号和图书名及价格

SELECT Bno, Bname, Price

FROM Books

WHERE Price NOT IN (15, 17, 10);

查询所有订购了图书的读者的读者号和姓名

SELECT DISTINCT R.Rno, R.Rname

FROM Readers R

JOIN Orders O ON R.Rno = O.Rno;

查询单价小于等于10的所有图书被读者订购的数量，输出书名和订购总数量

SELECT B.Bname, SUM(O.Qty)

FROM Books B

JOIN Orders O ON B.Bno = O.Bno

WHERE B.Price <= 10

GROUP BY B.Bname;

查询订购了至少两种书的读者姓名和书的种类数

SELECT R.Rname, COUNT(DISTINCT O.Bno)

FROM Readers R

JOIN Orders O ON R.Rno = O.Rno

GROUP BY R.Rno

HAVING COUNT(DISTINCT O.Bno) >= 2;

查询书价比同一出版社的平均书价高的图书的书号和书名

SELECT B.Bno, B.Bname

FROM Books B

WHERE B.Price > (

    SELECT AVG(B2.Price)

    FROM Books B2

    WHERE B2.Pub = B.Pub

);

查询每个出版社书价最高的图书是哪些, 输出出版社,书号,书名

SELECT B.Pub, B.Bno, B.Bname

FROM Books B

WHERE B.Price = (

    SELECT MAX(B2.Price)

    FROM Books B2

    WHERE B2.Pub = B.Pub);