find and solve || findandsolve.com
Please wait.....

Introduction SQl(Structured Query Language)

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

SQLSQL *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  SQLSQl * 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 characterSQL *PLUS uses a dash (-) as a continuation character if the command is longer than one line
It cannot be abbreviatedIt 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

Sundar  Neupane

Sundar Neupane

I like working on projects with a team that cares about creating beautiful and usable interfaces.

Comments



Report Response