Problem 102Write a query to display the patron ID, last name, number of times that patron hasever checked out a book, and the number of different books the patron has everchecked out. For example, if a given patron has checked out the same book twice,that would count as two checkouts but only one book. Limit the results to onlypatrons who have made at least three checkouts. Sort the results in descendingorder by number of books, then in descending order by number of checkouts, andthen in ascending order by patron ID (Figure P7.102).PAT IDPAT LNAMENUM CHECKOUTSNUM DIFFERENT BOOKS1161Коch3.1165Baldwin31181Horne331185Yang31210Cooley3.1229Burke331160carter31171Marsh2.1172Miles2.1207Ramos2.LUL 7 102 Database SchemaThe schema for the Ch07_FACT database is shown below and should be used to answer thenext several problems. Click this image to view it in its own tab.FIGURE P7.56 THE CH07_FACT ERDCHECKOUTPATRONPKCheck NumPK Pat IDFK1 Book_NumFK2 Pat_IDCheck_Out_DateCheck_Due_DateCheck_In_Date>0-----H-Pat_FNamePat LNamePat_TypeBOOKAUTHORPKBook_NumPK Au IDBook_TitleBook_YearBook_CostBook_SubjectFK1 Pat_IDAu_FNameAu_LNameAu_BirthYearWRITESPK,FK1 Book NumPK,FK2 Au IDThe CIS Department at Tiny College maintains the Free Access to Current Technology (FACT)library of e-books. FACT is a collection of current technology e-books for use by faculty andstudents. Agreements with the publishers allow patrons to electronically check out a book,

Answered: Image /qna-images/answer/a5026e58-c3c4-4364-b229-cf3cef6ce31f.jpg

Write a query to display the patron ID, last name, number of times that patron has ever checked out a book, and the number of different books the patron has ever checked out. For example, if a given patron has checked out the same book twice, that would count as two checkouts but only one book. Limit the results to only patrons who have made at least three checkouts. Sort the results in descending order by number of books, then in descending order by number of checkouts, and then in ascending order by patron ID (Figure P7.102).

Write a query to display the patron ID, last name, number of times that patron has ever checked out a book, and the number of different books the patron has ever checked out. For example, if a given patron has checked out the same book twice, that would count as two checkouts but only one book. Limit the results to only patrons who have made at least three checkouts. Sort the results in descending order by number of books, then in descending order by number of checkouts, and then in ascending order by patron ID (Figure P7.102).

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

See similar textbooks

Similar questions

Write a query that will group identical movie categories together. The table name is Movies.
Based on stage 2, write queries for the following questions: Write a query to display all vendors sorted by vendor name. Write a query to display the product id, description, type, base, category, and price for all products that have a product base of water and a product category of sealer. Write a query to display the first name, last name, and email address of employees who were not hired from January 1, 2015, to December 31, 2019. Sort the output by the last name and then by the first name. Write a query to display the first name, last name, phone number, title, and department number of employees who work in department 123 and have the title “Assistant.” Write a query to display the employee number, last name, first name, salary “from” date, salary end date, and salary amount for employees 99675, 53845, and 44035. Sort the output by employee number and salary “from” date. Write a query to display the first name, last name, street no, street name, city,…
Create a query to show the subject and the quantity of books in each category. Sort the results in ascending order by the number of books, then in ascending order by the subject name.
Write a query to list the author last name, author first name and the book title and cost for each book written by the author “Xia Chiang”. Sort the result by the book title and then the costdescending
Management is considering closing on Sundays and would like to have a feeling for the kinds of customers that come to the dealership on Sunday. Create a totals query that shows the count of encounters on the Sundays of July 2015—grouped by the credit description held by the customer of the encounter. The Sundays in July of 2015 are the 5th, 12th, 19th, and 26th. Your answer should have two columns (CreditDescription and encounter_count) and should be sorted alphabetically by credit description.
ERD:- PK CHECKOUT Check Num FK1 Book_Num FK2 Pat_ID Check Out Date Check_Due Date Check_In_Date PK BOOK Book Num Book Title Book_Year Book_Cost Book_Subject FK1 Pat_ID -H- PATRON PK Pat ID Pat_FName Pat_LName Pat_Type WRITES PK,FK1 Book Num PK,FK2 Au ID AUTHOR PK AU ID Au_FName Au_LName Au_BirthYear
Management is considering closing on Sundays and would like to have a feeling for the kinds of customers that come to the dealership on Sunday. Create a totals query that shows the count of encounters on the Sundays of July 2015—grouped by the credit description held by the customer of the encounter. The Sundays in July of 2015 are the 5th, 12th, 19th, and 26th. Your answer should have two columns (CreditDescription and encounter_count) and should be sorted alphabetically by credit description. This is what Ive been working with and doesn't give me results, just error messages. SELECT R.CreditDescription, E.CustomerID FROM Encounter E JOIN Customer C ON E.CustomerID = C.CustomerID JOIN CreditRating R ON C.CreditID = R.CreditID WHERE E.EncDate = STR_TO_DATE('2015-07-05','2015-07-12','2015-07-19','2015-07-26'); ORDER BY creditrating.creditid, ASC; creditrating creditid creditdescription minfico maxfico comments customer customerid cfirstname clastname…
Write a query to display the employee number, last name, first name, salary “from” date, salary end date, and salary amount for employees 99675, 53845, and 44035. Sort the output by employee number and salary “from” date.
Write a query to list the book title, checkout date, checkout due date and checkout in date for each book.
Write a query to display the book title, price and publisher name for all books.
Write a query to display the author ID, first and last name for all authors that have never written a book with the subject Programming. Sort the results by author's last name. (Figure P8.64)
Using F_STAFFS table, write a query that will display First_name and maximum salary of staffswith the column name “HighSalary”, whose first_name contain letter ‘u’, for eachFirst_Name, whose maximum salary is less than 10. Arrange it by Last_name from z to a.