+Expected Results PAT ID Average Days Kept 1160 53.00 1210 49.00 1185 30.00 1165 28.67 1209 28.33 1207 5.50 1172 4.50 1183 4.33 1171 3.67 1181 3.67 Actual Results PAT ID AVERAGE DATE KEPT 1160 7.0000 1161 3.3333 1165 5.6667 1171 3.6667 1172 4.5000 1181 3.6667 1183 4.3333 1185 6.6667 1207 5.5000 1209 5.3333

Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
icon
Related questions
icon
Concept explainers
Question

Problem 104

Write a query to display the patron ID and the average number of days that patron keeps books during a checkout. Limit the results to only patrons who have at least three checkouts. Sort the results in descending order by the average days the book is kept, and then in ascending order by patron ID (Figure P7.104).

SELECT P.PAT_ID, AVG(DATEDIFF(C.CHECK_IN_DATE,C.CHECK_OUT_DATE) ) AS "AVERAGE DATE KEPT" FROM PATRON P JOIN CHECKOUT C ON P.PAT_ID=C.PAT_ID GROUP BY P.PAT_ID HAVING COUNT(C.CHECK_OUT_DATE) >= 3 Order by "AVERAGE DAYS KEPT" DESC,P.PAT_ID ASC;

With the query below I am getting the wrong headers were returned, some expected rows were missing and some unexpected rows were returned.

How can i correct this query to ge the expected results? Thanks

+Expected Results
РАT ID
Average Days Kept
1160
53.00
1210
49.00
1185
30.00
1165
28.67
1209
28.33
1207
5.50
1172
4.50
1183
4.33
1171
3.67
1181
3.67
Actual Results
РАT ID
AVERAGE DATE KEPT
1160
7.0000
1161
3.3333
1165
5.6667
1171
3.6667
1172
4.5000
1181
3.6667
1183
4.3333
1185
6.6667
1207
5.5000
1209
5.3333
Transcribed Image Text:+Expected Results РАT ID Average Days Kept 1160 53.00 1210 49.00 1185 30.00 1165 28.67 1209 28.33 1207 5.50 1172 4.50 1183 4.33 1171 3.67 1181 3.67 Actual Results РАT ID AVERAGE DATE KEPT 1160 7.0000 1161 3.3333 1165 5.6667 1171 3.6667 1172 4.5000 1181 3.6667 1183 4.3333 1185 6.6667 1207 5.5000 1209 5.3333
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 3 steps with 1 images

Blurred answer
Knowledge Booster
Query Syntax
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education