Spring 2005

ITEC 340 Homework 2 - Solution

Use the DESCRIBE and SELECT commands to examine the following tables:

Nascar.Drivers

Nascar.Races

Nascar.Results

Nascar.Teams

Nascar.Cars

Construct a separate script for each of the following. Please use the script name indicated.

H2a.sql The driver name, team name, and finish place of Brickyard 400 finishes, ordered by finish place from best finish to worst finish.

SELECT drivers.driver, team, finish

FROM nascar.drivers INNER JOIN nascar.results

ON nascar.drivers.driver = nascar.results.driver

WHERE race=’Brickyard 400’

ORDER BY finish ASC;

H2b.sql The driver name, and team name of all drivers who drive for a team that runs Chevrolets.

SELECT driver, team

FROM nascar.drivers INNER JOIN nascar.teams

ON nascar.drivers.team = nascar.teams.team

WHERE make=’Chevrolet’;

Or the following nested query will work:

SELECT driver, team

FROM nascar.drivers

WHERE team IN (SELECT team

FROM nascar.teams

WHERE make=’Chevrolet’);

H2c.sql The driver name, race name, and finish place of all results for races that were run at the Pocono Raceway,

in order by race and within race by finish place.

SELECT driver, races.race, finish

FROM nascar.races INNER JOIN nascar.results

ON nascar.races.race = nascar.results.race

WHERE venue=‘Pocono Raceway’

ORDER BY races.race ASC, finish ASC;

Or the following nested query will work:

SELECT driver, race, finish

FROM nascar.results

WHERE race IN (SELECT race

FROM nascar.races

WHERE venue=’Pocono Raceway’);

H2d.sql A list of venues (Tracks) and the number or races planned at each venue.

SELECT venue, count(race) as NumRaces

FROM nascar.races

GROUP BY venue;

H2e.sql The driver name, team name, race name, and venue for all first place finishes.

SELECT drivers.driver, team, races.race, venue

FROM nascar.drivers INNER JOIN nascar.results

ON drivers.driver = results.driver

INNER JOIN nascar.races

ON results.race = races.race

WHERE finish = 1;

H2f.sql The name of any driver that raced at both the Brickyard 400 and the Coca-Cola 600.

SELECT driver

FROM nascar.drivers

WHERE race=’Brickyard 400’

INTERSECT

SELECT driver

FROM nascar.drivers

WHERE race=’Coca-Cola 600’;

H2g.sql The name of any driver that raced at the Brickyard 400 but not the Coca-Cola 600.

SELECT driver

FROM nascar.drivers

WHERE race=’Brickyard 400’

MINUS

SELECT driver

FROM nascar.drivers

WHERE race=’Coca-Cola 600’;

H2h.sql The name of any driver (with no names repeated) who ran a race at the Pocono Raceway.

SELECT DISTINCT driver

FROM nacar.results INNER JOIN nascar.races

ON results.race = races.race

WHERE venue = ‘Pocono Raceway’

Or the following nested query will work:

SELECT DISTINCT driver

FROM nascar.results

WHERE race IN ( SELECT race

FROM nascar.races

WHERE venue=’Pocono Raceway’);

H2i.sql The names of any drivers that have not run in any race.

SELECT driver

FROM nascar.drivers

MINUS

SELECT driver

FROM nascar.results

Or the following nested query will work:

SELECT driver

FROM nascar.drivers

WHERE driver NOT IN (SELECT driver

FROM nascar.drivers NATURAL INNER JOIN nascar.results);

Or the following outer join will work:

SELECT drivers.driver

FROM nascar.drivers LEFT OUTER JOIN nascar.results

ON drivers.driver = results.driver

WHERE race IS NULL

H2j.sql The name of every driver (including those that have not raced at all) and the number of races they ran in,

ordered from the driver who ran in the most races to the drivers that ran in no races.

SELECT driver, count(race) as NumRaces

FROM nascar.drivers LEFT OUTER JOIN nascar.results

ON drivers.driver = results.driver

GROUP BY driver

ORDER BY NumRaces DESC;

Or the following query will work:

SELECT driver, count(race)

FROM nascar.results

GROUP BY driver

UNION

(SELECT driver, 0

FROM nascar.drivers

MINUS

SELECT driver,0

FROM nascar.results)

Place your ten scripts in a folder named RU02 in your ITEC 340 submissions folder by 5:00 on Wednesday February 2nd.