6.3 STRUCTURED QUERY LANGUAGE (SQL)

In the database management, it is highly necessary to have one consistent language in which users can express their operation requests at will. In database-speak, a request submitted to a database is referred to a query. Such a language used for user database query is defined as a query language. Among a variety of query languages, Structured Query Language (SQL) is the most widely used one [2, 3]. The formal pronunciation for SQL is "es queue el," though it is often pronounced as sequel. SQL was originally created by IBM, and it has some variants such as Oracle Corporation's PL/SQL or Sybase and Microsoft's Transact-SQL. SQL has become the data query standard which is widely adopted in various database management systems. ODBC defines a standard SQL grammars, which is based on the previous X/Open SQL CAE specification. Applications can submit SQL statements through ODBC. SQL can be classified into several major sub-languages: Data Query Language (DQL), Data Manipulation Language (DML), Data Definition Language (DDL), and Data Control Language (DCL). Distinguished from other procedural languages such as C or Pascal, SQL is a set-based programming language. Certain SQL variants such as PL/SQL are developed to tackle this by adding procedural elements into SQL while keeping SQL's original merits. Another approach is to embed SQL statements into the procedural language code so as to interact with the database. For instance, embedded SQL is supported by the Oracle pre-compilers. The Oracle pre-compilers interpret embedded SQL statements and translate them into statements that can be understood by procedural language compilers. Some commonly used commands used in Data Manipulation Language, Data Definition Language, and Data Control Language are introduced in the following.

• Data Manipulation Language (DML): DML is a subset of SQL used for querying a database as well as adding, updating, and deleting data. DML is used to retrieve, insert, and modify database information. These commands can be used by all database users during the routine operations of database.
  
  - SELECT: Specify a query as a description of the desired result set.
  - INSERT: Add a row to a table.
  - UPDATE: Change the data values in a table row.
  - DELETE: Remove rows from a table.
  - BEGIN WORK: Mark the start point of a database transaction.
  - COMMIT: Make the data changes that crop up in a transaction permanent.
  - ROLLBACK: Discard the data changes after the last COMMIT or ROLLBACK operation.

• Data Definition Language (DDL): DDL allows the user to define new tables and associated elements. DDL contains the commands used to create and destroy databases and database objects. The most fundamental DDL commands are CREATE and DROP. After the database structure is defined using DDL, database administrators and users can utilize the Data Manipulation Language (DML) to retrieve, insert, and modify the data contained within it. The Data Definition Language (DDL) commands are primarily used by database administrators for generating and eliminating databases or database objects. Below are the two basic DDL commands:
  
  - CREATE: Create an object in the database.
  - DROP: Delete an existing object in the database.

• Data Control Language (DCL): DCL is used to deal with data access authorization and user privilege management. Below are two of its commands:
  
  - GRANT: Offer the user privilege for performing certain database operations.
  - REVOKE: Cancel/restrict the user privilege for performing certain database operations.

SQL defines how to generate and manipulate relational databases on the major platforms including DB2, Ingres, Informix, InterBase, Microsoft SQL Server, MySQL, Oracle, SQLite, Sybase, and so forth. SQL can benefit the users at different levels such as application programmers, database administrators, management, and end users. SQL is able to provide an interface to the relational database, and all SQL statements are instructions to the database. SQL accomplishes all the database operations using a single language. As all major relational database management systems support SQL, the user can transfer the experiences and skills that they have learned from one database to another. In addition, all programs written in SQL can be ported from one database to another without much effort.