--11-2
CREATE VIEW v_BookInfo
AS 
SELECT      BookID, BookName, 
            Author, ISBN, 
            PublishDate, 
            Price,
            [Content], 
            CategoryName, PublisherName
FROM        BookInfo a INNER JOIN Category b
            ON a.CategoryID = b.CategoryID
            INNER JOIN  Publisher  c
            ON a.PublisherID = c.PublisherID
WHERE     (c.PublisherName = '人民邮电出版社') AND (Price > 20)
--11-3
CREATE VIEW v_SalesInfo
AS 
SELECT d.UserName 用户,a.SalesDate 销售日期,
       c.BookName 书名 ,b.Quantity 数量,
       b.Price 价格, b.Discount 折扣,
       b.Price*b.Discount 销售价格       
FROM SalesMaster a INNER JOIN SalesDetails b
     ON a.SalesMasterID=b.SalesMasterID
     INNER JOIN BookInfo c
     ON b.BookID=c.BookID
     INNER JOIN UserInfo d
     ON a.UserID=d.UserID
12-5

USE BookShopDb
GO
/*-- 检查是否存在该索引（索引存放在系统表sysindexes中)---*/
IF EXISTS(SELECT name FROM sysindexes
   WHERE name='IX_BookInfo_Author')
   DROP INDEX BookInfo.IX_BookInfo_Author --删除索引
  
 --创建索引
 CREATE NONCLUSTERED INDEX IX_BookInfo_Author
    ON BookInfo(author)
 
 --创建索引之后查询数据可以指定SQL Server查询数据的方式
 SELECT * FROM BookInfo
 WITH (INDEX=IX_BookInfo_Author)
 WHERE Author LIKE '王%'



