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
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.
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
DML command
Data Manipulation Language (DML) statements are used for managing data in database. DML
commands are not auto-committed. It means changes made by DML command are not permanent
to database, it can be rolled back.
1) INSERT command
Insert command is used to insert data into a table. Following is its general syntax,
INSERT into table-name values(data1,data2,..)
Lets see an example,
Consider a table Student with following fields.
S_id S_Name age
INSERT into Student values(101,'Adam',15);
The above command will insert a record into Student table.
S_id S_Name age
101 Adam 15
Example to Insert NULL value to a column
Both the statements below will insert NULL value into age column of the Student table.
INSERT into Student(id,name) values(102,'Alex');
Or,
INSERT into Student values(102,'Alex',null);
The above command will insert only two column value other column is set to null.
S_id S_Name age
101 Adam 15
102 Alex
Example to Insert Default value to a column
INSERT into Student values(103,'Chris',default)
S_id S_Name age
101 Adam 15
102 Alex
103 chris 14
Suppose the age column of student table has default value of 14.
Also, if you run the below query, it will insert default value into the age column, whatever the default
value may be.
INSERT into Student values(103,'Chris')
2) UPDATE command
Update command is used to update a row of a table. Following is its general syntax,
UPDATE table-name set column-name = value where condition;
Lets see an example,
update Student set age=18 where s_id=102;
S_id S_Name age
101 Adam 15
102 Alex 18
103 chris 14
Example to Update multiple columns
UPDATE Student set s_name='Abhi',age=17 where s_id=103;
The above command will update two columns of a record.
S_id S_Name age
101 Adam 15
102 Alex 18
103 Abhi 17
3) Delete command
Delete command is used to delete data from a table. Delete command can also be used with
condition to delete a particular row. Following is its general syntax,
DELETE from table-name;
Example to Delete all Records from a Table
DELETE from Student;
The above command will delete all the records from Student table.
Example to Delete a particular Record from a Table
Consider the following Student table
S_id S_Name age
101 Adam 15
102 Alex 18
103 Abhi 17
DELETE from Student where s_id=103;
The above command will delete the record where s_id is 103 from Student table.
S_id S_Name age
101 Adam 15
102 Alex 18
SELECT Query
Select query is used to retrieve data from a tables. It is the most used SQL query. We can retrieve
complete tables, or partial by mentioning conditions using WHERE clause.
Syntax of SELECT Query
SELECT column-name1, column-name2, column-name3, column-nameN from table-name;
Example for SELECT Query
Conside the following Student table,
S_id S_Name age address
101 Adam 15 Noida
102 Alex 18 Delhi
103 Abhi 17 Rohtak
104 Ankit 22 Panipat
SELECT s_id, s_name, age from Student.
The above query will fetch information of s_id, s_name and age column from Student table
S_id S_Name age
101 Adam 15
102 Alex 18
103 Abhi 17
104 Ankit 22
Example to Select all Records from Table
A special character asterisk * is used to address all the data(belonging to all columns) in a
query. SELECTstatement uses * character to retrieve all records from a table.
SELECT * from student;
The above query will show all the records of Student table, that means it will show complete Student
table as result.
S_id S_Name age address
101 Adam 15 Noida
102 Alex 18 Delhi
103 Abhi 17 Rohtak
104 Ankit 22 Panipat
Example to Select particular Record based on Condition
SELECT * from Student WHERE s_name = 'Abhi';
103 Abhi 17 Rohtak
