SQLQuery4.sql

CREATE TABLE CLIENTS( CLIENT_ID DECIMAL(4) PRIMARY KEY,
CNAME VARCHAR(40) NOT NULL,
ADDRESS VARCHAR(30),
EMAIL VARCHAR(30) UNIQUE,
PHONE DECIMAL(10),
BUSINESS VARCHAR(20) NOT NULL);

INSERT INTO CLIENTS(
CLIENT_ID,CNAME,ADDRESS,EMAIL,PHONE,BUSINESS)
VALUES(1001,'ACME UTILITIES','NOIDA','contact@acmeutil.com',9567880032,'Manufacturing');

INSERT INTO CLIENTS(
CLIENT_ID,CNAME,ADDRESS,EMAIL,PHONE,BUSINESS)
VALUES(1002,'Trackon Consultants','Mumbai','consult@trackon.com',8734210090,'Consultant');

INSERT INTO CLIENTS(
CLIENT_ID,CNAME,ADDRESS,EMAIL,PHONE,BUSINESS)
VALUES(1003,'MoneySaver Distributors','Bangalore','save@moneysaver.com',7799886655,'Reseller');

INSERT INTO CLIENTS(
CLIENT_ID,CNAME,ADDRESS,EMAIL,PHONE,BUSINESS)
VALUES(1004,'Lawful Corp','Chennai','justice@lawful.com',9210342219,'Professional');




CREATE TABLE DEPARTMENTS(
DEPTNO DECIMAL(2) PRIMARY KEY,
DNAME VARCHAR(15) NOT NULL,
LOC VARCHAR(20));

INSERT INTO DEPARTMENTS(DEPTNO,DNAME,LOC) VALUES(10,'Design','Pune');
INSERT INTO DEPARTMENTS(DEPTNO,DNAME,LOC) VALUES(20,'Development','Pune');
INSERT INTO DEPARTMENTS(DEPTNO,DNAME,LOC) VALUES(30,'Testing','Mumbai');
INSERT INTO DEPARTMENTS(DEPTNO,DNAME,LOC) VALUES(40,'Document','Mumbai');


CREATE TABLE EMPLOYESS(
EMPNO DECIMAL(4) PRIMARY KEY,
ENAME VARCHAR(20) NOT NULL,
JOB VARCHAR(15),
SALARY DECIMAL(7) CHECK(SALARY>0),
DEPTNO DECIMAL(2) REFERENCES
DEPARTMENTS(DEPTNO));

INSERT INTO EMPLOYESS(EMPNO,ENAME,JOB,SALARY,DEPTNO) VALUES(7001,'Sandeep','Analyst',25000,10);
INSERT INTO EMPLOYESS(EMPNO,ENAME,JOB,SALARY,DEPTNO) VALUES(7002,'Rajesh','Designer',30000,10);
INSERT INTO EMPLOYESS(EMPNO,ENAME,JOB,SALARY,DEPTNO) VALUES(7003,'Madhav','Developer',40000,20);
INSERT INTO EMPLOYESS(EMPNO,ENAME,JOB,SALARY,DEPTNO) VALUES(7004,'Manoj','Developer',40000,20);
INSERT INTO EMPLOYESS(EMPNO,ENAME,JOB,SALARY,DEPTNO) VALUES(7005,'Abhay','Designer',35000,10);
INSERT INTO EMPLOYESS(EMPNO,ENAME,JOB,SALARY,DEPTNO) VALUES(7006,'Uma','Tester',30000,30);
INSERT INTO EMPLOYESS(EMPNO,ENAME,JOB,SALARY,DEPTNO) VALUES(7007,'Gita','Tech.Writer',30000,40);
INSERT INTO EMPLOYESS(EMPNO,ENAME,JOB,SALARY,DEPTNO) VALUES(7008,'Priya','Tester',35000,30);
INSERT INTO EMPLOYESS(EMPNO,ENAME,JOB,SALARY,DEPTNO) VALUES(7009,'Nutan','Developer',45000,20);
INSERT INTO EMPLOYESS(EMPNO,ENAME,JOB,SALARY,DEPTNO) VALUES(7010,'Smita','Analyst',20000,10);
INSERT INTO EMPLOYESS(EMPNO,ENAME,JOB,SALARY,DEPTNO) VALUES(7011,'Anand','Project Mgr',65000,10);

CREATE TABLE PROJECTS(
PROJECT_ID DECIMAL(3) PRIMARY KEY,
DESCR VARCHAR(30) NOT NULL,
START_DATE DATE,
PLANNED_END_DATE DATE,
ACTUAL_END_DATE DATE,
BUDGET DECIMAL(10) CHECK(BUDGET>0),
CLIENT_ID DECIMAL(4) REFERENCES CLIENTS(CLIENT_ID));


INSERT INTO PROJECTS(
PROJECT_ID,DESCR,START_DATE,PLANNED_END_DATE,ACTUAL_END_DATE,BUDGET,CLIENT_ID) VALUES(401,'Inventory','01-APR-11','01-OCT-11','31-OCT-11',150000,1001);
INSERT INTO PROJECTS(
PROJECT_ID,DESCR,START_DATE,PLANNED_END_DATE,ACTUAL_END_DATE,BUDGET,CLIENT_ID) VALUES(402,'Accounting','01-AUG-11','01-JAN-12',null,500000,1002);
INSERT INTO PROJECTS(
PROJECT_ID,DESCR,START_DATE,PLANNED_END_DATE,ACTUAL_END_DATE,BUDGET,CLIENT_ID) VALUES(403,'Payroll','01-OCT-11','31-DEC-11',null,75000,1003);
INSERT INTO PROJECTS(
PROJECT_ID,DESCR,START_DATE,PLANNED_END_DATE,ACTUAL_END_DATE,BUDGET,CLIENT_ID) VALUES(404,'Contact Mgmt','01-NOV-11','31-DEC-11',null,50000,1004);

CREATE TABLE EMPPROJECTTASKS(
PROJECT_ID DECIMAL(3) REFERENCES PROJECTS(PROJECT_ID),EMPNO DECIMAL(4) REFERENCES EMPLOYESS(EMPNO),START_DATE DATE,END_DATE DATE,
TASK VARCHAR(25) NOT NULL,STATUS VARCHAR(15) NOT NULL,PRIMARY KEY(PROJECT_ID,EMPNO));


INSERT INTO EMPPROJECTTASKS(PROJECT_ID,EMPNO,START_DATE,END_DATE,TASK,STATUS) VALUES(401,7001,'01-APR-11','20-APR-11','System Analysis','Completed');
INSERT INTO EMPPROJECTTASKS(PROJECT_ID,EMPNO,START_DATE,END_DATE,TASK,STATUS) VALUES(401,7002,'21-APR-11','30-MAY-11','System Design','Completed');
INSERT INTO EMPPROJECTTASKS(PROJECT_ID,EMPNO,START_DATE,END_DATE,TASK,STATUS) VALUES(401,7003,'01-JUN-11','15-JUL-11','Coding','Completed');
INSERT INTO EMPPROJECTTASKS(PROJECT_ID,EMPNO,START_DATE,END_DATE,TASK,STATUS) VALUES(401,7004,'18-JUL-11','01-SEP-11','Coding','Completed');

INSERT INTO EMPPROJECTTASKS(PROJECT_ID,EMPNO,START_DATE,END_DATE,TASK,STATUS) VALUES(401,7006,'03-SEP-11','15-SEP-11','Testing','Completed');
INSERT INTO EMPPROJECTTASKS(PROJECT_ID,EMPNO,START_DATE,END_DATE,TASK,STATUS) VALUES(401,7009,'18-SEP-11','05-OCT-11','Code Change','Completed');
INSERT INTO EMPPROJECTTASKS(PROJECT_ID,EMPNO,START_DATE,END_DATE,TASK,STATUS) VALUES(401,7008,'06-OCT-11','16-OCT-11','Testing','Completed');
INSERT INTO EMPPROJECTTASKS(PROJECT_ID,EMPNO,START_DATE,END_DATE,TASK,STATUS) VALUES(401,7007,'06-OCT-11','22-OCT-11','Documentation','Completed');
INSERT INTO EMPPROJECTTASKS(PROJECT_ID,EMPNO,START_DATE,END_DATE,TASK,STATUS) VALUES(401,7011,'22-OCT-11','31-OCT-11','Sign Off','Completed');
INSERT INTO EMPPROJECTTASKS(PROJECT_ID,EMPNO,START_DATE,END_DATE,TASK,STATUS) VALUES(401,7010,'01-AUG-11','20-AUG-11','System Design','Completed');
INSERT INTO EMPPROJECTTASKS(PROJECT_ID,EMPNO,START_DATE,END_DATE,TASK,STATUS) VALUES(401,7004,'01-OCT-11',null,'Coding','In Progress');


SELECT* FROM CLIENTS;
SELECT* FROM EMPLOYESS;
SELECT* FROM DEPARTMENTS;
SELECT * FROM PROJECTS;
SELECT * FROM EMPPROJECTTASKS;