Oracle Database

If you want to query from two tables with a right outer join, you can use the RIGHT OUTER JOIN ... ON clause in the FROM clause. The following query returns output with a right outer join from two tables: departments and employees. The join condition is that the manager ID in the departments table equals to the employee ID in the employees table:

SQL> set NULL 'NULL'

SQL> SELECT d.department_name, e.first_name, e.last_name

2 FROM departments d RIGHT OUTER JOIN employees e

3 ON d.manager_id = e.employee_id;

DEPARTMENT_NAME   FIRST_NAME   LAST_NAME

-------------------- -------------------- ---------------

Administration  Jennifer   Whalen

Marketing    Michael   Hartstein

Purchasing   Den   Raphaely

Human Resources   Susan   Mavris

Shipping   Adam    Fripp

IT    Alexander  Hunold

......

NULL    Clara   Vishney

NULL    Jason   Mallin

NULL    Hazel   Philtanker

NULL    Nanette   Cambrault

NULL    Alana   Walsh

NULL    Karen   Partners

NULL    Bruce 		

If you don't want to use the LEFT OUTER JOIN ... ON clause to write a left outer join, you can use a special criteria in the WHERE clause as "left_table.column = right_table.column(+)". The select statement below is an example of a left outer join written with the WHERE clause:

SQL> set NULL 'NULL'

SQL> SELECT d.department_name, e.first_name, e.last_name

2 FROM departments d, employees e

3 WHERE d.manager_id = e.employee_id(+);

DEPARTMENT_NAME   FIRST_NAME   LAST_NAME

-------------------- -------------------- --------------

Administration  Jennifer   Whalen

Marketing    Michael   Hartstein

Purchasing   Den   Raphaely

Human Resources   Susan   Mavris

Shipping   Adam    Fripp

IT    Alexander  Hunold

......

Treasury   NULL    NULL

Corporate Tax   NULL    NULL

Control And Credit  NULL    NULL

Shareholder Services NULL    NULL

Benefits   NULL    NULL

Manufacturing   NULL    NULL

......

A subquery can be used with the IN operator as "expression IN (subquery)". The subquery should return a single column with one or more rows to form a list of values to be used by the IN operation. The following tutorial exercise shows you how to use a subquery with the IN operator:

SQL> SELECT first_name, last_name FROM employees

2 WHERE department_id IN (

3 SELECT department_id FROM departments

4 WHERE location_id = 1700

5 );

FIRST_NAME   LAST_NAME

-------------------- -------------------------

Steven   King

Neena   Kochhar

Lex   De Haan

Nancy   Greenberg

Daniel   Faviet

John    Chen

Ismael   Sciarra

......

A subquery can be used with the EXISTS operator as "EXISTS (subquery)", which returns true if the subquery returns one or more rows. The following statement is a good example of "EXISTS (subquery)". It returns rows from employees table that there are rows existing in the departments table linked to the employees table with location_id = 1700.

SQL> SELECT first_name, last_name FROM employees e

2 WHERE EXISTS (

3 SELECT * FROM departments d

4 WHERE e.department_id = d.department_id

5 AND d.location_id = 1700

6 );

FIRST_NAME   LAST_NAME

-------------------- -------------------------

Steven   King

Neena   Kochhar

Lex   De Haan

Nancy   Greenberg

Daniel   Faviet

John    Chen

Ismael   Sciarra

......

If you have a query returning many rows of data, and you want to perform another query on those rows, you can put the first query as a subquery in the FROM clause of the second query. The following statement shows you how to use a subquery as base table for the main query:

SQL> SELECT * FROM (

2 SELECT first_name, last_name, department_name

3 FROM employees e, departments d

4 WHERE e.department_id = d.department_id

5 ) WHERE department_name LIKE 'S%' ORDER BY last_name;

FIRST_NAME  LAST_NAME   DEPARTMENT_NAME

----------------- ---------------------- ---------------

Ellen   Abel    Sales

Sundar  Ande    Sales

Mozhe   Atkinson   Shipping

Amit   Banda   Sales

Elizabeth  Bates   Sales

Sarah   Bell    Shipping

f you Oracle server or client installed on your windows system, you can start the command-line SQL*Plus in two ways:

1. Click Start > All Programs > Oracle ... > Start SQL Command Line. The SQL*Plus command window will show up with a message like this:

SQL*Plus: Release 10.2.0.1.0 - Production on Tue ...

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL>

2. Click Start > Run..., enter "cmd" and click OK. A Windows command window will show up. You can then use Windows commands to start the command-line SQL*Plus as shown in the tutorial exercise below:

>cd c:oraclexeapporacleproduct10.2.0server

>.insqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue ...

Copyright (c) 1982, 2005, Oracle. All rights reserved.

If you use the COUNT(*) function on groups returned with the GROUP BY clause, it will count the number of rows within each group, not the number of groups. If you want to count the number of groups, you can put the GROUP BY query into a subquery and apply the COUNT(*) function on the main query as shown in the following tutorial exercise:

SQL> SELECT first_name, COUNT(*) FROM employees

GROUP BY first_name HAVING COUNT(*) > 1;

FIRST_NAME   COUNT(*)

-------------------- ----------

Peter     3

Michael     2

Steven    2

John     3

Julia     2

William     2

Karen     2

Kevin     2

......

SQL> SELECT COUNT(*) FROM (

 SELECT first_name, COUNT(*) FROM employees 

 GROUP BY first_name HAVING COUNT(*) > 1

 );

 COUNT(*)

----------

  13

Once SQL*Plus is started, you will get a SQL prompt like this: SQL>. This where you can enter commands for SQL*Plus to run.

To get help information at the SQL prompt, you can use the HELP command as shown in the following tutorial example:

SQL> HELP INDEX

Enter Help [topic] for help.

 @   COPY  PAUSE  SHUTDOWN

 @@  DEFINE  PRINT  SPOOL

 /   DEL  PROMPT SQLPLUS

 ACCEPT  DESCRIBE  QUIT  START

 APPEND  DISCONNECT RECOVER  STARTUP

 ARCHIVE LOG EDIT  REMARK STORE

 ATTRIBUTE  EXECUTE REPFOOTER TIMING

 BREAK  EXIT  REPHEADER TTITLE

...

 COMPUTE  LIST  SET  XQUERY

 CONNECT  PASSWORD  SHOW

SQL> HELP CONNECT

CONNECT

-------

If you have used some DML statements updated some data objects, and you want to have the updates to be permanently recorded in the database, you can use the COMMIT statement. It will make all the database changes made in the current transaction become permanent and end the current transaction. The following tutorial exercise shows you how to use COMMIT statements:

SQL> connect HR/globalguideline

SQL> INSERT INTO ggl_links (url, id)

2 VALUES ('rendc.org', 101);

SQL> INSERT INTO ggl_links (url, id)

2 VALUES ('rendc.org/html', 110);

SQL> SELECT * FROM ggl_links;

  ID URL   NOTES  COUNTS CREATED

------- ---------------- ---------- ---------- ---------

 101 rendc.org     07-MAY-06

 110 rendc.org/html     07-MAY-06

SQL> COMMIT;

Commit complete.

If you have used some DML statements updated some data objects, you find a problem with those updates, and you don't want those updates to be permanently recorded in the database, you can use the ROLLBACK statement. It will remove all the database changes made in the current transaction and end the current transaction. The following tutorial exercise shows you how to use ROLLBACK statements:

SQL> connect HR/globalguideline

SQL> INSERT INTO ggl_links (url, id)

2 VALUES ('google.com', 102);

SQL> INSERT INTO ggl_links (url, id)

3 VALUES ('myspace.com', 103);

SQL> SELECT * FROM ggl_links;

ID URL NOTES COUNTS CREATED

------- ---------------- ---------- ---------- ---------

101 rendc.org 07-MAY-06

110 rendc.org/html 07-MAY-06

102 google.com 07-MAY-06

103 myspace.com 07-MAY-06