iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - SQL 

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 |

 

Lesson 09

"Give me a museum and I'll fill it." - Pablo Picasso (1881-1973)

 

Read first then play the video:

   SQL-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?