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).