7. Basic Queries

Types of Queries

The tables in the following sections provide a functional summary of SQL statements and are divided into these categories:

Data Definition Language (DDL) Statements:

DDL statements let you to perform these tasks:

§ Create, alter, and drop schema objects

§ Grant and revoke privileges and roles          

§ Analyze information on a table, index, or cluster

§ Add comments to the data dictionary

The DDL statements are:

1.      ALTER ... (All statements beginning with ALTER)

2.      ANALYZE

3.      ASSOCIATE STATISTICS

4.      AUDIT

5.      COMMENT

6.      CREATE ... (All statements beginning with CREATE)

7.      DISASSOCIATE STATISTICS

8.      DROP ... (All statements beginning with DROP)

9.      FLASHBACK ... (All statements beginning with FLASHBACK)

10.  GRANT

11.  NOAUDIT

12.  PURGE

13.  RENAME

14.  REVOKE

15.  TRUNCATE: removes all records from a table

16.UNDROP

Data Manipulation Language (DML) Statements

Data manipulation language (DML) statements access and manipulate data in existing schema objects. The data manipulation language statements are:

1.      CALL

2.      DELETE

3.      EXPLAIN PLAN

4.      INSERT

5.      LOCK TABLE

6.      MERGE

7.      SELECT

8.UPDATE

Transaction Control Statements

§ Transaction control statements manage changes made by DML statements.

§ A transaction is a unit of work that is performed against a database.

§ Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.

Properties of Transactions(ACID Properties)

1.      Atomicity: ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure, and previous operations are rolled back to their former state.

2.      Consistency: ensures that the database properly changes states upon a successfully committed transaction.

3.      Isolation: enables transactions to operate independently of and transparent to each other.

4.      Durability: ensures that the result or effect of a committed transaction persists in case of a system failure.

The transaction control statements are:

1.      COMMIT: To save the changes.

2.      ROLLBACK: To rollback the changes.

3.      SAVEPOINT: creates points within groups of transactions in which to ROLLBACK

4.      SET TRANSACTION: Places a name on a transaction.

All transaction control statements, except certain forms of the COMMIT and ROLLBACK commands, are supported in PL/SQL.

For information on the restrictions, see COMMIT and ROLLBACK.













<<Prev                                                                                    Next>>