Worth 3 Of The Total Marks: List In Ascending Order

Worth 3 Of The Total Marks 1 List In Ascending Order The Last Names

1. List in ascending order the Last Names of all borrowers with Card Number less than 150.

2. What are the records of those loans between 3 June 2014 and 8 March 2018?

  • The output should include the name of the cardholder, and title of the book.
  • List in ascending order by last name and first name.

3. Find the total number of loans with last names beginning with D through E (including E); include last names beginning with Q as well as Z.

4. Find all borrowers for a loan that have the Date-in before 15 March 2015 and the Card number between 100 and 300 in Karama or Darwin.

5. Find the number of loans that have been made from each branch, including branches with no loans.

Paper For Above instruction

Introduction

This paper addresses specific database queries concerning a library management system. The tasks involve retrieving and analyzing data related to borrowers, loans, and branches, with particular emphasis on sorting, filtering, and aggregation. These queries are designed to extract meaningful insights from the database, facilitating better understanding of borrower activity, loan distribution, and branch performance. The analysis employs structured query language (SQL) to perform the necessary data manipulations, consistent with best practices in relational database management.

1. Listing Borrowers by Last Name for Card Numbers Less Than 150

The first query aims to list all borrowers whose card numbers are below 150. This involves selecting the last names from the borrower table where the card number is less than 150. The results are ordered in ascending order. This operation helps identify a specific subset of borrowers, perhaps to target communication or analyze borrowing patterns among a particular group.

SQL implementation:

SELECT LastName

FROM Borrowers


WHERE CardNumber 

ORDER BY LastName ASC;

Such filtering provides quick insight into the demographics of borrowers within a specific card number range, which can be useful for targeted marketing or service improvements.

2. Loans Between 3 June 2014 and 8 March 2018

The second query seeks records of loans that occurred within a specified date range. The relevant fields include the cardholder's name and the title of the borrowed book. The results are sorted in ascending order by last name and first name, facilitating easy review of borrower activity over this period.

Key considerations for the query include joins between the loans, borrowers, and books tables to fetch comprehensive information.

SQL implementation:

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 query allows librarians and administrators to analyze borrowing trends, identify high activity periods, and possibly detect overdue patterns.

3. Counting Loans with Surnames Starting from D to E and Including Q and Z

The third query involves counting the total number of loans associated with borrowers whose last names start with specific letters. The specified range includes D through E, and additionally Q and Z.

This operation quantifies activity among these surname groups, which may correspond to demographic analysis or targeted outreach efforts.

Implementation approach includes aggregation via COUNT, filtering with LIKE operators for the specified initials, and combining conditions using OR.

SQL implementation:

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%';

4. Borrowers with Specific Loan and Card Number Criteria in Certain Locations

For the fourth scenario, the goal is to find all borrowers for loans where the Date-in is before 15 March 2015, with card numbers between 100 and 300, and in specific branches ('Karama' or 'Darwin').

This requires joining the loans, borrowers, and branch tables, applying multiple conditions on date, card number, and branch location.

Implementation approach includes filtering the relevant tables with WHERE clause conditions.

SQL implementation:

SELECT B.LastName, B.FirstName, L.LoanDate, BR.BranchName

FROM Loans L


JOIN Borrowers B ON L.CardNumber = B.CardNumber


JOIN Branches BR ON L.BranchID = BR.BranchID


WHERE L.DateIn 

AND B.CardNumber BETWEEN 100 AND 300


AND BR.BranchName IN ('Karama', 'Darwin');

5. Loan Counts Per Branch

The final query assesses the number of loans originating from each branch, including branches with no loans. This requires a LEFT JOIN between branches and loans, grouping by branch, and counting the loans.

Such an analysis helps identify branch activity levels and can inform resource distribution and management decisions.

Implementation:

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;

Conclusion

This analytical approach demonstrates how structured SQL queries can effectively extract insights from a library management database. By performing targeted filtering, sorting, and aggregation, library administrators can gain a comprehensive understanding of borrower activity, loan patterns, and branch performance. These insights facilitate data-driven decision-making, improve operational efficiency, and enhance service delivery to patrons. Future work could involve automating these queries for regular reporting and extending analysis to include additional parameters like overdue loans or demographic profiling.

References

  • Elmasri, R., & Navathe, S. B. (2015). Principles of Database Systems (7th ed.). Addison-Wesley.

Related Assignments