Types of SQL Query - Practicise

DBMS

About Lesson

In SQL (Structured Query Language), queries are broadly categorized into three main types based on their functionality: Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL). Each type serves a specific purpose in managing and interacting with a relational database.

1. Data Definition Language:

DDL statements are used to define, modify, or delete the structure of database objects. These objects include tables, indexes, views, and schemas. DDL statements do not deal with the actual data but focus on the database’s structure.

Common DDL statements include:

CREATE:

Used to create a new database object, such as a table or index.

SQL

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

ALTER:

Used to modify the structure of an existing database object.

SQL

ALTER TABLE Employees
ADD COLUMN BirthDate DATE;

DROP:

Used to delete an existing database object.

SQL

DROP TABLE Employees;

2. Data Manipulation Language:

DML statements are used to manipulate the data stored in the database. These statements enable the insertion, updating, and deletion of records in tables.

Common DML statements include:

SELECT:

Used to retrieve data from one or more tables.

SQL

SELECT * FROM Employees WHERE DepartmentID = 1;

INSERT:

Used to add new records into a table.

SQL

INSERT INTO Employees (EmployeeID, FirstName, LastName)
VALUES (1, 'John', 'Doe');

UPDATE:

Used to modify existing records in a table.

SQL

UPDATE Employees
SET FirstName = 'Jane'
WHERE EmployeeID = 1;

DELETE:

Used to remove records from a table.

SQL

DELETE FROM Employees
WHERE EmployeeID = 1;

3. Data Control Language:

DCL statements are used to control access to data within the database. These statements manage permissions and privileges related to security and access control.

Common DCL statements include:

Used to create a new database object, such as a table or index.

GRANT:

Used to give specific privileges to users or roles.

SQL

GRANT SELECT, INSERT ON Employees TO HR_User;

REVOKE:

Used to take away specific privileges from users or roles.

SQL

REVOKE DELETE ON Employees FROM HR_User;

It’s important to note that some database systems may have additional or proprietary extensions to these standard SQL statements. Additionally, the specific syntax and functionality of SQL statements can vary between database management systems (DBMS) such as MySQL, PostgreSQL, SQL Server, and Oracle.