SQL

Login as the oracle user.
SQL> CONNECT oracle/learning
 

Set the linesize to 100 and the pagesize to 55.
SQL> SET LINESIZE 100 PAGESIZE 55
 

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,

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)
/
 

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.
 

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.
 

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

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.

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?