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.