create table dept(deptno int constraint pk_dept primary key, dname varchar(14), loc varchar(13))
go
insert into dept values(10,'ACCOUNTING','NEW YORK')
go
insert into dept values(20,'RESEARCH','DALLAS')
go
insert into dept values(30,'SALES','CHICAGO')
go
insert into dept values(40,'OPERATIONS','BOSTON')
go
create table emp(empno int constraint pk_emp primary key, ename varchar(10), job varchar(9), mgr int null, hiredate date, sal float, comm float null, deptno int constraint fk_dept references dept)
go
insert into emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,null,20)
go
insert into emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30)
go
insert into emp values (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30)
go
insert into emp values (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20)
go
insert into emp values (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30)
go
insert into emp values (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30)
go
insert into emp values (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10)
go
insert into emp values (7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20)
go
insert into emp values (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10)
go
insert into emp values (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30)
go
insert into emp values (7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20)
go
insert into emp values (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30)
go
insert into emp values (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20)
go
insert into emp values (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10)
go
select ename,dname,d.deptno,empno from dept d left join emp e on (d.deptno=e.deptno)
go
commit
go