|
"Give me a
museum and I'll fill it." - Pablo Picasso (1881-1973) |
Read
first then play the video:
SQL012(VIDEO)-WITH
SUMMARY AS and MERGE statements
Oracle9i: More SQL New
Features
Login as the iself user.
SQL> CONNECT iself/schooling
Set the linesize to 100 and
the pagesize to 55.
SQL> SET LINESIZE 100 PAGESIZE 55
WITH … AS statement
If you have a query which
it needs to process the same sub-query several times, you should
consider using the WITH …AS clause in your statement.
This process will help you
a great deal of performance. The query will create a temporary table
to query it over and over.
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.
SQL> 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
SQL>/
Multiple columns in SQL
statement
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.
SQL> SELECT deptno, ename, job, sal
FROM emp
WHERE (deptno, sal) IN
(SELECT deptno, MAX(sal)
FROM emp
GROUP BY deptno)
/
Inline View
If you have a sub-query in
a FROM clause in the Oracle SQL statement, is called an inline view.
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.
SQL> 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.
MERGE statement
To show an example of how
the MERGE statement works, pay careful attention to the following
demonstration.
First, create a table with two columns.
SQL> CREATE TABLE Merge2Here (c1 NUMBER,
c2 VARCHAR2(10))
/
Write a stored procedure to
populate the table.
SQL> BEGIN
SQL> FOR i IN 1..10 LOOP
SQL> INSERT INTO Merge2Here VALUES (i, i+10);
SQL> END LOOP;
SQL> COMMIT;
SQL> END;
SQL> /
Then, create a second table
with three columns.
SQL> CREATE TABLE
MoreGoodRows
(cc1 NUMBER, cc2
VARCHAR2(10), cc3 NUMBER)
/
Write a stored procedure to
populate it.
SQL> BEGIN
SQL> FOR i IN 1..3 LOOP
SQL> INSERT INTO MoreGoodRows VALUES (i, i+10, i+20);
SQL> END LOOP;
SQL> FOR i IN 104..110 LOOP
SQL> INSERT INTO MoreGoodRows VALUES (i, i+10, i+20);
SQL> END LOOP;
SQL> COMMIT;
SQL> END;
SQL> /
Now, query the Merge2Here
table.
SQL> SELECT * FROM Merge2Here
/
Also, query the
MoreGoodRows table.
SQL> SELECT * FROM MoreGoodRows
/
Take notes about the data in these two tables.
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.
SQL> MERGE INTO Merge2Here
USING MoreGoodRows
ON (c1 = cc1)
WHEN MATCHED THEN
UPDATE SET c2 = cc3
WHEN NOT MATCHED THEN
INSERT VALUES (cc1, cc2)
/
Now, query the Merge2Here
table.
SQL> SELECT * FROM Merge2Here
SQL> /
Notice that the first three rows were changed and the last
seven rows were added.
Then, query the
MoreGoodRows table.
SQL> SELECT * FROM MoreGoodRows
SQL> /
No changes on this table.
Drop both the Merge2Here
and MoreGoodRows tables.
SQL> DROP TABLE Merge2Here
SQL> /
SQL> DROP TABLE MoreGoodRows
SQL> /
SQL> DELETE FROM dept
WHERE deptno = 40
SQL> /
SQL> COMMIT
SQL> /
You have dropped your tables so that you can do this
Hands-On exercise over.
|
"In theory,
there is no difference between theory and practice. But, in
practice, there is." - Jan L.A. van de Snepscheut |
Questions:
Q: When do you use the WITH
… AS clause in the SQL statement?
Q: How does the WITH … AS
clause help your performance?
Q: Write a query to list
all the department names that their total paid salaries are more than
1/3 of the total salary of the company.
Q: What are the multiple
columns in the SQL statement? Where or how do you use them?
Q: Write a SQL statement to
query the name of all employees who earn the maximum salary in their
department using the multiple columns syntax.
Q: What is the inline view
in the Oracle SQL statement?
Q: Write a SQL statement to
query all of the employee names, jobs, and salaries where their salary
is more than 10% of the total company paid salary.
Q: What does the MERGE
statement in the SQL statement?
Q: Can you update, insert,
or delete any records while you are using the MERGE statement?
|
