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 finishtime. (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. 1. myself List each player whose login is part of his or her name; i.e., his or her login is a substring of his or her name. This should be case insensitive; e.g., “thom” is a substring of “Thomas Kane”. schema: login, name, gender, address, joined order by login (asc) answer table: myself 5. bend List each player by login, name, and gender who gender swapped at least once with their avatars, along with the count of how many avatars that he or she has (avatars). schema: login, name, gender, avatars order by login answer table (first three records): bend 6. successful Select the themes (theme) for which the quests were always successful, and report the number of successful quests (quests) for each such. schema: theme, quests order by theme answer table (first three records): successful 7. frequency Report the average number of days (as frequency) between visits to each given realm for each player. Also show the number of visits (visits) to that realm for the player. (Ignore a player in a realm if the player has never visited it or has only visited it once; the frequency is not defined in such cases.) notes Cast frequency with precision five and scale two. schema: login, realm, visits, frequency order by login, realm answer table (first 12 records): frequency
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
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 finishtime. (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.
1. myself
List each player whose login is part of his or her name; i.e., his or her login is a substring of his or her name. This should be case insensitive; e.g., “thom” is a substring of “Thomas Kane”.
schema: login, name, gender, address, joined
order by login (asc)
answer table: myself
5. bend
List each player by login, name, and gender who gender swapped at least once with their avatars, along with the count of how many avatars that he or she has (avatars).
schema: login, name, gender, avatars
order by login
answer table (first three records): bend
6. successful
Select the themes (theme) for which the quests were always successful, and report the number of successful quests (quests) for each such.
schema: theme, quests
order by theme
answer table (first three records): successful
7. frequency
Report the average number of days (as frequency) between visits to each given realm for each player. Also show the number of visits (visits) to that realm for the player. (Ignore a player in a realm if the player has never visited it or has only visited it once; the frequency is not defined in such cases.)
notes
- Cast frequency with precision five and scale two.
schema: login, realm, visits, frequency
order by login, realm
answer table (first 12 records): frequency
Step by step
Solved in 8 steps
