Are you curious about how to manage and manipulate data in a relational database?
SQL commands are the key! SQL(Structured Query Language) is a programming language that helps you query, update, delete, and manage data stored in a database.SQL is used by developers, data analysts, and database administrators to perform a variety of tasks.
In this blog, we will introduce you to the five types of SQL commands:
1.Data Definition Language (DDL)
2.Data Manipulation Language (DML)
3.Data Control Language (DCL)
4.Transaction Control Language (TCL)
5.Data Query Language (DQL)
By understanding these SQL commands, you'll be able to manage and manipulate data in any relational database. Let's dive in!
1.Data Definition Language (DDL)-
DDL commands are used to define and manage the structure of database objects, such as tables, views, and indexes. The most commonly used DDL commands are:
1.1 CREATE: used to create a new table, view, index, or other database object.
Syntax:
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint
);
Example:
CREATE TABLE Employee (
EmployeeID int NOT NULL PRIMARY KEY,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
BirthDate date NOT NULL,
HireDate date NOT NULL,
Salary decimal(10,2) NOT NULL,
DepartmentID int
);
CREATE TABLE Department (
DepartmentID int NOT NULL PRIMARY KEY,
DepartmentName varchar(50) NOT NULL,
ManagerID int NULL
);
1.2 ALTER: used to modify the structure of an existing database object, such as adding a column to a table or changing the data type of a column,removing column from table.
Add a new column:
Syntax:
ALTER TABLE Employee ADD column_name datatype;
Example:
ALTER TABLE Employee ADD Age INT;
Modify a column:
Syntax:
ALTER TABLE Employee ALTER COLUMN column_name datatype;
Example:
ALTER TABLE Employee ALTER COLUMN LastName VARCHAR(60);
Rename a column:
Syntax:
EXEC sp_rename 'Employee.old_column_name', 'new_column_name', 'COLUMN';
Example:
EXEC sp_rename 'Employee.LastName', 'Surname', 'COLUMN';
Drop a column:
Syntax:
ALTER TABLE Employee DROP COLUMN column_name;
Example:
ALTER TABLE Employee DROP COLUMN Age;
Change table name:
Syntax:
EXEC sp_rename 'Employee', 'new_table_name';
Example:
EXEC sp_rename 'Employee', 'Employees';
1.3 DROP: used to delete a table, view, index, or other database object.
Syntax:
DROP TABLE table_name;
Example:
DROP TABLE Employee;
1.4 TRUNCATE: used to delete all rows from a table, but leaves the table structure intact.
Syntax:
TRUNCATE TABLE table_name;
Example:
TRUNCATE TABLE Employee;
2.Data Manipulation Language (DML)
DML commands are used to manipulate data stored in a database. The most commonly used DML commands are:
2.1 INSERT: used to add new rows to a table.
Syntax-
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
Example-
INSERT INTO Employee (EmployeeID, FirstName, Surname, BirthDate, HireDate, Salary, DepartmentID)
VALUES
(1, 'John', 'Doe', '1990-01-01', '2020-01-01', 50000.00, 1),
(2, 'Jane', 'Doe', '1995-06-15', '2021-01-01', 55000.00, 2),
(3, 'Bob', 'Smith', '1985-02-28', '2010-05-01', 60000.00, 3),
(4, 'Alice', 'Johnson', '1992-11-20', '2022-02-01', 65000.00, 4),
(5, 'Tom', 'Lee', '1988-08-08', '2015-09-01', 70000.00, 5);
INSERT INTO Department (DepartmentID, DepartmentName, ManagerID)
VALUES (1, 'Sales', 3),
(2, 'Marketing', 5),
(3, 'IT', 2),
(4, 'Finance', 4),
(5, 'HR', 1);
2.2 UPDATE: used to modify existing data in a table.
Syntax-
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Example-
UPDATE Employee SET Salary = Salary + 1000 WHERE DepartmentID = 1;
2.3 DELETE: used to remove rows from a table.
Syntax-
DELETE FROM table_name WHERE condition;
Example-
DELETE FROM Employee WHERE EmployeeID = 1;
3.Data Control Language (DCL)
DCL commands are used to manage database security and access control. The most commonly used DCL commands are:
3.1 GRANT: used to give a user or group of users permission to access a database object, such as a table or view.
Syntax-
GRANT privilege_type ON object_name TO user_name;
Example-
GRANT SELECT, INSERT, UPDATE, DELETE ON Employee TO user1;
3.2 REVOKE: used to revoke permissions previously granted to a user or group of users.
Syntax-
REVOKE privilege_type ON object_name FROM user_name;
Example-
REVOKE SELECT, INSERT, UPDATE, DELETE ON Employee FROM user1;
4.Transaction Control Language (TCL)
TCL commands are used to manage transactions in a database. Transactions are used to group multiple SQL statements into a single unit of work that must be executed atomically (i.e., either all the statements succeed or none of them succeed). The most commonly used TCL commands are:
4.1 BEGIN TRANSACTION: Starts a new transaction.
BEGIN TRANSACTION;
4.2 COMMIT: used to commit a transaction, which makes all the changes made during the transaction permanent.
COMMIT;
4.3 ROLLBACK: used to roll back a transaction, which undoes all the changes made during the transaction.
ROLLBACK;
4.4 SAVEPOINT: used to create a savepoint within a transaction, which allows you to roll back to a specific point within the transaction.
5.Data Query Language (DQL)
DQL commands are used to retrieve data from one or more tables in a database. The most commonly used DQL command is:
5.1 SELECT: used to retrieve data from one or more tables in a database.
Syntax:
SELECT column1, column2, ... FROM table1
Example:
SELECT * FROM Employee;
SELECT FirstName, Surname, Salary FROM Employee WHERE DepartmentID = 1;
5.2 JOIN: Combines rows from two or more tables based on a related column.
Syntax:
SELECT column1, column2, ...
FROM table1
JOIN table2 ON table1.column = table2.column;
Example:
SELECT Employee.FirstName, Employee.Surname, DepartmentName
FROM Employee
INNER JOIN Department ON Employee.DepartmentID = Department.DepartmentID;
GROUP BY: Groups rows based on the values of one or more columns.
5.3 GROUP BY: Groups rows based on the values of one or more columns.
Syntax:
SELECT column1, AVG(column2)
FROM table1
GROUP BY column1;
Example:
SELECT DepartmentID, AVG(Salary) AS AverageSalary
FROM Employee
GROUP BY DepartmentID;
In this article, we have introduced the five types of SQL commands: DDL, DML, DCL, TCL, and DQL. Each type of command serves a specific purpose in managing and manipulating data stored in a relational database. By mastering these SQL commands, you can become proficient in managing and manipulating data in any relational database.
0 Comments