(Microsoft Access SQL) I am trying find the UNum for users that played at least

Question

(Microsoft Access SQL) I am trying find the UNum for users that played at least one game played by at least one single user who played at least one game with a ListPrice of $9.99. Based on what the question is asking I belive the that the output should be U10 U30 and U50 but I am not sure why I am getting all Users as output.

SELECT DISTINCT UNum
FROM Plays AS P, Games AS G
WHERE P.GNum = G.GNum
AND P.GNum IN
(SELECT P2.GNum
FROM Plays AS P2
WHERE P2.UNum IN
(SELECT P3.UNum
FROM Plays AS P3
WHERE P3.GNum IN
(SELECT G2.GNum
FROM Games AS G2
WHERE ListPrice = 9.99
)));

Users UNum U10 U20 U30 U40 Plays UNumGNum U10 U10 U20 U20 U20 U20 U20 U20 U20 U20 U30 U30 U40 U40 U50 U50 U50 U50 U50 U50 U50 U50 U50 U50 UserName Masterv Hometown Sheldon Miss Meemaw CNum TimesPlayed To NewYork NewYork To Seattle G100 G100 G300 G300 G300 G300 G300 G300 G300 G500 G300 G400 G600 G600 G100 G200 G200 G300 G400 G500 G500 G500 G600 G600 Games GNum GameName ListPrice Version CityCreated G100IP G200 PacMan G300 Zelda G400 Doom G500 MarioBros 19.99 G600 Pitfall C6 9.99 24.99 19.99 9.99 0 NewYork C2 0 New York To 9.99 Consoles CNum ConsoleName Price ManuLoc NewYork Seattle Atari 99.99 Intellivision 129.99 119.99 Nintendo PlavStation Coleco 89.99Seattle 189.99 129.99 SegaGenesis 129.99 0 0

Explanation / Answer

SELECT DISTINCT UNum
FROM Plays AS P, Games AS G
WHERE P.GNum = G.GNum
AND P.GNum IN
(SELECT P2.GNum
FROM Plays AS P2
WHERE P2.UNum IN
(SELECT P3.UNum
FROM Plays AS P3
WHERE P3.GNum IN
(SELECT G2.GNum
FROM Games AS G2
WHERE ListPrice = 9.99
)));

I am assigning every statement a value

d=SELECT G2.GNum FROM Games AS G2 WHERE ListPrice = 9.99

c=SELECT P3.UNum FROM Plays AS P3 WHERE P3.GNum

b=SELECT P2.GNum FROM Plays AS P2 WHERE P2.UNum

a=SELECT DISTINCT UNum FROM Plays AS P, Games AS G WHERE P.GNum = G.GNum
AND P.GNum

Let's start from the end

a IN (b IN (c IN (d)));

d gives out the output

G100, G400, G600

c gives out the output

U10, U10, U30, U40, U40, U50, U50, U50, U50

b gives out the output

G100, G100, G300, G400, G600, G600, G100, G200, G200, G300, G400, G500, G500, G500,G600, G600

a gives out the output

U10, U20, U30, U40, U50

According to me query d and c are correct. b is incorrect and leads to all users as output. So query b is removed. At last, a is modified to

a=SELECT DISTINCT UNum FROM Plays AS P, Games AS G WHERE P.UNum = G.UNum
AND P.UNum

or you can use

a=SELECT DISTINCT UNum FROM Plays AS P WHERE P.UNum

a IN (c IN (d));

It will give the output as U10, U20, U40, U50

According to me output should contain 4 users.

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

---

---

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