DML Operation in Oracle

UPDATE EMP

SET Comm = NULL

WHERE Job = 'SALESMAN';

UPDATE Emp

SET(Job, Deptno) = (SELECT Job, Deptno

                    FROM Emp

                    WHERE Empno = 7499)

WHERE Empno = 7698 ;

UPDATE Emp

SET Deptno = (SELECT Deptno

                FROM Emp

                WHERE Empno = 7788)

WHERE Job = (SELECT Job

                FROM Emp

                WHERE Empno = 7788);

               

UPDATE  Emp

SET Job = 'MANAGER',

    Sal = 2500,

    Deptno = 10

WHERE Ename = 'ALLEN';

   

UPDATE Emp

SET Job = 'ANALYST',

Sal = Sal + 1000

WHERE Ename = 'BLAKE';

UPDATE Emp E1

SET Deptno = (SELECT Deptno

            FROM Dept

            WHERE Loc = 'DALLAS'),

    (Sal, Comm) = (SELECT 1.1 * AVG (Sal),

                        1.5 * AVG ( Comm )

                    FROM Emp E2

                    WHERE E1.Deptno = E2.Deptno)

WHERE Deptno IN (SELECT Deptno

                    FROM Dept

                    WHERE Loc = 'NEW YORK' OR

                    Loc = 'BOSTON');

UPDATE Emp

SET Sal = Sal * 1.10

WHERE Deptno = (SELECT Deptno

                FROM Dept

                WHERE Loc = 'CHICAGO');

CREATE TABLE MyBonus

(

 Empno NUMBER,

 Bonus NUMBER DEFAULT 100

 );

INSERT INTO MyBonus(Empno)

(SELECT E.Empno

    FROM Emp E

    WHERE Job = 'SALESMAN');

MERGE INTO MyBonus B

USING (SELECT Empno, Sal, Deptno

        FROM Emp

        WHERE Deptno = 30) S

ON (B.Empno = S.Empno)

WHEN MATCHED THEN

UPDATE

    SET B.Bonus = B.Bonus + S.Sal * 0.1

DELETE

WHERE (S.Sal > 4000)

WHEN NOT MATCHED THEN

INSERT(B.Empno, B.Bonus)

VALUES(S.Empno, S.Sal * 0.1)

WHERE(S.Sal <= 4000);  

CREATE TABLE ExamTimeTable

(

  ExamName VARCHAR2(30),

  ExamTime   VARCHAR2(12),

  CONSTRAINTExamNamePK PRIMARY KEY(ExamName)

  );

 

INSERT INTO ExamTimeTable

VALUES ('PHYSICAL SCIENCES' , '9:00 AM');

MERGE INTO ExamTimeTable E1

USING ExamTimeTable E2 ON

(E2.ExamName = E1.ExamName AND

 E1.ExamName = 'PHYSICAL SCIENCES')

 WHEN MATCHED THEN

 UPDATE

 SET E1.ExamTime = '10:30 AM'

 WHEN NOT MATCHED THEN

 INSERT(E1.ExamName, E1.ExamTime)

 VALUES('PHYSICAL SCIENCES' , '10:30 AM');

MERGE INTO ExamTimeTable E1

USING ExamTimeTable E2 ON

(E2.ExamName = E1.ExamName AND

 E1.ExamName = 'CHEMICAL SCIENCES')

 WHEN MATCHED THEN

 UPDATE SET E1.ExamTime = '12:30 PM'

 WHEN NOT MATCHED THEN

 INSERT(E1.ExamName, E1.ExamTime )

 VALUES('CHEMICAL SCIENCES' , '12:30 PM');