Oracle

SQL Assignment:

As part of this SQL assignment, you are required to design SQL queries that will extract the necessary data from a relational database to generate insights for a Library Management System Dashboard. The dashboard will focus on key areas such as book loans, member management, fines, and library branch inventory. Students are required to utilize the tables created during the Chapter 5 DDL assignment.

1. Book Overview

• Task: Write SQL queries to generate the following metrics:
  1. Total Books in Library: Write a query to count the total number of books across all branches.
  2. Books by Category: Write a query to count how many books exist in each category (for a bar or pie chart).
  3. Available Books per Branch: Write a query that retrieves the quantity of books available at each branch.
  4. Recently Added Books: Write a query that lists books added in the past 30 days.

2. Member Overview

• Task: Write SQL queries to pull member-related insights:
  1. Number of Members: Write a query to count the total number of members in the system.
  2. Active Members: Write a query that retrieves members who have borrowed books in the last 6 months.
  3. Member Loan History: Write a query that retrieves all loans made by each member, showing book titles, loan dates, and return dates (for a table).
  4. Member Fine History: Write a query to list members who have outstanding fines, including fine amounts.

3. Loan Overview

• Task: Use SQL to pull loan-related insights:
  1. Total Loans by Book: Write a query that retrieves the total number of loans for each book (for a bar chart).
  2. Active vs Returned Loans: Write a query to count active loans (loans where return_date is null) versus returned loans.
  3. Overdue Loans: Write a query that lists all overdue loans where the current date is past the due date and return_date is still null.

4. Employee Overview

• Task: Use SQL to manage employee-related data:
  1. Number of Employees per Branch: Write a query to retrieve the number of employees working at each branch.
  2. Employee Loan Activity: Write a query that retrieves the total number of loans issued by each employee.
  3. Employee Salary Report: Write a query to retrieve the salary details for all employees.

5. Branch Overview

• Task: Write SQL queries to generate insights about library branches:
  1. List of Branches: Write a query that lists all branches along with their locations.
  2. Branch Inventory: Write a query that retrieves the total number of books available at each branch.
  3. Branch Loan Activity: Write a query to retrieve total loans per branch, grouped by branch.

**Key Visuals and Corresponding SQL Queries: **

Note: All dashboard queries must work against current month or current year filters

Bar Chart - Collections by Month

• SQL Query: Retrieve the collection amount for each month.
• X-Axis: Month names.
• Y-Axis: Collection Amount.

Pie Chart - Books by Category

• SQL Query: Retrieve the count of books oer category.
• Pie Sections: Book Category and their counts.

Line Graph - User Signups by Month

• SQL Query: Retrieve the number of members registered per month.
• X-Axis: Registration date grouped by month.
• Y-Axis: Number of members.

**Table - Loans Status **

• SQL Query: Retrieve Title, Member Name, Pickup Date, Due Date and Fine Amount.
• Columns: Title, Member Name, Pickup Date, Due Date, Fine Amount.

Gauge - On-time Return VS Overdue

• SQL Query: Count the total number of ontime returns.
• Display: Total returns.

GOOD LUCK WITH YOUR ASSIGNMENT!!!

Don't forget to contact us if you need any further assistance with your assignments, and most importantly, for a manual review and approval of your work.

UX Screens for Library Management System Dashboard Mockup