Reference no: EM132697417

Case Description and ERD

UT Kermit (in Kermit, TX) maintains a library of free eBooks named FREeBOOKS. FREeBOOKS is a collection of current technology-subject eBooks for use by faculty and students. Agreements with the publishers allow patrons to electronically check out a book, which gives them exclusive access to the book online through the FREeBOOKS website, but only one patron at a time can have access to a book. A book may have never been checked out, but can be checked out many times by the same patron or different patrons over time. Because all faculty and staff in the department are given accounts at the online library, a patron may have never checked out a book or they may have checked out many books over time. Below is the ERD that visually describes this scenario:

1. Write SQL to insert the following records into the AUTHOR table. Using the IN operator, write a query to display these 3 rows only after they have been inserted.

710, Reena, Smooth, null
720, Candy, Kane, 1963
730, Yen, Wang, 1992

##Paste below: 1) copy your SQL code out of SSMS 2) screenshot of the result set displayed

SQL CODE (to both insert and display the result set):

RESULT SET SCREENSHOT:

2. Due to an error, the cost of all books published in 2020 are currently understated by 3.5%. Write a single SQL command to increase the cost by 3.5% of all books from that year. Then write a query to show all columns for all 2020 rows in the BOOK table - sort by BOOK_NUM.
##Paste below: 1) copy your SQL code out of SSMS 2) screenshot of the result set displayed

SQL CODE (to both fix the problem and display the result set):

RESULT SET SCREENSHOT:

3. Write a query that will list only 10 authors, showing the author's name in the following format: "last name, first name" (example: Kane, Candy) - sorted by last name. There should be no spaces between the last name and the comma, one space between the comma and the first name (e.g. Washington, George). Change the column header to be "Author". Only one column should display.
##Paste below: 1) copy your SQL code out of SSMS 2) screenshot of the result set displayed

SQL CODE (to display the result set):

RESULT SET SCREENSHOT:

4. Write a query that will list all books that have the word ‘database' anywhere in the book title. Display all columns. Order by Book year in descending order.
##Paste below: 1) copy your SQL code out of SSMS 2) screenshot of the result set displayed

SQL CODE (to display the result set):

RESULT SET SCREENSHOT:
5. Write a query to display the book title, book year, book subject , author last name and book cost for all books where the author's last name starts with the letter "B" and the book cost is greater than $60. Use table aliases in your SQL. Sort the results in ascending order by cost
Hint: Result set provided
##Paste below: 1) copy your SQL code out of SSMS 2) screenshot of the result set displayed

SQL CODE (to display the result set):

RESULT SET SCREENSHOT:

6. Write a query to show the min, max, and average cost for all books. Change the column headers to be descriptive. Note that this question must be answered after question 2.
##Paste below: 1) copy your SQL code out of SSMS 2) screenshot of the result set displayed

SQL CODE (to display the result set):

RESULT SET SCREENSHOT:

7. Write a query that will list all the books that are currently checked out. List the book number, book title, subject, check out date, check due date and check in date. Sort by book number.
Hint: There are 6 checked out books

##Paste below: 1) copy your SQL code out of SSMS 2) screenshot of the result set displayed

SQL CODE (to display the result set):

RESULT SET SCREENSHOT:

8. Write a query to display all columns for books that are either programming books with the word "cloud" in the title or are books with the word "database" in the title but are not cloud books (i.e. the book subject is not "cloud").
##Paste below: 1) copy your SQL code out of SSMS 2) screenshot of the result set displayed

SQL CODE (to display the result set):

RESULT SET SCREENSHOT:

9. Write a query to display all books that were checked back in late. List the check number, book number, book title, book subject, check due date, check in date and number of days late. Use the DATEDIFF function to calculate the days late and label the column ‘Days Late'. Order the query by the check number. Hint: See question 10 provided result set.

##Paste below: 1) copy your SQL code out of SSMS 2) screenshot of the result set displayed

SQL CODE (to display the result set):

RESULT SET SCREENSHOT:

10. Use the query you wrote in #9 to write a new query that lists counts of late books by book subject. A same-day return is not considered late. Your output should show only 2 columns: BOOK_SUBJECT and ‘Number Late' (a new column you create that shows the aggregated counts). Sort the results by BOOK_SUBJECT.
Hint: Here's what your output should look like:
##Paste below: 1) copy your SQL code out of SSMS

SQL CODE (to display the result set):

Attachment:- Case Description and ERD.rar