SQL (Structured Query Language) is the most common standardized database
language used to create, retrieve, access, modify, controland manage relational databases. SQL is a
querying language exclusively designed for controlling data and managing
databases effectively.
SQL was originally developed by IBM (International Business Machines
Corporation) in the 1970's for their DB2 RDBMS. The team of developers included
Andrew Richardson, Donald C. Messerly and Raymond F. Boyce. This first version
was initially known as SEQUEL. It was basically used to modify and query the
data stored in IBM's relational database product called System R. Later, SQL
version 3 was officially defined by the American National Standards Institute
(ANSI) in the ANSI SQL:1999 standard. Most existing DBMS's currently conform to
the earlier ANSI SQL92 standard.
Majority of database management systems implement these standards and add their
proprietary extensions. However, to be attuned with the ANSI standard, they all
maintain the key commands such as SELECT, UPDATE, DELETE, INSERT, WHERE.
SQL helps in the retrieval, insertion, updating, and deletion of data from
databases. A comprehensive database management system (DBMS) also comprises of
managerial and administrative tasks. Oracle produces a procedural version of SQL
which it calls PL/SQL. SQL is supported by every major database system in use
today, including MySQL, PostgreSQL, Berkeley DB, Oracle, DB2, Sybase, Informix,
and Microsoft SQL. SQL is often pronounced as "sequel".
Points to be Remembered about SQL
It is an abbreviation for Structured Query Language.
It helps in database access, control and manipulation.
It is an ANSI (American National Standards Institute) standard.
It is a nonprocedural database language.
It is used with relational database management system (RDBMS) like MS SQL
Server, IBM DB2, Oracle, MySQL, and Microsoft Access.
It is not case sensitive i.e. writing ‘SELECT’ or ‘select’ means the same
command.
Depending on the database used, semicolons have to be attached at the end of SQL
(Oracle requires semicolons whereas SQL Server do not)
Types of SQL statements
There are three primary types of SQL statements, which are as follows:
Data Definition Language (DDL) statements
Data definition language (DDL) statements helps the developer create, alter,
and drop schema objects. It can grant and revoke privileges and roles from other
database users. It examines information about a table, index, or cluster. It
creates auditing options. It adds comments to the data dictionary.
Examples of DDL statements
ALTER SEQUENCE, ALTER TABLE, COMMENT ON, CREATE ALIAS, CREATE, DISTINCT TYPE,
CREATE FUNCTION, CREATE INDEX, CREATE PROCEDURE, CREATE SCHEMA, CREATE SEQUENCE,
CREATE TABLE, CREATE TRIGGER, CREATE VIEW, DECLARE GLOBAL TEMPORARY TABLE, DROP
ALIAS, DROP DISTINCT TYPE, DROP FUNCTION, DROP INDEX, DROP PACKAGE, DROP
PROCEDURE, DROP SEQUENCE, DROP SCHEMA, DROP TABLE, DROP TRIGGER, DROP VIEW,
GRANT DISTINCT TYPE, GRANT FUNCTION, GRANT PACKAGE, GRANT PROCEDURE, GRANT
SEQUENCE, GRANT TABLE, LABEL ON, RENAME, REVOKE DISTINCT TYPE, REVOKE FUNCTION,
REVOKE PACKAGE, REVOKE PROCEDURE, REVOKE SEQUENCE, REVOKE TABLE
Data Manipulation Language (DML) statements
Data manipulation language (DML) statements access and manipulate data in the existing schema objects.
These statements do not implicitly commit the current transaction.
Examples of DML statements
CLOSE, COMMIT, DECLARE CURSOR, DELETE, FETCH, INSERT, LOCK TABLE, OPEN, REFRESH
TABLE, RELEASE SAVEPOINT, ROLLBACK, SAVEPOINT, SELECT INTO, SET variable,
UPDATE, VALUES INTO
Dynamic SQL statements
Dynamic SQL statements are used at runtime.
Examples of Dynamic statements
DESCRIBE, EXECUTE, EXECUTE IMMEDIATE, PREPARE
SELECT Statement
The SELECT statement is used to select data from a database. The result is
stored in a result table, called the result-set.
Syntax
SELECT * FROM <table-name>
OR
SELECT <column-name>, [<column-name>,…] FROM <table-name>
Where ‘*’ refers to selection of all the available columns within the table
<table-name>.
Example SELECT * FROM Customer, SELECT
CustomerID, CustomerName FROM Customer
WHERE Clause The WHERE clause is used to select only those records that match a specified
condition.
Syntax SELECT <column-name>, [<column-name>,…] FROM <table-name> WHERE
<column-name> <operator> <value> Example
SELECT CustomerID, CustomerName FROM Customer WHERE CustomerCity = ‘Newyork’
