Profile Picture
IT Made Easy
The site provides some rough IT Notes that make your life easier. The site currently has notes on HTML, CSS, Regex, OOPS, JS, JQuery, PHP, Java, ASP.NET, C#, Database, Linux, URL Rewrite, SEO, Project Management and Hosting. Please do share this site if you like.

Data Manipulation Language

Next
Next
  • DATA MANIPULATION LANGUAGE
  • SELECT
  • They have projection (columns), selection (rows) and joining capabilities
  • DISTINCT or UNIQUE or ALL
  • Pseudo columns ROWNUM (before ordering) ROWID (phyiscal)
  • Asterisk
  • Literals Number, Character of String, Date, Interval
  • 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 deafult
  • 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 +ve and 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 other 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 recieves 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 cant 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)

IT Notes

by VinodSebastian



IT Downloads


Catalogue