Introduction
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 commands, we can query, filter, sort, join, group and modify the data in the database.
SQL Commands
SQL commands are categorized into 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) Command in SQL
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.
Find more about DDL Command in SQL: DDL Statements in SQL Server
CREATE Command in SQL
SQL Create the database or its object (ie table, index, view, function, etc.).
Syntax
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 Command in SQL
Drop command helps to delete the object from the database (ie table, index, view, function, etc.).
Syntax
DROP object object_name
Example
DROP TABLE Student;
Syntax
DROP DATABASE database_name
Example
DROP DATABASE Student_data;
ALTER Command in SQL
Alter command is helpful to change or modify the structure of the database or its object.
Syntax
ALTER TABLE table_name
ADD column_name datatype
Example
ALTER TABLE Student
ADD Total int;
Syntax
ALTER TABLE table_name
DROP COLUMN column_name
Example
ALTER TABLE Student
DROP COLUMN Mark;
1) SQL Server / MS Access
Syntax
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)
Syntax
ALTER TABLE table_name
MODIFY COLUMN column_name datatype
Example
ALTER TABLE Student
MODIFY COLUMN Total Varchar(255);
3) Oracle 10G and later
Syntax
ALTER TABLE table_name
MODIFY column_name datatype
Example
ALTER TABLE Student
MODIFY Total Varchar(255);
TRUNCATE Command in SQL
SQL Truncate command helps to remove all records from a table.
Syntax
TRUNCATE TABLE table_name
Example
TRUNCATE TABLE Student;
COMMENT Command in SQL
SQL Comment is helpful to add comments to the data dictionary."--" is used to comment on the notes.
Syntax
--(notes,examples)
Example
--select the student data
SELECT * FROM Student;
RENAME Command in SQL
SQL Rename is helpful to rename an object existing in the database.
1) PostgreSQL
Syntax
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) Command in SQL
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 a DQL query, we can get the data from the database to perform actions or operations like analyzing the data.
SELECT Command in SQL
SQL SELECT a query on a table or tables to view the temporary table output from the database.
Syntax
Select * from Table_Name;
Example
Select * from Student;
DML(Data Manipulation Language) Command in SQL
DML or Data Manipulation Language is to manipulate the data inside the database. With the help of DML commands, we can insert, delete, and change the data inside the database. Find more about DML Command in SQL: DML Command in SQL.
INSERT Command in SQL
SQL Insert command is helpful to insert the data into a table.
1) All the column names are mentioned in the insert statement.
Syntax
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 mention in the query, Values should be given in the order according to the column.
Syntax
INSERT INTO table_name
VALUES (value1, value2, value3, ...)
Example
INSERT INTO Student
VALUES (12345, "Sri", "Durga");
UPDATE Command in SQL
SQL Update command is helpful to update the existing data in a table.
Syntax
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition
Example
UPDATE Student
SET FirstName = "Navin" , LastName = "Kumar"
WHERE StudentId=12345;
DELETE Command in SQL
SQL 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 Command in SQL
SQL Lock command is helpful to lock the table to control concurrency.
Syntax
LOCK TABLE table-Name IN { SHARE | EXCLUSIVE } MODE
Example
LOCK TABLE Student IN SHARE MODE;
CALL Command in SQL
SQL Call command is helping to Call a PL/SQL or JAVA subprogram.
Syntax
EXEC SQL
CALL GETEMPSVR (2, NULL)
END-EXEC
EXPLAIN PLAN
It describes the access path to the data.
Syntax
EXPLAIN PLAN FOR
SELECT Column_name FROM table_name
Example
EXPLAIN PLAN FOR
SELECT last_name FROM Student;
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) Command in SQL
DCL or Data Control Language is to provide rights, permissions, and other controls of the database system. Find more about DCL Command: DCL Command in SQL.
GRANT Command in SQL
GRANT command is helpful to provide privileges to the database.
Syntax
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;
Syntax
GRANT EXECUTE ON [ PROCEDURE | FUNCTION ] object TO user
REVOKE Command in SQL
SQL Revoke command is to withdraw the user’s access privileges given by using the GRANT command.
Syntax
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;
Syntax
REVOKE EXECUTE ON [ PROCEDURE | FUNCTION ] object FROM user
TCL (Transaction Control Language) Command in SQL
TCL or Transaction Control Language happens to a transaction in the database.
COMMIT Command in SQL
SQL Commit command is to commit the Transaction after insert or delete in the database.
Syntax
Commit;
Example
DELETE from Student where Student_Id = 12345;
COMMIT;
ROLLBACK Command in SQL
SQL Rollback command is to rollback a transaction in case of any error occurs.
Syntax
Rollback;
Example
DELETE from Student where Student_Id = 12345;
ROLLBACK;
SAVEPOINT Command in SQL
SQL Savepoint command is to Set a savepoint within a transaction. If a transaction happens in big data, then checking and rollup can't do it with all the data, to rollback the small part of the data we use a savepoint query.
Syntax
SAVEPOINT savepoint_name
SET TRANSACTION Command in SQL
SQL Set command is to Specify the characteristics of the transaction.
Syntax
SET TRANSACTION Access NAME transaction_name
Summary
In this article, you have learned about the various SQL commands, and their different categories and simple SQL queries with examples.
Join the conversation! Your thoughts help the community grow.