SQL statement categories (DDL, DML, DQL, DCL) - Step-by-Step Execution

Concept Flow - SQL statement categories (DDL, DML, DQL, DCL)

Start

↓

Identify SQL Statement

↓

DDL: Create, Alter, Drop

↓

DML: Insert, Update, Delete

↓

DQL: Select

↓

DCL: Grant, Revoke

↓

Unknown Category

This flow shows how to categorize an SQL statement by checking if it changes structure, data, queries data, or manages permissions.

Execution Sample

SQL

CREATE TABLE students (id INT, name VARCHAR(50));
INSERT INTO students VALUES (1, 'Alice');
SELECT * FROM students;
GRANT SELECT ON students TO user1;

This sample shows one statement from each SQL category: DDL, DML, DQL, and DCL.

Execution Table

Step	SQL Statement	Category	Reason
1	CREATE TABLE students (id INT, name VARCHAR(50));	DDL	Changes database structure by creating a table
2	INSERT INTO students VALUES (1, 'Alice');	DML	Changes data by adding a new row
3	SELECT * FROM students;	DQL	Queries data without changing it
4	GRANT SELECT ON students TO user1;	DCL	Manages permissions by granting access
5	END	N/A	No more statements to categorize

💡 All statements categorized; process ends.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	Final
Current Statement	None	CREATE TABLE ...	INSERT INTO ...	SELECT ...	GRANT ...	None
Category	None	DDL	DML	DQL	DCL	None

Key Moments - 3 Insights

Why is SELECT considered DQL and not DML?

SELECT only reads data without changing it, so it fits DQL (Data Query Language). DML changes data, but SELECT does not modify anything, as shown in execution_table step 3.

Can CREATE TABLE be DML since it adds something?

No, CREATE TABLE changes the structure of the database, not the data inside it. This is why it is DDL, as seen in execution_table step 1.

What makes GRANT part of DCL?

GRANT controls who can do what in the database, managing permissions. This is the core of DCL, shown in execution_table step 4.

Visual Quiz - 3 Questions

Look at the execution_table, what category is the statement 'INSERT INTO students VALUES (1, 'Alice');'?

A DML

B DDL

C DQL

D DCL

💡 Hint

Check execution_table row 2 under Category column.

At which step does the SQL statement change database structure?

A Step 3

B Step 1

C Step 2

D Step 4

💡 Hint

Look at execution_table Reason column for structure changes.

If a statement manages user permissions, which category does it belong to?

A DML

B DQL

C DCL

D DDL

💡 Hint

Refer to execution_table step 4 and variable_tracker Category after 4.

Concept Snapshot

SQL statements are grouped into four categories:
- DDL: Data Definition Language (structure changes like CREATE, ALTER)
- DML: Data Manipulation Language (data changes like INSERT, UPDATE)
- DQL: Data Query Language (data reading like SELECT)
- DCL: Data Control Language (permissions like GRANT, REVOKE)
Each category has a clear role in managing databases.

Full Transcript

This lesson shows how to classify SQL statements into four main categories: DDL, DML, DQL, and DCL. We start by checking if a statement changes the database structure, which means it is DDL. If not, we check if it changes data, which is DML. If it only reads data, it is DQL. Finally, if it manages permissions, it is DCL. We traced four example statements: CREATE TABLE (DDL), INSERT INTO (DML), SELECT (DQL), and GRANT (DCL). The execution table shows each step and why the statement fits its category. The variable tracker follows the current statement and category as we move through the steps. Key moments clarify common confusions, like why SELECT is not DML and why GRANT is DCL. The quiz tests understanding by asking about categories and steps. The snapshot summarizes the categories and their roles in simple terms.

Practice

1. Which category of SQL statements is used to create or modify the structure of database objects like tables and indexes?

easy

A. Data Manipulation Language (DML)

B. Data Query Language (DQL)

C. Data Definition Language (DDL)

D. Data Control Language (DCL)

Solution

1. Step 1: Understand the role of DDL

   DDL commands define or change database structures, such as creating tables or indexes.

2. Step 2: Differentiate from other categories

   DML changes data, DQL queries data, and DCL controls access, so they don't modify structure.

3. Final Answer:

   Data Definition Language (DDL) -> Option C

4. Quick Check:

   Structure changes = DDL [OK]

Hint: Structure changes mean DDL commands [OK]

Common Mistakes:

• Confusing DDL with DML because both change something
• Thinking SELECT is DDL instead of DQL
• Mixing up DCL with DDL

2. Which of the following is the correct syntax to grant SELECT permission on a table named employees to a user john?

easy

A. GRANT SELECT TO john ON employees;

B. GRANT john ON employees SELECT;

C. SELECT GRANT ON employees TO john;

D. GRANT SELECT ON employees TO john;

Solution

1. Step 1: Recall correct GRANT syntax

   The correct syntax is: GRANT permission ON object TO user;

2. Step 2: Match syntax with options

   GRANT SELECT ON employees TO john; matches the correct order and keywords exactly.

3. Final Answer:

   GRANT SELECT ON employees TO john; -> Option D

4. Quick Check:

   GRANT + permission + ON + object + TO + user [OK]

Hint: GRANT syntax: GRANT permission ON object TO user [OK]

Common Mistakes:

• Swapping TO and ON keywords
• Placing user before permission
• Using SELECT before GRANT

3. What will be the output of the following SQL statement?

SELECT * FROM employees WHERE salary > 50000;

medium

A. All rows from employees with salary greater than 50000

B. All rows from employees with salary less than 50000

C. Syntax error due to missing semicolon

D. No rows because salary column does not exist

Solution

1. Step 1: Understand the SELECT query

   The query selects all columns (*) from employees where salary is greater than 50000.

2. Step 2: Analyze the WHERE condition

   Only rows with salary > 50000 are returned; others are excluded.

3. Final Answer:

   All rows from employees with salary greater than 50000 -> Option A

4. Quick Check:

   WHERE salary > 50000 filters rows correctly [OK]

Hint: WHERE filters rows; > means greater than [OK]

Common Mistakes:

• Confusing > with <
• Thinking semicolon is mandatory for output
• Assuming salary column missing without info

4. Identify the error in the following SQL statement:

INSERT INTO employees (id, name salary) VALUES (1, 'Alice', 60000);

medium

A. Missing comma between columns 'name' and 'salary'

B. VALUES keyword is misspelled

C. Table name should be in quotes

D. Cannot insert numeric value into salary column

Solution

1. Step 1: Check column list syntax

   Columns must be separated by commas; here 'name' and 'salary' lack a comma.

2. Step 2: Verify other parts

   VALUES is correct, table name does not need quotes, numeric salary is valid.

3. Final Answer:

   Missing comma between columns 'name' and 'salary' -> Option A

4. Quick Check:

   Column list commas are required [OK]

Hint: Check commas between column names carefully [OK]

Common Mistakes:

• Ignoring missing commas in column list
• Thinking table names need quotes
• Assuming VALUES keyword typo

5. You want to remove a table named orders and all its data permanently from the database. Which SQL statement category should you use, and which command is correct?

hard

A. DML - DELETE FROM orders;

B. DDL - DROP TABLE orders;

C. DCL - REVOKE ALL ON orders;

D. DQL - SELECT * FROM orders;

Solution

1. Step 1: Understand the goal

   Removing a table and its data permanently means deleting the structure, not just data.

2. Step 2: Identify correct category and command

   DDL commands modify structure; DROP TABLE removes the table entirely.

3. Final Answer:

   DDL - DROP TABLE orders; -> Option B

4. Quick Check:

   Drop table = remove structure and data [OK]

Hint: DROP removes table; DELETE removes data only [OK]

Common Mistakes:

• Using DELETE which only removes data, not table
• Confusing REVOKE with deleting tables
• Thinking SELECT can remove anything