Re: Simple SQL Question

From: Peter WhytePOP_Server=pop.freeuk.net <pwhyte_at_freeuk.com>
Date: 2000/03/21
Message-ID: <YtRB4.1557$pQ5.41680@nnrp3.clara.net>#1/1

Hi,

This is the first:

SELECT cname
  FROM company
 WHERE city in

          (
            SELECT city
              FROM company
             WHERE cname = 'Bank of Trade'
          )

GROUP BY cname
  HAVING count (*) =

          (
            SELECT count (*)
              FROM company
             WHERE cname = 'Bank of Trade'
          )

/

This may be the second if I have guessed the relationships correctly:

SELECT e.city
  FROM employee e, manages m, company c

 WHERE m.eid = e.eid
   AND m.bid = c.bid
   AND e.city = c.city

 GROUP BY e.city
HAVING count(*) =

         (
          SELECT count (*)
            FROM company
           WHERE city = e.city
          )

/

This assumes that eid in MANAGES represents the manager, and that bid in MANAGES will
uniquely identify the company and branch.

HTH Peter Whyte

Vinh Duong <vduong_at_aei.ca> wrote in message news:38d2fab3_1_at_news.aei.ca...
> Hi,
>
> I have the following tables:
>
> Employee (eid, ename, street, city)
> Works (eid, bid, cname,salary)
> Company (bid, cname,city)
> Manages (eid, mid, bid, cname)
>
> A company, which is uniquely identified by its cname, may have branches
> (indicated by bid) in more than one city.
>
> Help me the write TWO appropriate queries that each satisfy the following
> requirements:
>
> 1- Find the names of companies that are located IN EVERY CITY in which
 'Bank
> of Trade' is located.
>
> 2-Make a list of the cities which have one of its residents as a manger IN
> EVERY COMPANY located in the city.
>
> Gracias,
>
> Vinh
> Montreal
>
>
Received on Tue Mar 21 2000 - 00:00:00 CST