Rene Nyffenegger wrote:
>>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);
>
>
> You might want to try analytical functions:
>
> drop table pres_hobby;
> drop table president;
>
> create table president (
> pres_name varchar2(40) primary key,
> party varchar2(40)
> );
>
> create table pres_hobby(
> pres_name references president not null,
> name varchar2(40)
> );
>
> insert into president values('Allan', 'REP');
> insert into president values('Basil', 'REP');
> insert into president values('Cats', 'DEM');
> insert into president values('De More', 'DEM');
> insert into president values('Eric', 'REP');
> insert into president values('Frot', 'DEM');
>
> insert into pres_hobby values('Allan', 'TV');
> insert into pres_hobby values('Allan', 'Ping Pong');
>
> insert into pres_hobby values('Basil', 'Golf');
> insert into pres_hobby values('Basil', 'TV');
> insert into pres_hobby values('Basil', 'Reading');
>
> insert into pres_hobby values('Cats', 'Eating');
>
> insert into pres_hobby values('De More', 'Wine');
> insert into pres_hobby values('De More', 'Computer');
> insert into pres_hobby values('De More', 'Arts');
> insert into pres_hobby values('De More', 'TV');
>
> insert into pres_hobby values('Eric', 'Reading');
>
> insert into pres_hobby values('Frot', 'Writing');
>
>
> select party, pres_name, cnt from (
> select
> p.party,
> p.pres_name,
> count(*) cnt,
> row_number() over (partition by p.party order by count(*) desc) r
> from
> president p join pres_hobby h on p.pres_name = h.pres_name
> group by
> p.party, p.pres_name
> )
> where r = 1;
>
> hth
> Rene
Great ... but students rarely learn from having solutions handed
to them. It would have been preferable if you'd provided a hint
or two and let the OP work through it.
--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Tue Feb 24 2004 - 15:37:38 CST
