Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Add Comment
Please, Sign In to add comment