In this article, you will learn the SQL command categories and their sub-categories. SQL is an open-source data management system. The SQL query is used to retrieve and manipulate the data from the table. With the help of SQL command we can query, filter, sort, join, group and modify the data in the database.
SQL Commands
SQL commands are categorized into below 5 categories:
- DDL – Data Definition Language
- DQL – Data Query Language
- DML – Data Manipulation Language
- DCL – Data Control Language
- TCL - Transaction Control Language
DDL (Data Definition Language)
DDL or Data definition language is actually the definition or description of the database structure or schema, it won't change the data inside the database. Create, modify, and delete the database structures, but not the data. Only These commands are not done by all the users, who have access to the database via an application.
CREATE
Create the database or its object (ie table, index, view, function etc.).
CREATE DATABASE databasename
Example
CREATE DATABASE Student_data;
Syntax
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);
Example
CREATE TABLE Student (
StudendId int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
Mark int
);
DROP
Drop command helps to delete the object from the database (ie table, index, view, function, etc.).
DROP object object_name
Example
DROP TABLE Student;
DROP DATABASE database_name
Example
DROP DATABASE Student_data;
ALTER
Alter command is helpful to change or modify the structure of the database or its object.
ALTER TABLE table_name
ADD column_name datatype
Example
ALTER TABLE Student
ADD Total int;
ALTER TABLE table_name
DROP COLUMN column_name
Example
ALTER TABLE Student
DROP COLUMN Mark;
1) SQL Server / MS Access
ALTER TABLE table_name
ALTER COLUMN column_name datatype
Example
ALTER TABLE Student
ALTER COLUMN Total Varchar(255);
2) My SQL / Oracle (prior version 10G)
ALTER TABLE table_name
MODIFY COLUMN column_name datatype
Example
ALTER TABLE Student
MODIFY COLUMN Total Varchar(255);
3) Oracle 10G and later
ALTER TABLE table_name
MODIFY column_name datatype
Example
ALTER TABLE Student
MODIFY Total Varchar(255);
TRUNCATE
Truncate command helps to remove all records from a table.
TRUNCATE TABLE table_name
Example
TRUNCATE TABLE Student;
COMMENT
Comment is helpful to add comments to the data dictionary."--" is used to comment the notes.
--(notes,examples)
Example
--select the student data
SELECT * FROM Student;
RENAME
Rename is helpful to rename an object existing in the database.
1) PostgreSQL
ALTER DATABASE "Old_DatabaseName" RENAME TO "New_DatabaseName";
Example
ALTER DATABASE "Student_data" RENAME TO "Employee_data";
2) MySQL
Example
SQL Command for Dump copy
mysqldump -u username -p"password" -R testDb > testDb.sql;
SQL Command for creating new DB
mysqladmin -u username -p"password" create testDB1;
SQL Command for Import
mysql -u username -p"password" testDb1 < testDb.sql;
Also for Unix, database names are case-sensitive
3) SQL Server
In SQL Server we can rename the database through server application, by right click the existing database and renaming it.
DQL (Data Query Language)
DQL or data query language is to perform the query on the data inside the schema or object (ie table, index, view, function, etc). With the help of DQL query we can get the data from the database to perform actions or operations like analysing the data.
SELECT
Select query on a table or tables to view the temporary table output from the database.
Select * from Table_Name;
Example
Select * from Student;
DML(Data Manipulation Language)
DML or Data Manipulation Language is to manipulate the data inside the database. With the help of DML commands, we can insert, delete, change the data inside the database.
INSERT
Insert command is helpful to insert the data into a table.
1) All the column names are mentioned in the insert statement.
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...)
Example
INSERT INTO Student (StudendId, FirstName, LastName)
VALUES (12345, "Sri", "Durga");
2) Column names do not need to mentioned in the query, Values should be given in the order according to the column.
INSERT INTO table_name
VALUES (value1, value2, value3, ...)
Example
INSERT INTO Student
VALUES (12345, "Anna", "Marry");
UPDATE
Update command is helpful to update the existing data in a table.
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition
Example
UPDATE Student
SET FirstName = "Peter" , LastName = "Scott"
WHERE StudentId=12345;
DELETE
Delete command helps to delete the records from a database table.
Syntax
DELETE FROM table_name WHERE condition;
Example
DELETE FROM Student WHERE StudentId=12345;
LOCK
Lock command is helpful to lock the table to control concurrency.
LOCK TABLE table-Name IN { SHARE | EXCLUSIVE } MODE
Example
LOCK TABLE Student IN SHARE MODE;
CALL
Call command is helping to Call a PL/SQL or JAVA subprogram.
EXEC SQL
CALL GETEMPSVR (2, NULL)
END-EXEC
EXPLAIN PLAN
It describes the access path to the data.
EXPLAIN PLAN FOR
SELECT Column_name FROM table_name
Example
EXPLAIN PLAN FOR
SELECT last_name FROM Student;
SQL
This query explanation will be stored in the PLAN_TABLE table. We can then select the execution plan to review the queries.
DCL (Data Control Language)
DCL or Data Control Language is to provide rights, permissions, and other controls of the database system.
GRANT
GRANT command is helpful to provide privileges to the database.
GRANT privileges_names ON object TO user
Example
GRANT SELECT, INSERT, DELETE, UPDATE ON Users TO 'Name'@'localhost;
GRANT ALL ON Users TO 'Name'@'localhost;
GRANT SELECT ON Users TO '*'@'localhost;
GRANT EXECUTE ON [ PROCEDURE | FUNCTION ] object TO user
REVOKE
Revoke command is to withdraw the user’s access privileges given by using the GRANT command.
REVOKE privileges ON object FROM user
Example
REVOKE SELECT, INSERT, DELETE, UPDATE ON Users TO 'Name'@'localhost;
REVOKE ALL ON Users TO 'Name'@'localhost;
REVOKE SELECT ON Users TO '*'@'localhost;
REVOKE EXECUTE ON [ PROCEDURE | FUNCTION ] object FROM user
TCL (Transaction Control Language)
TCL or Transaction Control Language happens to a transaction in the database.
COMMIT
Commit command is to commit Transaction after insert or delete in the database.
Commit;
Example
DELETE from Student where Student_Id = 12345;
COMMIT;
ROLLBACK
Rollback command is to rollback a transaction in case of any error occurs.
Rollback;
Example
DELETE from Student where Student_Id = 12345;
ROLLBACK;
SAVEPOINT
Savepoint command is to Set a savepoint within a transaction. If transaction happens in big data, then for checking and rollup can't do it with all the data, to rollback the small part of the data we use savepoint query.
SAVEPOINT savepoint_name
SET TRANSACTION
Set command is to Specify the characteristics of the transaction.
SET TRANSACTION Access NAME transaction_name