Several Olympic events are team sports (e.g., basketball, relays) and some involve a pair of athletes (e.g., kayaking, rowing, beach volleyball)
Draw a data model to record these details
SELECT wrk.empfname, boss.empfname, boss.deptname
FROM emp wrk JOIN emp boss
ON wrk.bossno = boss.empno
WHERE wrk.deptname = boss.deptname;
Design a database to record details of all Olympic cities
Add details of the last three summer Olympics’ cities.
Use SQL to determine which city was the host before London in 2008.
INSERT INTO city (cityName, cityNation) VALUES ('Athens', 'Greece');
INSERT INTO city (cityName, cityNation) VALUES ('London', 'United Kingdom');
INSERT INTO city (cityName, cityNation) VALUES ('Beijing', 'China');
INSERT INTO olympics (olympicYear, olympicSeason, olympicNumber, cityName) VALUES (2004, 'Summer', 28, 'Athens');
INSERT INTO olympics (olympicYear, olympicSeason, olympicNumber, cityName, prior_olympicYear, prior_olympicSeason) VALUES (2008, 'Summer', 29, 'Beijing', 2004, 'Summer');
INSERT INTO olympics (olympicYear, olympicSeason, olympicNumber, cityName, prior_olympicYear, prior_olympicSeason) VALUES (2012, 'Summer', 30, 'London', 2008, 'Summer');
SELECT cityName FROM olympics WHERE olympicYear = (SELECT prior_olympicYear
FROM olympics
WHERE cityName = 'London'
AND olympicYear = 2008);
In a round-robin tournament, each contestant meets all other contestants in turn
In the Olympics, it is common for an event with a large pool of contestants to be broken into groups, with a round-robin tournament in each group to determine who advances from the group to the next level
Design a data model to record details of a round-robin competition
Model the following situations
Insert data in the round-robin database for the 2012 Football (Soccer) competition for Group A, with four teams
How many ties were there in Group A?
Use the ISO two-character country code to identify countries.
INSERT INTO group (groupID) VALUES ('A');
INSERT INTO contestant (contestantID, groupID) VALUES ('AE', 'A');
INSERT INTO contestant (contestantID, groupID) VALUES ('GB', 'A');
INSERT INTO contestant (contestantID, groupID) VALUES ('SN', 'A');
INSERT INTO contestant (contestantID, groupID) VALUES ('UY', 'A');
INSERT INTO contest (contestantID1, contestantID2, score1, score2, contestDateTime) VALUES ('AE', 'UY', 1, 2, '20120726');
INSERT INTO contest (contestantID1, contestantID2, score1, score2, contestDateTime) VALUES ('GB', 'SN', 1, 1, '20120726');
INSERT INTO contest (contestantID1, contestantID2, score1, score2, contestDateTime) VALUES ('GB', 'AE', 3, 1, '20120729');
INSERT INTO contest (contestantID1, contestantID2, score1, score2, contestDateTime) VALUES ('SN', 'UY', 2, 0, '20120729');
SELECT count(*) FROM contest WHERE score1 = score2;
Model a diagram.
This page is part of the promotional and support material for Data Management (open edition) by Richard T. Watson |
