## 1. 📖 Definitions (Key Terminology)
* **🗄️ Database:** An organized collection of structured data.
* **⚙️ DBMS (Database Management System):** Software that manages databases (e.g., MySQL, PostgreSQL).
* **🔗 RDBMS (Relational DBMS):** A DBMS that organizes data into **Tables** (Relations) linked by common fields.
* **📝 Relation:** The formal name for a **Table**.
* **➡️ Tuple:** A **Row** in a table. It represents a single, complete record of data.
* **⬇️ Attribute:** A **Column** in a table. It represents a specific characteristic (e.g., Name, Age).
* **🔢 Degree:** The number of attributes (columns) in a relation.
* **bar chart icon Cardinality:** The number of tuples (rows) in a relation.
* **🗣️ SQL (Structured Query Language):** The standard language to interact with RDBMS.
---
## 2. 🧠 Explanation of Concepts
### 🏗️ The Relational Data Model
Data is stored in 2D tables. The "Keys" are crucial for maintaining data integrity:
1. **🔑 Primary Key (PK):** A column that **uniquely identifies** each row.
* *Rule:* Cannot be `NULL` and cannot contain duplicates.
* *Example:* `AdmNo` in a Student table.
2. **🥈 Candidate Key:** All columns that *could* be a Primary Key (they are eligible).
3. **🥉 Alternate Key:** A Candidate Key that was **not** selected as the Primary Key.
4. **🔗 Foreign Key (FK):** A column that links to the Primary Key of another table. It enforces **Referential Integrity**.
### 🔢 MySQL Data Types (Deep Dive)
* **Numeric:**
* `INT` or `INTEGER`: Whole numbers.
* `DECIMAL(M, D)`: Exact decimals. *M* is total digits, *D* is digits after the decimal.
* *Example:* `DECIMAL(5,2)` can store `123.45`.
* **String:**
* `CHAR(n)`: **Fixed** length. Fastest for fixed-size data (like Pincodes).
* `VARCHAR(n)`: **Variable** length. Saves memory for varying text (like Names).
* **Date:**
* `DATE`: Format is strictly **'YYYY-MM-DD'**.
### 🛠️ Types of SQL Commands
| Type | Full Form | Purpose | Examples |
| --- | --- | --- | --- |
| **DDL** | Data Definition Language | Defines structure | `CREATE`, `ALTER`, `DROP` |
| **DML** | Data Manipulation Language | Handles data | `INSERT`, `UPDATE`, `DELETE`, `SELECT` |
---
## 3. ⚡ Important Points to Remember
* **🔠 Case Insensitivity:** SQL keywords (`SELECT`, `from`, `Where`) are **not** case-sensitive. However, data inside quotes (`'Amit'` vs `'amit'`) **is** case-sensitive depending on the OS.
* **⛔ The NULL Concept:**
* `NULL` ≠ `0`
* `NULL` ≠ `' '` (Empty Space)
* `NULL` means **"Unknown"** or **"Not Applicable"**.
* *Math with NULL:* `5 + NULL = NULL` (Any calculation with NULL results in NULL).
* **🃏 Wildcards (Used with LIKE):**
* `%`: Represents **0, 1, or Many** characters.
* `_`: Represents exactly **1** character.
---
## 4. ⌨️ Syntax & 5. 🧪 Examples
### A. 📂 Managing Databases
```sql
CREATE DATABASE SchoolDB; -- Create
USE SchoolDB; -- Open/Select for use
```
### B. 🏗️ Creating Tables (With Constraints)
**Syntax:** `CREATE TABLE (col type constraints...);`
```sql
CREATE TABLE Student (
RollNo INT PRIMARY KEY, -- Unique ID
Name VARCHAR(30) NOT NULL, -- Cannot be blank
Stream VARCHAR(20) DEFAULT 'Science', -- Default value if skipped
Fee DECIMAL(10, 2) CHECK(Fee > 0) -- Validation check
);
```
### C. 📥 Inserting Data
**Method 1: All Columns**
```sql
INSERT INTO Student VALUES (1, 'Rohan', 'Science', 2500.00);
```
**Method 2: Specific Columns (Recommended)**
```sql
INSERT INTO Student (RollNo, Name) VALUES (2, 'Priya');
-- Stream takes 'Science' (Default), Fee takes NULL
```
### D. 🔍 Making Queries (SELECT - The Heart of SQL)
#### Basic Selection
```sql
SELECT * FROM Student; -- All data
SELECT Name, Fee FROM Student; -- Specific columns
```
#### Eliminating Duplicates (DISTINCT)
```sql
SELECT DISTINCT Stream FROM Student;
-- If 10 students are in 'Science', output shows 'Science' only once.
```
#### Select from All Rows (ALL)
*Note: `ALL` is the default behavior. It includes duplicates.*
```sql
SELECT ALL Stream FROM Student; -- Same as SELECT Stream...
```
#### Column Aliases (Renaming for Display)
Using `AS` gives a temporary name to a column in the output.
```sql
SELECT Name, Fee * 12 AS Annual_Fee FROM Student;
```
#### Range Search (BETWEEN)
* Includes both start and end values.
```sql
SELECT * FROM Student WHERE Fee BETWEEN 2000 AND 3000;
-- Equivalent to: Fee >= 2000 AND Fee <= 3000
```
#### List Search (IN)
* Matches any value in the list.
```sql
SELECT * FROM Student WHERE Stream IN ('Science', 'Commerce');
-- Equivalent to: Stream = 'Science' OR Stream = 'Commerce'
```
#### Pattern Matching (LIKE)
```sql
-- Name starts with 'A'
SELECT * FROM Student WHERE Name LIKE 'A%';
-- Name has 'a' as the second letter
SELECT * FROM Student WHERE Name LIKE '_a%';
-- Name is exactly 4 letters long
SELECT * FROM Student WHERE Name LIKE '____';
```
#### Handling NULL
* **Wrong:** `WHERE Fee = NULL` ❌
* **Correct:** `WHERE Fee IS NULL` ✅
```sql
SELECT * FROM Student WHERE Fee IS NULL;
SELECT * FROM Student WHERE Fee IS NOT NULL;
```
### E. 🔄 Inserting into Another Table
Useful for backing up data.
```sql
INSERT INTO Toppers (Name, Marks)
SELECT Name, Marks FROM Student WHERE Marks > 90;
```
### F. ✏️ Modifying Data (UPDATE)
**⚠️ Warning:** Always use a `WHERE` clause, or you will update **all** rows!
```sql
UPDATE Student
SET Fee = Fee + 500
WHERE Stream = 'Commerce';
```
### G. 🗑️ Deleting Data (DELETE)
```sql
DELETE FROM Student WHERE RollNo = 10; -- Deletes one row
DELETE FROM Student; -- Deletes ALL rows (Empty table remains)
```
### H. 🔧 Altering Tables (ALTER)
Used to change the **structure**, not the data.
```sql
-- 1. Add a new column
ALTER TABLE Student ADD Email VARCHAR(50);
-- 2. Modify datatype/size of existing column
ALTER TABLE Student MODIFY Name VARCHAR(100);
-- 3. Delete a column
ALTER TABLE Student DROP COLUMN Email;
```
### I. 💣 Dropping Tables (DROP)
```sql
DROP TABLE Student; -- Deletes table structure + data completely.
```
---
## 6. 📊 Tables & Diagrams: Critical Comparisons
### 🆚 CHAR vs VARCHAR (Very Important)
| Feature | `CHAR` | `VARCHAR` |
| --- | --- | --- |
| **Type** | Fixed Length | Variable Length |
| **Memory** | Wastes memory if data is short (pads with spaces). | Efficient. Uses only required memory + 1 byte for length. |
| **Speed** | Faster processing. | Slightly slower processing. |
| **Analogy** | A box of fixed size. | A flexible drawstring bag. |
### 🆚 DELETE vs DROP
| `DELETE` | `DROP` |
| --- | --- |
| Deletes **Rows** (Data). | Deletes **Table Structure**. |
| Can use `WHERE` condition. | Cannot use `WHERE`. |
| DML Command. | DDL Command. |
---
## 7. 🚫 Common Errors / Misconceptions
* **🚫 Error:** Writing `SELECT * FROM Student WHERE Name = NULL`.
* **✅ Fix:** `SELECT * FROM Student WHERE Name IS NULL`.
* **🚫 Error:** Thinking `DELETE` deletes the table file.
* **✅ Fix:** `DELETE` only empties the table. `DROP` destroys it.
* **🚫 Error:** Using double quotes `"` for strings in strict SQL.
* **✅ Fix:** Always use single quotes `'` for strings and dates (e.g., `'2023-12-31'`).
* **🚫 Error:** Confusing `ALTER` and `UPDATE`.
* **✅ Fix:** `ALTER` changes the **design** (columns). `UPDATE` changes the **content** (rows).
---
## 8. 🚀 Exam-Oriented Short Notes (Cheatsheet)
1. **Cartesian Product:** If Table A has `R1` rows and `C1` columns, and Table B has `R2` rows and `C2` columns:
* **Resultant Degree (Columns):** C1 + C2
* **Resultant Cardinality (Rows):** R1 \times R2
2. **Referential Integrity:** You cannot delete a record from a **Parent Table** (Primary Key side) if a related record exists in the **Child Table** (Foreign Key side).
3. **Default Date Format:** Remember MySQL stores dates as **Year-Month-Day** (`YYYY-MM-DD`).
4. **Selection vs Projection:**
* **Selection:** Choosing Rows (`WHERE` clause).
* **Projection:** Choosing Columns (`SELECT Name, Age...`).
* **🗄️ Database:** An organized collection of structured data.
* **⚙️ DBMS (Database Management System):** Software that manages databases (e.g., MySQL, PostgreSQL).
* **🔗 RDBMS (Relational DBMS):** A DBMS that organizes data into **Tables** (Relations) linked by common fields.
* **📝 Relation:** The formal name for a **Table**.
* **➡️ Tuple:** A **Row** in a table. It represents a single, complete record of data.
* **⬇️ Attribute:** A **Column** in a table. It represents a specific characteristic (e.g., Name, Age).
* **🔢 Degree:** The number of attributes (columns) in a relation.
* **bar chart icon Cardinality:** The number of tuples (rows) in a relation.
* **🗣️ SQL (Structured Query Language):** The standard language to interact with RDBMS.
---
## 2. 🧠 Explanation of Concepts
### 🏗️ The Relational Data Model
Data is stored in 2D tables. The "Keys" are crucial for maintaining data integrity:
1. **🔑 Primary Key (PK):** A column that **uniquely identifies** each row.
* *Rule:* Cannot be `NULL` and cannot contain duplicates.
* *Example:* `AdmNo` in a Student table.
2. **🥈 Candidate Key:** All columns that *could* be a Primary Key (they are eligible).
3. **🥉 Alternate Key:** A Candidate Key that was **not** selected as the Primary Key.
4. **🔗 Foreign Key (FK):** A column that links to the Primary Key of another table. It enforces **Referential Integrity**.
### 🔢 MySQL Data Types (Deep Dive)
* **Numeric:**
* `INT` or `INTEGER`: Whole numbers.
* `DECIMAL(M, D)`: Exact decimals. *M* is total digits, *D* is digits after the decimal.
* *Example:* `DECIMAL(5,2)` can store `123.45`.
* **String:**
* `CHAR(n)`: **Fixed** length. Fastest for fixed-size data (like Pincodes).
* `VARCHAR(n)`: **Variable** length. Saves memory for varying text (like Names).
* **Date:**
* `DATE`: Format is strictly **'YYYY-MM-DD'**.
### 🛠️ Types of SQL Commands
| Type | Full Form | Purpose | Examples |
| --- | --- | --- | --- |
| **DDL** | Data Definition Language | Defines structure | `CREATE`, `ALTER`, `DROP` |
| **DML** | Data Manipulation Language | Handles data | `INSERT`, `UPDATE`, `DELETE`, `SELECT` |
---
## 3. ⚡ Important Points to Remember
* **🔠 Case Insensitivity:** SQL keywords (`SELECT`, `from`, `Where`) are **not** case-sensitive. However, data inside quotes (`'Amit'` vs `'amit'`) **is** case-sensitive depending on the OS.
* **⛔ The NULL Concept:**
* `NULL` ≠ `0`
* `NULL` ≠ `' '` (Empty Space)
* `NULL` means **"Unknown"** or **"Not Applicable"**.
* *Math with NULL:* `5 + NULL = NULL` (Any calculation with NULL results in NULL).
* **🃏 Wildcards (Used with LIKE):**
* `%`: Represents **0, 1, or Many** characters.
* `_`: Represents exactly **1** character.
---
## 4. ⌨️ Syntax & 5. 🧪 Examples
### A. 📂 Managing Databases
```sql
CREATE DATABASE SchoolDB; -- Create
USE SchoolDB; -- Open/Select for use
```
### B. 🏗️ Creating Tables (With Constraints)
**Syntax:** `CREATE TABLE
```sql
CREATE TABLE Student (
RollNo INT PRIMARY KEY, -- Unique ID
Name VARCHAR(30) NOT NULL, -- Cannot be blank
Stream VARCHAR(20) DEFAULT 'Science', -- Default value if skipped
Fee DECIMAL(10, 2) CHECK(Fee > 0) -- Validation check
);
```
### C. 📥 Inserting Data
**Method 1: All Columns**
```sql
INSERT INTO Student VALUES (1, 'Rohan', 'Science', 2500.00);
```
**Method 2: Specific Columns (Recommended)**
```sql
INSERT INTO Student (RollNo, Name) VALUES (2, 'Priya');
-- Stream takes 'Science' (Default), Fee takes NULL
```
### D. 🔍 Making Queries (SELECT - The Heart of SQL)
#### Basic Selection
```sql
SELECT * FROM Student; -- All data
SELECT Name, Fee FROM Student; -- Specific columns
```
#### Eliminating Duplicates (DISTINCT)
```sql
SELECT DISTINCT Stream FROM Student;
-- If 10 students are in 'Science', output shows 'Science' only once.
```
#### Select from All Rows (ALL)
*Note: `ALL` is the default behavior. It includes duplicates.*
```sql
SELECT ALL Stream FROM Student; -- Same as SELECT Stream...
```
#### Column Aliases (Renaming for Display)
Using `AS` gives a temporary name to a column in the output.
```sql
SELECT Name, Fee * 12 AS Annual_Fee FROM Student;
```
#### Range Search (BETWEEN)
* Includes both start and end values.
```sql
SELECT * FROM Student WHERE Fee BETWEEN 2000 AND 3000;
-- Equivalent to: Fee >= 2000 AND Fee <= 3000
```
#### List Search (IN)
* Matches any value in the list.
```sql
SELECT * FROM Student WHERE Stream IN ('Science', 'Commerce');
-- Equivalent to: Stream = 'Science' OR Stream = 'Commerce'
```
#### Pattern Matching (LIKE)
```sql
-- Name starts with 'A'
SELECT * FROM Student WHERE Name LIKE 'A%';
-- Name has 'a' as the second letter
SELECT * FROM Student WHERE Name LIKE '_a%';
-- Name is exactly 4 letters long
SELECT * FROM Student WHERE Name LIKE '____';
```
#### Handling NULL
* **Wrong:** `WHERE Fee = NULL` ❌
* **Correct:** `WHERE Fee IS NULL` ✅
```sql
SELECT * FROM Student WHERE Fee IS NULL;
SELECT * FROM Student WHERE Fee IS NOT NULL;
```
### E. 🔄 Inserting into Another Table
Useful for backing up data.
```sql
INSERT INTO Toppers (Name, Marks)
SELECT Name, Marks FROM Student WHERE Marks > 90;
```
### F. ✏️ Modifying Data (UPDATE)
**⚠️ Warning:** Always use a `WHERE` clause, or you will update **all** rows!
```sql
UPDATE Student
SET Fee = Fee + 500
WHERE Stream = 'Commerce';
```
### G. 🗑️ Deleting Data (DELETE)
```sql
DELETE FROM Student WHERE RollNo = 10; -- Deletes one row
DELETE FROM Student; -- Deletes ALL rows (Empty table remains)
```
### H. 🔧 Altering Tables (ALTER)
Used to change the **structure**, not the data.
```sql
-- 1. Add a new column
ALTER TABLE Student ADD Email VARCHAR(50);
-- 2. Modify datatype/size of existing column
ALTER TABLE Student MODIFY Name VARCHAR(100);
-- 3. Delete a column
ALTER TABLE Student DROP COLUMN Email;
```
### I. 💣 Dropping Tables (DROP)
```sql
DROP TABLE Student; -- Deletes table structure + data completely.
```
---
## 6. 📊 Tables & Diagrams: Critical Comparisons
### 🆚 CHAR vs VARCHAR (Very Important)
| Feature | `CHAR` | `VARCHAR` |
| --- | --- | --- |
| **Type** | Fixed Length | Variable Length |
| **Memory** | Wastes memory if data is short (pads with spaces). | Efficient. Uses only required memory + 1 byte for length. |
| **Speed** | Faster processing. | Slightly slower processing. |
| **Analogy** | A box of fixed size. | A flexible drawstring bag. |
### 🆚 DELETE vs DROP
| `DELETE` | `DROP` |
| --- | --- |
| Deletes **Rows** (Data). | Deletes **Table Structure**. |
| Can use `WHERE` condition. | Cannot use `WHERE`. |
| DML Command. | DDL Command. |
---
## 7. 🚫 Common Errors / Misconceptions
* **🚫 Error:** Writing `SELECT * FROM Student WHERE Name = NULL`.
* **✅ Fix:** `SELECT * FROM Student WHERE Name IS NULL`.
* **🚫 Error:** Thinking `DELETE` deletes the table file.
* **✅ Fix:** `DELETE` only empties the table. `DROP` destroys it.
* **🚫 Error:** Using double quotes `"` for strings in strict SQL.
* **✅ Fix:** Always use single quotes `'` for strings and dates (e.g., `'2023-12-31'`).
* **🚫 Error:** Confusing `ALTER` and `UPDATE`.
* **✅ Fix:** `ALTER` changes the **design** (columns). `UPDATE` changes the **content** (rows).
---
## 8. 🚀 Exam-Oriented Short Notes (Cheatsheet)
1. **Cartesian Product:** If Table A has `R1` rows and `C1` columns, and Table B has `R2` rows and `C2` columns:
* **Resultant Degree (Columns):** C1 + C2
* **Resultant Cardinality (Rows):** R1 \times R2
2. **Referential Integrity:** You cannot delete a record from a **Parent Table** (Primary Key side) if a related record exists in the **Child Table** (Foreign Key side).
3. **Default Date Format:** Remember MySQL stores dates as **Year-Month-Day** (`YYYY-MM-DD`).
4. **Selection vs Projection:**
* **Selection:** Choosing Rows (`WHERE` clause).
* **Projection:** Choosing Columns (`SELECT Name, Age...`).