What Is SQL ?
SQL stands for “Structured Query language”. It’s a standard language for accessing and manipulating Databases. American National Standard Institute standardized SQL in 1986 and standardized in1987 by International Organization of Standardization. Through SQL you can Execute queries v/s Database. With the help of SQL you can insert data into the database table, or if you want to drop or delete any record from your table then you can perform these tasks easily with the help of simple Queries of SQL.
Some important commands of Structured Query language: and these are as follows.
DDL : extends for Data Definition Language. DDL Can’t be rollback. The commands are –
1 Create : this command is used to create the New table. The syntax is as follows.
Syntax:
Create table table_name
( column_name1 datatype(length/precision/date constraint constraint_name key_name,
Column_name2 datatype(length/precision/date constraint constraint_name key_name,
.
.
.
Column_n datatype(length/precision/date constraint constraint_name key_name);
Example :
Create table table1
(
Emp_id number(10) constraint pk primary key,
Emp_name Varchar2(20) not null ,
Salary number(10) constraint ch check(salary<=100000),
Address varchar2(20) constraint ad default ‘New_Delhi’,
Mgr_id number(10),
constraint mr primary key(emp_id,Mgr_id)
// if primary key already applied then you can’t apply the composite key
Dept_id number(10) constraint fk references (Emp_id)
);
Syntax : (i) Rename Old_table_name to New_table_name;
Example: Rename emp to emp001;
Syntax :
Drop table Table_name;
Example: Drop table emp;
Syntax:
(i) // to change any column_name.
Alter table table_name
Rename column old_column_name to new_column_name;
Example: Alter table emp
Rename column emp_id to empno;
(ii) //to add any column in the existing table.
Alter table table_name
Add column_name datatype(length/precision/date);
Example : Alter table emp
Add emp_name varchar2(20);
(iii) //for any modification at column level you can use this command.
Alter table table_name
Modify column_name (length/precision/Date);
Example : Alter Table emp
Modify qualification number(5); //if it has data in it then its size can increase only,you can’t decrease it’s size.
(iv) // to drop any column from your table.
Alter table table_name
Drop column column_name;
Example: Alter table emp
Drop column column_name;
(v) //to drop any constraint….
Alter Table emp
Drop constraint constraint_name;
Example: Alter table emp
Drop constraint pk;
5 Truncate : Truncate Command is used to remove all the data from your database table with the table structure.
Syntax:
Truncate table Table_name;
Example: Truncate table emp;
DML: DML Extends for Data manipulation language. These commands can be rollback.The commands are as follows-
(i) Insert : if we want to insert some data/records into database table. Then we can easily insert the data into the database table easily by using the following syntax.
Syntax: insert into table_name(column_1,column_2,column_3,……..column_n)
Values(values_0,value_1,value_2,…….,value_n);
(ii) Update: Update command is used to do some changes into the database table. The syntax is as follows-
Syntax: (a.)Update table_name
Set column_name=’String’/number;
(b.) Update table_name
Set column_name=COLUMN_NAME
Where column_name=CONDITION;
Syntax: (a) Delete table_name
//it will delete the entire record from your database table.
(b) Delete table_name
Where column_name=COLUMN_NAME;
(c) Delete table_name
Where column_name in (values1,2……n);
(d) Delete table_name
Where column_name not in (values 1,2….n);
DCL: extends for Data Control language, these are used to allow permission to the other users, schema, or server.
(i) Grant: grant is used to give the privilege to the other users.
Syntax: GRANT privilege_name
ON object_name
TO {user_name |PUBLIC |role_name}
[WITH GRANT OPTION];
(ii) Revoke: Revoke is used to remove all the privilege which was given through Grant.
Syntax: REVOKE privilege_name
ON object_name
FROM {user_name |PUBLIC |role_name};
TCL: TCL Extends for “Transaction Control Language”. TCL deals with the transaction within the database. These commands are applied only on DML commands. The TCL commands are as follows-
(i) Commit : commit is used to commit the last action.
Syntax: Commit;
(ii) Rollback : Rollback is used to undo the last action.
Syntax: Rollback;
(iii) Savepoint: this is used to save any point to rollback there.
Syntax:
SAVEPOINT SP1;
//Savepoint created.
DELETE FROM Student WHERE AGE = 20;
//deleted
SAVEPOINT SP2;
//Savepoint created.
Rollback to SP1;
DQL: DQL extends for “Data Query Language”. SELECT is the only command in DQL.
Syntex:
SELECT name,salary
from Table_name
where condition;
Example: SELECT name,salary
From Emp
Where salary<10000;
Difference between alias and Rename: Alias is a temporary changed name of column or query which is performed but if you talk about Rename is the permanently changed name of the column or query.
Comments
Post a Comment