EECS-3421 (A & B) Project #3 Jeopardy: SQL Queries solution

There are ten queries that you are to write. Each is worth one point, for a total of ten points. I am the manager and I provide you for each one with English specifications in the form of a statement, which you will turn into a question — an SQL query against the RR-DB, actually — like in the game show Jeopardy.

Each query is given a name for bookkeeping. A partial answer table for each is provided, along with how many tuples should appear in the answer table in total. This is so you can check whether you have likely implemented the SQL query correctly, by seeing whether you get the same sample tuples when run over the RR-DB database by PRISM’s PostgreSQL server, DB (version 11).

Note that, however, this is not proof that your query is entirely correct! A logically incorrect query with respect to the requirements would give the wrong answers on some instance of the database. And one could “cheat” by making a simple query on purpose that manages to print the same results on this instance of the database!) Grading per query is all-or-nothing. Either your query (always) produces the right results, or it does not. (We likely will test the queries for grading against a different instance of the database! That is, the RR-DB database but with different data.

The queries below are arranged very roughly in order of increasing difficulty. The ones at the beginning are relatively straightforward, while the ones towards the end are relatively difficult.

Some of the queries will be more involved to write than others. Do not get too discouraged if you are unable to do one right away. For some, you will need to look for features of SQL to help. For others, you will need to think carefully about the logic.

Description

Raccoon Rhapsody is a quest-based game.

People who sign up for Raccoon Rhapsody — call them players — have accounts. For each player’s account, we keep track of the player’s name, address, the date that they joined, a cc# (a valid credit card number we keep on file), and a balance (how much money in Canadian dollars they presently have in their game account). Each player will also have a unique login, which is the account’s “name”. It is assumed that a given player will have just one account.

A player may create over time any number of avatars. An avatar is an in-game persona. Thus, an avatar belongs to a player (an account). An avatar has a name, gender, look, and skill level. There is a small set of looks available, which may be extended over time; e.g., “wizard”, “faerie”, “footballer”, “shark”, and “raccoon”.

The game’s virtual word is divided up into a number of realms. On any given day, a player may log in (visit) as one of his or her avatars into one of the realms. (The player may not then switch avatars or realms for that day.) Of course, some days, a player might not log in at all. Realms will be intricate, amazing virtual worlds unto themselves! But, for the purposes of this database, we just need to know the realm’s unique name (realm).

The staff of Questeme — the gaming house that makes Raccoon Rhapsody — creates daily quests, usually ensuring that each realm on each day has several quests available. Each quest on a given day in a realm has a different theme. Themes are repeated over time. Thus, a given quest is associated with a day, a realm, and a theme. Quests will be intricate, amazing challenges! But, for the purposes of this database, we just need to know the quest’s name (quest). The quest’s name is guaranteed to be unique on its day in its realm. (That is, there will not be two quests named the same thing on a given day within a given realm). And, for a quest, we keep the time it was completed, if it was completed.

A player when he or she logs into a realm — as one of his or her avatars — on a given day may just hang out. (Believe me, our realms are amazing! The most amazing realms you’ve ever hung out in!) Or their avatar can join one of realm’s daily quests. We call this acting in a quest. When an avatar (the player as the avatar) joins a quest, they choose a role to play.

Associated with a quest is loot. Each piece of loot is a type of treasure. Each treasure type has a unique name (treasure), and has a worth associated with it in the game’s in-world currency, commonly called “scrip” in gaming parlance. Our in-world currency officially is sql, for Standard Quest Loonie. (Players can turn in pieces of loot they have received for the sql, or keep the loot. The pieces have uses in the game. But this is not modelled so far in the database.)

A quest can have any number of pieces of loot. And, of course, each treasure type can be loot for any number of quests.

If the quest is successfully completed by its “team” — that is, by the avatars signed up for (acting in) the quest — then the quest’s loot is given to those players. (The most famous treasure in the game is a golden lute. Lute. Loot. Get it? Snickers.)

Each piece of loot from the quest is given to just one of the players who participated in the quest (as decided by the game engine), if the quest is successful. We need to record who has been given what. If a quest was successfully completed, we record the finish time. (A quest is just for a given day, so we already know the day.)

Write an SQL query for each of the following with respect to the RR-DB database.

Queries on the York River Bookstore Schema

The York River Bookstore (YRB) schema script is for a small database that is similar to the example discussed in class. (The file yrb-drop is a simple file of SQL drop commands that clears out the YRB database.)

See the file yrb-query for examples of SQL queries over the YRB schema.

As with Project #2, write your queries to work with PostgreSQL version 11.

Due Date

Due by 11:59pm Tuesday 24 November 2020 by electronic submission.

Is the with clause useful for these queries?

Yes! The with clause is a useful way to organize your query into steps. For some queries, one use no need for this; but for others, it can make the query much easier to compose and read.

One may have more “tables” in the with clause, if need be:

with
    first (...) as
        (...),
    second (...) as
        (...),
    third (...) as
        (...)
select ...
    ...;

Note that you can use the (temporary) view first inside the definition for second, and the views first and second inside the definition for third, and so forth.