Answered step by step
Verified Expert Solution
Question
1 Approved Answer
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
- 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.Execute the following changes to the database. Save the command you created. HINT: Many of these changes can be done through MySQLWorkbench by clicking on the wrench symbol in the schema window for the table. MySQLWorkbench will then display the SQL code it generates for the changes you have selected using the table editor.
- Add a new column called Founded of type date to the TEAMS table after the TEAMNO.
- Add a new column called Paid of type char (1) to the PENALTIES table.
- In the PENALTIES table, change the column name AMOUNT to PENALTY_AMOUNT.
- Add a new column called Date of type date to the MATCHES table.
- In the MATCHES table, change the column name MATCHNO to MATCH. MATCH is a keyword but this can be done.
- SELECT * FROM TEAMS;
- SELECT * FROM PENALTIES;
- SELECT * FROM MATCHES;
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;
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started