You know how to **query** data ā now let's learn how to **build** the database and **manage** the data inside it! This chapter answers the BIG questions: **How do you create tables with proper rules? How do you insert, update, and delete data? How do you restructure a table after it's built?** Master this chapter and you control the full database lifecycle! š
> [!TIP]
> **How to use these notes:** This chapter is split into three power zones ā **DDL** (building structures), **DML** (changing data), and **Constraints** (enforcing rules). Board Exam Tips appear throughout ā don't skip them! Focus especially on **Section 11.3.1 (Constraints)** and **Section 11.4 (DML Commands)** ā examiner favourites every single year!
---
## 11.1 š Introduction
In the previous chapters, we learnt to **query** data with SELECT. But before any querying can happen, someone must have **created** the database, **created** the tables, and **inserted** the data ā and that someone could be you!
```mermaid
graph LR
C1["11.2\nšļø Create / Open /\nDrop Database"]
C2["11.3\nš Create Table\nwith Constraints"]
C3["11.4\nāļø DML: INSERT\nUPDATE DELETE"]
C4["11.5\nš§ More DDL:\nALTER and DROP Table"]
C1 --> C2 --> C3 --> C4
style C1 fill:#2196F3,color:#fff
style C2 fill:#9C27B0,color:#fff
style C3 fill:#FF9800,color:#fff
style C4 fill:#F44336,color:#fff
```
> Think of it this way: Chapter 11 taught you how to **read books in a library**. Chapter 12 teaches you how to **build the shelves, organise the sections, add new books, remove old ones, and rearrange everything** ā the full librarian role!
---
## 11.2 šļø Databases in MySQL
Before creating tables, you need a **database** to put them in. A MySQL server can host many databases simultaneously ā each isolated from the others.
### 11.2.1 Creating Databases šļø
The `CREATE DATABASE` command creates a fresh, empty database on the MySQL server.
**Syntax:**
```sql
CREATE DATABASE database_name;
```
**Examples:**
```sql
-- setup_databases.sql
CREATE DATABASE SchoolDB;
CREATE DATABASE CompanyDB;
CREATE DATABASE IF NOT EXISTS LibraryDB;
```
| Clause | Purpose |
| :--- | :--- |
| `CREATE DATABASE name;` | Creates the database; throws error if name already exists |
| `CREATE DATABASE IF NOT EXISTS name;` | Creates only if it doesn't already exist ā safer! |
> [!NOTE]
> **IF NOT EXISTS is your safety net! š§ **
> Running `CREATE DATABASE SchoolDB;` twice will throw an error the second time ā the database already exists. Using `IF NOT EXISTS` prevents this error, making scripts rerunnable without crashes. Always prefer this in real projects!
---
### 11.2.2 Opening Databases š
After creating a database, you must **select** it before any table operations can be performed on it.
**Syntax:**
```sql
USE database_name;
```
**Examples:**
```sql
-- open_database.sql
USE SchoolDB;
-- Confirm which database is currently active:
SELECT DATABASE();
```
| Command | Purpose |
| :--- | :--- |
| `USE database_name;` | Switches the active database for this session |
| `SELECT DATABASE();` | Shows the name of the currently active database |
| `SHOW DATABASES;` | Lists all databases on the server |
> [!WARNING]
> **Common Mistake**
> Every new MySQL session starts with **no database selected**. Forgetting `USE database_name;` before running CREATE TABLE or INSERT will always produce **"No database selected"** error. Make `USE` the very first command you type after logging in!
---
### 11.2.3 Removing Databases šļø
The `DROP DATABASE` command **permanently and irreversibly** deletes an entire database ā including every table and every row of data inside it.
**Syntax:**
```sql
DROP DATABASE database_name;
DROP DATABASE IF EXISTS database_name;
```
**Examples:**
```sql
-- remove_database.sql
DROP DATABASE OldTestDB;
DROP DATABASE IF EXISTS TempDB;
```
```mermaid
graph LR
DROP["š£ DROP DATABASE SchoolDB"]
GONE["ā SchoolDB is GONE\nAll tables deleted\nAll data deleted\nNO undo possible!"]
DROP --> GONE
style DROP fill:#F44336,color:#fff
style GONE fill:#9E9E9E,color:#fff
```
> [!WARNING]
> **DROP DATABASE is PERMANENT!**
> There is **no Recycle Bin, no Undo, no ROLLBACK** for DROP DATABASE. It is auto-committed the instant it runs. Always take a **backup** before dropping any database in a production environment. In exam contexts, know that this command deletes all tables and data inside too.
---
## 11.3 š Creating Tables
With an active database selected, `CREATE TABLE` defines a new table ā specifying each column's name, data type, size, and any constraints.
**Syntax:**
```sql
CREATE TABLE table_name (
column1_name datatype(size) [constraints],
column2_name datatype(size) [constraints],
...
[table_level_constraints]
);
```
**Complete Example ā Creating the Employee Table:**
```sql
-- create_employee.sql
CREATE TABLE Employee (
EmpId INT NOT NULL,
Name VARCHAR(30) NOT NULL,
Dept VARCHAR(20),
Salary DECIMAL(10,2) DEFAULT 0.00,
DOJ DATE,
City VARCHAR(20) DEFAULT 'Delhi',
PRIMARY KEY (EmpId)
);
```
**Breaking down each part:**
| Part | Example | Meaning |
| :--- | :--- | :--- |
| **Column name** | `EmpId` | The field's identifier |
| **Data type** | `INT`, `VARCHAR(30)` | What kind of data it holds |
| **NOT NULL** | `NOT NULL` | This column must always have a value |
| **DEFAULT** | `DEFAULT 0.00` | Auto-fills this value if none is provided |
| **PRIMARY KEY** | `PRIMARY KEY (EmpId)` | Uniquely identifies each row |
::: grid
::: card šļø | Column Definition | Every column needs at minimum a name and data type | EmpId INT
::: card š | Size and Precision | Many types need a size specification | VARCHAR(30), DECIMAL(10,2)
::: card š | Constraints | Optional rules that enforce data validity | NOT NULL, UNIQUE, DEFAULT
::: card š | Key Definitions | Usually placed at the end of the column list | PRIMARY KEY, FOREIGN KEY
:::
> [!IMPORTANT]
> **Board Exam Tip**
> "Write the SQL statement to create a table named Student with fields RollNo (integer, primary key), Name (string of 30 chars), and Marks (decimal)." ā **3-mark** question asked very often!
> Answer:
> ```sql
> CREATE TABLE Student (
> RollNo INT PRIMARY KEY,
> Name VARCHAR(30),
> Marks DECIMAL(5,2)
> );
> ```
---
### 11.3.1 Data Integrity Through Constraints š
**Constraints** are rules applied to columns (or the whole table) that prevent invalid or inconsistent data from being stored. They are the database's built-in quality control system.
```mermaid
graph TD
C["š CONSTRAINTS\nData Integrity Rules"]
C1["NOT NULL\nColumn must have a value"]
C2["UNIQUE\nNo two rows can have\nthe same value in this column"]
C3["PRIMARY KEY\nNot Null plus Unique\nIdentifies each row"]
C4["FOREIGN KEY\nLinks to Primary Key\nof another table"]
C5["CHECK\nValue must satisfy\na specified condition"]
C6["DEFAULT\nAuto-fills a value\nif none is provided"]
C --> C1
C --> C2
C --> C3
C --> C4
C --> C5
C --> C6
style C fill:#9C27B0,color:#fff
```
**All Six Constraints ā Detailed Explanation:**
#### NOT NULL
Ensures a column **cannot be left empty** ā every row must provide a value for this column.
```sql
-- not_null_demo.sql
CREATE TABLE Student (
RollNo INT NOT NULL,
Name VARCHAR(30) NOT NULL,
Marks INT -- NULL is allowed here
);
```
| Rule | Detail |
| :--- | :--- |
| **Effect** | INSERT/UPDATE fails if no value provided for this column |
| **Use when** | Column is essential ā name, ID, date of birth |
| **Without it** | Column can hold NULL (unknown/missing value) |
---
#### UNIQUE
Ensures **no two rows can have the same value** in this column ā but unlike PRIMARY KEY, UNIQUE columns CAN contain NULL values (multiple NULLs allowed).
```sql
-- unique_demo.sql
CREATE TABLE Employee (
EmpId INT PRIMARY KEY,
Email VARCHAR(50) UNIQUE,
Phone VARCHAR(15) UNIQUE
);
```
::: grid
::: card š | PRIMARY KEY vs UNIQUE | PRIMARY KEY = NOT NULL + UNIQUE; UNIQUE alone allows NULLs | A table can have only ONE Primary Key but MULTIPLE Unique constraints
::: card š¢ | Multiple UNIQUE Columns | A table can have many UNIQUE columns | Email AND Phone can both be UNIQUE independently
:::
---
#### PRIMARY KEY
The **most important constraint** ā uniquely identifies every row. It combines NOT NULL + UNIQUE into one powerful rule. Every table should have exactly one Primary Key.
```sql
-- primary_key_demo.sql
-- Method 1: Inline (single column PK)
CREATE TABLE Student (
RollNo INT PRIMARY KEY,
Name VARCHAR(30)
);
-- Method 2: Table-level (also required for COMPOSITE primary key)
CREATE TABLE Enrolment (
StudentId INT,
CourseId INT,
PRIMARY KEY (StudentId, CourseId)
);
```
| Property | Detail |
| :--- | :--- |
| **NULL allowed?** | No ā Primary Key column can NEVER be NULL |
| **Duplicates allowed?** | No ā every value must be unique |
| **Per table** | Only ONE Primary Key (but it can span multiple columns) |
| **Auto-creates** | An index on the PK column for faster lookups |
> [!IMPORTANT]
> **Board Exam Tip**
> "What is a Primary Key? Why is it used?" ā **2-mark** question.
> Answer: A **Primary Key** is a constraint applied to a column (or combination of columns) to **uniquely identify each row** in a table. It enforces two rules simultaneously: **NOT NULL** (the column must always have a value) and **UNIQUE** (no two rows can share the same value). Every table should have exactly one Primary Key.
---
#### FOREIGN KEY
A **Foreign Key** in one table is a column that **refers to the Primary Key of another table**, creating a link between the two tables and enforcing **Referential Integrity**.
```sql
-- foreign_key_demo.sql
CREATE TABLE Department (
DeptId INT PRIMARY KEY,
DeptName VARCHAR(30) NOT NULL
);
CREATE TABLE Employee (
EmpId INT PRIMARY KEY,
Name VARCHAR(30),
DeptId INT,
FOREIGN KEY (DeptId) REFERENCES Department(DeptId)
);
```
```mermaid
graph LR
DEPT["š Department Table\nDeptId PK"]
EMP["š Employee Table\nDeptId FK"]
EMP -->|"REFERENCES"| DEPT
style DEPT fill:#4CAF50,color:#fff
style EMP fill:#2196F3,color:#fff
```
| Rule | Detail |
| :--- | :--- |
| **Referential Integrity** | FK value MUST exist in the parent table's PK, or be NULL |
| **Prevents orphan records** | Can't add an Employee with a non-existent DeptId |
| **ON DELETE CASCADE** | If a Department row is deleted, all its Employees are deleted too |
> [!NOTE]
> **Foreign Key = The Link Between Tables! š§ **
> The Foreign Key is what makes a "relational" database truly relational ā it creates a verified, enforced connection between tables. Without FK constraints, tables are just isolated flat files that happen to share some column values.
---
#### CHECK
The **CHECK constraint** ensures a column's value satisfies a **user-defined condition** before being accepted.
```sql
-- check_constraint_demo.sql
CREATE TABLE Student (
RollNo INT PRIMARY KEY,
Name VARCHAR(30) NOT NULL,
Marks INT CHECK (Marks >= 0 AND Marks <= 100),
Age INT CHECK (Age >= 15)
);
```
| Example | What it prevents |
| :--- | :--- |
| `CHECK (Marks >= 0 AND Marks <= 100)` | Marks of -5 or 150 would be rejected |
| `CHECK (Age >= 18)` | Underage entries would be rejected |
| `CHECK (Salary > 0)` | Zero or negative salary would be rejected |
> [!NOTE]
> **CHECK Constraint in MySQL! š§ **
> MySQL versions **before 8.0.16** would accept the CHECK syntax but silently **ignore** it. From **MySQL 8.0.16 onwards**, CHECK constraints are fully enforced. Always check your MySQL version when relying on CHECK for data validation!
---
#### DEFAULT
The **DEFAULT constraint** automatically provides a pre-set value for a column when no value is explicitly given during INSERT.
```sql
-- default_constraint_demo.sql
CREATE TABLE Employee (
EmpId INT PRIMARY KEY,
Name VARCHAR(30) NOT NULL,
City VARCHAR(20) DEFAULT 'Delhi',
Salary DECIMAL(10,2) DEFAULT 0.00
);
-- This INSERT doesn't specify City or Salary:
INSERT INTO Employee (EmpId, Name) VALUES (101, 'Priya');
-- City automatically becomes 'Delhi', Salary becomes 0.00
```
| Property | Detail |
| :--- | :--- |
| **Triggers when** | INSERT statement doesn't provide a value for this column |
| **Can be overridden** | Yes ā explicitly providing a value in INSERT uses that value instead |
| **NULL vs DEFAULT** | Without DEFAULT, omitted columns become NULL; with DEFAULT, they get the set value |
> [!IMPORTANT]
> **Board Exam Tip**
> "Explain all constraints used in SQL with examples." ā **5-mark** question asked very often!
> You must cover: **NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT** ā one sentence plus one example for each.
**Constraints ā Complete Quick Reference:**
| Constraint | What It Does | Allows NULL? | Allows Duplicates? |
| :--- | :--- | :--- | :--- |
| **NOT NULL** | Column must have a value | No | Yes |
| **UNIQUE** | No duplicate values | Yes (multiple NULLs OK) | No |
| **PRIMARY KEY** | NOT NULL + UNIQUE | No | No |
| **FOREIGN KEY** | Value must exist in parent PK | Yes | Yes |
| **CHECK** | Value must pass a condition | Yes | Yes |
| **DEFAULT** | Auto-fills value when none given | N/A | N/A |
---
## 11.4 āļø Changing Data with DML Commands
**DML (Data Manipulation Language)** commands modify the **actual data** inside tables ā inserting new rows, updating existing rows, and deleting rows.
```mermaid
graph TD
DML["āļø DML COMMANDS"]
INS["š„ INSERT\nAdd new rows"]
UPD["š UPDATE\nChange existing data"]
DEL["šļø DELETE\nRemove rows"]
DML --> INS
DML --> UPD
DML --> DEL
style DML fill:#FF9800,color:#fff
style INS fill:#4CAF50,color:#fff
style UPD fill:#2196F3,color:#fff
style DEL fill:#F44336,color:#fff
```
---
### 11.4.1 Inserting Data into Table ā INSERT š„
The **INSERT INTO** command adds one or more new rows to a table.
**Syntax ā Two Forms:**
```sql
-- Form 1: Specify both columns and values (recommended)
INSERT INTO table_name (col1, col2, col3, ...)
VALUES (val1, val2, val3, ...);
-- Form 2: Provide values for ALL columns in table order
INSERT INTO table_name
VALUES (val1, val2, val3, ...);
```
**Inserting a Single Row:**
```sql
-- insert_single.sql
INSERT INTO Employee (EmpId, Name, Dept, Salary, DOJ, City)
VALUES (101, 'Priya Sharma', 'Sales', 45000.00, '2022-03-15', 'Delhi');
```
**Inserting Multiple Rows in One Statement:**
```sql
-- insert_multiple.sql
INSERT INTO Employee (EmpId, Name, Dept, Salary, DOJ, City)
VALUES
(102, 'Arjun Kumar', 'IT', 62000.00, '2021-07-01', 'Mumbai'),
(103, 'Sita Roy', 'HR', 38000.00, '2023-01-20', 'Delhi'),
(104, 'Rohan Das', 'IT', 71000.00, '2020-11-05', 'Bangalore');
```
**Inserting with NULL and DEFAULT:**
```sql
-- insert_null_default.sql
-- Explicitly inserting NULL:
INSERT INTO Employee (EmpId, Name, Dept, Salary, DOJ, City)
VALUES (105, 'Anjali Gupta', 'Sales', NULL, '2023-06-10', 'Mumbai');
-- Omitting columns that have DEFAULT values:
INSERT INTO Employee (EmpId, Name, Dept, DOJ)
VALUES (106, 'Vikram Singh', 'HR', '2022-09-25');
-- City and Salary will use their DEFAULT values automatically
```
::: grid
::: card ā
| Form 1 Named Columns | Explicit ā you control which columns get which values | Columns can be in any order; omitted columns get NULL/DEFAULT
::: card ā ļø | Form 2 All Columns | Must provide a value for EVERY column in exact table order | One missing or extra value = error
::: card š
| String and Date Values | Must be enclosed in single quotes | 'Priya', '2022-03-15'
::: card š¢ | Numeric Values | No quotes needed | 45000.00, 101
:::
> [!IMPORTANT]
> **Board Exam Tip**
> "Write an SQL statement to insert a new employee record." ā **2-mark** question, extremely common!
> Answer: `INSERT INTO Employee (EmpId, Name, Dept, Salary) VALUES (107, 'Neha Joshi', 'IT', 55000);`
> Key points: column names in `()`, VALUES keyword, string values in single quotes, numbers without quotes.
---
### 11.4.2 Modifying Data with UPDATE Command š
The **UPDATE** command changes the values of existing rows. Almost always used **with a WHERE clause** ā without it, EVERY row in the table gets updated!
**Syntax:**
```sql
UPDATE table_name
SET column1 = value1, column2 = value2, ...
[WHERE condition];
```
**Updating a Single Row:**
```sql
-- update_single.sql
UPDATE Employee
SET Salary = 50000.00
WHERE EmpId = 101;
-- Only Priya Sharma's salary changes; everyone else is untouched
```
**Updating Multiple Columns at Once:**
```sql
-- update_multiple_cols.sql
UPDATE Employee
SET Salary = 75000.00, City = 'Hyderabad'
WHERE EmpId = 104;
```
**Updating Multiple Rows with One Statement:**
```sql
-- update_multiple_rows.sql
UPDATE Employee
SET Salary = Salary * 1.10
WHERE Dept = 'IT';
-- ALL IT employees get a 10% salary increase in one query!
```
```mermaid
graph LR
U1["UPDATE Employee\nSET Salary = 50000\nWHERE EmpId = 101"]
R1["Only EmpId 101\nrow is updated"]
U2["UPDATE Employee\nSET Salary = 50000\n(No WHERE)"]
R2["ALL rows updated\nExtremely Dangerous!"]
U1 --> R1
U2 --> R2
style R1 fill:#4CAF50,color:#fff
style R2 fill:#F44336,color:#fff
```
> [!WARNING]
> **Always use WHERE with UPDATE!**
> Forgetting the WHERE clause in an UPDATE is one of the most dangerous mistakes in SQL. `UPDATE Employee SET Salary = 0;` will **set EVERY employee's salary to zero** with no confirmation prompt and no easy way to undo. Always write and double-check your WHERE condition before hitting Enter!
> [!IMPORTANT]
> **Board Exam Tip**
> "Write an SQL query to increase the salary of all employees in the 'HR' department by 5000." ā **2-mark** question!
> Answer: `UPDATE Employee SET Salary = Salary + 5000 WHERE Dept = 'HR';`
---
### 11.4.3 Deleting Data with DELETE Command šļø
The **DELETE** command removes existing rows from a table. Like UPDATE, it should almost always have a WHERE clause.
**Syntax:**
```sql
DELETE FROM table_name
[WHERE condition];
```
**Deleting a Single Specific Row:**
```sql
-- delete_single.sql
DELETE FROM Employee
WHERE EmpId = 105;
-- Only Anjali Gupta's record is removed
```
**Deleting Multiple Rows Matching a Condition:**
```sql
-- delete_multiple.sql
DELETE FROM Employee
WHERE Dept = 'HR';
-- All HR department employees are removed
```
**Deleting All Rows (No WHERE):**
```sql
-- delete_all.sql
DELETE FROM Employee;
-- ALL rows removed; table structure remains intact (unlike DROP TABLE)
```
**DELETE vs TRUNCATE vs DROP TABLE ā The Critical Three-Way Comparison:**
| Feature | DELETE | TRUNCATE | DROP TABLE |
| :--- | :--- | :--- | :--- |
| **Category** | DML | DDL | DDL |
| **Removes** | Selected rows | All rows | Entire table (structure + data) |
| **WHERE clause?** | Yes ā selective | No | No |
| **Can Rollback?** | Yes | No (auto-committed) | No (auto-committed) |
| **Table structure?** | Kept | Kept | Gone |
| **Speed** | Slower (logs each row) | Very Fast | Fast |
> [!IMPORTANT]
> **Board Exam Tip**
> "Differentiate between DELETE and TRUNCATE." ā **2-mark** question asked almost every year!
> Answer: **DELETE** is a DML command that removes specific rows using a WHERE clause and can be rolled back. **TRUNCATE** is a DDL command that removes ALL rows instantly, cannot be rolled back, and does not fire triggers. Both keep the table structure intact, unlike DROP TABLE.
---
## 11.5 š§ More DDL Commands
After a table is created, your requirements often change ā new columns needed, data type changes, constraints to add or remove. **ALTER TABLE** handles all of this. And when a table is no longer needed, **DROP TABLE** removes it entirely.
### 11.5.1 ALTER TABLE Command š§
**ALTER TABLE** modifies the **structure** of an existing table without losing its data (in most cases). It is the Swiss Army knife of DDL.
```mermaid
graph TD
ALTER["š§ ALTER TABLE"]
A1["ADD\nAdd a new column\nor constraint"]
A2["MODIFY\nChange a column's\ndata type or size"]
A3["CHANGE\nRename a column\nand optionally change type"]
A4["DROP COLUMN\nRemove a column\nand all its data"]
A5["ADD CONSTRAINT\nAdd PRIMARY KEY,\nFOREIGN KEY etc."]
A6["DROP CONSTRAINT\nRemove an existing\nconstraint"]
ALTER --> A1
ALTER --> A2
ALTER --> A3
ALTER --> A4
ALTER --> A5
ALTER --> A6
style ALTER fill:#9C27B0,color:#fff
```
**ADD ā Adding a New Column:**
```sql
-- alter_add_column.sql
ALTER TABLE Employee
ADD Email VARCHAR(50);
-- New 'Email' column added; existing rows get NULL for this column
ALTER TABLE Employee
ADD Gender CHAR(1) DEFAULT 'M' AFTER Name;
-- AFTER specifies where in column order it appears
```
**MODIFY ā Changing a Column's Data Type or Size:**
```sql
-- alter_modify.sql
ALTER TABLE Employee
MODIFY Name VARCHAR(50);
-- Expands Name column from VARCHAR(30) to VARCHAR(50)
ALTER TABLE Employee
MODIFY Salary DECIMAL(12,2) NOT NULL;
-- Changes precision AND adds NOT NULL constraint simultaneously
```
**CHANGE ā Renaming a Column (and Optionally Changing Type):**
```sql
-- alter_change.sql
ALTER TABLE Employee
CHANGE DOJ DateOfJoining DATE;
-- Renames column 'DOJ' to 'DateOfJoining'; data type stays DATE
ALTER TABLE Employee
CHANGE City Location VARCHAR(30);
-- Renames AND changes data type simultaneously
```
> [!NOTE]
> **MODIFY vs CHANGE! š§ **
> **MODIFY** changes a column's data type/size/constraints but **keeps the same name**.
> **CHANGE** can do everything MODIFY does **plus rename the column**.
> Use MODIFY for quick type changes; use CHANGE when you also need to rename!
**DROP COLUMN ā Removing a Column:**
```sql
-- alter_drop_column.sql
ALTER TABLE Employee
DROP COLUMN Email;
-- Email column and ALL its data are permanently deleted!
```
> [!WARNING]
> **DROP COLUMN is Permanent!**
> When you drop a column with ALTER TABLE, the column and **all data stored in it** are gone permanently. Unlike DELETE (which can be rolled back), ALTER TABLE DROP COLUMN is DDL ā auto-committed and irreversible. Always backup first!
**ADD CONSTRAINT ā Adding a Primary or Foreign Key After Creation:**
```sql
-- alter_add_constraint.sql
ALTER TABLE Employee
ADD PRIMARY KEY (EmpId);
ALTER TABLE Employee
ADD CONSTRAINT fk_dept
FOREIGN KEY (DeptId) REFERENCES Department(DeptId);
ALTER TABLE Employee
ADD UNIQUE (Email);
```
**DROP CONSTRAINT:**
```sql
-- alter_drop_constraint.sql
ALTER TABLE Employee
DROP PRIMARY KEY;
ALTER TABLE Employee
DROP FOREIGN KEY fk_dept;
ALTER TABLE Employee
DROP INDEX Email;
```
**Multiple Changes in a Single ALTER TABLE:**
```sql
-- alter_multiple.sql
ALTER TABLE Employee
ADD Phone VARCHAR(15),
MODIFY Name VARCHAR(50) NOT NULL,
DROP COLUMN Gender;
-- Multiple structural changes in one efficient statement!
```
**ALTER TABLE Operations ā Quick Summary:**
| Operation | Syntax | Use When |
| :--- | :--- | :--- |
| Add column | `ADD col_name datatype` | New field needed |
| Modify column type/size | `MODIFY col_name new_datatype` | Storage needs changed |
| Rename column | `CHANGE old_name new_name datatype` | Better naming needed |
| Remove column | `DROP COLUMN col_name` | Column no longer needed |
| Add PK | `ADD PRIMARY KEY (col)` | Forgot PK at creation |
| Add FK | `ADD FOREIGN KEY (col) REFERENCES tbl(col)` | Link to new parent table |
| Remove PK | `DROP PRIMARY KEY` | Changing key structure |
> [!IMPORTANT]
> **Board Exam Tip**
> "Write SQL to add a new column 'Email' of type VARCHAR(50) to an existing Employee table." ā **2-mark** question!
> Answer: `ALTER TABLE Employee ADD Email VARCHAR(50);`
>
> "Write SQL to change the data type of the 'Marks' column from INT to DECIMAL(5,2)." ā **2-mark** question!
> Answer: `ALTER TABLE Student MODIFY Marks DECIMAL(5,2);`
---
### 11.5.2 The DROP TABLE Command š£
**DROP TABLE** permanently deletes an entire table ā its structure, all its data, all its constraints, and all its indexes ā in one command.
**Syntax:**
```sql
DROP TABLE table_name;
DROP TABLE IF EXISTS table_name;
```
**Examples:**
```sql
-- drop_table.sql
DROP TABLE TempResults;
DROP TABLE IF EXISTS OldArchive;
-- Safe: no error if OldArchive doesn't exist
```
```mermaid
graph LR
BEFORE["š Employee Table\nStructure + 6 rows of data\nPrimary Key and Indexes"]
DROP["š£ DROP TABLE Employee;"]
AFTER["ā Table COMPLETELY GONE\nStructure deleted\nData deleted\nConstraints deleted\nIndexes deleted"]
BEFORE --> DROP --> AFTER
style DROP fill:#F44336,color:#fff
style AFTER fill:#9E9E9E,color:#fff
```
**DROP TABLE vs DROP DATABASE:**
| Command | Removes |
| :--- | :--- |
| `DROP TABLE Employee;` | Only the Employee table (structure + data) |
| `DROP DATABASE CompanyDB;` | The entire database, including ALL tables inside it |
**Final Three-Way Deletion Comparison:**
| Question | DELETE | TRUNCATE | DROP TABLE |
| :--- | :--- | :--- | :--- |
| Removes structure? | No | No | Yes |
| Removes data? | Selected rows | All rows | All rows |
| Table usable after? | Yes | Yes | No ā table gone |
| Can be undone? | Yes (ROLLBACK) | No | No |
> [!WARNING]
> **No Undo for DROP TABLE!**
> `DROP TABLE` is auto-committed DDL ā there is no ROLLBACK, no Recycle Bin, no recovery unless you have a backup. Before dropping any table in a real database, always run `SELECT * FROM table_name;` to confirm you're targeting the right table, and backup the data if you might need it.
> [!IMPORTANT]
> **Board Exam Tip**
> "What is the difference between DROP TABLE and TRUNCATE?" ā **2-mark** question.
> Answer: **TRUNCATE** removes all rows from the table but **keeps the table structure** intact ā the empty table still exists and can accept new data. **DROP TABLE** completely removes the table itself ā structure, data, constraints, and indexes are all deleted. The table ceases to exist entirely.
---
## ā ļø Common Errors and Misconceptions
| Misconception | Correct Fact |
| :--- | :--- |
| ā TRUNCATE and DELETE do the same thing | ā
DELETE is DML (can use WHERE, can ROLLBACK); TRUNCATE is DDL (removes all rows, no rollback) |
| ā PRIMARY KEY allows NULL | ā
PRIMARY KEY = NOT NULL + UNIQUE; NULL is never allowed in a PK column |
| ā UNIQUE and PRIMARY KEY are the same | ā
UNIQUE allows NULLs and a table can have multiple UNIQUE columns; PK cannot be NULL and is always exactly one per table |
| ā ALTER TABLE loses all existing data | ā
Most ALTER TABLE operations (ADD column, MODIFY type, CHANGE name) preserve existing data |
| ā FOREIGN KEY values must be unique | ā
FK values don't need to be unique ā many employees can share the same DeptId; only the parent PK must be unique |
| ā UPDATE without WHERE updates one row | ā
UPDATE without WHERE updates EVERY ROW in the table ā extremely dangerous! |
| ā DROP TABLE and DROP DATABASE do the same thing | ā
DROP TABLE removes one table; DROP DATABASE removes an entire database with all its tables |
| ā DEFAULT constraint prevents NULLs | ā
DEFAULT only kicks in when no value is provided in INSERT; explicitly inserting NULL still stores NULL |
---
## š Quick Revision ā Exam Ready!
**Database Commands ā One-Line Summary:**
- **CREATE DATABASE** ā Creates new empty database
- **USE** ā Selects database for the current session
- **DROP DATABASE** ā Permanently deletes entire database (no undo!)
**Table Creation Quick Reference:**
- `CREATE TABLE name (col type constraint, ..., PRIMARY KEY(col));`
- Constraints: NOT NULL | UNIQUE | PRIMARY KEY | FOREIGN KEY | CHECK | DEFAULT
**DML Commands ā One-Line Summary:**
- **INSERT INTO** ā Adds new rows; strings in quotes, numbers without
- **UPDATE SET WHERE** ā Modifies existing rows; WHERE is critical!
- **DELETE FROM WHERE** ā Removes rows; no WHERE = removes ALL rows
**ALTER TABLE Quick Reference:**
| Need | Command |
| :--- | :--- |
| Add column | `ALTER TABLE t ADD col datatype;` |
| Change type | `ALTER TABLE t MODIFY col new_type;` |
| Rename column | `ALTER TABLE t CHANGE old new datatype;` |
| Remove column | `ALTER TABLE t DROP COLUMN col;` |
| Add PK | `ALTER TABLE t ADD PRIMARY KEY (col);` |
**The Big Three Deletion Commands:**
| Feature | DELETE | TRUNCATE | DROP TABLE |
| :--- | :--- | :--- | :--- |
| Category | DML | DDL | DDL |
| WHERE supported | Yes | No | No |
| Rollback possible | Yes | No | No |
| Structure stays | Yes | Yes | No |
---
## šÆ Sample Board Exam Questions
### Q1: Very Short Answer [1 mark each]
a) Which SQL command is used to select an active database?
**ā USE database_name;**
b) Which constraint ensures no two rows can have the same value in a column?
**ā UNIQUE**
c) Which DML command is used to modify existing data in a table?
**ā UPDATE**
d) What is the purpose of the DEFAULT constraint?
**ā It automatically assigns a pre-defined value to a column when no value is provided during INSERT.**
e) Which ALTER TABLE clause is used to rename a column?
**ā CHANGE**
---
### Q2: Short Answer [2 marks]
**Q: Differentiate between PRIMARY KEY and UNIQUE constraints.**
| Feature | PRIMARY KEY | UNIQUE |
| :--- | :--- | :--- |
| **NULL allowed?** | No | Yes (multiple NULLs allowed) |
| **Per table** | Only ONE | Multiple UNIQUE columns allowed |
| **Implied rules** | NOT NULL + UNIQUE combined | Only UNIQUE |
---
### Q3: Practical SQL [3 marks]
**Q: Write SQL statements to:**
a) Create a table `Student` with RollNo (INT, PK), Name (VARCHAR 30, NOT NULL), Marks (DECIMAL 5,2), Grade (CHAR 1, DEFAULT 'C').
b) Insert a record: RollNo=1, Name='Priya', Marks=91.5
c) Update Grade to 'A' for students with Marks >= 90.
```sql
-- a) Create table
CREATE TABLE Student (
RollNo INT PRIMARY KEY,
Name VARCHAR(30) NOT NULL,
Marks DECIMAL(5,2),
Grade CHAR(1) DEFAULT 'C'
);
-- b) Insert record
INSERT INTO Student (RollNo, Name, Marks)
VALUES (1, 'Priya', 91.5);
-- c) Update grade
UPDATE Student
SET Grade = 'A'
WHERE Marks >= 90;
```
---
### Q4: Short Answer [2 marks]
**Q: Write SQL to add a column `Phone` (VARCHAR 15) to an existing Employee table, and then remove the column `City`.**
```sql
ALTER TABLE Employee ADD Phone VARCHAR(15);
ALTER TABLE Employee DROP COLUMN City;
```
---
### Q5: Output Based Question [2 marks]
**Q: What will happen when the following statement is executed?**
```sql
DELETE FROM Employee;
```
**ā All rows from the Employee table will be deleted. However, the table structure (columns, constraints) will remain intact ā the table will exist but be empty. Unlike DROP TABLE, the table itself is not removed from the database.**
---
## āļø Practice Problems
1. Write SQL to create a `Library` database, select it, and then create a `Books` table with: BookId (INT, PK), Title (VARCHAR 50, NOT NULL), Author (VARCHAR 30), Price (DECIMAL 7,2, CHECK Price > 0), Available (BOOLEAN, DEFAULT TRUE).
2. Insert three records into the Books table, one of which has a NULL Author.
3. Write SQL to give a 15% price increase to all books priced below 300.
4. Write SQL to delete all books where Available = FALSE.
5. Add a new column `ISBN` (CHAR 13, UNIQUE) to the Books table using ALTER TABLE.
6. Rename the column `Available` to `InStock` using ALTER TABLE CHANGE.
7. Write the difference between `DROP TABLE Books;` and `DELETE FROM Books;` ā what state is the database in after each?
8. A table `Employee` was created without a PRIMARY KEY. Write the ALTER TABLE command to add EmpId as the Primary Key.
9. Write SQL to create a `Marks` table with a FOREIGN KEY that links `StudentId` to the `Student` table's `RollNo` column.
10. Without running the query, predict what `UPDATE Employee SET Dept = 'IT';` does (no WHERE clause). How would you fix it to only update employees currently in the 'HR' department?
Back to List
Calculating...
UNIT 3 : CH 11
Jul 01, 2026