Question

SQL Command - Create a view called S LIST that lists the Cardholder Number, last name, first name and due date for all cardholders who have not returned a book. Use the CREATE OR REPLACE VIEW AS ... command. TABLE CARD HOLDERS Cardholder Numberint NOT NULL CONSTRAINT CH PK PRIMARY KEY, First_Name varchar(10) NULL, LastName varchar(15) NULL, Address varchar(20) NULL, varchar(15) NULL, State char(2) NULL, Zip_Code char(5) NULL) City TABLE BOOKS CHECKED OUT Cardholder_Numberint NOT NULL, Book Numberint NOT NULL, Date Checked Out Date default sysdate NOT NULL, Due Date Date NOT NULL, Actual Return Date Date NULL, CONSTRAINT BCO PK PRIMARY KEY (Cardholder Number, Book Number. Date_Checked_out), CONSTRAINT BCO_FK1 FOREIGN KEY (Cardholder Number) REFERENCES CARD HOLDERS(Cardholder Number), CONSTRAINT BCO FK2 FOREIGN KEY (Book Number) REFERENCES BOOK LIST(Book_Number))

Answer 1

Answer:

CREATE OR REPLACE VIEW S_LIST AS
SELECT ch.Cardholder_Number,ch.Last_Name,ch.First_Name,bco.Due_Date from CARD_HOLDERS ch
join BOOKS_CHECKED_OUT bco on bco.Cardholder_Number = ch.Cardholder_Number
where bco.Actual_Return_Date IS NULL order by ch.Cardholder_Number;

/*The above query will list down the Cardholder_Number, Last Name , First Name and Due Date for all card holders who has returned a book.*/

Let me know in case if you have any doubts. Thanks and all the best.