-
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
-
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?
-
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.
-
SELECT * FROM Employees where Mgr_Id_No = (select ID_Number from employees where Name = 'Williams');
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|