patron patron id 12A7 23U9 64J3 12H34 25V6 84Y8 book isbn 11353 11958 11789 11005 65345...
Fantastic news! We've Found the answer you've been seeking!
Question:
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.
Expert Answer:
Answer rating: 100% (QA)
a To create the tables in PostgreSQL with the necessary constraints primary keys and foreign keys you can use the following SQL statements sql CREATE ... View the full answer
Related Book For
Systems Analysis and Design in a Changing World
ISBN: 978-1305117204
7th edition
Authors: John W. Satzinger, Robert B. Jackson, Stephen D. Burd
Posted Date:
Students also viewed these programming questions
-
The following additional information is available for the Dr. Ivan and Irene Incisor family from Chapters 1-5. Ivan's grandfather died and left a portfolio of municipal bonds. In 2012, they pay Ivan...
-
Use the attached "words.txt" file to store the words along with their ranks in an AVL tree. Then provide the user the option to search for any word. If the word exists the program displays the rank...
-
The raw data for Problem 22, from Freund (1979), appear below. For the model of Problem 22, a. Fit the model. b. What discrepancies do you note between the results of (a) and the data summary...
-
State whether the following statement are True or False An increase in the owner(s) equity, in the absence of any further investment by the owners is typically effected by sale transactions.
-
Use the following information for questions 1 through 5: Assume you are presented with the following mutually exclusive investments whose expected net cash flows are as follows: 1. Construct NPV...
-
In Exercises 3344, add or subtract terms whenever possible. 412 - 275
-
You bought 200 shares of a stock at \($17\) per share in 2001. After 12 months, the stock doubled in value, and you sold 100 shares. Eighteen months later, the stock reached \($60\) and split. After...
-
(Issuance and Retirement of Bonds; Income Statement Presentation) Holiday Company issued its 9%, 25-year mortgage bonds in the principal amount of $3,000,000 on January 2, 1996, at a discount of...
-
corporate finance, Question 6 You want to open a coffee shop, which costs $50,000 now. There are two options. First, keep operating it yourself. It generates a positive cash ?ow of $25,000 per...
-
Alexander Smith and his wife Allison are married and file a joint tax return for 2021. The Smiths live at 1234 Buena Vista Drive, Orlando, FL 32830. Alexander is a commuter airline pilot but took 6...
-
Addition of steam to combustors in gas turbines and to internal-combustion engines reduces the peak temperatures and lowers emission of NOx. Consider a modification to a gas turbine, as shown in...
-
To what extent is the decision to collect more evidence a commercial one?
-
Why should an auditor conduct compliance tests every year, even if the companys system has always worked perfectly in the past?
-
Why is an examination of the system a vital part of the risk-based audit approach, as implied by the US requirement that the auditor should obtain an understanding of the companys control environment?
-
Why do auditors insist on sending management letters if managers do not find them useful?
-
The second quartile is the mean of an ordered data set. In Exercises 710, determine whether the statement is true or false. If it is false, rewrite it as a true statement.
-
A $100 bond has one year to maturity. The bond pays 6% per year with coupon payments made quarterly. The interest rate is 5.8% per year with interest compounded semi-annually. Calculate the value of...
-
Read Case Study Google: Dont Be Evil Unless and answer the following: Why do you think Google was adamant about not wanting to supply information requested by the government concerning the Child...
-
What is an action bar? Have you noticed one on an app? Describe it.
-
Given the following list of classes and associations for the previous car insurance system, list the preconditions and postconditions for the use case Add a new vehicle to an existing policy.
-
What is an association, and what system development standard defines it?
-
\(P(60
-
\(P(x <55)\) In Exercises 27-32, the random variable \(x\) is normally distributed with mean \(\mu=74\) and standard deviation \(\sigma=8\). Find the indicated probability.
-
\(P(x <84)\) In Exercises 27-32, the random variable \(x\) is normally distributed with mean \(\mu=74\) and standard deviation \(\sigma=8\). Find the indicated probability.
Study smarter with the SolutionInn App