b) 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. Sample Output:- Chekouts and Books by Patron PAT_ID PAT_LNAME NUM CHECKOUTS NUM DIFFERENT BOOKS 1161 Koch 1165 Baldwin 1181 Horne 1185 Yang 1210 Cooley 1229 Burke 1160 carter 1171 Marsh 1172 Miles 1207 Ramos 1209 Mathis 1183 Hughes 3 3 3 3 3 لا لا لا لا لا لا لا لا 3 3 3 3 3 3 3 3 3 3 3 3 3 2 2 2 2 2 1

b) 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. Sample Output:- Chekouts and Books by Patron PAT_ID PAT_LNAME NUM CHECKOUTS NUM DIFFERENT BOOKS 1161 Koch 1165 Baldwin 1181 Horne 1185 Yang 1210 Cooley 1229 Burke 1160 carter 1171 Marsh 1172 Miles 1207 Ramos 1209 Mathis 1183 Hughes 3 3 3 3 3 لا لا لا لا لا لا لا لا 3 3 3 3 3 3 3 3 3 3 3 3 3 2 2 2 2 2 1

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

Related questions

Q: The Binder Prime Company wants to recognize the employee who sold the most of its products during a…

A: We need to provide a query to display the employee number, employee first name, employee last name,…

Q: Write a query to display the first name, last name, phone number, title, and department number of…

A: Actually, A database is a collection of information that is organized so that it can be easily…

Q: Write a query to display the subject and the number of books in each subject. Sort the results by…

A: Hey! Hope you are doing good! Thanks for the question. Since you have not specified the version of…

Q: Use a query to compute the total of all purchases, the number of purchases, and the average purchase…

A: Step 1. Table creation: Step 2. Inserting values: Step 3. Display the table: Step 4. To calculate…

Q: Write a query to list the book subject and the number of books in each subject. Limit your result to…

A: SELECT – to query data in the database INSERT – to insert data into a table UPDATE – to update…

Q: Use a query to compute the total of all purchases, the number of purchases, and the average purchase…

A: SQL query The Round() function is used to round off the number to the desired decimal places. The…

Q: Write a query to display the first name, last name, and email address of employees hired from May 1,…

A: Here we have to write 2 queries whose details are as follows:Write a query to display the first…

Q: 4. Which customers live in Georgia or New Jersey? Put the results in ascending order by last name.…

A: Assume that, the name of the table is Customer The entities of Customer table are last name, first…

Q: Select clause and from clause: Display the columns specified above in the problem statement from…

A: Write a query to display the author ID, first name, last name, and year of birth for all authors…

Q: Management is considering closing on Sundays and would like to have a feeling for the kinds of…

A: The SQL query is given below with output screenshot and explanation Also a table with demo data has…

Q: Write query "List the title and ISBN of all books using the BOOKS table"

A: Query to display all the records of the books table: SELECT ISBN, BOOK_TITLE FROM BOOKS;

Q: inventory, i.e. the sum of all products on hand for each brand id. Sort the output by brand id in…

A: Here have to determine sql query for given problem.

Q: Write a query to display the first name, last name, street, city, state, and zip code of any…

A: Structured Query Language (SQL) can be defined in such a way that it is a standardized programming…

Q: Management is considering closing on Sundays and would like to have a feeling for the kinds of…

A: Data analysis is an essential tool for management decision-making, especially in today's data-driven…

Q: 7) Write a query to display Unique order dates from the shipper named “Speedy Express”.

A: According to Bartleby Guidelines we need to answer only one question so I have answered first…

Q: Create the following table in your database with the following schema: Table: Products I Column Name…

A: Create table syntax : CREATE TABLE TABLE_NAME( column_name data_type, column_name data_type,…

Q: Write a query to display the checkout number, book number, patron ID, checkout date, and due date…

A: The query follows the syntax given below. SELECT column1, column2 FROM table1 WHERE condition ORDER…

Q: Create a query to show the subject and the quantity of books in each category. Sort the results in…

A: Introduction: Important information to remember: Hey! I hope you're doing well! Thank you for your…

Q: Select clause: Display the columns stated in the problem statement From clause: AUTHOR table…

A: SQL is a structured query language, that's is used to extract and manipulate the data from the…

Q: Write a query to list the author last name, author first name and the book title and cost for each…

A: Solution: FROM: It is used to get data from the table WHERE: It is used to list the data where given…

Concept explainers

Single Table

In a database management system, a single table is designed to obtain data of heterogeneous items. A single-table design with DynamoDB resolves the issue of raising multiple requests for the relevant data of an entity.

Question

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

b) 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.
Sample Output:- Chekouts and Books by Patron
PAT ID PAT LNAME NUM CHECKOUTS NUM DIFFERENT BOOKS
1161 Koch
1165 Baldwin
1181 Horne
1185 Yang
1210 Cooley
1229 Burke
1160 carter
1171 Marsh
1172 Miles
1207 Ramos
1209 Mathis
1183 Hughes
3
3
3
3
3
3
3
لا لا لا لا لا لا
3
3
3
3
3
3
ww
3
3
3
3
3
2
2
2
2
2
1

Expert Solution

This question has been solved!

Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.

This is a popular solution!

SEE SOLUTION Check out a sample Q&A here

Step 1: Algorithm of the program:

VIEW

Step 2: Source code of the program:

VIEW

Step 3: Screenshot of the program:

VIEW

Solution

VIEW

Trending now

This is a popular solution!

Step by step

Solved in 4 steps with 2 images

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

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

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.
Create a query for each of the following, save and print a copy: Books on a loan, using only BookID, title, author, and date taken. List of female readers who have at least loaned a book using only ReaderID, surname, first name, town, sex, book, title, and date taken. List of books that have been taken at least once and whose date of publication is before January 2013.
Write a query to verify that the data has been entered correctly.
Task 2: List the owner number and last name for all owners located in the city of Seattle. Your query should ignore case. For example, a customer with the city Seattle should be included, as should customers whose city is SEATTLE, SEAttle, SeAttle, and so on.
Write a query that returns the EMP_NUM and Number of ratings he/she earned (based on EARNEDRATING table, call this Num_RTG), for those who had at least got 1 rating earned, but the number of ratings he/she earned must lower than the average number of ratings for all employees who had earned ratings before, and such employee has been served as “Copilot” before.
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.
Write a query that displays the book title, cost, and year of publication for every book in the system. Sort the results by book title.