SQL*PLUS COMMANDS



     @                        @@                     /

     ACCEPT                   APPEND                 BREAK

     BTITLE                   CHANGE                 CLEAR

     COLUMN                   COMPUTE                CONNECT

    COPY                     DEFINE                 DEL

    DESCRIBE                 DISCONNECT             EDIT

    EXECUTE                  EXIT                   GET

    HELP                     HOST                   INPUT

    LIST                     PAUSE                  PRINT

    PROMPT                   REMARK                 RUN

    RUNFORM                  SAVE                   SET

    SHOW                     SPOOL                  SQLPLUS

    START                    TIMING                 TTITLE

    UNDEFINE                 VARIABLE               WHENEVER OSERROR

    WHENEVER SQLERROR



PL/SQL COMMANDS (Statements)



    CLOSE Statement          EXIT Statement         FETCH Statement

    GOTO Statement           IF Statement           LOOP Statement

    NULL Statement           OPEN Statement         RAISE Statement

    RETURN Statement



SQL COMMANDS



The SQL commands are divided into these categories:



    * Data Definition Language commands

    * Data Manipulation Language commands

    * Transaction Control commands

    * Session Control commands

    * System Control commands



SQL Data Definition Language commands include the following:



    ALTER CLUSTER            ALTER SEQUENCE         COMMENT

    ALTER DATABASE           ALTER SNAPSHOT         CREATE CLUSTER

    ALTER FUNCTION           ALTER SNAPSHOT LOG     CREATE CONTROLFILE

    ALTER INDEX              ALTER TABLE            CREATE DATABASE

    ALTER PACKAGE            ALTER TABLESPACE       CREATE DATABASE LINK

    ALTER PROCEDURE          ALTER TRIGGER          CREATE FUNCTION

    ALTER PROFILE            ALTER USER             CREATE INDEX

    ALTER RESOURCE COST      ALTER VIEW             CREATE PACKAGE

    ALTER ROLE               ANALYZE                CREATE PACKAGE BODY

    ALTER ROLLBACK SEGMENT   AUDIT                  CREATE PROCEDURE

    CREATE PROFILE           DROP ROLE

    CREATE ROLE              DROP ROLLBACK SEGMENT

    CREATE ROLLBACK SEGMENT  DROP SEQUENCE

    CREATE SCHEMA            DROP SNAPSHOT

    CREATE SEQUENCE          DROP SNAPSHOT LOG

    CREATE SNAPSHOT          DROP SYNONYM

    CREATE SNAPSHOT LOG      DROP TABLE

    CREATE SYNONYM           DROP TABLESPACE

    CREATE TABLE             DROP TRIGGER

    CREATE TABLESPACE        DROP USER

    CREATE TRIGGER           DROP VIEW

    CREATE USER              GRANT

    CREATE VIEW              NOAUDIT

    DROP CLUSTER             RENAME

    DROP DATABASE LINK       REVOKE

    DROP FUNCTION            TRUNCATE

    DROP INDEX               UPDATE

    DROP PROCEDURE

    DROP PROFILE



SQL Data Manipulation Language commands include the following:



    DELETE

    EXPLAIN PLAN

    INSERT

    LOCK TABLE

    SELECT



SQL Transaction Control commands include the following:



    COMMIT

    ROLLBACK

    SAVEPOINT

    SET TRANSACTION



SQL Session Control commands include the following:



    ALTER SESSION

    SET ROLE



SQL System Control command (only one command):



    ALTER SYSTEM

Insert Command



INSERT INTO t/v_name (column, column,...)    --(column list) optional

    VALUES(value, value,...);



 INSERT INTO t/v_name (column, column,...) SELECT...;



 INSERT adds rows to the table or view specified.  Names in the column

 list may be in any order.  Unlisted columns are set to NULL; you must

 list any column created NOT NULL, and supply a value for it.



 VALUES inserts each item in its list of values into the corresponding

 column in the list of columns. The datatypes of each pair of items in

 the two lists must be compatible or convertible. CHAR and DATE values

 must be enclosed in "single quotes".



 Use a subquery to INSERT rows from another table.  SELECT must return

 a value for each column listed.



Update Command 



UPDATE table/view_name SET

     column_name = sql_expression                       --SQL option

     column_name = (one_value_SELECT_statement)         --SQL option

     (column_name, column_name,...) = SELECT_statement  --SQL option

   WHERE_clause;



 UPDATE replaces the values in rows of one or more specified columns

 with the values from a SQL expression or a SELECT statement. SELECT

 must return at least one row, and every column to the left of the =

 sign.  SELECT may not contain an INTO clause.  Use the WHERE clause

 to specify conditions rows must meet in order to be updated; if you

 omit the WHERE clause, all rows are updated.

 DELETE FROM table_or_view  WHERE clause;                   





 DELETE removes one or more rows from the table or view you specify.

 The WHERE clause specifies the rows to remove.  To remove all rows,

 omit the WHERE clause.





    SELECT command



PURPOSE:

    To retrieve data from one or more tables, views, or snapshots.



SYNTAX:

SELECT [DISTINCT | ALL] { *

                        | { [schema.]{table | view | snapshot}.*

                          | expr [c_alias] }

                       [, { [schema.]{table | view | snapshot}.*

                          | expr [c_alias] } ] ... }

    FROM [schema.]{table | view | snapshot}[@dblink] [t_alias]

      [, [schema.]{table | view | snapshot}[@dblink] [t_alias] ] ...

    [WHERE condition ]

    [ [START WITH condition] CONNECT BY condition]

    [GROUP BY expr [, expr] ... [HAVING condition] ]

    [{UNION | UNION ALL | INTERSECT | MINUS} SELECT command ]

    [ORDER BY {expr|position} [ASC | DESC]

           [, {expr|position} [ASC | DESC]] ...]

    [FOR UPDATE [OF [[schema.]{table | view}.]column

                 [, [[schema.]{table | view}.]column] ...] [NOWAIT] ]



where:



DISTINCT

    returns only one copy of each set of duplicate rows selected.

    Duplicate rows are those with matching values for each expression in

    the select list.



ALL

    returns all rows selected, including all copies of duplicates.



    The default is ALL.



*

    selects all columns from all tables, views, or snapshots listed in

    the FROM clause.



table.*

view.*

snapshot.*

    selects all columns from the specified table, view, or snapshot.

    You can use the schema qualifier to select from a table, view, or

    snapshot in a schema other than your own.



    If you are using Trusted ORACLE, the * does not select the ROWLABEL

    column.  To select this column, you must explicitly specify it in

    the select list.



expr

    selects an expression, usually based on columns values, from one of

    the tables, views, or snapshots in the FROM clause.  A column name

    in this list can only contain be qualified with schema if the table,

    view, or snapshot containing the column is qualified with schema in

    the FROM clause.



c_alias

    provides a different name for the column expression and causes the

    alias to be used in the column heading.  A column alias does not

    affect the actual name of the column.  Column aliases cannot be

    referenced elsewhere in a query.



schema

    is the schema containing the selected table, view, or snapshot.  If

    you omit schema, ORACLE assumes the table, view, or snapshot is in

    your own schema.



table

view

snapshot

    is the name of a table, view, or snapshot from which data is

    selected.



dblink

    is complete or partial name for a database link to a remote database

    where the table, view, or snapshot is located.  Note that this

    database need not be an ORACLE7 database.



    If you omit dblink, ORACLE assumes that the table, view, or snapshot

    is on the local database.



t_alias

    provides a different name for the table, view, or snapshot for the

    purpose of evaluating the query and is most often used in a

    correlated query.  Other references to the table, view, or snapshot

    throughout the query must refer to the alias.



WHERE

    restricts the rows selected to those for which the condition is

    TRUE.  If you omit this clause, ORACLE returns all rows from the

    tables, views, or snapshots in the FROM clause.



START WITH

CONNECT BY

    returns rows in a hierarchical order.



GROUP BY

    groups the selected rows based on the value of expr for each row and

    returns a single row of summary information for each group.



HAVING

    restricts the groups of rows returned to those groups for which the

    specified condition is TRUE.  If you omit this clause, ORACLE

    returns summary rows for all groups.



UNION

UNION ALL

INTERSECT

MINUS

    combines the rows returned by two SELECT statement using a set

    operation.



ORDER BY

    orders rows returned by the statement.

            expr

                   orders rows based on their value for expr.  The

                   expression is based on columns in the select list or

                   columns in the tables, views, or snapshots in the

                   FROM clause.