USE LearningBookstore2012;GO-- 应为Book6、Category3、Sale4;练习临时表不会改变这些值SELECT N'Book' AS [表],COUNT(*) AS [行数] FROM dbo.BookUNION ALL SELECT N'Category',COUNT(*) FROM dbo.CategoryUNION ALL SELECT N'Sale',COUNT(*) FROM dbo.Sale;
2 第一轮 只做单表
先不用答案,自己在空白窗口完成:
查询库存已知且至少10本的图书,只显示书名和库存。
查询价格最高的两本,并让价格相同的按编号升序。
统计未知库存有几种、已知库存总共多少本。
按作者统计图书种数,只看已知作者中至少2种的。
用CASE输出未知、缺货、有货三种状态。
第一轮答案
SELECT Title,Stock FROM dbo.Book WHERE Stock>=10; -- SQL、Python、唐诗SELECT TOP(2) Title,Price FROM dbo.Book ORDER BY Price DESC,BookId;SELECT COUNT(*) AS [未知种数] FROM dbo.Book WHERE Stock IS NULL; -- 1SELECT SUM(Stock) AS [已知总本数] FROM dbo.Book; -- 55SELECT Author,COUNT(*) AS [种数] FROM dbo.BookWHERE Author IS NOT NULL GROUP BY Author HAVING COUNT(*)>=2; -- 林青2SELECT Title,CASE WHEN Stock IS NULL THEN N'未知' WHEN Stock=0 THEN N'缺货' ELSE N'有货' END AS [状态]FROM dbo.Book;
3 第二轮 组合多张表
显示全部图书与分类,没有分类也要出现。
统计每种分类的图书种数,包括没有图书的科普。
显示每本书的累计售出本数,没卖过显示0。
用NOT EXISTS查从未售出的书。
用INTERSECT查既卖过又价格至少60的图书编号。
第二轮答案
SELECT b.Title,c.CategoryName FROM dbo.Book bLEFT JOIN dbo.Category c ON b.CategoryId=c.CategoryId;-- 数BookId而不是COUNT(*),无人归类的科普才会得到0SELECT c.CategoryName,COUNT(b.BookId) AS [图书种数]FROM dbo.Category c LEFT JOIN dbo.Book b ON c.CategoryId=b.CategoryIdGROUP BY c.CategoryId,c.CategoryName;-- 计算机3、文学2、科普0;未分类书不属于任何已列分类SELECT b.Title,ISNULL(SUM(s.Quantity),0) AS [售出本数]FROM dbo.Book b LEFT JOIN dbo.Sale s ON b.BookId=s.BookIdGROUP BY b.BookId,b.Title ORDER BY b.BookId; -- 3、3、0、2、0、0SELECT b.Title FROM dbo.Book bWHERE NOT EXISTS(SELECT 1 FROM dbo.Sale s WHERE s.BookId=b.BookId);SELECT BookId FROM dbo.SaleINTERSECTSELECT BookId FROM dbo.Book WHERE Price>=60; -- 2
GOCREATE PROCEDURE dbo.P_BooksInCategory @categoryId intASBEGIN SET NOCOUNT ON; SELECT BookId,Title,Price FROM dbo.Book WHERE CategoryId=@categoryId ORDER BY BookId;END;GOEXEC dbo.P_BooksInCategory @categoryId=1; -- 3本计算机书DROP PROCEDURE dbo.P_BooksInCategory; -- 用完只清理本次新建对象