1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SQL Commands Basics

Discussion in 'SQL PL/SQL' started by orafan, Mar 20, 2009.

  1. orafan

    orafan Active Member

    Messages:
    23
    Likes Received:
    3
    Trophy Points:
    90
    SQL Commands Basics

    This document contains absolute SQL Basics for the beginners (like me!). SQL stands for Structured Query Language. SQL is a computer language used to store, retrieve and manipulate data stored in relational databases (RDBMS). Oracle is one such RDBMS. This article is for SQL Basics in Oracle, but it is mostly similar in other databases too.

    SQL was developed initially by IBM in the 1970s for use in System R. SQL standardization is through ISO and ANSI standard.

    SQL Queries are used to select or extract data from a database and Queries are of two types, DDL or Data Definition Language and DML or Data Manipulation Language.

    Data Manipulation Language

    SELECT STATEMENT

    SELECT STATEMENT is used to "select" or query data stored in tables, views etc.

    Syntax:
    Code (Text):

    SELECT *
      FROM tableName
     WHERE col1 = 'value1'
       AND col2 = 'value2';
     
    Example:
    Code (Text):

    SELECT col1, col5
      FROM example_table
     WHERE col3 = 'value3'
     ORDER BY col 5;

    SELECT * FROM scott.emp;

    SELECT * FROM scott.emp WHERE emp_no='98765' AND dept_no='1;
     
    INSERT STATEMENT

    Syntax

    Insert a single row into a table:
    Code (Text):

    INSERT INTO table_name VALUES (col1, col2, ...);
     
    Insert rows from one table into another:
    Code (Text):

    INSERT INTO table_name(col1, col2, ...) (SELECT 'value1', 'value2', ... from table_name);
     
    UPDATE STATEMENT

    Update the entire column of a table (all rows):
    Code (Text):

    UPDATE example_table SET column_value ='CV';
     
    Update the specific record of the table:
    Code (Text):

    UPDATE example_table SET column_value ='CV' WHERE column_id = 10;
     
    DELETE STATEMENT

    Delete all rows from a table:
    Code (Text):

    DELETE FROM example_table;
     
    Delete rows based on a condition:
    Code (Text):

    DELETE
      FROM example_table
     WHERE column_id = '10';
     
    [BREAK=Data Manipulation Language Contd]

    GRANT STATEMENT

    Syntax:

    Code (Text):
    grant privileges on object to user;
    Example

    REVOKE STATEMENT

    Syntax

    Example

    [BREAK=Data Definition Language]

    Create a table

    The syntax to create a table is:

    example:

    Rename a table

    Syntax

    Example:

    Add a new column

    Syntax

    Example:

    Modify a column

    syntax:

    Example:

    Drop a column

    syntax:

    Example:
    Indexes

    An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

    Create an index

    Syntax:

    Example:

    Rename an index

    Syntax:

    For example:

    Drop an index

    The syntax for dropping an index is:

    Example:

    Adding Constraints to a table:

    Syntax

    Example: Add a primary key constraint to a table:

    Sequences

    A sequence is an object that can generate numeric value in sequence. Sequences are typically used to generate values for primary keys.

    Create a sequence

    syntax:

    Example:

    Generating sequence values

    Use the NEXTVAL function to generate the next sequence value. CURRVAL will return the last generated value. Example:

    Alter a sequence

    Increment a sequence by a certain amount:

    Syntax:

    Example:

    Change the maximum value of a sequence:

    Syntax:

    Example:

    Set the sequence to cycle or not cycle:

    Syntax:

    Example:

    Views

    A view is different from a table in that it is actually just a SQL query which returns data when the view name is called. A View thus does not contain any data

    Syntax:

    Example

    Instead of CREATE if we use CREATE OR REPLACE it will replace the View if it already exists. Additionally one can create Materialized Views which is a view which actually stores the data from other tables. A materialized view can be refreshed from time to time.

    Synonyms

    A synonym is a database object used to assign an alias name to an object. This is usefull when working with several Schemas

    Syntax:

    Example:
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Very Good and useful article for beginners, orafan, but in future please use
    Code (Text):
     blocks as your article needed to be edited for that.