asked
MS-ACCESS basic queries help
This is the database I am working on: PLAYERS (playerno, name, leagueno, year_of_birth, town, street, year_joined, postcode) TEAMS (teamno, playerno, division) MATCHES ( matchno, teamno, playerno, sets_won, sets_lost) PENALTIES (paymentno, playerno, pendate, amount) I am trying to create three separate queries for this database, but I am struggling: A)Finds the name of players who have more than two penalties and appear in a match and live in ‘Manchester’. B)Determine the name and street of players who were born after the player named ‘Jones’, but live in the same street as ‘Jones’. C)For each player who joined in 1980 and does not live in ‘Manchester’, find the name, the year of birth, the town and the number of penalties incurred by him/her. My attempts are shown below (they are horribly wrong and some aren't finished as I got a bit stuck)Please correct me where I've gone wrong or suggest what operators or functions I should use: A) SELECT name FROM PLAYERS, MATCHES, PENALTIES WHERE PLAYERS.playerno = MATCHES.playerno = PENALTIES.playerno AND town = ‘Manchester’ GROUP BY B) SELECT name, street FROM PLAYERS WHERE street = C) SELECT PLAYERS.name, PLAYERS.year_of_birth, PLAYERS.town FROM PLAYERS, PENALTIES INNERJOIN PLAYERS.playerno = PENALTIES.playerno WHERE town <> ‘Manchester’ AND year_joined = ‘1980’ HAVING COUNT (PENALTIES)
@GPO No this is not homework. I have an exam soon in SQL, this is my practicing for that exam. I can't alter the database, I am tasked with creating queries for that exact database
This is not necessarily MS Access syntax - you're on a SQL Server forum after all. The code is just freehand. It's not tested on anything, but you get the idea. Query A: SELECT plr.NAME as Player_name FROM PLAYERS plr JOIN ( SELECT pnl.playerno FROM PENALTIES pnl GROUP BY pnl.playerno HAVING count(*) > 2 ) pnl_gt_2 ON plr.playerno = pnl_gt_2.playerno WHERE plr.town = 'Manchester' AND EXISTS ( SELECT * FROM MATCHES m WHERE plr.playerno = m.playerno) Query B (In the absence of better instructions the query assumes there might at some point be more than one player whose name is Jones and identifies the oldest one. If tied on age, the first added to the table): SELECT p_not_jones.name ,p_not_jones.street FROM (SELECT TOP 1 p.street ,p.year_of_birth FROM PLAYERS p WHERE p.name = 'Jones' ORDER BY year_of_birth, playerno ) p_jones JOIN PLAYERS p_not_jones ON p_jones.street = p_not_jones.street AND p_jones.year_of_birth < p_not_jones.year_of_birth Query C (don't know whether town is a NULLable column): SELECT plr.name ,plr.year_of_birth ,plr.town ,count(*) as nbr_of_penalies FROM PLAYERS plr join PENALTIES pnl ON plr.playerno = pnl.playerno WHERE plr.year_joined = '1980' AND plr.town 'Manchester' AND plr.town is not null GROUP BY plr.name ,plr.year_of_birth ,plr.town
19 People are following this question.
