Hit 234 Assignment 1 Submission Details For Internal And Ext

Hit 234 Assignment 1submission Detailsfor Internal And External Stude

Hit 234 Assignment 1Submission Details for Internal and External students: submission is via Learnline. Instructions on how to submit the assignment are located under Assessments / Guidelines. Please answer all questions. Part A - Single and multiple table queries Value: 6%. For questions 1 to 5, provide your answer in the form of a SQL statement, with description before as necessary. Questions 6 requires creating a question based on the library database, with a SQL statement answer. Part B – Table and Advanced Queries Value: 4%. Provide SQL statements for creating and inserting data into tables, focusing on the `Occurrence_Exercise` table. Questions involve calculating total weights lifted and identifying exercises with incomplete attempts. Part C – All Topics Value: 10%. Questions involve querying the library database for popular books, inactive branches, and loan durations, as well as duplicate removal in the CDU Customer Invoice Database.

Paper For Above instruction

Hit 234 Assignment 1submission Detailsfor Internal And External Stude

Introduction

This assignment encompasses a range of SQL query tasks centered on database structures relevant to library management, gym activities, and customer invoicing. The tasks aim to demonstrate proficiency in writing complex queries, creating and manipulating tables, and understanding relationships within databases. The following paper systematically addresses each question, providing detailed SQL statements, explanations, and interpretations to showcase mastery of database querying techniques for academic and practical applications.

Part A: Single and Multiple Table Queries

Question 1: Listing Borrowers by Card Number

The first task requires retrieving the last names of all borrowers with a card number less than 150, sorted in ascending order. This task emphasizes basic SELECT statements and filtering criteria.

-- List in ascending order the Last Names of all borrowers with Card Number less than 150
SELECT LastName
FROM Borrowers
WHERE CardNumber 
ORDER BY LastName ASC;

This SQL query selects the LastName column from the Borrowers table where the CardNumber is below 150, ordering the results alphabetically by last name.

Question 2: Loans within a Date Range

Next, identify records of loans occurring between June 3, 2014, and March 8, 2018, including the cardholder's name and book title, sorted by last name and first name.

-- Retrieve loans between specified dates with borrower names and book titles
SELECT b.LastName, b.FirstName, bk.Title
FROM Loans l
JOIN Borrowers b ON l.CardNumber = b.CardNumber
JOIN Books bk ON l.BookID = bk.BookID
WHERE l.LoanDate BETWEEN '2014-06-03' AND '2018-03-08'
ORDER BY b.LastName ASC, b.FirstName ASC;

This statement joins Loans with Borrowers and Books, filtering by date range and ordering by borrower names.

Question 3: Counting Loans for Last Names Starting with D–E, Q, and Z

The task calculates the total number of loans for borrowers with last names starting with specific letters, including ranges and individual characters.

-- Count total loans for last names beginning with D-E, Q, and Z
SELECT COUNT(*) AS TotalLoans
FROM Loans l
JOIN Borrowers b ON l.CardNumber = b.CardNumber
WHERE b.LastName LIKE 'D%' OR b.LastName LIKE 'E%' OR b.LastName LIKE 'Q%' OR b.LastName LIKE 'Z%';

Question 4: Borrowers with Specific Conditions

Identify borrowers with a loan date before March 15, 2015, and card numbers between 100 and 300, located in Karama or Darwin.

-- Find borrowers with loans before a specific date, in specified locations, and within certain card numbers
SELECT b.*
FROM Borrowers b
JOIN Loans l ON b.CardNumber = l.CardNumber
WHERE l.LoanDate 
AND b.CardNumber BETWEEN 100 AND 300
AND (b.Location = 'Karama' OR b.Location = 'Darwin');

Question 5: Loans per Branch

Determine the number of loans associated with each branch, including branches with no loans.

-- Count number of loans per branch, including branches with zero loans
SELECT br.BranchName, COUNT(l.LoanID) AS LoanCount
FROM Branches br
LEFT JOIN Loans l ON br.BranchID = l.BranchID
GROUP BY br.BranchName
ORDER BY br.BranchName ASC;

Question 6: Creating a Custom Question

Formulate your own question about the library database, along with a corresponding SQL query. For example, the following asks for borrowers who have borrowed more than five books.

-- Custom Question: Find borrowers who have borrowed more than five books
-- List borrower names and their total number of borrowed books
SELECT b.LastName, b.FirstName, COUNT(l.BookID) AS NumberOfBooks
FROM Borrowers b
JOIN Loans l ON b.CardNumber = l.CardNumber
GROUP BY b.LastName, b.FirstName
HAVING COUNT(l.BookID) > 5;

Part B: Table and Advanced Queries

Question 1: Creating and Inserting Data into Occurrence_Exercise Table

This task focuses solely on constructing the table and inserting relevant data. The table captures gym exercise occurrences, conditions, and attempts.

-- Create the Occurrence_Exercise table with constraints on Condition column
CREATE TABLE Occurrence_Exercise (
OccExID INT PRIMARY KEY,
ExerciseNo VARCHAR(10),
OccurrenceID INT,
Rep INT,
Attempt1 INT,
Attempt2 INT,
Attempt3 INT,
Condition VARCHAR(10) CHECK (Condition IN ('Heavy', 'Medium', 'Light'))
);

Question 2: Inserting Data into Occurrence_Exercise Table

Insert sample data into the above table based on the provided gym activity records.

-- Sample data insertion into Occurrence_Exercise table
INSERT INTO Occurrence_Exercise (OccExID, ExerciseNo, OccurrenceID, Rep, Attempt1, Attempt2, Attempt3, Condition) VALUES
(1, '001A', 1, 10, 15, 15, 15, 'Light'),
(2, '002A', 2, 8, 18, 18, NULL, 'Medium'),
(3, '003A', 3, 12, 20, 20, 20, 'Heavy'),
(4, '004A', 4, 10, 60, 60, NULL, 'Medium'),
(5, '005A', 5, 15, 30, NULL, NULL, 'Heavy');

Question 3: Max Total Weight Lifted per Exercise

Calculate which exercise has the highest total weight lifted, considering repetitions, attempts, and weights.

-- Find the exercise with the maximum total weight lifted
SELECT e.ExerciseDescription, MAX(TotalWeight) AS MaxWeight
FROM (
SELECT oe.ExerciseNo, SUM(oe.Rep  CASE WHEN oe.Attempt1 IS NULL THEN 0 ELSE oe.Attempt1 END  15
+ CASE WHEN oe.Attempt2 IS NULL THEN 0 ELSE oe.Attempt2 END * 15
+ CASE WHEN oe.Attempt3 IS NULL THEN 0 ELSE oe.Attempt3 END * 15) AS TotalWeight
FROM Occurrence_Exercise oe
JOIN Exercise e ON oe.ExerciseNo = e.ExerciseNo
GROUP BY oe.ExerciseNo
) AS Subquery
JOIN Exercise e ON Subquery.ExerciseNo = e.ExerciseNo
GROUP BY e.ExerciseDescription
ORDER BY MaxWeight DESC
LIMIT 1;

Question 4: Exercise with Incomplete Attempts or No Attempts

Identify exercises where the user failed to complete 3 attempts or did not attempt at all, ordered by exercise type.

-- Find exercises with attempted attempts less than three times
SELECT e.ExerciseDescription, e.ExerciseType
FROM Exercise e
LEFT JOIN (
SELECT ExerciseNo, COUNT(*) AS AttemptCount
FROM Occurrence_Exercise
WHERE Attempt1 IS NOT NULL AND Attempt2 IS NOT NULL AND Attempt3 IS NOT NULL
GROUP BY ExerciseNo
) AS Attempts ON e.ExerciseNo = Attempts.ExerciseNo
WHERE Attempts.AttemptCount 
ORDER BY e.ExerciseType ASC;

Part C: All Topics

Question 1: Top 5 Most Popular Books

Retrieve the ISBN and Titles of the five most borrowed books.

-- Select top 5 most popular books based on loan count
SELECT b.ISBN, b.Title
FROM Books b
JOIN Loans l ON b.BookID = l.BookID
GROUP BY b.ISBN, b.Title
ORDER BY COUNT(l.LoanID) DESC
FETCH FIRST 5 ROWS ONLY;

Question 2: Branches with No Loans

List all branch names that currently have no books loaned out.

-- Find branches with no current loans
SELECT BranchName
FROM Branches
LEFT JOIN Loans l ON Branches.BranchID = l.BranchID
WHERE l.LoanID IS NULL
ORDER BY BranchName ASC;

Question 3: Longest Loan Duration per Branch

Determine the maximum number of days a book has been loaned out in each branch, even if no loans exist.

-- Calculate maximum loan duration per branch
SELECT br.BranchName, 
COALESCE(ROUND(MAX(DATEDIFF(day, l.LoanDate, l.ReturnDate)), 1), 0) AS LongestLoanDays
FROM Branches br
LEFT JOIN Loans l ON br.BranchID = l.BranchID
GROUP BY br.BranchName
ORDER BY br.BranchName ASC;

Question 4: Borrowers with Specific Return Dates

Find borrowers who returned a book on two specific dates, using two different methods.

-- Method 1
SELECT DISTINCT b.*
FROM Borrowers b
JOIN Loans l ON b.CardNumber = l.CardNumber
WHERE l.ReturnDate = '2010-12-16' OR l.ReturnDate = '2012-05-08';
-- Method 2
SELECT b.*
FROM Borrowers b
JOIN Loans l ON b.CardNumber = l.CardNumber
WHERE l.ReturnDate IN ('2010-12-16', '2012-05-08');

Question 5: Removing Duplicate Customer Invoices

Devise a delete statement to remove duplicate entries based on customer ID, names, invoice date, and invoice number, ensuring all duplicates are eliminated.

-- Delete duplicate invoices, keeping one record
DELETE FROM CustomerInvoices
WHERE rowid NOT IN (
SELECT MIN(rowid)
FROM CustomerInvoices
GROUP BY ID, CustomerLastName, CustomerFirstName, Invoice_Date, Invoice_No
);

References

• Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems. Pearson.