Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Hello, I need help with these MySQL statements: 1) Delete the two lowest penalties. 2) Delete all penalties and matches of player 104, regardless of

Hello, I need help with these MySQL statements:

1) Delete the two lowest penalties.

2) Delete all penalties and matches of player 104, regardless of whether the player appears in both tables.

I'm using the tennis database, but you may not need it:

USE TENNIS; DROP TABLE PLAYERS; DROP TABLE TEAMS; DROP TABLE MATCHES; DROP TABLE PENALTIES; DROP TABLE COMMITTEE_MEMBERS; 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

Flash XML Applications Use AS2 And AS3 To Create Photo Galleries Menus And Databases

Authors: Joachim Schnier

1st Edition

0240809173, 978-0240809175

More Books

Students also viewed these Databases questions