free software

# free software
theopencd.com
winlibre.com/en
osswin.sourceforge.net
myopensource.org
marcelgagne.com/freewinlist.html

oracle install notes

password: xxx for both sys and system account
e:\oraclexe
port for database listener: 1521
port for oracle services for microsoft transaction server: 2030
port for http listener: 8080

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

Oracle Database 10g SQL Fundamentals I Part 1

11.6.06

Oracle Database 10g: SQL Fundamentals I Part 1


Database Management in Oracle 10g 2hr
Preasses1
Database Manage: Intro
Oracle 10g
Components: Oracle 10g
isql*plus overiew
isqp*plus: interaction with a script file
sql statements
Post Assess1
Basic SQL Statements 2hr
preasses
retrieving data
select statement
select statement: arith
select statement: formatting
restricting data
where clause
where clause: comparison conditions
where clause: logical
where clause: rules of prec
order by clause
substitution variables
postassess
SQL Functions 2hr
single row functions
single row functions: features
char func
num func
date func
conv func
nested func
general func
conditional func
group functions
group functions: type
group by
Advanced SQL Statements 2hr
Joins
Natural Joins
Equijoin
Self-join
Outer Join
Non-equijoins
cartesian products
Subqueries
subqueries: introduction
single-row subqueries
multiple-row subqueries
Set Operators
set operators: types



sdlc - system development lifecycle

4 types of databases
- hierarchical
- network
- relationship
- object relational

5 phases of sdlc
- strategy and analysis: requirements, interviews
- design: model created, requirements depicted in graphical
- build and documentation: database built as a prototype, commands to make & populate tables
- transition: refined prototype, tested for user acceptance
- production: system delivered to the user, analysze performance



11.29.06 7:30pm
ER model pg. 1-17

Uses of Data models
- comm database design considerations
- categorizing info
- describing working databases
- analyzing design implementations

an ER model separates data structures from business activiities, ensuring minimal changes

three elements used in ER model
- entity
- attribute
- relationship

an entity represents areas which info is required. For example, courses, teachers, students are entities for the school

an attribute defines features of an entity, such as student name

an element relationship, such as course and teacher, many to one, etc.

attribute naming conventions
* : mandatory
o : optional

symbols to depict relationships
-- dashed line: maybe
-- solid line: mandaotry
gt- crows foot: one or more
-- single line: one and only one

Products
Priced Products
Item
Order
Customer






7:35pm
RDBMS, pg. 1-13
modeling and storing data in 2d tables, contain columns. Data stored in rows
columns contain attributes of the relation
rows contain values of the relation
select * from ct_employee
empno,name,salary represents attributes about employees
rows contain values about single employee

RDBMS
- collection of objects or relations
- contains operators that process relations
- maintain data integrity
- enables data access and mods

normalization process - reduce multiple copies of the data
minimize repetition
reduce inconsistencies
very strict normalization can make some queries run slower

referential integrity
values in one column correspond to values in another table

primary key: attribute that uniquely identies a value (or row
foreign key: same attribute refereinced in another table





Oracle 10g Architecture, pg. 1-21
sql, pl/sql, java, xlm: avoid proprietary lock-ins
Grid Computing: resources are pooled together

three components
- oracle database 10g
- oracle application server 10g
- oracle enterprise manager 10g grid control

infrastructure platform: includes J2EE
server applications: portals, web sites, java transactional apps, business intelligence apps
grid environment: central management console, create single logical entity of many databases
grouping of target: execute jobs, enforce standard policies, monitor perf, automate jobs

core components of 10g platform
- browser based clients
- application servers
- databases

brower-gtapp logic-gtbusiness logic-gtdata
store data in relational structure, and support Java & XML. Has Pl/sql engine




isql*plus overiew, pg. 1-21
browser based interface
local & remote databases
create script files for future use

command categors
- environment
- format
- file manipulation
- execution
- edit
- interaction
- misc: commands, such as connect, and displaying structure

unlike SQL, where you can continue lines, isql requires a " - "
sql uses functions to format results, whereas isql uses environment command

to access isql*plus
http://host:5560/isqlplus
enter sql, pl/sql, and sql*plus
execute : load script: save script

Describe tablename - fix inconsistences? not avail in earlier oracle




isql*plus: interaction with scritp file, pg. 1-18
run recently used stuff with History tab
set preferences: history size (of scripts), input area size, ouptut location
script configuration: script formatting, script execution, database admin


post assessment, got 71%

SQL Statements, pg. 1-15
sql standard accepted by ansi & iso
sql english like, accessible by inexperienced programmers

4 functions:
- data manipulation DML: 5 : select, insert, update, delete, merge
- data definition DDL: 6: create, alter, rename, drop, ate, comment
- transaction control: 3 : commit, rollback, savepoint
- data control : 2: grant, revoke



11.30.06
Select statement, pg 1-14
three features:
- projection: use selective columns from tables
- selection: use selective rows from tables
- joining: combine, specifying links b/w tables

you can split statements over multiple lines, using indent to make more readable

Select : Arithmetic Expressions, pg. 1-17
can be used in any clause, except FROM clause
with DATE & TIMESTAMP, can only use additon and subtraction operators
select name, salary, salary+500
constraints: not null & primary keys
blank space as a separator to define an alias
select name "Employee Name", job
also define alias with AS keyword, not mandatory
select ename AS name


Select Statement: Formatting Results, pg. 1-14
concatenation of strings, using " || " operator
select ename || job as "Employee and job"
a literal can be a date, char, or number
you must enclose date & char literals in single quotes
select 'SENIOR' emptype, ename # SENIOR becomes value in emptype column
if you want to include a single quote in a literal, you have option
- use alternative quote operator, selecting your own quote mark delimiter
can be single or multi-byte
select ename || q'['s department number is ]'||



Where Clause, pg 1-9
select just a few rows from the table
row filtering


Where Clause: Comparison Conditions, pg 1-15
gt lt = gt=
WHERE sal BETWEEN 3000 and 5000
WHERE mgr IN (1,2,3,4)
where expr LIKE 'value%
where expr NOT LIKE value
escape option
where job like '%\_MAN' escape \'
where mgr is NULL

WHERE Clause: Logical Conditions, pg 1-12
and, or, not
NOT BETWEEN 1 and 3
not like '%A%'
is not null


Where Clause: Rules of Precedence, pg, 1-7
order of precedence
1 arthithmetic
2 concat
3 comparison
4 is null, like, in
5 between
6 not equal to
7 not logical condition
8 and
9 or

where job like 'M%' or job like 'A%' and sal gt 8000
hmm
1st condition is job like A% and sal gt 8000
2nd condition is job like M%


ORDER BY clause, pg 1-10
asc, desc
sort by alias, expression, or columns
sort in asc, lowest value first, nulls grouped at bottom


Substituion Variables, pg 1-20
isql*plus enables user interaction to provide values for vars at run time
preceded by a & or &&
supplement where, order by, column expressions, table names, or entire select statements
enabling you to provide where clause at runitme
can also extract values from a file
or pass values from one sql statement to another
where deptno=&deptno
# finally learning something new
Prompt given to user:
Input Required
Enter value for deptno: 30
no validation checks performed
include quote if variable refers to date or char values
ie. where job = '&job'
select &col1, &col2
can use anywhere except as first word in statement
&& when you want to reuse
use prompted only once in same user session
where job='%%job'
you can also use the define command
DEFINE var_empno = 123
select ...
use not prompted at runtime
you can delete them with UNDEFINE
isql*plus commands
SHOW ALL - list of all system settings
VERIFY ON - confirm changes in SQL statements
isql*plus shows old & new variables if VERIFY ON


12.01.06

SQL Functions 2hr
Single Row Functions, pg-1-9
- formatting dates & umbers
- modifying data values
an argument can be a column name, expressions, variable value
single row funcitions
- return a single result for each row
- operate on each row returned in the query
- accept one or more arguments
can be nested
can be used in select, where, or order-by
multiple-row functions operate on many rows, return one value
usually used for calculating summary & statistics

character functions, pg 1-28
3 case manipulation fn: lower, upper, initcap
example:
select lower(ename) from ct_emp where ename like 'D%'
select ename from ct_emp where lower(ename) = "allen"

character manipulation functions:
- concat
- substr(val, start_i, num_i) # extract
- length
- inst - is one string present in another, returns either a position, or a zero
- lpad - ensure strings of minimum length
- rpad
- replace - replaces part of one string with another # A1=str_to_srch, A2=str_srch_for A3=repl_str
- trim - trim padding, converse to lpad/rpad

select ename,instr(ename,'a',1) as "" return pos if a is contained in ename
select lpad(col_name,ltfinal length), pad_char)
select lpad(ename,20,'*') from
select ename, TRIM('D' from ename) from # if name starts with D, trim, otherwise don't
select ename, REPLACE(ename,'D','J') from


Number Functions, pg 1-13
ROUND,TRUNC,MOD

select round(47.937,2) from dual
select round(47.937,0) from dual = 48
select round(47.937,-1) from dual = 50

select trunc(47.937,2) from dual
select trunc(47.937,0) from dual = 47
select trunc(47.937,-1) from dual = 40

select mod(300,70) from dual = 20 # remainder


Date Functions, pg 1-22
internal format: 20,05,11,17,19,20,01
century,year,month,day,hours,minu,sec
default for input and display is DD-MM-RR
RR format = May 16,04
YY format = May 16,04

if two digit year is b/w 0-49, the year of the current century is returned. in RR format
select ename from ct_emp where hiredate gt '10-mar-85'

Date Functions
SYSDATE
MONTHS_BETWEEN # RETURNS A NO
ADD_MONTHS
NEXT_DAY
LAST_DAY - returns date of the last day of month
ROUND - according to format model
TRUNC

select sysdate from dual # returns current date & time
select SYSDATE, NEXT_DAY(SYSDATE,'SUNDAY') from 27-JUN-04

ROUND- format models: month,day,year,cc (century)
if nothing specified, use the day
select sysdate, ROUND(sysdate,'MONTH')

TRUNC - similar, with format model

arthrithmetic operators
- add or subtract a number to/ from date
- subtract two dates
- add hours to a date


Conversion Functions
implicit data type conversions
explicit data type conversions

implicit conversions for assignment
char -gt num
char -gt date
num -gt varchar2
date-gtvarchar2

implicit type conversions for expression eval
varchar-gtnum
varchar-gtdate

explicit conversions
to_char
to_number
to_date
cast

to_char(number|date, [fmt], [nlsparams])

elements of the date format model
yyyy - 1979
year - nineteen seventy nice
month - june
mon - jun
mm - 06
date - sunday
dd- 23
dy - sun
sp - spelled out number six

sample format elements of valid date formats
scc - returns the centry
y,yyy
iyyy,iyy,iy,i
bc or ad
b.c. or a.d.
q - returns the quart of year (1-4) JAN-MAR=1
rm - returns roman number month JAN=1 DEC=x11

guidelines for using the date format model
- enclose the format model in a single quotation marks
- specifiy a valid date format element in the appropriate case
- separate the date value from the format model by a comma
- remove padded blansk or leading zeroes using the fill mode fm element

select ename,to_char(hiredate,'fmDD Month YYYY') as hiredate # 17 December 1980

elements of the Time FOrmat Elements
HH or HH12 or HH24 - hour of the day, or hour from 1-12, or hour from 0-23
MI - minutes 0-59
SS - seconds from 0-59
SSSSS - seconds past midnight 0-86399
AM or PM
A.M or P.M.

select ename,to_char(hiredate, 'fmMonth DDth YYYY,fmHH:MI AM')


elements of athe number format model
9 represents a number
0 forces a zero to be display
$ places a floating dollar sign
L uses the floating local currency symbol
. displays a decimal point
, displays a comma as the thousands indicator
D returns the decimal char at the specified position

0 : displays leading zeros: 09999 = 05678
MI: returns minus sign to right: 9999MI = 5678-
PR: returns neg in angle brackets: 9999PR lt5678gt
V : multiply by 10 for n number of times, 9999V99 567800
S: returns the negat or positiv sign: S9999 -5678, or +5678

to_char(sal,'999,999') = 1,600



to_number(char,[fmt],[nlsparams])
select to_number('289.45',999D99') as numeric

to_date(char,[fmt],[nlsparams])'
where hiredate gt to_date('July 30,1981),'fxMonth DD,YYYY')

RR date format

CAST(
conversions between compatible & incompatible data types such as CHAR & BINARY, and VARCHAR & INT

select deptno, CAST(deptno AS VARCHAR2(4)) AS DEPARTMENT


Nested Functions, pg 1-9
you can nest single-row functions to any level
select ename, UPPER(SUBSTR(ename,1,6)) as ""
select INITCAP(CONCAT(RPAD(ename,10),job))
select ename, to_char(add_months(hiredate,6) 'Ddspth Month, YYYY) as ""


General Functions, pg 1-14
can work on any datatype
NVL - replace null value with actual value
NVL2,- accepts 3 args
NULLIF - accepts 2 args, compared, if =, return null
COALESCE - accepts multiple arguments, returns first-null expression in list

select ename,sal,nvl(comm,0)
select ename,nvl2(comm,'salary with commission','salary') as
sleect ename, nullif(nlv(comm,0),sal)
select ename, coalesce(mg,comm,-2)


conditional expressions, pg 1-12
used to invoke if-the-else
use either CASE or DECODE

select ename
case when sal lt=2450 then 'sal not taxable'
when sal gt 2450 then 'sal taxable'
else 'none
end "Grade"

select ename,job,sal
Decode(job, 'clerk', 1.20*sal,
'salesperson', 1.3 * sal,
'manager', 1.4 * sal,
sal # default ) as "New Salary
# selecting 3 columns, but result as 4 columns


Group Functions: Types, pg 1-21
calculation on collection of records to achieve a single result, ie. SUM(*)
avg, count, min, max, stddev, sum, variance
group functions allow
- consider only distinct values # default is all, including dups
- substitue null values # default is to exclude NULL, you can use NVL, NVL2, or COALESCE
- use columns with char, varchar2, number, or date
select avg(nvl(comm,0) ) as average
select count(distinct job) as

Group BY clause, pg 1-19
divides rows of a table into groups
you use group functions to obtain summary information
guidelines:
- ?
- where clause is used to specify rows that are not to be devided
select deptno,avg(sal)
from ct_employee
group by deptno
group by deptno, then calculate average salary by that group.
missing where clause, means considere all rows in table

you can create groups within groups
select deptno as "dept", job as "design", avg(sal) as "avg salary"
from ct_employee
group by deptno, job

WHERE clause to exclude rows before grouping
HAVING to exclude rows after grouping
Steps to be performed using the HAVING Clause
- group the rows
- apply group function on the group
- display groups that match the condition in the HAVING
select sum(sal) as "total salary"
from ct_employee
group by deptno
having sum(sal) gt= 5000
order by sum(sal)

can nest 2 group functions
select sum(avg(sal)) sas "total dept avg sal"
from ct_employee
group by deptno


select round() from dual;





Advanced SQL Statements
Joins
Natural Joins
Equijoins
self-join
outer join
non-equijoins
cartesian products
Subqueries
subqueries: intro
single row subqueries
multi-row subqueries
Set Operators
set operators: types


12.7.06
Natural Join, pg. 1-13
select from multiple tables requires logical links
a natural join connects two tables with identical column names
selects rows that have equal values in all matched columns
if columns having same names but different data types, an error is returned
to join data without any redundancy, tables must be normalized
4 types of joins:
- natural
- equijoins
- self joins
- outer joins


for natural join, use "natural join", columsn with same nmae & data type
A natural join is a type of equi-join and oin column does not display data redundantly

select table1.column, table2.column from table1
natural join table2

select table1.column, table2.column from table1
join table2 using (column_name) # performs an equijoin

select table1.column, table2.column from table1
join table2 ON(table1.column_name = table2.column_name)


equijoins, pg 1-19
AKA simple join, or inner join
uses equality operators
if the tables are having multiple common columns, then specify columns with USING clause
when using USING clause, no columns in WHERE clause can have qualifyer
such as a table name or alias
ie: join ct_department USING(deptno)
where ct_department.deptno = 10 # invalid
to avoid confusion about qualifying column names, use table prefixes
ie: select ct_employee.ename from ct_employee
join ct_department on (ct_employee.depno = ct_department.deptno)
use of prefix helps enhance performance of the server
while using USING clause, you can't quality column with table prefix or table prefix alias
? getting fuzzy
while table prefixes may help distinguishing the column belonging to different tables, writing prefixes can be tedious. A possible solution is a table alias

if you have many columns with the same name in the two tables,
you can use the ON clause to specify which particular columsn are to be joined
# where as for natural join, maybe can only have one redundant column?


the ON clause can also be used in two tables to be join with different column names
table1: A, B
table2: C, D
table3: A, B, C, C



Self-Join
return date twice, for same query, pg 1-8
get data from employee data considering as managers, and then as subordinates
use ON clause to perform self-join

select ...
from ct_employee e join ct_employee m
on (e.mgr = m.empno)
Subordinate, Manager ID, Emp#, Manager, Emp#
most useful where hierachical structure exists in the table
for example, if you want to find out a particular employee's maanager, you first locate that employee
you have to look at employee table twice
find employees's last name
then name under the manager code id


Outer Join, pg 1-9
get all records from one table, and only certain records from another
inner joins, only those that match the condition specified
- left outer join
- right outer join
- full outer join
ltoutergt is optional
left: returns all rows from first table, even if no matches with 2nd table
full outer joins returns all rows of both tables, even if no match


non-equijoin, pg 1-10
joined by something other than the equality (=) operator
different values in related columns
arithmetic comparison or logical comparison

ct_salgrade: grade, lowsal, highsal
ct_employee: ename, sal

select e.ename, e.sal, g.grade
from ct_employee e join ct_salgrade g
on e.sal between g.lowsal and g.highsal

considerations
- conditions: !=, gt gt= lt lt= and BETWEEN
- specify low and high values when using BETWEEN
- use table aliases




Subqueries: Introduction, pg 1-15
an advanced feature of SELECT statement
values based on a condition, obtained by writing subqueries in WHERE clause
sometimes more than one query is required to solve a problem
nest one query in another
subquery is a SELECT statement inside another Select,Insert,Update,Delete
and can be nested with the WHERE, Having, and From,
and specified in column list of a Select statement

in a simple subquery, the inner query is executed only once.
the value returned is passed to outer statement
subquery doesn't depend on values that would be returned if outer query executed by itself

with correlated subquery
inner query is executed more than once
and what it returns depends on stuff returned by outer query
it is executed once for each return by the outer query

for both simple & correlated subquery
comparisons: = gt lt IN ANY ALL
Exists and Not Exists used in Simple, not in correlated


select ename from ct_employee where sal gt
( select sal from ct_employee where ename = 'JAMES' )
# outer query is displaying the value of ename, for employees whose salary greatre than James

top-N analysis
query can be writen to find top 5 salaries of a companies employees
\top 20 cities by population in order
or the top 13 batting averages of major league baseball players in order

inline views are used for Top-N analysis
normally queries are performed on tables & rows
but we can query from another select statement, the subquery, as a data source
written as part of the from clause is known as inline view

# inline view
select ename,sal
from (select ename, sal from ct_employee order by sal desc)
where rownum lt= 5
# rownum is a temporary pseudocolumn


Single Row Subqueries, pg 1-13
# list of employees who belong to certain manager
select empno, mgr from ct_employee

# this is considered 3 blocks, an inner query block & outer query block
select ename,mgr,sal
from ct_employee
where mgr =
( select mgr
from ct_employee
where empno = 7499)
and sal gt
( select sal
from ct_employee
where empno = 7369)
where mgr = (select mgr from ct_employee where empno = 7788)

# retrieve details of employees with minimum salary
select ename, mgr, sal
from ct_employee
where sal = ( select min(sal) from ct_employee)

# can also be placed in the HAVING clause
select deptno, min(sal)
from ct_employee
group by deptno
having main(sal) gt ( select min(sal) from ct_employee where deptno = 30)

# generates an eroror, subquery returns multiple rows
select empno, ename
from ct_employee
where sal = (select min(sal) from ct_employee group by deptno)

# single row subqueries that return null, outer query evaluates to false
select ename, mgr
from ct_employee
where job =
( select job from ct_employee where ename = 'John')


Multiple-Row Subqueries, pg 1-12
mostly used in where clause
the operators IN, ANY, ALL are used

# the code to retrieve data about employees wh are managers
select e.ename
from ct_employee e
where e.empno in (select m.mgr from ct_employee m)


# employees who are not analysts and whose salary is less than any analyst
select ename,job,sal,deptno
from ct_employee
where sal lt any (select sal from ct_employee where job = 'ANALYST')
and job ltgt 'ANALYST'

# employees who are not analysts and whose salary is less than all analyst
select ename,job,sal,deptno
from ct_employee
where sal lt all (select sal from ct_employee where job = 'ANALYST')
and job ltgt 'ANALYST'


select
from
where e.empno not in (select ... where mgr is not null

NOT IN condition is same as ltgt ALL

if there is NULLs
you can use the IN condition
select
from
where e.empno IN (select m.mgr from ct_employee m)



Set Operators: Types, pg. 1-23s
merge the output of two or more queries into single output
4 set operators
- UNION
- UNION ALL
- INTERSECT
- MINUS
compound queries

Guidelines
- number of columns should be same # actual names can be different
- data type same
- order by should contain column name from 1st query
- parenthesis, to change order of evaluation # all set operators equal precedence

ct_employee: empno, ename, job, mgr, hiredate, sal, comm, deptno
ct_job_history: empno, enmae, job, startdate, enddate, deptno

UNION - combines all rows, only distinct rows, in ascending order
UNION ALL - dups and unsorted. Cannot use distinct
INTERSECT - returns only common rows, uses ORDER BY, includes NULLs
MINUS - distinct rows from first, not in second

sometimes number & data type may not match
you can include dummy columns & conversions
such as to_char, to_number, to_date

select deptno, to_char(null) place, hiredate
from ct_employee
UNION
select deptno, loc place, to_date(null)
from ct_department

if column in one table, but not in other, you can substitute a literal

select empno, deptno
from ct_employee
where (empno, deptno) IN (select empno,deptno FROM ct_empoloyee UNION ...)

Assessment

reasons why an ISDN may not work

reasons why an ISDN may not work
- speed setting on router does not match switch
- dialer map does not contain phone #
- spid missing
- bad cable
- incorrect isdn switch type
- no route to remote network
- encapsulation types do not match

oracle study test2d

char(size) - max 2000
nchar(size)
varchar2(size) - max 4000
nvarchar2(size)


alter tablespace
rename datafile
database must be open
tablespace is taken offline


grant action, implicit commit after any DCL statement
in event of database crash


Resource parameters
- sessions_per_user
- cpu_per_session
- cpu_per_call
- connect_time
- idle_time
- logical_reads_per_session
- logical_reads_per_call
- composite_limit
- private_sga


all these are true:
- alter table employees drop primary key keep index;
- using index (create index emp_pk_idx on employee(empno));
- alter table employees drop constraint empno_pk;


select customer_name,loan_number
from borrower,loan
where loan number>10 and (customer_name = 'John' or customer_name = 'Jack');
# which is executed first: ?  where, from, and, or


CLOB - char large obj up to 4GB
NCLOB - for unicode
BLOB - large binary object
RAW - binary info up to 2000 bytes


check constriant, requires a condition  be true or unknown for every row in table
if a statement causes the condition to be false the statement is rolled back
the CHECK constraint cannot include SYSDATE, UID, USER, or USERENV SQL func


which of these functions returns the first non null value in an expression list?
COALESCE - select coalesce(null,null,'20') from dual;  # RETURNS 20
NULLIF


Which of the following returns a single numeric value?
ASCII
character fnunctions take char, or varchar2 and return a character string
Examples:  UPPER, LTRIM
character functions returning number values is small group of functions that take char arg and return a numeric, ie. LENGTH & ASCII

ALTER TABLE is used to drop a constraint


select last, first
from student
where marks < avg(marks)  # cant do this, have to use subquery to compare



changes you CANNOT make to an existing table
- a column that already contains nulls cannot be changed from null to not null
- a new column cannot be added as not null
- a columns size or datatype cannot be changed until the column does not contain any data


Assume table One contains A,B,C and Two contains A,B
One UNION Two A,B,C
One UNION ALL Two A,A,B,B,C
One INTERSECT Two A,B
One MINUS Two C


large batch jobs
ROLLBACK segments


MANAGE TABLESPACE
- take the tablespace online
- make the tablespace read only
- end a backup


LAtches - used in Oracle to protect internal memory structures
preventing simultaneous access
similar to locks.  Latches do internal memory, locks protect data structures


the following conditions should be met to make tablespace read only
- tablespace must be online
- tablespace must not contain any active rollback segments
- tablespace must not be involved in an open backup


Oralce sorting occurs:
- ORDER BY
- GROUP BY
- when an index is created
- when a merge sort is invoked by SQL optimizer because inadequate indexes exist for a table join


# ?
create tablespace temp
datafile "c:\database\temp.dbf"
temporary;


nvl2(x1,x2,x3) x1,x2,x3 are expressionses it returns
x3 if x1 is null
x2 if x1 is not null


MERGE
- can be used to conditionally insert
- can be used to conditionally update
- can perform the operation in parallel



multi-table inserts
restrictions:
- cannot be performaed on views
- cannot perform a multitable insert via a DB link
- cannot perform multitable insert into nested tables
- sum of all INTO columns cannot exceed 999
- sequences cannot be used in the subquery of the multitable insert


which if this trigger is fired once on behalf of the triggering statement even if the row is not affect?
* statement triggers


what is the characteristic of a latch willing to wait situation
- if latch not avail, the process will wait
- each time, the gets column value from v$latch is incremented
immediate
- if latch request is satisfied, the immediate_gets column is incremented
- otherwise the immediate_misses column is incremented


subqueries
in nested subqueries, they are resolved from inner-most, to outer-most
subqueries return rows with keywords like IN or EXISTS 
subquery cannot use ordering clauses like ORDER BY/ GROUP BY

selecting for update, which is already locked can cause hang
* FOR UPDATE WAIT 40
NOWAIT
select * 
from employees
where empno = 20
for update wait 40;


left outer join
right outer joint
full outer join - matching and unmatching rows from both tables


INSTR is a numeric function since it returns Numberic starting position of a substring.  Other scalar (single row functions), ASCII, CONCAt, INSTR
NVL is a numeric funciton


when does a sort occur?
- sql using the order by clause
- sql using the group by clause
- when an index is created
- when a merge sort is invoked by the sql optimizer because inadequate indexes exist for a table join


these are valued 
- alter table test1 add(constraint test1_pk primary key(col1));
- alter index test1_pk rename to test_pk;
- alter table test1 rename to test;



subqueries can be used by the following commands:
- create tables and insert records
- provide values to select, where, having in, update, delete
- insert records in the target views


immediate latch
- if latch request satisified, the immediate_gets column is incremented
- otherwise, the immediate_misses column is incremented


REVOKE select on jack.product_master from john


valid column names:
american$


30 characters, contain only ($), (_), and (#)


which tablespace is used as the temporary tablespace if "temporary tablespace" is not specified for the user
SYSTEM
there is no TEMP, CACHE tablespace in ORACLE


which is true for triggers?
* a trigger restriction specifies a boolean expression that is true to fire a trigger


CEIL(-84) = -83


which of these is used with GROUP BY to give cross tabulation?
GROUP BY CUBE
GROUP BY ROLLUP - gives automatic subtotals
HAVING - limmits GROUP BY to groups for which the condition is true

oracle study test2c

exclusive lock - allows queries but prohibits any other activity
share - allows queries but prohibits updates


retrieve all student records in all classes
left outer join

UPDATES without WHERE clauses are defined as unqualified UPDATES

syntax for a View
cannot use groupby, distinct, having, avg, sum, etc.


alter tablespace with TEMPORARY clause
oracle performs a checkpoint for all online datafiles
but does not ensure that all files can be written
any offline files may require media recovery before bringing the tablespace online

which of thesee is an iterative control
- for, while, goto


what type of locks are used with admin allows concurrent access to table,and also wants other users to prohibit other users to lock entire table exclusively?
- ROW SHARE UPDATE: allow concurrent access but prohibt users to lock entire table exclusively
- ROW EXCLUSIVE: same as ROW SHARE, but also prohibits locking in share mode
     which are acquired when updating, inserting, or deleting
- EXCLUSIVE allows uery on the locked resource but prohibits any other activity
- SHARE allows queries but prohibits updates to a table



"Binding" involves the use of placeholders and the USING clause, while concatenation adds the values directly to the SQL string.
the two methods to insert program values into sql string are binding & concat
binding is faster
when bindin, the sql string does not contain the the value; 
it contains only a placeholder name
this allows the user the ability to bind different values to the same sql statement
biding is much easier to write and maintain


types of triggers
- BEFORE statement trigger
- BEFORE row trigger
- AFTER statement trigger



REVOKE DELETE ON emp FROM MATT

CREAT VIEW dept_salary_vw AS
SELECT dept_no, salary, last_name
FROM employee
WHERE salary > 5000
with check option;
# cannot insert a row into view with salary > 5000

SELECT ... FOR UPDATE
- can be used to override locks
- cannot be used with DISTINCT and GROUP BY
- cannot be used with set operators and group functions


scalar subqueries
- return a single value
- can be used with CASE
- can b be used with ORDERBY
- can be used with SELECT
- VALUES clause of an INSERT statement
- WHERE clause
- ORDER BY clause
- As a parameter of a function


locking charasteristics of UPDATE, DElETE, INSERT
- acquires exclusive locks on the rows to be moidified, and row exclusive lock on table containing those rows
- other users can query the rows to be mod, but cannot alter.  Can work on other rows
- locks on rows and tables are released on COMMIT
- a rollback to savepoint, releases all locks acquired upto the named savepoint

GRANT option
allows for privileges passed on via WITH GRANT OPTION of GRANT to be revoked sepeartely from the privileges themselves
- revoke fails if it results in an abandoned object, such as a view, unless the CASCADE keyword has been specified
- privileges granted to this user by other users are not affected

MERGE 
- accepts the character string form of two labels
- the MERGE_LABEL function is a utility for merging two labels together
- it accepts the three character specification of a merge format


Sql*plus
to run a script file, issue the command "START filename, or use the @filename
SAVE
EDIT
GET


ALTER table command with the ADD clause in the Employee table


administrator has granted me the privilege Create any table and create procedure
* i can create tables in any schema but drop tables and create proc in my schema

GRANT select,insert,modify ON Employees TO HR WITH GRANT OPTION;


ROLLBACK with SAVEPOINT;
it rolls back just a portion of the transaction


which functions are used in Oracle 9i
Group functions return a result per set of rows, ie. DISTINCT, SUM
Object reference functions: Manage REF values in user-defined types (ie. DEREF and REFTOHEX)
Analytical funcitions, COmplex analytics
Misc functions: a couple dozen functions that do not fit any of the above categories (ie. NVL, USER, BFILENAME, GREATEST)


Which of these are the locking characteristics for explicit locking?
Explicit lock is put by a trigger
* DBA can lock any table
* Other users can lock tables they own or any tables on which they have been granted table privileges
Users can explicitly lock either tables or rows using select ... for update, or a lock statement


write a subquery
* subquery can be used by the following commands
- it can be used to create tables, and insert records
- it can be used to update records
- the subquery can be used to provide values for the conditions in SELECT, WHERE, HAVING IN, UPDATE, DELETE


procedures
CREATE OR REPLACE PROCEDURE truncobj (
  nm IN VARCHAR2,
  tp IN VARCHAR2 := ;'TABLE',
  sch IN VARCHAR2 := NULL)
IS
BEGIN  
   EXECUTE IMMEDIATE
     'TRUNCATE || tp || '' || nvl(sch, USER) || '.' || nm;
END

# WTF?  this is too tough
the following is the query to give all the clients and saleman in the city
SELECT name FROM sales_master
where city = 'new york' intersect
select name from sales_master
where city = 'new york' in 
(select city from client_master c
where c.client_no IN
select client_no from sales_order s
where salesman_no = sales_master.salesman_no)


COMPUTE - sql*plus  for calculating valoues
CHANGE - change characters in the buffer
CLEAR - clear definitions; or clear the screen or buffer
COLUMN - define column formats or characteristics


# turn on constraint
ALTER TABLE table_name
ENABLE CONSTRAINT constraint_NAME


Which of the following is true for DROP FUNCTION?
* it is used to remove standalone stored function from database


which of the following is true for views?
- primary key of the table should be included
- the view can be created using a single table
- aggregate functions cannot be used in a VIEW
- SELECT statement should not have subquery


which of this is true for iSQL*Plus
this is just a web-interface version of SQL*Plus


LEAST_UBOUND - returns char string label, one label which dominates both
GREatet_lbound - DETERMINE LOWEST LABEL OF THE DATA


mergE statement
- merges the data from a select statement into a table
- user can decide when data is updated/inserted via the "ON()" clause
- user can decide what columns are updated
- user can decide what data is inserted.


trusted in the function based index?
QUERY_REWRITE_ENABLED=TRUE
the followingn are requirements to create a function-based index in the user defined schema on his table:
- prereqs for creating a conventional index
- user should have the QUERY REWRITE system privilege
- to use the function-based indexes in queries, the QUERY_REWRITE_ENABLE=TRUE
- QUERY_REWRITE_INTEGRITY set to TRUSTED
- create the index in another schema the user must have the GLOBAL_QUERY_REWRITE privilege
- the table must have EXECUTE object privielge on the functionss) used in the function based index


steps to create a cursor?
- dclare a cursor that specifies the SQL select statement 
- open
- row at a time
- close

oracle study test2b

initcap()


concatenating char strings
  - concat two strings results in another string
  - preserves trailing spaces
  - uses concat as alternativ to vertical bar operator
  - trets zero-length char strings as nulls


convert


before trigger
after trigger


leve of precedence
1. all comparison ops
2. NOT 
3. AND
4. OR



grant privilege
privilege CONNECT nees to be assigned for user to access database
minimum privilege necessary required to access a database
by default, SYSTEM default userr tablespace


declare
begin
/* block1 */
declare
begin
end
/* block2 */
declare 
begin
end


rollback without a savepoint clause
- ends all transactions
- undoes all the changes in the current transactions
- erases all the savepoints in that transactions
- releases the transactions lock

create sequence order_seq
  start with 1
maxvalue 9999 
cycle;  # also nocycle


indexes
  context - for text retrieval, msword, html, xml, plain text
  ctxcat is used to improve mixed query performance, small text fragments, like dates, names
  ctxrule is used to build document classification app.  



grant select, update
on john.product_master
to jack

dml - select, insert, update, delete
ddl - create, alter, drop, rename, truncate
dcl - grant, revoke


subquery used in the FROM clause to eliminiate need to create a new view


spool, spool off
output is *.lst


MERGE can be used to both update and insert
rows that have matchinng keys are updated, others are inserted


lock table emp in row exclusive mode
  - it will wait if the resources are not available
  - row exclusive locks are same as row share
  - these locks are acquired when updating, inserting, deleting



check constraints, limitations
  - condition must be a boolean
  - condition cannot include sysdate, uid, user, or userenv sql
  - condition cannot contain subqueries or sequences


alter table supplier_master
  drop primary key;



CREATE/ALTER/DROP/TRUNCATE, DDL statements with implicit commits
DML do not have implicit
they are eligible for rollback until a commit is issued


normally, an operator recieves a NULL, the result is null
except CONCAt

script is defined as a group of SQL, Sql*plus, Pl/SQL.  
How can they be run?
- enter sql*plus, invoke commands and run interactively
- run a script in "batch mode", specify on command line
- speicify it on the command line with @ sign
  $ sqlplus user/pass@mydb @my_script.sql
two scripts are run too: glogin.sql & login.sql

get - loads external file contents into the buffer
append - appends a line to the buffer
edit - invokes system-default editor to the edit buffer


highest precedence
1. unary +/-, PRIOR
2  / arith ops
3. binary + - arith ops, || char ops
4. all comparison ops


order by desc
order by ascen, default, is alphabetically sorting




user_views - contains definition view
user_tab_views - contains table names for user views



how to run a sql command
- place semi-colon (;) as the end of last clause
- place a forward slash at the sql prompt
- issue a run command at the sql prompt


extneral table
new functionality
any flat file,, appears as real table
and user cannot index an external table


data dictionary
dba_: information useful for DBAs, view all database objects
user_: information on objects owned by a particular user id
all_: information on every object the user has access to


alter table sales_order_details
add constraint order_fkey
foreign key (s_order_no) references sales_order
modify (qty_order(7) not null);

select count(*)
SELECT CTX_QUERY.COUNT_HITS  # rough count


alter sequence
arguments used
- schema - name of the schema to contain the sequence
- increment by
- maxvalue
- minvalue
- nominvalue 

round(months_between(d1, d2))
ROUND(DAYS_BETWEEN)
MONTHS_BETWEEN

CURDATE 
CURRENT_DATE

Mod(10,0) = 0
returns remainder of m divided by n.  If n is 0, returns m


AGV cannot be used in a union
UNIONS
a) no. of columns in all queries should be the same
b) union cannot be used in subquery
c) aggregate functions cannot be used in a union
d) datatype of the columns in each query musts be the same


INSERT INTO employee(ID_NO,name,salary)
  values(employee_id_seq.NEXTVAL, &name, &salary)


SEQUENCE uses the following as default values:
- NOORDER
- INCREMENT BY 1
- CACHE 20


SELECT to_char(hire_date, 'fmDD MONTH YYYY')
  DATE HIRED FROM employee;


the purpose of GRANTS is to grants the ADMIN, DBA, DDL, or RESOURCE roles to users, or grants privileges on a database objects to users.  To grant roles, the user must be logged inot the database as SYSEM, or as a user with DBA/DDL AND ADMIN privileges, or with RESOURCE privileges to GRANT privileges on own objects to other users.  the dba role is recommended as a replacement for DDL role wheneve possible.

ALTER CREATE SEQUENCE order_seq
INCREMENT BY 2
CACHE 40;