{"id":182,"date":"2009-06-24T11:14:25","date_gmt":"2009-06-24T05:44:25","guid":{"rendered":"http:\/\/www.oratraining.com\/blog\/?p=182"},"modified":"2009-06-24T11:14:25","modified_gmt":"2009-06-24T05:44:25","slug":"sq-quick-referenence","status":"publish","type":"post","link":"https:\/\/www.oratraining.com\/blog\/2009\/06\/sq-quick-referenence\/","title":{"rendered":"Oracle SQL Quick reference"},"content":{"rendered":"

Oracle SQL Quick Reference<\/strong> <\/strong><\/p>\n

SELECT Query Statement<\/strong><\/p>\n

SELECT [DISTINCT] {*, column [alias],…}<\/p>\n

FROM table<\/p>\n

WHERE condition(s)]<\/p>\n

ORDER BY {column, exp, alias} [ASC|DESC]]<\/p>\n

NOTE:<\/em><\/strong> <\/em><\/p>\n

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.<\/em><\/p>\n

<\/em><\/p>\n

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)<\/em><\/p>\n

<\/p>\n

Joins<\/strong><\/p>\n

Oracle proprietary syntax for version 8i and earlier<\/strong><\/p>\n

Cartesian Product<\/strong><\/p>\n

SELECT table1.*, table2.*,[…]<\/p>\n

FROM table1,table2[,…]<\/p>\n

<\/strong><\/p>\n

Equijoin(Simple joins or inner join)<\/strong><\/p>\n

SELECT table1.*,table2.*<\/p>\n

FROM table1,table2<\/p>\n

WHERE table1.column = table2.column<\/p>\n

<\/strong><\/p>\n

Non-Equijoin<\/strong><\/p>\n

SELECT table1.*, table2.*<\/p>\n

FROM table1, table2<\/p>\n

WHERE table1.column<\/p>\n

BETWEEN table2.column1 AND table2.column2<\/p>\n

<\/strong><\/p>\n

Outer join<\/strong><\/p>\n

SELECT table1.*,table2.*<\/p>\n

FROM table1,table2<\/p>\n

WHERE table1.column(+) = table2.column<\/p>\n

SELECT table1.*,table2.*<\/p>\n

FROM table1,table2<\/p>\n

WHERE table1.column = table2.column(+)<\/p>\n

<\/strong><\/p>\n

Self join<\/strong><\/p>\n

SELECT alias1.*,alias2.*<\/p>\n

FROM table1 alias1,table1 alias2<\/p>\n

WHERE alias1.column = alias2.column<\/p>\n

SQL: 1999 compliant syntax for version 9i<\/strong><\/p>\n

SELECT table1.column, table2.column<\/p>\n

FROM table1<\/p>\n

[CROSS JOIN<\/strong> table2] |<\/p>\n

[NATURAL JOIN<\/strong> table2] |<\/p>\n

[JOIN<\/strong> table2 USING<\/strong> (column_name)] |<\/p>\n

[JOIN<\/strong> table2<\/p>\n

ON<\/strong> (table1.column_name=table2.column_name)] |<\/p>\n

[LEFT<\/strong>|RIGHT<\/strong>|FULL<\/strong> OUTER<\/strong> JOIN<\/strong> table2<\/p>\n

ON<\/strong> (table1.column_name=table2.column_name)];<\/p>\n

<\/strong><\/p>\n

Aggregation Selecting<\/strong><\/p>\n

SELECT [column,] group_function(column),…<\/p>\n

FROM table<\/p>\n

[WHERE condition]<\/p>\n

[GROUP BY group_by_expression]<\/p>\n

[HAVING group_condition]<\/p>\n

[ORDER BY column];<\/p>\n

<\/strong><\/p>\n

Group function<\/strong><\/p>\n

AVG([DISTINCT|ALL]n)<\/p>\n

COUNT(*|[DISTINCT|ALL]expr)<\/p>\n

MAX([DISTINCT|ALL]expr)<\/p>\n

MIN([DISTINCT|ALL]expr)<\/p>\n

STDDEV([DISTINCT|ALL]n)<\/p>\n

SUM([DISTINCT|ALL]n)<\/p>\n

VARIANCE([DISTINCT|ALL]n)<\/p>\n

<\/strong><\/p>\n

NOTE:<\/strong><\/p>\n

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.<\/em><\/p>\n

<\/em><\/p>\n

You can not use group functions in the WHERE clause;You use the HAVING clause to restrict groups.<\/em><\/p>\n

All group functions except count(*) ignores null values. The NVL function forces group functions to include null values.<\/em><\/p>\n

<\/strong><\/p>\n

Subquery<\/strong><\/p>\n

SELECT select_list<\/p>\n

FROM table<\/p>\n

WHERE expr operator(SELECT \u00a0\u00a0 select_list<\/p>\n

FROM \u00a0\u00a0\u00a0\u00a0 table);<\/p>\n

NOTE:<\/strong><\/p>\n

The subquery(inner query) executes once before the main query<\/em><\/p>\n

<\/em><\/p>\n

The result of the subquery is used by the main query(outer query)<\/em><\/p>\n

<\/em><\/p>\n

The ORDER BY clause in the subquery is not needed unless you are performing TOP-N analysis.<\/em><\/p>\n

single-row comparison operators: <\/strong>Return only one row<\/p>\n

=\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 > \u00a0\u00a0\u00a0 >= \u00a0\u00a0 < \u00a0\u00a0\u00a0 <= \u00a0\u00a0 <><\/p>\n

multiple-row comparison operators: <\/strong>Return more than one row<\/p>\n

IN \u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0ANY \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ALL<\/p>\n

<\/strong><\/p>\n

Multiple-column Subqueries<\/strong><\/p>\n

SELECT column, column, …<\/p>\n

FROM table<\/p>\n

WHERE (column, column, …) IN<\/p>\n

(SELECT column, column, …<\/p>\n

FROM table<\/p>\n

WHERE condition);<\/p>\n

Data Manipulation Statements (DML)<\/strong><\/p>\n

INSERT Statement(one row)<\/strong><\/p>\n

INSERT INTO table [ (column [,column…])]<\/p>\n

VALUES (value [,value…]);<\/p>\n

<\/strong><\/p>\n

INSERT Statement with Subquery<\/strong><\/p>\n

INSERT INTO table [ column(, column) ]<\/p>\n

subquery;<\/p>\n

<\/strong><\/p>\n

UPDATE Statement<\/strong><\/p>\n

UPDATE table<\/p>\n

SET column = value [, column = value,…]<\/p>\n

[WHERE condition];<\/p>\n

<\/strong><\/p>\n

Updating with Multiple-column Subquery<\/strong><\/p>\n

UPDATE table<\/p>\n

SET (column, column,…) =<\/p>\n

(SELECT column, column,…<\/p>\n

FROM table<\/p>\n

WHERE condition)<\/p>\n

WHERE condition;<\/p>\n

<\/strong><\/p>\n

Deleting Rows with DELETE Statement<\/strong><\/p>\n

DELETE [FROM] table<\/p>\n

[WHERE condition];<\/p>\n

<\/strong><\/p>\n

Deleting Rows Based on Another Table<\/strong><\/p>\n

DELETE FROM table<\/p>\n

WHERE column = (SELECT column<\/p>\n

FROM table<\/p>\n

WHERE condition);<\/p>\n

MERGE statement<\/strong><\/p>\n

MERGE INTO table_name table_aliase<\/p>\n

USING (table|view|sub_query) alias<\/p>\n

ON (join condition)<\/p>\n

WHEN MATCHED THEN<\/p>\n

UPDATE SET<\/p>\n

col1 = col1_val,<\/p>\n

col2 = col2_val<\/p>\n

WHEN NOT MATCHED THEN<\/p>\n

INSERT (column_list)<\/p>\n

VALUES (column_values);<\/p>\n

NOTE:<\/strong><\/p>\n

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<\/p>\n

<\/strong><\/p>\n

Transaction Control Statements (TCL)<\/strong><\/p>\n

COMMIT;<\/p>\n

SAVEPOINT name;<\/p>\n

ROLLBACK [TO SAVEPOINT name];<\/p>\n

<\/strong><\/p>\n

Displaying table structure<\/strong><\/p>\n

DESC[RIBE] tablename<\/p>\n

Data Definition Statemetns (DDL)<\/strong><\/p>\n

CREATE TABLE Statement<\/strong><\/p>\n

CREATE TABLE [schema.]table<\/p>\n

(column datatype [DEFAULT expr] [,…]);<\/p>\n

<\/strong><\/p>\n

CREATE TABLE Statement with Subquery<\/strong><\/p>\n

CREATE TABLE [schema.]table<\/p>\n

[(column, column…)]<\/p>\n

AS subquery;<\/p>\n

NOTE: <\/strong><\/p>\n

When a table is created using existing table except “NOT NULL” constraint, no other constraints are copied. If condition satisfied even rows are copied.<\/em><\/p>\n

<\/em><\/p>\n

<\/strong><\/p>\n

Datatype<\/strong><\/p>\n

VARCHAR2(size) \u00a0\u00a0 CHAR(size) \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NUMBER(p,s) \u00a0\u00a0\u00a0\u00a0\u00a0 DATE<\/p>\n

LONG \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0 CLOB \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RAW \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LONG RAW<\/p>\n

BLOB \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BFILE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ROWID<\/p>\n

<\/strong><\/p>\n

ALTER TABLE Statement <\/strong><\/p>\n

Adding columns<\/strong><\/p>\n

ALTER TABLE table_name<\/p>\n

ADD (column datatype [DEFAULT expr]<\/p>\n

[, column datatype]…);<\/p>\n

<\/strong><\/p>\n

Changing a column’s type, size and default value of a Table<\/strong><\/p>\n

ALTER TABLE table_name<\/p>\n

MODIFY (column datatype [DEFAULT expr]<\/p>\n

[, column datatype]…);<\/p>\n

RENAMING table column: <\/strong>Supported by 9.2 and higher version only <\/strong><\/p>\n

ALTER TABLE table_name<\/p>\n

RENAME column_name to new_column_name;<\/p>\n

<\/strong><\/p>\n

Dropping a column<\/strong><\/p>\n

ALTER TABLE table_name<\/p>\n

DROP (column);<\/p>\n

<\/strong><\/p>\n

Dropping a Table<\/strong><\/p>\n

DROP TABLE table_name;<\/p>\n

<\/strong><\/p>\n

Changing the Name of an Object<\/strong><\/p>\n

RENAME old_name TO new_name;<\/p>\n

<\/strong><\/p>\n

Trancating a Table<\/strong><\/p>\n

TRUNCATE TABLE table_name;<\/p>\n

<\/strong><\/p>\n

Adding Comments to a Table<\/strong><\/p>\n

COMMENT ON TABLE table_name | COLUMN table.column<\/p>\n

IS ‘text’;<\/p>\n

<\/strong><\/p>\n

Dropping a comment from a table<\/strong><\/p>\n

COMMENT ON TABLE table_name | COLUMN table.column IS ” ;<\/p>\n

<\/strong><\/p>\n

Data Dictionary<\/strong><\/p>\n

ALL_OBJECTS \u00a0\u00a0\u00a0\u00a0\u00a0 USER_OBJECTS<\/p>\n

ALL_TABLES \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 USER_TABLES<\/p>\n

ALL_CATALOG \u00a0\u00a0\u00a0\u00a0\u00a0 USER_CATALOG or CAT<\/p>\n

ALL_COL_COMMENTS USER_COL_COMMENTS<\/p>\n

ALL_TAB_COMMENTS USER_TAB_COMMENTS<\/p>\n

<\/strong><\/p>\n

Defining Constraints<\/strong><\/p>\n

CREATE TABLE [schema.]table<\/p>\n

column datatype [DEFAULT expr][NOT NULL]<\/p>\n

[column_constraint],…<\/p>\n

[table_constraint][,…]);<\/p>\n

<\/strong><\/p>\n

Column constraint level<\/strong><\/p>\n

column [CONSTRAINT constraint_name] constraint_type,<\/p>\n

<\/strong><\/p>\n

Constraint_type<\/strong><\/p>\n

PRIMARY KEY REFERENCES table(column) UNIQUE<\/p>\n

CHECK (condition)<\/p>\n

<\/strong><\/p>\n

Table constraint level<\/strong>(except NOT NULL)<\/p>\n

column,…,[CONSTRAINT constraint_name]<\/p>\n

constraint_type (column,…),<\/p>\n

<\/strong><\/p>\n

NOT NULL Constraint (Only Column Level)<\/strong><\/p>\n

CONSTRAINT table[_column…]_nn NOT NULL …<\/p>\n

<\/strong><\/p>\n

UNIQUE Key Constraint<\/strong><\/p>\n

CONSTRAINT table[_column..]_uk UNIQUE (column[,…])<\/p>\n

<\/strong><\/p>\n

PRIMARY Key Constraint<\/strong><\/p>\n

CONSTRAINT table[_column..]_pk PRIMARY (column[,…])<\/p>\n

<\/strong><\/p>\n

NOTE: <\/strong><\/p>\n

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.<\/em><\/p>\n

<\/strong><\/p>\n

FOREIGN Key Constraint<\/strong><\/p>\n

CONSTRAINT table[_column..]_fk<\/p>\n

FOREIGN KEY (column[,…])<\/p>\n

REFERENCES table (column[,…])[ON DELETE CASCADE]<\/p>\n

[ON DELETE SET NULL];<\/p>\n

<\/strong><\/p>\n

CHECK constraint<\/strong><\/p>\n

CONSTRAINT table[_column..]_ck CHECK (condition)<\/p>\n

<\/strong><\/p>\n

Adding a Constraint<\/strong>(except NOT NULL)<\/p>\n

ALTER TABLE table_name<\/p>\n

ADD [CONSTRAINT constraint_name ] type (column);<\/p>\n

<\/strong><\/p>\n

Adding a NOT NULL constraint<\/strong><\/p>\n

ALTER TABLE table_name<\/p>\n

MODIFY (column datatype [DEFAULT expr]<\/p>\n

[CONSTRAINT constraint_name_nn] NOT NULL);<\/p>\n

<\/strong><\/p>\n

Dropping a Constraint<\/strong><\/p>\n

ALTER TABLE table_name<\/p>\n

DROP CONSTRAINT constraint_name;<\/p>\n

ALTER TABLE table_name<\/p>\n

DROP PRIMARY KEY | UNIQUE (column) |<\/p>\n

CONSTRAINT constraint_name [CASCADE];<\/p>\n

<\/strong><\/p>\n

Disabling Constraints<\/strong><\/p>\n

ALTER TABLE table_name<\/p>\n

DISABLE CONSTRAINT constraint_name [CASCADE];<\/p>\n

<\/strong><\/p>\n

Enabling Constraints<\/strong><\/p>\n

ALTER TABLE table_name<\/p>\n

ENABLE CONSTRAINT constraint_name;<\/p>\n

NOTE:<\/strong><\/p>\n

A UNIQUE or PRIMARY KEY index is automatically created if you enable a UNIQUE key or PRIMARY KEY constraint.<\/em><\/p>\n

<\/strong><\/p>\n

Data Dictionary<\/strong><\/p>\n

ALL_CONSTRAINTS\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0 USER_CONSTRAINTS<\/p>\n

ALL_CONS_COLUMNS \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 USER_CONS_COLUMNS<\/p>\n

<\/strong><\/p>\n

View<\/strong><\/p>\n

Creating or Modifying a View<\/strong><\/p>\n

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view<\/p>\n

[(alias[, alias]…)]<\/p>\n

AS subquery<\/p>\n

[WITH CHECK OPTION [CONSTRAINT constraint_name]]<\/p>\n

[WITH READ ONLY [CONSTRAINT constraint_name]];<\/p>\n

Top-N Analysis<\/strong><\/p>\n

SELECT [column_list], ROWNUM<\/p>\n

FROM \u00a0(SELECT [column_list]<\/p>\n

FROM table_name<\/p>\n

ORDER BY Top-N_column)<\/p>\n

WHERE ROWNUM <= N;<\/p>\n

<\/em><\/p>\n

Removing a View<\/strong><\/p>\n

DROP VIEW view;<\/p>\n

SEQUENCE<\/strong><\/p>\n

CREATE SEQUENCE Statement<\/strong><\/p>\n

CREATE SEQUENCE sequence<\/p>\n

[INCREMENT BY n]<\/p>\n

[START WITH n]<\/p>\n

[{MAXVALUE n| NOMAXVALUE}]<\/p>\n

[{MINVALUE n| NOMINVALUE}]<\/p>\n

[{CYCLE | NOCYCLE}]<\/p>\n

[{CACHE [n|20]| NOCACHE}];<\/p>\n

<\/strong><\/p>\n

Pseudocolumns<\/strong><\/p>\n

sequence.NEXTVAL \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sequence.CURRVAL<\/p>\n

<\/strong><\/p>\n

Modifying a Sequence (No START WITH option)<\/strong><\/p>\n

ALTER SEQUENCE sequence<\/p>\n

[INCREMENT BY n]<\/p>\n

[{MAXVALUE n| NOMAXVALUE}]<\/p>\n

[{MINVALUE n| NOMINVALUE}]<\/p>\n

[{CYCLE | NOCYCLE}]<\/p>\n

[{CACHE [n|20]| NOCACHE}];<\/p>\n

<\/strong><\/p>\n

Removing a Sequence<\/strong><\/p>\n

DROP SEQUENCE sequence;<\/p>\n

<\/strong><\/p>\n

Index<\/strong><\/p>\n

Creating an Index<\/strong><\/p>\n

CREATE INDEX index<\/p>\n

ON TABLE (column[,column]…);<\/p>\n

<\/strong><\/p>\n

Removing an Index<\/strong><\/p>\n

DROP INDEX index;<\/p>\n

<\/strong><\/p>\n

Synoym<\/strong><\/p>\n

CREATE [PUBLIC] SYNONYM synonym<\/p>\n

FOR object;<\/p>\n

Removing Synonyms<\/strong><\/p>\n

DROP SYNONYM synonym;<\/p>\n

<\/strong><\/p>\n

Data Dictionary<\/strong><\/p>\n

ALL_VIEWS \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0 USER_VIEWS<\/p>\n

ALL_SEQUENCES \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0 USER_SEQUENCES<\/p>\n

ALL_INDEXES \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0 USER_INDEXES<\/p>\n

ALL_IND_COLUMNS \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0 USER_IND_COLUMNS<\/p>\n

<\/strong><\/p>\n

System Privileges (DBA)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 User System Privileges<\/strong><\/p>\n

CREATE USER \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CREATE SESION<\/p>\n

DROP USER \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CREATE TABLE<\/p>\n

DROP ANY TABLE \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CREATE SEQUENCE<\/p>\n

BACKUP ANY TABLE \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CREATE VIEW<\/p>\n

CREATE PROCEDURE<\/p>\n

Creating Users<\/strong><\/p>\n

CREATE USER user<\/p>\n

IDENTIFIED BY password;<\/p>\n

<\/strong><\/p>\n

Creating Roles<\/strong><\/p>\n

CREATE ROLE role;<\/p>\n

Granting System Privileges<\/strong><\/p>\n

GRANT privelges[,…] TO user[,…];<\/p>\n

GRANT privelges TO role;<\/p>\n

GRANT role TO user[,…];<\/p>\n

<\/strong><\/p>\n

Changing Password<\/strong><\/p>\n

ALTER USER user IDENTIFIED BY new_password;<\/p>\n

<\/strong><\/p>\n

Dropping Users<\/strong><\/p>\n

DROP USER user [CASCADE];<\/p>\n

<\/strong><\/p>\n

Dropping Roles<\/strong><\/p>\n

DROP ROLE role;<\/p>\n

<\/strong><\/p>\n

Object Privileges<\/strong><\/p>\n

Object \u00a0\u00a0\u00a0\u00a0 Table\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 View\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Sequence\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Procedure<\/p>\n

ALTER \u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0X \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0X<\/p>\n

DELETE \u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0X \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0X<\/p>\n

EXECUTE \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0X<\/p>\n

INDEX \u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0X<\/p>\n

INSERT \u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0X \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0X<\/p>\n

REFERENCES \u00a0\u00a0X<\/p>\n

SELECT\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 X \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0X \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0X<\/p>\n

UPDATE \u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0X \u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0X<\/p>\n

<\/strong><\/p>\n

Object Privileges<\/strong><\/p>\n

GRAND object_priv [(column)]<\/p>\n

ON object<\/p>\n

TO {user|role|PUBLIC}<\/p>\n

[WITH GRANT OPTION];<\/p>\n

<\/strong><\/p>\n

Revoking Object Privileges<\/strong><\/p>\n

REVOKE {privilege [,privilege…] | ALL}<\/p>\n

ON object<\/p>\n

FROM {user[,user…]|role|PUBLIC}<\/p>\n

[CASCADE CONSTRAINTS];<\/p>\n

<\/strong><\/p>\n

Data Dictionary<\/strong><\/p>\n

ROLE_SYS_PRIVS<\/p>\n

ROLE_TAB_PRIVS \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 USER_ROLE_PRIVS<\/p>\n

USER_TAB_PRIVS_MADE \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 USER_TAB_PRIVS_RECD<\/p>\n

USER_COL_PRIVS_MADE \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 USER_COL_PRIVS_RECD<\/p>\n

<\/strong><\/p>\n

Database Links<\/strong><\/p>\n

CREATE [PUBLIC] DATABASE LINK link_name<\/p>\n

[CONNECT TO user_name IDENTIFIED BY password]<\/p>\n

USING connection_string;<\/p>\n

<\/strong><\/p>\n

Single-Row Functions<\/strong><\/p>\n

Character Functions<\/strong><\/p>\n

LOWER(column|expression)<\/p>\n

UPPER(column|expression)<\/p>\n

INITCAP(column|expression)<\/p>\n

INSTR(column|expression,m)<\/p>\n

CONCAT(column1|expression1,column2|expression2}<\/p>\n

SUBSTR(column|expression,m,[n])<\/p>\n

LENGTH(column|expression)<\/p>\n

LPAD(column|expression,n,’string’)<\/p>\n

RPAD(column|expression,n,’string’)<\/p>\n

TRIM(‘character’ FROM column|expression)<\/p>\n

REPLACE(‘string1’, ‘string_to_replace’,[replacement_string])<\/p>\n

<\/strong><\/p>\n

Number Functions<\/strong><\/p>\n

MOD(m,n)<\/p>\n

ROUND(column|expression,n)<\/p>\n

TRUNC(column|expression,n)<\/p>\n

<\/strong><\/p>\n

Date Functions<\/strong><\/p>\n

MONTHS_BETWEEN(date1,date2)<\/p>\n

ADD_MONTHS(date,n)<\/p>\n

NEXT_DAY(date,’char’)<\/p>\n

LAST_DAY(date)<\/p>\n

ROUND(date[,’fmt’])<\/p>\n

TRUNC(date[,’fmt’])<\/p>\n

Arithmetic with Dates<\/em><\/p>\n