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