Notes

CREATE DATABASE Library

Use Library

Create Table Authors(
Id INT PRIMARY KEY IDENTITY,
AuthorName NVARCHAR(50),
AuthorSurname NVARCHAR(100)
)

Create Table Books(
Id INT PRIMARY KEY IDENTITY,
BookName NVARCHAR(100) CHECK(LEN(BookName) BETWEEN 2 AND 100),
AuthorId INT REFERENCES Authors(Id),
PageCount INT CHECK(PageCount > 9)
)

--1--
Create View GetInfo
As
Select b.BookName , b.PageCount , (a.AuthorName + ' ' + a.AuthorSurname) As 'Fullname Author' From Authors As a
Join Books As b
On b.AuthorId = a.Id

Select * From GetInfo

--2--
Alter Procedure usp_BookPageCount @count int
As
Select b.BookName , b.PageCount From Books As b
Where b.PageCount > @count

exec usp_BookPageCount 300


--3--
Alter Procedure usp_SearchByNameOrFullnameAuthor @name nvarchar(100)
As
Select b.BookName , b.PageCount , (a.AuthorName + ' ' + a.AuthorSurname) As 'Fullname Author' From Books As b
Join Authors As a
On b.AuthorId = a.Id
Where b.BookName Like '%'+@name+'%' or a.AuthorName Like '%'+@name+'%' or a.AuthorSurname Like '%'+@name+'%'

exec usp_SearchByNameOrFullnameAuthor 'Three'


--4--
Create View GetBooks
As
Select b.BookName , (a.AuthorName + ' ' + a.AuthorSurname) As 'Fullname Author', b.PageCount From Books As b
Join Authors As a
On b.AuthorId = a.Id

Select * From GetBooks

-------5------
--Procedures--
Create Procedure usp_bookInsert @bookName nvarchar(100) , @authorId int , @pageCount int
As
Begin
Insert Into Books
Values
(@bookName , @authorId , @pageCount)
End

Create Procedure usp_bookDelete @id int
As
Begin
Delete From Books
Where Books.Id = @id
End

Create Procedure usp_bookUpdate @bookName nvarchar(100), @id int
As
Begin
Update Books
Set Books.BookName = @bookName
Where Books.Id = @id
End

--Triggers--
Alter Trigger AfterOperation
On Books
After Insert , update, delete
As
Select * from GetBooks

usp_bookInsert 'Death on the Nile' , 2 , 450



--Create Trigger AfterOperation2
--On Books
--After Update
--As
--Select b.BookName , (a.AuthorName + ' ' + a.AuthorSurname) As 'Fullname Author', b.PageCount From Books As b
--Join Authors As a
--On b.AuthorId = a.Id

--Create Trigger AfterOperation3
--On Books
--After Delete
--As
--Select b.BookName , (a.AuthorName + ' ' + a.AuthorSurname) As 'Fullname Author', b.PageCount From Books As b
--Join Authors As a
--On b.AuthorId = a.Id


exec usp_bookDelete 6

--6--

Create View AuthorInfo2
As
Select DISTINCT a.Id , (a.AuthorName + ' ' + a.AuthorSurname) As 'Fullname Author',
(Select COUNT(*) From Books As b Where b.AuthorId = a.Id ) As 'Books Count',
(Select Max(PageCount) From Books As b Where b.AuthorId = a.Id ) As 'Max Page Count'
From Authors As a


Select * From AuthorInfo2