Wednesday, August 10, 2016

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);



No comments:

Post a Comment