Answered step by step
Verified Expert Solution
Link Copied!

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.
    1. Add a new column called Founded of type date to the TEAMS table after the TEAMNO.
    2. Add a new column called Paid of type char (1) to the PENALTIES table.
    3. In the PENALTIES table, change the column name AMOUNT to PENALTY_AMOUNT.
    4. Add a new column called Date of type date to the MATCHES table.
    5. In the MATCHES table, change the column name MATCHNO to MATCH. MATCH is a keyword but this can be done.
    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.
    1. SELECT * FROM TEAMS;
    2. SELECT * FROM PENALTIES;
    3. 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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Accounting And Auditing Research And Databases Practitioner's Desk Reference

Authors: Thomas R. Weirich, Natalie Tatiana Churyk, Thomas C. Pearson

1st Edition

1118334426, 978-1118334423

More Books

Students also viewed these Databases questions

Question

What is MaaS? What does it offer to businesses?

Answered: 1 week ago