PaP final

CREATE DATABASE Books14114034

USE Books14114034
GO
CREATE TABLE TheAuthors4
(Id int NOT NULL IDENTITY,
Name nvarchar(20) NOT NULL
Primary key(Id));
GO

CREATE TABLE TheBooks4
(Id int IDENTITY,
Name nvarchar(150) NOT NULL,
Description varchar(MAX),
AuthorId int NOT NULL FOREIGN KEY REFERENCES TheAuthors4(Id) ON UPDATE CASCADE,
Primary key(Id));
GO

CREATE TABLE TheBorrowers4(
Id int IDENTITY,
Name nvarchar(30) NOT NULL,
Phone nvarchar(30) NOT NULL,
Email nvarchar(30) NOT NULL,
Occupation nvarchar(30),
Age int,
Primary key(Id));
GO

Alter TABLE TheBorrowers4
ADD CHECK(Age >18)
GO

CREATE TABLE TheBorrowedBooks4(
Id int IDENTITY,
BookId int NOT NULL FOREIGN KEY REFERENCES TheBooks4(Id),
BorrowerId int NOT NULL FOREIGN KEY REFERENCES TheBorrowers4(Id),
DateOfBorrowing DATE DEFAULT GETDATE(),
Returned tinyInt NOT NULL DEFAULT 0,
DateOfReturning DATE,
Primary key(Id));
GO

INSERT INTO TheAuthors4(name) VALUES ('Ivan Vazov'),('Isaac Asimov'),('Terry Pratchet'),('Howard Lovecraft'),('Harley Stevens'),('Franz Kafka'),('Geo Milev');
GO

INSERT INTO TheBooks4(Name,AuthorId,Description) VALUES
('Under the yoke',1,'The very first Bulgarian novel')
,('The Solar System and Back ',2,'A collection of science essays')
,('The Stars in their Courses',2,'A collection of seventeen scientific essays')
,('Spell it out',5, 'A dark comedy involving little wizard girls, psychic warfare, insanity and nihilism'),
('Halberts and katanas',5,'Collection of fun little short stories'),
('The Castle',6,'Alienation, unresponsive bureaucracy, the frustration of trying to conduct business with non-transparent, seemingly arbitrary controlling systems, and the futile pursuit of an unobtainable goal')
,('A Little Expressionist Calendar for the Year 1921',7,'Every month has its poem')
,('At the mountains of madness',4, 'The forzen deserts have never been so horyfing');
GO

UPDATE TheBooks4 SET Description = 'The colour of magic' Where id = 8;
GO

SELECT TheBooks4.Name,TheAuthors4.Name FROM TheBooks4 LEFT JOIN TheAuthors4 ON TheAuthors4.Id = TheBooks4.AuthorId
GO

SELECT Name FROM TheAuthors4
GO

INSERT INTO TheBorrowers4(Name,Phone,Email,Occupation,Age)
VALUES ('John Lennon','0895666131','j_lennon.vr@gmail.com','musician',21),
 ('Wolfgang Mozart','0895535525','gang_of_wolves@gmail.com','composer',25),
 ('Leslie Claypool','0895999622','primussucks@gmail.com','wine entrepenuer',35),
  ('Jack White','0895656131','jackieIIIwhite@gmail.com','professional willie wonka',40),
   ('Ian Kilmister','0895199622','lemmy_killmaster@abv.com','rockstar',19)
GO
UPDATE TheBooks4 SET AuthorId = 3 Where id = 5
GO
SELECT Name,Phone FROM TheBorrowers4
GO
SELECT Phone FROM TheBorrowers4 WHERE Name = 'Wolfgang Mozart';
GO
INSERT INTO TheBorrowedBooks4(BookId, BorrowerId) VALUES
(1,2),(2,3),(3,3),(4,4),(5,5),(7,5)
GO
UPDATE TheBorrowedBooks4 SET Returned = 1 WHERE BookId = 4
GO
UPDATE TheBorrowedBooks4 SET DateOfReturning = '2016-12-05' WHERE BookId =4
GO
UPDATE TheBorrowedBooks4 SET Returned = 1 WHERE BookId = 5
GO
UPDATE TheBorrowedBooks4 SET DateOfReturning = '2016-12-18' WHERE BookId =5
GO

SELECT * FROM TheBorrowedBooks4

SELECT TheBooks4.Name,TheBorrowedBooks4.DateOfReturning FROM TheBooks4 LEFT JOIN TheBorrowedBooks4 ON TheBooks4.Id = TheBorrowedBooks4.BookId
WHERE Returned = 1
GO

DELETE FROM TheBooks4 WHERE Name = 'The colour of magic'
GO
SELECT COUNT(*) FROM TheBorrowedBooks4 Where Returned = 0
GO



SELECT B.Name,BOR.Name, BOR.Phone, BB.DateOfBorrowing, BB.DateOfReturning, DateDiff(DAY,BB.DateOfBorrowing,BB.DateOfReturning) AS NumberOfDays FROM TheBorrowedBooks4 as BB
INNER JOIN TheBooks4 as B ON B.Id = BB.BookId
INNER JOIN TheBorrowers4 as Bor ON Bor.Id = BB.BorrowerId
WHERE DateOfReturning IS NOT NULL

SELECT AVG(DateDiff(DAY,BB.DateOfBorrowing,BB.DateOfReturning)) AS NumberOfDays FROM TheBorrowedBooks4 as BB
WHERE DateOfReturning IS NOT NULL


SELECT TheBooks4.Name,TheAuthors4.Name FROM TheBooks4 LEFT JOIN TheAuthors4 ON TheAuthors4.Id=TheBooks4.AuthorId
GROUP BY TheAuthors4.Name, TheBooks4.Name
GO