SQL Sub Language
SQL commands are grouped into below major categories depending on their functionality
1) Data Definition Language (DDL)
2) Data Manipulation Language (DML)
3) Transaction Control Language (TCL)
4) Data Control Language (DCL)
5) Data Query Language (DQL)
Data Definition Language (DDL) :-DDL commands are used to create, alter, drop, rename of a database objects like table,view,sequence,index etc .These commands works on structure. Below are commands under this category
1) CREATE
2) ALTER
3) DROP
4) RENAME
5) TRUNCATE
CREATE:- CREATE command is used to create Database objects like table,view,sequence, index etc.
Example:- For example on create table command please click on Create Table Examples
Create table STUDENT (regno NUMBER,name VARCHAR2(20),dob DATE,course VARCHAR2(10));
ALTER:-ALTER command is used for changing the existing structure of Database objects.
Example:-
ALTER table STUDENT ADD (address VARCHAR2(50));
ALTER table STUDENT ADD (address1 VARCHAR2(50),address2 VARCHAR2(50));
ALTER table STUDENT ADD PRIMARY KEY(regno);
ALTER table STUDENT ADD CONSTRAINT pk_regno PRIMARY KEY(regno);
ALTER table STUDENT MODIFY (name VARCHAR2(30));
ALTER table STUDENT RENAME COLUMN address TO address3;
ALTER table STUDENT RENAME CONSTRAINT pk_regno TO pk_regno1;
ALTER table STUDENT ENABLE CONSTRAINT pk_regno1;
ALTER table STUDENT DISABLE CONSTRAINT pk_regno1;
ALTER table STUDENT DROP COLUMN address2;
ALTER table STUDENT DROP CONSTRAINT pk_regno1;
DROP:-DROP command is used to drop the Database objects.
Example:-
DROP table STUDENT;
DROP table STUDENT purge;
DROP view myview;
DROP SEQUENCE seq1;
DROP index idx1;
DROP function add_number;
TRUNCATE:-TRUNCATE command is used for deleting complete data from an existing table.
Example:-
TRUNCATE table STUDENT;
TRUNCATE table STUDENT DROP STORAGE;
TRUNCATE table STUDENT REUSE STORAGE;
RENAME:-RENAME command is used to rename a table
Example:-
RENAME STUDENT TO STUDENT1;
Data Manipulation Language (DML):-DML commands work on records in a table. These are basic operations we perform on data such as inserting new records, deleting unnecessary records, and updating existing records.Below are the commands are under this category
1) UPDATE
2) DELETE
3) INSERT
UPDATE:-UPDATE command is used to update existing records in a table.
Example:-
UPDATE student SET course='MCA' WHERE regno=101;
INSERT:-INSERT command is used to insert new records in a table.
Example:-
DELETE:-DELETE command is used to delete records in a table.
Example:-
DELETE FROM student WHERE regno=101;
Transaction Control Language (TCL):- The commands of SQL that are used to control the transactions made against the database.Below are the commands are under this category
1) COMMIT
2) ROLLBACK
3) SAVEPOINT
COMMIT:-COMMIT command is used for modify/insert/update records makes permanent.
Example:-
COMMIT;
ROLLBACK:-Rollback is used to undo the changes made by any command but only before a commit is done
Example:-
ROLLBACK;
SAVEPOINT:-SAVEPOINT command is used to creates points within groups of transactions in which to ROLLBACK.
Example:-
SAVEPOINT ABC;
Data Control Language (DCL):-These commands of SQL are used to give the permission or revoke the permission on System/User's objects to user/role in the database
GRANT:-Grant is used to provide the access of objects to users/roles.
Example:-
GRANT CREATE TABLE FROM SCOTT;
GRANT CREATE TABLE FROM SCOTT WITH ADMIN OPTION;
GRANT SELECT ON emp to HR;
GRANT SELECT ON emp to HR WITH GRANT OPTION;
REVOKE:-Revoke is used to withdraw the access from users/roles.
Example:-
REVOKE CREATE TABLE FROM SCOTT;
REVOKE SELECT ON emp FROM SCOTT;
Data Query Language (DCL):-These commands are used to select the data from database tables,view,synonyms and sequence etc.
SELECT:-SELECT command is used to select data from a database.
Example:-
SELECT * FROM emp;
SELECT empno,ename,job,sal from emp;
SELECT empno,ename,job,sal from emp WHERE sal>2000;
No comments:
Post a Comment