CODEWITHSUNDEEP
sqlsqlite
smith
smith

Reputation: 128

SQL library database schema

The question I have is find the book ID, title, and due date of all the books currently being checked out by John Smith.

This is the schema:

Members can borrow books from the library. The number of books they can borrow is limited by the “limit” field of the Member relation (it may differ for different members). The category of a book includes fiction, non-fiction, children’s and reference. The CurrentLoan table represents the information about books that are currently checked out. When the book is returned to the library, the record will be removed from CurrentLoad relation, and will be inserted into History relation with the return-date. A library may have more than one copy of the same book, in which case each copy has its own bookID, but all copies share the same ISBN.

CREATE TABLE Book
(
    bookID INT NOT NULL,
    ISBN INT NOT NULL,
    title VARCHAR NOT NULL,
    Author CHAR,
    publish_year INT, 
    category VARCHAR,      
    PRIMARY KEY (bookID)
);

CREATE TABLE Member_
(
    memberID INT NOT NULL,
    lastname CHAR NOT NULL,
    firstname CHAR NOT NULL,
    address_ VARCHAR,    
    phone_number INT,   
    limit_ INT,

    PRIMARY KEY (memberID, lastname, firstname)
);

CREATE TABLE CurrentLoan
(
    memberID INT NOT NULL,
    bookID INT NOT NULL,
    loan_date DATE,
    due_date DATE,

    PRIMARY KEY (memberID, bookID),
    FOREIGN KEY (bookID) REFERENCES Book(bookID)
);

CREATE TABLE History
(
    memberID INT NOT NULL,
    bookID INT NOT NULL,
    loan_date DATE NOT NULL,
    return_date DATE,

    PRIMARY KEY (memberID, bookID, loan_date),
    FOREIGN KEY (bookID) REFERENCES Book(bookID),
    FOREIGN KEY (memberID) REFERENCES Member_(memberID)
);

INSERT INTO Member_ 
VALUES (1001, 'Smith', 'John', 'Captial road', 9195863230, 10);

INSERT INTO Member_ 
VALUES (2123, 'Sacks', 'Mark', '7th Black Street', 9195627774, 10);

INSERT INTO Member_ 
VALUES (3456, 'Johnson', 'Susan', '405 South', 9198564325, 10);

INSERT INTO Member_ 
VALUES (4223, 'States', 'Nick', '12th East Street', 9198889999, 10);

INSERT INTO Member_ 
VALUES (5987, 'Stew', 'Martha', '85 North', 9193642256, 10);

INSERT INTO Book 
VALUES (0001, 444222666325, 'Mars', 'Mark Sas', 2001, 'fiction');

INSERT INTO Book 
VALUES (0002, 784566512135, 'The Cow', 'Stephy Williams', 1996, 'children');

INSERT INTO Book 
VALUES (0003, 488984115444, 'XML for beginners', 'Jake Snow', 2005, 'non-fiction'); 

INSERT INTO Book 
VALUES (0004, 544465545655, 'Into Thin Air', 'Jon Krakauer', 1990, 'non-fiction'); 

INSERT INTO Book 
VALUES (0005, 878745656513, 'And Tango Makes Three', 'Peter Parnell', 1956, 'children');

INSERT INTO Book 
VALUES (0006, 564564123213, 'Swimmy', 'Leo Lionni', 2010, 'children');

INSERT INTO Book 
VALUES (0007, 132125645678, 'XML and XQuery Tutorial', 'Lee Cakes', 2014, 'non-fiction');

INSERT INTO Book 
VALUES (0008, 132154548746, 'Happy Places', 'Steve Zus', 1998, 'refrence');

INSERT INTO Book 
VALUES (0009, 788897998754, 'The Mascot', 'Kevin Bacon', 1987, 'fiction');

INSERT INTO Book 
VALUES (0010, 878561132116, 'XQuery for beginners', 'Virginia

Woolf', 2018, 'non-fiction');

INSERT INTO CurrentLoan VALUES (5987, 0004, '13-SEP-17', '14-NOV-17');
INSERT INTO CurrentLoan VALUES (2123, 0003, '13-JAN-17', '15-NOV-17');
INSERT INTO CurrentLoan VALUES (4223, 0009, '14-FEB-17', '12-MAR-17');
INSERT INTO CurrentLoan VALUES (1001, 0005, '12-OCT-17', '09-NOV-17');
INSERT INTO CurrentLoan VALUES (2123, 0002, '13-APR-17', '12-MAY-17');

INSERT INTO History VALUES (4223, 0007, '14-Jan-17', '04-OCT-17');
INSERT INTO History VALUES (3456, 0001, '12-Jan-17', '04-NOV-17');
INSERT INTO History VALUES (1001, 0003, '14-APR-17', '08-OCT-17');
INSERT INTO History VALUES (5987, 0009, '14-MAY-17', '04-DEC-17');

This is the query but it outputs all the CurrentLoan values can someone help me have it only output the values by John Smith :

SELECT 
    Book.bookID, title, due_date 
FROM 
    Book, Member_ 
INNER JOIN 
    CurrentLoan ON Book.bookID = CurrentLoan.bookID 
WHERE 
    Member_.lastname = 'Smith' AND Member_.firstname = 'John';

The question I need to answer is: find the book ID, title, and due date of all the books currently being checked out by John Smith.

Upvotes: 2

Views: 10729

Answers (1)

nimajv
nimajv

Reputation: 433

try it :


SELECT book.bookID, title, due_date 
FROM Book 
INNER JOIN CurrentLoan ON Book.bookID= CurrentLoan.bookID 
inner join Member on Member.memberID = CurrentLoan.memberID
WHERE Member.lastname='Smith' AND Member.firstname= 'John';

Upvotes: 2

Related Questions