Oracle SQL Fundamentals Part2

11.7.06

Oracle SQL Fundamentals Part2
DML and Schema Objects
Manipulating Data
Insert
Update
Delete
Merge
Transaction Control
Creating and Maintaining Tables
Database Objects: Naming Guidelines
Constraints: Syntax and Types
Tables: Managing
Other Schema Objects
Views:
Views: Managing
Sequences: Creating
Sequences: Modifying
Synonyms: Creating and Dropping
Indexes: Overview
Indexes: Managing
Data Dictionary
Data Dictionary: Features
Data Dictionary: Views
Objects and Data Management
Controlling User Access
System Privileges
Object Privileges
Maintaining Schema Objects
Columns: Maintaining
Constraints: Managing
FLASHBACK TABLE Statement
External Tables: Creating
Manipulating Large Data Sets
Subqueries
Tracking Changes
Multitable Inserts
Unconditional INSERTALL
Conditional INSERT ALL
Conditional INSERT FIRST
Pivoting INSERT
Grouping Data
ROLLUP Operator
CUBE Operator
GROUPING function
GROUPING SETS
Composite Columns
Concatenated Groups

Manipulating Data Across Timezones
Datetime Functions
Datetime Conversions

Using Advanced Subqueries
Multiple-Column Subqueries
Scalar Subqueries
Correlated Subqueries
SQL Operators: EXISTS and NOT EXISTS
Correlated Operations
COrrelated Subqueries: WITH Clause
Data Retrieval
Hierarchical Data Retrieval
Hierarchical Queries
Hierarchical Reports: Formatting
Regular Expression Support
Regular Expression: Functions



Insert Statement, pg 1-15
insert into table (col1, col2) values (val1, val2)
you can only add a single row with VALUES clause
all char & date, should be in single quotes
inserting nulls
2 methods - explicit & implicit
explicit, for date/char, you can use NULL or ""

to copy rows from other tables
use subquery in insert statement
INSERT INTO table (col1, col2) select val1, val2 from table2


Update Statement, pg 1-12
Update table set col = val, col2 = val2 [ where condition];

Update table set column = (select col2 from tab2 where condition);


Delete Statement, pg 1-13
delete from tab1 where cond1
# confirm with a following select statement

delete from ct_employee
wehre deptno = (select deptno from ct_department
where dname like '%LES');

Truncate table tab1;
table structure maintained even after all rows are deleted
can't be used if there is primary/foreign key constraints

Truncate - DDL, data definition lang
DELETE - DML, data manip lang
it is faster to truncate, because does not gen ROLLBACK info
and does not start the delete triggers
TRUNCATE - rollback is not possible

Merge, pg 1-11
can be used for inserting/deleting/updating based on a condition
provides ease of use
data warehousing operations where multiple sources involved
helps avoid duplicaton of data


syntax
merge into table
using (table | view | subquery) # source of data
on (join conditon)
when matched then
update set
col1 = col_val1,
col2 = col2_val
when not matched then
insert (column_list)
values(column_vals);

if match is found (ON clause), rows are updated to match the source table
otherwise, a new row is inserted

merge into ct_employee_history c
using ct_employee e
on (c.empno = e.empno)
when matched then
update set
c.ename = e.ename, c.job = e.job, c.sal = e.sal
when not matched then
insert values
(e.empno, e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno)


Transaction Control, pg. 1-26
transaction is a combo of statements
consistency
3 types of transactions:
DML, multiple statements
DDL, only one
DCL only one

transaction begins with DML
ends with commit, rollback, DDL or DCL, or when user exits isqlplus, or crash

4 transaction control statements
- rollback
- commit
- savepoint
- rollback to savepoint

A commit can also happen automatically if exit from isqlplus
with autocommit setting = on
savepoints can divide transaction into sections, with different names

delete from ct_emp
savepoint a;
insert into ...
savepoint b;
update ct_emp
rollback;

read consistencies - copies of previously commited data



Tables, pg 1-9
data from selected columns of one or more tables can be accessed via view
views are named queries
simplify complex queries

sequences as objects
used to generate unique sequential numeric values

indexes

synomyns - aliases to db objects, like tables & views

database naming conventions
- names are not case sensitive
- less than 30chars long
- only A-Z, 0-9, _ $ #




Tables: Creating, pg 1-26
objects used for storing data in columsn and rows
must have "create table" privilege

create table schema.tablename
( name type(30) [ not null ],

by default, a table is owned by the person who create its
to create a table owned by another user, use the schema options

data types:
varchar2, char, number, date
long,clob,raw,long raw
blob, bfile, rowid

varchar2 stores var char b/w 1 & 4000 chars.
char is fixed length 1-2k
number stores variable length, precision & scale, 1-38 & -84-gt127
date: jan 1 4712 bc and dec 31 9999 AD
long: limit 2GB (only one per table)
clob: limit 4GB
raw: binary data 1-2k
long raw: binary: 2GB
blob: binary 4GB
bfile: binary, in external file, to 4GB, such as images, stores links in table
rowid:
variable length preferred over fixed length

default values:
create table empdata
( doj DATE DEFAULT SYSDATE

create table ct_empdata
(ename, doj, salar)
as
select ename, hiredate,sal
from ct_employee

create table tab1
as
select ename, hiredate, sal
from employee
# integrity contraints not inherited


you can also add comments using "COMMENT ON TABLE"

COMMENT ON TABLE ct_empdata
IS 'employee information from the employee table'




Contraits: Syntax and Types, pg 1-24\
you can define at the column level, or at table level
created with table, or aftward
constraints stored in data dictionary

create table schema.tablename
(col1 varchar(20) constraint ltnamegt ltconstraitgt

# table level
create schema.table
( col varchar(20
constraint ltnamegt ltconstraitgt

types of constraints
- not null # only at column level
- unique
- not null unique, because null is unique
- primary key - column that distinguishes each row in table
- foreign key - referential integrity contraint, parent table
- check

you can also place UNIQUE at table level,
called consolidated unique key

# implement foreign key at table level
create table ct_empdata
(empno number(5),
empname varchar2(30),
doj DATE,
FOREIGN KEY(empno) REFERENCES ct_employee(empno));

# to implement at column level
create table ct_empdata
(empno NUMBER(5) REFERENCES ct_employee(empno),
empname VARCHAR(30),

by default, rows in parent table that have dependent rows cannot be deleted
to enable deletion all dependent rows in child table:
foreign key (empno) REFERENCES ct_employee(empno) ON DELETE CASCADE

or the child values can be turned to NULL
FOREIGN KEY(empno) REFERENCES ct_employee(empno) ON DELETE SET NULL

check constraint,
check conditions
imploment at both column & table

create table ct_emptable
( salary NUMBER(8),
check (salarygt0)

checks cannot refer to CURRVAL, NEXTVAL, LEVEL, ROWNUM
and cannot call the SYSDATE, UID, USER, USERENV

previous versions of Oracle database used share locks on entire child table to prevent DML when parent modified
Oracle 10g can instantly issue and remove share lock at table lock
for multiple modifications, issued at row level


Tables: Managing, pg 1-10
to modify a table
ALTER TABLE
- add, modfiy, delete a column
- add, enable, and disable constraints

ALTER TABLE ct_sales
MODIFY unit_cost number(8)

DROP TABLE statement
- deletes data and structures
- drops indexes constraints and triggers
- views and synomymsn remain, but invalid

DROP TABLE lt table_namegt
[ CASCADE CONSTRAINTS ]
disables foreign constraints

DDL statement
you can only issue, if you are owner, or have "DROP TABLE" priviledge





12.10.06 11pm
Views, pg 1-14
hide part of a table, or a large complex join query
given a name and executed later
view or modify data
view stored as select statement in data dictionary

advantages
- restrict access to data
- simplify complex queries, query several tables without writing a join
- provide data independence
- present different views of same data

two types
simple view, fetch from single table, always support INSERT & DELETE
complex view, multiple table, contain functions or groups of data

a materialized view is database object that contains the results of a query
results in creation of internal table

create view ct_emp_view
( empno primary key disable novalidate, ename, sal) AS select ... from ct_employee where ...
DISABLE NOVALIDATE - constraints not validated

Views: Managing, pg 1-12
create or replace view v1 as subquery

# create view with column alias
create view annual_sal
as select empno, ename,sal*12 annualsal
from ct_employee
where deptno = 20

# query view, like querying a table
select * from annual_sal

drop view emp_view;




Sequences: Creating, pg 1-19
user-created database objets
useful for generating sequences
shareable object
can recycle number after specified maximum
eliminates need to use applciation code to write a sequence routine

create sequence sequence [start with n] [incr by n] [maxvalue][minvalue][cycle][cache]
cycle - whether sequence keeps generating values after reaching max
sequence generates an error
cache - whether value generated in advance

create sequence dept_seq
increment by 10
start with 10
maxvalue 200
nocache
nocycle;

select dep_seq.NEXTVAL from dual;
select dep_seq.CURRVAL from dual;

where NEXTVAL & CURRVAL can be used
- select not part of subquery
- select list of a subquery in an insert
- values of an insert
- set clause of an update

where not permissible
- select list of a view
- select containing a group by, having, order by
- select distinct
- default expression in a create table

insert into ct_department (deptno, dname, loc)
values (dept_seq.nextval, 'GRAPHICS', 'NEW YORK');

sequence gaps, from rollbacks & crashes


Sequences: Modifying, pg 1-15
for example, if Sequence is max'd and NOCYCLE

alter sequence ltseq_namegt
[incr by n][maxval n][minvalue n][cycle][cache n]

alter sequence employee_seq
maxvalue 1800
cycle
cache 10

considations
- you can modify, only if you are owner, or have ALTER privilege



Synonyms: Creating and Dropping, pg 1-13
aliases for database objects
: table, view, sequence, stored funtion, stored proc, package, user defined object type
you can access object in some other schema, owned by another user

create synonym - create private syn
create public synonym

create synonym ltsyn_namegt for ltschemagt.ltobjnamegt
create synonym emp_sy for ct_employee
create public synonym ...

guidelines
- objects cannot be in a package

drop synonym emp_sy





Index: Overview, pg 1-16
created manually or automatically (with primary/unique)
provides direct access to rows
a pointer to row
reduces disk IO
if present on column referenced in WHERE clause
otherwise, full table scan
either UNIQUE or non-unique'

types of Indexes based on structure
- normal indexes: b-tree
- bitmap: b-tree
- partitioned
- domain
- function-based

B-tree by default, called Normal
indexes stores pairs of index key values and rowids of the table rows with those key values in the column

bit-map indexes store row IDs related to index key values as a bitmap

Partitioned indexes contain a separate entry for each value appearing in the indexed columns of a table
These indexes are suitable for creating tables on very large indexes

A domain index is an instance of an application-specific index

Function based index helps you generate queries that assess the value returned by an expression
enhancement in Oracle 10g, is support for null values

In Oracle 10g you can define index that supports primary & unique keys independent of constraint
to do so, use the CREATE INDEX statement in the USING INDEX clause of CREATE TABLE

can drop contraint without dropping index
ALTER TABLE table1 drop primary key keep index



Index: Managing, pg 1-17
create index ind1 on table_name(col);

# create function based index, must set QUERY_REWRITE_ENABLED=TRUE to use
create index ct_emp_index
ON ct_emp(LOWER(ename))

select * from ct_employee
where LOWER(ename) LIKE '%d'
ORDER BY LOWER(ename)

Indexes make DML to run slower
you should maintain a bnalance b/w query requirment and DML ops

don't create index an index if:
- columns not used often in where clause or
- table not very big
- or table updated often

index cannot be updated manually
you can drop and re-add to rebuild
drop index index1;

# weird syntax
create table ct_salesperson_details
( empno NUMBER(5) PRIMARY KEY USING INDEX (CREATE INDEX ct_sale_emp_idx ON
ct_salesperson_details(empno)),

# obtain information in data dictionary
select index_name, table_name
from user_indexes
where table_name = 'CT_SALESPERSON_DETAILS'

# Index organized Tables (IOT)
when you need to index a table that contains only a few columns
create table tab1(col1) ORGANIZING INDEX;

#IOT
create table ct_student_table
(sno NUMBER(5) CONSTRAINT pk_sno PRIMARY KEY,
sname VARCHAR2(30, total_marks NUMBER(9)))
ORGANIZATION INDEX
ename VARCHAR2(20), sal NUMBER(6), deptno NUMBER(3))





Data Dictionary : Features, pg 1-14
in form of tables & views
read-only
- user names
- priveleges & roles
- default column values
- integrity constraints
- definition of schema objects
- general database information

data about data, called metadata
only DBAs can access metadata
regular users only access summary views of base tables

data dictionary offers two types of views
- static views
- dynamic views

three levels of views:
USER_OBJECTS = stuff they own
ALL_OBJECTS = stuff they can access
DBA_OBJECTS = all objects by all users

views ALL_OBJECTS & DBA_OBJECTS have extra column: OWNER

dynamic viewss: performance of database
V$ and GV$
only a user with admin perms can access directly
otherwise DBA creates views on these views for users to access

Describe Dictionary - lists all static & dynamic views under TABLE_COLUMN

select table_name
from dictionary
where lower(comments) like '%columns'



Data Dictionary: Views, pg 1-29
frequently used:
- user_objects: LAST_DDL_TIME ~ datetime of last modification
- user_tables
- user_tab_columns
- user_constraints
- user_cons_columns
- user_views
- user_sequences
- user_synonyms
- user comments views

ALL_TABLES,TABS, and TAB synonyms

DESCRIBE user_tables

USERS_CONSTRAINTS ~ include foreign key constraints, conditional constraints
Describe USER_CONSTRAINT
CONSTRAINT_TYPE:
C ~ check constraint
P ~ primary key
U ~ unique
R ~ referential integrity, ie. foreign key
Select * from user_constraits
DELETE_RULE=[ CASCADE | NO ACTION]
CASCADE ~ all child records will be deleted if the parent record is deleted
USER_CONS_COLUMNS, specific columns on which constraints placed.

set LONG 1000
select * from user_views # by default LONG only returns 80

ALL_COL_COMMENTS, USER_COL_COMMENTS, ALL_TAB_COMMENTS, USER_TAB_COMMENTS




12.12.06 13:51pm
System Privileges, pg 1-27
2 types:
- system, user & pass, disk space, system ops
- data, access to database objects

schema is collection of database objects, such as tables & views
each database user owns a unique schema that has smae nmae as the user

DBA can assign two kinds of privileges to users:
- system, access database, and perform activities
- object, enable user to access & manipulate

Key system privileges
- create user
- drop user
- drop any table (in any schema)
- backup any table (in any schema)
- select any table(in any schema)
- create any table(in any schema)

Create USER ltusergt identified by ltpassgt

System Privileges for Users
- create session (allow connection)
- create table (in own schema)
- create sequence
- create view
- create procedure

Grant ltprivgt to ltusergt
GRANT CREATE SESSION, CREATE TABLE TO susan
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO PUBLIC
ALTER USER ltusergt IDENTIFIED BY ltpassgt # change pass
ALTER USER susan IDENTIFIED BY susan01
ALTER USER, also allows changing default tablespace, and locking an account
but need the ALTER USER privilege

Group some privileges together, and give common name, called ROLE
user can be granted multiple ROLES
CREATE ROLE ltrolegt
CREATE ROLE staff
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO staff
GRANT STAFF TO susan, jones


Object Privileges, pg 1-14
objects: table, view, sequence, procedure

Object Priv Table View Seq Proc
ALTER v
DELETE V
EXECUTE v
INDEX v
INSERT v v
REFERENCES v
SELECT v v v
UPATE v

Tasks Involved in Granting Object Privileges
- verifying the right to grant
- using SQL to grant rights
- passing on granted privileges
- confirming granted privileges


Confirming Privileges Granted
Data Dict View Descript
ROLE_SYS_PRIVS System privileges granted to roles
ROLE_TAB_PRIVS table privileges granted to roles
USER_ROLE_PRIVS Roles accessible by the user
USER_TAB_PRIVS_MADE Object privileges granted on the user's objects
USER_TAB_PRIVS_RECD
USER_COL_PRIVS_MADE
USER_COL_PRIVS_RECD
USER_SYS_PRIVS

to grant object privileges to others
GRANT ALL ON ltobjectnamegt TO PUBLIC # all privs to all users
WITH GRANT OPTION # you can forward an object privilege

GRANT SELECT, INSERT
ON ct_employee
TO ken, steve
WITH GRANT OPTION

REVOKE INSERT
on ct_employee
from ken # has a cascading effect, whoever he had passed on to



Columns: Maintaining, pg 1-19
add, drop, modify, mark as unused

ALTER TABLE ct_age ADD job VARCHAR(20)
ALTER TABLE ct_age MODIFY job VARCHAR(10) # modify data type, default value, and size
you can decrease size of columns if there are no rows
change CHAR to VARCHAR2
ALTER TABLE ct_age DROP COLUMN job
you have to specify CASCADE CONSTRAINTS for dropping a column included in a constraint
if column you want to drop is big, designate unused. Column can be dropped later
ALTER TABLE ct_age SET UNUSED COLUMN empno # disables column
even with DESCRIBE, unused columns not displayed
you can query USER_UNUSED_COL_TABS to get list of tables having unused columsn
ALTER TABLE ct_age DROP UNUSED COLUMNS
when you drop a column, it is stored in recycle bin, which is new to 10g
you can query RECYCLEBIN or USER_RECYCLEBIN, and restore using FLASHBACK BEFORE DROP
Recycle Bin can be cleared using PURGE RECYCLEBIN


Constraints: Managing, pg 1-31
ALTER TABLE statement enables you to:
- add, drop, enable/disable, add a NOT NULL

ALTER TABLE ct_employee
ADD CONSTRAINT pk_empno
PRIMARY KEY(empno)

ALTER TABLE ct_employee
ADD CONSTRAINT fk_deptno
FOREIGN KEY(deptno)
REFERENCES ct_department

ALTER TABLE ct_employee
ADD FOREIGN KEY(deptno) REFERENCES ct_department
ON DELETE CASCADE

Constraints is Deferred
- DEFERRABLE
- NOT DEFERRABLE
- INITIALLY DEFERRED
- IMMEDIATE

ALTER TABLE ct_customers
ADD CONSTRAINT pk_cust
PRIMARY KEY(cust_id)
DEFERRABLE # until after transaction commits

SET CONSTRAINT pk_cust IMMEDIATE # checked at end of each statement

ALTER TABLE ct_department
ADD CONSTRAINT pk_deptno
PRIMARY KEY(deptno)
DEFERRABLE INITIALLY DEFERRED

DROP CONSTRAINT
find name from USER_CONSTRAINTS or USER_CONS_COLUMNS

ALTER TABLE ct_department DROP PRIMARY KEY CASCADE # CASCADE, any dependent constraints
ALTER TABLE ct_department ENABLE/DISABLE CONSTRAINT pk_deptno

to enable a constraint
- you require privileges to create an index on the table to enable a constraint
- foreign keys dependent on the primary key are not enabled on enabling a PRIMARY KEY

ALTER TABLE ct_employee
DROP COLUMN empno
CASCADE CONSTRAINTS # drops empno, with PRIMARY KEY, also drops all FOREIGN KEY CONSTRAINTS



Flashback TABLE Statement, pg 1-11
new DDL command, FLASHBACK TABLE statement
you can restore a table dropped by accident
from last saved state of a table
also restores depended objects such as views and indexes
FLASHBACK TABLE ct_employee TO BEFORE DROP
DROP statement moves stuff to recyle bin

# with SYSDBA privilege
SELECT original_name, operation, droptime FROM recyclebin
PURGE RECYCLEBIN
dropping USER purges users stuff



External Table: Creating, pg 1-28
metadatabase stored in db, real data outside
read-only
query using SQL, PL/SQL or Java
you cannot create an index, and cannot use DML
server provides two main access drivers for external tables:
ORACLE_LOADER, default, format can be interpreted by SQL*Loader
ORACLE_DATAPUMP: platform independed format, for import & export.
ORACLE_DATAPMP allows you to unload from db, and reload to another db
one time operation, after create & population, you cannot update, insert, or delete rows
query with SELECT command
To create,
- create directory object
- requires CREATE ANY DIRECTORY system privilege
create or replace directory ltdir_namegt AS 'path_name'
create or replace DIRECTORY emp_data as '/emp_dir'
after creating a directory object, you can create a table
CREATE TABLE lttabgt (col1) ORGANIZATION EXTERNAL TYPE ltaccess_drivergt DEFAULT DIRECTORY ltdirgt LOCATION('file')
where
ltaccess_drivergt=ORACLE_LOADER

CREATE TABLE ct_ext( fname VARCHAR2(20) )
ORGANIZATION EXTERNAL (
DEFAULT DIRECTORY CT_DIR
LOCATION('empdat.dmp'))
REJECT LIMIT 100



Subqueries pg 1-17
- copy data from one table to another using single select statement
- insert & retrieve data from an inline view
- retrieve data from inline view
- update multiple columns of a table
- update rows in table based on values from another table
- delete rows in a table based on values from another table

# copies rows from ct_employee to ct_emp
INSERT INTO ct_emp(empno, ename, sal, comm)
SELECT empno, ename, sal, comm
FROM ct_employee
WHERE job LIKE '%P%'

# copy all rows
INSERT INTO ct_emp
Select * from ct_employee


you can also insert data by using subquery as the target.
Subquery replaces table name in INSERT statement
# I tested this, and the where clause appears irrelevent, just inserts data into base table
INSERT INTO (select ename, sal, comm
FROM ct_bonus WHERE comm = 500 ) VALUES ( 'Ed Young', 4500, 1256)

# inline view, subquery used in FROM statement
SELECT e.ename, e.sal, e.deptno, a.salavg
FROM ct_employee e, ( SELECT deptno, AVG(sal) salavg
FROM ct_employee GROUP BY deptno) a
WHERE e.deptno = a.deptno AND e.sal gt a.salavg
Values ('ed young', 4500, 1256)

# update multiple columns
UPDATE ct_emp
set job = (select job from ct_employee where empno = 7782,
sal = (select sal from ct_employee where empno = 7782)
where empno = 7934

UPDATE ct_emp
SET deptno = (SELECT deptno FROM ct_employee WHERE empno = 7782)
WHERE job = (SELECT job from ct_employee WHERE empno = 7788)


DELEET FROM ct_emp
WHERE deptno = (SELECT deptno FROM ct_department WHERE dname = 'SALES' )

# no changes to row not in subquery, or in this case deptno = 30. Would try to copy in as NULL != 30
INSERT INTO (select empno, ename, sal, deptno FROM ct_emp
WHERE deptno = 30 with check option) values (7965, 'Allen Smith', 3400)


SQLgtset autoprint on
sqlgtvar x char(20)
sqlgtvar y number
sqlgtupdate ct_employee set sal = 2000
2 where empno = 7876
3 returning ename, sal into :x , :y ;



Tracking Changes, pg 1-19
data accidently deleted or wrong data commited
or logical corruptions in database
use flashback functionalities to analyze

Flashback technology
recover data at rows, transactions, tables, tablespaes

- Flashback Query : query at a specific point in time, and reconstruct lost or modified data using redo entries
- Flashback Transacation Query: view changes by an erroneous transaction, use FB Table to restore a table
- Flashback Table:
- Flashback Version Query: view changes at row level over time, use the VERSIONS clause

- VERSIONS clause used with a SELECT statement.
- specifies system change number (SCN) or a timestamp range
everytime you execute a commit statemet on a table, a new row version is created
- returns all committed versions that exist or existed in specified time interval
- returns both deleted and subsequently reinserted versions of rows
- does not return any changes made by the current version
- does not affect the way a query functions

provides row access based on ROWID or returns all versions of the rows.

invoke a flashback version query:
4
Select sal from ct_employee
VERSIONS BETWEEN SCN MINVALUE and MAXVALUE
WHERE ename = 'Ronald Billing'
VERSIONS BETWEEN {SCN|TIMESTAMP} MINVALUE and MAXVALUE



Unconditional INSERT ALL, pg 1-13
one of the significant additions in new SQL features is multitable INSERT feature
use single INSERT INTO ... select # to insert data into multiple tables
INSERT ALL is type of multitable INSERT

appropriate for Extraction, Transformation and Loading in a data warehouse

4 types of Multitable INSERT
- unconditional INSERT ALL
- conditional INSERT ALL
- conditional FIRST INSERT
- pivoting INSERT


Restrictions on Multitable INSERT statement
- non-applicability on views and materialized views
- inability to perform multitable INSERT into a remote table
- inability to specifiy a table collection expression
- inability to specifiy more than 999 target columns

INSERT ALL
INTO ct_commission(empno, sal,comm) VALUES(empno, sal, comm)
INTO ct_commission_details(empno, ename) VALUES(empno, enmae)
SELECT empno, ename, sal, comm FROM ct_employee
WHERE empno gt 7654



Conditional INSERT ALL

INSERT ALL
WHEN sal gt 5000 THEN
INTO ct_employee_details(empno, ename,sal) VALUES(...)
WHEN sal lt 5000 THEN
INTO ...
SELECT empno, ename FROM ct_employee
WHERE empno gt 7000


Conditional INSERT FIRST

INSERT FIRST
WHEN average gt 5000 THEN
INTO ct_dept(deptno) VALUES(deptno)
WHEN deptno = 20 THEN
INTO ...
ELSE INTO ct_ ... VALUES
Select deptno, SUM(sal) total_sal, AVG(sal) from ct_employee GROUP BY deptno



Pivoting INSERT, pg 1-7
process of transforming non-relational data into relational data
one row into several smaller rows of more relational design
build a transformation

INSERT ALL
INTO ct_sales_info VALUES(empno, week, mon_sale)
INTO ct_sales_info VALUES(empno, week, tue_sale)
INTO ct_sales_info VALUES(empno, week, wed_sale)
SELECT empno, week, mon_sale, tue_sale ....
FROM ct_sales_data



ROLLUP Operator, pg 1-16
you often need cumulative aggregates along with normal grouping
used in the GROUP BY clause
ROLLUP operator extends the functionality of the GROUP BY clause by returning the cumulative aggregates of the expressions in the GROUP BY statement

the same type of result can be produced by combining many SELECT statements with the UNION ALL operator on different groups. Consider a situation where you need to extract

Consider a situation where you need to extract the subtotals of x columns using the GROUP BY statement. WIthout the ROLLUP operator, you need to write x+1 SELECT statements combined with the UNION ALL operator

each SELECT statement accesses the table seperately and decreases efficiency. ROLLUP accesses table only once


# calc total salaryfor each type of job in each department listed. ROLLUP operator fist extracts the total salary for each job, then for each department, and finally for all departments
SELECT deptno, job, SUM(sal)
FROM ct_employee
WHERE deptno gt 10
GROUP BY ROLLUP (deptno, job)



CUBE Operator, pg 1-10
enhance aggregation
both CUBE & ROLLUP generate super-aggregates
ROLLUP - fetches result set that displays grouped rows and subtotal rows
CUBE - produces all possible combinations of values of grouping
it returns a single row of summary for each possible grouping
used for cross-tabular reports.
result set returns by the ROLLUP operator and the rows fetched from cross tabulation
n-dimensional cube with additional combinations


CUBE is extention to the functionality of GROUP BY clause
operator can be succesfully applied to aggregate functions, including AVG, SUM, MAX, MIN, COUNT

Number of columns in GROUP BY clause determines the number of additional groups in result set
if there are x number of grouping params, there will be 2^x number of groups

you can use charts & groups to effectively depict the superagg rows by using application or programming tools

SELECT deptno, job, SUM(sal) FROM ct_employee
WHERE deptno lt 40 GROUP BY CUBE(deptno, jbo);
# 4 subtotals
1. total salary for each job within a department
2. total salary for departments with depno lt 40
3. total salary for each job irrespective of department
4. total salary for departments with deptno lt 40, irrespective of the jobs

also includes results by ROLLUP
- total salry for departments with deptno lt 40
- total salary for department with depnto lt 40, irrespective of the jobs



GROUPING function, pg 1-11
used in SELECT statement that contains CUBE or ROLLUP with the GROUP BY
This function enables you to interpret the summary values produced by these operators
CUBE & ROLLUP display aggregates that contain NULL values
GROUPING allows you to distinguish NULL values returned by CUBE/ROLLUP vs. those stored in base tables.
it also helps you determine group or groups which the subtotal is based, sort subtotal rows, and filter results
GrOUPING fucntion returns 0 if value of expr, in the row represents a stored NULL or if expression has been used to calculate an aggregate
GROUPING value returns a value of 1 if it encounters a NULL if produced by ROLLOW or CUBE, or if expression has not been used to calculate aggregate

SELECT deptno, job, SUM(sal) AS sal_total,
GROUPING(deptno) dept_groups,
GROUPING(job) job_groups
FROM ct_employee
WHERE deptno gt 10
GROUP BY ROLLUP (deptno, job)

returns:
# total salary for job title cleark
DEPTNO JOB SAL_TOTAL DEPT_GROUPS JOB_GROUPS
20 CLERK 3050 0 0 0
1 # because job column not taken into accout
1 1 # not taken into account either dept_groups or job_groups




GROUPING SETS, pg 1-14
extention of GROUP BY clause
once performs all the GROUPINGS, it uses the UNION ALL operation to merge the output of each grouping
code efficiency, access base table only once.
eliminates the need of specifying several SELECT statements combined with UNION ALL operator


SELECT mgr manager, job, deptno, AVG(sal), Total_sal FROM ct_employee
GROUP BY
GROUPING SETS((deptno, job, mgr),(deptno,mgr),(job,mgr))

returns
# MANAGER JOB DEPTNO TOTAL_SAL
7566 CLERK 20 950
7566 CLERK 950 (average of 4 other rows)


# you can also specify the ROLLUP and CUBE operatoers with the GROUP BY clause
# calculate aggregate of all three groupings by using the CUBE operator


SELECT mgr, job, deptno, AVG(sal) FROM ct_employee
GROUP BY CUBE(deptno, job, mgr)


CUBE(a,b,c) GROUPING SETS ((a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),())
ROLLUP(a,c,b) GROUPING SETS ((a,c,b),(a,b),(a),() )

if you do not using the GROUPING SETS, you need to specify severla queries with UNION ALL to obtain same result


# scan base table 3 times
SELECT deptno, jbo, mgr, AVG(sal) FROM ct_employee GROUP BY deptno, job, mgr
UNION ALL
SELECT deptno, NULL, mgr, AVG(sal) FROM ct_employee GROUP BY deptno, mgr
UNION ALL
SELECT NULL, job, mgr, AVG(sal) FROM ct_employee GROUP BY job, mgr


SELECT mgr manager, job, deptno, AVG(sal) Total_sal FROM ct_employee
GROUP BY
GROUPING SETS ((deptno, job, mgr),(deptno, mgr),(job,mgr))

((deptno, job, mgr) # average salary of employee working as a CLERK in dept 20 under manager 7566 is 950
(deptno, mgr) # average salary of the employee working in dept number 10 under manager 7839 is 2450
(job,mgr)) # average salary of employee working as an ANALYST under manager 7566 is 3000




Composite Columns, pg 1-13
you use composite columns in the GROUP BY clause. These are used in ROLLUP & CUBE, and GROUPING SETS
skip some levels during aggregation process
limit the computation to certain levels of GROUPING

Composite Columns
with ROllup:
GROUP BY ROLLUP(hour,(min,sec))
the output will be equivalent to the ouptut generated by one query with three select statements:
GROUP BY hour, min, sec UNION ALL Group BY hour, UNION ALL, GROUP BY()
(min,sec)) is considered a unit, and ROLLUP not applied across this level
GROUP BY () is SELECT statement with NULL values for the columns.
Also contains an aggregate function, which is used in calculating grand totals.

CCOmposite Columns
With CUBE
GROUP BY CUBE((hour,min),sec)
equivalent to:
GROUP BY hour,min,sec UNION ALL
GROUP BY hour,min UNION ALL
GROUP BY sec UNION ALL
GROUP BY ()

Composite COlumns
with GROUPING SETS Statements
GROUP BY GROUPING SESTS(hour,min,sec)
GROUP BY hour UNION ALL
GROUP BY min UNION ALL
GROUP BY sec
GROUP BY GROUPING SETS(hour,min,(min,sec))
GROUP BY hour UNION ALL
GROUP BY min UNION ALL
GROUP BY min,sec
GROUP BY GROUPING SETS(hour,(min),())
GROUP BY hour UNION ALL
GROUP BY min UNION ALL
GROUP BY ()
GROUP BY GROUPING SETS(hour,ROLLUP(min,sec))
GROUP BY hour UNION ALL
GROUP BY ROLLUP(min,sec))

as DBA, you are asked to retrieve totals at various levels with various combinations of columns
retreieve deptno, job, mgr, SUM(sal) from ct_employee with job and mgr as composite columsn

SELECT deptno, job, mgr, SUM(sal)
FROM ct_employee
GROUP BY ROLLUP(deptno, (job,mgr))

grouping between job & mgr, deptno, gruping by deptno only, and the grand total generated by GROUP BY()
code generates total salary for every job and manager, and the total salary for every department, job, and manager
also computes total salary for every department and the grand total


Concatenated Groupings, pg 1-7
combination of useful resultant groupings

GROUP BY GROUPING SETS(x,y), GROUPING SETS (p,q)
results in cross product of elements of each grouping set as new sets
results in:
(x,p),(x,q),(y,p),(y,q)

saves you from manual enumeration of groupings
commonly used in SQL generated by OLAP

# alot of shit happening here
SELECT deptno, job, mgr, SUM(sal) FROM ct_employee WHERE deptno IN (10,20)
GROUP BY deptno, ROLLUP(job, CUBE(mgr)

# total salary is calculated for the resulting groups.
The first group comprises total salary of all managers of the correspdoning jobs
Second total salary of all the managers of corresponding jobs and departments
Third total salary of all employees and their correspoding jobs




Datetime Functions, pg 1-8
deal with timzones and local date display conventions
GMT aroudn Greenwich England, is Zero, UTC
GMT-12:00 going westbound, around western edge of alaska
GMT-6:00 Texas
GMT+3 Turkey

# set a timezone for each session
ALTER SESSION SET TIME_ZONE="-05:00';
ALTER SESSION SET TIME_ZONE=dbtimezone;
ALTER SESSION SET TIME_ZONE=local;
ALTER SESSION SET TIME_ZONE='America/New_York';

# customize the format of datetime display
- CURRENT_DATE # 26-JUN-2004 18:53:07
- CURRENT_TIMESTAMP 26-jun-04 06.55.22.576205 PM -03:00
- LOCALTIMESTAMP # 24-JUN-04 03.57.28.856690 PM
- DBTIMEZONE
- SESSIONTIMEZONE #
- TZ-OFFSET
- EXTRACT

ALTER SESSION
SET NLS_DATE_FORMAT='DD-MON-YYYY HH24 : MI : SS';
ALTER SESSION SET TIME_ZONE='-3:0';
SELECT sessiontimezone,current_date FROM dual
# 26-JUN-2004 18:53:07

select tz_offset('Canada/Yukon') from dual # -07:00
select * from v$timezone_names
TZNAME TZABBREV
Africa/Algiers LMT
gotta have SYSDBA role or the SELECT FROM CATALOG system privilege

Select ename, hiredate, EXTRACT ( YEAR FROM hiredate)
FROM ct_employee
WHERE job = 'CLERK'
ENAME HIREDATE EXTRACT(YEARFROMHIREDATE)
Eddie Paige 26-JUN-04 2004

select extract(month from sysdate) from dual



Datetime Conversions, pg 1-20
Oracle 10g provides the INTERVAL data types for storing data related to time period
functions to convert CHAR and VARCHAR2 into INTERVAL type values
INTERVAL data types store time differences b/w datetime values.
2 categors:
- year-month intervals: year & month # NLS Dependent
- day-time intervals: day, hour, min, sedc # NLS independent

Time Conversion Functions
- FROM_TZ # convert a TIMESTAMP value to a TIMESTAMP WITH TIME ZONE
- TO_TIMESTAMP - convert a string of CHAR , VARCHAR2 to a TIMESTAMP
- TO_TIMESTAMP_TZ
- TO_YMINTERNVAL - convert CHAR, VARCHAR2 to INTERVAL YEAR TO MONTH
- TO_DSINTERVAL - convert CHAR, VARCHAR2 to INTERVAL DAY to SEC

SELECT FROM_TZ(TIMESTAMP'2000-03-28 08:00:00','3:00') from dual # 28-Mar-00 08.00.00.000000 AM +03:00

SELECT TO_TIMESTAMP('2000-12-01 11:00:00','YYYY-MM-DD HH:MI:SS') from dual

SELECT TO_TIMESTAMP_TZ('1999-12-01 11:00:00 -8,00','YYYY-MM-DD HH:MI:SS TZH:TZM') FROM dual/

SELECT hiredate, hiredate+TO_YMINTERVAL('01-02') AS hd_interval
FROM ct_employee
WHERE deptno = 20
HIREDATE HD_INTERVAL
02-APR-81 02-JUN-82 # one year and two months into future # cool

SELECT ename
to_char(hiredate,'dd:mm:yy hh:mi:ss') as "Hire Date",
to_char(hiredate+TO_DSINTERVAL('10 10:00:00'), 'dd:mm:yy hh:mi:ss')
as "10 Days 10Hours past HireDate"
from ..
where ...





12.17.06
Multi-COlumn Subqueries, pg 1-12
return more than one column

SELECT prod_i, cust_id, quantity_sold
FROM ct_sales
WHERE (prod_id, cust_id) IN
(SELECT prod_id, cust_id
FROM ct_sales_transactions)
AND cust_id = 1385

two types of column comparisons
- pairwise
-non-parewise

# pairwise comparison
SELECT empno, mgr, deptno
FROM ct_employee
WHERE (mgr, deptno) IN
( SELECT mgr, deptno
FROM ct_employee
WHERE empno IN (7369, 7900)
and empno NOT IN (7369, 7900)

# non-pairwise, same query as before
SELECT empno, mgr, deptno
FROM ct_employee
WHERE (mgr) IN
( SELECT mgr
FROM ct_employee
WHERE empno IN (7369, 7900)
AND deptno IN (
...
)
and empno NOT IN (7369, 7900)



Scalar Subqueries, pg 1-9
return a single value
used in DECODE & CASE, and all clauses of SELECT statement exxcept GROUP BY and CONNECT BY
not accepted as default values for columns or hash expressions
cannot use them in CHECK constraints, WHEN conditions, or statements unreleated to queries, such as CREATE

SELECT ename, job
( CASE WHEN deptno = ( SELECT deptno FROM ct_deptment WHERe loc = 'DALLAS')
THEN 'Research'
ELSE 'Others'
END) Department
FROM ct_employee

# this is confusing
SELECT ename, job, deptno
FROM ct_employee e
ORDER BY (SELECT deptno FROM ct_department d WHERE e.deptno = d.deptno)


Correlated Subqueries, pg 1-14
complex queries
need subquery to retreive data based on column values of main query
the correlated subquery executes once for each row that results from main query

For example, you need to find personal details of each employee in your organization. You write main query that selects employee codes. Based on that, the corr subquery extrracts personal data from another table

nested inside main query. But it executes differently. Executes after mani query has fetched a row

# find employees whose salaries are less than the average of the employees of a department
SELECT empno, sal, deptno
FROM ct_employee outer
WHERE sal lt
( SELECT avg(sal) FROM ct_employee WHERE deptno = outer.deptno);

# find details of those employees who have changed at least one job
SELECT em.empno, ename, em.job
FROM ct_employee em
WHERE 1 lt= (SELECT COUNT(*)
FROM ct_employee_history
WHERE empno=em.empno);



SQL Operators: EXISTS and NOT EXISTS, pg 1-11
identify presence of particular rows in the reuslt set of a subquery
used with correlated subqueries
tests whether column value retreived by main query exists in the result set of subquery
if exists, it returns TRUE, and the main query selects the specified columsn

# EXISTS when the subquery returns at least one row
# EXISTS ensure search through subquery stops after one row matching criteria is found
# stops when at least one record for an employee who is a manager is found
SELECT empno, ename FROM ct_employee emp
WHERE EXISTS ( SELECT '*' FROM ct_employee
WHERE mgr=emp.empno)

# return deptno, dname for those records where the department nubmer is not the same in ct_employee and ct_department
SELECT deptno, dname FROM ct_department dept
WHERE NOT EXISTS ( SELECT '*' FROM ct_employee
WHERE deptno = dept.deptno)

you can also use NOT IN in lieu of NOT EXISTS
# is this still a correlated subquery?
SELECT dname FROM ct_department dept
WHERE deptno NOT IN (SELECT deptno FROM ct_employee)



Correlated Operations, pg 1-9
two types: correlated UPDATE and correlated DELETE

# increase salary of employees based on who is in ct_emp table
UPDATE ct_employee emp1
SET sal = (SELECT sal+500
FROM ct_emp emp2

# delete employee records which have salaries more than the average salary
DELETE FROM ct_employee e
WHERE sal gt
( SELECT avg(sal) FROM ct_employee_history h
WHERE h.deptno = e.deptno)
WHERE emp1.empno = emp2.empno)



Correleated Subqueries: WITH Clause, pg 1-11
WITH clause makes query block reusable
features of the WITH clause used to perform correlated subqueries
A query is complex query when it has many references within the same query block and the query contains joins and aggregations. When too many system resources are used up to evaluate a complex query, you can reuse a query using the WITH clause. Reuslts are stored in users temporary tablespace to improve perofrmance.


#total & average calculated for every department. TOtal and average compared. Without the WITH clause, the query would require either correlated subqueries with joins, or in-line views with joins. The WITH clause is internally resolved as an inline view or temporary table

WITH
emp_query AS (SELECT d.dname, SUM(e.sal) AS total
FROM ct_employee e, ct_department d
WHERE e.deptno = d.deptno
GROUP BY d.dname),
average AS (SELECT (SUM(total)/COUNT(*)) AS avg1
FROM emp_query
SELECT *
FROM emp_query
WHERE total lt (SELECT avg1 FROM average)
ORDER BY dname





Hierarchical Queries, pg 1-18
hierachical relationships between rows in single table
tree walking
used in ares of human genealogy for creating family trees
corporate management structure of a compnay
scientific research

Select * from ct-employee
employees that report to same manager
and that manager belongs to same president of company
hierarchy exists b/w Empno & mgr columsn
joining table with itself and matching the values of empno & mgr
parent / child relationship
exists within same table
the direction and starting point of hierachy is decided using tree structure

SELECT [LEVEL], column FROM table [START WITH condition] [ CONNECT BY PRIOR condition]
LEVE refers to the position in the hierarchy, as a pseudocolumn
1 for root/parent row
2 for child of parent row
CONNECT BY specifies parent & child columns in the hier relationship
wehther directio of query is top-down, parent to child, bottom-up, from child to parent
position of PRIOR is important.
if operator specified before child column, direction is from parent to child
if before parent, direction from child to parent

# hierachy is constructed with root as the employee name Damian Page
SELECT empno, ename, job, mgr
FROM ct_employee
START WITH ename = 'Damian Paige'
CONNECT BY PRIOR mgr=empno

empno ename job mgr
7654 Damian Page Salesperson 7698
7698 Chris Donaldson Manager 7839
7839 Larry Williams President

# top down
SELECT empno, ename, job, mgr
FROM ct_employee
START WITH ename = 'Larry Williams'
CONNECT BY PRIOR empno=mgr

empno ename job mgr
7839 Larry Williams President
7698 Chris Donaldson Manager 7839
7654 Damian Page Salesperson 7698


Hierarchical Reports: Formatting, pg 1-11
to create hierarchy query, you report the relation among rows in a table in specific order
to specifically show the position of a row in a hier of a table, use the LEVEL

Room Node,
Parent Node, Parent Node, Parent Node
Leaf, Leaf, Leaf, Leaf, Leaf (no child)

COLUMN report FORMAT a12
SELECT LPAD(ename, LENGTH(ename)+(LEVEL *2)-2,'*')
AS report
FROM ct_employee
STARTY WITH ename = 'Larry Williams'
CONNECT BY PRIOR empno = mgr

Larry Willams
* Christine Turner
** Damian Page
*** David Johson
** Ed Young
* Chris Donaldson

To display part of the hierarchical report, you prune the inverted tree
eliminating either a whole branch or a node
to do this, you use a CONNECT BY clause and the WHERE clause0.

# remove node & children
SELECT ename AS nodes
FROM ct_employee
START WITH mgr IS NULL
CONNECT BY PRIOR empno=mgr
And ename != 'Christine Turner'

# remove node & leave children
SELECT ename AS nodes
FROM ct_employee
WHERE ename != 'Christine Turner'
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr


12.18.06
Regular Expressions: Functions, pg 1-30
sequence of characters made of literal & meta characters used to describe pattern in text
useful in searching & changing text patterns in the database
Oracle has introduced 4 functions
implemented as UNIX/Posix standard for data matching

metacharacters:
\ escape, treat next meta as literal
* zero or more
. any char
+ one or more
^ beginning
$ end
[...] any char in list
[^...] any char not in list
? zero or one
{m} interval, exactly m occurances
{m,} interval, at least m occurances
{m,n} interval, at least m, not more than n
\n back reference, matches nth proceding subexpression
| matches either
(...) matches expression as a unit
[:class:] any character in class

Regular Expression Functions:
- REGEXP_LIKE
- REGEXP_REPLACE
- REGEXP_INSTR
- REGEXP_SUBSTR

you can use these with char based data types:
CHAR, NCHAR, CLOB, NCLOB, NVARCHAR2, VARCHAR2

REGEXP_LIKE - searches DB column or PLSQL variable, use as part of WHERE or HAVING

SELECT ename, empno
FROM ct_employee
WHERE REGEXP_LIKE(ename,'^Da(mian|vid)(Paige|Johnson)$')

REGEXP_REPLACE

SELECT cust_street_address,
REGEXP_REPLAY(cust_street_address, 'West', 'East')
FROM ct_customers

REGEXP_INSTR - search for occuring, and return starting or ending position

SELECT cust_street_address
REGEXP_INST(cust_street_address,'[^[:alpha:]]')
FROM ct_customers
WHERE REGEXP_INST(cust_street_address,'[^[:alpha:]]') gt 0

REGEXP_SUBSTR

SELECT cust_street_address,
REGEXP_SUBSTR(cust_street_address,'[^ ]+')
FROM ct_customers
ORDER BY cust_street_address DESC


# also use regular expressions to enforce check constraints

ALTER TABLE ct_customers
ADD CONSTRAINT cons_mail
CHECK (REGEXP_LIKE(cust_email, '@')) NOVALIDATE

INSERT INTO ct_customers VALUES( ...)
if doesn't containt @ sign, returns an error

Assessment

SELECT cust_s