Project Deliverable #6: Sample DML Queries

These queries answer business related questions relevant to the operation of the Swapaholic.com site. The DML population script supplies the database's business data.

SELECT Statement Queries

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

Q1. What groups are there, and who belongs to each?

A simple membership question.

Query

The query throws in the date the swapper joined the group, just for good measure. The results are sorted first by group, and then by the join date.

SELECT  GroupName, S.Name AS SwapperName, JoinDate
FROM    Swapper_Group_T SG
        INNER JOIN Group_Membership_T GM ON(SG.Name = GM.GroupName)
        INNER JOIN Swapper_T S ON (S.ID = GM.SwapperID)
ORDER   BY GroupName, JoinDate;

Resultset

Roger belongs to both groups.

 GroupName   |       SwapperName        | JoinDate
-------------+--------------------------+------------
 Anybody     | Bartholomew J. Simpson   | 2008-10-01
 Anybody     | Stewie Gilligan Griffin  | 2008-10-05
 Anybody     | Roger the Alien          | 2008-10-10
 Spacefarers | Bender Bending Rodriguez | 2008-10-02
 Spacefarers | Roger the Alien          | 2008-10-09
(5 rows)

Q2. For each group, which items are currently borrowed, and by whom?

Given that Swapaholics.com is all about swaps, it stands to reason that someone would want to know which items are currently being swapped.

Query

This query's a little bit tricky because it shows borrowed items by group. Because a single swapper can belong to many groups, the joins have to loop around and use the SWAPPER table twice: once for the owner of the item (which determines the group), and once for the name of the borrower.

Items that are currently borrowed do not have a ReturnDate value (it is NULL) but do have a non-NULL LoanDate.

Finally, the COALESCE() function in the SELECT clause converts NULL MediaID values to just "N/A" to look a little better.

SELECT  GroupName, Title, COALESCE(MediaID, 'N/A') AS MediaNo,
        LoanDate, SB.Name AS BorrowerName
FROM    Swapper_Group_T SG
        INNER JOIN Group_Membership_T GM ON(SG.Name = GM.GroupName)
        INNER JOIN Swapper_T S ON (S.ID = GM.SwapperID)
        INNER JOIN Item_T I ON (I.OwnerID = S.ID) 
        INNER JOIN Borrow_T B ON (B.ItemID = I.ID)
        INNER JOIN Swapper_T SB ON (B.BorrowerID = SB.ID)
WHERE   LoanDate IS NOT NULL
AND     ReturnDate IS NULL
ORDER   BY GroupName, LoanDate;

Resultset

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

 GroupName   |       Title        |   MediaNo   |  LoanDate   |  BorrowerName
-------------+--------------------+-------------+-------------+-----------------
 Anybody     | This is Spinal Tap | N/A         | 2008-10-21  | Roger the Alien
 Spacefarers | This is Spinal Tap | 6305922756  | 2008-10-11  | Roger the Alien
(2 rows)

Q3. For each group, which items are currently available to be borrowed (i.e., they're not already loaned-out)?

Perhaps even more important than knowing what has been borrowed is knowing what items are available to be borrowed.

Query

The subquery below finds all of the items that are currently unavailable (they've either been borrowed or have already been requested). The main query finds all items that are not in that list, meaning all of the items that are available.

SELECT  GroupName, Title, COALESCE(MediaID, 'N/A') AS MediaNo,
        S.Name AS OwnerName
FROM    Swapper_Group_T SG
        INNER JOIN Group_Membership_T GM ON (SG.Name = GM.GroupName)
        INNER JOIN Swapper_T S ON (S.ID = GM.SwapperID)
        INNER JOIN Item_T I ON (I.OwnerID = S.ID)
WHERE   I.ID NOT IN (
                SELECT  ItemID
                FROM    Borrow_T
                WHERE   ReturnDate IS NULL
        )
ORDER   BY GroupName, Title;

Resultset

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

 GroupName   |               Title                |   MediaNo   |         OwnerName
-------------+------------------------------------+-------------+--------------------------
 Anybody     | Chemistry of Powder and Explosives | 0913022004  | Stewie Gilligan Griffin
 Anybody     | Richard III                        | 0792844041  | Stewie Gilligan Griffin
 Anybody     | The Godfather                      | B0001NBNB6  | Stewie Gilligan Griffin
 Spacefarers | Public Domain Folk Songs           | N/A         | Bender Bending Rodriguez
(4 rows)

Q4. For a given swapper, what is the complete history of his or her swaps?

On the Swapaholic.com site, each swapper would probably want to check what they've borrowed in the past from time to time, if only to avoid arriving home with a video only to realize that they'd already seen it.

Query

For this example, we'll look at Roger the Alien's swapping history. Since the email column of Swapper_T has a UNIQUE constraint, it's the safest way to use it for selecting a single swapper.

This query also needs the Swapper_T table twice: once for the owner (SO) and once for the borrower (SB).

SELECT  LoanDate, ReturnDate, SO.Name AS OwnerName, Title, COALESCE(MediaID, 'N/A') AS MediaNo, Category
FROM    Swapper_T SB
        INNER JOIN Borrow_T B ON B.BorrowerID = SB.ID
        INNER JOIN Item_T I ON I.ID = B.ItemID
        INNER JOIN Swapper_T SO ON SO.ID = I.OwnerID 
WHERE   SB.Email = 'roger@american.dad'
ORDER   BY LoanDate, ReturnDate;

Resultset

Roger is a prolific borrower, but not the best returner.

  LoanDate   |  ReturnDate   |        OwnerName         |               Title                |   MediaNo   | Category
-------------+---------------+--------------------------+------------------------------------+-------------+-----------
 2008-10-05  | 2008-11-08    | Bender Bending Rodriguez | Public Domain Folk Songs           | N/A         | Audio CD
 2008-10-08  | 2008-10-10    | Stewie Gilligan Griffin  | Richard III                        | 0792844041  | DVD Video
 2008-10-08  | 2008-11-02    | Stewie Gilligan Griffin  | Chemistry of Powder and Explosives | 0913022004  | Book
 2008-10-11  |               | Bender Bending Rodriguez | This is Spinal Tap                 | 6305922756  | DVD Video
 2008-10-21  |               | Bartholomew J. Simpson   | This is Spinal Tap                 | N/A         | VHS Video
(5 rows)

Q5. For each swapper in each group, what is each swapper's rating point total?

Each swapper is awarded points for a swap: starting with 3 and then reducing that if the item is returned damaged, and also losing one point for every week past a two-week period that the item is late in being returned. The minimum point value for a single swap is zero.

Query

This one's a bit complicated, so two extra view objects are used to help solve the problem.

The Late_Returned_Swap_V view calculates the point deduction for all late returns. It is composed of two UNION-ed queries. The first query returns the number of weeks late that an already-returned borrow was past the two-week (14 days) grace period. This number is calculated as a negative since it is a points deduction.

The second query finds all outstanding borrows that have not been returned for over two weeks. It uses the SQL special variable current_date to do this.

CREATE VIEW Late_Returned_Swap_V AS
SELECT  ItemID, BorrowerID, RequestDate,
        ROUND(-(ReturnDate - LoanDate - 14) / 7) AS Points
FROM    Borrow_T
WHERE   ReturnDate IS NOT NULL
AND     ReturnDate - LoanDate > 14
UNION
SELECT  ItemID, BorrowerID, RequestDate,
        ROUND(-(current_date - LoanDate - 14) / 7)
FROM    Borrow_T
WHERE   LoanDate IS NOT NULL
AND     ReturnDate IS NULL
AND     current_date - LoanDate > 14;

Selecting the contents of this view shows all late borrows and the point deductions. The first three columns match the primary key of the Borrow_T table so that this view can be used just like the Borrow_T table if needed.

      ItemID       |  BorrowerID |  RequestDate   | Points
-------------------+-------------+----------------+--------
                 1 |           3 | 2008-10-15     |     -2
                 2 |           1 | 2008-10-05     |     -1
                 4 |           3 | 2008-10-08     |     -1
                 5 |           3 | 2008-10-11     |     -4
                 6 |           3 | 2008-10-05     |     -2
(5 rows)

The Borrow_Points_V view calculates the points awarded for each borrow. It uses the Late_Returned_Swap_V view to calculate the points for late returns, and the points column from the Condition_T table to look up any deduction for a damaged item. It then adds these values to the starting point count of 3, and then uses the GREATEST() function to make sure the points total is never less than zero.

The view uses an outer join, in order to calculate the points for all borrows, even those that haven't been returned yet, or that don't have a deduction in the Late_Returned_Swap_V view.

CREATE VIEW Borrow_Points_V AS
SELECT  ItemID, BorrowerID, RequestDate,
        GREATEST(3 + COALESCE(C.Points, 0) + COALESCE(LRS.Points, 0), 0) AS Points
FROM    Borrow_T
        LEFT JOIN Condition_T C ON ReturnCondition = C.RetCondition
        LEFT JOIN Late_Returned_Swap_V AS LRS USING (ItemID, BorrowerID, RequestDate);

This view gives us a list of all borrows (using the same primary key columns as Borrow_T) and the points awarded.

       ItemID      |  BorrowerID |  RequestDate   | Points
-------------------+-------------+----------------+--------
                 3 |           1 | 2008-10-05     |      2
                 6 |           3 | 2008-10-05     |      1
                 2 |           1 | 2008-10-05     |      3
                 3 |           3 | 2008-10-06     |      0
                 1 |           2 | 2008-10-08     |      3
                 4 |           3 | 2008-10-08     |      1
                 5 |           3 | 2008-10-11     |      0
                 1 |           3 | 2008-10-15     |      1
                 1 |           2 | 2008-10-14     |      3
(9 rows)

Finally, we can write the query to answer the original question. Like earlier queries, it has to loop around to deal with swappers that belong to multiple groups. It then sums the points, grouping by group and borrower names. The ORDER BY clause makes sure that, for each group, the swapper with the highest point score is shown first.

SELECT  GroupName, SB.Name AS BorrowerName, SUM(Points) AS TotalPoints
FROM    Swapper_Group_T SG
        INNER JOIN Group_Membership_T GM ON (GM.GroupName = SG.Name)
        INNER JOIN Swapper_T SO ON (SO.ID = GM.SwapperID)
        INNER JOIN Item_T I ON (I.OwnerID = SO.ID)
        INNER JOIN Borrow_Points_V BV ON (BV.ItemID = I.ID)
        INNER JOIN Swapper_T SB ON (SB.ID = BV.BorrowerID)
GROUP   BY GroupName, SB.Name
ORDER   BY GroupName, TotalPoints DESC;

Resultset

Stewie seems to be the best-behaved borrower. Roger is a bad borrower, but is slightly better when participating in the Anybody group.

  GroupName  |       BorrowerName      |   TotalPoints
-------------+-------------------------+--------------
 Anybody     | Stewie Gilligan Griffin |            6
 Anybody     | Bartholomew J. Simpson  |            5
 Anybody     | Roger the Alien         |            2
 Spacefarers | Roger the Alien         |            1
(4 rows)

Q6. For each group, what are the most requested items?

Advertisers will want to know this so that they can sell copies of those particular items to swappers.

Query

Counting the requests is easy enough. The joins are a little easier since we only need to know who owns each item, not who may have borrowed it.

SELECT  GroupName, Title, COALESCE(MediaID, 'N/A') AS MediaNo,
        S.Name AS OwnerName, COUNT(*) AS RequestCount
FROM    Swapper_Group_T SG
        INNER JOIN Group_Membership_T GM ON (GM.GroupName = SG.Name)
        INNER JOIN Swapper_T S ON (S.ID = GM.SwapperID)
        INNER JOIN Item_T I ON (I.OwnerID = S.ID)
        INNER JOIN Borrow_T B ON (B.ItemID = I.ID)
GROUP   BY GroupName, Title, MediaID, S.Name
ORDER   BY GroupName, RequestCount DESC;

Resultset

Everybody loves Spinal Tap, although, oddly, the bootleg VHS version.

  GroupName  |               Title                |   MediaNo   |         OwnerName        |  RequestCount
-------------+------------------------------------+-------------+--------------------------+---------------
 Anybody     | This is Spinal Tap                 | N/A         | Bartholomew J. Simpson   |             3
 Anybody     | Richard III                        | 0792844041  | Stewie Gilligan Griffin  |             2
 Anybody     | The Godfather                      | B0001NBNB6  | Stewie Gilligan Griffin  |             1
 Anybody     | Chemistry of Powder and Explosives | 0913022004  | Stewie Gilligan Griffin  |             1
 Spacefarers | This is Spinal Tap                 | 6305922756  | Bender Bending Rodriguez |             1
 Spacefarers | Public Domain Folk Songs           | N/A         | Bender Bending Rodriguez |             1
(6 rows)

Changes from the Previous Deliverables

There are no changes to the previous deliverables.