Student Name _____Stuart Crome_______________________________________________________
Create the following SQL SELECT statements. If you have added, deleted or changed records within your studies, you will need to create clean database. You will need to drop tennis and reconstruct it from the original instructions in chapter 3 and 4.
While many of these statements might be able to be created without GROUP BY, HAVING, or ORDER BY, this assessment is specifically for these clauses in SQL. You may need to stretch your thinking a little, but be sure that each statement under the GROUP BY header, has a GROUP BY clause; likewise with the HAVING and ORDER by requests.
· Once you have completed each statement, paste the SQL statements into this document along with a screen capture of your results screen. Remember that your statements include the GROUP BY, HAVING and ORDER BY clauses
1. Find the player name and initials as player, the total number of penalties they have incurred for those players living in Stratford.
SELECT P.NAME + P.INITIALS AS PLAYER, COUNT(PEN.PAYMENTNO) AS TOTAL
FROM PLAYERS AS P,
PENALTIES AS PEN
WHERE P.TOWN = 'Stratford' AND P.PLAYERNO = PEN.PLAYERNO
GROUP BY
P.NAME + P.INITIALS
1.
Find the sum of the penalty amounts, the number of
payments and group them by the year that the payment was made.
SELECT SUM(AMOUNT) AS SUM, COUNT(PAYMENTNO) AS TOTAL, YEAR(PAYMENT_DATE) AS YEAR
FROM PENALTIES
GROUP BY YEAR(PAYMENT_DATE)
2. Find the team number and the number of matches where the number of matches won is more than the number matches lost.
SELECT TEAMNO, COUNT(MATCHNO) AS TOTAL_MATCHES
FROM MATCHES
WHERE WON > LOST
GROUP BY
TEAMNO
4.
Find the player number and the sum of the amounts paid
for penalties as total paid where the sum of the amounts is greater than 50.
SELECT PLAYERNO, SUM(AMOUNT) AS TOTAL_PAID
FROM PENALTIES
GROUP BY
PLAYERNO
HAVING SUM(AMOUNT) > 50
5. List all the players by name and initials from Eltham and sort them by postcode.
SELECT NAME, INITIALS
FROM PLAYERS
WHERE TOWN = 'Eltham'
ORDER BY POSTCODE
6. List all the information available from the matches played ordered by the player no in descending order, the number of matches won and the number of matches lost
SELECT *
FROM MATCHES
ORDER BY PLAYERNO DESC, WON, LOST
Turn in:
· You should turn in this Word document with your 6 SQL select statements, and the screen captures.
Where to turn it in:
· Return to Blackboard and turn this in via the link below.