1. Create a new table to track the Library location. - Screen shot not required,
ID: 3919987 • Letter: 1
Question
1. Create a new table to track the Library location. - Screen shot not required, please paste the SQL statement to create the table. (5 pts) LIBRARY (lib id, lib_name, lib address, lib_city, lib state, lib zip) LIB_ID is the primary key and should be numeric. LIB_NAME, LIB_ADDRESS, and LIB_CITY is between 1 and 35 characters. -These should not be null LIB_STATE is 2 characters- default to TX. LIB_ZIP is 5 numbers. Check for one of the following zip codes- 75081, 75080, 75082 75079, 75078 CREATE TABLE LIBRARY LIB_ID YARCHAR(4) PRIMARY KEY LIB NAME VARCHAR(35) NOT NULL, LIB_ADDRESS VARCHAR(35) NOT NULL LIB_CITY VARCHAR(35) NOT NULL LIB_STATE VARCHARI2) DEFAULT 'TX' NOT NULL LIB_ZIP NUMBER(5) NOT NULL CHECK(LIB_ZIP IN (75081', 75080','75082','75079''75078') CREATE SEQUENCE LIB_ID_SEQ MINVALUE 1000 START WITH 1001 INCREMENT BY 1 2. Insert the following records into the LIBRARY table - Show each SQL statement and then provide a screen shot listing all the rows in the table. (5 pts) 1000, JFK Library, 800 West Campbell Road, Richardson, 75080 1001, MLK Library, 105 King Blvd., Richardson, TX, 75081 1002, Hoover Library, 932 Arapaho St., Richardson, TX, 75080 INSERT INTO LIBRARY (lib_id, lib_name, lib_address, lib_city, lib state, lib..zip) VALUES ('1000', 'JFK Library', '800 West Campbell Road','Richardson', 'TX', '75080'); INSERT INTO LIBRARY (lib_id, lib_name, lib_address, lib_city, lib state, lib..zip) VALUES ('1001', 'MLK Library', '105 King Blvd.', 'Richardson', 'TX', '75081'); INSERT INTO LIBRARY (lib_id, lib_name, lib_address, lib_city, lib state, lib..zip) VALUES ('1002', 'Hoover Library', '932 Arapaho St.', 'Richardson', TX', '75080);Explanation / Answer
8) UPDATE PATRON SET PAT_TYPE = UPPER(PAT_TYPE);
12) To Select: You wrote it correct
SELECT B.BOOK_NUM, B.BOOK_TITLE, B.BOOK_SUBJECT, C.CHECK_OUT_DATE
FROM BOOK B FULL JOIN CHECKOUT C
ON C.BOOK_NUM = B.BOOK_NUM
WHERE C.CHECK_OUT_DATE IS NULL
To Delete: You need to delete from the SubQuery which selects those book numbers which have never been checked out. So we are deleting from book using your subquery to select.
DELETE FROM BOOK WHERE BOOK_NUM IN
( SELECT B.BOOK_NUM BOOK_NUM
FROM BOOK B FULL JOIN CHECKOUT C
ON C.BOOK_NUM = B.BOOK_NUM
WHERE C.CHECK_OUT_DATE IS NULL)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.