**Problem 100**Write a query to display the author ID, author last name, book title, checkout date, and patron last name for all the books written by authors with the last name “Bruer” that have ever been checked out by patrons with the last name “Miles.” Sort the results by checkout date (Figure P7.100).**Figure 7.100: Data Table Explanation**The figure contains a table with the following columns:1. **AU_ID**: Author ID - Example: 2512. **AU_LNAME**: Author Last Name - Example: Bruer3. **BOOK_TITLE**: Title of the Book - Example: Capture the Cloud4. **CHECK_OUT_DATE**: Date when the book was checked out - Examples: 2017-04-21, 2017-05-155. **PAT_LNAME**: Patron Last Name - Example: MilesThe table shows two records with the same author and book, but with different checkout dates.**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 (Entity-Relationship Diagram)**This diagram includes five entities: CHECKOUT, PATRON, BOOK, AUTHOR, and WRITES, each with various attributes and relationships.1. **CHECKOUT** - **PK**: Check_Num - **FK1**: Book_Num - **FK2**: Pat_ID - Attributes: Check_Out_Date, Check_Due_Date, Check_In_Date2. **PATRON** - **PK**: Pat_ID - Attributes: Pat_FName, Pat_LName, Pat_Type3. **BOOK** - **PK**: Book_Num - **FK1**: Pat_ID - Attributes: Book_Title, Book_Year, Book_Cost, Book_Subject4. **AUTHOR** - **PK**: Au_ID - Attributes: Au_FName, Au_LName, Au_BirthYear5. **WRITES** - **PK, FK1**: Book_Num - **PK, FK2**: Au_ID**Description of Diagram:**- *Relationships:* - There are connecting lines between CHECKOUT and PATRON, BOOK and AUTHOR, indicating relational connections. - A dotted line connects BOOK with PATRON through Pat_ID, and a solid line ties AUTHOR with WRITES and BOOK.- *Keys:* - PK indicates Primary Key, unique identifiers for each entity. - FK indicates Foreign Key, which creates a relationship between tables.**Context:**The CIS Department at Tiny College maintains the Free Access to Current Technology (FACT) library of e-books. FACT is a collection designed for technology e-books usable by both faculty and students. Agreements with publishers enable patrons to electronically check out books, record usage, and track the authorship of these books.

SYNTAX The syntax for the query is as follows. SELECT column1, column2 FROM table1 JOIN table2 ON table1.column3 = table2.column3 WHERE column2 = "xyz" ORDER BY column1 ; Here, column1 and column2 are retrieved from the resultant joined table of table1 and table2 and displayed. The tables are joined using the common column in them. The rows of column2 having the value "xyz" only are included in the output. The output is sorted in the ascending order of column1.QUERY The final query is as follows. SELECT Au_ID, Au_LName, Book_Title, Check_Out_Date, Pat_LName FROM AUTHOR JOIN WRITES ON AUTHOR.Au_ID = WRITES.Au_ID JOIN BOOK ON WRITES.Book_Num = BOOK.Book_Num JOIN CHECKOUT ON CHECKOUT.Book_Num = BOOK.Book_Num JOIN PATRON ON PATRON.Pat_ID = CHECKOUT.Pat_ID WHERE Au_LName = "Bruer" AND Pat_LName = "Miles" ORDER BY Check_Out_Date ;The above query returns all checked out books written by authors having the last name as Bruer and patrons having the last name as Miles. The output is sorted in the ascending order of check-out date.

Engineering

Computer Science

Write a query to display the author ID, author last name, book title, checkout date, and patron last name for all the books written by authors with the last name "Bruer" that have ever been checked out by patrons with the last name "Miles." Sort the results by check out date (Figure P7.100). BOOK, TITLE AU ID AU LNAME CHECK OUT DAТЕ PAT LNAME 251 Bruer Capture the Cloud 2017-04-21 Miles 251 Bruer Capture the Cloud 2017-05-15 Miles Figure 7.100

Write a query to display the author ID, author last name, book title, checkout date, and patron last name for all the books written by authors with the last name "Bruer" that have ever been checked out by patrons with the last name "Miles." Sort the results by check out date (Figure P7.100). BOOK, TITLE AU ID AU LNAME CHECK OUT DAТЕ PAT LNAME 251 Bruer Capture the Cloud 2017-04-21 Miles 251 Bruer Capture the Cloud 2017-05-15 Miles Figure 7.100

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: Write a query to list the book number, title, and number of times each book has been checked out…

A: Query The query for viewing book number, title and the number of times each book had been checked…

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: using the code belowe write a query to display the movie genre and average price rental fee for…

A: In the database analysis, understanding the distribution of movie costs across genres is a crucial…

Q: using the code write a query to display the movie number, movie title, and price code for all movies…

A: Algorithm:Define a table named PRICE with columns PRICE_CODE, PRICE_DESCRIPTION, PRICE_RENTFEE, and…

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 display the book number, title, and replacement cost for all books in the…

A: Query to display the book number, title , and replacement cost for all books in the "Database"…

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: Create a query that SELECT all of the EMPLOYEES from the EMPLOYEE TABLE that are Mangers. Include…

A: Self Join: A self join occurs when a table is linked to itself (also known as Unary…

Q: Create a query based on tblReservations that displays the guests that have reservations for all…

A: Structured Query Language, abbreviated as SQL, which usually refers to the one it is a…

Q: Write a query to display the rental number, rental date, movie title, and detail fee for each movie…

A: According to the given schema, first create a table Movies and insert some records in the table.…

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 employee number, last name, email address, title, and department name…

A: QUERY: SELECT EMP_NUM, EMP_LNAME,EMP_EMAIL, EMP_TITLE,DEPT_NAME FROM EMPLOYEE WHERE EMP_TITLE NOT…

Q: using the code below write a query to display the movie title, movie genre, price description, and…

A: Select Columns: Choose the columns to be displayed: MOVIE_TITLE, MOVIE_GENRE, PRICE_DESCRIPTION, and…

Q: Create a query for displaying minimum salary in column alias “Minimum Salary” along with job_id…

A: Format of query :- SELECT colounname , MIN( coloumname) AS aliasname FROM tablename; According…

Q: using the code write a query to display the movie number, movie title, movie cost, and movie genre…

A: 1. Create a table named MOVIE: - Define columns: - MOVIE_NUM as INT (Primary Key) -…

Q: Write a query that will display all movies with a return date of March 1, 2018, March 5, 2018, March…

A: Sql query to display all the movies with a return date of March 1, 2018, March 5, 2018, March 15,…

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

Multiple Table

In a database, a table is used to store data in the form of rows and columns. Rows are aligned horizontally and identified by one or more values in a specific column. Columns are vertically aligned and identified by a unique column name.

Question

**Problem 100**

Write a query to display the author ID, author last name, book title, checkout date, and patron last name for all the books written by authors with the last name “Bruer” that have ever been checked out by patrons with the last name “Miles.” Sort the results by checkout date (Figure P7.100).

**Figure 7.100: Data Table Explanation**

The figure contains a table with the following columns:

1. **AU_ID**: Author ID
- Example: 251

2. **AU_LNAME**: Author Last Name
- Example: Bruer

3. **BOOK_TITLE**: Title of the Book
- Example: Capture the Cloud

4. **CHECK_OUT_DATE**: Date when the book was checked out
- Examples: 2017-04-21, 2017-05-15

5. **PAT_LNAME**: Patron Last Name
- Example: Miles

The table shows two records with the same author and book, but with different checkout dates.

**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 (Entity-Relationship Diagram)**

This diagram includes five entities: CHECKOUT, PATRON, BOOK, AUTHOR, and WRITES, each with various attributes and relationships.

1. **CHECKOUT**
- **PK**: Check_Num
- **FK1**: Book_Num
- **FK2**: Pat_ID
- Attributes: Check_Out_Date, Check_Due_Date, Check_In_Date

2. **PATRON**
- **PK**: Pat_ID
- Attributes: Pat_FName, Pat_LName, Pat_Type

3. **BOOK**
- **PK**: Book_Num
- **FK1**: Pat_ID
- Attributes: Book_Title, Book_Year, Book_Cost, Book_Subject

4. **AUTHOR**
- **PK**: Au_ID
- Attributes: Au_FName, Au_LName, Au_BirthYear

5. **WRITES**
- **PK, FK1**: Book_Num
- **PK, FK2**: Au_ID

**Description of Diagram:**

- *Relationships:*
- There are connecting lines between CHECKOUT and PATRON, BOOK and AUTHOR, indicating relational connections.
- A dotted line connects BOOK with PATRON through Pat_ID, and a solid line ties AUTHOR with WRITES and BOOK.

- *Keys:*
- PK indicates Primary Key, unique identifiers for each entity.
- FK indicates Foreign Key, which creates a relationship between tables.

**Context:**

The CIS Department at Tiny College maintains the Free Access to Current Technology (FACT) library of e-books. FACT is a collection designed for technology e-books usable by both faculty and students. Agreements with publishers enable patrons to electronically check out books, record usage, and track the authorship of these books.

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

Step 3

VIEW

Trending now

This is a popular solution!

Step by step

Solved in 3 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

Use the code below to write a query to display the movie title, movie year, and movie genre for all movies CREATE TABLE MOVIE ( MOVIE_NUM NUMBER(8,0) PRIMARY KEY, MOVIE_TITLE VARCHAR2(75) NOT NULL, MOVIE_YEAR NUMBER(4,0) CHECK (MOVIE_YEAR > 1900), MOVIE_COST NUMBER(5,2), MOVIE_GENRE VARCHAR2(50), PRICE_CODE NUMBER(2,0) CONSTRAINT MOVIE_PRICE_CODE_FK REFERENCES PRICE ); INSERT INTO MOVIE VALUES (1234, 'The Cesar Family Christmas', 2011, 39.95, 'FAMILY', 2); INSERT INTO MOVIE VALUES (1235, 'Smokey Mountain Wildlife', 2008, 59.95, 'ACTION', 1); INSERT INTO MOVIE VALUES (1236, 'Richard Goodhope', 2012, 59.95, 'DRAMA', 2); INSERT INTO MOVIE VALUES (1237, 'Beatnik Fever', 2011, 29.95, 'COMEDY', 2); INSERT INTO MOVIE VALUES (1238, 'Constant Companion', 2012, 89.95, 'DRAMA', NULL); INSERT INTO MOVIE VALUES (1239, 'Where Hope Dies', 2002, 25.49, 'DRAMA', 3); INSERT INTO MOVIE VALUES (1245, 'Time to Burn', 2009, 45.49, 'ACTION', 1);
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 employee number, last name, first name, salary “from” date, salary end date, and salary amount for employees 83731, 83745, and 84039. Sort the output by employee number and salary “from” date (Refer to Figure P7.31)
Question #10.Write a query to retrieve invoice_number, invoice_date, and “Balance Due” from the invoices table.Balance Due can be calculated by subtracting payment and credit from the original invoicedamount. Make sure to only select rows with positive “Balance Due” and sort the results basedon Balance Due. Refer to the sample result screen below.
Write a query to display the movie title, movie year, price description, and price rental fee for all movies in all categories except for family where the table name is Movies.