Thread: Need help with a college SQL exam question...
Need help with a college SQL exam question...
From
dejauser2001@yahoo.co.uk (dejauser2001@yahoo.co.uk)
Date:
Hi everyone, Its coming close to my January examinations, and while revising I got stuck in the follwing question. I literally spent hours trying to solve it but I just can't :( Consider the folowing relations; (where the * means primary key) The "Car Rental" database ------------------------- customer(*cust_num*,cust_name) hire(*car_reg*,*cust_num*,*hire_date*) shop(*outlet_code*,address) car_model(*model*,make,num_seats,max_speed) car(*car reg*,model,year, outlet_code) And the question is; Using SQL (using SELECT, FROM, WHERE, GROUP BY, COUNT(*), etc..) write a query to list the names of customers who have borrowed cars made by every maker. Thats it! Hard or what? Any help greatly apreciated
"dejauser": > Its coming close to my January examinations, and > while revising I got stuck in the follwing question. > I literally spent hours trying to solve it but I > just can't :( hmmm ... I don't think it's according to the honor code to ask us to help with your exams. ;-) > And the question is; > Using SQL (using SELECT, FROM, WHERE, GROUP BY, COUNT(*), etc..) > write a query > to list the names of customers who have borrowed cars made by every > maker. It's doable. Just think "nested subselects" and heavy use of the HAVING clause. They're also screwing with you by providing an irrelevant table (shop) and not providing a relevant table (makers). If I was the professor, I'd give the question writer a D+ for database design ... -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
>>>>> "Josh" == Josh Berkus <josh@agliodbs.com> writes: Josh> It's doable. Just think "nested subselects" and heavy use Josh> of the HAVING clause. They're also screwingwith you by Josh> providing an irrelevant table (shop) and not providing a Josh> relevant table (makers). If I was the professor, I'd give Josh> the question writer a D+ for database design ... I would treat the table car_model as providing a complete list of makes as well, i.e., "select distinct make from car_model" provides a list of all car makes (from which the customer can borrow). I'd agree that the database *should* have a makers table to constrain the values of that column. A real-life database without it is prone to *lots* of data entry errors (let's see, should I put in GM, Chevy or Chevrolet?). roland -- PGP Key ID: 66 BC 3B CD Roland B. Roberts, PhD RL Enterprises roland@rlenter.com 76-15 113th Street, Apt 3B roland@astrofoto.org Forest Hills, NY 11375
Hi, Nice question.... I made a nice query that'll get you the result. I will not give you the code because: 1. it's your exam, 2. I think It has some "rough edges" and I don't want you to get the blame of my "edges" :-) I'll give you a narrative version: 1. I'm counting the number of distinct makes... 2. I'm counting the number of distinct makes every customer has ever used (join, join, join!) and compare that number to the result of #1 This of course happens in subqueries. I do not use HAVINGs though... And on the design of the exam-database; lousy. Succes, A. Prins. "dejauser2001@yahoo.co.uk" schreef: > Hi everyone, > > Its coming close to my January examinations, and > while revising I got stuck in the follwing question. > I literally spent hours trying to solve it but I > just can't :( > > Consider the folowing relations; (where the * means > primary key) > > The "Car Rental" database > ------------------------- > > customer(*cust_num*,cust_name) > hire(*car_reg*,*cust_num*,*hire_date*) > shop(*outlet_code*,address) > car_model(*model*,make,num_seats,max_speed) > car(*car reg*,model,year, outlet_code) > > And the question is; > Using SQL (using SELECT, FROM, WHERE, GROUP BY, COUNT(*), etc..) write a query > to list the names of customers who have borrowed cars made by every maker. > > Thats it! Hard or what? > > Any help greatly apreciated