TREASURE HUNTER’S RELATIONAL MODEL Player ( username , firstName, lastName, gend

Question

TREASURE HUNTER’S RELATIONAL MODEL

Player (username, firstName, lastName, gender, DOB, email, streetNo, streetName, suburb, state, postcode, creationDateTime, totalPoints)

PhoneNumber (phoneNumber, username)

Treasure (treasureID, description, points, webpage, type, questID)

Quest (questID, questName, story, beacon, advancedQuestID)

Store (storeID, storeName, openTime, closeTime)

Badge (badgeID, badgeName, badgeDescription)

PlayerProgress (questID, username, progress)

PlayerTreasure (username, treasureID)

Purchase (purchaseID, storeID, username, badgeID, purchaseDateTime, cost)

FOREIGN KEYS

                    • PhoneNumber (username) is dependent on Player(username)

                                • Quest (advancedQuestID) is dependent on Quest(questID)

                                • Treasure (questID) is dependent on Quest (questID)

                                • PlayerProgress (questID) is dependent on Quest (questID)

                                • PlayerProgress (username) is dependent on Player (username)

                                • PlayerTreasure (username) is dependent on Player (username)

                                • PlayerTreasure (treasureID) is dependent on Treasure (treasureID)

                                • Purchase (storeID) is dependent on Store (storeID)

                                • Purchase (username) is dependent on Player (username)

                                • Purchase (badgeID) is dependent on Badge (badgeID)

OTHER CONSTRAINTS

                    • Player (gender) must be female, male, other or prefer not to disclose.

                                • Player (state) domain is [QLD, SA, TAS, NSW, WA, NT or ACT].

                                • Treasure (type) domain is [common, uncommon, rare, ultra-rare or elite].

                                • Players may enter up to three phone numbers.

                                • Players must enter at least one phone number.

                                • PlayerProgress (progress) domain is [active, inactive or complete].

                                • Player (email) is mandatory.

Question

Write a command to create a view to list the firstname, lastname and account creation date of any players that haven’t completed any quests.

Explanation / Answer

This will do the trick!
SELECT firstname, lastname, creationDateTime FROM player WHERE username NOT IN(
select username from PlayerPogress WHERE NOT pogress = 'complete'
)

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.