Description
Martians have infiltrated the summer olympics by planting Martian althetes as athetes
belonging to various countries. As in assignment 8, you have been hired by the martian
olympic committee to review the results.
You are to do a number of queries for a database which contains information on the
Martian infiltrators with the following tables:
CREATE TABLE Athlete (
athleteId int,
athlete_Name varchar(15),
sport varchar(15),
gender char(1),
country varchar(15),
primary key (athleteID)
);
CREATE TABLE Doping (
athleteId int,
test int,
outcome varchar(15),
primary key (athleteID,test)
);
CREATE TABLE Results (
event varchar(20),
sport varchar(15),
athleteID int,
medal varchar(6),
primary key (event,sport)
);
The Athelete table records the Information about the Martian athletes that have been
planted. Likewise the Doping table contain only information about planted Martian athletes. The Results table records information about martian athletes who have medaled.
Below are a number of queries. For each query, express the query in MySQL. Submit a
text document that contains the MySQL queries executed and their output in interactive
output format. This can be done in batch mode or via a mysql script, but for this small
assignment it is probably just as easy to copy the command and output from your
interactive window running mysql and paste it into a text document. Note that you
will probably want to initially type your commands/queries into a second text file, so
that when an error occurs it is easy to edit them and recopy them into the interactive
window to try them again. In order to execute your MySQL queries, you will need to
first create the 3 tables. In addition, you will need to fill the tables with their data. With
this assignment, there are four text files: Olym-def.txt, Athetedata.txt, Dopingdata.txt
and Resultsdata.txt. The first contains the sql commands to create the tables, and the
other three contain test data to be loaded into the three tables. The way to load the
Results data is as follows:
load data local infile ’Resultsdata.txt’ into table Results;
assuming that the file Resultsdata.txt is in the current directory (log into mysql when
in the directory that contains the files). The other two are similar. The mysql system
expects that the files use LF for end-of-line, and the tab character is used to separate
the attribute values of one tuple. The data files have been set up in this way, so be
careful not to change these. Of course, before you start your queries, you should output
the description and contents of each table to ensure that everything was set up correctly.
The required queries follow:
1. Which countries have had infiltrating Martian athletes produce positive doping
tests?
2. What are the names of the Martian athletes who tested postitive and medaled?
3. How many women Martian athletes have won Bronze medals?
4. What are the names of the Martian athletes that tested postive in one doping test
and negative in another?
