SQL Query

[mitty]

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

[stecal]

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?

[mitty]

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.

[malay_biswal]

SELECT * FROM Employees where Mgr_Id_No = (select ID_Number from employees where Name = 'Williams');

[PAVB]

homework, isn't it?

[gandolf989]

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;

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