SQL Commands-
The SQL commands are used for giving instructions to database for performing task. In database lots of records are saved successively. The user can communicate with database with use of commands. The commands includes statements and that all statements can executes on database for completing user expecting tasks. The commands perform different tasks like create table, update table, alter table, delete table. The SQL commands are divided into fallowing sections mainly.
- Data Definition Language(DDL)-Create, Drop, Alter, Truncate
- Data Manipulation Language(DML)-Insert, Update, Delete
- Data Query Language(DQL)-Select
- Data Control Language(DCL)-Grant, Revoke
- Transaction Control Language(TCL)-Commit, Rollback, Save point
We describe each and every command in detail as fallows-
1. Data Definition Language(DDL)-
The DDL commands start the database or table creation in SQL. The DDL commands are use create table, Alter table and drop table. the fallowing commands are used in DDL for execution task-
1. Create-
It is used for create a new table in database. The create table are mos frequently command for create table into database with their datatype.
Syntax-
Create Table Table_Name (Column_Name Datatype[,….]);
Example-
Create Table Emp(id varchar(20),name varchar(20), address varchar(20));
OUTPUT-
select * from Emp;
2. Drop-
That command is used for drop or delete whole table from database.
Syntax-
Drop Table Table_Name;
Example-
Drop Table Emp;
3. Alter
The alter command is used for changing table already created column name or their datatype size or adding new column in table. Their are 2 types of alter table-
- Add-
That command is used to add new column name in to table but the table is already created is required.
Syntax-
Alter Table table_name ADD column_name COLUMN-definition;
Example-
Alter Table Emp ADD(education VARCHAR2(20));
OUTPUT-
select * from Emp;
- Modify-
That command is used to modify already inserted column name or value changed.
Syntax-
Alter Table table_name MODIFY(column_definitions….);
Example-
Alter TABLE Emp MODIFY (name varchar(30));
4. Truncate-
That command is used to delete all the rows from the table and free space from table.
Syntax-
Truncate Table table_name;
Example-
Truncate Table Emp;
2. Data Manipulation Language(DML)-
If we want made changes in database the DML command is used. The DML command are basically used for modification of database. The DML command not commit automatically.
The DML command as fallows-
1. Insert-
If we want to insert records in database with use of front end then use Insert command for saving data in to database mainly.
Syntax-
Insert Into Table_name (col1, col2, col3,…. col N) values (value1, value2, value3, …. valueN);
Example-
Insert into Emp(id,name,address,education)values(101,Akshay,Kundal,MBA);
Insert into Emp(id,name,address,education)values(102,Ajit,Palus,MCA);
Insert into Emp(id,name,address,education)values(103,Prakash,Kolhapur,BCA);
OUTPUT-
Select * from Emp;
2. Update-
If we want change record value from table then use update statement. Modification made in table with use of update table.
Syntax-
update table_name set [column_name1= value1,…column_nameN = valueN] [where CONDITION];
Example-
update Emp set name=’kiran’ where id=’101′;
OUTPUT-
select * from Emp;
3. Delete-
If we want to delete a particular record from table then delete command is used.
Syntax-
delete from table_name [WHERE condition];
Example-
delete from Emp where id=“101”;
OUTPUT-
select * from Emp;
3. Data Query Language(DQL)-
DQL command is used for fetching data from table. If we want specific records or all records from table then we use the select command for data fetching.
In that only ‘select’ command is used-
1. Select-
The select command is used for fetching a particular record or all records from table and display it.
Syntax-
select expressions from table WHERE conditions;
Example-
If we want to display all records from Emp table then select is used as-
select *from Emp;
OUTPUT-
If we want to display a particular record from Emp table then select command is used as-
select name from Emp where id=’102′;
OUTPUT-
4. Data Control Language(DCL)-
DCL commands are used to give permission to user about database. In that 2 commands occur-
Grant-It is used to give user access.
Revoke- It is used to fallow back permission from user.
5.Transaction Control Language(TCL)-
The TCL commands are used for several important operation fallow on database. The TCL command used only with DML commands.
1. Commit-
Used for save all transaction successively.
2. Rollback-
That command is used for undo transaction that not be saved in database.
3. Save point-
That command is used for roll back several transaction not for all rollback.