Doing my coursework and wrote the following database:
DROP DATABASE IF EXISTS canary;
CREATE DATABASE canary;
USE canary;
CREATE TABLE member(
memberID INT AUTO_INCREMENT,
firstName VARCHAR(40),
lastName VARCHAR(40),
grade char, -- j, junior, s, senior
PRIMARY KEY(memberID)
);
CREATE TABLE series(
seriesID INT AUTO_INCREMENT,
seriesName VARCHAR(40),
seriesYear INTEGER,
PRIMARY KEY(seriesID)
);
CREATE TABLE race(
raceID INT AUTO_INCREMENT,
seriesID INT,
raceName VARCHAR(40),
raceDate DATE,
FOREIGN KEY series_key(seriesID) REFERENCES series(seriesID),
PRIMARY KEY(raceID),
);
CREATE TABLE competitor(
competitorID INT AUTO_INCREMENT,
memberID INT,
raceID INT,
position INT,
FOREIGN KEY race_key(raceID) REFERENCES race(raceID),
FOREIGN KEY member_key(memberID) REFERENCES member(memberID),
PRIMARY KEY(competitorID),
);
CREATE TABLE course(
courseID INT AUTO_INCREMENT,
courseName VARCHAR(50),
courseLevel INT,
PRIMARY KEY(courseID)
);
CREATE TABLE enrolment(
enrolmentID INT AUTO_INCREMENT,
memberID INT,
courseID INT,
FOREIGN KEY course_key(courseID) REFERENCES course(courseID),
FOREIGN KEY member_key(memeberID) REFERENCES memner(memberID)
PRIMARY KEY(enrolmentID)
);
INSERT INTO series VALUES(NULL, 'BritishSeries', 2020);
INSERT INTO series VALUES(NULL, 'EuropeanSeries', 2020);
INSERT INTO series VALUES(NULL, 'InternationalSeries', 2020);
INSERT INTO course VALUES(NULL, 'WhiteWaterRafting', 2);
INSERT INTO course VALUES(NULL, 'BasicTraining', 1);
INSERT INTO course VALUES(NULL, 'ProfessionalTraining', 3);
INSERT INTO race VALUES (NULL, 1, 'Qualifiers', '2018-04-12')
INSERT INTO race VALUES (NULL, 1, 'Regionals', '2019-05-16')
INSERT INTO race VALUES (NULL, 2, 'Nationals', '2019-07-19')
INSERT INTO race VALUES (NULL, 3, 'Internationals', '2019-09-21')
INSERT INTO member VALUES(NULL, 'Jimmy', 'Smith', senior);
INSERT INTO member VALUES(NULL, 'Macky', 'Gee', junior);
INSERT INTO member VALUES(NULL, 'Tempa', 'T', junior);
INSERT INTO member VALUES(NULL, 'Graeme', 'Maigai', senior);
INSERT INTO member VALUES(NULL, 'Willy', 'Nelson', junior);
SELECT * FROM member;
DELETE FROM race WHERE raceID='3';
INSERT INTO enrolmemnt VALUES(NULL, 1, 2);
-- Member 1
INSERT INTO competitor VALUES(NULL, 1, 2, 3);
INSERT INTO competitor VALUES(NULL, 1, 1, 1);
INSERT INTO competitor VALUES(NULL, 1, 4, 5);
-- Member 2
INSERT INTO competitor VALUES(NULL, 2, 2, 2);
INSERT INTO competitor VALUES(NULL, 2, 1, 3);
INSERT INTO competitor VALUES(NULL, 2, 4, 8);
-- Member 3
INSERT INTO competitor VALUES(NULL, 3, 2, 1);
INSERT INTO competitor VALUES(NULL, 3, 1, 4);
INSERT INTO competitor VALUES(NULL, 3, 4, 3);
-- Member 4
INSERT INTO competitor VALUES(NULL, 4, 2, 5);
INSERT INTO competitor VALUES(NULL, 4, 1, 2);
The question is:
8. List the results of all races in which a given member has participated, use member first name and last name to specify the member. The result should include member first name, last name and grade, series name and year, race name, date and position. The results should be ordered by their position, best position first. If the member has been entered in a race but not yet participated the race should not be included in the list. IDs should not be shown.
So I wrote the code:
SELECT firstName, lastName, grade, seriesName, seriesYear, raceName, raceDate, position FROM member, series, race, competitor
WHERE competitor.memberID = member.memberID ORDER BY position;
But it outputted all the positions as 1 and 2 instead of the race positions.
Please help me!