He says that I do not have a highlighted line.
This is a question: Find the participant ID, last name and first name of members who have never occupied any books in the past or present.
This is a diagram. Primary keys are in bold.
Book ( bookID , ISBN, title, author, publication-year, category)
Member ( memberID , last name, first name, address, phone number, limit)
CurrentLoan ( memberID , bookID , loan date, due date)
History ( memberID , bookID , loan date, return date)
Members can borrow books from the library. The number of books that they can borrow is limited by the "limit" field of the Member relationship (this may vary for different participants). The book category includes fiction, fiction, children's and reference materials. The CurrentLoan table provides information on currently verified books. When the book is returned to the library, the record will be deleted from the CurrentLoad relationship and inserted into the History relationship with the return date. A library can have more than one copy of the same book, in which case each copy has its own bookID, but all copies have the same ISBN.
This is my code:
CREATE TABLE Book
(bookID INT,
ISBN INT,
title varchar (25),
author varchar (20),
publish_year INT,
category varchar(20),
PRIMARY KEY (bookID));
CREATE TABLE Member
(memberID INT,
lastname varchar (20),
firstname varchar (20),
address varchar(20),
phone_number INT,
limit_ INT,
PRIMARY KEY (memberID));
CREATE TABLE CurrentLoan
(memberID INT ,
bookID INT,
loan_date DATE,
due_date DATE,
PRIMARY KEY (memberID, bookID),
FOREIGN KEY (memberID) REFERENCES Member(memberID),
FOREIGN KEY (bookID) REFERENCES Book(bookID));
CREATE TABLE History
(memberID INT,
bookID INT,
loan_date DATE,
return_date DATE,
PRIMARY KEY (memberID, bookID, loan_date),
FOREIGN KEY (memberID) REFERENCES Member(memberID),
FOREIGN KEY (bookID) REFERENCES Book(bookID));
INSERT INTO Book VALUES (10, 1113312336, 'The Dog', 'Jack Crow', 1990, 'fiction');
INSERT INTO Book VALUES (12, 2221254896, 'Worms', 'Jim Kan', 2013, 'childrens');
INSERT INTO Book VALUES (13, 3332546987, 'Crow', 'Jan Flo', 2000, 'fiction');
INSERT INTO Book VALUES (14, 4443456215, 'Big Dog', 'Lan Big', 1993, 'children');
INSERT INTO Book VALUES (15, 5552314569, 'Green Apple', 'Theo Brown', 1978, 'children');
INSERT INTO Book VALUES (16, 6664581631, 'Red Bean', 'Khang Nk', 2017, 'fiction');
INSERT INTO Book VALUES (17, 7771452369, 'XML and XQuery Knowledge', 'Author Le', 2017, 'non-fiction');
INSERT INTO Book VALUES (18, 8881245525, 'The Dark Room', 'Jack Se', 2017, 'fiction');
INSERT INTO Book VALUES (19, 9991123546, 'Lonely Mens', 'Geen Brown', 2014, 'refrence');
INSERT INTO Book VALUES (20, 1122112356, 'XML or XQuery', 'Heart Le', 2002, 'fiction');
INSERT INTO Member VALUES (001, 'Lee', 'Nancy', 'Brownlea Drive', 1254896325, 2);
INSERT INTO Member VALUES (002, 'Le', 'Ray', '10th Street', 1234561256, 2);
INSERT INTO Member VALUES (003, 'Kan', 'Charlie', '5th Street', 1234567236, 2);
INSERT INTO Member VALUES (004, 'Brown', 'Joe', 'Elm Street', 1234567845, 2);
INSERT INTO Member VALUES (005, 'Smith', 'John', '33 East', 1234567890, 2);
INSERT INTO CurrentLoan VALUES (005, 10, '13-SEP-17', '14-NOV-17');
INSERT INTO CurrentLoan VALUES (005, 19, '13-JAN-17', '15-NOV-17');
INSERT INTO CurrentLoan VALUES (003, 16, '14-FEB-17', '12-MAR-17');
INSERT INTO CurrentLoan VALUES (004, 15, '12-OCT-17', '09-NOV-17');
INSERT INTO CurrentLoan VALUES (005, 18, '13-APR-17', '12-MAY-17');
INSERT INTO History VALUES (001, 10, '14-Jan-17', '04-OCT-17');
INSERT INTO History VALUES (002, 19, '12-Jan-17', '04-NOV-17');
INSERT INTO History VALUES (003, 13, '14-APR-17', '08-OCT-17');
INSERT INTO History VALUES (005, 20, '14-MAY-17', '04-DEC-17');
My request:
SELECT Member.memberID, lastname, firstname
FROM Member MINUS(
SELECT Member.memberID, lastname, firstname
FROM Member, CurrentLoan
WHERE Member.memberID = CurrentLoan.memberID
UNION
SELECT Member.memberID, lastname, firstname
FROM Member, History
WHERE Member.memberID = History.memberID);