Common Oracle statement files (2)

Displaying Data from Multiple Tables

Types of Joins

SQL: 1999 Compliant Joins:

-- Cross joins

-- Natural joins

-- Using clause

-- Full or two sided outer joins

-- Arbitrary join conditions for outer joins

Syntax:

Select tableA. column, tableB. column from tableA

[Cross join tableB] |

[Natural join tableB] |

[JOIN tableB USING (column_name)] |

[JOIN tableB ON (tableA. column_name = tableB. column_name)] |

[LEFT | RIGHT | full outer join tableB ON (tableA. column_name = tableB. column_name)];

 

Oracle Proprietary Joins

-- Equijoin

Select a. columnA, B. columnB, c. columnC from tableA a, tableB B, tableC c where a. id = B. id and B. id = c. id;

-- Non-euqijoins

Select a. columnA, B. columnB from tableA a, tableB B where a. salary between B. lowestsalary and B. highestsalary;

-- Outer join

Select a. column, B. column from tableA a, tableB B where a. column (+) = B. column;

Select a. column, B. column from tableA a, tableB B where a. column = B. column (+ );

-- Self join

Select a. columnA, a. columnB from tableA a, tableA B where a. columnC = B. columnC;

 

Cartesian Products

Select ColumnA, ColumnB, ColumnC from tableA tableB;

Select columnA, columnB from tableA cross jion tableB;

 

Aggregating Data Using Group Functions

Types of group functions: Avg, count, max, min, sum, stddev, variance

Select count (distinct columnA) from dual;

Select avg (nvl (columnA, 0) from tale;

 

Using the group by clause on multiple columns

Select avg (salary) from tableA group by columnA, columnB;

Syntax:

SELECT column,Group_functionFROM table [WHERE condition]

[Group by group_by_expression] [having group_condition] [order by column];

 

Note: you can't useWhereClause to restrict groups but you can useHavingClause to restrict groups.

Error: select department_id, avg (salary) from employee where avg (salary)> 8000 group by department_id;

Right: select department_id, avg (salary) from employee group by department_id having avg (salary)> 8000;

 

Nesting group functions:

Select max (avg (salary) from employees group by department_id;

 

Subqueries

SELECT select_list FROM table WHERE exprOperator

(SELECT select _ list FROM table );

Operator:IN, ANY, ALL

IN: Equal to any member in the list

ANY: compare value to each value returned by the subquery

ALL: compare value to every value returned by the subquery

<ANY means less than the maximum

> ANY means more than the minimum

= ANY is equivalent to IN

<ALL means less than the minimum

> ALL means more than the maximum

The NOT operator can be used with IN, ANY and ALL operators.

If one of the values returned by the inner query is a null value, and hence the entire query returns no rows. the reason is that all conditions that compare a null value result is a null. so whenever null values are likely to be part of the results set of a subquery, do not use the not in operator. the not in operator is equivalent to <> ALL.

 

Manipulating Data

Data Manipulation Language

A dml statement is executed when you:

-Add new rows to a table

-Modify exsitng rows in a table

-Remove exsting rows from a table

ATransaction consistsOf a collection of DML statements that form a logical unit of work.

 

Insert into table [(column [, column…])] VALUES (value [, value…]);

Insert into table (login_time) values (TO_DATE (DATE ('feb 3, 100', 'mon DD, yyyy '));

Insert itno (SELECT columnA, columnB from tableA where id = 50) VALUES (avariableA, avariableB );

Insert into (columnA) values (default );

UPDATE table SET column = value [, column = value,…] [WHERE condition];

DELETE [FROM] table [WHERE condition];

WITH CHECK OPTIONKeyword

The with check option keyword prohibits you from changing rows that are not in the subquery

The MERGE statement

Provides the ability to conditionally update or insert data into a database table

Performs an update if the row exists, and an insert if it is a new row;

-- Avoids separate updates

-- Increases performance and capacity of use

-- Is useful in data warehousing applications

Syntax:

Merge into table_name table_alias

USING (table | view | sub_query) alias

ON (join condition)

WHEN MATCHED THEN

UPDATE SET

Col1 = col_vall,

Col2 = col_val2

WHEN NOT MATCHED THEN

INSERT (column_list)

VALUES (column_values );