Re: SQL help needed

From: j reed <reed_at_speakeasy.org>
Date: Tue, 02 Mar 2004 19:42:39 -0800
Message-ID: <5-adncAKOrWsydjdRVn-iQ@speakeasy.net>

Daniel Morgan wrote:

> Paul wrote:
> 

>> damorgan_at_x.washington.edu says...
>>
>>
>>> Look at solving the problem using an in-line view. The basic code
>>> structure is:
>>> SELECT fld, fld, fld
>>> FROM
>>> (select statement) a,
>>> (select statement) b
>>> WHERE a.something = b.something
>>> AND ...
>>> the in-line statements aliased as 'a' and 'b' can contain all of
>>> the group by and other clauses you require as can the out statement.
>>> HTH
>>
>>
>>
>>
>> I've been messing around with this for hours now, stuff like
>>
>> Select MAX(zz.bill)
>> (
>> Select count(a.pres_name) bill, a.pres_name, b.party
>> from pres_hobby a, president b
>> where a.pres_name = b.pres_name
>> and b.party = 'DEM'
>> group by a.pres_name, b.party
>> order by 1 desc
>> ) AS zz
>> FROM zz
>>
>>
>> And I just haven't been getting anywhere - any chance that you could
>> expand on your hint?
>>
>>
>> Paul...
>>

> 
> Send me the entire problem off-line and I'll see what I can do
> this weekend. Today I am grading midterms and it isn't pretty.
> I asked my students to write FGAC from scratch and while they
> did very well ... trying to offer helpful hints and advice is
> ... lets just say challenging.  ;-)
> 

demo: show the name of the employee, together with the department and number of vacations (er, i mean business trips) of the person who has taken the largest number of business trips within each department

create table empl (
emp_name varchar2(40) primary key,
dept varchar2(40)
);

create table trips (
emp_name references empl not null,
vacation varchar2(40)
);

insert into empl values('George', 'AP');
insert into empl values('Tom', 'AP');
insert into empl values('Eliot', 'AP');
insert into empl values('Donald', 'IT');
insert into empl values('Charles', 'IT');
insert into empl values('Lawrie', 'PHS');
insert into empl values('Flaubert', 'NCI');
insert into empl values('Bowie', 'NCI');

--select 'insert into trips values (''' || emp_name

   || ''', ''xxx'');' from empl;
--will give you:

insert into trips values ('George', 'xxx');
insert into trips values ('Tom', 'xxx');
insert into trips values ('Eliot', 'xxx');
insert into trips values ('Donald', 'xxx');
insert into trips values ('Charles', 'xxx');
insert into trips values ('Lawrie', 'xxx');
insert into trips values ('Flaubert', 'xxx');
insert into trips values ('Bowie', 'xxx');

--so copy and paste and make up some trips.

insert into trips values ('George', 'boston');
insert into trips values ('George', 'rio');
insert into trips values ('George', 'st. kitts');
insert into trips values ('George', 'st. petersburg');
insert into trips values ('George', 'caribou');
insert into trips values ('Tom', 'houston');
insert into trips values ('Eliot', 'ft. lauderdale');

insert into trips values ('Lawrie', 'xxx');

insert into trips values ('Donald', 'xxx');
insert into trips values ('Charles', 'dover');
insert into trips values ('Charles', 'portsmouth');
insert into trips values ('Charles', 'andover');

insert into trips values ('Bowie', 'halifax'); insert into trips values ('Bowie', 'peggys cove');

--sweet. now who went where?

  col emp_name format a12
  col dept format a10
  col trip format a20

select e.emp_name, e.dept, t.vacation
from empl e, trips t
where e.emp_name = t.emp_name;

--how many trips did each person take?

select e.emp_name, e.dept, count(t.vacation) bt

   from empl e, trips t
  where e.emp_name = t.emp_name
group by e.emp_name, dept;

--who is earning frequent flyer miles?

select x.dept, max(x.bt)
from

       (
       select e.emp_name, e.dept, count(t.vacation) bt
       from empl e, trips t
       where e.emp_name = t.emp_name
       group by e.emp_name, dept
        ) x

group by x.dept;

---

now, add employee name back into the above query and you're good to go.

search documentation for " subquery " or " inline view " over at http://tahiti.oracle.com/

come up with a select statement that works, drop it into another query, give it an alias, and treat it like a short lived view.

good luck,
j reed Received on Tue Mar 02 2004 - 21:42:39 CST