In the “Introduction 02” movie you will learn more about some Oracle9i: SQL New Features such as the “MERGE” and  “WITH summary_name AS” statements.

-- Introduction 02 ("Must to know" Oracle9i: SQL New Features)

SET ECHO ON
CLEAR SCR
-- Login as the iself user.
-- 
pause

CONNECT iself/schooling
pause

CLEAR SCR
-- Set the linesize to 100 and the pagesize to 55.
--
pause

SET LINESIZE 100 PAGESIZE 55
pause

CLEAR SCR
-- Use the (WITH ... AS) statement
-- to query all the department names that 
-- their total paid salaries are more than
-- 1/3 of the total salary of the company.
--
pause

WITH summary_totals AS
(SELECT dname, SUM(sal) AS totals 
FROM emp NATURAL JOIN dept GROUP BY dname)
SELECT dname, totals FROM summary_totals
WHERE totals > (SELECT SUM(totals)*1/3 FROM summary_totals)
ORDER BY totals DESC
/
pause

CLEAR SCR
-- You can use multiple columns to match the multiple
-- columns returned from the sub-query.

-- Get the name of all employees who earn the maximum 
-- salary in their department.
--
pause

SELECT deptno, ename, job, sal
FROM emp
WHERE (deptno, sal) IN 
(SELECT deptno, MAX(sal) 
FROM emp
GROUP BY deptno)
/
pause

CLEAR SCR
-- Use the (inline view) to query all of the employee
-- names, jobs, and salaries where their salary is more than
-- 10% of the total company paid salary.
--
pause

SELECT ename, job, sal
FROM (SELECT ename, job, sal
FROM emp
WHERE sal > (SELECT SUM(sal) * .1 FROM emp)
ORDER BY 3)
/
-- This is an example of the inline view query.
pause

CLEAR SCR
-- To show an example of how the MERGE statement
-- works, pay careful attention to the following
-- demonstration.

-- First, create a table with two columns.
--
pause

CREATE TABLE Merge2Here (c1 NUMBER, c2 VARCHAR2(10))
/
pause

CLEAR SCR
-- Write a stored procedure to populate the table.
-- 
pause

BEGIN 
FOR i IN 1..10 LOOP 
INSERT INTO Merge2Here VALUES (i, i+10);
END LOOP;
COMMIT;
END;
/
pause

CLEAR SCR
-- Then, create a second table with three columns.
--
pause

CREATE TABLE MoreGoodRows (cc1 NUMBER, cc2 VARCHAR2(10), cc3 NUMBER)
/
pause

CLEAR SCR
-- Write a stored procedure to populate it.
--
pause

BEGIN 
FOR i IN 1..3 LOOP 
INSERT INTO MoreGoodRows VALUES (i, i+10, i+20);
END LOOP;

FOR i IN 104..110 LOOP 
INSERT INTO MoreGoodRows VALUES (i, i+10, i+20);
END LOOP;
COMMIT;
END;
/
pause

CLEAR SCR
-- Now, query the Merge2Here table.
--
pause

SELECT * FROM Merge2Here
/
pause

CLEAR SCR
-- Also, query the MoreGoodRows table.
--
pause

SELECT * FROM MoreGoodRows
/
-- Take notes about the data in these two tables.
pause

CLEAR SCR
-- Use the MERGE statement to merge the MoreGoodRows table
-- into the Merge2Here table using the first column as 
-- a join column.

-- If there was a match, you should replace the third column
-- value of MoreGoodRows with the second column of the Merge2Here table.

-- On the other hand, if there was no match, just insert the 
-- first two columns of data into the Merge2Here table.
-- 
pause

MERGE INTO Merge2Here
USING MoreGoodRows ON (c1 = cc1)
WHEN MATCHED THEN UPDATE SET c2 = cc3
WHEN NOT MATCHED THEN INSERT VALUES (cc1, cc2)
/
pause

CLEAR SCR
-- Now, query the Merge2Here table.
--
pause

SELECT * FROM Merge2Here
/
-- Notice that the first three rows were changed and 
-- the last seven rows were added.
pause

CLEAR SCR
-- Then, query the MoreGoodRows table.
--
pause

SELECT * FROM MoreGoodRows
/
-- No changes on this table.
pause

CLEAR SCR
-- Drop both the Merge2Here and MoreGoodRows tables.
--
pause

DROP TABLE Merge2Here
/
DROP TABLE MoreGoodRows
/
DELETE FROM dept 
WHERE deptno = 40
/
COMMIT
/
-- You have dropped your tables so that you can do this Hands-On
-- exercise over and over again.
pause

CLEAR SCR
-- Now, practice this exercise over and over
-- again until you become a master at it.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck!
Pause