C993

Cartseion Product

1) When no join condition is provided 2) join condition is invaid 3) all rows in first table are joined to all rows in second table 4) always include where clause

Three ways to do Column Alias

1) as name 2) name 3) " LAST nAme"

Truncate Statement Rules

Faster than delete No Rollback Does not fire triggers No where clause Recovers space

DCL Statements

Grant REvoke

Concatenating with literal strings

column_one || ' value ' || column_two

DML Statements

Select Insert Update Delete Merge

TO_CHAR = DATE_FORMATS

TO_CHAR(DATE, 'dd.mm.yyyy') 1) dd = day 2) mm = month 3) yyyy = year 4)hh24 vs hh/hh12 hour 24 or 12 5) mi = minutes 6)ss = seconds 7 AM/PM 8) FM removes spaces 9)Month = full month 10) DDsp = Four 11) ddth = 4th 12) Ddspth = fourth

Substituion Variable Rules

1) &EMPLOYEE_NUM - prompts everytime. 2) When using VARCHAR wrap in ' ' - '%EMPLOYEE_NAME', can be provided in prompt though 3) &&VARIABLE_NAME to only have a value prompted once and be reused during session

Order by Clause Rules

1) ASC or DESC, ASC by default 2) Always at the end of the select statement 3) Null comes last in asc order 4) Can override with NULLS FIRST; 5) Can sort by column alias 6) sort by expression COLUMN+100 7)Can sort by column not in select rule 8) Can sort by Column number 9) Can sort by both

Group functions

1) AVG(DINSTNCT | ALL n) - IGNORE NULL 2) Count - number of rows, including duplicates and rows with nulls 3) max(DINSTNCT | ALL) - IGNORE NULL 4) min (DINSTNCT | ALL) - IGNORE NULL 5)stdev(DINSTNCT | ALL) - IGNORE NULL 6) sum(DINSTNCT | ALL) - IGNORE NULL 7)LISTAGG - Orders data within each group secified in the order by clause and then concats the values of the meauring column 8) VARIANCE(DINSTNCT | ALL) - variance of N - IGNORE NULL

Rules of Precedence in Comparisons

1) Arithmitic 2) Concat 3) Comparison 4) IS [NOT] NULL LIKE [NOT] IN 5) [NOT] BETWEEN 6) NOT EQUAL TO 7) NOT LOGIC OPERATION 8) AND 9) OR

SQL: 1999 Syntax

1) Cross Join 2) Natural Join 3) Join table using 4) join table on 5) left, right, full outer join

SQL: 1999 Complian Joins

1) Cross Joins 2) Natural Joins 3) Using Clause 4) Full or two side outer joins 5) Arbitrary join conditions for outer joins)

Date Arithmitic

1) Date + number = date (adding days) 2) date - number = date (substracting days) 3) date - day = number of days 4) date + number/24 (add number of hours to a date)

Define/Undefine Substitution Variables

1) Define VARIABLE_NAME = VALUE 2)UNDEFINE EMPLOYEE_NUM 3) ONLY AVAILABLE DURING SESSION 3) can change prompt ACCEPT DEPT_ID PROMPT 'please enter dept id' ; 4) SET VERIFY ON to see variable before and after sql change 4) SET DEFINE OFF to use ampersand to look for values.

Group function rules

1) Distincts make the functions go against non-duplicate values. ALL is default 2) must be char, varchar, number, date 3) All group functions ignore null values, need to sub first 4) Must have group by clause 5) All columns not in group function must be in group by 6) where clause exlucdes rows before group 7) Can not use column alias in group by clause 8) Group functions can go in where clause, must be in having clause 9) Only two group functions can be nested

Fetch Rules

1) Fetch first 5 rows only 2) fetch first 50 percent rows only; 3) offset 5 rows fetch next 5 rows only; 4) offset 4 rows fetch next 50% rows only 5) fetch first 2 rows with ties

DUAL Table

1) Has one column, and one row with value x 2) public table

Create Sequence Syntax

1) Increment by N = specifies the interval between sequence numbers, where n is an integer. Default 1 2) Start with N = specifies the first seuqnece number to be generated, default 1 3) MAXVALUE = specifies the maxvalue 4) nomaxvalue =specifies maximum of 10^27, positive or negative 5) minvalue = specifies the minvalue 6) order/noorder = generated in order of request 7)cycle/nocycle = specifies whether the sequence continues to generate values after reaching its maximum or minimum value 8)cache = specifies how many vlaues the oracle server preallocates

Create Sequence Use

1) NEXTVAL = returns the next available sequence value. It returns unique even for different users 2) currval, obtains the current sequence number 3) nextval must be issued once before curvall 4) Don't use Cycle for PKs

Sunquery Rules

1) Subquery can go on both sides of comparison operator 2) single row operators to single row subqueries. multirow operatiors for mutli-row queries 3) Subqueries executed first

Data Dictionary Prefixes

1) USER_ = whats in your schema, what you own 2) ALL = CONTAINS ALL OBJECTS WHICH YOU HAVE ACCESS TO 3) DBA _ CONTAINS ALL OBJECTS THAT ALL USERS HAVE ACCESS TO 4) V$ PERFORMANCE RELATED DATA

Using quote escape character

1) q'[ work in department]' 2) q'(work in department)'

Oracle Join syntax

1) select a.id, b.id from a, b where a.id = b.id 2) use table names for alias 3) need n -1 join conditions where n is tables 4) outer join = table1.id(+) = tabl2.id(+)

Datetime data types

1)Timestamp : Data with fractional seconds 2)Interval year to Month: Stored as interval years and months 3)Interval day to second: Stored as interval of days, hours, minutes, and seconds

Most used Data Types

1)VARCHAR2: minimum size must be specified, max size is 32767, 4000 extended/legacy 2)Char: default and minimum size is 1, maximum size is 2000 3)number: 4) Date

rollback to savepoint name

1)rolls back transaction to the specified savepoint, therey discarding any changes and or savepoints you created that were after the saveponit you rolled back to 2)Omitting savepoint name, rollbacks entire transaction

TO_NUMBER/TO_DATE Fuction

1.Just inverse of to_char format models but using string and returning number or date 2. Does not care about spaces, will trim

Alter TABLE

Add Column Modify Column Define default value Drop column Rename column Change tabe to read only Add Constraint Disable Constraint Drop Constraint

Database Transactions

Begins when first DML statement is executed Ends with the following events: 1)Commit or rollback 2) A DDL or DCL statement (implicit commit) 3) User exits SQL Dev 4) System Crashses

COALESCE function

COALESCE( expr1, expr2, .... ) 1) Returns the first non-null expression in the expression list

Constraint Rules

Can name or let oracle specify for you Can create at time of table creation or after Can define at column or table level Can view constraints in dictionary Column Constraint: [column datatype, default, column constraint Not null has to be defined at column level One primary key per table

Alter Modify column

Can only decrease width if only null values, has no rows, decrease in column width doesn't overwrrite other values Can change from char to varchar only if sizes same changing default value only changes further columns

Alter Drop Column

Can only drop one column at a time Must have one column in table after drop Cant recover column that is dropped Cant drop PK unless with cascade Set to unused if lots of rows

Set unusued

Can set many columns Drop unused columns afterwards Online keyword allows DML statements Does not free space, and doesn't allow for it to accessed

Alter add table rules

Cannot specify where it appears if table already has rows, values will be null you can overwrite not null only with default value if table is empty can add not null with no default

CASE Function

Case column when comparion_expr1 then return_expr when comparion_expr1 then return_expr ... else else_expr End 1)ANSI SQL Compliant 2) returns null if no else

TCL Statements

Commit Rollback Savepoint

DDL Statements

Create Alter Drop Rename Truncate Comment

System Privs

Create User Drop User Drop any table Backup any table Select any table Create any table Create Session Create Table Create Sequence Create View Create procedure

Decode Function

DECODE(COL, search1, result1, search2, result2, .... default) 1) Returns null if no default

Default date format

DD-MON-RR 1)RR = 19xx if year between 50-99, 0-49 will be a 20xx year

How do you describe a table?

DESC or Describe

Oracle Proprietary Joins (8i and prior):

Equijoin Nonequijoin Outer Join Self Join

initcap function

INITCAT(column) Returns column with every word separate by space with first letter capitalized

Drop Table

Moves data to recycle bin Remove table entirely with purge invalidates depedents objects and removes pboject priveleges Removes all in the table and all indexes Needs DROP ANY Table Privelege

LPAD/RPAD Function

L/RPAD(COLUMN, NUMBER_TO_PAD, 'CHARACTER') 1) Pads the characters left or right to number specified

LAST_DAY FUNCTION

LAST_DATE(date) 1) returns last day of month

lower function

LOWER(COLUMN) returns column to lowercase

LENGTH FUNCTION

Length (first_name) 1) returns length of column

Other Data Types

Long: data type up to 2 gb not copied when using subquery cannot be included in group by or order by only one long column per table no constraint clob: single byte, or multie byte, up to 4 gb, stores national data set nclob: raw: long_raw: blob: bfile: rowid

MOD function

MOD(value_one, value_two) 1) returns the remainder

Month_between function

MONTHS_BETWEEN*(DATE_ONE, DATE_TWO) 1) returns a positive or negative value of months between two values specified 2) more accurate than days/30

Create Table Statement

Must have create table privelege Can only create in your schema Need Create ANY Table to put in other schemas Can use default in column definition

NEXT_DAY function

NEXT_DAY(date, 'name of day all caps' or NUMBER) 1) returns next date of day specified 2) 1 is Sunday by default 3) can changes through NLS_DATE_LANGUAGE

NULLIF Functon

NULIF(expr1, expr2) 1)Compares two expressions and returns null if they are equal, else returns first expression

NVL function

NVL(expr1, expr2) 1) Converts null value to an actual value 2) Must match data types

NVL2 Function

NVL2(expr1, expr2, expr3) 1) If expr1 is not null, nvl2 returns expr2, else nvl2 returns expr3 2) expr1 can be any data type 3) expr2-3 must match

Constraints

Not Null: specifies that the column cannot contain anull value Unique: can not have duplicates, but can have nulls Primary Key: Not NUll + Unique Foreign Key: Enforences referencial integrity to another table Check: specifies a condition that must be true

Intersect

Number of columns and data types must be exactly the same. Name of columns don't need to be valid Intersect does not ignore null values

ROUND Function

ROUND(COLUMN, place) 1)default is to round to no decimals 2)Negative values will correlate to whole number places 3) round date defaults to days 4) )Can truncate dates to specified time interval, 'DAY','YEAR',MONTH'

How to get current database server data and time?

SELECT SYSDATE FROM DUAL;

SUBSTR function

SUBSTR(column|expression, starting_pos, ending pos) 1) returns a portion of string 2)if you don't specify end position, it will go to end of string 3) if start_pos is negative, the count starts from end of string

Minus

Same rules as Union. Subtracts distinct rows from first select to second

Union All rules

Same rules as union, but returns all rows including duplicates. Output isn't sorted though

TO_CHAR Number Formats

TO_CHAR(number, format) 1) '9999' normal 2) '9,999' puts comma 3) '$9,999' puts comma + dollar 4) G/D use to specify group separator/decimal separator. Must use D if using G 5) Number format will round on decimal place 6)Converting big whole number to small format will fail (1598, '99) 7

Trim function

TRIM( [ [ LEADING | TRAILING | BOTH ] trim_character FROM ] string1 1) TRIMS SPACES BY DEFAULT

TRUNC function

TRUNC(COLUMN, values) 1) removes values/decimal places 2) default removes to whole number 3)negative values zero out previous whole places values 4)Can truncate dates to specified time interval, 'DAY','YEAR',MONTH'

Database Objects

Table View Sequence Index Synonym

upper function

UPPER(column) Returns column to uppercase

Set Operators

Union Union All Intersect Minus

Union Rules

Union returns all rows that are selected by either query. 1)Return all rows from multiple tables and elimnate duplicate rows 2)number of columns must be the same 3)data type of the columns must be the same 4)names don't have to be the same 5)Null values are not ignored during duplicate checking 6)by default, output is sorted in ascending order of the columns of the select clause 7)column names from the first query appear in the result 8)Order by clause can only appear once at the end 9) Only recognizes column names of the first select query

Single-Row Subquery

Use any normal comparison operator

Multi-row Subquery

Use in, all, any operator: 1) IN: Equal to any member in list - NOT IN with a single null returns no records 2) ANY: Must be preceed by comparison operator, returns true if at least one element is true 3) ALL: must be preced by compariosn operator, returns true if all conditions true

Escape special characters

WHERE JOB_ID like 'SA/_%' escape '/';

add_month function

add_months (date_one, value) 1) reutnrs new date with months added 2) can also use negative numbers

CONCAT function

concat(column_one, column_two) returns combination of two columns better to use ||

Commit Statement

ends current transaction by making all pending data changes permanenet

rollback

ends the current transaction by discarding all pending data changes

INSTR function

instr(column,, search_value, starting_pos, occurence) 1) 1 is the default 2)Search value can be entire string

Savepoint name

marks a savepoint within the current transaction