Advanced Oracle SQL

by Sandeep Desai (http://www.thedesai.net)

Book: Mastering Oracle SQL by Sanjay Mishra and Alan Beaulieu

Multitable inserts

INSERT ALL

INTO table1 (col1, col2)

VALUES (1, 2)

INTO table1 (col1, col2)

VALUES (3, 4)

INTO table2 (tcol1, tcol2)

VALUES (3, 4)

Insert rows returned by SELECT into different tables depending on WHEN condition

INSERT FIRST

WHEN condition1

INTO table1 (col1,...) VALUES (...)

WHEN condition2

INTO table2 (col1,...) VALUES (...)

SELECT ... FROM .. WHERE ...

MERGE allows you to insert new row or update existing row. (useful in Data Warehousing)

MERGE INTO table1dest

USING table2 src

ON (table1.key = table2.key)

WHEN MATCHED THEN UPDATE

SET src.col1 = dest.col1

WHEN NOT MATCHED THEN INSERT (...) VALUES (...)

WHERE REGEXP_LIKE(phone, “^781”)

WHERE mgr_empid IS NULL OR mgr_empid != 1234

WHERE NVL(mgr_empid, -999) != 1234 – NVL replace NULL with –999

-- ANSI JOIN

SELECT ...

FROM part p INNER JOIN supplier s

ON s.supplier_id = p.supplier_id

WHERE ....

Types of Joins

Cross joins (full cross product no = condition)
Inner Join (Regular Joins)
Outer Joins (Joined rows and not joined also e.g get Department and list of Employees for that department, return department with no employees also)

left outer join
right outer join
full outer join

Self join
Equi join and non equi join
Partition Outer Joins

View that have a key preserved tables can be updated (insert, update and delete) where row deleted depends on order of join

USER_UPDATABLE_COLUMNS dictionary view describes which columns are updatable

Create View with check option prevent delete that will remove rows from view

Aggregate Functions

COUNT(*) counts null

AVG(SALES) does not count null columns

AVG(NVL(SALES,0)) counts null

SELECT SUBSTR(lname, 1, 1), AVG(sales)

FROM emp

GROUP BY lname

HAVING AVG(sales) < 5000 – column referenced should be in select or group by

SELECT MAX(SUM(salary) FROM employee GROUP BY emp_id – nested group operations

ORDER BY foo NULLS FIRST

ORDER BY foo NULLS LAST

Subqueries

Corrleated subqueries (outer query references inner query column)

subquery executed once for each row

Noncorrelated subqueries

single-row, single-column subqueries (scalar subquery)

SELECT name FROM emp WHERE salary > (SELECT AVG(salary) FROM emp)

multiple-row, single-column subqueries (use ANY or ALL keyword)

SELECT name FROM emp where salary >= ALL (SELECT salary FROM emp)

multiple-column subqueries

DELETE FROM line_item WHERE (order_nbr, part_nbr) IN (SELECT order_nbr, part_nbr....)
UPDATE foo SET (c1, c2,..) = (SELECT ....)
WITH avg_sal as (SELECT ...) SELECT .. FROM avg_sal

Inline Views

Inline views are executed prior to the containing query, inline views must be noncorrelated.

SELECT sizes.name order_size, SUM(co.sales_price) tot_dollars FROM cust_order co INNER JOIN

(select ‘SMALL’ name, 0 lower_bound, 29 upper_bound FROM DUAL

UNION ALL

select ‘LARGE’ name, 30 lower_bound, 79 upper_bound FROM DUAL

) sizes

ON co.saes_price BETWEEN sizes.lower_bound AND sizes.upper_bound WHERE ...

GROUP BY sizes.name

ORDER BY sizes.name DESC

Hierarchical query (Parent child table)

Query cannot be joined

Will print tree depth first

SELECT child_id, name parent_id FROM hier START WITH name = ‘World’ CONNECT BY PRIOR child_id = parent_id

Analytic functions

SELECT salesperson_id, SUM(tot_sales) tot_sales, RANK() OVER (ORDER BY SUM(tot_sales) DESC) sales_rank

FROM orders WHERE ... GROUP BY salesperson_id

TEMPORAL QUERY

ALTER SESSION SET TIME_ZONE = ‘EST’;

Temporal Data Types

DATE (7 byte field, stores date and time)
TIMESTAMP (9i) (supports fractional seconds)

TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE

INTERVAL (9i)

INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND

Links