self join, Sql Server

self join

CREATE TABLE DEPT
       (DEPTNO Numeric(2),
	DNAME VARCHAR(14) ,
	LOC VARCHAR(13) ) ;
CREATE TABLE EMP
       (EMPNO Numeric(4) ,
	ENAME VARCHAR(10),
	JOB VARCHAR(9),
	MGR Numeric(4),
	HIREDATE varchar(20),
	SAL Numeric(7,2),
	COMM Numeric(7,2),
	DEPTNO Numeric(2));
INSERT INTO DEPT VALUES
	(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
	(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
	(40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,'17/12/1980',800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,'20/2/1981',1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,'22/2/1981',1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,'2/4/1981',2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,'28/9/1981',1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,'1/5/1981',2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,'9/6/1981',2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,'13/7/1987',3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,'17/11/1981',5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,'8/9/1981',1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,'13/7/1987',1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,'3/12/1981',950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,'3/12/1981',3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,'23/1/1982',1300,NULL,10);

CREATE TABLE BONUS
	(
	ENAME VARCHAR(10)	,
	JOB VARCHAR(9)  ,
	SAL Numeric(10,0),
	COMM Numeric(10,0)
	) ;
CREATE TABLE SALGRADE
      ( GRADE int,
	LOSAL Numeric(10,0),
	HISAL Numeric(10,0) );
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);

select * from emp
select e1.empno,e1.ename from emp e1

select e.empno,e.ename,e1.ename as mgrName,e1.empno as mgrNo,
e.job,e.sal from emp e left join emp e1 on e.mgr = e1.empno order by sal desc


run \| edit \| history \| help	0