Introduction SQl(Structured Query Language)

ISO and ANSI standard SQL is often pronounced SEQUEL. 

In common usage SQL also encompasses DML (Data Manipulation Language), for INSERTs, UPDATEs, DElETEs and DDL (Data Definition Language), used for creation and modifying tables and other database structures.

The development of SQL is governed by standards. The American National Standards Institute (ANSI) is an organization that approves certain standards in many different industries. SQl has been deemed the standard language in relational database communication, originally approved in 1986 based on IBM's implementation. In 1987, the International Standards Organization (ISO) accepted the ANSL SQL Standard as the international standard. The standard was revised again in 1992 and was called SQL-92. The newest standard is now called SQL-99 it is also referred to as SQL3. SQL3 support object extensions and are partially implemented in Oracle8 and 9.

SQL has been a command language for communication with the Oracle 9i Server from any tool or application Oracle SQL contains many extensions. When an SQL statement is entered, it is stored in a part of memory called the SQL buffer and remains there until a new SQL statement  is entered.

1. SQL can be used by a range of users, including those with little or no programming experience

2. It is a non procedural language

3. It reduces the amount of time required for creating and maintaining systems

4. It is an English-like language

1. SQL *Plus accepts ad hoc entry of statement

2. It accepts SQL input from files

3. It provides a line editor for modifying SQL statements

4. It controls environmental settings

5. It formats query results into basis reports

6. It accesses local and remote databases

1. SQL starts with a verb (i.e. a SQL action word). Example: SELECT  statements. This verb have additional adjectives. Example: FROM

2. Each verb is followed by number of clauses. Example: FROM, WHERE, HAVING

3. A space separates clauses, Example: DROP TABLE EMP;

4. A comma (,) separates parameters without a clause

5. A';' is used to end SQL statements

6. Statements may be split across lines but keywords may not

7. Lexical units such as identifiers, operator names, literals are separated by one or more spaces or other delimiters that will not be confused with the lexical unit

8. Reserved words cannot be used as identifiers unless enclosed with double quotes. Reserved words are:

9. Identifiers can contain up to 30 characters and must start with an alphabetic character

10. Character and data literals must be enclosed within single quotes

11. Numeric literals can be represented by simple values such as 0.32, -34, 01991, and so on, scientific notation as 2E5 meaning 2*10 to the power                       of 5=200,000.

12. Comments may be enclosed between /* and*/ symbols and may be multi line. Single line comments may be prefixed with a - symbol

Delimiters are symbols or compound symbols, which have a special meaning within 

SQL and PL/SQL statements.

+Addition                          " Quote identifier

- Subtraction                      : Host varible

* Multiplication                  ** Exponential

/ Division  <>                     = = Relational

=>< Relational                   <=>= Relational

() Expression or list             := Assignment

; Terminator                        => Association

% Attribute indicator Concatenation

, Item separator                            <<  Label

. Component selector                    >> Label

@ Remote access indicator           -- Comment

' Character string delimiter            /**/ Comment (multi line)

1.DDL (Data Definition Language) - It is a set of SQL commands used to create, modify and delete database structures but not data. These commands are normally not used by a general user, who should be accessing the database via an application. They are normally used by the DBA to a limited extent, a database designer or application developer. These statement are immediate i.e. they are not susceptible to ROLLBACK commands. It should also be noted that if several DML statements for example updates are executed the issuing any DDL command would COMMIT all the update as every DDL command implicity issues a COMMIT command to the database. Anybody using DDL must have the CREATE object privilege and a Tablespace area in which to create objects.

2.DML (Data Munipulation Language) - It is the area of SQL that allows changing data within the database.

3.DCL (Data Control Language) - It is the component of SQL statement that control access ti data and to the database. Occasionallu DCL statements are grouped with DML statements.

4.DQL (Data Query Language) - It is the component of SQL statement that allows getting data from the database and imposing ordering upon it. In 

includes the SELECT statement. This command is the heart of SQL. It allows getting the data of out of the database perform operation with it. When a SELECT is fired against a table or tables the result is compiled into a further temporary table, which is displayed or perhaps received by the program i.e. front-end. 

DDL : Data Defination Language statements

Examples:

⦁ CREATE To create objects in the database

⦁ ALTER Alters the structure of the database

⦁ DROP Delete objects from the database

⦁ TRUNCATE Remove all records from a table, including all spaces allocated for the records are removed

⦁ COMMENT Add comments to the data dictionary

⦁ GRANT Gives user's access privileges to database

⦁ REVOKE Withdraw access privileges given with the GRANT command

DML: Data Manipulation Languages statements

Examples:

⦁ UPDATE  Updates existing data within a table

⦁ DELETE Deletes all records from a table, the space for the records remain 

⦁ CALL Call a PL/SQL or Java subprogram

⦁ EXPLAIN PLAN Explain access path to data

⦁ LOCK TABLE Control concurrency

DCL: Data Control Language statements

Examples:

⦁ COMMIT Save work done

⦁ SAVEPOINT Identify a point in a transaction to which you can later roll back

⦁ ROLLBACK Restore database to original since the last COMMIT

⦁ SET TRANSACTION Change transaction options like what rollback segment to use 

⦁ GRANT / REVOKE Grant or take back permissions to or from the oracle users 

DQL: Data Query Language statement

Examples: