SQL (Structured Query Language)

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’