USE dbBookStore

--16.List the book code and the book title of each book that has the type of TRA, PSY or CMP.
SELECT BookCode, Title FROM tblBOOK WHERE Category IN('TRA', 'PSY', 'CMP')

--17.How many employees does the books store have?
SELECT SUM(NumberEmployees) AS [Total NUMBER OF employees IN books store] FROM tblBRANCH

--18.For each book published by Tor Books, list the book code, book title, and price.
SELECT BookCode, Title, Price FROM tblBOOK AS tbl1, tblPUBLISHER AS tbl2 WHERE (tbl2.PublisherName='Tor Books' AND tbl1.PublisherCode = tbl2.Code)

--19.List the book code, book title, and price of each book published by Tor Books that has a book price of less than $15
SELECT BookCode, Title, Price FROM tblBOOK AS tbl1, tblPUBLISHER AS tbl2 WHERE (tbl1.Price < 15 AND tbl2.PublisherName='Tor Books' AND tbl1.PublisherCode = tbl2.Code)

--20.List the number of books published every year at the existing publishers.
SELECT PublisherName, PublicationYear, COUNT(*) AS [NUMBER OF books published every YEAR BY a certain publisher] FROM tblBOOK AS tbl1, tblPUBLISHER AS tbl2 WHERE tbl1.PublisherCode=tbl2.Code
GROUP BY PublisherName,PublicationYear
ORDER BY PublicationYear,PublisherName

--21.List the book code, book title, author's name for a certain branch (name of the branch is passed as parameter)
GO
CREATE PROCEDURE GetBranchInformation @BranchName nvarchar(20)
AS
SELECT tbl1.BookCode, Title, LastNameAuthor + ' '+ FirstNameAuthor AS [Author's name] FROM tblBOOK AS tbl1, tblAUTHOR AS tbl2, tblINVENTORY AS tbl3, tblBRANCH AS tbl4  , tblWROTE AS tbl5
WHERE (tbl4.BranchID=tbl3.BranchID AND tbl3.BookCode=tbl1.BookCode AND tbl5.BookCode=tbl3.BookCode AND tbl2.AuthorID=tbl5.AuthorID AND tbl4.BranchName=@BranchName)

EXEC GetBranchInformation @BranchName='Abbey';

--22.List the number of books written by a certain author (name of the author is passed as parameter)
GO
CREATE PROCEDURE GetNumberOfBooks @Author nvarchar(30)
AS
SELECT LastNameAuthor + ' '+ FirstNameAuthor AS [Author's name],COUNT(*) AS [Books written BY a certain author] FROM tblBOOK AS tbl1, tblAUTHOR AS tbl2, tblINVENTORY AS tbl3, tblWROTE AS tbl4
WHERE(tbl3.BookCode=tbl1.BookCode AND tbl4.BookCode=tbl3.BookCode AND tbl2.AuthorID=tbl4.AuthorID AND (tbl2.LastNameAuthor + ' ' + tbl2.FirstNameAuthor = @Author))
GROUP BY LastNameAuthor,FirstNameAuthor

EXEC GetNumberOfBooks @Author='Jones Max';