Description

• Do your own work: This is an individual assignment and you must do your own work and create your own SQL statements. If you are caught cheating on this, you will receive a zero on this assignment and be reported to the Dean of Students. Also, this homework prepares you for the exam coming up so doing the work now will help you learn and do well on when it counts more.
• What to turn in o Clearly separate your code for each question. Save your code into one SQL file with the naming format: LastName_FirstName_UTEid. Please make sure the lastname and firstname you use matches what is in Canvas.
o Only submissions in .sql or .txt format will be accepted. All other file formats will be given a 0.
• The SQL problems below will be based on the DDL script that is posted on the Canvas instructions. Download that script and run it before you start.

Joins and multi-table SELECT problems:
1. Write a query that returns the first_name, last name, email, and accrued_fees of all patrons from the “Northeast Central” branch. Filter out any patrons with no accrued fees. Sort results to show the patrons with highest fees first. Hint on how to write this query and others in the future. Build step by step. Start by writing a query that joins Location to Patron and pulls all columns. Then add a WHERE to filter down to patrons from the right branch with accrued fees. Then last step is to pick the appropriate columns in your SELECT and add in the Sort (i.e. Order by).

2. Write a query that joins the appropriate tables in order to show the title of a book, number of pages, and the author’s full name. Only include titles that are in ‘Book’ format (i.e. ‘B’) and ignore others like Audiobooks or DVD. Also only pull books that are of the ‘Fiction’ genre (i.e. FIC). I expected something like the following but with more rows. Sort results first by the author_name using the column alias and then by title. FYI: If I don’t specify that the sort is in a certain order or in descending, you can assume it’s an ascending order sort.

……………………………………………………………………………………………….
……………………………………………………………………………………………….

3. Write a query that pulls Title, Format, Genre, and ISBN of all the titles that currently do not have holds. While there are ways to do this using a subquery, I’d like you to use an outer join to accomplish this to show you understand how to properly use them. Sort results by genre and title ascending.

Sort the final result set by Reading_Level and Title. Results should look something like this but with more records.
READING_LEVEL TITLE PUBLISHER NUMBER_OF_PAGES GENRE
… … … … …
… … … … …

Summary Problems:
5. Write a query that pulls patrons and their associated checkouts. Filter records down to just the checkouts that are not flagged as “Late” (i.e. ignore is late_flag is ‘Y’). Summarize the data to return just two columns named as follows:
Zip – the zip column from patron
Average_Accrued_Fees – the average of accrued_fees for each zip. Round the results to nearest 2 decimals. Order results by average_accrued_fee from highest to lowest

6. Find all the titles that have more than 1 author and then return the title, genre, and author_count for that book. Sort your results by genre descending and the title ascending. You’ll need to find the count of authors for each title and then filter out titles that has author counts of 1.
A single row example row could look something like this:
TITLE GENRE AUTHOR_COUNT
The President Is Missing MYS 2

Not done yet! Update the query to add in a filter to only show titles that have authors that include the letters ‘PhD’ in their last_name. This should only return titles that have at least 2 or more authors that are PhDs.

Subquery Problems:
7. Write a SELECT statement that answers this question: Which titles have a higher than average number of pages? i.e.
Just find the books that have a number of pages greater than the average number of pages for all books.
Return the title, publisher, number_of_pages, genre for each title that fit this criteria. Sort the results by the genre ascending and then by number_of_pages decsending.

DataTypes & Function Problems:
9. Please write a checkout status report for patrons that have any checkouts. The report should format the data to be more user-friendly and clear for volunteers to understand. It should return the following four columns:
• PATRON – a combination of the patron’s first and last name
• RETURN_STATUS – If the checkout has not been returned (i.e. date_in is null) show “Not returned yet”, otherwise show “Returned”.
• FEES – Concatenate the text and the patron’s accrued fees while formatting fees as a currency to match examples below
• Sort by retruned_status and due_back_date.
PATRON CHECKOUT_DUE_BACK RETURN_STATUS FEES
… … … …
… … … …
NOTE: This is just an example design. Your results could produce different values and number of rows

10. Write a query that uses the appropriate string parsing functions to return the branch_id, a “District” column based on the first word in the branch_name, and also divides the address into two columns called street number and street name. Street number is the first numbers on the address and everything that follows the first space is considered the street name. Results should look something like the following but for all branches

11. Write a single SELECT that returns the same results that the UNION query did on a #4 above but this time use a CASE statement to dynamically change the reading_level column. Make sure your new query returns the same results as the union query.

12. Write a SELECT statement that pulls all books and their number of checkouts. Include a column called row_number that uses row_number function to create a row number. That means, do not use the rownum pseudo colomn that only works in the WHERE clause. Include all books, even ones that have no checkouts. Results should show the following columns for all titles.
• Popularity_Rank – Use dense_rank based on the count of distinct checkouts.
• Title
• Number_of_Checkouts – Count the distinct checkout_ids attributed to this title if there are any
Note: Since the library system is new at Guillory, they don’t have a lot of checkouts across all the times, so it’s quite possible that some many books will have the same number of checkouts in cases where the titles have only been checked out once. That’s okay. As times goes one and more checkouts occur, this report will become more helpful in identifying popular and unpopular titles.
Once you have your query running, as a final step sort results by number_of_checks descending then title ascending.

After you complete this query, make it an in-line subquery and select * from it like a table but only return row_number 58 like so to reveal one of Clint’s favorite books
Row_Number Title Number_of_Checkouts
58 Man Called Ove 0