The name SQL pronounced as “ess-cue-ell” or ‘sequel’ is the abbreviation for structured query language. The SQL consists of a set of facilities for defining, accessing and managing relational databases. All tasks related to relational data management-creating tables, querying the database, deleting, granting access to users etc., can be done using SQL. It has been accepted as an American standard by American National Standards Institute (ANSI) and is a Federal Information Processing Standard (FIPS). It is also an international standard recognized by the ISO. The first commercial DBMS that supported SQL was Oracle in 1979. SQL statements can be invoked either interactively in a terminal session or by embedding them in application programs.
1. Characteristics of SQL
The following are the important characteristics of SQL.
- SQL is extremely flexible.
- SQL uses a free form syntax that gives the user the ability to structure SQL statements in a way best suited.
- It is a free formated language, e., there is no need to start SQL statements in a particular column or to be finished in a single line.
- It has relatively few commands.
- It is a non-procedural language.
2. Advantages of SQL
The advantages of SQL are as follows:
- SQL is a high level language that provides a greater degree of abstraction than procedural The programmer has to specify what data is needed but need not to specify, how to retrieve it.
- SQL is a unified language. The same language can be used to define data structures, querying data, control access to the data, insert, delete and modify occurrences of the data and so on.
- All the programs written in SQL are portable, thus they can be moved from one database to another with very little Such porting could be required when DBMS needs to be upgraded or changed.
- The language is simple and easy to It can handle complex situations very efficiently.
- The language has sound theoretical base and there is no ambiguity about the way a query will interpret the data and produce the Thus the results to be expected are well defined.
- SQL processes sets-of-records rather than just one record-at-a time. This set-at-a time feature of the SQL makes it more powerful.
- SQL as a language is independent of the way it is implemented This is because SQL specifies what is required and not how it should be done.
- SQL enables its users to deal with a number of database management systems where it is available.
3. Parts (Components) of SQL Language
The SQL language is mainly divided into FOUR major parts. The four parts are further divided into subparts. The major parts and subparts are as follows:
3.1. Data-Definition Language (DDL)
The SQL DDL provide commands for defining the relations, deleting the relations and modifying the existing relation schemas.
- View Definition Language (VDL) : The SQL DDL provide commands for defining and dropping the views.
- Integrity : The SQL DDL provide commands for specifying integrity constraints that must be satisfied by the data stored in the database.
- Authorization : The SQL DDL provide commands for specifying access rights to the relations and views.
3.2. Data Manipulation Language (DML)
The SQL DML provides a query language. This query language is based on relational algebra and tuple relational calculus. This contain commands to insert tuples into the database, to delete tuples from the database and to modify/update tuples in the database.
3.3. Data Control Language or Transaction Control Language (DCL or TCL)
The SQL DCL provide commands that help the DBA to control the database such as commands to grant or revoke privileges to access the database and to store or remove transactions that would affect the database.
3.4. Embedded SQL and Dynamic SQL
- Embedded SQL defines the way the SQL statements can be embedded within general purpose programming languages like C, C++, Cobol, Pascal The language in which SQL queries are embedded is referred to as a host language. The SQL queries embedded in the host language constitute embedded SQL.
- Dynamic SQL allows programs to construct and submit SQL queries at run time.
To show the working of DML, DDL and DCL commands, the company database is used. The relational schema is shown in Figure 7.1 with descriptions and primary key attributes underlined. The corresponding tables are shown in Figure 7.2 and Figure 7.3, where DID is the foreign key in Emp table.
4. Basic Data Types
The SQL supports a variety of data types as shown in Table 7.1.
Source: Gupta Satinder Bal, Mittal Aditya (2017), Introduction to Basic Database Management System, 2nd Edition-University Science Press (2017)