SQL commands are grouped into four 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
CREATE:- CREATE command is used to create Database objects like table,view,sequence, index etc.
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.
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.
DROP table STUDENT;
DROP View myview;
DROP SEQUENCE seq1;
TRUNCATE:-TRUNCATE command is used for deleting complete data from an existing table.
TRUNCATE table STUDENT;
TRUNCATE table STUDENT DROP STORAGE;
TRUNCATE table STUDENT REUSE STORAGE;
RENAME:-RENAME command is used to rename a table
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
UPDATE:-UPDATE command is used to update existing records in a table.
UPDATE student SET course='MCA' WHERE regno=101;
INSERT:-INSERT command is used to insert new records in a table.
DELETE:-DELETE command is used to delete records in a table.
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
COMMIT:-COMMIT command is used for modify/insert/update records makes permanent.
ROLLBACK:-Rollback is used to undo the changes made by any command but only before a commit is done
SAVEPOINT:-SAVEPOINT command is used to creates points within groups of transactions in which to ROLLBACK.
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:-All users access previleges to database.
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:-Withdraw users access previleges given by using the Grant command.
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.
SELECT * FROM emp;
SELECT empno,ename,job,sal from emp;
SELECT empno,ename,job,sal from emp WHERE sal>2000;