sql_for_assignment1

gistfile1.sql

CREATE TABLE OFFICE (
OFF_ID NUMBER NOT NULL,
OFF_ADDR varchar2(20) NOT NULL,
OFF_NAME varchar2(10) NOT NULL,
PRIMARY KEY (OFF_ID));

CREATE TABLE EMPLOYEE (
EMP_ID NUMBER NOT NULL,
EMP_FNAME varchar2(20) NOT NULL,
EMP_LNAME varchar2(20) NOT NULL,
EMP_AGE NUMBER NOT NULL,
EMP_TEL varchar2(20) NOT NULL,
EMP_GEND varchar(10) NOT NULL,
CAR_ID varchar(20) NOT NULL,
POSI_ID varchar(10) NOT NULL,
OFF_ID NUMBER NOT NULL,

PRIMARY KEY (EMP_ID), 
CONSTRAINT OFF_ID_FK FOREIGN KEY(OFF_ID)
REFERENCES OFFICE(OFF_ID)
ON DELETE CASCADE);

CREATE TABLE POSITION (
POSI_ID NUMBER NOT NULL,
POSI_NAME varchar(20) NOT NULL,
PRIMARY KEY (POSI_ID));

CREATE TABLE CLIENT (
CLI_ID NUMBER NOT NULL,
CLI_FNAME varchar2(20) NOT NULL,
CLI_LNAME varchar2(20) NOT NULL,
CLI_GEND varchar2(20) NOT NULL,
LIC_NUM NUMBER NOT NULL,
REG_INFO varchar(50) NULL,
EMP_ID NUMBER NOT NULL,
PRIMARY KEY (CLI_ID));

CREATE TABLE APPIONMENT (
APPI_ID NUMBER NOT NULL,
EMP_ID varchar2(20) NOT NULL,
CLI_ID varchar2(10) NOT NULL,
APPI_TIME DATE NOT NULL,
PRIMARY KEY (APPI_ID));

CREATE TABLE CAR (
CAR_ID NUMBER NOT NULL,
MAIN_REC varchar(50) NOT NULL,
PRIMARY KEY (CAR_ID));

CREATE TABLE INTERVIEW (
INTE_ID NUMBER NOT NULL,
CLI_ID NUMBER NOT NULL,
EMP_ID NUMBER NOT NULL,
PRIMARY KEY (INTE_ID));

CREATE TABLE EXAM (
EXAM_ID NUMBER NOT NULL,
EXAM_TIME DATE NOT NULL,
EXAM_RESULT varchar2(20),
CERT_TYPE varchar2(20),
CLI_ID NUMBER NOT NULL,
PRIMARY KEY (EXAM_ID));

CREATE TABLE LESSON (
LES_ID NUMBER NOT NULL,
CAR_MILE varchar2(20) NOT NULL,
EMP_ID NUMBER NOT NULL,
CLI_ID NUMBER NOT NULL,
LES_FEDBCK varchart(50) NOT NULL,
PRIMARY KEY (LES_ID));