Oracle
Select
- Select
- Where
- DUAL Table
- SQL Comparison Operators
- ALL,ANY
- AND, OR, and NOT
- BETWEEN ... AND matches a range of values
- IN matches lists of values
- LIKE(NOT LIKE) matches patterns in strings
- IS NULL(IS NOT NULL) matches null values
- Group
- Having
- Order
- Join
- Set Operators
- CASE
- Hierarchical Queries
String Functions
| Function | Description |
|---|---|
| ASCII | Gives the ASCII value of the first character of a string. |
| CHR(n) | Character with ASCII value n |
| CONCAT | Concatenates two strings |
| INITCAP | Changes the first letter of a string or series of words into uppercase. |
| INSTR | Returns the beginning location of a pattern in a given string. |
| LENGTH | Returns the length of a string. |
| LOWER | Converts every letter of a string to lowercase. |
| LPAD | Adds a specified set of characters to the left of the original string. |
| LTRIM | Removes a set of characters from the left of a string. |
| REGEXP_COUNT | Returns the number of times the pattern is found. |
| REGEXP_INSTR | Returns the location of a pattern in a given string and supports the regular expression. |
| REGEXP_LIKE | Searches x for the regular expression defined in the pattern parameter. |
| REGEXP_REPLACE | Replaces string with the regular expression matching supports |
| REGEXP_SUBSTR | Returns the substring with the regular expression matching supports |
| REPLACE | Does the string replacement |
| RPAD | Adds a specified set of characters to the right of the original string. |
| RTRIM | Removes a set of characters from the right of a string. |
| SOUNDEX | Converts a string to a code value based on similar sounds. |
| String||String | Concatenates two strings. |
| SUBSTR | Retrieves a portion. |
| TRANSLATE | Replaces a string character by character. |
| TRIM | Removes a set of characters from both sides. |
| UPPER | Converts every letter to uppercase. |
| VSIZE | Returns the storage size. |
Numeric Functions
| Function | Description |
|---|---|
| abs(x) | absolute value of x |
| BITAND(x,y) | a bitwise AND on x and y. |
| CEIL | Returns the ceiling value (next highest integer above a number). |
| EXP | Returns e raised to a value. |
| FLOOR | Returns the floor value (next lowest integer below number). |
| LN(n),LOG(m,n) | Natural logarithm, and logarithm base m |
| Mod | Returns The Remainder Of N/M Where Both N And M Are Integers. |
| POWER | Returns value raised to some exponential power. |
| REMAINDER(n1, n2) | Identifies the multiple of n2 that is nearest to n1, and returns the difference between those two values. |
| ROUND | Returns the number rounded to nearest value, adjusts precision. |
| Sign | Returns 1 If The Argument Is Positive; -1 If The Argument Is Negative; And 0 If The Argument Is Negative. |
| Sqrt(x) | Square Root Of x |
| TRUNC | Returns the truncated value (removes decimal part of a number, precision adjustable). |
| SIN(n), COS(n), TAN(n) | Sine, cosine, and tangent of n (n expressed in radians) |
| ASIN(n), ACOS(n), ATAN(n) | Arcsine, arccosine, and arctangent of n |
| SINH(n), COSH(n), TANH(n) | Hyperbolic sine, hyperbolic cosine, and hyperbolic tangent of n |
| ATAN2(x,y) | Returns the arctangent of x and y. |
Conversion Functions
| Function | Description |
|---|---|
| ASCIISTR | Converts x to an ASCII string, where x may be a string in any character set. |
| BIN_TO_NUM | Converts a binary number x to a NUMBER. |
| CAST(x AS type) | Converts x to a compatible type specified in type. |
| CHARTOROWID(x) | Converts x to a ROWID. |
| COMPOSE(x) | Converts x to a Unicode string in normalized form. |
| CONVERT(x,source_char_set,dest_char_set) | Converts x from source_char_set to dest_char_set. |
| DECODE | Use it as if then else statement |
| DECOMPOSE(x) | Decomposes the string into the same character set as x and convert x to a Unicode string. |
| HEXTORAW(x) | Converts the character x containing hexadecimal digits (base-16) to a binary number (RAW). |
| NUMTODSINTERVAL(x) | Converts the number x to an INTERVAL DAY TO SECOND. |
| NUMTOYMINTERVAL(x) | Converts the number x to an INTERVAL YEAR TO MONTH. |
| NVL | Returns value if x is null; otherwise x is returned. |
| NVL2 | Returns value1 if x is not null; otherwise value2 is returned. |
| RAWTOHEX(x) | Converts the binary number (RAW) x to a VARCHAR2 string in equivalent hexadecimal number. |
| RAWTONHEX(x) | Converts the binary number (RAW) x to an NVARCHAR2 string in the equivalent hexadecimal number. |
| ROWIDTOCHAR(x) | Converts the ROWID x to a VARCHAR2 string. |
| ROWIDTONCHAR(x) | Converts the ROWID x to an NVARCHAR2 string. |
| TO_BINARY_DOUBLE(x) | Converts x to a BINARY_DOUBLE. |
| TO_BINARY_FLOAT(x) | Converts x to a BINARY_FLOAT. |
| TO_BLOB(x) | Converts x to a binary large object (BLOB). |
| TO_CHAR | Converts number and date value to string and format |
| TO_CLOB(x) | Converts x to a character large object (CLOB). A CLOB is used to store large amounts of character data. |
| TO_DATE | Converts string to date value |
| TO_DSINTERVAL(x) | Converts the string x to an INTERVAL DAY TO SECOND. |
| TO_MULTI_BYTE(x) | Converts the single-byte characters in x to the corresponding multi-byte characters. |
| TO_NCHAR(x) | Converts x in the database character set to an NVARCHAR2 string. |
| TO_NCLOB(x) | Converts x to a large object NCLOB. |
| TO_NUMBER(x [, format] | Converts x to a NUMBER. |
| TO_SINGLE_BYTE(x) | Converts the multi-byte characters in x to the corresponding single-byte characters. |
| TO_TIMESTAMP(x) | Converts the string x to a TIMESTAMP. |
| TO_TIMESTAMP_TZ(x) | Converts the string xto a TIMESTAMP WITH TIME ZONE. |
| TO_YMINTERVAL(x) | Converts the string x to an INTERVAL YEAR TO MONTH. |
| TRANSLATE(x,from_string, to_string) | Converts all occurrences of from_string in x to to_string. |
| UNISTR(x) | Converts the characters in x to an NCHAR character. |
Aggregate Functions
| Name | Description |
|---|---|
| AVG | Calculates the average |
| COUNT(x) | Returns the number of rows returned by a query involving x |
| MAX | Returns the maximum value |
| MEDIAN | Returns the median value |
| MIN | Returns the minimum value |
| STDDEV | Returns the standard deviation |
| SUM | Returns the sum |
| VARIANCE | Returns the variance |
Incorrect Usage of Aggregate Functions
Data types
Date functions
| Function | Description |
|---|---|
| ADD_MONTHS(d, n) | Date d plus n months |
| LAST_DAY(d) | Last day of the month containing date d |
| MONTHS_BETWEEN(d, e) | Months between dates d and e |
| NEXT_DAY(d, weekday) | The first weekday (mon, tue, etc.) after d |
| NEW_TIME(d, z1, z2) | Convert date/time d from time zone z1 to z2 |
| ROUND(d[, fmt]) | d rounded on fmt (the default for fmt is midnight) |
| TRUNC(d[, fmt]) | d truncated on fmt (the default for fmt is midnight) |
| EXTRACT(c FROM d) | Extract date/time component c from expression d |
| Sysdate | Returns the current operating system date from database server |
Timestamp Functions
| Function | Description |
|---|---|
| CURRENT_TIMESTAMP | Returns a TIMESTAMP WITH TIME ZONE for the session |
| EXTRACT | Extracts and returns the year, month, day, hour, minute, second, or time zone from x. |
| FROM_TZ(x, time_zone) | Merges xand time_zone into one value. |
| LOCALTIMESTAMP | Returns a TIMESTAMP for the session. |
| SYSTIMESTAMP | Returns a TIMESTAMP WITH TIME ZONE for the database. |
| SYS_EXTRACT_UTC(x) | Converts the TIMESTAMP WITH TIMEZONE x to a TIMESTAMP in UTC. |
| TO_TIMESTAMP(x, [format]) | Converts the string x to a TIMESTAMP. |
| TO_TIMESTAMP_TZ(x, [format]) | Converts the string x to a TIMESTAMP WITH TIMEZONE. |
Time Interval Functions
| Function | Description |
|---|---|
| NumToDSInterval | Converts number x to INTERVAL DAY TO SECOND. |
| NumToYMInterval | Converts number x to INTERVAL YEAR TO MONTH. |
| TO_DSInterval | Converts string x to INTERVAL DAY TO SECOND. |
| TO_YMInterval | Converts string x to INTERVAL YEAR TO MONTH. |
Time Zone Functions
| Function | Description |
|---|---|
| CURRENT_DATE | returns the current date in the local time zone in the database session |
| DBTIMEZONE | Returns the time zone for the database |
| NEW_TIME(x,time_zone1,time_zone2) | Converts x from time_zone1 to time_zone2 |
| SESSIONTIMEZONE | Returns the database session time zone |
| TZ_OFFSET(time_zone) | Returns the offset for time_zone in hours and minutes |
| SYS_EXTRACT_UTC | Extracts the UTC from a datetime value. |
General Functions
| Function | Description |
|---|---|
| GREATEST(a, b, ...) | Greatest value of the function arguments |
| LEAST(a, b, ...) | Least value of the function arguments |
| NULLIF(a, b) | NULL if a = b; otherwise a |
| COALESCE(a, b, ...) | The first not NULL argument (and NULL if all arguments are NULL) |
Analytical Functions
Ranking Functions
| Ranking Functions | Description |
|---|---|
| ROWNUM | pseudo-column and is calculated as rows are retrieved |
| ROW_NUMBER | row number for ranking |
| RANK | Returns the rank of items in a group and leaves a gap for a tie. |
| DENSE_RANK | Rank the rows and does not skip ties |
| PERCENT_RANK() | Returns the percent rank of a value relative to a group. |
| CUME_DIST() | Returns the position of a value relative to a group. |
| NTILE | Groups data by putting data into a number of percentile groups |
| Hypothetical | Rank and Distribution Functions |
Group By Functions
| Group By Functions | Description |
|---|---|
| PARTITION BY | Separate data groups and perform calculation within that group |
| CUBE | Returns rows containing a subtotal for all combinations of columns |
| ROLLUP | Returns a row containing a subtotal |
| GROUPING | Deals with NULL value for ROLLUP and CUBE |
| GROUPING_ID | Returns the decimal equivalent of the GROUPING bit vector. |
| GROUP_ID | Removes duplicate rows returned by a ROLLUP or CUBE. |
Inverse Percentile Functions
| Inverse Percentile Functions | Description |
|---|---|
| PERCENTILE_DISC | Checks the cumulative distribution values |
| PERCENTILE_CONT | Checks the percent rank values in each group |
Window Functions
- Cumulative Sum
- Moving Average
- Centered Average
- Gets the first rows in a window.
- Gets the last rows in a window.
Report Function
- RATIO_TO_REPORT()
- LAG
- LEAD
- FIRST gets the first values in an ordered group.
- LAST gets the last values in an ordered group.
Linear Regression Functions
| Linear Regression Functions | Description |
|---|---|
| REGR_AVGX(y, x) | Returns the average of x after eliminating x and y pairs |
| REGR_AVGY(y, x) | Returns the average of y after eliminating x and y pairs |
| REGR_COUNT(y, x) | Returns the number of non-null number pairs that are used to fit the regression line |
| REGR_INTERCEPT(y, x) | Returns the intercept on the y-axis of the regression line |
| REGR_R2(y, x) | Returns the coefficient of determination of the regression line |
| REGR_SLOPE(y, x) | Returns the slope of the regression line |
| REGR_SXX(y, x) | Returns REG_COUNT (y, x) * VAR_POP(x) |
| REGR_SXY(y, x) | Returns REG_COUNT (y, x) * COVAR_POP(y, x) |
| REGR_SYY(y, x) | Returns REG_COUNT (y, x) * VAR_POP (y) |
Subqueries
- Single-row subqueries
- Single-row subquery Errors
- Multiple-Row Subqueries
- Multiple-column subqueries
- Correlated subqueries
- Nested subqueries
Table
- Describe
- Insert
- Update, Returning
- Delete
- Merge
- Primary Key
- Foreign Key
- Default Values
- Transactions
- Table Creation, Rename, Truncating, Data Dictionary, Dropping
- Columns
- Constraints
- Comments
- Sequence
- Index
- Views
- Flashback Data Archives
SQLPlus and Reporting
| Item | Description |
|---|---|
| Column | Set up the column format |
| Script | Execute a script file |
| Echo | Echo feature |
| Verify | Verify feature |
| Break | Break on a column |
| Comment | Adding remarks to the script |
| TTITLE | Top title |
| BTITLE | Bottom title |
PL/SQL
- Block structure
- IF...ELSIF
- LOOP...END LOOP
- WHILE
- FOR LOOP
- Cursors
- Exceptions
- Procedures
- Functions
- Packages
- Triggers
- Object Types
- Collections
User, Privilege, Role
Data Dictionaries
- ALL views have information about all accessible data.
- USER views have information about your own data.
- DBA views have database-wide information.
- V$ views are dynamic performance views.
Appendix
Home »
Oracle
Oracle