Introduction to SQL
Structure Query Language(SQL) is a programming language used for storing and managing data in RDBMS. SQL was the first commercial language introduced for E.F Codd's Relational model. Today almost all RDBMS(MySql, Oracle, Infomix, Sybase, MS Access) uses SQL as the standard database language. SQL is used to perform all type of data operations in RDBMS.
SQL Command
SQL defines following data languages to manipulate data of RDBMS.
DDL : Data Definition Language
All DDL commands are auto-committed. That means it saves all the changes permanently in the database.
Command Description
create to create new table
or database
alter for alteration
truncate delete data from
table
drop to drop a table
rename to rename a table
DML : Data Manipulation Language
DML commands are not auto-committed. It means changes are not permanent to database, they can be rolled back.
Command Description
insert to insert a new row
update to update existing
row
delete to delete a row
merge merging two rows or
two tables
TCL : Transaction Control Language
These commands are to keep a check on other commands and their affect on the database. These commands can annul changes made by other commands by rolling back to original state. It can also make changes permanent.
Command Description
commit to permanently save
rollback to undo change
savepoint to save temporarily
DCL : Data Control Language
Data control language provides command to grant and take back authority.
Command Description
grant grant permission of
right
revoke take back permission.
DQL : Data Query Language
Command Description
select retrieve records from
one or more table
1)Create command
create is a DDL command used to create a table or a database.
Creating a Database
To create a database in RDBMS, create command is uses. Following is the Syntax :
create database database-name;
Example for Creating Database
create database Test;
The above command will create a database named Test.
Creating a Table
create command is also used to create a table. We can specify names and datatypes of various columns along.Following is the Syntax,
create table table-name
{
column-name1 datatype1,
column-name2 datatype2,
column-name3 datatype3,
column-name4 datatype4
};
create table command will tell the database system to create a new table with given table name and column information.
Example for creating Table
create table Student(id int, name varchar, age int);
The above command will create a new table Student in database system with 3 columns, namely id, name and age.
2) alter command
alter command is used for alteration of table structures. There are various uses of alter command, such as,
to add a column to existing table
to rename any existing column
to change datatype of any column or to modify its size.
alter is also used to drop a column.
To Add Column to existing Table
Using alter command we can add a column to an existing table. Following is the Syntax,
alter table table-name add(column-name datatype);
Here is an Example for this,
alter table Student add(address char);
The above command will add a new column address to the Student table
To Add Multiple Column to existing Table
Using alter command we can even add multiple columns to an existing table. Following is the Syntax,
alter table table-name add(column-name1 datatype1, column-name2 datatype2, column-name3 datatype3);
Here is an Example for this,
alter table Student add(father-name varchar(60), mother-name varchar(60), dob date);
The above command will add three new columns to the Student table
To Add column with Default Value
alter command can add a new column to an existing table with default values. Following is the Syntax,
alter table table-name add(column-name1 datatype1 default data);
Here is an Example for this,
alter table Student add(dob date default '1-Jan-99');
The above command will add a new column with default value to the Student table
To Modify an existing Column
alter command is used to modify data type of an existing column . Following is the Syntax,
alter table table-name modify(column-name datatype);
Here is an Example for this,
alter table Student modify(address varchar(30));
The above command will modify address column of the Student table
To Rename a column
Using alter command you can rename an existing column. Following is the Syntax,
alter table table-name rename old-column-name to column-name;
Here is an Example for this,
alter table Student rename address to Location;
The above command will rename address column to Location.
To Drop a Column
alter command is also used to drop columns also. Following is the Syntax,
alter table table-name drop(column-name);
Here is an Example for this,
alter table Student drop(address);
The above command will drop address column from the Student table