DDL:
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
SQL queries to Truncate, Drop or Rename a Table
truncate command
truncate command removes all records from a table. But this command will not destroy the
table's structure. When we apply truncate command on a table its Primary key is initialized.
Following is its Syntax,
truncate table table-name
Here is an Example explaining it.
truncate table Student;
The above query will delete all the records of Student table.
truncate command is different from delete command. delete command will delete all the rows
from a table whereas truncate command re-initializes a table(like a newly created table).
For eg. If you have a table with 10 rows and an auto_increment primary key, if you use delete
command to delete all the rows, it will delete all the rows, but will not initialize the primary key,
hence if you will insert any row after using delete command, the auto_increment primary key
will start from 11. But in case of truncate command, primary key is re-initialized.
drop command
drop query completely removes a table from database. This command will also destroy the table
structure. Following is its Syntax,
drop table table-name
Here is an Example explaining it.
drop table Student;
The above query will delete the Student table completely. It can also be used on Databases. For
Example, to drop a database,
drop database Test;
The above query will drop a database named Test from the system.
rename query
rename command is used to rename a table. Following is its Syntax,
rename table old-table-name to new-table-name
Here is an Example explaining it.
rename table Student to Student-record;
The above query will rename Student table to Student-record.
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
101
S_Name
Adam
age
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
101
Adam
102
Alex
age
15
Example to Insert Default value to a column
INSERT into Student values(103,'Chris',default)
S_id
S_Name
101
Adam
102
Alex
103
chris
Suppose the age column of student table has default value of 14.
age
15
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
TCL command
Transaction Control Language(TCL) commands are used to manage transactions in
database.These are used to manage the changes made by DML statements. It also allows
statements to be grouped together into logical transactions.
Commit command
Commit command is used to permanently save any transaaction into database.
Following is Commit command's syntax,
commit;
Rollback command
This command restores the database to last commited state. It is also use with savepoint
command to jump to a savepoint in a transaction.
Following is Rollback command's syntax,
rollback to savepoint-name;
Savepoint command
savepoint command is used to temporarily save a transaction so that you can rollback to that
point whenever necessary.
Following is savepoint command's syntax,
savepoint savepoint-name;
Example of Savepoint and Rollback
Following is the class table,
ID
NAME
abhi
adam
alex
Lets use some SQL queries on the above table and see the results.
INSERT into class values(5,'Rahul');
commit;
UPDATE class set name='abhijit' where id='5';
savepoint A;
INSERT into class values(6,'Chris');
savepoint B;
INSERT into class values(7,'Bravo');
savepoint C;
SELECT * from class;
The resultant table will look like,
ID
NAME
abhi
adam
alex
abhijit
chris
bravo
Now rollback to savepoint B
rollback to B;
SELECT * from class;
The resultant table will look like
ID
NAME
abhi
adam
alex
abhijit
chris
Now rollback to savepoint A
rollback to A;
SELECT * from class;
The result table will look like
ID
NAME
abhi
adam
alex
abhijit
