USE BookShopDB

GO
--Create view to give authors and their books
DROP VIEW BookAuthorView
GO
CREATE VIEW BookAuthorView
AS
SELECT a.FirstName, a.LastName, b.Title 
FROM Authors a JOIN BookAuthors ba
  ON a.AuthorID = ba.AuthorID
  JOIN Books b
  ON ba.TitleID = b.TitleID
GO

--Modify the view to include the TitleID
ALTER VIEW BookAuthorView
AS
SELECT  b.Title, a.FirstName, a.LastName, b.TitleID
FROM Authors a JOIN BookAuthors ba
  ON a.AuthorID = ba.AuthorID
  JOIN Books b
  ON ba.TitleID = b.TitleID

--Use view
SELECT * FROM BookAuthorView

--Delete the view
DROP VIEW BookAuthorView