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 Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
i think i got it.. [message #374036 is a reply to message #373902] |
Tue, 22 May 2001 02:46 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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')
/
|
|
|
i think i got it.. [message #374037 is a reply to message #373902] |
Tue, 22 May 2001 02:48 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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')
/
|
|
|
i think i got it.. [message #374038 is a reply to message #373902] |
Tue, 22 May 2001 03:04 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
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')
/
|
|
|
Goto Forum:
Current Time: Sun Aug 18 04:27:46 CDT 2024
|