Spring 2005

ITEC 340 Homework 1 - 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. Please note that the second character in each script name is the number one not the letter L.

H1a.sql The driver name and team name of all rookie drivers

ordered by team name.

SELECT driver, team

FROM nascar.drivers

WHERE drivertype=’Rookie’

ORDER BY team;

H1b.sql The driver name, finish place, and points earned from the Daytona 500

order by finish place from first to last.

SELECT driver, finish, points

FROM nascar.results

WHERE race='Daytona 500'

ORDER BY finish ASC;

H1c.sql The race name, starting position, finish place, and difference between

starting position and finish place for all the race results of Jeff Gordon

ordered by finish place from best finish to worst finish. Where there

are multiple races with the same finish position order within them by

starting position from worst starting position to best starting position.

SELECT race, startposition, finish, startposition-finish AS movement

FROM nascar.results

WHERE driver=’Jeff Gordon’

ORDER BY finish ASC, startposition DESC;

H1d.sql A non-repeating list of the venues (tracks) in the database.

SELECT DISTINCT venue

FROM nascar.races;

H1e.sql The driver name, race name, and finish place for any race result

where the driver started out of the top ten but finished in the top ten

or any race result where bonus points were awarded.

SELECT driver, race, finish

FROM nascar.results

WHERE bonus>0 OR (startposition > 10 AND finish<=10);

H1f.sql The number of races that Jeff Gordon raced in. (not a list… the number)

SELECT count(race)

FROM nascar.results

WHERE driver=’Jeff Gordon’;

H1g.sql The list of driver names and the number of total number of points they

earned ordered from the driver with the most points to the driver with

the fewest points.

SELECT driver, sum(points) as PointsEarned

FROM nascar.results

GROUP BY driver

ORDER BY PointsEarned DESC;

H1h.sql Same as h1h except only include those drivers that have more than

3000 points.

SELECT driver, sum(points) as PointsEarned

FROM nascar.results

GROUP BY driver

HAVING sum(points) > 3000

ORDER BY PointsEarned DESC;

H1i.sql The driver name, team name, and driver type of the driver that won

the Daytona 500. Do not hard code your query… use a nesting.

SELECT driver, team, drivertype

FROM nascar.drivers

WHERE driver = (SELECT driver

FROM nascar.results

WHERE finish = 1 AND race=’Daytona 500’);

H1j.sql The team name and number of veteran drivers for those teams with more

than two veteran drivers ordered from the team with the most veteran

drivers to the team with the fewest veteran drivers.

SELECT team, count(driver) as NumDrivers

FROM nascar.drivers

WHERE drivertype = ‘Veteran’

GROUP BY team

HAVING count(driver) > 2

ORDER BY NumDrivers DESC;

Place your ten scripts in a folder named RU01 in your ITEC 340 submissions folder by 5:00 on Monday January 24^th.