ITEC 340

Homework 3

Spring 2005

Homework 3 continues the use of the Nascar Schema:

Construct the following scripts to create two VIEWS and QUERIES that use the views you construct

(h3a.sql)

DriverView1

======================

This view provides more comprehensive driver information. The view should include the driver name, driver type, primary car, primary car main sponsor, team, primary car crewchief, and team automobile make (Chevrolet, Ford, etc). The view should include all 89 drivers.

h3b.sql - a query which returns the driver name, driver type, and main sponsor for all Chevrolets.

h3c.sql – a list of automobile makes and the number of drivers that drive each make.

(h3d.sql)

PointsLeaders

======================

This view will provide quick access to information on the current points leaders. It should not be hard coded… that is to say if the points leader changes the view should reflect that. The view should include all columns from the driver table plus the total number of races run, total points earned, and total dollar winnings for all drivers with more than 3000 points. This is not an easy query which is why a view would be convenient to have. You will need to make sure to rename your sum and count columns because names like sum(x) are not allowed in a view.

h3e.sql – the driver name, driver type, number of races run, total points earned, and total dollar winnings for all points leaders.

h3f.sql – the driver name, driver type, number of races run, total points earned, and total dollar winnings for the driver with the most points.

Create a folder named RU03in your submissions folder on Neelix. Place your scripts in that folder by 5 PM Friday, February 11^th.