Skip to main content

7. Basic Queries

Image
By


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>>









Labels:

Comments

Post a Comment

Popular posts from this blog

5. INDEX

By
§ Indexes are special lookup tables that the database search engine can use to speed up data retrieval. § An index can be created in a table to find data more quickly and efficiently. § An index helps speed up SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements. § Indexes can be created or dropped with no effect on the data. § Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). Clustered indexes § Uses physical memory. § Easy to retrieve data. § Data is in stored form. Non-clustered indexes § No use of physical memory is needed. § More complicated, data retrieval is not that easy. § We can too createuniqueindex . Duplicate values are not allowed . CREATEINDEX index_name ON table_name ( column_name ) CREATEINDEX index_name ON table_name ( column_name ) CREATEINDEX Pindex Difference between clustered index and no...
Post a Comment
Read more

10. SQL Aliases

By
§ SQL aliases are used to temporarily rename a table or a column heading. § Basically aliases are created to make column names more readable. Syntax for Columns SELECT column_name AS alias_name FROM table_name Syntax for Tables SELECT column_name ( s ) FROM table_name AS alias_name ; Here is a SQL statement with alias § Example SELECT o . OrderID , o . OrderDate , c . CustomerName FROM Customers AS c , Orders AS o WHERE c . CustomerName = ’Around the Horn’ AND c . CustomerID = o . CustomerID ; The same SQL statement without aliases: § Example SELECT Orders . OrderID , Orders . OrderDate , Customers . CustomerName FROM Customers , Orders WHERE Customers . CustomerName = "Around the Horn" AND Customers . CustomerID = Orders . CustomerID ; <<Prev                                               ...
Post a Comment
Read more

6. VIEW

By
§ A view is a virtual table. § Database views are created using the CREATE VIEW statement. § Views can be created from a single table, multiple tables, or another view. § When we update a view it will update underline base table. CREATEVIEW view_name AS SELECT column1 , column2 ..... FROM table_name WHERE [condition] Example : CREATEVIEW CUSTOMERS_VIEW AS SELECT name , age FROM   CUSTOMERS SELECT * FROM CUSTOMERS_VIEW                        Advantages § Reduce complexity of database schema. § Mechanism to implement row and column level security. § Represent aggregate data and hide detailed data. Limitations § Cannot pass parameter to view. § Rules and default cannot be associated with the view. § Order by clause is invalid in a view unless TOP or XML is also specified. § Cannot be created on the bases of temporary tables. Indexed View § Create u...
Post a Comment
Read more