SQL LAB 2 (2.1 - 3.9)

                                            SQL  QUREIES

           Make the combination of DNO and PRJ_NO as primary key in the table PROJ.

ALTER TABLE PROJ ADD PRIMARY KEY(DNO,PRJ_NO);

         Add a column to EMP table named PRJ_ID. Add a foreign key constraint to EMP table on                     (DeptNo, Proj_Id) referencing PROJ. (Indicates an employee from which

department is working on which project/s.)

ALTER TABLE EMP ADD PRJ_ID varchar2(5);

ALTER TABLE EMP ADD FOREIGN KEY(DEPTNO,PRJ_ID) REFERENCES PROJ;

            Add constraints(VALID_EMPNO_Cons) to the EMP table to check the EMPNO >100.

ALTER TABLE EMP ADD CONSTRAINT VALID_EMPNO_CONS CHECK(EMPNO > 100);

            Add a new column Dept_Budget column of size 7 digits to the DEPT table.

ALTER TABLE DEPART ADD DEPT_BUDEGET NUMBER(7);

            Add a new column Prj_Fund column of size 7 digits to the PROJ table.

ALTER TABLE PROJ ADD Prj_Fund NUMBER(7);

------------------------------------------------------- INSERT-------------------------------------------------------------

            INSERT DATA INTO PROJ, EMP, DEPART.

-------------------------------------------------------DEPART-------------------------------------------------------------

insert INTO DEPART (DNO, DNAME, LOCATION, DEPT_BUDGET) 

VALUES

(NULL,'CORPORATE','HYD',700000);

VALUES

('D2','ACCOUNT','HYD',500000);

VALUES

('D3','RESEARCH','MNG',300000);

VALUES

('D4','IT','BNG',400000);

VALUES

('D5','HR','BNG',200000);

------------------------------------------------------- PROJ----------------------------------------------------------------

INSERT INTO PROJ (DNO,PRJ_NO,PRJ_NAME,PRJ_CREDIT,PRJ_FUND)

VALUES('D1','P1','PRJ_1',4,400000);

VALUES('D3','P3','PRJ_5',3,400000);

------------------------------------------------------- EMP---------------------------------------------------------------

INSERT INTO EMP (EMPNO,ENAME,JOB,DATE_BIRTH,DEPTNO,DATE_JOIN,PRJ_ID,sal)

VALUES

(101,'RAVI','MGR',TO_DATE('10-10-1998', 'dd-mm-yyyy'),'D1',TO_DATE('01-09-2001','dd-mm-yyyy'),'P1',22000);

-------------------------------------------------------UPDATE-------------------------------------------------------------

UPDATE EMP SET MGRID=111 WHERE EMPNO=101

/*MGRID NUMBER MUDT BE PRESENTED IN EMPNO OTHER WISE ERROR OCCUR*/

UPDATE PROJ SET DNO='D5'  WHERE DNO='D1';

//GET ERROR.

UPDATE EMP SET COMM=10000 WHERE JOB='MGR'

-------------------------------------------------------COMMIT------------------------------------------------------------

COMMIT; 

// TO SAVE/PRESERVE  DATA FROM DATA DELETION.

-----------------------------------------------------ROLLBACK----------------------------------------------------------

ROLLBACK;

// TO RETRIVE DELETED DATA.