Structured Query Language (SQL) is a language that provides an interface to relational database systems. SQL was developed by IBM in the 1970s for use in System R, and is a de facto standard, as well as an
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.
Features of SQL
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
Features of SQL *Plus
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
SQL | SQL *PLUS | |
---|---|---|
SQL is a language for communicating with the Oracle Server to access data | SQL *PLUS recognize SQL statement and sends them to the server | |
SQL is based on American National Standards Institute (ANSI) standard SQL | SQl * PLUS is the Oracle proprietary interface for executing SQL statements | |
SQL manipulates data and table definitions in the database on one or more lines | TSQL *PLUS does not manipulation of value in the databases |
SQL is entered into the SQL buffer | SQL *PLUS is entered one line at a time, not stored in the SQL buffer |
---|---|
SQL does not have a continuation character | SQL *PLUS uses a dash (-) as a continuation character if the command is longer than one line |
It cannot be abbreviated | It can be abbreviated |
SQL uses a termination character to execute commands immediately | SQL *PLUS does not require termination characters; executes commands immediately |
SQL uses functions to perform some formatting | SQL *PLUS uses commands to format data |
---|
Rules for SQL
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:
AS ASC INTO ALTER ROWID INSERT VALUES PCTFREE VARCHAR2
BY NOT LIKE AUDIT SHARE MODIFY BETWEEN NOAUDIT WHENEVER
IN RAW LOCK CHECK START NOWAIT CLUSTER SESSION EXCLUSIVE
IS ROW LONG FLOAT TABLE NUMBER COMMENT SYSDATE IMMEDIATE
OF SET MODE GRANT UNION ONLINE COMPRESS SYNONYM INCREMENT
ON UID NULL GROUP WHERE OPTION CONNECT TRIGGER INTERSECT
OR CHAR ROWS INDEX ACCESS RENAME CURRENT VARCHAR IDENTIFIED
TO DATE SIZE LEVEL COLUNM REVOKE DECIMAL DISTINCT MAXEXTENTS
ADD DESC THEN MINUS CREATE ROWNUM DEFAULT DISTINCT MAXEXTENTS
ALL DROP USER ORDER DELETE SELECT INITIAL ROWLABEL PRIVILEGES
AND ELSE VIEW PRIOR EXISTS UNOQUE INTEGER SMALLINT SUCCESSFUL
ANY FILE WITH PUBLIC HAVING UPDATE OFFLINE VALIDATE
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
SQL Delimiters
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)
Components of SQL
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.
Examples of DDL, DML and DCL commands
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:
⦁ INSERT Insert data into a table
⦁ 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:
SELECT Retrieve data from the a database
Comments