patron patron id 12A7 23U9 64J3 12H34 25V6 84Y8 book isbn 11353 11958 11789 11005 65345...

Transcribed Image Text:

patron patron id 12A7 23U9 64J3 12H34 25V6 84Y8 book isbn 11353 11958 11789 11005 65345 89434 43498 44983 patron_name Juliet Beatrice Falstaff Claudius Miranda Puck age 21 14 87 city Dallas Houston Baton Rouge Dallas 73 31 Shreveport Dallas 48 title War and Peace Great Expectations The Hobbit Don Quixote The Count of Monte Cristo The Lord of the Rings Moby Dick The Brothers Karamazov publisher Penguin Penguin Random House RAE Oxford Random House Oxford Oxford state TX TX LA TX LA TX phone 972-7823562 713-5689420 225-5387460 972-6597534 318-9837547 972-1764390 num_copies 5 7 10 4 3 18 5 2 pages 1225 328 300 928 1276 1178 378 840 69939 author author name Miguel de Cervantes J. R. R. Tolkien Charles Dickens Herman Melville Fyodor Dostoevsky borrows isbn A Christmas Carol 11353 44983 11789 65345 65345 11005 43498 44983 65345 patron id 12A7 12A7 12A7 12H34 25V6 64J3 84Y8 84Y8 12H34 country Spain England England USA Russia age 33 54 41 56 47 is_author isbn author name 11005 Miguel de Cervantes 11789 J. R. R. Tolkien 89434 J. R. R. Tolkien 11958 Charles Dickens 69939 Charles Dickens 43498 Herman Melville 44983 Fyodor Dostoevsky Oxford checkout date return_date 02/09/2022 02/16/2022 02/09/2022 02/16/2022 02/09/2022 02/16/2022 03/09/2022 03/18/2022 03/09/2022 03/11/2022 03/09/2022 03/16/2022 04/09/2022 04/09/2022 04/09/2022 date 1605 1937 1937 1861 1843 1851 1880 birth_date 09/29/1547 01/03/1892 02/07/1812 08/01/1819 11/11/1821 03/17/2022 03/17/2022 NULL 3 104 a) (0 pts.) Create the above tables in PostgreSQL, making sure that all dates are implemented using the DATE type. The table definitions must define the necessary primary keys, foreign keys, not-null constraints, and check constraints. b) (10 pts.) Find the names of the patrons that have borrowed more than two books on the same day. c) (10 pts.) Find the names of the English authors who have written books that have been borrowed two or more times. d) (10 pts.) Find those dates on which at least three books were returned. e) (10 pts.) Find the dates on which books written by Miguel de Cervantes or Fyodor Dostoevsky were borrowed. You must not use nested queries. Page 2 of 3 f) (10 pts.) Repeat question e) but using a nested query. g) (10 pts.) Find the dates on which books by Alexandre Dumas were borrowed and none from Herman Melville. You must not use nested queries. h) (10 pts.) Repeat question g) but using a nested query. i) (10 pts.) Find the titles of books borrowed two or more times using a correlated query with EXISTS. You cannot use GROUP BY. j) (10 pts.) Change the city and state of Miranda from "Shreveport" and “Louisiana” to "Orlando" and "Florida", respectively. k) (10 pts.) Delete all patrons that have not borrowed a book in more than 3 years. patron patron id 12A7 23U9 64J3 12H34 25V6 84Y8 book isbn 11353 11958 11789 11005 65345 89434 43498 44983 patron_name Juliet Beatrice Falstaff Claudius Miranda Puck age 21 14 87 city Dallas Houston Baton Rouge Dallas 73 31 Shreveport Dallas 48 title War and Peace Great Expectations The Hobbit Don Quixote The Count of Monte Cristo The Lord of the Rings Moby Dick The Brothers Karamazov publisher Penguin Penguin Random House RAE Oxford Random House Oxford Oxford state TX TX LA TX LA TX phone 972-7823562 713-5689420 225-5387460 972-6597534 318-9837547 972-1764390 num_copies 5 7 10 4 3 18 5 2 pages 1225 328 300 928 1276 1178 378 840 69939 author author name Miguel de Cervantes J. R. R. Tolkien Charles Dickens Herman Melville Fyodor Dostoevsky borrows isbn A Christmas Carol 11353 44983 11789 65345 65345 11005 43498 44983 65345 patron id 12A7 12A7 12A7 12H34 25V6 64J3 84Y8 84Y8 12H34 country Spain England England USA Russia age 33 54 41 56 47 is_author isbn author name 11005 Miguel de Cervantes 11789 J. R. R. Tolkien 89434 J. R. R. Tolkien 11958 Charles Dickens 69939 Charles Dickens 43498 Herman Melville 44983 Fyodor Dostoevsky Oxford checkout date return_date 02/09/2022 02/16/2022 02/09/2022 02/16/2022 02/09/2022 02/16/2022 03/09/2022 03/18/2022 03/09/2022 03/11/2022 03/09/2022 03/16/2022 04/09/2022 04/09/2022 04/09/2022 date 1605 1937 1937 1861 1843 1851 1880 birth_date 09/29/1547 01/03/1892 02/07/1812 08/01/1819 11/11/1821 03/17/2022 03/17/2022 NULL 3 104 a) (0 pts.) Create the above tables in PostgreSQL, making sure that all dates are implemented using the DATE type. The table definitions must define the necessary primary keys, foreign keys, not-null constraints, and check constraints. b) (10 pts.) Find the names of the patrons that have borrowed more than two books on the same day. c) (10 pts.) Find the names of the English authors who have written books that have been borrowed two or more times. d) (10 pts.) Find those dates on which at least three books were returned. e) (10 pts.) Find the dates on which books written by Miguel de Cervantes or Fyodor Dostoevsky were borrowed. You must not use nested queries. Page 2 of 3 f) (10 pts.) Repeat question e) but using a nested query. g) (10 pts.) Find the dates on which books by Alexandre Dumas were borrowed and none from Herman Melville. You must not use nested queries. h) (10 pts.) Repeat question g) but using a nested query. i) (10 pts.) Find the titles of books borrowed two or more times using a correlated query with EXISTS. You cannot use GROUP BY. j) (10 pts.) Change the city and state of Miranda from "Shreveport" and “Louisiana” to "Orlando" and "Florida", respectively. k) (10 pts.) Delete all patrons that have not borrowed a book in more than 3 years.

Answer rating: 100% (QA)

the SQL queries to create the tables answer a CREATE TABLE patron patronid VARCHAR4 PRIMARY KEY patronname VARCHAR20 NOT NULL age INT NOT NULL city VA... View the full answer