Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fB4992m71926 for ; Tue, 4 Dec 2001 04:09:02 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id fB48sq181784 for pgsql-sql@postgresql.org; Tue, 4 Dec 2001 03:54:52 -0500 (EST) (envelope-from news) From: Arian Prins X-Newsgroups: comp.databases.postgresql.sql, mailing.database.sql-general, microsoft.public.it.sql Subject: Re: Need help with a college SQL exam question... Date: Tue, 04 Dec 2001 09:54:43 +0100 Organization: Academic Medical Center, The Netherlands Lines: 47 Message-ID: <3C0C8F53.26701052@zonnet.nl> References: <383ef96c.0111290754.4ab26b07@posting.google.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.surfnet.nl X-Mailer: Mozilla 4.7 [nl] (WinNT; I) X-Accept-Language: nl Cache-Post-Path: sphere.amc.uva.nl!unknown@amcnip36143.amc.uva.nl X-Cache: nntpcache 2.4.0b2 (see http://www.nntpcache.org/) To: pgsql-sql@postgresql.org X-Archive-Number: 200112/74 X-Sequence-Number: 5897 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