Develop a SQL query that will select all of the books that are currently checked
ID: 3920461 • Letter: D
Question
Develop a SQL query that will select all of the books that are currently checked out and are past due to be returned, which have a value greater than $10.00. To find out if a loaned book is past due, you must compare the return date with the current date. Your query must return the name, address, postal code, and phone number of the borrower, the name and phone number of the librarian responsible for the transaction, the date the book was due to be returned, and the name, cost, and ISBN number of the book. You query should sort the list by the return date in descending order which is the oldest date should appear first in the list. You must submit both your SQL query and the output of executing your query against your database. Assignment Instructions: the assignment must include the SQL DML statements required to implement at least the following relations: o Book o Borrower o BookLended o Librarian the assignment must make appropriate use of Null (and Not Null) parameters to ensure data validity. The minimum standard will be measured against the instructor's example and will be based upon rules associated with primary and foreign keys and integrity constraint rules. the assignment must make appropriate use of constraint clauses to ensure the referential integrity of the relations in the schema. The minimum standard will be measured against the instructor's example and will be based upon rules associated with primary and foreign keys and integrity constraint rules. the assignment must make appropriate use of keys including the automatic generation of key values where appropriate.Explanation / Answer
CREATE TABLE Book (ISBN_number INT PRIMARY KEY,
Title VARCHAR(40) NOT NULL,Author VARCHAR(20) NOT NULL,Cost DECIMAL(10,2));
CREATE TABLE Borrower (LibraryCard int PRIMARY KEY AUTO_INCREMENT,Name VARCHAR(20) NOT NULL,Address VARCHAR (30) NOT NULL,
PostalCode INT NOT NULL,PhoneNumber BIGINT,MembershipDate DATE);
CREATE TABLE Librarian (Librarianid INT PRIMARY KEY,Name VARCHAR(20) NOT NULL,Phone BIGINT,Supervisor INT);
CREATE TABLE BookLedner (Borrower INT,CheckOutDate Date,ReturnDate Date,ISBN_number INT,LibrarianId INT,
FOREIGN KEY (Borrower) REFERENCES Borrower(LibraryCard),
FOREIGN KEY (ISBN_number) REFERENCES Book(ISBN_number),
FOREIGN KEY (LibrarianId) REFERENCES Librarian(LibrarianId));
SELECT b.Name,b.Address,b.PostalCode,b.PhoneNumber,l.Name AS librarianName, l.Phone AS librarianPhone,
bo.ISBN_number,bo.Title,,bo.cost FROM Book bo INNER JOIN BookLedner bl ON BO.ISBN_number=bl.ISBN_number
INNER JOIN Librarian l ON bl.LibrarianId=l.LibrarianId
INNER JOIN Borrower b on bl.Borrower=b.LibraryCard
WHERE bl.ReturnDate < GETDATE()
ORDER bY ReturnDate DEsc;
INSERT INTO Book VALUES (1441438,'Alice in Wonderland','Lewis Carroll',7.95)
INSERT INTO Book VALUES (6006374,'A First Course in Database Systems (3rd ed.)','Jeffrey Ullman',99.49)
INSERT INTO Book VALUES (3523323,'Database System Concepts','Abraham Silberschatz',119.67)
INSERT INTO Book VALUES (1429477,'Grimm’s Fairy Tales','Jacob Grimm',26.99)
INSERT INTO Book VALUES (1486025,'A Tale of Two Cities','Charles Dickens',7.95)
INSERT INTO Book VALUES (1853602,'War and Peace','Leo Tolstoy',7.99)
INSERT INTO Book VALUES (1904129,'The Scarlet letter','Nathaniel Hawthorne',7.95)
INSERT INTO Book VALUES (1593832,'Pride and Prejudice','Jane Austen',7.95)
INSERT INTO Book VALUES (1538243,'Pride and Prejudice','Jane Austen',7.95)
INSERT INTO Borrower VALUES (1,'Samil Shah','123 Home st',62989,5551212'39479')
INSERT INTO Borrower VALUES (2,'Tim Jones','3435 Main st.',54232,5552934'40737')
INSERT INTO Borrower VALUES (3,'Sue Smith','2176 Baker st.',43542,5556723'38482')
INSERT INTO Borrower VALUES (4,'Jeff Bridges','176 Right st.',28460,5551745'40349')
INSERT INTO Borrower VALUES (5,'Steve Smith','435 Main St.',28454,5556565'38490')
INSERT INTO Borrower VALUES (6,'Arun Goel','34 Home St.',56234,5554889'39522')
INSERT INTO Borrower VALUES (7,'Jane Doe','65 Water St.',42358,5554581'40793')
INSERT INTO Borrower VALUES (8,'Jim Jones','23 Hill Drive',85423,5557891'40505')
INSERT INTO BookLedner VALUES (2,'40513','41567',1441438,1'1')
INSERT INTO BookLedner VALUES (5,'40513','40452',6006374,2'2')
INSERT INTO BookLedner VALUES (2,'40513','40928',3523323,1'2')
INSERT INTO BookLedner VALUES (8,'40513','42031',1429477,1'3')
INSERT INTO BookLedner VALUES (6,'40513','42186',1853602,2'4')
INSERT INTO BookLedner VALUES (3,'40513','42239',1904129,1'2')
INSERT INTO librarian VALUES (1,'Gertrude Smith','555-1212',3)
INSERT INTO librarian VALUES (2,'Mable Markham','555-1212',1)
INSERT INTO librarian VALUES (3,'Penelope Pretty','555-1212',1)
INSERT INTO librarian VALUES (4,'Olga Brown','555-2300',1)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.