SQL

SQL

SQL was developed by IBM in the mid-1970s. Structured Query Language is the standard command set used to communicate with the relational database management systems. All tasks related to relational data management creating tables, querying the database for information, modifying the data in the database, deleting them, granting access to users and son on can be done using SQL.

According to America National Standards Institute (ANSI), it is the standard language for relational database management systems. Some common relational database management systems that use SQL are : Oracle, Sybase, Microsoft SQL Server, Access, Ingres etc.

Advantages of SQL

1. It is a high level language that provides a greater degree of abstraction than procedural languages.
2. Application written in SQL can be easily ported from one system to another. Such a need would arise when a system needs upgrade or change.
3. It enables the end users and systems personnel to deal with a number of database management systems where it is available. Increased acceptance and availability of SQL are also in its favor.
4. SQL specifies what is required and not how it should be done.
5. The language while being simple and easy to learn can handle complex situations.
6. All SQL operations are performed at a set level. One select statement can retrieve multiple rows, one modify statement can modify multiple rows. This set at a time feature of the SQL makes it increasingly employed in language like COBOL.

 

SQL Data Types

Data types are classification of a particular type of information. SQL supports following data types :

1. Character (n)

This type represents a fixed length string of exactly ‘n’ characters where ‘n’ is greater than zero and should be an integer.

Example :

Name character (10)

2. Varchar (n) or character varying (n)

This data type represents a varying length string whose maximum length is ‘n’ characters.

Example :

Name varchar (n)

3. Numeric (p,q)

This type represents a decimal number ‘p’ and sign with assumed decimal point ‘q’ digits from the sign. Both ‘p’ and ‘q’ are integers.

Example :

Price number (6,2)

4. Integer

An integer represents a signed integer decimal or binary.

Example :

Roll_no integer (3)

5. Small int

Same as Integer except that precision must be smaller than INT precision in the same table.

Example : Roll_No small int (3)

6. Real, Double precision

Floating -point and double precision floating -point numbers with machine dependent precision.

7. Float (n)

A floating point number, with precision of at least n digits.

Example :

Rate float (5,2)

8. Date

This type has ten positions embedded in single quotes i.e. ‘DD-MM-YYYY’

Example:

’30-10-1985’ implies 3oth October 1985

9. Time

This data type has atleast 8 positions embedded in single quotes ’HH:MM:SS’.

Example:

’10:08:05 implies 10.08.05 A.M. and ‘22:08:05’ implies 10:08:05 PM.

10. Time stamp

It includes both Date and Time along with minimum 6 digits representing decimal fraction of seconds.

Example :

‘DD-MM-YYYY HH:MM:SS’

’31-05-1950 01:02:05 567892’.

SQL Command

SQL commands can be classified into the following categories:

1. Data Definition Language (DDL)

It allows to create databases, tables and indexes. It is used to create, alter and delete database objects. For instance, to create a Book table, we can use Create table command provided by SQL as follows:

Create Table Book

Id                               INTEGER

Title                           CHAR (30)

Author                       CHAR (20)

Publication                CHAR (30)

Subject                        (CHAR (20)

We specify the name by which the table should be referred and in this case its Book. The columns that form the table are specified within the brackets. Along with every column we specify the type and the size of data allowed for that column. For example the title can contain upto  a maximum of 30 characters whereas ID is an integer. Note that we have not specifies any length for the Id. For integers, the DBMS uses its own pre-specified length.

After the Create table statement, only an empty structure (called as template) of the table is created as shown below:

ID

Title

Author

Publication

Subject

2. Data Manipulation Language(DML)

The DML commands are used to manipulate the information in the tables like INSERT, UPDATE or DELETE commands.

3. Data query Language (DQL)

This is one  of the most commonly used SQL statements. This SQL statement enables the users to query one or more tables to get the information they want. SQL has only one data query statement ‘select’.

4. Data Control Language (DCL)

The data control language consists of commands that control the user access to the database objects. Various DCL commands are : Commit, Rollback, Save point, Grant, Revoke.

5. Data Administrative Statement (DAS)

These commands are basically AUDIT commands used to analyze the system performance. There are two commands START AUDIT an STOP AUDIT.

6. Transaction Control Statement (TCS)

These commands are used to control transaction like SET TRANSACTION, SAVEPOINT, COMMIT and ROLLBACK.