OCP SQL Exam

Overview of Oracle

RDBMS - Relational database management system
SQL - Structured Query Language

An RDBMS translates a SQL statement into a series of operations that retrieve the actual data from a file. This step is called parsing. After parsing, the RDBMS executes the statements to complete the action.

A flat file system has the disadvantegeous of:
Not being easily adaptable to changing business needs
Does not handle data relationships other than parent/child.
Does not always handle data retrieval easily

3 Components of Oracle Database

Memory - System Global Area (SGA).
Disk - Datafiles, redo logs, control files, password files and parameter files.
Process - Threads in oracle.exe (Windows) or individual processes (UNIX).

SGA:
Buffer Cache - Stores data in memory, it uses a LRU algorithm to determine what to write back to disk.
Log Buffer - Stores redo.
Shared Pool - Library cache (parsed SQL), dictionary or row cache, latches and other control mechanisms.
Large Pool - Supports backup and restore, I/O server processes and session memory (it's optional).
Other Memory Areas - Such as Java pool.

Disk Components:
Datafiles (at least 1)
Redo logs
Control Files (at least 1)
Password files (optional)
Parameter files

ORDBMS - Oracle has adjusted the RDBMS to include Object Support, this includes:
Permitting users to define the structure of the data they wish to store.
Allowing users to define methods for manipulating that data and associating these to the data.

Usage & Benefits of PL/SQL
Easy to learn & use.
Stored in the database, so improves performance.
Integrates well with database
Adept at processing large blocks of data
Comes with lots of Oracle-supplied code to assist in performing tasks
Suppots named and anonymous programs
Integrated via triggers
Supports encapsulation and modularization
Supports overloading
Allows you to package code
Supports advanced datatypes
portable (between operating systems)

Basic SQL Statements

select - not part of DML
DML - insert, update and delete (not select)
DDL - create, alter and drop
DCL - (data control language) grant and revoke
Transaction control activity - commit, rollback and savepoint.

SQL is a functional language, you define the results you want.
iterative or procedural languages are C++ or COBOL - they define the process.

Select statements are split into two parts

select or column clause
from or table clause

Operator Precedence
Please Excuse My Dear Aunt Sally - parentheses, exponents, multiplication and division, addition and subtraction.

DUAL
As every SELECT statement must have a column clause and a table clause DUAL exists where you don;t want to specify a table.
Ex. Select 2 + 2 from dual;

NULL
Null is the absence of data.
To sunstitue a value for null use nvl().
Ex. select empno, ename, nvl(mgr,0) from emp;
The value of the returned must be the same datatype as the column specified.

DISTINCT - Returns unique values for a column.
Ex. Select distinct job, empno from emp;
In the above example Oracle will identify all distinct combinations of job and empno.

Changing Headings with Aliases
By default, the heading is displayed exactly as you defined the column in the select statement.
To change the heading use an aliase (you can omit the as).
Ex. select empno, ename, nvl(mgr,0) as mgr;

Concatenate Columns
You cancatenation to glue columns together, || or concat().
Ex. select ename || ', who is the '|| concat(job, for the company') as "Name & Role" from emp;

Name & Role
--------------------------------------------
SMITH, who is the CLERK for the company
ALLEN, who is the SALESMAN for the company

SQL & SQL*Plus

Change
lineno
c(hange)/'none'/0

ed(it)
Access to text editor
most recently stored statement is in afeidt.buf
define_editor='youreditor'

@testfile
start textfile
get textfile (places it in afeidt.buf)
/ runs afeidt.buf

/ SQL*Plus stores the most recently executed statement in afiedt.buf.

GET filename - loads file into buffer (afiedt.buf)
START filename or @filename - loads file into buffer and executes it.

DESC(RIBE) - description.
LIST - Lists contents of buffer
DEL number - Deletes line number from buffer.
APPEND string - Adds string specified to current line.
CLEAR BUFFER - clears the buffer.
INPUT - Allows you to start adding to buffer at current line.
RUN - executes the buffer.
number string - Adds the string as the line number specified, replacing one that already exists.
SPOOL {filename|OFF|OUT} Writes all output following this command to filename.
SAVE filename - Places contents of buffer into a file called filename.sql.

ARRAYSIZE[ARRAY] {15|n} - Sets the number of rows that SQL*Plus fetches from the database at one time. Large values increase the efficiency of large queries but increases memory usage.
COLSEP { |text} - Sets the text to be printed between selected columns.
FEEDBACK [FEED] {6|n|OFF|ON} - This turns on/off the display of the number of rows selected when greater than n (0 = off, 1 = on).
HEADING [HEA] {OFF|ON} - ON - print headings on reports.
LINESIZE [LIN] {80|n} - Number of chars display on one line (max size is system dependent).
LONG {80|n} - Sets maximum width for displaying LONG, CLOB & NCLOB, max value is 2GB.
PAGESIZE [PAGES] {24|n} - Sets number of lines in a page, setting to 0 suppresses all headings, page breaks, titles etc.
PAUSE [PAU] {OFF|ON|text} - ON causes the report to pause at the beginning of each page (press enter), the text one displays text as pausing.
TERMOUT [TERM] {OFF|ON} - Controls the output of commands executed from a command file, OFF suppresses the display so it can not be seen on the screen.

Producing Readable Output
COLUMN {col} FORMAT {FMT} HEADING {string} - col is the name of the column (column col off - turns formatting off).
EX. column sal format $9,999.99
column format ename a12
define_editor='youreditor' changes the text editor used.
Set feedback off - stops the row count at the end of a select statement.
define - can be used to set a variable for all occurences of it within a script

Limiting, Sorting & Manipulating Return Data

Order by
This usually appears last in the SQL statement, each column can be followed by asc or desc.
Asc order is the default. NULL records will be placed at the end in asc, and at the top in desc.
It can be applied to NUMBER, VARCHAR, CHAR and DATE datatypes.
(You can use numbers in place of column names select empno, ename from emp order by 2)

Where
follows the select and from clasue
where clauses perform comparisons based on one or more of the following operations

x = y
x > y
x >= y
x < y
x <= y
x <> y, x != y, x ^= y
like	in conjunction with % (multiple chars) _ (single chars)
soundex	fuzzy logic - sounds like
between	between 300 and 500 (allowed on numbers, cahrs and date)
in (set)

Multiple comparisons can be strung together with

x and y
x or y
not x
x is NULL	returns TRUE if the value is NULL. Eg. select ename from emp where mgr is null;

Order of precedent

oracle compare comparison operators
'and'
'or'
'not'

Single row functions
Single row functions operate on individual column records in a row for every row returned.

Text functions
lpad(x,y[,z]) and rpad(x,y[,z])	return data in string or column x padded to left or right respectively to width y. Optional z indicates the character(s) to use, default is space.
lower(x), upper(x), initcap(x)	Return data in string or column x in lowercase or uppercase or cahnge initial letter to capital.
length(x)	Returns the number of characters in string or column
substr(x,y[,z])	returns substring of x, starting in column y (until end or z).
instr(x,y)	Determines if substr y can be found in x Eg. instr('CORPORATE FLOOR','OR') returns 2
trim( [[keyword] 'x' from] y)	keyword is replace by leading, trailing or both, or its omitted. x is replace with character to be trimmed or its omitted (default whitespace). y is the column to be trimmed. It will only trim leading or trailing charaters, not those occuring in the middle.
Arithmetic Functions	all having NULL as input will output NULL
round(x,y)	rounds x to decimal precision of y. if y -ve rounds to left of decimal point. Eg. round(134.01,-1)=130. It can also be used on DATE functions
ceil(x)	same as above but always rounds up
floor(x)	rounds down
mod(x,y)	modulus of x, integer remaining when x divided by y mod(10,3)=1
sign(x)	1 if x +ve, -1 if x -ve
sqrt(x)
trunc(x,y)	same as round but truncates
vsize(x)	storage size in bytes for value x
List Functions
greatest(x,y, ...) least(x, y, ...)	returns the highest, or lowest value from the list of text, strings, numbers or dates
decode(column_name, value1, substitute1, value2, substitute2, ... , return_default)
Date Functions
add_months(x, y)	returns date x with y months added
last_day(x)	returns last date of month that contains date x
months_between (x,y)	returns number of months between x and y. +ve or -ve, if x and y same day in different months result is integer, otherwise a decimal.
new_time(x,y,z)	returns the current date and time for date x in time zone y as it would be in time zone z
next_day(x)	identifies the name of the next day from the given date x
Conversion Functions
to_char(x)	converts the value x to char or a date to a char string - see date formatting below
to_number(x)	converts nonnumeric value x to a number
to_date(x,[y])	converts nondate value x to a date using format y
to_multi_byte(x)	converts single_byte char string x to multibyte chars according to national language standards
to_single_byte	converts the multibyte char string x to single byte char string
chartorowid(x)	converts the string of chars x into an Oracle ROWID
rowidtochar(x)	converts rowid to string chars (varchar2)
hextoraw / rawtohex	converts hexidecimal (base 16) value x into raw (binary)
convert(x, [,y [,z]])	Executes a conversion of alphanumeric string x from current char set (optionally specified as Z) to one specified as Y
translate (x, y, z)	Executes a simple value conversion for char or numeric string x into something else based on the conversion factors y and z

These are only the most common functions - need to add more

Date Arithmetic

Oracle stores dates as numbers, so you can perform arithmetic on them.

To add 180 days:
select hiredate + 180 as "Review Date"
from emp;

2 subtract 2 dates to find the number of weeks between them:
select ename, (hiredate-sysdate)/7 as "Weeks at Work"
from emp;

Date Formating

DD - 2 digit day DAY - day spelled out MON - 3 letter month abbreviation MONTH - month spelled out YY, YYYY, not millennium-compliant RR - RRRR year in digits, millenium compliant HH - 2 digit hour (not 24), must use MIAM mask HH24 - 2 digit 24 hour (cannot use MIAM) MI - 2 digit minute (24 hour) MIAM 2 digit minute SS - 2 digit second

Advanced Data Selection

Cartesian products
formed when you omit a join condition - usually meaningless, all columns in table a joined to all colums in table b. So two tables with 2 rows each will produce a result with 4 rows.

The same result is produced with the ANSI/ISO standard via a cross join
select col1, col2
from example1 cross join example2;

Join syntax ANSI/ISO
Select ename, deptno, dname
from emp join dept
on emp.deptno = dept.deptno;


Natural Joins
select ename, deptno, dname
from emp natural join dept;

This will join the table based on columns sharing the same name.

To not get a cartesian product include N-1 equality comparisons

Outer Joins
Handle situations where you want to see information from tables even when no corresponding rows exist in the common column.

Oracle standard
select e.ename, e.deptno, d.dname
from dept d, emp e
where d.deptno (+) = e.deptno;
This will list columns in emp even if a corresponding row does not exist in dept

ANSI/ISO
select e.ename, e.deptno, d.dname
from emp e felt outer join dept d
on d.deptno = e.deptno

- you must state left or right (there is no default)
Self Join - Join a table to itself
- useful if there are some slight differences between 2 rows that would otherwise be identical.
- can take a long time to process and cause performance problems (2 complete table scans)
need 2 equality operations (1 to join table, 1 to differentiate table) so easy to get cartesian product.

Group Functions and their uses

avg(x)	Averages column x
count(x)	Counts the number of non-NULL values returned by the select statement for column x.
max (x)	max value in column x
min (x)	min value in column x
stddev (x)	Standard deviation for all values in column x
sum (x)
variance (x)

select avg(sal) from emp;

select count(*) from emp - slow
select count(1) from emp - fast
select count(ROWID) from emp - fast

In general, group functions operate on columns of datatypes NUMBER and DATE (except count, min and max)
They ignore NULL values by default, so use nvl() to force oracle to look at null values

select avg(nvl(comm,0)) from emp;
as select avg(comm) will only provide average of not null columns.
count(comm) would only count not null comm rows, so use count(rowid) which is faster than count(*)

Group by Clause

select job, count(job) from emp;
ORA-00937: not a single-group group function

so use
select job, count(job) from emp group by job;

All columns in select that are not a group function should be in the group by clause. No nongorup expression can appear after the group expression in the column clause. The output is ordered based on order of clauses in group by clause you can change this via an order by clause.

select deptno, job, avg(sal)
from emp
group by job, deptno
order by 3 desc;

Exluding group data with having

Once data is grouped (using group by) you can weed out data with having.
Eg. From previous query only report job titles where the average salary is over $2000 (in effect a where clause on the group).
select deptno, job, avg(sal)
from emp
group by deptno, job
having avg(sal) > 2000;

having clause can be placed before or after the group by, it is logical to place it afterwards.

OLAP (Online analytical processing) Features

Useful for datawarehousing and data mart applications and business decision making processes.
top_N query (performance enhancing - discussed later

rollup - used in group by
Its used to produce subtotals. To get a subtotal for each deptno and job:

select deptno, job, sum(sal) as salary
from emp
group by rollup(deptno, job);

cube - cube is similar to rollup but n-dimensional so gives us summarys as before plus seperate summaries by each job

select deptno, job, sum(sal) as salary
from emp
group by cube(deptno, job);

The following shows output from the rollup statement and then the cube:

deptno	job	salary		deptno	job	salary
10	clerk	1300		10	clerk	1300
10	manager	2450		10	manager	2450
10	president	5000		10	president	5000
10		8750		10		8750
20	analyst	6000		20	analyst	6000
20	clerk	1900		20	clerk	1900
20	manager	2975		20	manager	2975
20		10875		20		10875
					analyst	6000
					clerk	3200
					manager	5425
					president	5000
		19625				19625

These statements replace lengthy queries using UNION ALL operations.

Subqueries (query within a query)

They are used to obtain values for parent select statements when specific search criteria isn't known. The where clause in the parent select statement must have a comparison operation and the subquerie should be inside parentheses and on the right of the comparison operator. The inner subquery executes once, before the main outer query executes.
comparison clauses =, <,>,<=,<>,>=, in, exists can be used

Correlated subqueries
This is where the subquery references a column in a table in the parent statement.
Each correlated subquery is executed once for every row in the parent query.

There can be upto 255 subqueries in a single select.

select e.ename, e.job, e.sal
from emp e
where exists
(select d.deptno
from dept d
where d.loc = 'NEW YORK'
and d.deptno = e.deptno);

Different Types of subqueries:

Single-row - the subquery returns 1 row (can use = in condition)
if you use = and many rows are returned from subquery then ORA-01427 - single row subquery returns more than 1 row

Multirow-row - the subquery returns multiple rows, the condition must be able to handle multiple rows (like in).

having clause and subqueries -

select deptno, job, avg(sal)
from emp
group by deptno, job
having avg(sal) >
(select sal
from emp
where ename = 'MARTIN');

Order by clause - The order by clause cannot appear inside the subquery, only in the outer query.

The 'with' clause and subqueries (new in 9i)

Queries may need to process the same subquery several times, once for each row obtained by the main query. This cause a lot of overhead. Oracle now provides the 'with' clause to help. You give the subquery a name, then reference it several times within the original query. This lets Oracle choose how to deal with the results, it might create a temporary table or use an online view.

select dname, sum(sal) as dept_total
from emp, dept
where emp.deptno = dept.deptno
group by dname having sum(sal) >
(select sum(sal)*1/3
from emp, dept
where emp.deptno = dept.deptno)
order by sum(sal) desc

bold - are bits performed many times

with summary as
(select dname, sum(sal) as dept_total
from emp, dept
where emp.deptno = dept.deptno
group by dname)
select dname, dept_total
from summary
where dept_total >
(select sum(det_total) * 1/3
from summary)
order by dept_total desc;

bold - are bits performed only once

Writing multiple column subqueries

To handle multiple columns returned from a subquery

select deptno, ename, job, sal
from emp
where (deptno, sal) in
(select deptno, max(sal)
from emp
group by deptno)

You must enclose the multiple columns requested in the main query in parentheses (otherwise error 'invalid operational operator'). Also the column order must match in main query and subquery.

SQL*Plus

/ SQL*Plus stores the most recently executed statement in afiedt.buf.

get filename - loads file into buffer (afiedt.buf)
start filename or @filename - loads file into buffer and executes it.

DESC(RIBE) - description.
LIST - Lists contents of buffer
DEL number - Deletes line number from buffer.
APPEND string - Adds string specified to current line.
CLEAR BUFFER - clears the buffer.
INPUT - Allows you to start adding to buffer at current line.
RUN - executes the buffer.
number string - Adds the string as the line number specified, replacing one that already exists.
SPOOL {filename|OFF|OUT} Writes all output following this command to filename.
SAVE filename - Places contents of buffer into a file called filename.sql.

ARRAYSIZE[ARRAY] {15|n} - Sets the number of rows that SQL*Plus fetches from the database at one time. Large values increase the efficiency of large queries but increases memory usage.
COLSEP { |text} - Sets the text to be printed between selected columns.
FEEDBACK [FEED] {6|n|OFF|ON} - This turns on/off the display of the number of rows selected when greater than n (0 = off, 1 = on).
HEADING [HEA] {OFF|ON} - ON - print headings on reports.
LINESIZE [LIN] {80|n} - Number of chars display on one line (max size is system dependent).
LONG {80|n} - Sets maximum width for displaying LONG, CLOB & NCLOB, max value is 2GB.
PAGESIZE [PAGES] {24|n} - Sets number of lines in a page, setting to 0 suppresses all headings, page breaks, titles etc.
PAUSE [PAU] {OFF|ON|text} - ON causes the report to pause at the beginning of each page (press enter), the text one displays text as pausing.
TERMOUT [TERM] {OFF|ON} - Controls the output of commands executed from a command file, OFF suppresses the display so it can not be seen on the screen.

Producing Readable Output
COLUMN {col} FORMAT {FMT} HEADING {string} - col is the name of the column (column col off - turns formatting off).
EX. column sal format $9,999.99
column format ename a12
define_editor='youreditor' changes the text editor used.
Set feedback off - stops the row count at the end of a select statement.

Creating Database Objects

Describing Tables

desc[ribe] tablename
A listing of NOT NULL for the column usually indicates that this
column is the primary key.
It also shows datatypes, but not foreign keys

Creating Tables

You need CREATE TABLE privilege
The maximum number of columns a table can have is 1000.

CREATE TABLE employee
(empid varchar2(5),
firstname varchar2(10),
salary number(7));

Creating Temporary Tables

The definition of a table is visible to all but the data only to the session that inserts it.
The ON COMMIT keywords indicate if the data in the table is transaction specific (ON COMMIT DELETE ROWS)
(default) or session specific (ON COMMIT PRESERVE ROWS).
Indexes can also be created, they are also temporary and have same rules as table.

CREATE GLOBAL TEMPORARY TABLE temp_emp
(empno number,
ename varchar2(10))
ON COMMIT DELETE ROWS;

Creating one table with data from another

CREATE TABLE emp_copy
AS SELECT * FROM emp;

This didn't require use to specify columns in the table to be created as a * was used in the select statement.

CREATE TABLE emp_copy
(empno, sal) AS
SELECT empno, sal FROM emp
WHERE deptno = 10;

This did require (empno, sal) as columns (not *) were specified in select statement.

Any select statement can be included.

Table Naming Conventions

Database object names must begin with a letter and be between 1 and 30 chars 
(except for database names 8 char and link names 128 chars). Names are not case sensitive.

A user cannot own 2 objects of the same name.

Don't call a table DUAL, begin names with SYS or call one after an Oracle reserved word 
(or a reserved word of the product you use to access Oracle).

You can only use 3 special characters #, $, and _. Oracle recommeds not using # and $ 
as they are used by Oracle specific views.

Don't use special characters from European or asian character sets in database names, 
global database names or database link names.

Datatypes and Column Definitions

Column Default Values

CREATE TABLE display
(col1 varchar2(10),

Altering Table Definitions

Adding new columns

ALTER TABLE employee ADD (hire_date DATE);

You can increase the size of a column but decreasing requires there to be no column data.
You can change the datatype (requires empty data)

ALTER TABLE products MODIFY (lastname VARCHAR2(25));

Dropping columns

The following removes the column and all contents
ALTER TABLE employee DROP COLUMN salary;

Dropping, Renaming and Truncating Tables

Dropping Tables

You cannot rollback a dropped table.
It must be part of your schema unless you have DROP ANY TABLE privilege.
With CASCADE CONSTRAINTS Oracle drops any associated index, trigger or constraint.

DROP TABLE emp; 

Truncating a Table

Deletes table data, its DDL so no rollback. Its faster than using DELETE.
It resets the high-water mark to zero.

Renaming Tables

You can rename a table with either the RENAME or the ALTER TABLE command.

RENAME table1 TO table2;

ALTER TABLE table1 RENAME TO table2;

Commenting Objects

You can add comments to a table or column.

COMMENT ON TABLE emp IS 'xxxxx';
COMMENT ON COLUMN emp.empid IS 'xxxxxx';

To access these comments USER_TAB_COMMENTS (tables) or 
USER_COL_COMMENTS (columns)     

Including Constraints

Constraints accomplish 3 goals:
Create real relationships between tables
Prevent unwanted data getting into the database
Prevent deletion of data if dependencies exist between tables

5 types of constraints

Two methods exist for defining constraints: the table constraint method and the column constraint method.
NOT NULL constraints can only be defined as column constraints
Primary keys consisting of 2 or more columns must be defined as table constraints.

CREATE TABLE employee
(empid varchar2(5),
lastname varchar2(25),
firstname varchar2(25),
salary number(10,4),
constraint pk_employee_01
primary key (empid);

CREATE TABLE employee
(empid varchar2(5)
constraint pk_employee_01 primary key,
lastname varchar2(25),
firstname varchar2(25),
salary(10,4));

or

CREATE TABLE department
(department_num number(5) primary key,
department_name varchar2(25),
location varchar2(25));

The second way of defining a column constraint is simpler but means that oracle names the constraint for you using the convention SYS_Cnnnnn.

you can also use alter table

ALTER TABLE Emp_tab
ADD CONSTRAINT Dept_fkey FOREIGN KEY (Deptno) REFERENCES Dept_tab;

The creator of a constraint must have the ability to create tables (the CREATE TABLE or CREATE ANY TABLE system privilege), or the ability to alter the table (the ALTER object privilege for the table or the ALTER ANY TABLE system privilege) with the constraint. Additionally, UNIQUE and PRIMARY KEY integrity constraints require that the owner of the table have either a quota for the tablespace that contains the associated index or the UNLIMITED TABLESPACE system privilege. FOREIGN KEY integrity constraints also require some additional privileges.

Foreign Key Constraints

To be valid the columns appearing in both tables must have the same data type but not the same names.

CREATE TABLE employee
(empid varchar2(5) primary key,
lastname varchar2(25),
department_num number(5)
references department (department_num)
on delete set null);

A foreign key cannot be created on a child until the parent table is created and the primary key defined.
You can have a self-referential integrity constraint where one column in a table points to its own tables primary key. Eg every value in manager_id corresponds to an employee_id.
Foreign keys can be composite but must reference a composite or primary or unique key with the same number of columns. Composite keys are all limited to 32 columns.

You should almost always index foreign keys except where the matching unique or primary key is never updated or deleted. Otherwise a greater level of locking is required when the referenced key is updated or deleted.

Options for foreign keys
ON DELETE CASCADE - when parent deleted, delete child. Parent key info cannot be updated
ON DELETE SET NULL - set foreign keys to null when parent deleted, parent cannot be updated
DEFAULT (nothing) - prevents update or delete of parent key

CHECK integrity Constraints

It must be boolean expression
It cannot contain subqueries
It cannot include SYSDATE, UID, USER, USERENV or LEVEL or ROWNUM

A single column can have multiple check constraints.

Oracle performs check constraints after the statement has been executed, until all statements in a transaction have been executed.
SO:
UPDATE employees
SET employee_id = employee_id + 5000,
manager_id = manager_id + 5000;
is valid even though there is a check constraint that verifies that each manager_id matches an employee_id as Oracle checks for this once all rows have been updated.

According to the ANSI/ISO standard, a NOT NULL integrity constraint is an example of a CHECK integrity constraint, where the condition is the following: CHECK (Column_name IS NOT NULL)

Therefore, NOT NULL integrity constraints for a single column can, in practice, be written in two forms: using the NOT NULL constraint or a CHECK constraint. For ease of use, you should always choose to define NOT NULL integrity constraints, instead of CHECK constraints with the IS NOT NULL condition

Indexes Created by Constraints

Unique indexes are created automatically for primary and unique. The name of the unique index is the same as the one given to the constraint, the constraint will use an existing index if it exists or you can put USING INDEX. Deferrable UNIQUE and PRIMARY keys all must use non-unique indexes.

CREATE TABLE b
(
b1 INTEGER,
b2 INTEGER,
CONSTRAINT unique1 (b1, b2) USING INDEX (CREATE UNIQUE INDEX b_index on
b(b1, b2),
CONSTRAINT unique2 (b1, b2) USING INDEX b_index
);

You cannot drop an index created via a constraint with the drop index command

Disabling Constraints

A constraint can be turned on or off
ALTER TABLE employee DISABLE PRIMARY KEY;
ALTER TABLE employee DISABLE CONSTRAINT uk_employee_01;

The cascade option means that any foreign keys will also be disabled.
ALTER TABLE dept
DISABLE PRIMARY KEY CASCADE;

Enabling Constraints

Enabling a unique or foreign key builds the index for it.
Enabling will fail if data violates a constraint.

If the constraint is in the enable novalidated state, violations resulting from data entered while the constraint was disabled remain. The rows that violate the constraint must be either updated or deleted in order for the constraint to be put in the validated state. While in this state all subsequent inserts are checked for validity.
You can identify exceptions to a specific integrity constraint while attempting to enable the constraint. All rows violating constraints are noted in an EXCEPTIONS table, which you can examine.

Using integrity constraint states in the following order can ensure the best benefits:

Disable state.
Perform the operation (load, export, import).
Enable novalidate state.
Enable state.
Some benefits of using constraints in this order are:

No locks are held.
All constraints can go to enable state concurrently.
Constraint enabling is done in parallel.
Concurrent activity on table is permitted.

When an integrity constraint is defined in a CREATE TABLE or ALTER TABLE statement, it can be enabled, disabled, or validated or not validated as determined by your specification of the ENABLE/DISABLE clause. If the ENABLE/DISABLE clause is not specified in a constraint's definition, Oracle automatically enables and validates the constraint.

you can keep the index when you disable
ALTER TABLE dept
DISABLE PRIMARY KEY KEEP INDEX,
DISABLE UNIQUE (dname, loc) KEEP INDEX;

You cannot disable NOT NULL constraints, only drop them.The ALTER TABLE ... RENAME CONSTRAINT statement enables you to rename any currently existing constraint for a table. The new constraint name must not conflict with any existing constraint names for a user.

The following statement renames the dname_ukey constraint for table dept:

ALTER TABLE dept
RENAME CONSTRAINT dname_ukey TO dname_unikey;

When you rename a constraint, all dependencies on the base table remain valid.

The RENAME CONSTRAINT clause provides a means of renaming system generated constraint names.

The ALTER TABLE ... RENAME CONSTRAINT statement enables you to rename any currently existing constraint for a table. The new constraint name must not conflict with any existing constraint names for a user.
The following statement renames the dname_ukey constraint for table dept:
ALTER TABLE dept
RENAME CONSTRAINT dname_ukey TO dname_unikey;

When you rename a constraint, all dependencies on the base table remain valid.
The RENAME CONSTRAINT clause provides a means of renaming system generated constraint names.

Deferring Constraint Checks
When Oracle checks a constraint, it signals an error if the constraint is not satisfied. You can defer checking the validity of constraints until the end of a transaction.
When you issue the SET CONSTRAINTS statement, the SET CONSTRAINTS mode lasts for the duration of the transaction, or until another SET CONSTRAINTS statement resets the mode.

Notes:
You cannot issue a SET CONSTRAINT statement inside a trigger.
Deferrable unique and primary keys must use nonunique indexes.
Set All Constraints Deferred
Within the application being used to manipulate the data, you must set all constraints deferred before you actually begin processing any data. Use the following DML statement to set all deferrable constraints deferred:
SET CONSTRAINTS ALL DEFERRED;

Note:
The SET CONSTRAINTS statement applies only to the current transaction. The defaults specified when you create a constraint remain as long as the constraint exists. The ALTER SESSION SET CONSTRAINTS statement applies for the current session only.

Views for constraints:

ALL_CONSTRAINTS
ALL_CONS_COLUMNS
USER_CONSTRAINTS
USER_CONS_COLUMNS
DBA_CONSTRAINTS
DBA_CONS_COLUMNS

	Constraint name	type
	Primary Key	P
	Unique Key	U
	Foreign key	R
	Check, not null	C

NOT NULL constraints are clearly identified in the SEARCH_CONDITION column.
The conditions for user-defined CHECK constraints are explicitly listed in the SEARCH_CONDITION column of dba_constraints

Note:
An additional constraint type is indicated by the character "V" in the CONSTRAINT_TYPE column. This constraint type corresponds to constraints created by the WITH CHECK OPTION for views.

Manipulating Data

Adding rows to a table

INSERT INTO tablename (column_list) values (values_list);

INSERT INTO emp VALUES ('49392, 'SPANKY',1000,null);

INSERT INTO scott.emp (select * from master.emp);

INSERT INTO scott.emp values (2, default);
Default means that the column will be set to its default, if there is one set.

Data must match on datatype and must not violate an integrity constraint.

Changing rows in a table

UPDATE employee SET salary = 99000 WHERE lastname = 'SPANKY';

Deleting rows in a table

DELETE FROM employee WHERE lastname = 'TURNER';
If you don't use the where clause you delete all rows in the table.
Delete will fail if the table is a parent table and the child table would be left with orphans.

Merging data

Table is either updated or inserted according to whether a row already exists.
MERGE INTO movies table1 t1 USING table2 t2
(t2.movie_name = t1.movie_name
and t1.movie_name = 'GONE WITH THE WIND')
WHEN MATCHED THEN UPDATE SET t1.movie_name, t2.showtime)
VALUES ('GONE WITH THE WIND','7:30 PM')

Transaction Control

SET TRANSACTION
This is not always required as a transactio begins, when you log on, after a rollback or a commit, when you exit or the system crashes, when a data control language command is issued e.g. alter database.
A transaction will provide read and write access unless you override this SET TRANSACTION READ ONLY.
The SET TRANSACTION ISOLATION LEVEL SERIALIZABLE command specifies that if a transaction contains DML and if any other transaction is attempting to modify it the second transaction fails!!!!
DDL cause an implicit commit
COMMIT
ROLLBACK
ROLLBACK TO SAVEPOINT xxx
SAVEPOINT xxx
A crucial part of oracle transaction control is read consistency
Locks:
Table and row locks
Table locks can be row share (RS), row exclusive (rx), share (s), share row exclusive (srx) and exclusive (x)

Views

Views are stored queries that dynamically assemble data into a virtual table.
A view will not be created if the base table does not exist (unless you use force, it will be invalid).
Views can add extra security by restricting the data shown to users.

What is valid to include:

order by clauses are allowed, as long as thay are outside the parentheses.CREATE VIEW my_view AS (SELECT * FROM emp) ORDER BY empno.
Group by, connect by and group functions such as count(), as long as each function has an alias.
You cannot include FOR UPDATE

A simple view is one that uses data from only one table.

CREATE VIEW emp_view AS
(SELECT * FROM emp
WHERE job = 'ANALYST');

You can insert, delete or update information on an underlying table via simple views subject to the following restrictions:

All constraint restrictions on the underlying table apply (eg. can't violate primary key).
If underlying table has NOT NULL on columns that aren't in your view you will have problems.
You can delete rows even if you don't have access to all columns.
You cannot update data in a column of an underlying table if the column was defined using a single row function or function based key words (user or sysdate).
You cannot update any data on the table if a group by clause, group function or distinct clause is used in the view.
You cannot modify data if the pseudocolumn ROWNUM is used or if the columns are defined by expressions.

Views that enforce constraints

This adds extra constraints to the view (above whats on the underlying table)

CREATE OR REPLACE VIEW emp_view AS
(SELECT empno, ename, job, deptno
FROM emp
WHERE deptno = 10)
WITH CHECK OPTION CONSTRAINT emp_view_constraint;

This means that insert or update statements issued against the view cannot create rows that the view cannot subsequently select.

This will be listed in USER_CONSTRAINTS, constraint type V (viewability).

Creating simple views that cannot change underlying data

CREATE OR REPLACE VIEW emp_view AS (SELECT * FROM emp) WITH READ ONLY

Creating Complex Views (or join views)

Views based on data from more than 1 table.

The same sort of select statements are allowed as in the simple views (you can use outer joins).

The dictionary view USER_UPDATABLE_COLUMNS can tell you whether the columns in a complex view can be modified. (column_name, updatable (yes or no))

Generally you cannot change data in any of the base tables if you haven't properly defined foreign key and primary key relationships.

You can execute data change on a view if:
the statement affects only one of the tables.
For update statements, all columns changed must be extracted from a key preserved table.
If the view is created using the WITH CHECK OPTION clause, join columns and columns taken from tables that are referenced more than once cannot be updated.
For delete statements the join may have only one key preserved table. This may be present more than once unless the WITH CHECK OPTION clause is present.
For insert statements, all columns must come from a key-preserved table and the view must not have been created with the WITH CHECK OPTION clause.
The complex view does not contain group functions, GROUP BY expressions, set operations, DISTINCT, START WITH or CONNECT, ROWNUM.
(SET operations include UNION, INTERSECT & MINUS)

A key preserved table is one where the unique row in a table will only appear once in a view

******* To find out if a view is VALID look in USER_OBJECTS (not USER_VIEWS) **********

The only thing you use ALTER VIEW for is RECOMPILING or VALIDATING
Use CREATE OR REPLACE VIEW to modify it.

If a base table is modified a view may be marked as INVALID.

You can create a view with no syntax errors but that access a table or column that does not exist by using
CREATE FORCE VIEW
It has a satus of invalid.

The command for removing a view is DROP VIEW.

To create a view you must have CREATE VIEW privilege, or CREATE ANY VIEW
The owner of the view must have been explicitly granted privileges (not through a role) to access all objects referenced in the view definition.
If the owner of the view intends to grant access to the view to other users, the owner must have received the object privileges with the GRANT OPTION or the system privileges with the ADMIN OPTION.

Sequences

Automatically generates unique numbers.
START WITH denotes what the first number generated will be (with NEXTVAL), if not specified will start with 1
INCREMENT BY, defaults to 1, can be positive or negative.
MINVALUE, if not specified oracle will assume the default NOMINVALUE.
MAXVALUE, if not specified oracle will assume the default NOMAXVALUE.
CYCLE, if not specified oracle will assume the default NOCYCLE.
CACHE n. Enables oracle to cache a specified number to improve performance, default is 20.
ORDER. Assigns values in the order they are received by the sequence, default NOORDER.

SELECT seq_countdown.NEXTVAL FROM dual;

Sequences can be altered with the ALTER SEQUENCE statement, only clauses directly referenced will be changed.

Gaps can occur because of ROLLBACKS, system crash (numbers in cache are lost).

Using Indexes

Indexes have 2 uses, they can be used to ensure uniqueness and they can boost performance.
They can be created on any column unless its LONG.

The B-tree index structure is the traditional index in Oracle.
Midpoint of entire list is placed at the top (root node), the mid points of the remaining two lists are placed below this (& so on). By using divide & conquer the values of a column are only a few hops away.
(max 32 columns)

The other type of index available is a bit mapped index.
Conceptualize it as a table with a row for each unique value of an index. The columns hold the unique rowid of the column being indexed, the start rowid for all rows in the table, the end rowid of all rows in the table and the last column contains a bit for every row in the table, each bit will be set off (0) or on (1) depending if the column being indexed matches this unique value.

Growth of a table does not affect the size of the index too much (1 bit per unique value per row added to the table).
They work better with very few distinct values.
A typical use would be a table that is indexed on a GENDER column. This information does not change and has only 2 distinct values, so a b-tree is not useful but a bit mapped is ideal.
(max 30 columns)

Reverse key indexes reverse the filed being indexed and are often used in parallel server environments to improve parallel query performance

Function based indexes are designed to make it possible to define an index that works when your where clause contains operations on columns.
Ex. CREATE INDEX xxx ON emp (sal*1.08);
so that
select * from emp where (sal*1.08) > 63000 - will use the above index, it would not use a normal index it would do a full table scan.

To create a function based index in your own schema you must have CREATE INDEX and QUERY REWRITE privilege, to create it in another schema you need CREATE ANY INDEX and GLOBAL QUERY REWRITE.
The function must be repeatable.
Also two alter session statements must be issued to enable function based indexes.
ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;
ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED;

To create an index in your schema you must have CREATE TABLE privilege, to create an index in another schema you must have CREATE ANY INDEX privilege.

CREATE UNIQUE INDEX xxxx ON table (column1, column2);

CREATE INDEX xxxx ON table (column) REVERSE;

CREATE BITMAP INDEX xxxx ON table (column);

Dictionary views are ALL_INDEX - storage info, ALL_OBJECTS - index stats, ALL_IND_COLUMNS

DROP INDEX xxxxx;

Guidelines
A unique index works well for b-tree, or a non unique if only about 10% of rows have same value.
Bitmaps are more appropriate for switch flags, valid/invalid.
Reverse key indexes work well with oracle parallel server, maximise paralelism.
uniqueness is referred to as cardinality

Create an index when:
You have a large table,
The table is mainly used for queries
When you generally query for 1 or a few distinct values.
When a large number of NULLs are in the column.

Synonyms

Synonyms are alternative names that can be created as objects.
private synonyms are your 'pet names', public synonyms are available to everyone.
CREATE PUBLIC SYNONYM emp FOR scott.emp;

User Access Control

Two basic components of the Oracle security model: password authentification and privilege administration.

Password Authentification

Users can be authenticated by Oracle or by the host system.
You must be connected directly to the host system for host authentication to work.

CREATE USER OPS$harvey IDENTIFIED EXTERNALLY

User names can be up to 30 chars in length and can contain alphanumeric characters plus $, #, and _

To look at other users use ALL_USERS, to look at info about yourself USER_USERS (username, id, account_status, lock_date,expiry_date, default_tablespace, temprary_tablespace, created etc.)

Privilege Administration

Privilegs are the rights to execute particular SQL statements
Privileges are given to users with the GRANT command.

GRANT CREATE SESSION TO harvey;

And taken away with the REVOKE command.

REVOKE CREATE SESSION FROM harvey;

You can grant multiple privileges at the same time
GRANT SELECT, UPDATE ON emp TO elmo;

System Privileges :

Database Access:

CREATE SESSION
ALTER SESSION
RESTRICTED SESSION

Users:

CREATE USER
ALTER USER
DROP USER

Tables

CREATE TABLE
CREATE ANY TABLE
ALTER ANY TABLE
BACKUP ANY TABLE
DROP ANY TABLE (also allows you to TRUNCATE)
LOCK ANY TABLE
COMMENT ANY TABLE
SELECT ANY TABLE
INSERT ANY TABLE
CREATE TABLE and CREATE ANY TABLE (create a table and ANALYZE)

Indexes:

CREATE ANY INDEX
ALTER ANY INDEX
DROP ANY INDEX
There is no CREATE INDEX privilege, the CREATE TABLE privilege allows you to alter & drop indexes that you own and are associated with the table.

Synonyms:

CREATE SYNONYM
CREATE ANY SYNONYM
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM
The CREATE SYNONYM privilege allows you to drop and alter synonyms you own.

Views:

CREATE VIEW
CREATE ANY VIEW (allows you to alter and drop a view)
DROP ANY VIEW

Sequences:

CREATE SEQUENCE (also allows you to drop sequences you own)
CREATE ANY SEQUENCE
ALTER ANY SEQUENCE
DROP ANY SEQUENCE
SELECT ANY SEQUENCE

Roles

CREATE ROLE
DROP ANY ROLE
GRANT ANY ROLE
ALTER ANY ROLE

Transactions

FORCE TRANSACTION
FORCE ANY TRANSACTION

PL/SQL

CREATE PROCEDURE (also allows you to alter and drop procedures you own)
CREATE ANY PROCEDURE
ALTER ANY PROCEDURE
DROP ANY PROCEDURE
EXECUTE ANY PROCEDURE

Triggers

CREATE TRIGGER (allows you to alter and drop triggers that you own)
CREATE ANY TRIGGER
ALTER ANY TRIGGER
DROP ANY TRIGGER

Dictionary Views to Display Privileges

USER_SYS_PRIVS - All system privileges associated with the user
SESSION_PRIVS - All privileges associated with the session

To grant an object privilege the grantor must have been granted the privilege with the grant option, or must own the object. An object privilege is a right to perform a particular action on a specific table, view, sequence or procedure.

Available Object privileges.

Select
insert
update
delete
alter - alter definition of sequence or table only
index - can create index on a table
references - put foreign key constraint on a table (alter table)
execute
all - grant all on emp to harvey (grants all object privileges on emp to harvey)

Granting / Revoking object privileges

To grant an object privilege you must have been granted the privilege with grant option or you must own the object. The user can give or take away an object privilege with GRANT OPTION. You cannot use this when granting to a role.
You can grant INSERT, UPDATE or REFERENCES on individual columns.

If you revoke the object privilege REFERENCES with CASCADE CONSTRAINTS and foreign keys created with the privilege are dropped.
If an object privilege is revoked and the user had the GRANT OPTION all grantees receiving the privilege from the user lose the privilege as well.

Granting / Revoking system privileges

To grant a system privilege you must be the DBA or have been granted the privilege with ADMIN OPTION. The user can then grant or revoke the privilege from any user or role, however he cannot revoke it from himself. He can grant it with the ADMIN OPTION.

If you revoke a system privilege from a user and they had admin privilege any user that was granted that privilege via them will still have it. You need to revoke system privileges explicitly.

Facts about Granting and Revoking.

If a privilege has been granted on 2 individual columns, the privilege cannot be revoked on only 1 column, it must be revoked entirely and regranted.

If the user has been given the references privilege and has used it to create a foreign key, revoking the grant does not remove the foreihn key unless you state CASCADE CONSTRAINTS.
Ex. REVOKE REFERENCES ON emp FROM turner CASCADE CONSTRAINTS.

The insert, update and references privilege can be granted on columns within the database object. However, if the user has the insert privilege on several columns (but not all) it must be ensured that no remaining columns ate not NULL columns.

If a user has the ability to execute a stored procedure, and the procedure accesses some tables, the object privileges required to access those tables must be granted to the owner of the procedure directly (not through a role) and not to the executor of the procedure.

Depending on what is granted or revoked, a grant or revoke takes place at different times. All grants/revokes of privileges (system and schema object) to users, roles or PUBLIC are immediately observed. All grants/revokes of roles to users, other roles, or PUBLIC are observed only when a current user session issues a SET ROLE statement to reenable the role after the grant/revoke, or when a new user session is created after the grant/revoke.

If a system privilege or object privilege is granted to PUBLIC then every user in the database has that privilege.


Using Roles

Predefined roles:
CONNECT - extensive development ability including create table.
RESOURCE - enables development ability.
DBA - all system privileges.

Dictionary views for roles:
USER_ROLE_PRIVS - roles granted to you
ROLE_ROLE_PRIVS - roles granted to other roles
ROLE_TAB_PRIVS - object privileges granted to roles
ROLE_SYS_PRIVS - system privileges granted to roles
SESSION_ROLES - roles available to current session

In order to use roles there are four steps:
1. You must logically group certain kinds of users together according to the privileges required.
2. You must define a role for each user type.
You must grant appropriate privileges to each user of the roles.
You can then grant the roles to specific users of each type.

If a role is already granted to a user and that role is later granted another privilege that additional privilege is available to the user immediately. The same statement can be made for privileges revoked from roles.

alter user default role - defines a users default role - can have options 'all' and 'all except rolename'.
By default a role granted to a user is a default role.
If a role has a password do not set it as a default, so that the user has to enter another password on setting it.
SET ROLE data_changer IDENTIFIED BY password.