
示例7-2
 SELECT COUNT(bookid) 图书数量,SUM(price) 图书总价值,SUM(Price*0.8) 购书总金额  FROM BookInfo
WHERE YEAR(PublishDate)='2006'

示例7-6
SELECT MAX(Quantity) 销售数量,BookID 
FROM SalesDetails  
GROUP BY BookID 
ORDER BY 销售数量  DESC

示例7-7
 SELECT COUNT(*) 'Total person',DATEPART(YYYY,hiredate)  '年份'
FROM UserInfo 
GROUP BY   DATEPART(YYYY,hiredate)

示例7-8
SELECT COUNT(*) 'Total person',DATEPART(YYYY,hiredate) '年份'
FROM UserInfo 
GROUP BY   DATEPART(YYYY,hiredate)
HAVING   DATEPART(YYYY,hiredate)='2004'

示例7-10
SELECT  A.BookID 书号,A.BookName 书名 ,B.CategoryName 类别,
C.PublisherName  出版社
FROM  BookInfo  A ,Category  B ,Publisher C
WHERE A.CategoryID =B.CategoryID  AND A.PublisherID =C.PublisherID

示例7-11
SELECT  a.SalesDate,SUM(b.Quantity*b.Discount*b.Price) 销售总金额 
FROM SalesMaster a Inner Join SalesDetails b
ON a.SalesMasterID=b.SalesMasterID  
GROUP BY a.SalesDate 
HAVING  DATEPART(yyyy,a.SalesDate)='2008'
ORDER BY 销售总金额  DESC 



示例7-12
SELECT a.SalesDate,a.UserID,c.UserName,c.Addr,b.Bookid 
FROM SalesMaster a  INNER JOIN SalesDetails b ON
a.SalesMasterID=b.SalesMasterID INNER JOIN UserInfo c  ON a.UserID=c.UserID 
WHERE c.Addr  like '%广州%' ORDER BY b.bookID 

示例7-13
SELECT a.*,b.SalesDate 
 FROM UserInfo a LEFT JOIN SalesMaster b ON a.UserID=b.UserID 
ORDER BY SalesDate

示例7-14
SELECT a.PublisherID,a.PublisherName,b.* 
 FROM Publisher a  LEFT JOIN  BookInfo b 
ON A.PublisherID =B.PublisherID  
 WHERE B.BookID IS NULL  
 
 示例7-14
 SELECT a.bookid 书号,a.bookName 书名 ,a.publishdate 出版日期,
  a.price 价格a,b.price 价格b
FROM BookInfo  a  INNER JOIN BookInfo b ON a.Price >b.Price
WHERE  b.BookID ='B0002'

