Welcome back to **MySQL**! 🎉 In Class 11, you learnt the basics of databases and SQL. This chapter is a **complete revision tour** — refreshing everything you know, filling in the gaps, and getting you 100% exam-ready before we move to advanced topics like Joins and Python connectivity!
> [!TIP]
> **How to use these notes:** Even if you remember some of this from Class 11, READ IT ALL — CBSE loves to test the "small details" like NULL handling, constraint syntax, and DELETE vs DROP. Type out every SQL command yourself! 🎯
---
## 5.1 🌟 Introduction
Let's refresh the big picture before diving into commands:
```mermaid
graph TD
DB["🗄️ Database\nOrganised collection\nof related data"]
DBMS["⚙️ DBMS\nSoftware to manage\ndatabases"]
RDBMS["🔗 RDBMS\nDBMS that stores data\nin TABLES"]
SQL["🗣️ SQL\nLanguage to talk\nto RDBMS"]
MYSQL["🐬 MySQL\nA popular RDBMS\nsoftware"]
DB --> DBMS --> RDBMS --> SQL
RDBMS --> MYSQL
style DBMS fill:#2196F3,color:#fff
style RDBMS fill:#9C27B0,color:#fff
style MYSQL fill:#4CAF50,color:#fff
```
::: grid
::: card 🗄️ | Database | Organised collection of related data | Student records, library catalogue
::: card ⚙️ | DBMS | Software to manage databases | MySQL, Oracle, PostgreSQL
::: card 🔗 | RDBMS | DBMS storing data as linked TABLES | MySQL is an RDBMS!
::: card 🗣️ | SQL | The language to talk to RDBMS | CREATE, SELECT, INSERT...
:::
---
## 5.2 📐 Relational Data Model
The **Relational Model** organises all data into simple 2D **tables** — rows and columns, just like an Excel sheet!
```
RollNo Name Marks
------ ---- -----
1 Amit 85 ← This entire row = ONE Tuple
2 Neha 90
3 Raj 78
↑
This column = ONE Attribute
```
**Key Terminology — Memorise These! 🧠**
| Term | Simple Meaning | Example |
| :--- | :--- | :--- |
| **Relation** | Fancy word for a Table | The Student table itself |
| **Tuple** | Fancy word for a Row | One student's complete record |
| **Attribute** | Fancy word for a Column | 'Marks' column |
| **Degree** | Number of columns | 3 columns → Degree = 3 |
| **Cardinality** | Number of rows | 3 students → Cardinality = 3 |
```mermaid
graph LR
T["Table = Relation"]
R["Row = Tuple"]
C["Column = Attribute"]
style T fill:#FF9800,color:#fff
style R fill:#2196F3,color:#fff
style C fill:#4CAF50,color:#fff
```
---
### The Key Family — Primary, Candidate, Alternate, Foreign 🔑
::: grid
::: card 🔑 | Primary Key | Uniquely identifies EVERY row; cannot be NULL or duplicate | RollNo — no two students share it
::: card 🥈 | Candidate Key | ANY column that COULD be chosen as primary key | RollNo, Admission Number — both are "candidates"
::: card 🥉 | Alternate Key | A Candidate Key NOT chosen as primary | If RollNo is PK, Admission Number becomes Alternate
::: card 🔗 | Foreign Key | Links to another table's Primary Key | DeptId in Student table, linking to Department table
:::
> **Analogy:** Imagine choosing a team captain (Primary Key) from a group of eligible players (Candidate Keys). The ones NOT chosen are still "eligible" — they're your Alternate Keys!
> [!IMPORTANT]
> **Board Exam Tip**
> "Define Primary Key, Candidate Key, and Alternate Key." — **3-mark** question, asked EVERY year!
> **Primary Key:** Uniquely identifies each row; can never be NULL.
> **Candidate Key:** Any column(s) eligible to become the primary key.
> **Alternate Key:** A candidate key that was NOT selected as primary key.
---
## 5.3 🐬 MySQL — A Popular RDBMS
**MySQL** is free, open-source RDBMS software used worldwide — from small school projects to giant companies!
### 5.3.1 MySQL and SQL 🗣️
> **Important distinction:** MySQL is the **software (the engine)**. SQL is the **language** you use to talk to it (the steering wheel).
```mermaid
graph LR
YOU["👤 You type\nSQL commands"]
MYSQL["🐬 MySQL Server\n(understands SQL,\nstores your data)"]
RESULT["📊 Result\n(your data,\nback to you)"]
YOU -->|"SQL"| MYSQL --> RESULT
style MYSQL fill:#4CAF50,color:#fff
```
---
### 5.3.2 Common MySQL Data Types 📦
Every column needs a **data type** — this decides what kind of value it can hold.
| Category | Data Type | Description | Example |
| :--- | :--- | :--- | :--- |
| **Whole Numbers** | `INT` | Integer values | 25, 100, -5 |
| **Decimal Numbers** | `DECIMAL(M,D)` | Exact decimals — M=total digits, D=after decimal | `DECIMAL(5,2)` → 123.45 |
| **Fixed Text** | `CHAR(n)` | Always uses exactly n characters | `CHAR(10)` for Pincode |
| **Flexible Text** | `VARCHAR(n)` | Uses only as much space as needed (up to n) | `VARCHAR(30)` for Name |
| **Date** | `DATE` | Format is ALWAYS 'YYYY-MM-DD' | '2026-07-15' |
::: grid
::: card 📦 | CHAR(n) | Fixed box — always n-size, even if data is shorter | Good for Pincode, Gender (fixed length)
::: card 🎒 | VARCHAR(n) | Flexible bag — uses only what's needed | Good for Name, Address (varies a lot)
:::
> [!IMPORTANT]
> **Board Exam Tip**
> "Differentiate between CHAR and VARCHAR." — **2-mark** question asked constantly!
> `CHAR(n)` is **fixed-length** — always reserves n characters (padded with spaces if shorter). `VARCHAR(n)` is **variable-length** — uses only as much space as the actual data needs, up to n characters. CHAR is slightly faster; VARCHAR saves memory.
---
## 5.4 🔓 Accessing Database in MySQL
Before creating any table, you need to CREATE and SELECT a database:
```python
-- Wait, this is SQL, not Python! Let's use proper SQL comments:
```
```sql
-- accessing_database.sql
CREATE DATABASE SchoolDB; -- Create a brand-new database
USE SchoolDB; -- Select it for use (MUST do this before creating tables!)
SHOW DATABASES; -- See all databases on the server
```
| Command | Purpose |
| :--- | :--- |
| `CREATE DATABASE name;` | Makes a new, empty database |
| `USE name;` | Switches to that database — REQUIRED before any table work |
| `SHOW DATABASES;` | Lists all databases available |
| `DROP DATABASE name;` | Permanently deletes a database (careful!) |
> [!WARNING]
> **Forgetting `USE`!**
> If you skip `USE SchoolDB;`, MySQL doesn't know WHICH database you want to work in, and gives the error: **"No database selected"**. Always run `USE` first!
---
## 5.5 🏗️ Creating Tables in MySQL
The `CREATE TABLE` command builds a new table with named columns and data types.
```sql
-- create_table.sql
CREATE TABLE Student (
RollNo INT,
Name VARCHAR(30),
Stream VARCHAR(20),
Fee DECIMAL(10,2)
);
```
**General Syntax:**
```
CREATE TABLE table_name (
column1_name datatype,
column2_name datatype,
...
);
```
> [!IMPORTANT]
> **Board Exam Tip**
> "Write SQL to create a table Student with RollNo (integer), Name (30-char string), Marks (decimal)." — **3-mark** question, extremely common!
> Answer:
> ```sql
> CREATE TABLE Student (
> RollNo INT,
> Name VARCHAR(30),
> Marks DECIMAL(5,2)
> );
> ```
---
## 5.6 📥 Inserting Data into Table
The `INSERT INTO` command adds new rows (records) to a table.
**Method 1 — All Columns (must match table order EXACTLY):**
```sql
-- insert_all_columns.sql
INSERT INTO Student VALUES (1, 'Rohan', 'Science', 2500.00);
```
**Method 2 — Specific Columns (Recommended! ⭐):**
```sql
-- insert_specific_columns.sql
INSERT INTO Student (RollNo, Name) VALUES (2, 'Priya');
-- Other columns (Stream, Fee) become NULL automatically
```
**Inserting Multiple Rows at Once:**
```sql
-- insert_multiple.sql
INSERT INTO Student VALUES
(3, 'Karan', 'Commerce', 2200.00),
(4, 'Sita', 'Science', 2800.00),
(5, 'Meera', 'Arts', 1900.00);
```
| Method | When to use |
| :--- | :--- |
| All columns (no names) | Quick, but MUST match exact table column order |
| Specific columns (named) | Safer — order doesn't matter, can skip columns |
> [!WARNING]
> **String and Date values need single quotes!**
> `INSERT INTO Student VALUES (1, Rohan, ...)` ❌ — missing quotes around 'Rohan' causes an ERROR!
> `INSERT INTO Student VALUES (1, 'Rohan', ...)` ✅ — always quote text/date values.
---
## 5.7 🔍 Making Simple Queries through SELECT Command
`SELECT` is the most powerful and most-used SQL command — it retrieves (fetches) data from tables.
**Our reference Student table for ALL examples below:**
| RollNo | Name | Stream | Fee |
| :--- | :--- | :--- | :--- |
| 1 | Rohan | Science | 2500.00 |
| 2 | Priya | Science | NULL |
| 3 | Karan | Commerce | 2200.00 |
| 4 | Sita | Science | 2800.00 |
| 5 | Meera | Arts | 1900.00 |
---
### 5.7.1 Selecting All Data ⭐
```sql
-- select_all.sql
SELECT * FROM Student;
```
> The `*` (asterisk) means "give me ALL columns". This shows the entire table.
---
### 5.7.2 Selecting Particular Rows 🎯
Use the `WHERE` clause to filter and show only specific rows that match a condition.
```sql
-- select_particular_rows.sql
SELECT * FROM Student WHERE Stream = 'Science';
```
**Output:**
```
RollNo Name Stream Fee
1 Rohan Science 2500.00
2 Priya Science NULL
4 Sita Science 2800.00
```
```sql
-- Another example:
SELECT * FROM Student WHERE Fee > 2000;
```
> Only rows that SATISFY the condition are shown — this is called **filtering**!
---
### 5.7.3 Selecting Particular Columns 📋
Instead of `*` (all columns), name only the columns you want:
```sql
-- select_particular_columns.sql
SELECT Name, Fee FROM Student;
```
**Output:**
```
Name Fee
Rohan 2500.00
Priya NULL
Karan 2200.00
Sita 2800.00
Meera 1900.00
```
**Combine columns AND rows together:**
```sql
SELECT Name, Fee FROM Student WHERE Stream = 'Science';
```
---
### 5.7.4 Eliminating Redundant Data — DISTINCT Keyword 🧹
`DISTINCT` removes duplicate values, showing each unique value only once.
```sql
-- distinct_demo.sql
SELECT DISTINCT Stream FROM Student;
```
**Output:**
```
Stream
Science
Commerce
Arts
```
> Even though "Science" appears 3 times in the table, DISTINCT shows it only ONCE!
---
### 5.7.5 Selecting from all the Rows — ALL Keyword 📊
`ALL` is the OPPOSITE of DISTINCT — and it's actually the DEFAULT behaviour (you rarely type it explicitly).
```sql
-- all_keyword_demo.sql
SELECT ALL Stream FROM Student;
-- Same as: SELECT Stream FROM Student;
-- Shows Science, Science, Commerce, Science, Arts (ALL rows, WITH duplicates)
```
| Keyword | Shows Duplicates? | Is it the Default? |
| :--- | :--- | :--- |
| `ALL` | ✅ Yes | ✅ Yes (automatic) |
| `DISTINCT` | ❌ No | ❌ No (must be typed) |
---
### 5.7.6 Viewing Structure of a Table 🏗️
`DESCRIBE` (or `DESC`) shows the table's STRUCTURE (column names, data types) — NOT the actual data!
```sql
-- describe_demo.sql
DESCRIBE Student;
-- OR:
DESC Student;
```
**Output:**
```
Field Type Null Key Default Extra
RollNo int YES
Name varchar(30) YES
Stream varchar(20) YES
Fee decimal(10,2) YES
```
> [!IMPORTANT]
> **Board Exam Tip**
> "Which command shows a table's structure without displaying its data?" — **1-mark** question!
> Answer: **`DESCRIBE table_name;`** or **`DESC table_name;`**
---
### 5.7.7 Performing Simple Calculations ➕
SQL can do math directly! You can calculate values right inside a SELECT statement.
```sql
-- simple_calculations.sql
SELECT 25 + 10; -- Output: 35
SELECT 100 / 4; -- Output: 25.0000
SELECT 6 * 7; -- Output: 42
```
**Calculations using actual table columns:**
```sql
-- calculation_on_column.sql
SELECT Name, Fee * 12 FROM Student; -- Annual Fee = Monthly Fee × 12
```
**Output:**
```
Name Fee*12
Rohan 30000.00
Priya NULL ← NULL × 12 = NULL (any math with NULL = NULL!)
Karan 26400.00
Sita 33600.00
Meera 22800.00
```
> [!NOTE]
> **NULL "infects" every calculation! 🧠**
> Any arithmetic done with NULL always gives NULL as the result! `NULL + 100 = NULL`, `NULL * 5 = NULL`. This is because NULL means "unknown" — you can't do math with an unknown value!
---
### 5.7.8 Using Column Aliases 🏷️
An **Alias** gives a column a temporary, nicer name in the output — using the `AS` keyword.
```sql
-- column_alias_demo.sql
SELECT Name, Fee * 12 AS AnnualFee FROM Student;
```
**Output:**
```
Name AnnualFee
Rohan 30000.00
Karan 26400.00
```
> The alias `AnnualFee` is much more readable than the messy default heading "Fee\*12"! Remember: the alias is TEMPORARY — it doesn't rename anything in the actual table.
> [!IMPORTANT]
> **Board Exam Tip**
> "Write a query to display Name and Annual Fee (Fee × 12) with a proper column heading." — **2-mark** question!
> Answer: `SELECT Name, Fee * 12 AS AnnualFee FROM Student;`
---
### 5.7.9 Condition Based on a Range — BETWEEN 📏
`BETWEEN` checks if a value falls within a range — **INCLUSIVE** of both ends!
```sql
-- between_demo.sql
SELECT * FROM Student WHERE Fee BETWEEN 2000 AND 2800;
```
> This is exactly the same as writing: `WHERE Fee >= 2000 AND Fee <= 2800`
**Output:**
```
RollNo Name Stream Fee
1 Rohan Science 2500.00
3 Karan Commerce 2200.00
4 Sita Science 2800.00 ← 2800 IS included!
```
---
### 5.7.10 Condition Based on a List — IN 📋
`IN` checks if a value matches ANY value from a given list — a shortcut for multiple OR conditions.
```sql
-- in_demo.sql
SELECT * FROM Student WHERE Stream IN ('Science', 'Commerce');
-- Same as: WHERE Stream = 'Science' OR Stream = 'Commerce'
```
**Output:**
```
RollNo Name Stream Fee
1 Rohan Science 2500.00
2 Priya Science NULL
3 Karan Commerce 2200.00
4 Sita Science 2800.00
```
> Meera (Arts) is excluded — 'Arts' is not in our list!
---
### 5.7.11 Condition Based on Pattern Matches — LIKE 🔎
`LIKE` searches for a text PATTERN using wildcard symbols.
| Wildcard | Meaning | Example |
| :--- | :--- | :--- |
| `%` | Zero or MORE characters | `'R%'` → matches Rohan, Raj, Riya |
| `_` | Exactly ONE character | `'_a%'` → 2nd letter must be 'a' |
```sql
-- like_demo.sql
-- Names starting with 'R':
SELECT * FROM Student WHERE Name LIKE 'R%';
-- Names where 2nd letter is 'a':
SELECT * FROM Student WHERE Name LIKE '_a%';
-- Names that are EXACTLY 5 characters long:
SELECT * FROM Student WHERE Name LIKE '_____'; -- 5 underscores!
-- Names ending in 'a':
SELECT * FROM Student WHERE Name LIKE '%a';
```
::: grid
::: card 🔤 | Starts With | 'R%' | Name begins with R
::: card 🔚 | Ends With | '%a' | Name ends with 'a'
::: card 🔍 | Contains | '%an%' | Name has 'an' anywhere
:::
> [!IMPORTANT]
> **Board Exam Tip**
> "Write a query to display names of students whose name ends with 'a'." — Common **2-mark** question!
> Answer: `SELECT * FROM Student WHERE Name LIKE '%a';`
---
### 5.7.12 Searching for NULL 🕳️
Since NULL means "unknown", you CANNOT use `=` to find it! You must use special keywords.
```sql
-- null_search_demo.sql
-- WRONG way (returns NOTHING, even if NULLs exist!):
SELECT * FROM Student WHERE Fee = NULL; ❌
-- CORRECT way:
SELECT * FROM Student WHERE Fee IS NULL; ✅
SELECT * FROM Student WHERE Fee IS NOT NULL; ✅
```
**Output of `WHERE Fee IS NULL`:**
```
RollNo Name Stream Fee
2 Priya Science NULL
```
> [!WARNING]
> **The #1 NULL Mistake in Exams!**
> `WHERE Fee = NULL` will ALWAYS return **zero rows**, even if NULL values exist in the table! This is a favourite trick question. You MUST use `IS NULL` and `IS NOT NULL` — never `=` or `!=` with NULL!
---
## 5.8 🔒 Creating Tables with SQL Constraints
### 5.8.1 SQL Constraints — What Are They? 🛡️
**Constraints** are RULES applied to columns that prevent bad or invalid data from being stored. Think of them as "quality control" for your table!
```mermaid
graph TD
C["🔒 SQL CONSTRAINTS"]
C1["NOT NULL\nMust have a value"]
C2["UNIQUE\nNo duplicate values"]
C3["PRIMARY KEY\nNOT NULL + UNIQUE\n(one per table)"]
C4["FOREIGN KEY\nMust match another\ntable's Primary Key"]
C5["CHECK\nMust satisfy a\ncondition"]
C6["DEFAULT\nAuto-fills a value\nif none given"]
C --> C1
C --> C2
C --> C3
C --> C4
C --> C5
C --> C6
style C fill:#9C27B0,color:#fff
```
**All Six Constraints Explained Simply:**
::: grid
::: card 🔴 | NOT NULL | Column can NEVER be left empty | Name NOT NULL — every student MUST have a name
::: card 🟡 | UNIQUE | No two rows can have the same value | Email UNIQUE — no two students share an email
::: card 🟢 | PRIMARY KEY | Uniquely identifies each row (NOT NULL + UNIQUE combined) | RollNo PRIMARY KEY — the ID of each student
::: card 🔵 | FOREIGN KEY | Links to another table's Primary Key | DeptId — must exist in the Department table
::: card 🟠 | CHECK | Value must pass a test/condition | Fee CHECK (Fee > 0) — no negative fees allowed!
::: card ⚪ | DEFAULT | Fills a value automatically if none given | Stream DEFAULT 'Science' — assumed if not mentioned
:::
**Constraints Quick Reference:**
| Constraint | Allows NULL? | Allows Duplicates? |
| :--- | :--- | :--- |
| NOT NULL | ❌ No | ✅ Yes |
| UNIQUE | ✅ Yes | ❌ No |
| PRIMARY KEY | ❌ No | ❌ No |
| FOREIGN KEY | ✅ Yes | ✅ Yes |
| CHECK | ✅ Yes | ✅ Yes |
---
### 5.8.2 Applying Table Constraints 🛠️
```sql
-- table_with_constraints.sql
CREATE TABLE Student (
RollNo INT PRIMARY KEY, -- Unique ID, can't be NULL
Name VARCHAR(30) NOT NULL, -- Cannot be blank
Email VARCHAR(50) UNIQUE, -- No duplicates
Stream VARCHAR(20) DEFAULT 'Science', -- Auto-fills if skipped
Fee DECIMAL(10,2) CHECK (Fee > 0), -- Must be positive!
DeptId INT,
FOREIGN KEY (DeptId) REFERENCES Department(DeptId) -- Links to another table
);
```
**Testing the constraints:**
```sql
-- Valid insert (follows all rules):
INSERT INTO Student (RollNo, Name, Fee) VALUES (1, 'Rohan', 2500);
-- Stream automatically becomes 'Science' (DEFAULT)
-- INVALID insert (violates CHECK — negative fee!):
INSERT INTO Student (RollNo, Name, Fee) VALUES (2, 'Priya', -500);
-- ❌ ERROR! CHECK constraint failed.
-- INVALID insert (violates NOT NULL):
INSERT INTO Student (RollNo, Fee) VALUES (3, 2000);
-- ❌ ERROR! Name cannot be NULL.
```
> [!IMPORTANT]
> **Board Exam Tip**
> "Write SQL to create a Student table with RollNo as Primary Key, Name that cannot be blank, and Fee that must always be positive." — **4-mark** question, very common!
> Answer:
> ```sql
> CREATE TABLE Student (
> RollNo INT PRIMARY KEY,
> Name VARCHAR(30) NOT NULL,
> Fee DECIMAL(10,2) CHECK (Fee > 0)
> );
> ```
---
## 5.9 🔍 Viewing a Table Structure
We touched on this in 5.7.6, but let's cement it — `DESCRIBE` shows structure, NOT data:
```sql
-- viewing_structure.sql
DESCRIBE Student;
```
**Output:**
```
Field Type Null Key Default Extra
RollNo int NO PRI
Name varchar(30) NO
Email varchar(50) YES UNI
Stream varchar(20) YES Science
Fee decimal(10,2) YES
```
**What each column of the output means:**
| Output Column | Meaning |
| :--- | :--- |
| **Field** | Column name |
| **Type** | Data type and size |
| **Null** | Can this column be NULL? (YES/NO) |
| **Key** | PRI = Primary Key, UNI = Unique |
| **Default** | Default value (if any) |
> [!NOTE]
> **`DESCRIBE` vs `SELECT * FROM`! 🧠**
> `DESCRIBE table_name;` → shows the STRUCTURE (blueprint) — column names, types, constraints.
> `SELECT * FROM table_name;` → shows the DATA (actual rows) stored in the table.
> Don't confuse these two — a favourite one-mark question!
---
## 5.10 🔄 Inserting Data into another Table
You can copy matching data from one table into another table, using `INSERT INTO ... SELECT`:
```sql
-- insert_into_another_table.sql
-- First, create a table for toppers:
CREATE TABLE Toppers (
Name VARCHAR(30),
Fee DECIMAL(10,2)
);
-- Copy data from Student table where Fee > 2500:
INSERT INTO Toppers (Name, Fee)
SELECT Name, Fee FROM Student WHERE Fee > 2500;
```
**What happens:**
```mermaid
graph LR
STU["📋 Student Table\n(All Students)"]
FILTER["WHERE Fee > 2500"]
TOP["📋 Toppers Table\n(Only matching rows\ncopied here!)"]
STU --> FILTER --> TOP
style FILTER fill:#FF9800,color:#fff
style TOP fill:#4CAF50,color:#fff
```
> Very useful for **creating backups**, **archiving old data**, or **splitting data into categories** — like moving "Toppers" into their own table!
> [!IMPORTANT]
> **Board Exam Tip**
> "Write a query to copy names and marks of students scoring above 90 into a new table Toppers." — **3-mark** question!
> Answer: `INSERT INTO Toppers (Name, Marks) SELECT Name, Marks FROM Student WHERE Marks > 90;`
---
## 5.11 ✏️ Modifying Data in Tables
The `UPDATE` command changes existing data in a table.
```sql
-- update_demo.sql
UPDATE Student
SET Fee = Fee + 500
WHERE Stream = 'Commerce';
```
**More Examples:**
```sql
-- Update a single row:
UPDATE Student SET Fee = 3000 WHERE RollNo = 1;
-- Update multiple columns at once:
UPDATE Student SET Fee = 2600, Stream = 'Arts' WHERE RollNo = 2;
-- Give everyone in Science a 10% raise:
UPDATE Student SET Fee = Fee * 1.10 WHERE Stream = 'Science';
```
> [!WARNING]
> **NEVER forget the WHERE clause!**
> `UPDATE Student SET Fee = 5000;` (with NO WHERE!) changes **EVERY SINGLE ROW** in the entire table to Fee = 5000! Always double-check your WHERE condition before pressing Enter!
> [!IMPORTANT]
> **Board Exam Tip**
> "Write a query to increase the fee of all Science students by 500." — **2-mark** question!
> Answer: `UPDATE Student SET Fee = Fee + 500 WHERE Stream = 'Science';`
---
## 5.12 🗑️ Deleting Data from Tables
The `DELETE` command removes rows from a table.
```sql
-- delete_demo.sql
-- Delete ONE specific row:
DELETE FROM Student WHERE RollNo = 5;
-- Delete MULTIPLE matching rows:
DELETE FROM Student WHERE Stream = 'Arts';
-- Delete ALL rows (table becomes empty, but structure remains!):
DELETE FROM Student;
```
> [!NOTE]
> **DELETE keeps the table alive! 🧠**
> `DELETE FROM Student;` (without WHERE) removes ALL rows — but the empty table STILL EXISTS and can accept new data. This is very different from `DROP TABLE`, which destroys the table entirely (covered in 5.14)!
---
## 5.13 🔧 Altering Tables
`ALTER TABLE` changes the STRUCTURE of a table (adding/removing/modifying columns) — NOT the data inside it!
```mermaid
graph TD
ALTER["🔧 ALTER TABLE"]
ADD["ADD\nAdd a new column"]
MODIFY["MODIFY\nChange a column's\ndata type/size"]
DROP["DROP COLUMN\nRemove a column"]
ALTER --> ADD
ALTER --> MODIFY
ALTER --> DROP
style ALTER fill:#FF9800,color:#fff
```
**Adding a new column:**
```sql
-- alter_add.sql
ALTER TABLE Student ADD Email VARCHAR(50);
```
**Changing a column's data type/size:**
```sql
-- alter_modify.sql
ALTER TABLE Student MODIFY Name VARCHAR(50);
-- Expands Name from VARCHAR(30) to VARCHAR(50)
```
**Removing a column:**
```sql
-- alter_drop_column.sql
ALTER TABLE Student DROP COLUMN Email;
-- Email column AND its data are permanently gone!
```
**Renaming a column (bonus!):**
```sql
-- alter_change.sql
ALTER TABLE Student CHANGE Fee Tuition DECIMAL(10,2);
-- Renames 'Fee' to 'Tuition'
```
| Clause | Purpose |
| :--- | :--- |
| `ADD column datatype` | Add a new column |
| `MODIFY column new_datatype` | Change type/size (keep same name) |
| `CHANGE old new datatype` | Rename column (and optionally change type) |
| `DROP COLUMN column` | Delete a column permanently |
> [!IMPORTANT]
> **Board Exam Tip**
> "Write SQL to add a column Phone (VARCHAR 15) to the Student table." — **2-mark** question!
> Answer: `ALTER TABLE Student ADD Phone VARCHAR(15);`
> [!WARNING]
> **ALTER vs UPDATE — Don't Confuse Them!**
> `ALTER TABLE` changes the table's **STRUCTURE** (columns) — it's a DDL command.
> `UPDATE` changes the actual **DATA** (values inside rows) — it's a DML command.
> Mixing these up is a classic exam mistake!
---
## 5.14 💣 Dropping Tables
`DROP TABLE` permanently and completely destroys a table — structure AND data — with no way to undo it!
```sql
-- drop_table_demo.sql
DROP TABLE Student;
-- Student table is COMPLETELY GONE. Structure gone. Data gone. Forever.
```
```mermaid
graph LR
BEFORE["📋 Student Table\nStructure + All Data"]
DROP["💣 DROP TABLE Student;"]
AFTER["❌ COMPLETELY GONE\nNo Recycle Bin\nNo Undo!"]
BEFORE --> DROP --> AFTER
style DROP fill:#F44336,color:#fff
style AFTER fill:#9E9E9E,color:#fff
```
**DELETE vs DROP — The Ultimate Comparison Table!**
| Feature | DELETE | DROP |
| :--- | :--- | :--- |
| **What it removes** | Rows (data) only | Entire table (structure + data) |
| **WHERE clause?** | ✅ Can use it | ❌ Cannot use it |
| **Table survives?** | ✅ Yes, empty table remains | ❌ No, table is gone |
| **Command type** | DML | DDL |
| **Can be undone?** | ✅ Yes (ROLLBACK, if not committed) | ❌ No (auto-committed) |
> [!WARNING]
> **DROP is FOREVER!**
> There is NO undo button for `DROP TABLE`. Always be 100% sure before running this command, and take a backup if there's ANY chance you'll need the data again!
> [!IMPORTANT]
> **Board Exam Tip**
> "Differentiate between DELETE and DROP commands." — **2-mark** question, asked EVERY single year!
> Answer: **DELETE** removes rows (data) from a table but the table itself remains — it's a DML command that can use WHERE. **DROP** removes the entire table (structure + data) permanently — it's a DDL command that cannot use WHERE and cannot be undone.
---
## ⚠️ Common Errors and Misconceptions
| Mistake | What's Wrong | Correct Understanding |
| :--- | :--- | :--- |
| ❌ `WHERE Fee = NULL` | Always returns ZERO rows | ✅ Use `WHERE Fee IS NULL` |
| ❌ Thinking DELETE removes the table | Only removes ROWS | ✅ Table structure stays; use DROP to remove the table itself |
| ❌ Using `"double quotes"` for text | Errors in strict SQL mode | ✅ Always use `'single quotes'` for text/dates |
| ❌ Confusing ALTER and UPDATE | Wrong command used | ✅ ALTER = structure (columns); UPDATE = data (values) |
| ❌ Forgetting WHERE in UPDATE/DELETE | Changes/removes ALL rows! | ✅ Always double-check WHERE before running |
| ❌ CHAR and VARCHAR are the same | They behave differently | ✅ CHAR = fixed length; VARCHAR = variable length |
| ❌ Thinking Candidate Key = Primary Key | They're different concepts | ✅ Primary Key IS a Candidate Key, but not vice versa |
---
## 🔑 Quick Revision — Exam Ready!
**Database Commands:**
```sql
CREATE DATABASE name; USE name; DROP DATABASE name;
```
**Table Commands:**
```sql
CREATE TABLE name (col type constraint, ...);
DESCRIBE name; -- or DESC name; (view structure)
ALTER TABLE name ADD/MODIFY/DROP COLUMN ...;
DROP TABLE name; -- permanently destroys table
```
**Data Commands:**
```sql
INSERT INTO name VALUES (...);
UPDATE name SET col=value WHERE condition;
DELETE FROM name WHERE condition;
```
**SELECT Quick Reference:**
| Need | Use |
| :--- | :--- |
| All rows/columns | `SELECT * FROM table;` |
| Specific columns | `SELECT col1, col2 FROM table;` |
| Filter rows | `WHERE condition` |
| Remove duplicates | `DISTINCT` |
| Range | `BETWEEN val1 AND val2` |
| List match | `IN (val1, val2, ...)` |
| Pattern match | `LIKE 'pattern%'` |
| Missing value | `IS NULL` / `IS NOT NULL` |
| Rename column in output | `AS alias_name` |
**Constraints — Quick Table:**
| Constraint | Purpose |
| :--- | :--- |
| NOT NULL | Value is compulsory |
| UNIQUE | No duplicates allowed |
| PRIMARY KEY | NOT NULL + UNIQUE (row identifier) |
| FOREIGN KEY | Links to another table's Primary Key |
| CHECK | Value must satisfy a condition |
| DEFAULT | Auto-fills value if not provided |
**The Big Comparison — DELETE vs DROP:**
| | DELETE | DROP |
| :--- | :--- | :--- |
| Removes | Data (rows) | Everything (structure + data) |
| Table survives | Yes | No |
| Type | DML | DDL |
---
## 🎯 Sample Board Exam Questions
### Q1: Very Short Answer [1 mark each]
a) What is the full form of DBMS?
**→ Database Management System**
b) Which command is used to view a table's structure?
**→ DESCRIBE (or DESC)**
c) Which keyword removes duplicate values in a SELECT query?
**→ DISTINCT**
d) What is the correct format for a DATE value in MySQL?
**→ 'YYYY-MM-DD'**
e) Which constraint ensures a column can never contain duplicate values?
**→ UNIQUE**
---
### Q2: Short Answer [2 marks]
**Q: Differentiate between DELETE and DROP.**
**DELETE** is a DML command that removes rows (data) from a table using a WHERE clause; the table itself continues to exist (empty if all rows deleted).
**DROP** is a DDL command that removes the ENTIRE table — both structure and data — permanently, with no WHERE clause option and no way to undo it.
---
### Q3: Practical SQL [4 marks]
**Q: Write SQL statements to:**
a) Create a table Employee with EmpId (Primary Key), Name (cannot be blank), and Salary (must be positive).
b) Insert a record.
c) Display employees earning more than 30000, sorted... (display all fields).
d) Increase salary of all employees by 10%.
```sql
-- a) Create table
CREATE TABLE Employee (
EmpId INT PRIMARY KEY,
Name VARCHAR(30) NOT NULL,
Salary DECIMAL(10,2) CHECK (Salary > 0)
);
-- b) Insert record
INSERT INTO Employee VALUES (1, 'Aman', 35000);
-- c) Display employees earning > 30000
SELECT * FROM Employee WHERE Salary > 30000;
-- d) Increase salary by 10%
UPDATE Employee SET Salary = Salary * 1.10;
```
---
### Q4: Output Based [2 marks]
**Q: What is the output?**
```sql
SELECT Name FROM Student WHERE Fee IS NULL;
```
*(Given: Priya has NULL fee)*
**Output:**
```
Name
Priya
```
---
## ✏️ Practice Problems
1. Write SQL to create a `Book` table with BookId (Primary Key), Title (cannot be blank), Price (must be greater than 0), and Genre (default value 'Fiction').
2. Insert 3 records into the Book table, one with a missing Price (NULL).
3. Write a query to display all books priced between ₹200 and ₹500.
4. Write a query to display book titles that start with the letter 'H'.
5. Write a query to find books where Genre is either 'Fiction' or 'Mystery' using the IN operator.
6. Write SQL to add a new column `PublishYear` to the Book table.
7. Write a query to display book titles where Price is NULL.
8. Explain the difference between `CHAR(10)` and `VARCHAR(10)` with a real-world example.
9. Write SQL to copy all books priced above ₹500 into a new table called `PremiumBooks`.
10. What will happen if you run `DELETE FROM Book;` followed by `SELECT * FROM Book;`? What if you instead ran `DROP TABLE Book;` followed by `SELECT * FROM Book;`? Explain both outcomes.
Back to List
Calculating...
UNIT 2 : CH 5
Dec 14, 2025
🖥️ MySQL SQL Revision Tour
Learning Support
Need Help With This Chapter?
Save key topics for exam revision, ask questions to teachers, or submit content corrections.
Verified Doubts & Teacher Answers
Loading resolved questions for this note...