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 query that lists all of the badges. If the badge has been purchased include the first name, last name and email address of the player(s). Sort the list based on the badge name followed by first name then last name in ascending order.

Explanation / Answer

select B.badgeName,PL.firstName,PL.lastName,PL.email

from Player PL, Badge B, Purchase PC

where PL.username = PC.username and

B.badgeID = PC.badgeID

order by B.badgeName,PL.firstName,PL.lastName ASC;

Here we are getting details from 3 tables Player , Badge and Purchase

by using instances PL , B , PC and

1st we are joining Player and Purchase based on their common column

PL.username = PC.username

after that we are joining the resultant table with Badge using common column badgeID.

Finally we are sorting based on order by.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

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