• Use the original Tennis database. Make sure to undo any changes you may have made.
• This will change the database so use ReplaceAndPopulateTennisTables.txt to restore the database to its original content.
• Under Preferences -> SQL Editor you may need to disable Safe Update mode.
• Execute the following changes to the database. Save the command you created.
  1. Add a new penalty to the PENALTIES table; the payment number is 11, this concerns player 44, the payment date was 1985-11-08, and the penalty amount is $100.
  2. Double the amount of all penalties.
  3. Delete the two lowest penalties.
  4. Delete all players who live in the same town as player 57 but keep the data about player 57 (you may need to alias the sub-query (example 17.15) on the town name for 57).
  5. Delete all penalties and matches of player 104, regardless of whether the player appears in both tables.
• Submit the following queries as a file using the below name or paste as a text submission. Make sure to include the commands you created from #4.
  1. SELECT * FROM PENALTIES;
  2. SELECT * FROM MATCHES;
  3. SELECT * FROM PLAYERS;

CREATE DATABASE TENNIS; USE TENNIS; CREATE TABLE PLAYERS (PLAYERNO INTEGER NOT NULL, NAME CHAR(15) NOT NULL, INITIALS CHAR(3) NOT NULL, BIRTH_DATE DATE , SEX CHAR(1) NOT NULL, JOINED SMALLINT NOT NULL, STREET VARCHAR(30) NOT NULL, HOUSENO CHAR(4) , POSTCODE CHAR(6) , TOWN VARCHAR(30) NOT NULL, PHONENO CHAR(13) , LEAGUENO CHAR(4) , PRIMARY KEY (PLAYERNO) ); CREATE TABLE TEAMS (TEAMNO INTEGER NOT NULL, PLAYERNO INTEGER NOT NULL, DIVISION CHAR(6) NOT NULL, PRIMARY KEY (TEAMNO) ); CREATE TABLE MATCHES (MATCHNO INTEGER NOT NULL, TEAMNO INTEGER NOT NULL, PLAYERNO INTEGER NOT NULL, WON SMALLINT NOT NULL, LOST SMALLINT NOT NULL, PRIMARY KEY (MATCHNO) ); CREATE TABLE PENALTIES (PAYMENTNO INTEGER NOT NULL, PLAYERNO INTEGER NOT NULL, PAYMENT_DATE DATE NOT NULL, AMOUNT DECIMAL(7,2) NOT NULL, PRIMARY KEY (PAYMENTNO) ); CREATE TABLE COMMITTEE_MEMBERS (PLAYERNO INTEGER NOT NULL, BEGIN_DATE DATE NOT NULL, END_DATE DATE , POSITION CHAR(20) , PRIMARY KEY (PLAYERNO, BEGIN_DATE)); INSERT INTO PLAYERS VALUES ( 2, 'Everett', 'R', '1948-09-01', 'M', 1975, 'Stoney Road', '43', '3575NH', 'Stratford', '070-237893', '2411'); INSERT INTO PLAYERS VALUES ( 6, 'Parmenter', 'R', '1964-06-25', 'M', 1977, 'Haseltine Lane', '80', '1234KK', 'Stratford', '070-476537', '8467'); INSERT INTO PLAYERS VALUES ( 7, 'Wise', 'GWS', '1963-05-11', 'M', 1981, 'Edgecombe Way', '39', '9758VB', 'Stratford', '070-347689', NULL); INSERT INTO PLAYERS VALUES ( 8, 'Newcastle', 'B', '1962-07-08', 'F', 1980, 'Station Road', '4', '6584WO', 'Inglewood', '070-458458', '2983'); INSERT INTO PLAYERS VALUES ( 27, 'Collins', 'DD', '1964-12-28', 'F', 1983, 'Long Drive', '804', '8457DK', 'Eltham', '079-234857', '2513'); INSERT INTO PLAYERS VALUES ( 28, 'Collins', 'C', '1963-06-22', 'F', 1983, 'Old Main Road', '10', '1294QK', 'Midhurst', '010-659599', NULL); INSERT INTO PLAYERS VALUES ( 39, 'Bishop', 'D', '1956-10-29', 'M', 1980, 'Eaton Square', '78', '9629CD', 'Stratford', '070-393435', NULL); INSERT INTO PLAYERS VALUES ( 44, 'Baker', 'E', '1963-01-09', 'M', 1980, 'Lewis Street', '23', '4444LJ', 'Inglewood', '070-368753', '1124'); INSERT INTO PLAYERS VALUES ( 57, 'Brown', 'M', '1971-08-17', 'M', 1985, 'Edgecombe Way', '16', '4377CB', 'Stratford', '070-473458', '6409'); INSERT INTO PLAYERS VALUES ( 83, 'Hope', 'PK', '1956-11-11', 'M', 1982, 'Magdalene Road', '16A', '1812UP', 'Stratford', '070-353548', '1608'); INSERT INTO PLAYERS VALUES ( 95, 'Miller', 'P', '1963-05-14', 'M', 1972, 'High Street', '33A', '5746OP', 'Douglas', '070-867564', NULL); INSERT INTO PLAYERS VALUES ( 100, 'Parmenter', 'P', '1963-02-28', 'M', 1979, 'Haseltine Lane', '80', '6494SG', 'Stratford', '070-494593', '6524'); INSERT INTO PLAYERS VALUES ( 104, 'Moorman', 'D', '1970-05-10', 'F', 1984, 'Stout Street', '65', '9437AO', 'Eltham', '079-987571', '7060'); INSERT INTO PLAYERS VALUES ( 112, 'Bailey', 'IP', '1963-10-01', 'F', 1984, 'Vixen Road', '8', '6392LK', 'Plymouth', '010-548745', '1319'); INSERT INTO TEAMS VALUES (1, 6, 'first'); INSERT INTO TEAMS VALUES (2, 27, 'second'); INSERT INTO MATCHES VALUES ( 1, 1, 6, 3, 1); INSERT INTO MATCHES VALUES ( 2, 1, 6, 2, 3); INSERT INTO MATCHES VALUES ( 3, 1, 6, 3, 0); INSERT INTO MATCHES VALUES ( 4, 1, 44, 3, 2); INSERT INTO MATCHES VALUES ( 5, 1, 83, 0, 3); INSERT INTO MATCHES VALUES ( 6, 1, 2, 1, 3); INSERT INTO MATCHES VALUES ( 7, 1, 57, 3, 0); INSERT INTO MATCHES VALUES ( 8, 1, 8, 0, 3); INSERT INTO MATCHES VALUES ( 9, 2, 27, 3, 2); INSERT INTO MATCHES VALUES (10, 2, 104, 3, 2); INSERT INTO MATCHES VALUES (11, 2, 112, 2, 3); INSERT INTO MATCHES VALUES (12, 2, 112, 1, 3); INSERT INTO MATCHES VALUES (13, 2, 8, 0, 3); INSERT INTO PENALTIES VALUES (1, 6, '1980-12-08',100); INSERT INTO PENALTIES VALUES (2, 44, '1981-05-05', 75); INSERT INTO PENALTIES VALUES (3, 27, '1983-09-10',100); INSERT INTO PENALTIES VALUES (4,104, '1984-12-08', 50); INSERT INTO PENALTIES VALUES (5, 44, '1980-12-08', 25); INSERT INTO PENALTIES VALUES (6, 8, '1980-12-08', 25); INSERT INTO PENALTIES VALUES (7, 44, '1982-12-30', 30); INSERT INTO PENALTIES VALUES (8, 27, '1984-11-12', 75); INSERT INTO COMMITTEE_MEMBERS VALUES ( 6, '1990-01-01', '1990-12-31', 'Secretary'); INSERT INTO COMMITTEE_MEMBERS VALUES ( 6, '1991-01-01', '1992-12-31', 'Member'); INSERT INTO COMMITTEE_MEMBERS VALUES ( 6, '1992-01-01', '1993-12-31', 'Treasurer'); INSERT INTO COMMITTEE_MEMBERS VALUES ( 6, '1993-01-01', NULL, 'Chairman'); INSERT INTO COMMITTEE_MEMBERS VALUES ( 2, '1990-01-01', '1992-12-31', 'Chairman'); INSERT INTO COMMITTEE_MEMBERS VALUES ( 2, '1994-01-01', NULL, 'Member'); INSERT INTO COMMITTEE_MEMBERS VALUES (112, '1992-01-01', '1992-12-31', 'Member'); INSERT INTO COMMITTEE_MEMBERS VALUES (112, '1994-01-01', NULL, 'Secretary'); INSERT INTO COMMITTEE_MEMBERS VALUES ( 8, '1990-01-01', '1990-12-31', 'Treasurer'); INSERT INTO COMMITTEE_MEMBERS VALUES ( 8, '1991-01-01', '1991-12-31', 'Secretary'); INSERT INTO COMMITTEE_MEMBERS VALUES ( 8, '1993-01-01', '1993-12-31', 'Member'); INSERT INTO COMMITTEE_MEMBERS VALUES ( 8, '1994-01-01', NULL, 'Member'); INSERT INTO COMMITTEE_MEMBERS VALUES ( 57, '1992-01-01', '1992-12-31', 'Secretary'); INSERT INTO COMMITTEE_MEMBERS VALUES ( 27, '1990-01-01', '1990-12-31', 'Member'); INSERT INTO COMMITTEE_MEMBERS VALUES ( 27, '1991-01-01', '1991-12-31', 'Treasurer'); INSERT INTO COMMITTEE_MEMBERS VALUES ( 27, '1993-01-01', '1993-12-31', 'Treasurer'); INSERT INTO COMMITTEE_MEMBERS VALUES ( 95, '1994-01-01', NULL, 'Treasurer'); show tables;