• Loading
    • SQL Statements

      Learning Objectives
      After completing this session, you will be able to:
      • Write SQL Statements
      • Create tables
      • Maintain tables
      • INSERT into table
      • UPDATE rows from table
      • DELETE rows from table
      • Grant rights to Oracle database
      • Revoke rights to Oracle database



      Understand the features of SQL

      What is SQL?

      The Structured Query Language (SQL) is a set of statements with which all programs and users access data in an Oracle Database.

      Who Developed SQL and When?

      IBM developed the SQL, popularly pronounced as “SEQUEL” and implemented it in 1979. Actually, in doing so, IBM implemented the model on Relational Database Management Systems (RDBMS) developed by Dr. E.F. Codd. Today, SQL is the most popular RDBMS language.

      What can SQL do?

      With SQL*Plus, you can execute SQL commands and PL/SQL blocks, additionally you can perform the following tasks:
      • Enter, edit, store, retrieve and run SQL commands and PL/SQL blocks
      • Format, perform calculations on, store, print and create web output of query results
      • List column definitions for any table
      • Access and copy data between SQL databases
      • Send messages to and accept responses from an end user
      • Perform database administration




      SQL Statements
      SQL statements can be classified into following categories:

      Statements Description
      SELECT Retrieves data from the database.
      INSERT
      UPDATE
      DELETE
      MERGE
      Enter new rows, changes existing rows, delete unwanted rows. Collectively known as data manipulation language (DML)
      CREATE
      ALTER
      DROP
      RENAME
      TRUNCATE
      Set up, change, and remove data structure from the tables. Collectively known as data definition language (DDL)
      COMMIT
      ROLLBACK
      SAVEPOINT
      Manage the change made by DML statements. Collectively known as transaction control language(TCL)
      GRANT
      REVOKE
      Grant or remove access to both database and its objects. Collectively Known as data control language (DCL)




      Data Definition Language (DDL)

      Data Definition Language is a set of SQL commands used to create, modify and delete database structures.
      • The CREATE TABLE is used to create and define a new relational table
      • The DROP TABLE is used to delete a table and all data within the specified table
      • The ALTER TABLE is used to change an existing table definition
      • o Cannot delete an existing column
      • o Cannot change an existing table constraint definition
      • The TRUNCATE TABLE is used to delete all the rows within the specified table
      • Rename table is used to change the name of an existing tables to the specified name.


      CREATE TABLE
      : To create a table the user must have Create table privileges and a storage area in which to create the table
      Rules for naming a table:
      • The name must begin with a letter, A-Z or a-z.
      • May contain letters, numeric and special character _(underscore). The $ and # are also legal but discouraged;
      • The name is the same whether upper or lower case letters are used
      • It may be up to 30 char length
      • The name must not duplicate the name of any other object in your account.
      • The name must not be a sequel reserved word


      Syntax:
      Code:
      CREATE TABLE [SCHEMA.]Tablename (column_name data type [DEFAULT expr], column_name data type [default expr], …)
      In syntax
      • SCHEMA is same as the owner’s name.
      • Table name is the name of the table.
      • DEFAULT expr specifies a default value
      • Column_name is the name of the column.
      • Data type is the column’s data type and its length.


      Example:

      Code:
      CREATE TABLE product_dim 
      (product_id NUMBER(5), 
      product_name VARCHAR2(10))

      Create table with constraints:

      Constraint is a rule that restricts the values for a column on which it is defined. Constraints check data as it is entered or updated in the database and prevent data that does not conform to the constraint's rule from being entered.
      Constraints are classified into two types:
      1. Column level constraints.
      2. Table level constraints.


      NOT NULL:
      NOT NULL specifies that a column must have some value.

      NULL:
      NULL (default) allows NULL values in the column.

      DEFAULT:
      Specifies some default value if no value entered during INSERT

      UNIQUE:
      Specifies that column(s) must have unique values

      PRIMARY KEY:
      Specifies that column(s) must have unique values. Index is automatically generated for column

      FOREIGN KEY :
      Specifies that column(s) are defined primary key in another table. Used for referential uniqueness of parent table. Index is automatically generated for column.

      CHECK :
      Applies a condition to an input column value

      DISABLE:
      Suffix DISABLE to any other constraint to make Oracle ignore the constraint, the constraint will still be available to
      applications / tools and you can enable the constraint later if required


      Example:
      Code:
      CREATE TABLE DAILY_PIPELINE_LOANS_FACT 
      (LOANNO NUMBER(4) CONSTTRAINT DLY_PRIM PRIMARY KEY, 
      LOAN_STATUS VARCHAR2(10) CONSTRAINT LOAN_STATUS_CONS 
      CHECK(LOAN_STATUS=UPPER(LOAN_STATUS)), 
      LOAN_AMOUNT NUMBER(10) AMOUNT_CONS NOT NULL, 
      UPDATE_DATE DATE DEFAULT SYSDATE, 
      PRODUCT_KEY VARCHAR2(5) CONSTRAINT DLY_PRODUCT 
      FOREIGN KEY(PRODUCT_KEY) 
      REFERENCES PRODUCT_DIM(PRODUCT_KEY))

      ALTER TABLE:
      Alter table statement can be used to perform the followings task.
      • Add a new column.
      • Modify an existing Column.
      • Define a default value for the new column.
      • Drop a column


      Example:
      Add a new column lien_code to existing table.
      Code:
      ALTER TABLE DAILY_PIPELINE_LOAN_FACT ADD (lien_code varchar2(5));
      Change the existing columns data type and size.
      Code:
      ALTER TABLE DAILY_PIPELINE_LOAN_FACT MODIFY (lien_code Varchar2 (10));
      Dropping a column:
      Code:
      ALTER TABLE DAILY_PIPELINE_LOAN_FACT DROP COLUMN lien_code

      SET UNUSED OPTION:
      The set unused option marks one or more column as unused so that they can be dropped when required.
      Example:
      Code:
      ALTER TABLE DAILY_PIPELINE_LOAN_FACT SET UNUSED (lien_code)

      Dropping a table:
      While we dropped a table
      • All data and structure in the table deleted.
      • Any pending transactions are committed.
      • All indexes are dropped.
      • You can not rollback the drop table statement.
      • Only the creator of the table or the user with drop any table privilege can drop a table


      Example:

      Code:
      DROP TABLE DAILY_PIPELINE_LOAN_FACT

      Rename table: You can use RENAME TABLE keyword to change the name of the table. To change the name of the table, you must be owner of the table.
      Example:
      Code:
      RENAME TABLE Daily_pipeline_loan_fact to Dly_pipeline_loan_fct
      Truncating a table
      • Remove all rows from the table.
      • Release the storage space used by the table.
      • You can not rollback rows removal when using TRUNCATE.


      Example:
      Code:
      TRUNCATE TABLE daily_pipeline_loan_fact

      Data Manipulation Language (DML)


      A DML statement is executed When You
      Code:
      Add new rows to a table. 
      Modifying existing rows in a table. 
      Removing existing rows from a table.

      Adding a new row to a table:
      You can insert new rows in a table by using INSERT statement.
      Syntax:
      Code:
      INSERT INTO table_name [(Column),(column)….] 
      VALUES (value [, value]…);
      In the syntax:
      • Table_name is the name of the table
      • Column is the name of the column in the table to populate
      • Value is the corresponding value for the column.


      Example:

      Code:
      INSERT INTO DAILY_PIPELINE_LOAN_FACT 
      (LOANNO, 
      LOAN_STATUS, 
      LOAN_AMOUNT) 
      VALUES (1001, 
      ‘FUNDED’, 
      1000)

      If you are inserting a new row that contains values for each column, the column list is not required in the INSERT clause .However, if you do not use the column list, the value must be listed according to the default orders of the columns in the table, and a value must be provided for each column.

      Use and substitution in a SQL statement to prompt for values
      Code:
      INSERT INTO DAILY_PIPELINE_LOAN_FACT 
      (LOANNO, 
      LOAN_STATUS, 
      LOAN_AMOUNT) 
      VALUES (&LOANNO, 
      ‘& LOAN_STATUS’, 
      & LOAN_AMOUNT)
      Copying rows from another table
      Code:
      INSERT INTO COPY_DLY_PIPELINE_LOAN_FACT 
      SELECT * FROM DAILY_PIPELINE_LOAN_FACT

      DELETE Statement:
      You can remove existing rows from a table by using the delete statement.
      Code:
      DELETE [FROM] Table 
      [WHERE condition]
      Table is the name of the table.
      Condition identifies the rows to be deleted.

      Example:

      Code:
      DELETE FROM daily_pipeline_loan_fact WHERE loan_no=‘1001’ ;

      MERGE Statement
      :
      Using the MERGE statements you can update or insert a row conditionally into a table, thus avoiding multiple Update statements. The decision whether to insert or update into the target table is based on a condition in the ON clause.

      Because the MERGE command include both INSERT and UPDATE commands , you need to have both INSERT and UPDATE privilege on the target table and SELECT privilege on the source table .
      Syntax:
      Code:
      MERGE INTO Table_name AS table_alias 
      USING (table|view|subqueries) AS alias 
      ON (Join condition) 
      WHEN MATCHED THEN 
      UPDATE SET 
      col1=val1 
      col2=val2 
      WHEN NOT MATCHED THEN 
      INSERT (column_list) VALUES (Column_values);
      In
      In the Syntax:
      • INTO clause specifies the target table you are updating or inserting.
      • USING clause identifies the source of the data to be updated or inserted; can be a table, view, or subquery.
      • ON clause the condition upon which the MERGE operation either updates or inserts
      • WHEN MATCHED
      • WHEN NOT MATCHED Instruct the server how to respond to the result of the join condition.


      Example:

      Code:
      MERGE INTO copy_dly_pipeline_loan_fact AS c 
      USING daily_pipeline_loan_fact d 
      ON (d.loanno=c.loanno) 
      WHEN MATCHED THEN 
      UPDATE SET 
      c.laon_status=d.loan_status 
      c.loan_amount=d.loan_amount 
      c.update_date=d.update_date 
      c.product_key=d.product_key 
      WHEN NOT MATCHED THEN 
      INSERT VALUES(d. laon_status, 
      d.loan_amount, 
      d.update_date, 
      d.product_key)

      TCL Statements


      Transaction control language (TCL) manages the change made by DML statements.
      With commit or rollback statements, you can do the following:
      • Ensure data consistency
      • Preview data changes before making changes permanent
      • Group logical related transaction.
      • An automatic COMMIT occurs when:
      • o DDL statement is issued
      • o DCL statement is issued
      • o Normal exit from SQL*PLUS without issuing explicit COMMIT or ROLLBACK
      • An automatic ROLLBACK occurs under an abnormal termination of SQL *PLUS or due to system failure




      State of data before commit or Rollback
      • The previous state of data can be recovered.
      • The current user can review the results of the DML operations by using the SELECT statement.
      • Other users cannot view the results of the DML statements by the current user.
      • The affected rows are locked; other users cannot change the data within the affected row.


      State of data after commit
      • Data changes are made permanent in the database.
      • The previous state of data is permanently lost.
      • All users can view the results.
      • All save points are erased.


      State of data after Rollback

      • Discard all pending changes by suing the ROLLBACK statement.
      • Data changes are undone
      • Previous state of the data is released


      DCL Statements

      You can use GRANT or REVOKE to Grant or remove access to both database and its objects. Collectively known as data control language (DCL) .

      Granting system privileges

      GRANT create table, create view, Create sequence TO Scott;

      Role:
      A role is a named group of related privileges that can de grant to a user.

      Create a Role

      Code:
      CREATE ROLE manager;

      Grant privileges to a role

      Code:
      GRANT create table, create view, create sequence TO manager;

      Grant a role to users

      Code:
      GRANT manager to Scott, scott1;

      Granting object privileges

      Code:
      GRANT SELECT ON daily_pipeline_loan_fact TO scott1;
      Revoke object privileges: Use the revoke statement to revoke all the privileges granted to other user.
      Code:
      REVOKE SELECT ON daily_pipeline_loan_fact TO scott1;

      SELECT Statements


      The SELECT statement is an SQL statement that specifies which rows and columns to fetch from one or more tables or views. The SELECT statement is very helpful in finding filtered records from a database. If the database happens to be very large, the SELECT statement, used with the right parameters, can work wonders in finding the right information.
      The following are the features of a SELECT statement:
      • It is used for retrieving data from the database either selectively or collectively.
      • Column names can be specified or * may be specified for displaying all columns.
      • Calculated columns can be displayed.
      • Duplication can be removed.
      • Sorting of rows is possible.
      • Summary information can be displayed
      • SQL statement includes a:
        o SELECT clause, which lists the columns to be displayed
        o FROM clause, which specifies the table involved
        o [WHERE] clause, which limits the number of rows returned
        o [GROUP BY] clause, which groups the rows based on specified columns
        o [HAVING] clause, which specifies the table involved
        o [ORDER BY] clause, which sorts the rows based on specified columns


      Examples:

      To select all the columns:

      Code:
      SELECT * FROM daily_pipeline_loan_fact;

      To select specific columns:

      Code:
      SELECT loanno, loan_amount FROM daily_pipeline_loan_fact;

      The DISTINCT clause:

      The default output of a SELECT query is all rows including duplicate rows. The DISTINCT keyword is used to eliminate duplicate rows from the output.
      Example:
      Code:
      SELECT DISTINCT loan_status FROM daily_pipeline_loan_fact;
      The Where clause:
      • Applies conditions to filter rows
      • Appears immediately after the SELECT and FROM clause
      • Does not allow alias names
      • Works on row levels


      Syntax:
      Code:
      SELECT <COLUMN NAME(S)> 
      FROM <Table name> 
      WHERE <CONDITION>

      Example:

      Code:
      SELECT loanno 
      FROM daily_pipeline_loan_fact 
      WHERE loan amount >10000

      Summary

      • SELECT statement is used to retrieve data from the database either selectively or collectively.
      • The WHERE clause is used to apply conditions to filter rows.
      • Oracle provides DDL statements for creating, altering, dropping, and truncating tables.
      • Oracle provides DML statements to insert, update, and delete rows.
      • Constraint is a rule that restricts the values for a column on which it is defined.
      • Oracle provides TCL statements to ensure data consistency.
      • Grant and revoke privileges and role using DCL statement


      Test Your Understanding

      • What are the different types of SQL statement?
      • What is a constraint?
      • What is the difference between unique key and primary key?
      • What is the use of MERGE statement?
      • What is save point?
      • What is a role?
      • Which statements are auto commit?
      • When there will be an automatic rollback?
    • Enter your email address:

      Delivered by FeedBurner

    • Categories




    Disclaimer: Users of techforum4u.com are responsible for ensuring that any material they post (article, blog posts, images or other mulitimedia content) does not violate or infringe upon the copyright, patent, trademark, or any personal or proprietary rights of any third party, and is posted with the permission of the owner of such rights.Anyone who violates these rules may have their access privileges removed without warning.