SQL Command
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 a table |
|
Drop |
to drop a table |
|
Rename |
to rename a table |
Table 1: DDL Commands
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 |
Table 2: DML Commands
TCL:
Transaction Control Language
These
commands are used to keep a check on other commands and their effect on the
database. These commands can terminate 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 the change |
|
save point |
to save temporarily |
Table 3: TCL Commands
DCL: Data
Control Language
Data control
language provides a command to grant and
take back authority.
|
Command |
Description |
|
Grant |
grant
permission of the right |
|
Revoke |
take
back permission |
Table 4 : DCL Commands
DQL: Data
Query Language
|
Command |
Description |
|
Select |
retrieve
records from one or more table |
Table 5: DQL Commands
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 used.
Syntax: create
database database-name;
Example: 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.
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: create
table Student (id int, name varchar, age int);
alter command:
Used for alteration of table structures. There are various uses
of alter command, such
as,
- to add a column to the existing table
- to rename any existing column
- to change the datatype of any column or to modify its size
- to drop a column
Using alter
command we can add a column to an existing table.
Syntax: alter table table-name add (column-name datatype);
Example: alter table Student add (address char);
To add a column with Default
Value
alter command
can add a new column to an existing table with default values.
Syntax: alter table table-name add (column-name1 datatype1 default data);
Example: alter table Student add (dob date default '1-Jan-99');
To Modify an existing Column: Used to modify data type of an
existing column.
Syntax: alter table table-name modify (column-name datatype);
Example: alter table Student modify (address varchar (30));
To Rename a column: Using alter command user can rename an existing column.
Syntax: alter table table-name rename old-column-name to column-name;
Example: alter table Student rename
address to Location;
To Drop a Column: Used to drop columns also.
Syntax: alter table table-name drop(column-name);
Example: alter table Student drop(address);
truncate command
The 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.
Syntax: truncate table table-name
Example: truncate table Student;
drop command
drop query
completely removes a table from the database.
This command will also destroy the table structure.
Syntax: drop table table-name
Example: drop table Student;
rename command
rename
command is used to rename a table.
Syntax: rename table old-table-name to new-table-name
Example: rename table Student to Student-record;
DML Commands
1) INSERT command
Insert
command is used to insert data into a table.
Syntax: INSERT into table-name values (data1, data2,)
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
2) UPDATE command
Update
command is used to update a row of a table.
Syntax: UPDATE table-name set column-name = value where condition;
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:
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 the condition to delete a particular
row.
Syntax: DELETE from table-name;
Example: DELETE from Student;
The above
command will delete all the records from Student table.
TCL command
Transaction
Control Language (TCL) commands are used to manage transactions in the 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 transaction into the database.
Syntax: commit;
Rollback command
This command
restores the database to last committed state. It is also used with savepoint command to jump to a save point in a transaction.
Syntax: rollback to save point-name;
Savepoint command
savepoint
command is used to temporarily save a transaction so that you can rollback to
that point whenever necessary.
Syntax: savepoint savepoint-name;
DCL command
System:
creating a session, table etc. are all
types of system privilege.
Object: any
command or query to work on tables comes under object privilege.
DCL defines
two commands,
Grant: Gives
user access privileges to the database.
Revoke: Take
back permissions from the user.
Example: grant create session to username;
|
S_id |
s_Name |
Age |
Address |
|
101 |
Adam |
15 |
Noida |
|
102 |
Alex |
18 |
Delhi |
|
103 |
Abhi |
17 |
Rohtak |
|
104 |
Ankit |
22 |
Panipat |
Table 6: DCL Command Example