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.
The following six questions are all taken from the project proposal document.
A simple membership question.
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;
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)
Given that Swapaholics.com is all about swaps, it stands to reason that someone would want to know which items are currently being swapped.
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;
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)
Perhaps even more important than knowing what has been borrowed is knowing what items are available to be borrowed.
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;
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)
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.
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;
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)
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.
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;
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)
Advertisers will want to know this so that they can sell copies of those particular items to swappers.
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;
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)
There are no changes to the previous deliverables.