A couple quick SQL questions

[Slater]

Hiya, folks. I'm taking an intro-to-databases course this semester, and I'm having a little trouble with a couple of questions on my homework. I'm hoping that there's someone in here who has a little more experience with this than I do...

Both of these questions have to be programmed using nothing but SQL queries; while I will be using JDBC/ODBC to implement them, my instructor insists that we come up with the answers using just raw SQL and the SQL aggregate functions (MAX, MIN, AVG, etc...).

So, let me set the two problems up. They both refer to the same database tables...

Consider the following schema:
Sales_Rep (name, dept, phone)
Automobile (vin, make, model, color, seller, purchased_by)
FK ["Foreign Key"] seller references Sales_Rep
FK purchased_by references Customer
Customer (license, cname, address, phone)
Test_Drives (vin, license, date)
FK vin references Automobile
FK license references Customer

If this isn't clear in English, here's the exact SQL queries I executed to create the tables:

Code: Select all

create table Sales_Rep (
name varchar(10),
dept varchar(10),
phone varchar(10),
Primary Key(name)
);


create table Customer (
license varchar(10),
cname varchar(10),
address varchar(10),
phone varchar(10),
Primary Key(license)
);


create table Automobile (
vin varchar(10),
make varchar(10),
model varchar(10),
color varchar(10),
seller varchar(10),
purchased_by varchar(10),
Primary Key(vin),
Foreign Key(seller) references Sales_Rep(name),
Foreign Key(purchased_by) references Customer(license)
);

create table Test_Drives (
vin varchar(10),
license varchar(10),
date date,
Primary Key(vin, license),
Foreign Key(vin) references Automobile(vin),
Foreign Key(license) references Customer(license)
);


So that's the setup I'm running with on these questions. And so... here are the questions.

"Find the average number of cars that each sales_rep sells."
And...
"For each car, find the customer who has driven that car the maximum number of times."

For the first one, I think that I'd like to count the number of cars being sold and divide that by the number of sales_reps... but I'm not sure how to do that in SQL. (obviously, this would be trivial if I did it in the Java program, but I'm not supposed to do it that way.)

The second one, I'm really not sure how to go about it. I know that I should probably use a GROUP-BY-clause to get all the rows grouped based on who test-drove the cars, but how to figure out which person's license appears most frequently in the table is beyond me.

Any tips on this would be greatly appreciated!

[Kaligraphic]

For the first one, I'd look at count() and avg().

For the second, I might use count() and max(). Or maybe ORDER BY and LIMIT instead of max().

I've just skimmed that, though, so I'll come back after dinner to see if I've pointed you in an entirely wrong direction.

[Slater]

Hey, thanks!

As for the first one, I came up with this little chunk of code to do it manually...

Code: Select all

DECLARE @numberOfAutomobiles float;
DECLARE @numberOfSalesReps float;

Select @numberOfAutomobiles = COUNT(vin)
from Automobile;

Select @numberOfSalesReps = COUNT(name)
from Sales_Rep;

select @numberOfAutomobiles / @numberOfSalesReps as answer;


Only time it fails is with blank tables (divide by 0 error), but I think the teacher will let that slide...

Still working on 2nd one...

[Kaligraphic]

I think I did steer you wrong on the second one, just noticed the primary key constraint on the Test_Drives table. Strictly speaking, you can't have more than one test drive of a vehicle, as the combination of customer and vehicle must be unique, so every customer who test drives a vehicle would be tied for first place.

*edit: maybe not, it looks like that primary key is just in your implementation.*

I might suggest checking on the first one to make sure that the car is actually sold, though.

[Slater]

Yes, you are right... Just looked at the class' forum and saw that the teacher announced a couple days ago that she asked the wrong question, lol...

Replacement question now is "Find the name and license of the customer who has test driven the maximum number of cars."

I guess that means to find the person who has test-driven the most... hmm...