Oracle Certification Notes: Part XIII

This is a continuation of my notes as I study for the Oracle Certified Associate crediential:

Data Manipulation Language (DML)

5 DML commands: SELECT, INSERT, UPDATE, DELETE, MERGE

May INSERT into 1 row using VALUES; May INSERT into multiple rows using SELECT

May INSERT into multiple tables at a time. As an example: INSERT ALL WHEN column1=1 INTO table1(column1) VALUES (retrieved.column1) WHEN column2=2 INTO table2(column1) VALUES (retrieved.column2) SELECT column1, column2 FROM table3;

TRUNCATE is technically considered a command of the Data Definition Language (DDL)

MERGE can be thought of as a way to perform an INSERT, UPDATE, or DELETE depending on the data circumstance. Instead of coding for all 3 scenarios, a MERGE does it all at once.

CONTROL Transactions

COMMIT, ROLLBACK, SAVEPOINT are control transactions

Relational database standards for transactions - ACID: atomicity, consistency, isolation, durability

Example: SAVEPOINT savepoint

Example: >ROLLBACK [TO SAVEPOINT savepoint]

Terminating transaction statements include COMMIT, ROLLBACK, and TRUNCATE. SAVEPOINT and ROLLBACK TO SAVEPOINT does not terminate a transaction.

Database Objects

An object (e.g., table, stored procedure) is owned by a database user; a set of objects owned by a database user is referred to as its schema.

Object names can be no more than 30 characters consisting of letters, numbers, underscore (_), dollar sign ($) and hash symbol (#)

Objects are defined by their namespace and schema_owner.object_name

Namespace is a group of object types. Stored Procedures, Tables, Views, Synonyms, and Sequences are considered to be in a namespace. Indexes are stored in a separate namespace.

The amount of limited space assigned to a table is known as an extent.

Posted via Woodland Blog (http://dramse01.blogspot.com)

Oracle Certification Notes: Part XII

This is a continuation of my notes as I study for the Oracle Certified Associate credential:

Subqueries: Overview

Subqueries can be included within SELECT, FROM, WHERE, GROUP BY and HAVING clauses.

The WHERE clause may only contain 255 levels of subqueries; whereas, an unlimited depth may be used within the FROM clause.

Example of subquery in a SELECT clause: SELECT (SELECT COUNT(*) FROM employees), 'Hello World!' FROM dual;

A subquery cannot be used within the VALUES clause of an INSERT statement

Multi-row subquery comparison operators include IN, NOT IN, ANY, ALL. = ANY is the same as IN.

An example of ANY/ALL: SELECT * FROM TABLE1 WHERE AMT > ALL (SELECT AMT FROM TABLE2);

Subqueries: Types

Single-row, multiple-row, and correlated subqueries

A correlated subquery: Where a subquery references a column from the parent query. Often inefficient construction of code.

A scalar subquery is one that only returns a single column from a single row - often substituted for a literal value.

Star Transformation: Modifying a query using traditional table joins to use IN-Subquery relationships. May have execution benefits, especially for data warehouses, as well easier to maintain. Oracle parameter included for automatic star transformation.

SET operators

UNION, UNION ALL, INTERSECT, MINUS (ISO equivalent: EXCEPT) are used to make a compound query

No order of precedence.** Read top-to-bottom, left-to-right. Use parentheses to establish order of precedence. **(yet. appears INTERSECT may eventually take precedence)

Columns from each query may be different in name, but must be of same datatype group. Column names from first query will be displayed as the result set.

Data from a compound query is returned with no duplicates and sorted by column values from left-to-right, with the exception of UNION ALL - returns all rows without sorting

ORDER BY can only be placed at the bottom of the last query of a compound query

Posted via Woodland Blog (http://dramse01.blogspot.com)

Oracle Certification Notes: Part XI

This is a continuation of my notes as I study for the Oracle Certified Associate credential:

Grouping Functions

COUNT(*) is the same as COUNT(ALL column)

Group functions ignore NULL values unless otherwise handled with NVL functions; As an example, this concept is important when using AVG()

Statistical group functions include VARIANCE() and STDDEV()

Group functions may be nested no more than two levels deep: G1(G2(column))

HAVING clause may only be used when a GROUP BY is also used

HAVING clause may contain multiple restrictions by use the AND, OR, & NOT conditions

Table Joins: Natural, Equi, Nonequi, and Self Joins

Equijoin: Matching a row from one table to one or more rows on another table based on equality operators

NATURAL JOIN can be used when the columns of the joining tables have the exact same column name. There are three methods.

As a natural join example: SELECT * FROM TABLE1 NATURAL JOIN TABLE2; is the same as SELECT * FROM TABLE1, TABLE2 WHERE TABLE1.column1 = TABLES2.column1;

Another natural join example: SELECT * FROM TABLE1 JOIN TABLE2 USING (COLUMN1);

Another natural example: SELECT TABLE1.* FROM TABLE1 JOIN TABLE2 ON (TABLE1.column1=TABLE2.column1);

Nonequijoin: Matching a row from one table to one or more rows on another table based on a range of inequality operators

Self-join: Matching a row from one table to one or more rows (usually in a hierarchical manner) on itself

All of the above are also considered to be Inner Joins

Table Joins: Cartesian and Outer Joins

Cartesian join: Also known as a cross-join. Where one row on a table matches to every row on another table. Number of rows returned equals TABLE1 count times TABLE2 count (5x3=15).

A cartesian join example: SELECT COUNT(*) FROM TABLE1 CROSS JOIN TABLE2;

A Cross Join may not contain an ON clause

A cartesian join occurs when no joins or fewer than N-1 (where N=number of tables) are specified in a given query

Outer join: Retrieving orphan records (or one-sided rows) from a table. Can use RIGHT OUTER JOIN ON, LEFT OUTER JOIN ON, or FULL OUTER JOIN ON

As a traditional Oracle example: SELECT TABLE1.* FROM TABLE1 LEFT OUTER JOIN TABLE2 ON (TABLE1.column1=TABLE2.column1); retrieves additional rows from TABLE1 that do not match the join criteria.

As an Oracle example: SELECT TABLE1.* FROM TABLE1, TABLE2 WHERE TABLE1.column1=TABLE2.column1 (+); retrieves additional rows from TABLE1 that do not match the join criteria.

The (+) asks for the additional rows that do match the join criteria on the opposite side of the equals sign

Posted via Woodland Blog (http://dramse01.blogspot.com)

Oracle Certification Notes: Part X

Here is a continuation of my notes as I study for the Oracle Certified Associate credential:

Character Conversion Functions

NVL2(value, if_not_null, if_null): Watch out that the 2nd parameter after the compared value is if_NOT_null

NULLIF(if_not_equal, compare_value): IF compare_value equals if_not_equal THEN return null; ELSE return if_not_equal;

COALESCE(if_not_null_1, if_not_null_2, ... if_not_null_n): Like using nested NVL() statements. It will return the first NOT NULL value.

Implicit Conversion: Oracle attempts to automatically convert a value into the appropriate datatype before execution

Explicit Conversion: Examples TO_CHAR(), TO_NUMBER(), or TO_DATE()

TO_NUMBER(value, mask): Watch out for the use of format masks. If the value doesn't fit within format mask, an error is thrown; whereas, an error would NOT be thrown if done as a part of TO_CHAR()

Date Formatting: SELECT TO_CHAR(SYSDATE, 'fmMonth, DDth, Yyyysp, YEAR') FROM dual; equals 'February, 3RD, Two Thousand Nine, TWO THOUSAND NINE'

Posted via Woodland Blog (http://dramse01.blogspot.com)