This discussion is archived
14 Replies Latest reply: Jan 29, 2013 9:50 AM by 987761 RSS

simply querry

987761 Newbie
Currently Being Moderated
Hello



I have such a database "ZOO" in oracle PL/SQL

[http://img62.imageshack.us/img62/6361/databasep.jpg]

The query is "Show all the keepers, who cares of all animals in the zoo.
Thanks for any help.

Regards
  • 1. Re: simply querry
    Paul Horth Expert
    Currently Being Moderated
    984758 wrote:
    Hello



    I have such a database "ZOO" in oracle PL/SQL

    [http://img62.imageshack.us/img62/6361/databasep.jpg]

    The query is "Show all the keepers, who cares of all animals in the zoo.
    Thanks for any help.

    Regards
    Welcome to the forum.

    Please read {message:id=9360002} and follow the advice there.

    This does sound like a homework question: show us what you have tried so far.

    Also, don't post external links. Place all you requirements here in the thread.
  • 2. Re: simply querry
    stefan nebesnak Journeyer
    Currently Being Moderated
    Read this first..

    Zoo Data Model Entity Relationship Diagram:
    (MIT Computer Science and Artificial Intelligence Laboratory)

    https://docs.google.com/viewer?a=v&q=cache:7ub1cdeeP8IJ:db.csail.mit.edu/6.830/lectures/lec1.ppt+SQL+%22keepers+animals+species%22&hl=cs&gl=cz&pid=bl&srcid=ADGEESjGa58qdRqhyaymanH2tipnMph3HshtAGEZnSNO_Ptm6_VRLONfnXA2-tKDPeQsCdzWHC8DMpZY1yVMQobWn9fTsAFBxZiQxmviAvjCpSWWq8BvNMNMTsKZEJfTWa_twu0wp5lX&sig=AHIEtbRTmVvugB7HiYppgfHMVL0kzPWNNw
  • 3. Re: correlated query ?
    987761 Newbie
    Currently Being Moderated
    OK my fault, once again ;]

    i got such a database:
    -----
    create table keeper
    (
    keeper_id number(10) not null,
    keeper_name varchar2(50) not null,
    constraint keeper_pk PRIMARY KEY (keeper_id)
    );
    ----
    create table species
    (
    species_id number(10) not null,
    species_name varchar2(50) not null,
    constraint species_pk PRIMARY KEY (species_id)
    );
    ----
    create table animal
    (
    animal_id number(10) not null,
    animal_name varchar2(255) not null,
    keeper_id number(10) not null,
    species_id number(10) not null,
    constraint animal_pk PRIMARY KEY (animal_id),
    constraint fk_keeper FOREIGN KEY(keeper_id) references keeper(keeper_id),
    constraint fk_species FOREIGN KEY(species_id) references species(species_id)
    );
    -----

    I inserted a data:

    insert into keeper values(1,'Alex');
    insert into keeper values(2,'John');
    insert into keeper values(3,'Mike');

    insert into species values(1,'White');
    insert into species values(2,'Rede');
    insert into species values(3,'Black');


    insert into animal values(1,'cat',1,1);
    insert into animal values(2,'cat',1,2);
    insert into animal values(3,'cat',1,3);
    insert into animal values(4,'dog',1,1);
    insert into animal values(5,'dog',1,2);
    insert into animal values(6,'rat',1,3);
    insert into animal values(7,'rat',1,1);
    insert into animal values(8,'cat',2,1);
    insert into animal values(9,'cat',3,2);
    insert into animal values(10,'cat',2,3);
    insert into animal values(11,'dog',2,1);
    insert into animal values(12,'dog',3,2);
    insert into animal values(13,'rat',3,3);
    insert into animal values(14,'rat',3,1);

    -----

    The query is *"Show all the keepers, who cares of all animals in the zoo"*

    I know that i have to get one keeper_id. Then count the number of all animals at the zoo. And compare it to the number of animals with that keeper_id. And then select the second id and do the same. I think it's a correlated querry.

    select o.keeper_id,keeper_name
    from animal z1,keeper o
    where z1.keeper_id=o.keeper_id
    group by o.keeper_id,keeper_name
    having count(z1.keeper_id)=(select count(distinct animal_name)
    from animal z2)

    I tried to do something like this.

    I will be greatful for any help and sugestions.
    Sorry for my english.
  • 4. Re: correlated query ?
    Paul Horth Expert
    Currently Being Moderated
    You're very nearly there.

    You don't want to compare the count of keeper ids as that is a count of the total number of animals cared for by each keeper.

    You seem to want to detect keepers who look after each 'type' of animal. That is, ones who look after dogs, cats and rats.

    Try this:
    select o.keeper_id
          ,keeper_name
      from animal z1
          ,keeper o
     where z1.keeper_id = o.keeper_id
     group by o.keeper_id
             ,keeper_name
    having count(distinct z1.animal_name) = (select count(distinct z2.animal_name)
                                               from animal z2)
  • 5. Re: correlated query ?
    987761 Newbie
    Currently Being Moderated
    Hmmmm.
    It's shows me Mike and Alex. But only correct is Alex.
    I coounted manually the numbers of all animals in zoo and that is 7. When i change the query to:

    select o.keeper_id,keeper_name
    from animal z1,keeper o
    where z1.keeper_id=o.keeper_id
    group by o.keeper_id,keeper_name
    having count(z1.keeper_id)=7

    It's correct. But there is no informations about species. So I think the main problem is to count all animals in the zoo right now ;]
  • 6. Re: correlated query ?
    Paul Horth Expert
    Currently Being Moderated
    984758 wrote:
    Hmmmm.
    It's shows me Mike and Alex. But only correct is Alex.
    I coounted manually the numbers of all animals in zoo and that is 7. When i change the query to:

    select o.keeper_id,keeper_name
    from animal z1,keeper o
    where z1.keeper_id=o.keeper_id
    group by o.keeper_id,keeper_name
    having count(z1.keeper_id)=7

    It's correct. But there is no informations about species. So I think the main problem is to count all animals in the zoo right now ;]
    Well, Mike looks after all animals in the zoo (if you mean he looks after cats, rats and dogs).

    If you mean ALL animals in the zoo, there are 14, and nobody looks after ALL of them.

    Alex looks after 7 animals, and that's not all of them.

    Please be more clear as to what you want.
  • 7. Re: correlated query ?
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    984758 wrote:
    OK my fault, once again ;]

    i got such a database:
    -----
    create table keeper ...
    Thanks for posting the CREATE TABLE and INSERT statements; that's very helpful.
    Don't forget to post the results you want from that data.
    The query is *"Show all the keepers, who cares of all animals in the zoo"*

    I know that i have to get one keeper_id.
    Why does it have to be one? Depending on the data, there could be any number of keepers (0, 1, 2, 3 or more) who care for all species.
    Then count the number of all animals at the zoo. And compare it to the number of animals with that keeper_id. And then select the second id and do the same. I think it's a correlated querry.

    select o.keeper_id,keeper_name
    from animal z1,keeper o
    where z1.keeper_id=o.keeper_id
    group by o.keeper_id,keeper_name
    having count(z1.keeper_id)=(select count(distinct animal_name)
    from animal z2)

    I tried to do something like this.
    That's pretty close.
    The sub-query in the HAVING clause is correctly getting the number of different species. (This assumes that "species" means what it usually does in English: cat, dog, rat, ... and that the table called species has nothing to do with this problem
    But what are you comparing that total to?
    COUNT (z1.keeper_id)
    is the total number of animals that the keeper cares for. That COUNT will be 3 if the keeper cares for 3 cats, or 2 cats and 1 dog. Also, the COUNT will be higher than 3 if the keeper cares for all species, but has 2 or more individuals in the same species.
    You're correctly counting distinct animal names on the right side of the = operator; do the same thing on the left side.
  • 8. Re: correlated query ?
    987761 Newbie
    Currently Being Moderated
    Well, Mike looks after all animals in the zoo (if you mean he looks after cats, rats and dogs).

    If you mean ALL animals in the zoo, there are 14, and nobody looks after ALL of them.

    ----
    I mean the first solution. All animals = all species of animal
    I mean that, there are animals who have two keepers.

    Most crealy
    animal id=1 white cat and id=8 also white cat is the same and one animal who has two keepers ;]

    Edited by: 984758 on 2013-01-29 04:38
  • 9. Re: correlated query ?
    stefan nebesnak Journeyer
    Currently Being Moderated
    Check this..
    with t as
    (select max(rownum) cnt from (select distinct animal_name, species_name from animal a2, species s2
    where a2.species_id = s2.species_id))
    select k.keeper_id, k.keeper_name, t.cnt 
    from animal a, species s, keeper k, t
    where a.keeper_id = k.keeper_id
    and a.species_id = s.species_id
    group by k.keeper_id, k.keeper_name, t.cnt
    having count(k.keeper_name) =  t.cnt
  • 10. Re: correlated query ?
    Paul Horth Expert
    Currently Being Moderated
    984758 wrote:
    Well, Mike looks after all animals in the zoo (if you mean he looks after cats, rats and dogs).

    If you mean ALL animals in the zoo, there are 14, and nobody looks after ALL of them.

    ----
    I mean the first solution. All animals = all species of animal
    I mean that, there are animals who have two keepers.

    Most crealy
    animal id=1 white cat and id=8 also white cat is the same and one animal who has two keepers ;]

    Edited by: 984758 on 2013-01-29 04:38
    I see, you don't mean all animals, you mean all distinct species where a species is white cat, red dog etc.

    I think Stefan's answer will probably do what you want then.

    If not, please explain clearly what you mean.
  • 11. Re: correlated query ?
    987761 Newbie
    Currently Being Moderated
    I think Stefan answer is correct but i can't understand this code...

    I have to change data ans test it few times.

    Could anyone explain this code step by step. Or Please write a links to this solution >>?

    Anyway thank You for help.
  • 12. Re: simply querry
    stefan nebesnak Journeyer
    Currently Being Moderated
    You can play around with this statement..
    with t as
    (select distinct k.keeper_id, k.keeper_name, a.animal_id, a.species_id, count(*) over (partition by k.keeper_id) cnt_keeper, count(*) over() cnt_all, (select max(rownum) cnt from (select distinct animal_name, species_name from animal a2, species s2
    where a2.species_id = s2.species_id)) cnt_distinct
    from animal a, species s, keeper k
    where a.keeper_id = k.keeper_id
    and a.species_id = s.species_id)
    select * from t 
    --where t.cnt_keeper = cnt_distinct;
    Result:
           KEEPER_ID     KEEPER_NAME     ANIMAL_ID     SPECIES_ID     CNT_KEEPER     CNT_ALL     CNT_DISTINCT
         1             Alex           1             1               7               14         7
         1             Alex           2             2               7               14         7
         1             Alex           3             3               7               14         7
         1             Alex           4             1               7               14         7
         1             Alex           5             2               7               14         7
         1             Alex           6             3               7               14         7
         1             Alex           7             1               7               14         7
    
         2             John           8             1               3               14         7
         2             John           10           3               3               14         7
         2           John           11           1               3               14         7
    
         3           Mike           9             2               4               14         7
         3           Mike           12           2               4               14         7
         3           Mike           13           3               4               14         7
         3           Mike           14           1               4               14         7
    Edited by: stefan nebesnak on 29.1.2013 7:13
  • 13. Re: correlated query ?
    stefan nebesnak Journeyer
    Currently Being Moderated
    with t as
    (select max(rownum) cnt from (select distinct animal_name, species_name
    from animal a2, species s2
    where a2.species_id = s2.species_id))

    select k.keeper_id, k.keeper_name, t.cnt
    _from animal a, species s, keeper k, t_
    where a.keeper_id = k.keeper_id
    and a.species_id = s.species_id

    group by k.keeper_id, k.keeper_name, t.cnt

    having count (k.keeper_name) = t.cnt
    You will compare count of all distinct animal species ( t.cnt )
    to count of particular keeper's animal species ( count (k.keeper_name))

    t is alias name for count table:
    (select max(rownum) cnt from (select distinct animal_name, species_name from animal a2, species s2
    where a2.species_id = s2.species_id))
    ANIMAL_NAME     SPECIES_NAME
    cat             Black
    cat             Rede
    cat             White
    
    dog             Rede
    dog             White
    
    rat             Black
    rat             White
    with clause is shown here: http://ss64.com/ora/syntax-analytic-with.html
  • 14. Re: correlated query ?
    987761 Newbie
    Currently Being Moderated
    Once again Thanks Stefan. I have to read about it. I thougt that is a simply query like others. Without your help i wouldn't even started. It's not just solution of this single request but a chance to learn sth new.

    Thanks once again.

    Edited by: 984758 on 2013-01-29 09:50

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points