Created
September 21, 2020 13:37
-
-
Save youngjinmo/62727c3cc575a3a032e4828854dd7d70 to your computer and use it in GitHub Desktop.
Create table emp, dept for exam.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create table dept( | |
deptno number(2,0), | |
dname varchar2(14), | |
loc varchar2(13), | |
constraint pk_dept primary key (deptno) | |
); | |
create table emp( | |
empno number(4,0), | |
ename varchar2(10), | |
job varchar2(9), | |
mgr number(4,0), | |
hiredate date, | |
sal number(7,2), | |
comm number(7,2), | |
deptno number(2,0), | |
constraint pk_emp primary key (empno), | |
constraint fk_deptno foreign key (deptno) references dept (deptno) | |
); | |
insert into DEPT (DEPTNO, DNAME, LOC) | |
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( | |
7839, 'KING', 'PRESIDENT', null, | |
to_date('17-11-1981','dd-mm-yyyy'), | |
5000, null, 10 | |
); | |
insert into emp | |
values( | |
7698, 'BLAKE', 'MANAGER', 7839, | |
to_date('1-5-1981','dd-mm-yyyy'), | |
2850, null, 30 | |
); | |
insert into emp | |
values( | |
7782, 'CLARK', 'MANAGER', 7839, | |
to_date('9-6-1981','dd-mm-yyyy'), | |
2450, null, 10 | |
); | |
insert into emp | |
values( | |
7566, 'JONES', 'MANAGER', 7839, | |
to_date('2-4-1981','dd-mm-yyyy'), | |
2975, null, 20 | |
); | |
insert into emp | |
values( | |
7788, 'SCOTT', 'ANALYST', 7566, | |
to_date('13-JUL-87','dd-mm-rr') - 85, | |
3000, null, 20 | |
); | |
insert into emp | |
values( | |
7902, 'FORD', 'ANALYST', 7566, | |
to_date('3-12-1981','dd-mm-yyyy'), | |
3000, null, 20 | |
); | |
insert into emp | |
values( | |
7369, 'SMITH', 'CLERK', 7902, | |
to_date('17-12-1980','dd-mm-yyyy'), | |
800, null, 20 | |
); | |
insert into emp | |
values( | |
7499, 'ALLEN', 'SALESMAN', 7698, | |
to_date('20-2-1981','dd-mm-yyyy'), | |
1600, 300, 30 | |
); | |
insert into emp | |
values( | |
7521, 'WARD', 'SALESMAN', 7698, | |
to_date('22-2-1981','dd-mm-yyyy'), | |
1250, 500, 30 | |
); | |
insert into emp | |
values( | |
7654, 'MARTIN', 'SALESMAN', 7698, | |
to_date('28-9-1981','dd-mm-yyyy'), | |
1250, 1400, 30 | |
); | |
insert into emp | |
values( | |
7844, 'TURNER', 'SALESMAN', 7698, | |
to_date('8-9-1981','dd-mm-yyyy'), | |
1500, 0, 30 | |
); | |
insert into emp | |
values( | |
7876, 'ADAMS', 'CLERK', 7788, | |
to_date('13-JUL-87', 'dd-mm-rr') - 51, | |
1100, null, 20 | |
); | |
insert into emp | |
values( | |
7900, 'JAMES', 'CLERK', 7698, | |
to_date('3-12-1981','dd-mm-yyyy'), | |
950, null, 30 | |
); | |
insert into emp | |
values( | |
7934, 'MILLER', 'CLERK', 7782, | |
to_date('23-1-1982','dd-mm-yyyy'), | |
1300, null, 10 | |
); | |
select ename, dname, job, empno, hiredate, loc | |
from emp, dept | |
where emp.deptno = dept.deptno | |
order by ename; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment