Using the PSQL function in a linux terminal solve.

PART2

Provide solutions with screenshots please show everything. I will like pls solve

Heres a txt file that you can use for test data(populate_psql.txt) In order to run the script you can just cut and paste it into a psql command session, or run it with the \i command.

CREATE TABLE CUSTOMER ( CID INTEGER PRIMARY KEY, NAME VARCHAR(60), ADDR VARCHAR(160), PHONE CHAR(10) ); CREATE TABLE BOOK ( ISBN CHAR(17) PRIMARY KEY, TITLE VARCHAR(255), AUTHOR VARCHAR(120), QTY_IN_STOCK INTEGER, PRICE REAL ); CREATE TABLE ORDERS ( ORDERNUM INTEGER PRIMARY KEY, CID INTEGER NOT NULL, ORDER_DATE DATE NOT NULL, CCARD CHAR(16) NOT NULL, FOREIGN KEY (CID) REFERENCES CUSTOMER(CID) ON UPDATE CASCADE ); CREATE TABLE ORDERLIST ( ORDERNUM INTEGER NOT NULL, ISBN CHAR(17) NOT NULL, QTY INTEGER NOT NULL, SHIP_DATE DATE, PRIMARY KEY (ORDERNUM, ISBN), FOREIGN KEY (ISBN) REFERENCES BOOK(ISBN) ON UPDATE CASCADE, FOREIGN KEY (ORDERNUM) REFERENCES ORDERS(ORDERNUM) ON DELETE CASCADE ON UPDATE CASCADE ); INSERT INTO CUSTOMER VALUES (101, 'John Doe', '123 Nowhere Lane', '7895551234'); INSERT INTO CUSTOMER VALUES (102, 'Jane Doe', '123 Nowhere Lane', '1235554567'); INSERT INTO CUSTOMER VALUES (103, 'Solo', '99 Galaxy Way', '5555555555'); INSERT INTO BOOK VALUES ('0000136006329', 'Operating Systems: Internals and Design Principles', 'Stallings, William', 1, 89.99); INSERT INTO BOOK VALUES ('0000763717614', 'Fundamentals of Computer Science using Java', 'Hughes, David', 0, 49.99); INSERT INTO BOOK VALUES ('9780072465631', 'Database Management Systems; 3rd ed.', 'Ramakrishnan, Gehrke', 1, 99.99); INSERT INTO BOOK VALUES ('9780136006633', 'Modern Operating Systems; 3rd ed.', 'Tanenbaum, Andrew', 2, 109.99); INSERT INTO BOOK VALUES ('0000895775522', 'The Further Adventures of Sherlock Holmes', 'Arthur Conan Doyle', 10, 30.99); INSERT INTO BOOK VALUES ('8493948584832', 'Star Wars - Episode 1', 'Lucas, George', 1, 67.44); INSERT INTO BOOK VALUES ('8392938392938', 'Star Wars - Episode 2', 'Lucas, George', 4, 57.33); INSERT INTO BOOK VALUES ('8594930494030', 'Star Wars - Episode 3', 'Lucas, George', 2, 65); INSERT INTO BOOK VALUES ('0000345453395', 'Star Wars Trilogy, Episodes IV, V, VI', 'Lucas, George', 0, 45.2); INSERT INTO BOOK VALUES ('0000064471063', 'The Horse and His Boy', 'C.S. Lewis', 5, 5.99); INSERT INTO BOOK VALUES ('0000064471047', 'The Lion, the Witch and the Wardrobe', 'C.S. Lewis', 5, 5.99); BEGIN; INSERT INTO ORDERS VALUES (123, 101, '2011-05-01', '4505123412344321'); INSERT INTO ORDERLIST VALUES (123, '9780072465631', 1, '2011-05-02'); INSERT INTO ORDERLIST VALUES (123, '9780136006633', 1, null); COMMIT; BEGIN; INSERT INTO ORDERS VALUES (124, 102, '2011-05-01', '4505444566778899'); INSERT INTO ORDERLIST VALUES (124, '0000763717614', 1, '2011-05-02'); COMMIT; BEGIN; INSERT INTO ORDERS VALUES (125, 101, '2011-05-11', '4505123412344321'); INSERT INTO ORDERLIST VALUES (125, '0000763717614', 1, null); COMMIT; BEGIN; INSERT INTO ORDERS VALUES (130, 103, '2012-02-11', '4505123412344322'); INSERT INTO ORDERLIST VALUES (130, '0000895775522', 11, '2012-02-11'); COMMIT; BEGIN; INSERT INTO ORDERS VALUES (1111, 102, '2012-01-01', '9999991234567890'); INSERT INTO ORDERLIST VALUES (1111, '8493948584832', 2, NULL); COMMIT; BEGIN; INSERT INTO ORDERS VALUES (1234, 101, '2013-02-28', '4505123412344322'); INSERT INTO ORDERLIST VALUES (1234, '0000064471063', 1, '1892-03-01'); INSERT INTO ORDERLIST VALUES (1234, '0000064471047', 2, NULL); COMMIT;

Part 2: SQL Queries The queries in this section will be based on the following relational schema: Customer(CID, Name, Address, Phone) Orders (ordernum, CID, Order_date, Cardnum) Orderlist(ordernum, ISBN, Qty, Ship_date) Book(ISBN, Title, Author, Qty_in_stock, Price) When an order is placed, there is one entry added to Orders which contains the customer information and order details. Then there is one entry per book entered into Orderlist with "NULL" in the ship date, which will be replaced once that line item has been shipped. Each book item will only be shipped when all the copies are present (ie: we have sufficient Qty_in_stock for the Qty of the order, that way there is a unique ship_date for each line item). 8. Write a SQL query which will list the Ordernum, title and qty of books that are still waiting to be shipped for orders that have already been placed. 9. Write a SQL query which will list the CID, name and phone number of any customer who has not placed an order. 10. Write a SQL query which will list the name of any customer who has ordered both a book written by "J.R.R. Tolkien" and "C.S. Lewis". 11. Write a SQL query which will list the ISBN, Author, Title and total number of copies ordered, for every Book in our catalogue (even if there were no copies ordered). 12. Write a SQL query which will list the Customer Name, Ordernum and total Order cost for any order which contains 3 or more different books. 13. Write a SQL query which will list the CID and name of all the customers who have ordered every book with a title begining with "Star Wars" and that we have in stock. 14. Write a SQL query which will list the ISBN and number of book copies that we would need to receive so that we can complete all of the orders which have not been fully shipped. (consider using views to get the intermediate information which you will need)