DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: SQL Query

  1. #1
    Join Date
    Apr 2010
    Posts
    2

    SQL Query

    I'm trying to query a database with the following table (employees)

    ID_No | Name |Hire_Date | Position |Salary |Mgr_ID_No |Dept_No
    E001 |GAFFER |1997-01-01 | CHAIRMAN | 150000 |NULL | 1
    E019 |WILLAIMS |1992-02-02 | MANAGER | 88999.99| E001 | 1
    E017 |STERLING |2001-04-17 | ADMIN | 18000 ||E109 | 1
    E108 |ALLEN |2000-04-18 | ACCOUNTANT | 38000 |E109 | 1
    E102 |SANDLER |2000-09-01 | MANAGER | 55000 |E001 | 2
    E110 |CARTER |2003-10-01 | DESIGNER | 35000 |E102 | 2

    I'm trying to find out all of the employees who have Williams as their manager.

    I'm not sure how to write the query for this.

    I was thinking along the lines of

    SELECT * FROM Employees where Mgr_Id_No = ID_Number AND Name = 'Williams';
    I know this brings up nothing but I really don't know how to query it.


    Thanks in Advance

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    Every employee has an ID_No (although, check your data, lots of typos, E019 versus E109, etc.). People may or may not have a manager. What column in your table reflects the employee ID of that person's manager?

  3. #3
    Join Date
    Apr 2010
    Posts
    2
    Hi, Thanks fr the reply.

    It was a typo. E019 should be E109. My mistake.

    Everyone in the table has some sort of manager except the chairman. the ID_No reflects everyone's employee ID so that's the column which holds the Manager's Employee ID as well.

  4. #4
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    SELECT * FROM Employees where Mgr_Id_No = (select ID_Number from employees where Name = 'Williams');
    http://www.perf-engg.com
    A performance engineering forum

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    homework, isn't it?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    This is your basic self joining table. Here is some code to help you understand what you are doing.

    SYS@rman AS SYSDBA> create table what_department
    2 ( Dept_No NUMBER NOT NULL,
    3 dept_name VARCHAR2(64),
    4 CONSTRAINT what_department_pkey
    5 PRIMARY KEY (Dept_No));

    Table created.

    Elapsed: 00:00:00.02
    SYS@rman AS SYSDBA>
    SYS@rman AS SYSDBA> INSERT INTO what_department
    2 ( Dept_No, dept_name )
    3 VALUES ( 1, 'meeting');

    1 row created.

    Elapsed: 00:00:00.00
    SYS@rman AS SYSDBA>
    SYS@rman AS SYSDBA> INSERT INTO what_department
    2 ( Dept_No, dept_name )
    3 VALUES ( 2, 'slacking');

    1 row created.

    Elapsed: 00:00:00.00
    SYS@rman AS SYSDBA>
    SYS@rman AS SYSDBA> COMMIT;

    Commit complete.

    Elapsed: 00:00:00.00
    SYS@rman AS SYSDBA>
    SYS@rman AS SYSDBA> create table someones_homework
    2 ( ID_No VARCHAR2(10) NOT NULL,
    3 Name VARCHAR2(64),
    4 Hire_Date DATE,
    5 Position VARCHAR2(64),
    6 Salary NUMBER,
    7 Mgr_ID_No VARCHAR2(10),
    8 Dept_No NUMBER,
    9 CONSTRAINT someones_homework_pkey
    10 PRIMARY KEY (id_no),
    11 CONSTRAINT someones_homework_fkey
    12 FOREIGN KEY (Dept_No)
    13 REFERENCES what_department (Dept_No));

    Table created.

    Elapsed: 00:00:00.03
    SYS@rman AS SYSDBA>
    SYS@rman AS SYSDBA> INSERT INTO someones_homework
    2 ( ID_No, Name, Hire_Date, Position, Salary, Mgr_ID_No, Dept_No)
    3 VALUES
    4 ( 'E001', 'GAFFER', TO_DATE('1997-01-01', 'YYYY-MM-DD'), 'CHAIRMAN', 150000, NULL, 1 );

    1 row created.

    Elapsed: 00:00:00.00
    SYS@rman AS SYSDBA>
    SYS@rman AS SYSDBA> INSERT INTO someones_homework
    2 ( ID_No, Name, Hire_Date, Position, Salary, Mgr_ID_No, Dept_No)
    3 VALUES
    4 ( 'E109', 'WILLAIMS', TO_DATE('1992-02-02', 'YYYY-MM-DD'), 'MANAGER', 88999.99, 'E001', 1 );

    1 row created.

    Elapsed: 00:00:00.00
    SYS@rman AS SYSDBA>
    SYS@rman AS SYSDBA> INSERT INTO someones_homework
    2 ( ID_No, Name, Hire_Date, Position, Salary, Mgr_ID_No, Dept_No)
    3 VALUES
    4 ( 'E017', 'STERLING', TO_DATE('2001-04-17', 'YYYY-MM-DD'), 'ADMIN', 18000, 'E109', 1 );

    1 row created.

    Elapsed: 00:00:00.00
    SYS@rman AS SYSDBA>
    SYS@rman AS SYSDBA> INSERT INTO someones_homework
    2 ( ID_No, Name, Hire_Date, Position, Salary, Mgr_ID_No, Dept_No)
    3 VALUES
    4 ( 'E108', 'ALLEN', TO_DATE('2000-04-18', 'YYYY-MM-DD'), 'ACCOUNTANT', 38000, 'E109', 1 );

    1 row created.

    Elapsed: 00:00:00.01
    SYS@rman AS SYSDBA>
    SYS@rman AS SYSDBA> INSERT INTO someones_homework
    2 ( ID_No, Name, Hire_Date, Position, Salary, Mgr_ID_No, Dept_No)
    3 VALUES
    4 ( 'E102', 'SANDLER', TO_DATE('2000-09-01', 'YYYY-MM-DD'), 'MANAGER', 55000, 'E001', 2 );

    1 row created.

    Elapsed: 00:00:00.00
    SYS@rman AS SYSDBA>
    SYS@rman AS SYSDBA> INSERT INTO someones_homework
    2 ( ID_No, Name, Hire_Date, Position, Salary, Mgr_ID_No, Dept_No)
    3 VALUES
    4 ( 'E110', 'CARTER', TO_DATE('2003-10-01', 'YYYY-MM-DD'), 'DESIGNER', 35000, 'E102', 2 );

    1 row created.

    Elapsed: 00:00:00.00
    SYS@rman AS SYSDBA>
    SYS@rman AS SYSDBA> commit;

    Commit complete.

    Elapsed: 00:00:00.00
    SYS@rman AS SYSDBA>
    SYS@rman AS SYSDBA> col ID_NO format a5
    SYS@rman AS SYSDBA> col name format a12
    SYS@rman AS SYSDBA> col position format a12
    SYS@rman AS SYSDBA> col manager format a12
    SYS@rman AS SYSDBA> col dept_name format a12
    SYS@rman AS SYSDBA>
    SYS@rman AS SYSDBA> SELECT emp.id_no, emp.name, emp.hire_date, emp.position,
    2 emp.salary, mgr.name manager, dep.dept_name
    3 FROM someones_homework emp
    4 LEFT OUTER JOIN someones_homework mgr
    5 ON emp.Mgr_ID_No = mgr.id_no
    6 LEFT OUTER JOIN what_department dep
    7 ON emp.Dept_No = dep.Dept_No
    8 ORDER BY 1;

    Code:
    ID_NO NAME         HIRE_DATE  POSITION         SALARY MANAGER      DEPT_NAME
    ----- ------------ ---------- ------------ ---------- ------------ ------------
    E001  GAFFER       01-JAN-97  CHAIRMAN         150000              meeting
    E017  STERLING     17-APR-01  ADMIN             18000 WILLAIMS     meeting
    E102  SANDLER      01-SEP-00  MANAGER           55000 GAFFER       slacking
    E108  ALLEN        18-APR-00  ACCOUNTANT        38000 WILLAIMS     meeting
    E109  WILLAIMS     02-FEB-92  MANAGER        88999.99 GAFFER       meeting
    E110  CARTER       01-OCT-03  DESIGNER          35000 SANDLER      slacking
    
    6 rows selected.
    
    Elapsed: 00:00:00.01

Posting Permissions

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


Click Here to Expand Forum to Full Width