Problem 87Write a query to display the patron ID, book number, and days kept for eachcheckout. "Days Kept" is the difference from the date on which the book isreturned to the date it was checked out. Sort the results by days kept indescending order, then by patron ID, and then by book number (Figure P7.87). (68rows)PATRONBOOKDays Kept1160524078116552357811855240781219524878122252407812265244781202523677121852367712285237771220523576Figure 7.87Task 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,

In SQL, to calculates the number of days between two dates, DATEDIFF() function is used. It retrieve…

Write a query to display the patron ID, book number, and days kept for each checkout. "Days Kept" is the difference from the date on which the book is returned to the date it was checked out. Sort the results by days kept in descending order, then by patron ID, and then by book number (Figure P7.87).

Write a query to display the patron ID, book number, and days kept for each checkout. "Days Kept" is the difference from the date on which the book is returned to the date it was checked out. Sort the results by days kept in descending order, then by patron ID, and then by book number (Figure P7.87).

A Guide to SQL

9th Edition

ISBN:9781111527273

Author:Philip J. Pratt

Publisher:Philip J. Pratt

Chapter2: Database Design Fundamentals

Section: Chapter Questions

Problem 11RQ

See similar textbooks

Related questions

Q: Write a query to produce the total number of hours and charges for each of the projects represented…

A: A query is a way to give instruction to the server to extract data from database. In SQL, SELECT…

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: Write a query to display the book number, title and cost of books that have the lowest cost of any…

A: Query: In general, the term query describes a question or request that is made by a user or another…

Q: Write a query to generate a report indicating the total number of sick leave days for each employee.…

A: Actually, database is a used to stores the data\information.

Q: Write a query to display names, street, city, state, zip code of any customer who purchased a Danish…

A: Explanation: The below query will display the employees “CUST_FNAME”, “CUST_LNAME”, “CUST_STREET”,…

Q: .Write a query to list the first name and last name of all authors. Concatenate the first and last…

A: We need to select the name of both editor and author and do the union. The query for the same as per…

Q: Write a query to display the author ID and the number of books written by that author.Sort the…

A: Structured Query Language is abbreviated as SQL or also pronounced as See-Qwell. It is developed for…

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 customer code, first name, last name of all customers who made more…

A: Select: The SELECT statement is used to select data from a database. SELECT Syntax SELECT…

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: Write a query to display the number of books that are available (not currently checked out) (Figure…

A: SQL aggregate function COUNT can be used to get number of books that are available.

Q: Write a query to display the brand ID, brand name, and average price of products of each brand. Sort…

A: Since we need to get the average price for each brand, we will be using group by clause to perform…

Q: Write a query to verify that the data has been entered correctly.

A: First, insert data into the table using a query for example, declare @firstName varchar(500) =…

Q: Write a query to display the employee number, last name, first name, salary "from" date, salary end…

A: Two tables must be joined using INNER JOIN clause. Give the conditions in WHERE clause. Sort the…

Q: Write a query to fetch details of employees whose EmpLname ends with an alphabet ‘A’ and contains…

A: Given, 'EmpLname' is the column name of the table. The last name of the employee ends with the…

Q: Write a query to display the patron ID, last name, number of times that patron has ever checked out…

Q: Write a query to display the employee number, last name, first name, salary “from” date, salary end…

A: Solution: Given, Write a query to display the employee number, last name, first name, salary…

Q: Write a query to list the book title, checkout date, checkout due date and checkout in date for each…

A: According to the information given:- We have to write a query on the basic of mention need.

Q: Write a query to display the book number, title, and cost for all books in the “Database” subject…

A: The name of the table is the subject.Attributes of the table are book_number, book_title,…

Q: Write a query to retrieve the Fname and Lname in a single column as “FullName”. The first name and…

A: Given, Column name: Fname Lname FullName We need to separate the first name and the last name…

Q: Write a query to display the customer code, balance, and total purchases for each customer. Total…

A: REQUIRED : Executable query to find total purchases. INTRODUCTION : QUERY is used retrieve…

Q: Write a query to display each department's name, location (Name of the City), number of employees,…

A: We have to group the data by the department name and then calculate number of employees in that…

Q: Write a query to display the movie title, movie year, price description, and price rental fee for…

A: From the given details, we are creating a sample table, with name Movies:

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

Problem 87
Write a query to display the patron ID, book number, and days kept for each
checkout. "Days Kept" is the difference from the date on which the book is
returned to the date it was checked out. Sort the results by days kept in
descending order, then by patron ID, and then by book number (Figure P7.87). (68
rows)
PATRON
BOOK
Days Kept
1160
5240
78
1165
5235
78
1185
5240
78
1219
5248
78
1222
5240
78
1226
5244
78
1202
5236
77
1218
5236
77
1228
5237
77
1220
5235
76
Figure 7.87
Task

Database Schema
The schema for the Ch07_FACT database is shown below and should be used to answer the
next several problems. Click this image to view it in its own tab.
FIGURE P7.56 THE CH07_FACT ERD
CHECKOUT
PATRON
PK
Check Num
PK Pat ID
FK1 Book_Num
FK2 Pat_ID
Check_Out_Date
Check_Due_Date
Check_In_Date
>0-----H-
Pat_FName
Pat LName
Pat_Type
BOOK
AUTHOR
PK
Book_Num
PK Au ID
Book_Title
Book_Year
Book_Cost
Book_Subject
FK1 Pat_ID
Au_FName
Au_LName
Au_BirthYear
WRITES
PK,FK1 Book Num
PK,FK2 Au ID
The 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 and
students. Agreements with the publishers allow patrons to electronically check out a book,

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

VIEW

Step 2

VIEW

Trending now

This is a popular solution!

Step by step

Solved in 2 steps

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

The following tables provide a very small portion of the data that will be kept in the database. These data need to be inserted into the database for testing purposes. Write the INSERT commands necessary to place the following data in the tables that were created in Problem 65. RENTAL RENT_NUM RENT_DATE MEM_NUM 1001 01-MAR-13 103 1002 01-MAR-13 105 1003 02-MAR-13 102 1004 02-MAR-13 110 1005 02-MAR-13 111 1006 02-MAR-13 107 1007 02-MAR-13 104 1008 03-MAR-13 105 1009 03-MAR-13 111 VIDEO VID_NUM VID_INDATE MOVIE_NUM 54321 18-JUN-12 1234 54324 18-JUN-12 1234 54325 18-JUN-12 1234 34341 22-JAN-11 1235 34342 22-JAN-11 1235 34366 02-MAR-13 1236 34367 02-MAR-13 1236 34368 02-MAR-13 1236 34369 02-MAR-13 1236 44392 21-OCT-12 1237 44397 21-OCT-12 1237 59237 14-FEB-13 1237 61388 25-JAN-11 1239 61353 28-JAN-10 1245 61354 28-JAN-10 1245 61367 30-JUL-12 1246 61369 30-JUL-12 1246
Which of the following data dictionary objects should be used to view information about the constraints in a database? USER_TABLES USER_RULES USER_COLUMNS USER_CONSTRAINTS None of the above objects should be used.
3.9 LAB - Select lesson schedule with inner join The database has three tables for tracking horse-riding lessons: Horse with columns: ID - primary key RegisteredName Breed Height BirthDate Student with columns: ID - primary key FirstName LastName Street City State Zip Phone EmailAddress LessonSchedule with columns: HorseID - partial primary key, foreign key references Horse(ID) StudentID - foreign key references Student(ID) LessonDateTime - partial primary key Write a SELECT statement to create a lesson schedule with the lesson date/time, horse ID, and the student's first and last names. Order the results in ascending order by lesson date/time, then by horse ID. Unassigned lesson times (student ID is NULL) should not appear in the schedule. Hint: Perform a join on the Student and LessonSchedule tables, matching the student IDs.
Write Mysql code The database has three tables for tracking horse-riding lessons: Horse with columns: ID - primary key RegisteredName Breed Height BirthDate Student with columns: ID - primary key FirstName LastName Street City State Zip Phone EmailAddress LessonSchedule with columns: HorseID - partial primary key, foreign key references Horse(ID) StudentID - foreign key references Student(ID) LessonDateTime - partial primary key Write a SELECT statement to create a lesson schedule with the lesson date/time, horse ID, and the student's first and last names. Order the results in ascending order by lesson date/time, then by horse ID. Unassigned lesson times (student ID is NULL) should not appear in the schedule. Hint: Perform a join on the Student and LessonSchedule tables, matching the student IDs.
Part A: MySQL Query/Exec Create StudentsDB database in MySQL server with the following table: Table: Students uid int, Primary Key, Auto Increment studentID varchar(8), Unique name varchar(50) major varchar(50) passedCredits int CGPA float Implement the following PHP scripts as required below: php: the script should display student’s info (StudentID, name, major, credits passed and CGPA) in HTML table with appropriate title for each column. Student cells/rows should be highlighted as follows: Condition Highlight Color Student At Risk: CGPA < 2.0 Red for CGPA Cell Expected to graduate: Passed Credits >=115 Yellow for Passed Credits Cell Outstanding Student: CGPA = 3.5 or above Green for the entire Student Row php: This script should display all students’ names and major. Each student will be identified by a checkbox (define the checkbox name as a numeric array holding the uid). The…
We need to create a database for the Circulation Department of a public library. The database will store information about the patrons, books, and circulation. It contains the following: 4 tables for the database: PATRON, BOOK_COPY, BOOK, and CHECKOUT. (Since a book may have multiple copies that may be purchased by the library at different time, it is better to have a BOOK_COPY table to avoid unnecessary data redundancy.) The data in the tables are as follows: PATRON table records a patron’s ID, name, address, phone number, and email address. BOOK table contains information such as author, title, publication date, subject, language, and a unique identifier (It can be the ISBN of the book) for each book. BOOK_COPY table records a unique identifier for each copy of a book, the date of purchase, and the identifier of the book from the BOOK table. CHECKOUT table records the date of check-out, patron’s ID, the…
We need to create a database for the Circulation Department of a public library. The database will store information about the patrons, books, and circulation. It contains the following: 4 tables for the database: PATRON, BOOK_COPY, BOOK, and CHECKOUT. (Since a book may have multiple copies that may be purchased by the library at different time, it is better to have a BOOK_COPY table to avoid unnecessary data redundancy.) The data in the tables are as follows: PATRON table records a patron’s ID, name, address, phone number, and email address. BOOK table contains information such as author, title, publication date, subject, language, and a unique identifier (It can be the ISBN of the book) for each book. BOOK_COPY table records a unique identifier for each copy of a book, the date of purchase, and the identifier of the book from the BOOK table. CHECKOUT table records the date of check-out, patron’s ID, the…
We need to create a database for the Circulation Department of a public library. The database will store information about the patrons, books, and circulation. It contains the following: 4 tables for the database: PATRON, BOOK_COPY, BOOK, and CHECKOUT. (Since a book may have multiple copies that may be purchased by the library at different time, it is better to have a BOOK_COPY table to avoid unnecessary data redundancy.) The data in the tables are as follows: PATRON table records a patron’s ID, name, address, phone number, and email address. BOOK table contains information such as author, title, publication date, subject, language, and a unique identifier (It can be the ISBN of the book) for each book. BOOK_COPY table records a unique identifier for each copy of a book, the date of purchase, and the identifier of the book from the BOOK table. CHECKOUT table records the date of check-out, patron’s ID, the…
Computer engineering lab ApexSOQL - salesforce object query languageWhy i am getting this error in the below queryHere is the query -List<Prod2> productList = [Select Id, Name, Parent_Product__c, IsActive, Description, Account__c, (Select Id, Product2Id, Quantity From OrderItem) From Prod2 Where Account__c = 'XYZ'];ERROR at Row:3:Column:70I am not able to understand the relation between the OrderItem Object from the above query.Please resolve the error and provided me solution so i can solve it.Solve in Apex
I can't get the coding right on this one, I think I'm missing something here. Can anyone help please? 1.11 LAB - Query execution plans (Sakila) This lab illustrates how minor changes in a query may have a significant impact on the execution plan. MySQL Workbench exercise Refer to the film, actor, and film_actor tables of the Sakila database. This exercise is based on the initial Sakila installation. If you have altered these tables or their data, your results may be different. Do the following in MySQL Workbench: Enter the following statements: USE sakila; SELECT last_name, first_name, ROUND(AVG(length), 0) AS average FROM actor INNER JOIN film_actor ON film_actor.actor_id = actor.actor_id INNER JOIN film ON film_actor.film_id = film.film_id WHERE title = "ALONE TRIP" GROUP BY last_name, first_name ORDER BY average; Highlight the SELECT query. In the main menu, select Query > Explain Current Statement. In the Display Info box, highlighted in red below, select Data Read per Join.…
The database has three tables for tracking horse-riding lessons: Horse with columns: ID - primary key RegisteredName Breed Height BirthDate Student with columns: ID - primary key FirstName LastName Street City State Zip Phone EmailAddress LessonSchedule with columns: HorseID - partial primary key, foreign key references Horse(ID) StudentID - foreign key references Student(ID) LessonDateTime - partial primary key Write a SELECT statement to create a lesson schedule for Feb 1, 2020 with the lesson date/time, student's first and last names, and the horse's registered name. Order the results in ascending order by lesson date/time, then by the horse's registered name. Make sure unassigned lesson times (student ID is NULL) appear in the results.
Write Mysql code for: The database has three tables for tracking horse-riding lessons: Horse with columns: ID - primary key RegisteredName Breed Height BirthDate Student with columns: ID - primary key FirstName LastName Street City State Zip Phone EmailAddress LessonSchedule with columns: HorseID - partial primary key, foreign key references Horse(ID) StudentID - foreign key references Student(ID) LessonDateTime - partial primary key Write a SELECT statement to create a lesson schedule for Feb 1, 2020 with the lesson date/time, student's first and last names, and the horse's registered name. Order the results in ascending order by lesson date/time, then by the horse's registered name. Make sure unassigned lesson times (student ID is NULL) appear in the results. Hint: Perform a join on the LessonSchedule, Student, and Horse tables, matching the student IDs and horse IDs.

SEE MORE QUESTIONS

Recommended textbooks for you

A Guide to SQL

Computer Science

ISBN:

9781111527273

Author:

Philip J. Pratt

Publisher:

Course Technology Ptr

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

Oracle 12c: SQL

Computer Science

ISBN:

9781305251038

Author:

Joan Casteel

Publisher:

Cengage Learning

Programming with Microsoft Visual Basic 2017

Computer Science

ISBN:

9781337102124

Author:

Diane Zak

Publisher:

Cengage Learning

A Guide to SQL

Computer Science

ISBN:

9781111527273

Author:

Philip J. Pratt

Publisher:

Course Technology Ptr

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

Oracle 12c: SQL

Computer Science

ISBN:

9781305251038

Author:

Joan Casteel

Publisher:

Cengage Learning

Programming with Microsoft Visual Basic 2017

Computer Science