Ora Training Blog

Oracle SQL Quick Reference

SELECT Query Statement

SELECT [DISTINCT] {*, column [alias],…}

FROM table

WHERE condition(s)]

ORDER BY {column, exp, alias} [ASC|DESC]]

NOTE:

Avoid using DISTINCT with large table as it first does sorting of all the rows and then eliminates duplicate rows, so need a full table scan and thus it’s very slow.

ORDER BY is the last clause to get executed and thus could see all the column aliases; You can sort by a columns that is not in SELECT list; Default sorting order is Ascending (ASC)

Joins

Oracle proprietary syntax for version 8i and earlier

Cartesian Product

SELECT table1.*, table2.*,[…]

FROM table1,table2[,…]

Equijoin(Simple joins or inner join)

SELECT table1.*,table2.*

FROM table1,table2

WHERE table1.column = table2.column

Non-Equijoin

SELECT table1.*, table2.*

FROM table1, table2

WHERE table1.column

BETWEEN table2.column1 AND table2.column2

Outer join

SELECT table1.*,table2.*

FROM table1,table2

WHERE table1.column(+) = table2.column

SELECT table1.*,table2.*

FROM table1,table2

WHERE table1.column = table2.column(+)

Self join

SELECT alias1.*,alias2.*

FROM table1 alias1,table1 alias2

WHERE alias1.column = alias2.column

SQL: 1999 compliant syntax for version 9i

SELECT table1.column, table2.column

FROM table1

[CROSS JOIN table2] |

[NATURAL JOIN table2] |

[JOIN table2 USING (column_name)] |

[JOIN table2

ON (table1.column_name=table2.column_name)] |

[LEFT|RIGHT|FULL OUTER JOIN table2

ON (table1.column_name=table2.column_name)];

Aggregation Selecting

SELECT [column,] group_function(column),…

FROM table

[WHERE condition]

[GROUP BY group_by_expression]

[HAVING group_condition]

[ORDER BY column];

Group function

AVG([DISTINCT|ALL]n)

COUNT(*|[DISTINCT|ALL]expr)

MAX([DISTINCT|ALL]expr)

MIN([DISTINCT|ALL]expr)

STDDEV([DISTINCT|ALL]n)

SUM([DISTINCT|ALL]n)

VARIANCE([DISTINCT|ALL]n)

NOTE:

All columns in the SELECT list that are not in group functions must be in the GROUP BY clause; but GROUP BY column does not have to be in the SELECT list.

You can not use group functions in the WHERE clause;You use the HAVING clause to restrict groups.

All group functions except count(*) ignores null values. The NVL function forces group functions to include null values.

Subquery

SELECT select_list

FROM table

WHERE expr operator(SELECT    select_list

FROM      table);

NOTE:

The subquery(inner query) executes once before the main query

The result of the subquery is used by the main query(outer query)

The ORDER BY clause in the subquery is not needed unless you are performing TOP-N analysis.

single-row comparison operators: Return only one row

=        >     >=    <     <=    <>

multiple-row comparison operators: Return more than one row

IN         ANY             ALL

Multiple-column Subqueries

SELECT column, column, …

FROM table

WHERE (column, column, …) IN

(SELECT column, column, …

FROM table

WHERE condition);

Data Manipulation Statements (DML)

INSERT Statement(one row)

INSERT INTO table [ (column [,column…])]

VALUES (value [,value…]);

INSERT Statement with Subquery

INSERT INTO table [ column(, column) ]

subquery;

UPDATE Statement

UPDATE table

SET column = value [, column = value,…]

[WHERE condition];

Updating with Multiple-column Subquery

UPDATE table

SET (column, column,…) =

(SELECT column, column,…

FROM table

WHERE condition)

WHERE condition;

Deleting Rows with DELETE Statement

DELETE [FROM] table

[WHERE condition];

Deleting Rows Based on Another Table

DELETE FROM table

WHERE column = (SELECT column

FROM table

WHERE condition);

MERGE statement

MERGE INTO table_name table_aliase

USING (table|view|sub_query) alias

ON (join condition)

WHEN MATCHED THEN

UPDATE SET

col1 = col1_val,

col2 = col2_val

WHEN NOT MATCHED THEN

INSERT (column_list)

VALUES (column_values);

NOTE:

MERGE provides ability to conditionally update or insert data into a database table. It is useful in data warehousing applications. It avoids separate updates; increase performance and ease of use

Transaction Control Statements (TCL)

COMMIT;

SAVEPOINT name;

ROLLBACK [TO SAVEPOINT name];

Displaying table structure

DESC[RIBE] tablename

Data Definition Statemetns (DDL)

CREATE TABLE Statement

CREATE TABLE [schema.]table

(column datatype [DEFAULT expr] [,…]);

CREATE TABLE Statement with Subquery

CREATE TABLE [schema.]table

[(column, column…)]

AS subquery;

NOTE:

When a table is created using existing table except “NOT NULL” constraint, no other constraints are copied. If condition satisfied even rows are copied.

Datatype

VARCHAR2(size)    CHAR(size)        NUMBER(p,s)       DATE

LONG              CLOB              RAW               LONG RAW

BLOB              BFILE             ROWID

ALTER TABLE Statement

Adding columns

ALTER TABLE table_name

ADD (column datatype [DEFAULT expr]

[, column datatype]…);

Changing a column’s type, size and default value of a Table

ALTER TABLE table_name

MODIFY (column datatype [DEFAULT expr]

[, column datatype]…);

RENAMING table column: Supported by 9.2 and higher version only

ALTER TABLE table_name

RENAME column_name to new_column_name;

Dropping a column

ALTER TABLE table_name

DROP (column);

Dropping a Table

DROP TABLE table_name;

Changing the Name of an Object

RENAME old_name TO new_name;

Trancating a Table

TRUNCATE TABLE table_name;

Adding Comments to a Table

COMMENT ON TABLE table_name | COLUMN table.column

IS ‘text’;

Dropping a comment from a table

COMMENT ON TABLE table_name | COLUMN table.column IS ” ;

Data Dictionary

ALL_OBJECTS       USER_OBJECTS

ALL_TABLES        USER_TABLES

ALL_CATALOG       USER_CATALOG or CAT

ALL_COL_COMMENTS USER_COL_COMMENTS

ALL_TAB_COMMENTS USER_TAB_COMMENTS

Defining Constraints

CREATE TABLE [schema.]table

column datatype [DEFAULT expr][NOT NULL]

[column_constraint],…

[table_constraint][,…]);

Column constraint level

column [CONSTRAINT constraint_name] constraint_type,

Constraint_type

PRIMARY KEY REFERENCES table(column) UNIQUE

CHECK (condition)

Table constraint level(except NOT NULL)

column,…,[CONSTRAINT constraint_name]

constraint_type (column,…),

NOT NULL Constraint (Only Column Level)

CONSTRAINT table[_column…]_nn NOT NULL …

UNIQUE Key Constraint

CONSTRAINT table[_column..]_uk UNIQUE (column[,…])

PRIMARY Key Constraint

CONSTRAINT table[_column..]_pk PRIMARY (column[,…])

NOTE:

PRIMARY Key defined on more than column called ‘Composite Primary Key’ and to define composite primary key you need table level constraint as two columns are involved.

FOREIGN Key Constraint

CONSTRAINT table[_column..]_fk

FOREIGN KEY (column[,…])

REFERENCES table (column[,…])[ON DELETE CASCADE]

[ON DELETE SET NULL];

CHECK constraint

CONSTRAINT table[_column..]_ck CHECK (condition)

Adding a Constraint(except NOT NULL)

ALTER TABLE table_name

ADD [CONSTRAINT constraint_name ] type (column);

Adding a NOT NULL constraint

ALTER TABLE table_name

MODIFY (column datatype [DEFAULT expr]

[CONSTRAINT constraint_name_nn] NOT NULL);

Dropping a Constraint

ALTER TABLE table_name

DROP CONSTRAINT constraint_name;

ALTER TABLE table_name

DROP PRIMARY KEY | UNIQUE (column) |

CONSTRAINT constraint_name [CASCADE];

Disabling Constraints

ALTER TABLE table_name

DISABLE CONSTRAINT constraint_name [CASCADE];

Enabling Constraints

ALTER TABLE table_name

ENABLE CONSTRAINT constraint_name;

NOTE:

A UNIQUE or PRIMARY KEY index is automatically created if you enable a UNIQUE key or PRIMARY KEY constraint.

Data Dictionary

ALL_CONSTRAINTS        USER_CONSTRAINTS

ALL_CONS_COLUMNS        USER_CONS_COLUMNS

View

Creating or Modifying a View

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view

[(alias[, alias]…)]

AS subquery

[WITH CHECK OPTION [CONSTRAINT constraint_name]]

[WITH READ ONLY [CONSTRAINT constraint_name]];

Top-N Analysis

SELECT [column_list], ROWNUM

FROM  (SELECT [column_list]

FROM table_name

ORDER BY Top-N_column)

WHERE ROWNUM <= N;

Removing a View

DROP VIEW view;

SEQUENCE

CREATE SEQUENCE Statement

CREATE SEQUENCE sequence

[INCREMENT BY n]

[START WITH n]

[{MAXVALUE n| NOMAXVALUE}]

[{MINVALUE n| NOMINVALUE}]

[{CYCLE | NOCYCLE}]

[{CACHE [n|20]| NOCACHE}];

Pseudocolumns

sequence.NEXTVAL        sequence.CURRVAL

Modifying a Sequence (No START WITH option)

ALTER SEQUENCE sequence

[INCREMENT BY n]

[{MAXVALUE n| NOMAXVALUE}]

[{MINVALUE n| NOMINVALUE}]

[{CYCLE | NOCYCLE}]

[{CACHE [n|20]| NOCACHE}];

Removing a Sequence

DROP SEQUENCE sequence;

Index

Creating an Index

CREATE INDEX index

ON TABLE (column[,column]…);

Removing an Index

DROP INDEX index;

Synoym

CREATE [PUBLIC] SYNONYM synonym

FOR object;

Removing Synonyms

DROP SYNONYM synonym;

Data Dictionary

ALL_VIEWS                     USER_VIEWS

ALL_SEQUENCES                 USER_SEQUENCES

ALL_INDEXES                   USER_INDEXES

ALL_IND_COLUMNS               USER_IND_COLUMNS

System Privileges (DBA)       User System Privileges

CREATE USER                   CREATE SESION

DROP USER                     CREATE TABLE

DROP ANY TABLE                CREATE SEQUENCE

BACKUP ANY TABLE              CREATE VIEW

CREATE PROCEDURE

Creating Users

CREATE USER user

IDENTIFIED BY password;

Creating Roles

CREATE ROLE role;

Granting System Privileges

GRANT privelges[,…] TO user[,…];

GRANT privelges TO role;

GRANT role TO user[,…];

Changing Password

ALTER USER user IDENTIFIED BY new_password;

Dropping Users

DROP USER user [CASCADE];

Dropping Roles

DROP ROLE role;

Object Privileges

Object      Table       View        Sequence          Procedure

ALTER         X                        X

DELETE        X          X

EXECUTE                                                  X

INDEX         X

INSERT        X          X

REFERENCES   X

SELECT        X          X             X

UPDATE        X          X

Object Privileges

GRAND object_priv [(column)]

ON object

TO {user|role|PUBLIC}

[WITH GRANT OPTION];

Revoking Object Privileges

REVOKE {privilege [,privilege…] | ALL}

ON object

FROM {user[,user…]|role|PUBLIC}

[CASCADE CONSTRAINTS];

Data Dictionary

ROLE_SYS_PRIVS

ROLE_TAB_PRIVS                USER_ROLE_PRIVS

USER_TAB_PRIVS_MADE           USER_TAB_PRIVS_RECD

USER_COL_PRIVS_MADE           USER_COL_PRIVS_RECD

Database Links

CREATE [PUBLIC] DATABASE LINK link_name

[CONNECT TO user_name IDENTIFIED BY password]

USING connection_string;

Single-Row Functions

Character Functions

LOWER(column|expression)

UPPER(column|expression)

INITCAP(column|expression)

INSTR(column|expression,m)

CONCAT(column1|expression1,column2|expression2}

SUBSTR(column|expression,m,[n])

LENGTH(column|expression)

LPAD(column|expression,n,’string’)

RPAD(column|expression,n,’string’)

TRIM(‘character’ FROM column|expression)

REPLACE(‘string1’, ‘string_to_replace’,[replacement_string])

Number Functions

MOD(m,n)

ROUND(column|expression,n)

TRUNC(column|expression,n)

Date Functions

MONTHS_BETWEEN(date1,date2)

ADD_MONTHS(date,n)

NEXT_DAY(date,’char’)

LAST_DAY(date)

ROUND(date[,’fmt’])

TRUNC(date[,’fmt’])

Arithmetic with Dates

• Add or subtract a number to or from a date for a resultant date value
• Subtract two dates to find the number of days between those dates
• Add hours to a date by dividing the number of hours by 24

Conversion Functions

TO_CHAR(number|date[,’fmt’])

TO_NUMBER(char[,’fmt’])

TO_DATE(char[,’fmt’])

NVL(expr1,expr2)

NVL2(expr1,expr2,expr3)

NULLIF(expr1,expr2)

COALESCE(expr1,expr2,…,exprn)

Conditional Expressions: Provide the use of IF-THEN-ELSE logic within a SQL statement.

DECODE(col|expr,search1,result1

[,search2,result2,…,]

[,default])

CASE expr WHEN comparision_expr1 THEN return_expr1

[WHEN comparision_expr2 THEN return_expr2

WHEN comparision_exprn THEN return_exprn

ELSE else_expr]

END

Nesting Functions

• Single-row functions can be nested to any level
• Nested functions are evaluated from deepest level to the least deep level
• In below example, functions F1, F2 and then F3 gets executed

F3(F2(F1(col,arg1),arg2),arg3)

Operators

Arithmetic        *     /     +     –

Comparison        =     >     >=    <     <=    <> or ^= or ~= or !=

BETWEEN…AND…, IN(set), LIKE, IS NULL

Concatenation     ||

Logical           AND   OR    NOT

Rules of Precedence

Order Evaluated         Operator

• 1 Arithmetic operators – multiplication and division(* /)
• 2 Arithmetic operators – Addition, subtraction and Concatenation operator ( + – || )
• 3 Comparison conditions
• 4 IS [NOT] NULL, LIKE, [NOT] IN
• 5 [NOT] BETWEEN
• 6 NOT logical condition; ** Exponentiation
• 7 AND logical condition
• 8 OR logical condition

Notes

• 1) Use ESCAPE identifier to search for the actual % and _ symbols (Here, % denotes zero or many characters and _ denotes one character in search condition )

Example: Get employee list having underscore (_) in last_name string

SELECT last_name

FROM EMPLOYEE

WHERE last_name LIKE ‘%_%’ ESCAPE ”;

• 2) DUAL is a dummy table you can use to view results from functions and calculations
• 3) SYSDATE is a function that returns: Date and Time
• 4) Inline view is a subquery with an alias that you can use within a SQL statement. A named subquery in the FROM clause of the main query is an example of an inline view. It is not a schema object.