SQL Revision Notes
1. DDL (Data Definition Language)
Description: DDL commands are used to define, modify, or delete the structure of
database objects such as tables, schemas, or databases. They do not manipulate data
directly (except TRUNCATE removes all data).
Commands:
1. CREATE
o Purpose: Creates a new database object.
o Syntax:
o CREATE TABLE table_name (
o column1 datatype,
o column2 datatype,
o ...
o );
o Example:
o CREATE TABLE Students (
o ID INT,
o Name VARCHAR(50),
o Age INT
o );
2. ALTER
o Purpose: Modifies an existing database object.
o Syntax:
o ALTER TABLE table_name
o ADD column_name datatype;
o Example:
o ALTER TABLE Students
o ADD Address VARCHAR(100);
3. DROP
o Purpose: Deletes a database object permanently.
o Syntax:
o DROP TABLE table_name;
o Example:
o DROP TABLE Students;
4. TRUNCATE
o Purpose: Removes all records from a table but keeps its structure.
o Syntax:
o TRUNCATE TABLE table_name;
o Example:
o TRUNCATE TABLE Students;
5. RENAME
o Purpose: Renames a database object.
o Syntax:
o RENAME TABLE old_table_name TO new_table_name;
o Example:
o RENAME TABLE Students TO Learners;
6. DESC / DESCRIBE
o Purpose: Displays the structure of a table (columns, datatype, and
constraints).
o Syntax:
o DESC table_name;
o -- or
o DESCRIBE table_name;
o Example:
o DESC Students;
2. DML (Data Manipulation Language)
Description: DML commands are used to manipulate data inside tables, such as inserting,
updating, deleting, or selecting records.
Commands:
1. INSERT
o Purpose: Adds new records to a table.
o Syntax:
o INSERT INTO table_name (column1, column2, ...)
o VALUES (value1, value2, ...);
o Example:
o INSERT INTO Students (ID, Name, Age)
o VALUES (1, 'John', 20);
2. UPDATE
o Purpose: Modifies existing records.
o Syntax:
o UPDATE table_name
o SET column1 = value1, column2 = value2, ...
o WHERE condition;
o Example:
o UPDATE Students
o SET Age = 21
o WHERE ID = 1;
3. DELETE
o Purpose: Removes records from a table.
o Syntax:
o DELETE FROM table_name
o WHERE condition;
o Example:
o DELETE FROM Students
o WHERE ID = 1;
4. SELECT
o Purpose: Retrieves data from a table.
o Syntax:
o SELECT column1, column2, ...
o FROM table_name
o WHERE condition;
o Example:
o SELECT * FROM Students;
o SELECT Name, Age FROM Students WHERE Age > 18;
3. WHERE Clause
Purpose: Filters records based on a specified condition. Can be used with SELECT, UPDATE,
or DELETE.
• Syntax:
• SELECT column1, column2, ...
• FROM table_name
• WHERE condition;
• Example:
• SELECT * FROM Students WHERE Age > 18;
• SELECT Name FROM Students WHERE Name LIKE 'J%';
• Notes: Supports operators: =, >, <, >=, <=, !=, BETWEEN, LIKE, IN.
4. AS Keyword
Purpose: Assigns a temporary alias (name) to a column or table for readability or complex
queries.
1. Column Alias
o Syntax:
o SELECT column_name AS alias_name
o FROM table_name;
o Example:
o SELECT Name AS StudentName FROM Students;
2. Table Alias
o Syntax:
o SELECT t.column1, t.column2
o FROM table_name AS t;
o Example:
o SELECT s.Name, s.Age
o FROM Students AS s;
• Notes: Alias exists only for the duration of the query.
✅ Quick Memory Tip:
• DDL → Defines structure
• DML → Manipulates data
• WHERE → Will filter data
• AS → Alias for clarity
