有如下数据库结构：图书表【Book】编号 字段名称 字段描述 数据类型 备注1 bookId 图书编号 number 主键2 bookName 图书名称 varchar250 3 author 作者 varchar240 4 publisher 出版社 varchar2200 5 salePrice 销售价 number 6 vipPrice 会员价

1、查询图书表中的3到5行记录。

SELECT * FROM Book WHERE ROWNUM BETWEEN 3 AND 5;

2、从订单表中查询出顾客姓名、下单日期、订单状态。订单状态按如下要求显示。 A显示：确认 B显示:已付款 C 显示:发货 D 显示:已收货

SELECT custName, createDate, CASE WHEN status = 'A' THEN '确认' WHEN status = 'B' THEN '已付款' WHEN status = 'C' THEN '发货' WHEN status = 'D' THEN '已收货' END AS orderStatus FROM Orders;

3、查询张三下过的订单的详细信息。

SELECT * FROM Orders o JOIN OrderItems oi ON o.oid = oi.oid WHERE o.custName = '张三';

4、查询各个作者(一个作者可以写多本书)的图书售量(已发货的)

SELECT b.author, SUM(oi.amounts) as salesVolume FROM Book b JOIN OrderItems oi ON b.bookId = oi.bookId JOIN Orders o ON oi.oid = o.oid WHERE o.status = 'C' GROUP BY b.author;

5、查询写过1本以上的作者。

SELECT author FROM Book GROUP BY author HAVING COUNT(*) > 1;

6、查询售量排在前5位的图书名称的作者姓名及销量。

SELECT b.bookName, b.author, SUM(oi.amounts) as salesVolume FROM Book b JOIN OrderItems oi ON b.bookId = oi.bookId JOIN Orders o ON oi.oid = o.oid WHERE o.status = 'C' GROUP BY b.bookName, b.author ORDER BY salesVolume DESC FETCH FIRST 5 ROWS ONLY