POSSIBLE HELPER BELOW: - MAY OR MAY NOT NEED B) Write and run the following quer
ID: 3906460 • Letter: P
Question
POSSIBLE HELPER BELOW: - MAY OR MAY NOT NEED
B) Write and run the following queries (50 points) 1. Print the player_ids and names of players of Point guards drafted after the year 2000 2. Print the name and city of arenas that hosted a team from LA. Do this query in two ways (i) as a flat select (ii) completely nested, i.e., using sub queries so as to avoid all JOIN clauses Print the names of arenas that hosted a team from Charlotte and hosted a Shooting guard 3. 4. Print the names of players who have the letters 'on' in their name 5. Print the names of arenas that hosted a team from Charlotte but did not host a Shooting guard 6. Print the player_id and average points scored of players who scored an average of points that is greater than the average of points scored in arena 1 7. Print the arena name & city of arenas that did not host the Bulls team 8. Print the names of all retired players 9. Print the names of arena cities that hosted a player whose name begins with a T'Explanation / Answer
If you have any doubts, please give me comment...
-- 1)
SELECT Player_ID, Pname
FROM Player
WHERE Draft_year >2000;
-- 2)
SELECT A.Aname, A.City
FROM ARENA A, TEAM_PLAYER_ARENA TPA, TEAM T
WHERE A.ARENA_ID = TPA.ARENA_ID AND TPA.Team_ID = T.Team_ID AND T.City='LA';
SELECT A.Aname, A.City
FROM ARENA
WHERE ARENA_ID IN(
SELECT ARENA_ID
FROM TEAM_PLAYER_ARENA
WHERE Team_ID IN(
SELECT Team_ID
FROM Team
WHERE City = 'LA'
)
);
-- 3)
SELECT A.Aname
FROM ARENA A, TEAM_PLAYER_ARENA TPA, TEAM T, PLAYER P
WHERE A.ARENA_ID = TPA.ARENA_ID AND TPA.Team_ID = T.Team_ID AND TPA.PlayerID = P.PlayerID AND T.City = 'Charlotte' AND P.Position='Shooting guard';
--4)
SELECT PName
FROM PLAYER
WHERE PName LIKE '%on%';
--5)
SELECT A.Aname
FROM ARENA A, TEAM_PLAYER_ARENA TPA, TEAM T
WHERE A.ARENA_ID = TPA.ARENA_ID AND TPA.Team_ID = T.Team_ID AND T.City = 'Charlotte' AND TPA.Player_ID NOT IN(
SELECT Player_ID
FROM PLAYER
WHERE Position='Shooting guard'
);
-- 6)
SELECT Player_ID, AVG(Points)
FROM TEAM_PLAYER_AREANA
GROUP BY PlayerID
HAVING AVG(Points) > (
SELECT AVG(Points)
FROM TEAM_PLAYER_AREANA
WHERE ARENA_ID = 1
);
--7)
SELECT Aname, City
FROM ARENA
WHERE ARENA_ID NOT IN(
SELECT ARENA_ID
FROM TEAM_PLAYER_ARENA TPA, TEAM T
WHERE TPA.Team_ID = T.Team_ID AND T.Tname ='Bulls'
);
-- 8)
SELECT Pname
FROM PLAYER
WHERE Retire_year IS NOT NULL;
--9)
SELECT Aname
FROM ARENA A, TEAM_PLAYER_ARENA TPA, PLAYER P
WHERE A.ARENA_ID = TPA.ARENA_ID AND TPA.Player_ID = P.Player_ID AND Pname LIKE 'T%';
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.