Created Three Tables
1.LOCATIONS
2.DEPARTMENTS
3.EMPLOYEES
1.LOCATIONS
CREATE TABLE LOCATIONS(
LOCATION_ID NUMBER(4),
STREET_ADDRESS VARCHAR2(40),
POSTAL_CODE VARCHAR2(12),
CITY VARCHAR2(30) NOT NULL,
STATE_PROVINCE VARCHAR2(25),
COUNTRY_ID CHAR(2),
constraint pk_loc primary key (LOCATION_ID));
2.DEPARTMENTS
/
CREATE TABLE DEPARTMENTS(
DEPARTMENT_ID NUMBER(4),
DEPARTMENT_NAME VARCHAR2(30) NOT NULL,
MANAGER_ID NUMBER(6),
LOCATION_ID NUMBER(4),
constraint pk_dep primary key (DEPARTMENT_ID,MANAGER_ID),
--constraint pk_dep primary key (MANAGER_ID),
constraint fk_locId foreign key (LOCATION_ID ) references LOCATIONS (LOCATION_ID ) );
/
3.EMPLOYEES
CREATE TABLE EMPLOYEES(
EMPLOYEE_ID NUMBER(6),
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25) NOT NULL,
EMAIL VARCHAR2(25) NOT NULL,
PHONE_NUMBER VARCHAR2(20),
HIRE_DATE DATE NOT NULL,
JOB_ID VARCHAR2(10) NOT NULL,
SALARY NUMBER(8,2),
COMMISSION_PCT NUMBER(2,2),
MANAGER_ID NUMBER(6),
DEPARTMENT_ID NUMBER(4),
constraint pk_emp primary key (EMPLOYEE_ID),
constraint fk_DeptID foreign key (DEPARTMENT_ID,MANAGER_ID ) references DEPARTMENTS (DEPARTMENT_ID,MANAGER_ID )) ;
Insert Values into Tables
select * from LOCATIONS;
insert into LOCATIONS values(7,'Kirkland','98007','Kent','Lynnwood','K');
select * from DEPARTMENTS;
insert into DEPARTMENTS values(10,'ACCOUNTING',010,1);
insert into DEPARTMENTS values(20,'RESEARCH',020,2);
insert into DEPARTMENTS values(30,'SALES',030,3);
insert into DEPARTMENTS values(40,'OPERATIONS',040,4);
insert into DEPARTMENTS values(41,'SHIPPING',050,5);
insert into DEPARTMENTS values(50,'SHIPPING_DESK',060,6);
insert into DEPARTMENTS values(60,'CARGO_DESK',070,7);
select * from EMPLOYEES;
insert into EMPLOYEES values(7839,'KING','KING','king@gmail.com','123456789','17-NOV-81','PRESIDENT',5500,null,010,10);
insert into EMPLOYEES values(7698,'BLAKE','BLAKE','BLAKE@gmail.com','123456789','01-MAY-81','MANAGER',3350,null,030,30);
insert into EMPLOYEES values(7782,'CLARK','CLARK','CLARK@gmail.com','123456789','09-JUN-81','MANAGER',2950,null,010,10);
insert into EMPLOYEES values(7566,'JONES','JONES','JONES@gmail.com','123456789','02-APR-81','MANAGER',3475,null,020,20);
insert into EMPLOYEES values(7788,'SCOTT','SCOTT','SCOTT@gmail.com','123456789','09-DEC-82','ANALYST',3500,null,020,20);
insert into EMPLOYEES values(7902,'FORD','FORD','FORD@gmail.com','123456789','03-DEC-81','ANALYST',3500,null,020,20);
insert into EMPLOYEES values(7369,'SMITH','SMITH','SMITH@gmail.com','123456789','17-DEC-80','CLERK',1300,null,020,20);
insert into EMPLOYEES values(7499,'ALLEN','ALLEN','ALLEN@gmail.com','123456789','20-FEB-81','SALESMAN',2100,null,030,30);
insert into EMPLOYEES values(7521,'WARD','WARD','WARD@gmail.com','123456789','22-FEB-81','SALESMAN',1750,null,030,30);
insert into EMPLOYEES values(7654,'MARTIN','MARTIN','MARTIN@gmail.com','123456789','28-SEP-81','SALESMAN',1750,null,030,30);
insert into EMPLOYEES values(7844,'TURNER','TURNER','TURNER@gmail.com','123456789','08-SEP-811','SALESMAN',2000,null,030,30);
insert into EMPLOYEES values(7876,'ADAMS','ADAMS','ADAMS@gmail.com','123456789','12-JAN-83','CLERK',1600,null,020,20);
insert into EMPLOYEES values(7900,'JAMES','JAMES','JAMES@gmail.com','123456789','03-DEC-81','CLERK',1450,null,030,30);
insert into EMPLOYEES values(7934,'MILLER','MILLER','MILLER@gmail.com','123456789','23-JAN-82','CLERK',1800,null,010,10);
1.LOCATIONS
2.DEPARTMENTS
3.EMPLOYEES
1.LOCATIONS
CREATE TABLE LOCATIONS(
LOCATION_ID NUMBER(4),
STREET_ADDRESS VARCHAR2(40),
POSTAL_CODE VARCHAR2(12),
CITY VARCHAR2(30) NOT NULL,
STATE_PROVINCE VARCHAR2(25),
COUNTRY_ID CHAR(2),
constraint pk_loc primary key (LOCATION_ID));
2.DEPARTMENTS
/
CREATE TABLE DEPARTMENTS(
DEPARTMENT_ID NUMBER(4),
DEPARTMENT_NAME VARCHAR2(30) NOT NULL,
MANAGER_ID NUMBER(6),
LOCATION_ID NUMBER(4),
constraint pk_dep primary key (DEPARTMENT_ID,MANAGER_ID),
--constraint pk_dep primary key (MANAGER_ID),
constraint fk_locId foreign key (LOCATION_ID ) references LOCATIONS (LOCATION_ID ) );
/
3.EMPLOYEES
CREATE TABLE EMPLOYEES(
EMPLOYEE_ID NUMBER(6),
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25) NOT NULL,
EMAIL VARCHAR2(25) NOT NULL,
PHONE_NUMBER VARCHAR2(20),
HIRE_DATE DATE NOT NULL,
JOB_ID VARCHAR2(10) NOT NULL,
SALARY NUMBER(8,2),
COMMISSION_PCT NUMBER(2,2),
MANAGER_ID NUMBER(6),
DEPARTMENT_ID NUMBER(4),
constraint pk_emp primary key (EMPLOYEE_ID),
constraint fk_DeptID foreign key (DEPARTMENT_ID,MANAGER_ID ) references DEPARTMENTS (DEPARTMENT_ID,MANAGER_ID )) ;
Insert Values into Tables
select * from LOCATIONS;
insert into LOCATIONS values(7,'Kirkland','98007','Kent','Lynnwood','K');
select * from DEPARTMENTS;
insert into DEPARTMENTS values(10,'ACCOUNTING',010,1);
insert into DEPARTMENTS values(20,'RESEARCH',020,2);
insert into DEPARTMENTS values(30,'SALES',030,3);
insert into DEPARTMENTS values(40,'OPERATIONS',040,4);
insert into DEPARTMENTS values(41,'SHIPPING',050,5);
insert into DEPARTMENTS values(50,'SHIPPING_DESK',060,6);
insert into DEPARTMENTS values(60,'CARGO_DESK',070,7);
select * from EMPLOYEES;
insert into EMPLOYEES values(7839,'KING','KING','king@gmail.com','123456789','17-NOV-81','PRESIDENT',5500,null,010,10);
insert into EMPLOYEES values(7698,'BLAKE','BLAKE','BLAKE@gmail.com','123456789','01-MAY-81','MANAGER',3350,null,030,30);
insert into EMPLOYEES values(7782,'CLARK','CLARK','CLARK@gmail.com','123456789','09-JUN-81','MANAGER',2950,null,010,10);
insert into EMPLOYEES values(7566,'JONES','JONES','JONES@gmail.com','123456789','02-APR-81','MANAGER',3475,null,020,20);
insert into EMPLOYEES values(7788,'SCOTT','SCOTT','SCOTT@gmail.com','123456789','09-DEC-82','ANALYST',3500,null,020,20);
insert into EMPLOYEES values(7902,'FORD','FORD','FORD@gmail.com','123456789','03-DEC-81','ANALYST',3500,null,020,20);
insert into EMPLOYEES values(7369,'SMITH','SMITH','SMITH@gmail.com','123456789','17-DEC-80','CLERK',1300,null,020,20);
insert into EMPLOYEES values(7499,'ALLEN','ALLEN','ALLEN@gmail.com','123456789','20-FEB-81','SALESMAN',2100,null,030,30);
insert into EMPLOYEES values(7521,'WARD','WARD','WARD@gmail.com','123456789','22-FEB-81','SALESMAN',1750,null,030,30);
insert into EMPLOYEES values(7654,'MARTIN','MARTIN','MARTIN@gmail.com','123456789','28-SEP-81','SALESMAN',1750,null,030,30);
insert into EMPLOYEES values(7844,'TURNER','TURNER','TURNER@gmail.com','123456789','08-SEP-811','SALESMAN',2000,null,030,30);
insert into EMPLOYEES values(7876,'ADAMS','ADAMS','ADAMS@gmail.com','123456789','12-JAN-83','CLERK',1600,null,020,20);
insert into EMPLOYEES values(7900,'JAMES','JAMES','JAMES@gmail.com','123456789','03-DEC-81','CLERK',1450,null,030,30);
insert into EMPLOYEES values(7934,'MILLER','MILLER','MILLER@gmail.com','123456789','23-JAN-82','CLERK',1800,null,010,10);
No comments:
Post a Comment