Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2013
    Posts
    57

    Unanswered: SQL for query help?

    Can someone help me out, or point me in the right direction.
    I don't understand joins to well, so a good explanation of what im doing wrong, along with anything you would care to show me, would help, thanks.

    The problem

    What is the name of the school that sponsors the Bears and how many Bear teams are there?
    THE TABLES
    School Table
    Code:
    SQL> desc school
     Name
     -----------------
     SCHOOL_ID
     SCHOOL_NAME
     SCHOOL_TYPE
    
    
    SQL> select * from school;
    
     SCHOOL_ID SCHOOL_NAME                                        SCHOOL_TYPE
    ---------- -------------------------------------------------- -------------
         11546 Ivy Tech College                                   COLLegE
         11090 LAWRENCE Central Grade School                      GRADE SCHOOL
         11111 Lawrence NORTH High School                         HIGH SCHooL
         19283 Howe High SCHOOL                                   High SchooL
        123134 Lawrence Central High School                       HIGH SCHOOL
           192 Little Big Horn Grade School                       GRADE SCHOOL

    Team Table

    Code:
    SQL> desc team
     Name
     -------------
     TEAM_ID
     SPORT_ID
     SCHOOL_ID
     TEAM_NAME
    SQL> select * from team;
    
       TEAM_ID   SPORT_ID  SCHOOL_ID TEAM_NAME
    ---------- ---------- ---------- ----------
             1          1      11111 WildCats
             2          2      11111 WILDCATS
             3          3      11111 WildCats
             4          4      11111 WILDCATS
             5          5      11111 WildCats
             6          6      11111 WildCats
             7          7      11111 WildCats
             8          1     123134 Bears
             9          2     123134 Bears
            10          3     123134 BEARS
            11          4     123134 Bears
            12          5     123134 Bears
            13          6     123134 BEars

    Some recent attempts


    Code:
    SQL> select school_name, count(Team_name) as "Num_of_Bears_teams"
      2  from team join school
      3  on team.School_id = team.school_id
      4  where  Upper(Team_Name) = 'BEARS';
    select school_name, count(Team_name) as "Num_of_Bears_teams"
           *
    ERROR at line 1:
    ORA-00937: not a single-group group function
    Code:
    SQL> select count(team_name) as team_num, school_name
      2  from team
      3  join school on school.school_id = team.school_id
      4  where Tean.team_name = UPPER('BEARS');
    where Tean.team_name = UPPER('BEARS')
          *
    ERROR at line 4:
    ORA-00904: "TEAN"."TEAM_NAME": invalid identifier
    http://img.photobucket.com/albums/v1....png[/IMG]
    Last edited by bwilson95; 09-28-13 at 00:58.

  2. #2
    Join Date
    Sep 2013
    Posts
    57

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    select school_name, count(*)
    from school ss, team tt
    where ss.school_id = tt.school_id
     and upper(tt.team_name) = 'BEARS' 
    group by school_name
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Sep 2013
    Posts
    57
    Quote Originally Posted by anacedent View Post
    Code:
    select school_name, count(*)
    from school ss, team tt
    where ss.school_id = tt.school_id
     and upper(tt.team_name) = 'BEARS' 
    group by school_name

    Thank you, I had to fine tune it a bit (TY classmate), but figured it out.
    Im just learning joins, so it's somewhat confusing.....

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    thanks for sharing your solution so others may benefit in the future from it.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Sep 2013
    Posts
    57
    Quote Originally Posted by anacedent View Post
    thanks for sharing your solution so others may benefit in the future from it.
    SQL> select school_name, count(*)
    2 from school ss, team tt
    3 where ss.school_id = tt.school_id
    4 and upper(tt.team_name) = 'BEARS'
    5 group by school_name;


    Had to look it up, appears to be the same as yours, If I remember right, I kept hitting '.' instead of ','

    I do thank you for your help, however, sarcasm isn't needed.

  7. #7
    Join Date
    Aug 2013
    Posts
    1

    Post to find no of bears ,join incorrect and include group by

    select school_name, count(Team_name) as "Num_of_Bears_teams"
    from team join school
    on team.School_id = school.school_id
    where Upper(Team_Name) = 'BEARS'
    group by school.school_id ,school.school_name;

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by prasadm View Post
    select school_name, count(Team_name) as "Num_of_Bears_teams"
    from team join school
    on team.School_id = school.school_id
    where Upper(Team_Name) = 'BEARS'
    group by school.school_id ,school.school_name;
    the inclusion of "school.school_id" in GROUP BY is superfluous, since it is not part of the SELECT clause
    Code:
      1  select school_name, count(Team_name) as "Num_of_Bears_teams"
      2  from team join school
      3  on team.School_id = school.school_id
      4  where Upper(Team_Name) = 'BEARS'
      5* group by school.school_name
    SQL> /
    
    SCHOOL_NAME                      Num_of_Bears_teams
    -------------------------------- ------------------
    CHICAGO                                           1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •