Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Posts
    2

    Question Unanswered: Help with Queries

    I'm having trouble with some problems. This is a homework assignment, so if you have qualms about helping with homework no problem.

    First I need to find the employees that have a salary greater then the average salary of the employees in the company. THere is an employee table that has employee name, employee number information and a works for table that has company name, salary and empolyee number.

    This is what I have, but it's not working correctly
    select name
    from Employee
    where empno in(
    select empno
    from works X
    where salary < (
    select avg(salary)
    from works
    where company_name = X.company_name));

    Second problem I have is finding employees that live in same city and state as their manager. Again the employee table holds information about the employee number, city and state and name. There is a Manages table that has the employee number and the employee number of their manager. This is what I have for this, but once again it's not working

    select X.name
    from Employee X, Employee Y
    where Y.empno in (
    select manager
    from Manages
    where empno = X.manager)
    and X.city = Y.city and X.state = Y.state;

    Any help will be appreciated, thanks.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Post DDL for tables.
    Post DML for test data.

    Post expected/desired results.
    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.

  3. #3
    Join Date
    Mar 2009
    Posts
    2
    empno name city state
    157 Jones, Tim Los Angeles Calif
    1110 Smith, Paul Oakland Calif
    35 Evans, Michael El Cerrito Calif
    129 Thomas, Tom Dallas Tex
    13 Edwards, Peter San Francisco Calif
    215 Collins, Joanne Boston Mass
    55 James, Mary Arlington Tex
    26 Thompson, Bob Denver Colo
    98 Williams, Judy Arlington Tex
    32 Smythe, Carol Amherst Mass
    33 Hayes, Evelyn Boston Mass
    199 Bullock, J.D. Dallas Tex
    4901 Bailey, Chas M. New York NY
    843 Schmidt, Herman Oakland Calif
    2398 Wallace, Maggie J. Hickville Okla
    1639 Choy, Wanda Salt Lake City Utah

    Works
    empno company_name salary
    157 IBM 2000
    1110 IBM 6000
    35 IBM 5000
    129 IBM 10000
    13 IBM 9000
    215 IBM 7000
    55 BOA 12000
    26 BOA 13000
    98 BOA 9000
    32 BOA 9050
    33 BOA 10100
    199 Microsoft 27000
    4901 Microsoft 8377
    843 Microsoft 11204
    2398 Microsoft 7880
    1639 Microsoft 11160


    Company(company_name, state)
    company_name state
    BOA Tex
    Microsoft Neb
    GE Mass
    IBM Calif
    Cisco Ga

    Manages(empno, manager)
    empno manager
    157 1110
    1110
    35 1110
    129 1110
    13 1110
    215 1110
    55
    26 55
    98 55
    32 55
    33 55
    199 1639
    4901 1639
    843 1639
    2398 1639
    1639

    Here is the data and tables.
    The results for the first should be
    1 row
    Williams, Judy

    For the second it should be
    5 rows
    Thomas, Tom
    Edwards, Peter
    James, Mary
    Thompson, Bob
    Bullock, J.D.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Joins are better than In (select...)

    The expected result you posted from your first query is incorrect, read the requirement carfully: "find the employees that have a salary greater then the average salary of the employees in the company"

    So, here is one way to do it:
    Code:
    SQL> WITH
      2    employee AS
      3       (
      4          SELECT 157 empno, 'Jones, Tim   ' NAME, 'Los Angeles' city, 'Calif' state FROM DUAL UNION
      5          SELECT 1110, 'Smith, Paul      ', 'Oakland', 'Calif' FROM DUAL UNION
      6          SELECT 35, 'Evans, Michael   ', 'El Cerrito', 'Calif' FROM DUAL UNION
      7          SELECT 129, 'Thomas, Tom      ', 'Dallas', 'Tex' FROM DUAL UNION
      8          SELECT 13, 'Edwards, Peter   ', 'San Francisco', 'Calif' FROM DUAL UNION
      9          SELECT 215, 'Collins, Joanne  ', 'Boston', 'Mass' FROM DUAL UNION
     10          SELECT 55, 'James, Mary      ', 'Arlington', 'Tex' FROM DUAL UNION
     11          SELECT 26, 'Thompson, Bob    ', 'Denver', 'Colo' FROM DUAL UNION
     12          SELECT 98, 'Williams, Judy   ', 'Arlington', 'Tex' FROM DUAL UNION
     13          SELECT 32, 'Smythe, Carol    ', 'Amherst', 'Mass' FROM DUAL UNION
     14          SELECT 33, 'Hayes, Evelyn    ', 'Boston', 'Mass' FROM DUAL UNION
     15          SELECT 199, 'Bullock, J.D.    ', 'Dallas', 'Tex' FROM DUAL UNION
     16          SELECT 4901, 'Bailey, Chas M.  ', 'New York', 'NY' FROM DUAL UNION
     17          SELECT 843, 'Schmidt, Herman  ', 'Oakland', 'Calif' FROM DUAL UNION
     18          SELECT 2398, 'Wallace, Maggie J', 'Hickville', 'Okla' FROM DUAL UNION
     19          SELECT 1639, 'Choy, Wanda      ', 'Salt Lake City', 'Utah' FROM DUAL)
     20     ,
     21    works AS
     22       (
     23          SELECT 157 empno, 'IBM' company_name, 2000 salary FROM DUAL UNION
     24          SELECT 1110, 'IBM', 6000 FROM DUAL UNION
     25          SELECT 35, 'IBM', 5000 FROM DUAL UNION
     26          SELECT 129, 'IBM', 10000 FROM DUAL UNION
     27          SELECT 13, 'IBM', 9000 FROM DUAL UNION
     28          SELECT 215, 'IBM', 7000 FROM DUAL UNION
     29          SELECT 55, 'BOA', 12000 FROM DUAL UNION
     30          SELECT 26, 'BOA', 13000 FROM DUAL UNION
     31          SELECT 98, 'BOA', 9000 FROM DUAL UNION
     32          SELECT 32, 'BOA', 9050 FROM DUAL UNION
     33          SELECT 33, 'BOA', 10100 FROM DUAL UNION
     34          SELECT 199, 'Microsoft', 27000 FROM DUAL UNION
     35          SELECT 4901, 'Microsoft', 8377 FROM DUAL UNION
     36          SELECT 843, 'Microsoft', 11204 FROM DUAL UNION
     37          SELECT 2398, 'Microsoft', 7880 FROM DUAL UNION
     38          SELECT 1639, 'Microsoft', 11160 FROM DUAL)
     39  SELECT NAME, w.company_name, w.salary, c.avg_sal
     40    FROM employee e,
     41         works w,
     42         (SELECT   company_name, AVG (salary) avg_sal
     43              FROM works
     44          GROUP BY company_name) c
     45   WHERE w.empno = e.empno
     46     AND c.company_name = w.company_name
     47     AND w.salary > c.avg_sal
     48   ORDER BY 2,1 
     49  /
    
    NAME              COMPANY_N     SALARY    AVG_SAL
    ----------------- --------- ---------- ----------
    James, Mary       BOA            12000      10630
    Thompson, Bob     BOA            13000      10630
    Collins, Joanne   IBM             7000       6500
    Edwards, Peter    IBM             9000       6500
    Thomas, Tom       IBM            10000       6500
    Bullock, J.D.     Microsoft      27000    13124.2
    
    6 rows selected.


    PS: Using Joins is allways better than In (select...).
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by LKBrwn_DBA
    PS: Using Joins is allways better than In (select...).
    My experience is, that it doesn't really matter.
    In about 99% of the cases where I tested the execution plans, Oracle used the exact same plan regardless whether I was using a sub-select or a a join.
    The optimizer is pretty smart

Posting Permissions

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