查表

男娘i 2022-05-11 11:48 184阅读 0赞

1.  查询"红楼梦"目前可借的各图书编号，及所属版本信息。（是否借出为'否'的图书）

select D.ISBN,
    D.BNAME as 书名
    from BOOK B, BOOKDETAIL D
    WHERE 
      D.isbn = B.Isbn
      AND B.B_ISBORROW = '否'
      AND D.BNAME = '红楼梦'

![70][]

2、查找高等教育出版社的所有书目及单价，结果按单价降序排序。

select BD.ISBN,
    BD.BNAME AS 书名,
    bd.bprice as 价格
    from BOOKDETAIL BD
    WHERE BD.BPUBLISH ='人民出版社'
    ORDER BY BD.BPRICE DESC

![70 1][]  
3、统计"红楼梦"各版的藏书数量（ISBN不同则版本不同）。

select B.ISBN,
    COUNT(B.BID) AS 数量
    from BOOKDETAIL BD, BOOK B
    WHERE BD.BNAME = '红楼梦'
    AND BD.ISBN = B.ISBN
    GROUP BY B.ISBN

![70 2][]  
4、查询学号"20061234"号借书证借阅未还的图书的信息。

select BD.*
    from BOOKDETAIL BD, BOOK B, LEND L
    WHERE BD.ISBN = B.ISBN
    AND B.B_ISBORROW = '是'
    AND L.BID = B.BID
    AND L.RID = '20061234'

![70 3][]  
5、查询各个出版社的图书最高单价、平均单价。

select 
    BD.BPUBLISH,
    MAX(BD.BPRICE) AS 最高价,
    AVG(BD.BPRICE) AS 平均价
    from BOOKDETAIL BD
    GROUP BY BD.BPUBLISH

![70 4][]  
6、要查询借阅了两本和两本以上图书的读者的个人信息。

SELECT R.*
    FROM READER R,
    (select L.RID AS RID
    from LEND L
    GROUP BY L.RID
    HAVING COUNT(*) >= 2) TEMP
    WHERE R.RID = TEMP.RID

![70 5][]  
7、查询"王菲"的单位、所借图书的书名和借阅日期。

SELECT R.RDEPT AS 单位,
    BD.BNAME AS 书名,
    L.STARTDATE AS 借阅日期
    FROM READER R, BOOK B, BOOKDETAIL BD, LEND L
    WHERE B.ISBN = BD.ISBN
    AND    L.BID = B.BID
    AND    R.RID = L.RID
    AND    R.RNAME = '王菲'

![70 6][]  
8、查询每类图书的册数和平均单价。

SELECT 
    BD.CID,
    COUNT(*),
    AVG(BD.BPRICE)
    FROM BOOKDETAIL BD, CAT C
    WHERE BD.CID = C.CID 
    GROUP BY BD.CID

![70 7][]  
9、统计从未借书的读者人数。

SELECT COUNT(*)
    FROM READER R
    WHERE R.RID NOT IN (
      SELECT L.RID
      FROM LEND L
    )

![70 8][]  
10、统计参与借书的人数。

SELECT COUNT(*)
    FROM READER R
    WHERE R.RID IN (
      SELECT L.RID
      FROM LEND L
    )

![70 9][]  
11、找出所有借书未还的读者的信息及所借图书编号及名称。

select BD.ISBN,
    BD.BNAME,
    R.*
    from BOOKDETAIL BD, BOOK B, LEND L, READER R
    WHERE L.RID = R.RID
    AND B.B_ISBORROW = '是'
    AND L.ENDDATE IS NULL
    AND BD.ISBN = B.ISBN
    AND L.BID = B.BID

![70 10][]  
12、检索书名是以"Internet"开头的所有图书的书名和作者。

我用数据开头好让能查一个数据出来

select BD.BNAME AS 书名,
    BD.BAUTHER AS 作者
    from BOOKDETAIL BD
    WHERE REGEXP_LIKE(BD.BNAME, '数据')

![70 11][]  
13、查询各图书的罚款总数。

select BD.BNAME, F.FMONEY
    from FINE F
    LEFT JOIN LEND L 
    ON L.FID = F.FID,
    BOOK B,
    BOOKDETAIL BD
    WHERE F.FID = L.FID AND L.BID = B.BID AND BD.ISBN = B.ISBN

![70 12][]  
14、查询借阅及罚款分类信息，如果有罚款则显示借阅信息及罚款名称、罚金，如果没有罚款则罚款名称、罚金显示空（左外连接）

select L.*, F.FNAME, F.FMONEY
    from LEND L
    LEFT OUTER JOIN FINE F
    ON L.FID = F.FID

![70 13][]

15、查询借阅了所有"文学"类书目的读者的姓名、单位。

[70]: /images/20220505/73e7e575176141b3acac2940bb43c596.png
[70 1]: /images/20220505/4511d5ad81f244bb98c482622ae5c8d4.png
[70 2]: /images/20220505/79eb55445dbb4135ad46739fc36150be.png
[70 3]: /images/20220505/85b5fe2494584b6da97da87f1199c994.png
[70 4]: /images/20220505/e0d5310fce1448c6b436b3c6eea8826d.png
[70 5]: /images/20220505/eb6341eeff8749629d6b82716223a668.png
[70 6]: /images/20220505/522c65826f9f4634af91dc1af908da7d.png
[70 7]: /images/20220505/df45a7e166e8415c89b8a3b3e91f1963.png
[70 8]: /images/20220505/ecd7a8507b784ef0a64aa73ca22cd830.png
[70 9]: /images/20220505/60a33ac855c040c0a5a07c8aadc5e1e6.png
[70 10]: /images/20220505/136493c5164c4212b96585e4025fbcf0.png
[70 11]: /images/20220505/f3c540e51e0146f9bdad6efcfb1fd74c.png
[70 12]: /images/20220505/8da03c3b42ae4052ac5d7ca9f29ce95e.png
[70 13]: /images/20220505/a76d4f5d319445b6ad0bd695691a1359.png