Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> SQL help needed

SQL help needed

From: Irene <irenemartine_at_hotmail.com>
Date: Tue, 24 Feb 2004 18:10:10 +0100
Message-ID: <403b84f3$0$41751$5fc3050@dreader2.news.tiscali.nl>


I am doing a course in which we are now using SQL-plus. One of the queries that has to be made I can not get solved (very annoying).

It handles about a database that contains presidents of the United States, together with e.g. marriages and hobbies of these presidents. The database contains amongst others the following two tables: president and pres_hobby. President has pres_name and party as attributes (pres_name is primairy key), and pres_hobby has pres_name and hobby as attributes (combination of those two attributes is primairy key). Pres_hobby.pres_name is foreign key for president.pres_name.

The query that has to be designed:

Give the name of the president, together with party and number of hobbies of this presidents, that have the largest number of hobbies of all presidents within there party.

I have been trying (see below) but I get only one president, the one with the most hobbies of all, and it should give presidents per party with most hobbies.

Who can help me?

SELECT p.party, p.pres_name, count(*)
FROM president p, pres_hobby h
WHERE p.pres_name = h.pres_name
GROUP BY p.party, p.pres_name

    HAVING count(*) = (SELECT max(count(*))     FROM president p2, pres_hobby h2
    WHERE p2.pres_name = h2.pres_name
    GROUP BY p2.pres_name); Received on Tue Feb 24 2004 - 11:10:10 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US