Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

AD688 Assignment 2

Testing your Knowledge on SQL

Business Problem

A library called Book Lovers & Co. is experiencing difficulties in tracking all book loans. They currently store this data in excel files but would like a more advanced option to meet their data storage needs and have considered the development of a database. They’ve created a simple design of this database and have hired you as a consultant to build this database, add data and to perform simple data manipulations to help them make sense of the current state of the library’s loan transactions.

Data

You’ve been provided with the following .csv files, each containing critical data that the library has collected:

Address.csv

Book.csv

Patron.csv

Loan_Trans.csv

Database Design

The following image has been identified as the design of the library’s database :

 

Assignment Instructions

Assignment 2 contains 5 sections. You are required to complete sections 1 – 4 with Section 5 being optional and is geared to those of who require additional practice to test your SQL skills.

For sections 1 – 4, you are required to answer each question, paste your code and a screenshot of your results in this document. Let’s look at the following example for reference:

Question: Return all values of all the columns in the Book table

Answer:

Select *

From Book;

 

Now it’s your turn! Answer the questions in the sections below.

Section 1: Basic SQL Commands (Create, Insert, Alter, Select)

Note: You will be awarded with 2 points for the successful completion of Section 1.

1. Using SQL, create the following tables for the Book Lovers Library:

a. Book

b. Patron

c. Address

d. Loan transaction

HINT: You should create a database in SQLite first, then proceed to create the tables.

2. Display the structure of the tables that you have just created using SELECT command. Paste a screenshot of all three tables.

3. Insert data into the tables

a. Insert the data found in the book.csv file into the Book table in SQLite

b. Insert the data found in the address.csv file into the Address table in SQLite

c. Insert the data found in the patron.csv file into the Patron table in SQLite

d. Insert the data found in the loan_trans.csv file into the Loan transaction table in SQLite

4. Write a SQL statement to modify the Patron table to include a new column called email. The email column has character field of size 30. (Hint: Use ALTER)

5. Create a table Seniors and copy the records from Patron where age > 50.

6. Customer Camryn George, patron id = 101, just provided us with her email address [email protected]. Write a SQL query to update the library’s database with this information. (HINT: Locate the table that stores customer data and write a query using the UPDATE operator to add this customer’s email address)

7. Write SQL statement to list title and subject for each book in the library.

8. Write SQL statement to display the unique subjects

9. Write SQL statement to list titles of Advertising books

10. List the book with call number 2000

Section 2: Operators (Arithmetic, Boolean, Like, Between, IS NULL, IN)

Note: You will be awarded with 1 point for the successful completion of Section 2.

1. List all loans where the loan fee is greater than $5.

2. List the call numbers of books borrowed by patron 140, where the fine was paid

3. List books with 'Database' in the title (HINT: Use the LIKE operator)

4. List books with title having an 'r' as second character.

5. List books with call numbers between 800 and 1300 (HINT: Use BETWEEN)

6. Create a list of Patrons who have not returned their book. (HINT: Use IS NULL)

7. List the patron id, first name, last name of patrons who have paid the fine and returned the book.

8. List the names of patrons whose patron id is 127, 124, 137, 114 (HINT: Use IN).

9. List all of the computing and Literature titles using IN operator.

10. List the patron id, first name, last name of all patrons born between 1990 and 2020 (HINT: Use BETWEEN)

Section 3: Aggregate Functions (MAX, MIN, SUM, COUNT)

Note: You will be awarded with 2 points for the successful completion of Section 3.

1. List the total number of outstanding books (HINT: Use the COUNT operator. Also, a book is outstanding if it hasn’t been returned as yet).

2. How much has the library collected in fines? (HINT: Use SUM)

3. What is the average fine collected by the Library? (AVG)

4. List the total number of books that have been returned late. Note, books are marked as late if they are not returned ten days after the checkout date.

5. After the library’s leadership reviewed the financial statements, it was uncovered that the patrons who returned their books late have not paid the late fee. Let’s help the library by computing the amount owed. For each patron who returned their book late, perform an arithmetic operation to the determine the amount each patron owes.

a. Write a SQL query to determine the total number of days late per patron

b. If the loan fee has already been paid, the amount owed for an outstanding book is days late * daily late rate which is $5 per day. If the loan fee has not been paid, the amount owed for an outstanding book is loan fee + (days late * daily late rate).

Section 4: Ordering and Grouping (Order By, Group by), Joins

Note: You will be awarded with 2 points for the successful completion of Section 4.

1. List the books in alphabetical order by title. (ORDER BY)

2. List books in subject order, and, with each subject, order them by call number.

3.  For each patron, list the total fines paid and group the list by GROUP BY function. (GROUP BY).

4. Return the total number of books checked out each month for the year 2021

5. Total number of books on loan per subject

6. List the names of patrons, their IDs, and the call numbers of the books they have borrowed. Capture a screenshot and paste it in your submission.  (JOINS)

7. Our customer support agents will reach out to customers who owe the library to retrieve all outstanding funds. You are asked to create a list of these customers and the total amount they owe the library. Return the patron id, first name, last name, email, and total owed. 

Section 5 (OPTIONAL): Analysis for Strategic Decision- Making

1. What are the most popular books in the library?

2. What month had the highest book checkout?

3. List the patrons who have not borrowed any books.

4. Create a visualization to show the trend of rentals per month between 2021 and 2022.