第6课数据查询.pptVIP

返回 例6-16 显示所有图书的书号、书名、单价和类别代号，首先按照类别代号升序排序，对于类别代号相同的图书再并按照图书单价降排序。 SELECT bookID, title, unitPrice, categoryCode FROM Books ORDER BY categoryCode, unitPrice DESC GO 返回 例6-17 显示所有图书的书号、书名、单价和类别代号，并按照单价降序排序。 SELECT bookID, title, unitPrice, categoryCode FROM Books ORDER BY 3 DESC GO 返回 例6-18 显示所有图书的书号、书名和类别代号，并按照单价降序排序。 SELECT bookID, title, categoryCode FROM Books ORDER BY unitPrice DESC GO 返回 例6-19 显示所有图书的书号、书名和打8折的折扣价，并按照折扣价升序排序。 SELECT bookID, title, salePrice=unitPrice*0.8 FROM Books ORDER BY salePrice GO 返回 例6-20 统计有多少图书的价格高于30元。 SELECT COUNT(*) AS 图书册数 FROM Books WHERE unitPrice 30 GO 也可以写为如下形式： SELECT COUNT(bookID) AS 图书册数 FROM Books WHERE unitPrice 30 GO 返回 例6-21 在Books表中统计有多少个类别代号。 SELECT COUNT(categoryCode) AS 类别数 FROM Books GO 语句应该写为如下形式： SELECT COUNT(DISTINCT categoryCode) AS 类别数 FROM Books GO 返回 例6-22 统计类别编号是CO01的图书册数和单价总和。 SELECT COUNT(bookID) AS 图书册数, SUM(unitPrice) AS 单价总和 FROM Books WHERE categoryCode=CO01 GO 返回 例6-23 统计所有图书的最高单价、最低单价和平均单价。 SELECT MAX(unitPrice) AS 最高单价, MIN(unitPrice) AS 最低单价, AVG(unitPrice) AS 平均单价 FROM Books GO 返回 例6-24 统计每本图书的销售数量总和。 SELECT SUM(quantity) AS 销售总册数 FROM OrderItems GROUP BY bookID GO 将例6-24中查询语句修改为如下形式。 SELECT bookID, SUM(quantity) AS 销售总册数 FROM OrderItems GROUP BY bookID GO 查询结果如图6-20所示，这样用户就可以清楚地看出每本书的销售总量分别是多少。 返回 例6-25 统计销售次数超过1次的图书的书号和销售总册数。 SELECT bookID, SUM(quantity) AS 销售总册数 FROM OrderItems GROUP BY bookID HAVING COUNT(orderID)3 GO 返回 例6-25 统计单笔销售数量达到或超过10本的图书的书号和销售总册数。 SELECT bookID, SUM(quantity) AS 销售总册数 FROM OrderItems GROUP BY bookID HAVING MAX(quantity)=10 GO 返回 例6-27 显示所有图书的书号、书名、单价和类别代号，最后显示所有图书的总价。 SELECT bookID,title,unitPrice,categoryCode FROM Books ORDER BY categoryCode COMPUTE SUM(unitPrice) GO 返回 例6-28显示所有图书的书号、书名、单价和类别代号，并显示每类图书的总价。 SELECT bookID,title,unitPrice,categoryCode FROM Books ORDER BY categoryCode COMPUTE SUM(unitPrice) BY categoryCode GO 返回 例6-29 连接Books表和Categories表，显示图书编号、书名、单价以及类别编号和类别名称。 SELECT b.bookID, b.title, b.unitPrice, c.categoryCode, c.categoryName FRO