SQL statement categories (DDL, DML, DQL, DCL) - Mini Project: Build & Apply

Understanding SQL Statement Categories: DDL, DML, DQL, DCL

📖 Scenario: You are working as a junior database assistant in a small company. Your manager wants you to organize and understand different types of SQL statements so that you can use them correctly when managing the company database.

🎯 Goal: Build a simple SQL script that categorizes example SQL statements into the four main categories: DDL, DML, DQL, and DCL. You will create tables, insert data, query data, and control access using the correct SQL commands for each category.

📋 What You'll Learn

Create a table using a DDL statement

Insert data into the table using a DML statement

Query data from the table using a DQL statement

Grant permission using a DCL statement

💡 Why This Matters

🌍 Real World

Organizing SQL statements into categories helps database administrators and developers understand how to manage data and structure safely and efficiently.

💼 Career

Knowing SQL categories is essential for roles like database administrators, backend developers, and data analysts to perform their tasks correctly.

1

Create a table using a DDL statement

Write a SQL statement to create a table called Employees with two columns: EmployeeID as an integer and Name as a variable character string of length 50. Use the correct DDL command.

SQL

-- Write the CREATE TABLE statement for Employees
-- Your code here

Hint

DDL commands are used to define or change database structures. To create a table, use CREATE TABLE.

2

Insert data into the table using a DML statement

Write a SQL statement to insert a new employee with EmployeeID 1 and Name 'Alice' into the Employees table. Use the correct DML command.

SQL

CREATE TABLE Employees (
    EmployeeID INT,
    Name VARCHAR(50)
);
-- Write the INSERT statement to add Alice
-- Your code here

Hint

DML commands are used to add or change data. To add a row, use INSERT INTO.

3

Query data from the table using a DQL statement

Write a SQL statement to select all columns and rows from the Employees table. Use the correct DQL command.

SQL

CREATE TABLE Employees (
    EmployeeID INT,
    Name VARCHAR(50)
);
INSERT INTO Employees (EmployeeID, Name) VALUES (1, 'Alice');
-- Write the SELECT statement to get all employees
-- Your code here

Hint

DQL commands are used to read data. To get all data from a table, use SELECT * FROM.

4

Grant permission using a DCL statement

Write a SQL statement to grant SELECT permission on the Employees table to a user named guest_user. Use the correct DCL command.

SQL

CREATE TABLE Employees (
    EmployeeID INT,
    Name VARCHAR(50)
);
INSERT INTO Employees (EmployeeID, Name) VALUES (1, 'Alice');
SELECT * FROM Employees;
-- Write the GRANT statement to allow guest_user to SELECT
-- Your code here

Hint

DCL commands control access. To give permission, use GRANT.

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