Oracle - Vinod Sebastian

Miscellaneous

REGULAR EXPRESSIONS:

REGEX_SUBSTR(string,pattern,position,whichoccurence,instructionofmatch) character string matched.
REGEX_INSTR(string,pattern,position,whichoccurence,optional,instructionofmatch) optional = 1 position after match otherwise position of match.
REGEX_REPLACE(string,pattern,replace,position,optional,instructionofmatch) pattern replaced.
CHECK (REGEXP_LIKE (columnname, regexpression)).
everything starts at 1.
instructionofmatch c=case sensitive i=caseinsensitive n=. to match newline m=multiline x=ignore white spaces last info has precedence.
[anyonecharacter] [^notanycharacter].
[:graph:] = [A-Za-z0-9punctuation].
+ one or more *zero or more ? zero or one {n1} {n1,} {n1,n2}.
(ab|bc|cd) where | operates like OR.
backslash Infront to tell you intend a character.
^starts $ends.
1 the first captured expression.

SESSION CONTROL STATEMENTS:

ALTER SESSION.
SET ROLE.

SYSTEM CONTROL STATEMENTS:

ALTER SYSTEM.

EMBEDDED SQL STATEMENTS:

Any DDL, DML, TCL that is integrated into a 3GL.

MISCELLANEOUS:

SYSTEM PRIVILEGES license to drive car and OBJECT PRIVILEGES license to drive particular car.
CREATE USER username IDENTIFIED BY password.
ALTER USER username IDENTIFIED BY password.
DROP USER username.
DROP USER username CASCADE.
CONNECT username/password.

Transaction Control Language

TRANSACTION CONTROL LANGUAGE:

Session based.
SAVEPOINT name.
ROLLOVER WORK name.
COMIMT WORK.

Data Control Language

Role and System privileges can be granted together but not with object privileges.

DATA DICTIONARY:

Metadata.
DBA_TABLES All tables in DB.
ALL_TABLES All tables to which current user has privileges.
USER_TABLES All tables owned by user.
For above three ((TABLE_NAME, STATUS, ROW_MOVEMENT, AVG_ROW_LEN), OWNER).

SYSTEM:

GRANT privilege TO user option.
REVOKE privileges FROM user.
Use ANY for any user in privileges.
WITH ADMIN OPTION that can give user to again grant WITH ADMIN OPTION.
Revoking this does not cascade.
GRANT ALL PRIVILEGES TO user.
GRANT privileges To PUBLIC All.

OBJECTS:

PUBLIC SYNONYMNS do not need access (since PUBLIC) Alias table it references need specific permission.
Even if permitted has to use user.table for others.
GRANT user PRIVILEGES ON table TO user WITH GRANT OPTION They can again grant.
ALL PRIVILEGES can be shortened to ALL for objects.
REVOKE privileges ON table FROM user cascades for objects.

Only INSERT, UPDATE, and REFERENCES privileges can be granted at the column level. When granting INSERT at the column level, you must include all the not null columns in the row.

USER_SYS_PRIVS (PRIVILEGE, ADMIN_OPTION) Privileges for your user.
USER_TAB_PRIVS ON whom we have granted privileges.

CONNECT for (session) typical generic user.
RESOURCE for (create table) typical application developer.
DBA for database administrator.
CREATE ROLE rolename.
GRANT privilege TO rolename.
GRANT role TO user not cascaded.
Role and privilege independent.

Data Manipulation Language

DATA MANIPULATION LANGUAGE:

SELECT.
They have projection (columns), selection (rows) and joining capabilities.
DISTINCT or UNIQUE or ALL.
Pseudo columns ROWNUM (before ordering) ROWID (physical).
Asterisk.
Literals Number, Character of String, Date, Interval.
INTERVAL ’24-3′ YEAR(2) TO MONTH, INTERVAL ’24’ MONTH, DAY TO SECONDS.
Operators use same precedence rules as in maths.

VIEWS are named SELECT statements.
TABLES are stored in data dictionary.
SYNONYMNS are aliases for other database objects.
DESCRIBE command provides information about the columns of the table.

WHERE IN, LIKE(_ 1 char, % zero or more char, IS NULL NOT NULL).
NULL is not equal to NULL.
Empty < Blank < Numeric and Operators < Upper < Lower.
AND >(precedence) OR NOT before column.

ORDER BY ASC(default) DESC NULL has highest priority.
ORDER BY n means nth column is select.

AS is an alias for column You can use space also.

DAUL has one row and one column with value X in every database.

INSERT.
UPDATE.
DELETE.

FUNCTIONS.
UPPRER(s1) LOWER(s1) INITCAP(s1).
CONCAT(s1,s2) s1 || s2.
LPAD(s1,n,s2) RPAD(s1,n,s2) Space default.
LTRIM(s1,2) RTRIM(s1,s2) Space default.
TRIM(triminfo trimchar FROM trimsource) ‘LEADING’, TRAILING’ or ‘BOTH’.
LENGTH(s1).
INSTR(s1,s2,pos,n) nth occurrence.
SUBSTR(s1,pos,len).
SOUNDEX(s1).

ROUND(n,i).
TRUNC(n,i).
REMAINDER(n1,n2) uses FLOOR.
MOD(n1,n2).

SYSDATE TO_CHAR to display time.
ROUND(d,i).
TRUNC(d,i) i is ‘MM’.
NEXTDAY(d,c) c is ‘Saturday’.
LASTDAY(d) last day of month.
ADD_MONTHS(d,n).
MONTHS_BETWEEN(d1,d2) d1>d2 for positive value and it is not rounded.
NUMTOYMINTERVAL(n,interval_period) intervalunit = ‘YEAR’ or ‘MONTH’ Returns INTERVAL YEAR TO MONTH.
NUMTODSINTERVAL(n,interval_period) intervalunit = ‘DAY’, ‘HOUR’, ‘MINUTE’ or ‘SECOND’ Returns INTERVAL DAY TO SECOND.

NVL(e1,e2) output numeric, varchar2 or NULL e2 is the value returned if e1 is null..
DECODE( expression , search , result [, search , result]… [, default] ) default is returned or NULL (if no d) if not matched NULL=NULL in this case IF-THEN-ELSE.
CASE expression1 WHEN condition1 THEN result1 ELSE resultfinal END.
NULLIF(e1,e2) returns NULL if e1=e2.

TO_NUMBER(e1,format_model,nls_params).
TO_CHAR(c).
TO_DATE(c, format_model,nls_params).

TO_TIMESTAMP(c, format_model,nls_params).
TO_DSINTERVAL(sql_format, nls_params).
TO_YMINTERVAL(‘y-m’).

CAST(e AS d) For casting.
TO_TIMESTAMP.
EXTRACT(fm FROM e) fm fm being ‘YEAR’, ‘MONTH’, ‘DAY’, ‘HOUR’, ‘MINUTE’, ‘SECOND’, ‘TIMEZONE_HOUR’, ‘TIMEZONE_MINUTE’, ‘TIMEZONE_REGION’, ‘TIMEZONE_ABBR’.
SYS_EXTRACT_UTC(dtz).
AT TIME ZONE DBTIMEZONE.
AT TIME ZONE SESSIONTIMEZONE.
AT LOCAL.

AGGREGATE FUNCTIONS can be used at SELECT, ORDER BY, HAVING.
COUNT(e1).
SUM(e1).
MIN(e1).
MAX(e1).
AVG(e1).
MEDIAN(e1) Ignores NULL.
RANK(c1) WITHIN GROUP (ORDER BY e1).
DENSE_RANK If there is a tie still ordering is consecutive.
aggregate_function KEEP (DENSE_RANK FIRST ORDER BY e1) OVER (PARTITION By columnname).
aggregate_function KEEP (DENSE_RANK LAST ORDER BY e1) OVER (PARTITION By columnname).

HAVING or GROUP BY can occur in either order.
GROUP BY cannot have column names.

FOREIGN KEY is PRIMARY KEY stored in another table.

INNER JOIN and OUTER JOIN.
EQUI-JOIN and NON EQUIJOIN.

(INNER) JOIN ON otherwise, WHERE.
LEFT (OUTER) JOIN RIGHT (OUTER) JOIN FULL (OUTER) JOIN Use + OUTER is optional.
Use table name to differentiate.
NATURAL JOIN by common column.
USING uses common column value or if not NULL.
ON columnname BETWEEN min AND max.
CROSS JOIN cartesian product.

SUB QUERIES.
SINGLE ROW SUBQUERY.
MULTI ROW SUBQUERY uses IN, operator ANY/SOME, operator ALL. The IN operator returns TRUE if the comparison value is contained in the list; in this case, the results of the subquery. The ANY and ALL operators work with the equal operators. The ANY operator returns TRUE if the comparison value matches any of the values in the list. The ALL operator returns TRUE only if the comparison value matches all the values in the list.
MULTI COLUMN SUBQUERY (col1, col2) = (SELECT col1, col2 …).
CORRELATED SUBQUERY in SELECT, UPDATE, DELETE.
WHERE EXISTS (subquery) WHERE NOT EXISTS (subquery).
WITH alias1 AS (subquery1), alias2 AS (subquery2) SELECT …. WITH query is temporary table or inline view.

UNION Union with no duplicates.
UNION ALL Union with duplicates.
INTERSECT Intersection with duplicates.
MINUS Additionally, if there are two identical rows in table_A, and that same row exists in table_B, BOTH rows from table_A will be removed from the result set.
ORDER BY only in the end (outer select) in the above cases with reference to first select or position.

GROUP BY ROLLUP (column1, column2) In addition to the regular aggregation results we expect from the GROUP BY clause, the ROLLUP extension produces group subtotals from right to left and a grand total. If “n” is the number of columns listed in the ROLLUP, there will be n+1 levels of subtotals.
GROUP BY CUBE (column1, column2)In addition to the subtotals generated by the ROLLUP extension, the CUBE extension will generate subtotals for all combinations of the dimensions specified. If “n” is the number of columns listed in the CUBE, there will be 2n subtotal combinations.
GROUP BY GROUPING SETS((column1, column2), column3, NULL).
GROUPING informs whether NULL value is stored value or created by ROLLUP or CUBE.

SELECT * FROM TABLE tablename WHERE … STARTS WITH columnname1 = … CONNECT BY PRIOR columnname1 = columnname2 AND … (PRIOR decides direction remember PRIOR PRIMARY KEY DOWNWARDS NEXT PRIMARY KEY IS FOREIGN KEY PRIOR FOREIGN KEY UPWARDS NEXT FOREIGN KEY IS PRIMARY KEY)
SYS_CONNECT_BY_PATH(columnname, character).
CONNECT BY ROOT columnname Any data from root node.

Correlated subquery is a subquery that receives value from outer query table. Here outer query is executed first and each row used by subquery. This is opposite of nested query.

CREATE TABLE tablename AS SELECT … –Here explicit NOT NULL are copied nothing else.
INSERT INTO tablename (columnnames) SELECT (columnnames) … –Here on constraint violation everything fails.
UPDATE tablename SET (columnnames) = (SELECT columnnames) …
INSERT option INTO table1 VALUES (columnnames1) INTO table2 VALUES (columnnames2) subquery –If option is ALL it processes all of the INTO clauses.
INSERT option WHEN expression INTO table1 VALUES (columnnames1) WHEN expression INTO table2 VALUES (columnnames2) subquery –If option is ALL it processes all of the INTO clauses.
For above you can’t use sequence geneartors as well as you have to alias the columns with table alias in subquery to use in insert.
MERGE INTO TABLE tablenname USING tablename or subquery ON condition WHEN MATCHED THEN UPDATE SET col = expression where_clause DELETE whereclause WHEN NOT MATCHED INSERT (column1, column2) VALUES, expression1, expression2) where_clause WHERE condition.
SELECT * FROM TABLE AS OF TIMESTAMP timestamp_expression or SCN scn_expression.
v$SYSTEM_PARAMETER(undo_retension) in seconds.
SELECT * FROM TABLE VERSION BETWEEN timestamp_expression1 AND timestamp_exprssion2 If VERSION_STARTXXX is NULL then row version created before if VERSIONENDXXX is NULL then row version current or deleted.
Combining above two VERSIONS BETWEEN AS OF TIME mentioned (order important).
SELECT * FROM USER_RECYCLEBIN or SELECT * FROM RECYCLEBIN.
CREATE TABLE … / ALTER TABLE … ENABLE ROW MOVEMENT.
SCN_TO_TIMESTAMP(s1).
TIMESTAMP_TO_SCN(t1).

The Oracle user SYS owns all base tables and user-accessible views of the data.
For these Oracle recommend creating temporary tables out of views, extracting required data and joining them. This helps overcome lack of read consistency. For this we query V$ views.
ALL_TAB_COMMENTS Comments on all tabs There is also DB_TAB_COMMENTS, USER_TAB_COMMENTS All updated when comments added.
ALL_COL_COMMENTS Comments on all columns There is also DB_COL_COMMENTS, USER_COL_COMMENTS All updated when comments added.
COMMENT ON objecttype fullObjectName c1 — objecttype can be TABLE or COMMENT.
DICTIONARY has TABLE_NAME and COMMENTS as columns.
USER_CATALOG (TABLE_NAME, TABLE_TYPE).
USER_OBJECTS (OBJECT_NAME, OBJECT_TYPE< STATUS) STATUS is INVALID for VIEWS (OBJECT_TYPE = VIEW).
USER_CONSTRAINTS (CONSTRAINT_NAME, CONSTRAINT_TYPE, R_CONSTRAINT_NAME, STATUS) STATUS = ENABLED CONSTRAINT_TYPE = P / R (foreign key) / U / C.
USER_TAB_COLUMNS (TABLE_NAME, COLUMN_NAME).

Data Definition Language

DATA DEFINITION LANGUAGE:

DEFAULT dafaultvalue — To set default value.
CONSTRAINT name_PK PRIMARY KEY (fieldname).
CONSTRAINT name_FK FOREIGN KEY (fieldname).
CONSTRAINT name_NN NOT NULL (fieldname) — ONLY INLINE.
CONSTRAINT name_CK CHECK (fieldname IN(x,y)).
CONSTRAINT name_UQ UNIQUE (fieldname).

Implicit commit occurs after DDL even if it fails on even if rolled back.

CREATE DATABASE.
CREATE TABLE.
ALTER TABLE ADD (columnname) MODIFY (columnname) (No need of brackets if single data) The existing data should not be altered by a change of properties Add DEFAULT value there are NULL values and we change to NOT NULL.
ALTER TABLE tablename RENAME columnname1 TO columname2.
ALTER TABLE DROP (columnname) (No need of brackets if single data) If it is a primary key or foreign key we need to add CASCADE CONSTRAINT.
ALTER TABLE tablename SET UNUSED COLUMN columnname Similar to drop though it is retained. Better performance in heavy usage.

ALTER TABLE ADD (CONTRAINT constraintname contrainttype (columnname) Not for NULL.
NOT NULL has to be in line not out of line (not using ADD).
ALTER TABLE MODIFY columnname CONTRAINT constraintname contrainttype.
ALTER TABLE tablename DROP PRIMARY KEY CASCADE or KEEP INDEX or DROP INDEX.
ALTER TABLE tablename DROP UNIQUE (col1, colo2, …).
To DROP NULL use modify as NULL.
ALTER TABLE DISABLE constrainttype constraintname — PRIMARY KEY UNIQUE CHECK FOREIGN KEY.
ALTER TABLE MODIFY constrainttype constraintname DISABLE — PRIMARY KEY UNIQUE CHECK FOREIGN KEY.
ENABLE VALIDATE same as ENABLE.
ENABLE NOVALIDATE same as ENABLE without validation.
DISABLE VALIDATE same as ENABLE Disables.
DISABLE NOVALIDATE same as DISABLE.
ENABLE ensures that all incoming data conforms to the constraint.
DISABLE allows incoming data, regardless of whether it conforms to the constraint.
VALIDATE ensures that existing data conforms to the constraint.
NOVALIDATE means that some existing data may not conform to the constrain.
DROP TABLE tablename CASCADE CONSTRAINTS.
ALTER TABLE … ON DELETE CASCADE or SET NULL (more relations) for deleting rows with constraints.
To use DEFERRED create constraint with DEFERRABLE in the end and then use.
SET CONSTRAINT ALL or contraintname DEFFERED Reverse it with IMMEDIATE.
ALTER TABLE RENAME contraintname1 TO constraintname2.

CREATE TABLE tablename fieldname fielddatatype CONSTRAINT — INLINE.
CREATE TABLE tablename fielddetails, ADD CONSTRAINT constraintname CONSTRAINTTYPE fieldname — EXTERNAL.
ALTER TABLE tablename MODIFY fieldname CONSTRAINT constraintname CONSTRAINTTYPE — INLINE.
ALTER TABLE tablename ADD CONSTRAINT constraintname CONSTRAINTTYPE fieldname — EXTERNAL.
FOREIGN KEY (columnname) REFERENCES tablename(columnname).

CREATE OR REPLACE VIEW viewname AS SELECT … WITH CHECK OPTION (Now with check option any insert or update operation that makes a record disappear from the view raises a trappable runtime error).
Views provide security and abstraction.
They are objects and satisfy underlying table constraints.
INLINE VIEWS.
ALTER VIEW viewname COMPILE.

CREATE SEQUENCE sequence_name sequence_options INCREMENT BY integer STARTS BY integer MAXVALUE integer MINVALUE integer NOMAXVALUE NOMINVALUE CYCLE NOCYCLE.
columnname.NEXTVAL columnname.CURRVAL.
Can use within INSERT, SELECT not in WHERE.
First call-in session must be NEXTVAL.
Even if INSERT fails INCREMENT occurs.

CREATE INDEX indexname ON tablename(columnname).
INDEX work on =, <, >, LIKE string% not on functions (unless function based index) or on inequality or %string.
Usually five indexes on a transaction-based table.

Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the non-leading key of the index

CREATE UNIQUE INDEX indexname ON tablename(columnname)
DROP INDEX indexname

CREATE OR REPPLACE SYNONYMN syynonymnname for tablename
CREATE OR REPLACE PUBLIC SYNONYMN synonymnname for tablename Here created by USER PUBLIC Everyone can see but access to be provided.

Consider A has created a table and B needs access We can give access by.
GRANT SELECT ON tablename TO user.
Access by user.tablename etc.
Now using PUBLIC SYNONYMNS user.table can be replaced with user. If user changes, we need to alter only PUBLIC SYNONYMN.
DROP SYNONYMN synonymnname.
DROP PUBLIC SYNONYMN synonymnname.
First namespace searched for is the schema followed by public.
SYNONYMN used by itself is private.

SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = tablename.
CREATE TABLE table_name PRIMARY KEY USING INDEX (CREATE INDEX indexname ON tablename(columnname) We can create index on PRIMARY KEY as well as UNIQUE.
CREATE TABLE tablename … CONTRAINT indexname(columnnames) USING INDEX (CREATE INDEX indexname ON tablename(columnnames) or … tablename(expression) to create function-based indexes. Only computation result is considered mainly not order.

FLASHBACK_TRANSACTION_QUERY (UNDO_SQL) UNDO_SQL the query that will undo but not necessarily the ROWID.
FLASHBACK TABLE tablename TO BEFORE DROP.
FLASHBACK TABLE tablename TO SCN scn_expression.
FLASHBACK TABLE tablename TO TIMESTAMP timestamp_expression.
FLASHBACK TABLE tablename TO RESTOREPOINT restore_point_expression.
When flashedback all constraints except refereential integrity constraints are restored.

EXTERNAL TABLE
CREATE TABLE statments
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER     -- ORACLE DATA_PUMP
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
SKIP 2
FIELDS ( fieldname fieldtype)
)
LOCATION 'filepath'
)

Cannot use LOB, Foreign key constraints, UNUSED Also no INSERT, UPDATE or DELETE.

Introduction

GENERAL:

SQL is 4GL.
“” can be used to make case sensitive for column names.
Schema is like a folder in a database. It is objects that can be owned by a user account.

NORMAL FORM:

1NF –> No repeating groups 2-dimensional.
2NF –> Primary key that is not composite.
3NF –> No data that does not provide the intend of primary key.
Boyce-Codd NF –> Remove certain rare logical inconsistencies.
4NF –> Every multivalued dependency is dependent on a super key.
5NF –> Every join dependency is a result of the candidate keys.

NAMESPACE:

USER, ROLES, PUBLIC SYNONYMNS System.
TABLE, VIEW, SEQUENCE, PRIVATE SYNONYMNS, USER DEFINED TYPES Schema.
INDEX Schema.
CONSTRAINT Schema.

DATATYPES:

CHAR(n) Retrieved with spaces.
VARCHAR2(n).
NUMBER(n, m) n is precision – total number in either side after scaling m is scale – total number on right side of decimal point. If a value has more significant digits to the left of the decimal place than are specified in the format, then pound signs (#) replace the value. If a positive value is extremely large and cannot be represented in the specified format, then the infinity sign (~) replaces the value. Likewise, if a negative value is extremely small and cannot be represented by the specified format, then the negative infinity sign replaces the value (-~).
DATE NLS_DATE_FORMAT NLS_TERRITORY.
TIMESTAMP(n 1-9/6).
TIMESTAMP(n 1-9/6) WITH TIMEZONE.
TIMESTAMP(n 1-9/6) WITH LOCAL TIMEZONE.
INTERVAL YEAR(n 1-9/2) TO MONTH.
INTERVAL DAY(n1 1-9/2) TO SECOND(n2 1-9/6).
BLOB.
CLOB.
NCLOB (UTF).