SQL—Structured Query Language

Introduction

SQL stands for structured query language which comprises of fundamental building blocks of databases,it is used to defined the method used to create  and manipulate relational databases on all major platforms.

SQL was originated in 1970 in IBM San Jose laboratory where it was used as the relational database language for System-R DBMS.  Successful outcome of this project at 1979, made SQL becoming popular among many RDBMS vendors

SQL received certification from American National Standard Institute (ANSI) and from International Standard Organisation (ISO) in 1986. There are number of advantages in using a standardized relational language such as reduced training costs, application portability, application longality, reduced dependence on a single vendor and cross-system communication

There are three types of SQL commands

1. Data definition language (DDL) – these commands are issued to create, alter or delete tables.  Usually these commands can be executed by database administrator only to prevent any accidental or deliberate damage to the database.
2. Data manipulation language (DML) – these commands are used to insert, update, delete or query data from tables.
3. Data control language (DCL) – these commands are used to grant various access privileges of the database structure to users.  Only the database administrator can execute these commands.

DDL Command

It has three attributes : 1. Create 2. Alter 3. Delete

1. Create
   The create table command defines each column of the table uniquely. Each column has minimum of three attributes.
   • Name
   • Data type
   • Size(column width)
   The Structure of Create Table Command

   Column name	Data type	Size
   Reg_no	varchar2	10
   Name	char	30
   DOB	date
   Address	varchar2	50

   Example:
    
   CREATE TABLE Student

                (Reg_no varchar2(10),

                 Name char(30),

                 DOB date,

                 Address varchar2(50));

   The DROP Command

   Syntax:

   DROP TABLE <table_name>
    
2. Example:
   DROP TABLE Student;
   It will destroy the table and all data which will be recorded in it.
   The TRUNCATE Command

Syntax:

TRUNCATE TABLE <Table_name>

 

Example:

TRUNCATE TABLE Student;

The RENAME Command

 

Syntax:

RENAME <OldTableName> TO <NewTableName>

 

Example:

RENAME <Student> TO <Stu>

 

The old name table was Student now new name is the Stu.

The ALTER Table Command

 

By The use of ALTER TABLE Command we can modify our exiting table.

Adding New Columns

 

Syntax: 

ALTER TABLE <table_name>

         ADD (<NewColumnName> <Data_Type>(<size>),......n)

Example:

ALTER TABLE Student ADD (Age number(2), Marks number(3));

 

The Student table is already exist and then we added two more columns Age and Marks respectively, by the use of above command.

 

Dropping a Column from the Table

 

Syntax:

ALTER TABLE <table_name> DROP COLUMN <column_name>

 

Example:

ALTER TABLE Student DROP COLUMN Age;

 

This command will drop particular column 

 

Modifying Existing Table

 

Syntax:

ALTER TABLE <table_name> MODIFY (<column_name> <NewDataType>(<NewSize>))

 

Example:

ALTER TABLE Student MODIFY (Name Varchar2(40));

 

The Name column already exist in Student table, it was char and size 30, now it is modified by Varchar2 and size 40.

 

Restriction on the ALTER TABLE

 

Using the ALTER TABLE clause the following tasks cannot be performed.

• Change the name of the table
• Change the name of the column
• Decrease the size of a column if table data exists