**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…

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: Create a query for displaying average commission percentage company is giving to its employees.…

A: Format of Query :- SELECT AVG ( coloum name ) AS aliasname FROM tablename; According To question :-…

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 Director Name and their age. The result-set should only show directors…

A: CREATE TABLE director ( dir_age INT NOT NULL, dir_name VARCHAR(30) NOT NULL, ); INSERT INTO…

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: Write a query that returns the Staff ID for the staff member who is involved in the most rentals

A: The goal of this search is to identify the employee who handles the most rentals. In order to…

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: department_id from employees table. Instead of displaying department_id display the related…

A: Create a query for displaying emp_id,first_name,last_name,department_id from employees table.…

Q: Write a query that displays the name and hiredate of any employee hired after 24 October 2000 and…

A: Query: Query means a request to retrieve data or information from databases. Query can be either…

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: Question 4 Write a query to display the first name concatenated with the last name as Full Name, and…

A: This particular question belongs to Computer Science and Engineering. Computer Science is an…

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

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.
fdhdhdh
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 book title, price and publisher name for all books.
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.
Write a query to display the first name, last name, street, city, state, and zip code of any customer who purchased a Foresters Best brand top coat between July 15, 2015, and July 31, 2015.If a customer purchased more than one such product, display the customer's information only once in the output. Sort the output by state, last name, and then first name (Figure P7.32).