Home » RDBMS Server » Server Administration » How to query this question?
How to query this question? [message #373902] Wed, 16 May 2001 18:13 Go to next message
Steve
Messages: 190
Registered: September 1999
Senior Member
Hi,ALL:
I would like to make a query from these sample tables for a dummy insurance company.
The tables are as follows.
1. Person(pid,fname,lname,bdate,street,suburb,state,postcode)
2. Client(pid,cid)
3. Insured_by(cid,pno)
4. Policy(pno,pname,status,startdate,expirydate,id)
5. Insured_Item(id,brand,model,year,reg)
My question: List the most popular car models insured in specified suburb(that is, cars whose owners live in that suburb. For one policy there may be more than one holders)
Thanks for your help.
Steve

Report message to a moderator

Re: How to query this question? [message #373922 is a reply to message #373902] Thu, 17 May 2001 05:35 Go to previous messageGo to next message
Sirajdin
Messages: 17
Registered: May 2001
Junior Member
hi steve..

u mean the models insured the most?

Report message to a moderator

i think i got it.. [message #374036 is a reply to message #373902] Tue, 22 May 2001 02:46 Go to previous messageGo to next message
Muhamad Sirajdin
Messages: 12
Registered: May 2001
Junior Member
SELECT BRAND FROM INSURED_ITEM II,
INSURED_BY IB, CLIENT CL, POLICY PL, PERSON PE
WHERE CL.PID=PE.PID AND CL.CID=IB.CID AND
IB.PNO=PL.PNO AND PL.ID=II.ID
GROUP BY BRAND,SUBURB HAVING COUNT(BRAND)=
(SELECT MAX(COUNT(BRAND)) FROM INSURED_ITEM II,
INSURED_BY IB, CLIENT CL, POLICY PL, PERSON PE
WHERE CL.PID=PE.PID AND CL.CID=IB.CID AND
IB.PNO=PL.PNO AND PL.ID=II.ID GROUP BY BRAND,SUBURB HAVING SUBURB='&&SUBURB'
INTERSECT
SELECT COUNT(BRAND) FROM INSURED_ITEM II,
INSURED_BY IB, CLIENT CL, POLICY PL, PERSON PE
WHERE CL.PID=PE.PID AND CL.CID=IB.CID AND
IB.PNO=PL.PNO AND PL.ID=II.ID GROUP BY BRAND,SUBURB HAVING SUBURB='&&SUBURB')
/

Report message to a moderator

i think i got it.. [message #374037 is a reply to message #373902] Tue, 22 May 2001 02:48 Go to previous messageGo to next message
Muhamad Sirajdin
Messages: 12
Registered: May 2001
Junior Member
SELECT BRAND FROM INSURED_ITEM II,
INSURED_BY IB, CLIENT CL, POLICY PL, PERSON PE
WHERE CL.PID=PE.PID AND CL.CID=IB.CID AND
IB.PNO=PL.PNO AND PL.ID=II.ID
GROUP BY BRAND,SUBURB HAVING COUNT(BRAND)=
(SELECT MAX(COUNT(BRAND)) FROM INSURED_ITEM II,
INSURED_BY IB, CLIENT CL, POLICY PL, PERSON PE
WHERE CL.PID=PE.PID AND CL.CID=IB.CID AND
IB.PNO=PL.PNO AND PL.ID=II.ID GROUP BY BRAND,SUBURB HAVING SUBURB='&&SUBURB'
INTERSECT
SELECT COUNT(BRAND) FROM INSURED_ITEM II,
INSURED_BY IB, CLIENT CL, POLICY PL, PERSON PE
WHERE CL.PID=PE.PID AND CL.CID=IB.CID AND
IB.PNO=PL.PNO AND PL.ID=II.ID GROUP BY BRAND,SUBURB HAVING SUBURB='&&SUBURB')
/

Report message to a moderator

i think i got it.. [message #374038 is a reply to message #373902] Tue, 22 May 2001 03:04 Go to previous message
Muhamad Sirajdin
Messages: 12
Registered: May 2001
Junior Member
SELECT BRAND FROM INSURED_ITEM II,
INSURED_BY IB, CLIENT CL, POLICY PL, PERSON PE
WHERE CL.PID=PE.PID AND CL.CID=IB.CID AND
IB.PNO=PL.PNO AND PL.ID=II.ID
GROUP BY BRAND,SUBURB HAVING COUNT(BRAND)=
(SELECT MAX(COUNT(BRAND)) FROM INSURED_ITEM II,
INSURED_BY IB, CLIENT CL, POLICY PL, PERSON PE
WHERE CL.PID=PE.PID AND CL.CID=IB.CID AND
IB.PNO=PL.PNO AND PL.ID=II.ID GROUP BY BRAND,SUBURB HAVING SUBURB='&&SUBURB'
INTERSECT
SELECT COUNT(BRAND) FROM INSURED_ITEM II,
INSURED_BY IB, CLIENT CL, POLICY PL, PERSON PE
WHERE CL.PID=PE.PID AND CL.CID=IB.CID AND
IB.PNO=PL.PNO AND PL.ID=II.ID GROUP BY BRAND,SUBURB HAVING SUBURB='&&SUBURB')
/

Report message to a moderator

Previous Topic: How to stop null fields from filling with data
Next Topic: MSSql to Oracle
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Jul 03 18:13:03 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.