Project Deliverable #5: Sample DML Queries

These queries answer business related questions relevant to the operation of the website associated with Ken's Library.

SELECT Statement Queries

The following questions are all taken from the project proposal document..

Q1. How many copies are available for a particular work?

Query

This query requires a join. To find out how many copies of Hamlet exist, both the Book and the Content tables are necessary.

Select B.Title, C.Copies FROM Content_T as C JOIN Book_T as B ON B.Title = "Hamlet" AND B.BookID = C.ContentID;

Resultset

Hamlet has 1 copy available.

+--------+--------+
| Title  | Copies |
+--------+--------+
| Hamlet |      1 | 
+--------+--------+
1 row in set (0.00 sec)

Q2. How many different books (in total) are managed by Ken's Library?

Perhaps a librarian needs this information to statistically analyze how the library has grown over time. Or, a patron could simply be curious.

Query

This query requires a simple Count(*) applied to all rows of the Book_T Table.

Select Count(*) as NumberOfBooks From Book_T;

Resultset

Roger likes Spinal Tap so much that he borrowed it twice (the VHS version does not have a media_id_no value).

+---------------+
| NumberOfBooks |
+---------------+
|             1 | 
+---------------+
1 row in set (0.00 sec)

Q3. How many physical books exist in Ken's Library?

This question is a bit more interesting. This question requires a Join of the Book and Content Tables, since the Content table contains the number of copies.

Query

Select SUM(TotalCopies) FROM Book_T AS B JOIN Content_T as C ON B.BookID = C.ContentID;

Resultset

We already saw the list of two borrowed items, so the other four must be available.

+------------------+
| SUM(TotalCopies) |
+------------------+
|                3 | 
+------------------+
1 row in set (0.00 sec)

Q4. Which items am I currently borrowing?

This is a very natural question for a library patron. This requires a triple JOIN between the Patron, Loan, and Content Tables to extract which user loaned which content.

Query

Select C.Title FROM Patron_T as P JOIN Loan_T as L ON P.PatronID = L.PatID AND P.PatronID = 1 JOIN Content_T as C ON L.ContID = C.ContentID;

Resultset

Ken has one item in his possession: Stadium Arcadium

Select C.Title FROM Patron_T as P JOIN Loan_T as L ON P.PatronID = L.PatID AND P.PatronID = 1 JOIN Content_T as C ON L.ContID = C.ContentID;

Q5. Does Ken's Library have any jazz?

A user of the library might want to search for content by genre. Simply Join the Content_T Table with the table storing the type of content the user is looking for.

Query

Select C.Title FROM Content_T C JOIN CD_T D ON C.ContentID = D.CDID AND D.CDGenre = 'Jazz';

Resultset

Empty set (0.00 sec)

Q5b Does Ken's Library have any funk rock?

Query

Select C.Title FROM Content_T C JOIN CD_T D ON C.ContentID = D.CDID AND D.CDGenre = 'Funk Rock';

Resultset

+------------------+
| Title            |
+------------------+
| Stadium Arcadium | 
+------------------+
1 row in set (0.00 sec)

Q6. What copies have I donated?

Donors want some recognition of their generosity.

Query

Select C.Title FROM Patron_T P JOIN Donation_T D ON P.PatronID = D.DonorID JOIN Content_T C ON D.DonatedItemID = C.ContentID;

Resultset

+--------------+
| Title        |
+--------------+
| Harry Potter | 
+--------------+
1 row in set (0.00 sec)

Changes from the Previous Deliverables

There are no changes to the previous deliverables.