Wednesday, October 23, 2013

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),
  CONSTRAINT ExamNamePK 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');

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect