Moderated Memo: SQL 2017

Moderated memo: SQL 2017

1.SELECT tblPlayers.PlayerName, tblPlayers.PlayerId, tblPlayers.SchoolId
FROM tblPlayers
ORDER BY tblPlayers.PlayerName;
OR:
SELECT PlayerName, PlayerId, SchoolId
FROM tblPlayers
ORDER BY PlayerName;(3)

2.SELECT Count (tblSchools.Name) AS CountOfName, tblSchools.Girls
FROM tblSchools
GROUP BY tblSchools.Girls;(4)

3.SELECT tblPlayers.PlayerName, tblSchools.Name
FROM tblSchools INNER JOIN tblPlayers 
ON tblSchools.SchoolId = tblPlayers.SchoolId
WHERE (((Mid ([PlayerName],2,1))=" ")); (1 for mid; 1 for rest of statement)
OR:
SELECT PlayerName, Name
FROM tblPlayers, tblSchools
WHERE tblSchools.SchoolId = tblPlayers.SchoolId
AND (((Mid ([PlayerName],2,1))=" ")); (1 for mid; 1 for rest of statement)(5)

4.SELECT top 10tblPlayers.PlayerName,
Sum(tblGames.Games) AS SumOfGames,
Sum(tblGames.Point) AS SumOfPoint, sum both fields alias both fields
FROM tblPlayers
INNER JOIN tblGames correct two tables
ON tblPlayers.PlayerId = tblGames.PlayerId 
GROUP BY tblPlayers.PlayerName 
ORDER BY Sum(tblGames.Games) DESC, Sum(tblGames.Point) DESC;
sum(games) then sum(points)  both DESC
OR:
SELECT top 10PlayerName,
Sum(Games) AS SumOfGames,
Sum(Point) AS SumOfPoint, sum both fields alias both fields
FROM tblPlayers, tblGames correct two tables
WHERE tblPlayers.PlayerId = tblGames.PlayerId 
GROUP BY PlayerName 
ORDER BY Sum(Games) DESC, Sum(Point) DESC;
sum(games) then sum(points)  both DESC(8)

5.SELECT tblGames.gameNo, tblSchools.Name
FROM tblSchools
INNER JOIN (tblPlayers INNER JOIN tblGames all three tables
ON tblPlayers.PlayerId = tblGames.PlayerId)
ON tblSchools.SchoolId = tblPlayers.SchoolId
GROUP BY tblGames.gameNo, tblSchools.Name;
OR:
SELECT tblGames.gameNo, tblSchools.Name
FROM tblSchools, tblPlayers, tblGames all three tables
WHERE tblPlayers.PlayerId = tblGames.PlayerId
AND tblSchools.SchoolId = tblPlayers.SchoolId
GROUP BY tblGames.gameNo, tblSchools.Name;(6)

6.SELECT PlayerName, (Now()-dateofbirth)/365.25 AS Age
FROM tblPlayers
WHERE (Now()-dateofbirth)/365.25 Between 14 And 18; (5)

7.SELECT tblPlayers.PlayerName
FROM tblPlayers
LEFT JOIN  tblGames correct two tables
ON tblPlayers.PlayerId = tblGames.PlayerId
WHERE tblPlayers.PlayerId NOT IN (SELECT tblGames.PlayerId FROM tblGames);
OR:
SELECT tblPlayers.PlayerName, 
FROM tblPlayers
LEFT JOINtblGames correct two tables
ON tblPlayers.PlayerId = tblGames.PlayerId
WHERE tblGames.Games IS NULL;(7)

8.INSERT INTO tblplayers (PlayerName, dateOfBirth, SchoolId) (if ID included)
SELECT "Sasha Bloem" , #1/2/2000#, tblSchools.SchoolId  (-1 for each error)
FROM tblSchools
WHERE tblSchools.Name="Festival Invitational" 
AND tblSchools.Girls=Yes;(6)

9.DELETE 
FROM tblPlayers
WHERE playerId=77 (if other criteria used)(3)

10.UPDATE tblSchools
SET Games = 0,  Points = 0; (if “and” is used in place of , )
max 2 marks if there is a where clause included(3)