Description
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.Assignment InstructionsAssignment 2 contains 5 sections. You are required to complete all 5 sections. For each section, you are required to answer each question, paste your code and a screenshot of your results in this document. You are also required to submit your .sql file and code used for section 5. Let’s look at the following example for reference:
Unformatted Attachment Preview
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
Page 1 of 6
The following image has been identified as the design of the library’s database
:
Assignment Instructions
Page 2 of 6
Assignment 2 contains 5 sections. You are required to complete all 5 sections.
For each section, you are required to answer each question, paste your code and a screenshot of
your results in this document. You are also required to submit your .sql file and code used for section
5. 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)
Page 3 of 6
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. 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).
Page 4 of 6
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. (JOINS)
Page 5 of 6
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 : Analysis for Strategic Decision- Making
Note: You will be awarded with 2 points for the successful completion of Section 5.
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.
Page 6 of 6
Purchase answer to see full
attachment
