Data Insights MySQL Masterclass — Part 1

MySQL Fundamentals: Database, Architecture, Data Types & Constraints

MySQL ki duniya mein pehla kadam — Database kya hota hai, RDBMS kaise kaam karta hai, Data Types kaise select karte hain aur Constraints se data integrity kaise maintain hoti hai. Sab kuch real-world examples ke saath seekhiye Data Insights par.

📑 Is Part 1 Mein Aap Kya Sikhenge:

MySQL ka foundation — database concepts se lekar data integrity tak, har cheez step-by-step:

• Topic 1: Database Kya Hai — DBMS vs RDBMS
• Topic 2: MySQL Architecture — Client/Server Model
• Topic 3: MySQL Data Types — INT, VARCHAR, DATE, TEXT, BLOB & more
• Topic 4: Constraints — PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, DEFAULT, CHECK
• Topic 5: DDL Commands — CREATE, ALTER, DROP, TRUNCATE
• Topic 6: DML Commands — INSERT, UPDATE, DELETE
• Topic 7: DQL Command — SELECT (Introduction)
• Topic 8: DCL Commands — GRANT, REVOKE
• Topic 9: TCL Commands — COMMIT, ROLLBACK, SAVEPOINT

text

🔍 Definition: A Database is an organized collection of structured data stored electronically in a computer system, managed by a Database Management System (DBMS). It allows users to efficiently store, retrieve, modify and delete data.

🎯 Samjho Simple Bhasha Mein: Socho tumhare paas ek school hai. Uss school ke saare students ka naam, roll number, class, marks — yeh sab ek jagah organized tarike se rakhna hai. Agar tum register mein likho toh woh manual database hai. Agar computer mein store karo toh woh digital database hai. Aur agar MySQL jaisi software se manage karo toh woh RDBMS hai.

💡 DBMS vs RDBMS — Key Difference:
DBMS (Database Management System): Data ko files ke form mein store karta hai. Tables ke beech koi relationship nahi hoti. Example: Microsoft Access, File Systems.

RDBMS (Relational DBMS): Data ko tables (rows & columns) mein store karta hai aur tables ke beech relationships (Primary Key - Foreign Key) hoti hain. Example: MySQL, PostgreSQL, Oracle, SQL Server.

📊 DBMS vs RDBMS — Comparison Table:

Feature	DBMS	RDBMS
Data Storage	Files / Hierarchical	Tables (Rows & Columns)
Relationships	Not Supported	Foreign Key Relationships
ACID Properties	Not Guaranteed	Fully Supported
Normalization	Not Applied	1NF, 2NF, 3NF Applied
Example	XML, File System	MySQL, PostgreSQL, Oracle
Data Integrity	Low	High (Constraints)

💻 Real-World Example — MySQL Database Creation:

-- Step 1: Naya database banao
CREATE DATABASE school_management;

-- Step 2: Us database ko select karo
USE school_management;

-- Step 3: Verify karo ki database ban gaya
SHOW DATABASES;

📊 Expected Output:

5 rows in set (0.01 sec)

⚠️ Common Mistakes:

• Mistake: Database name mein spaces dena → CREATE DATABASE school management ❌
  Fix: Underscore use karo → CREATE DATABASE school_management ✅
• Mistake: USE command bhool jaana aur directly table banana → Error aayega "No database selected".
  Fix: Pehle hamesha USE database_name; run karo.
• Mistake: DBMS aur RDBMS ko same samajhna → Interview mein seedha marks katenge.

💬 Interview Questions:

text

🔍 Definition: MySQL follows a Client-Server Architecture where the client sends SQL queries to the MySQL Server, which processes those queries, interacts with the storage engine, and returns results back to the client. The architecture has three main layers: Connection Layer, SQL Layer, and Storage Engine Layer.

🎯 Samjho Simple Bhasha Mein: Socho tum ek restaurant mein ho. Tum (Client) waiter (Connection Layer) ko order dete ho. Waiter woh order chef (SQL Layer/Query Processor) ko deta hai. Chef kitchen ke storage (Storage Engine — InnoDB) se ingredients nikaal ke dish banata hai. Phir waiter tumhe dish serve karta hai (Result). MySQL bilkul aise hi kaam karta hai!

💡 Architecture ke 3 Layers:

Layer 1 — Connection Layer: Client ka connection handle karta hai. Authentication (username/password), Thread management aur connection pooling yahan hoti hai.

Layer 2 — SQL Layer (Brain): Query parsing, optimization, caching aur execution yahan hoti hai. Yeh decide karta hai ki query ko kaise efficiently run karna hai.

Layer 3 — Storage Engine Layer: Actual data disk par read/write yahan hota hai. MySQL ka default engine InnoDB hai jo transactions aur foreign keys support karta hai.

📊 MySQL Query Execution Flow:

┌─────────────────┐
│ CLIENT │ (MySQL Workbench, Terminal, PHP, Python)
│ SQL Query → │
└────────┬────────┘
│
▼
┌─────────────────┐
│ CONNECTION LAYER│ (Authentication + Thread Creation)
└────────┬────────┘
│
▼
┌─────────────────┐
│ SQL LAYER │ (Parser → Optimizer → Executor)
│ │
│ Parser: │ Query ko tokens mein todta hai
│ Optimizer: │ Best execution plan decide karta hai
│ Executor: │ Plan ko run karta hai
└────────┬────────┘
│
▼
┌─────────────────┐
│ STORAGE ENGINE │ (InnoDB — Default)
│ │ Data Read/Write on Disk
│ │ Transaction Support (ACID)
│ │ Foreign Key Support
└─────────────────┘
│
▼
┌─────────────────┐
│ RESULT SET │ → Back to Client
└─────────────────┘

📊 Storage Engines Comparison:

Feature	InnoDB (Default)	MyISAM
Transactions	✅ Supported	❌ Not Supported
Foreign Keys	✅ Supported	❌ Not Supported
Row-Level Locking	✅ Yes	❌ Table-Level Only
Crash Recovery	✅ Auto Recovery	❌ Manual Repair
Full-Text Search	✅ (MySQL 5.6+)	✅ Supported
Best For	Banking, E-commerce	Read-Heavy, Logging

💻 Real-World Code Examples:

Example 1: Check karo ki current storage engine kaunsa hai.

-- Default storage engine check karo
SHOW ENGINES;

-- Specific table ka engine check karo
SHOW TABLE STATUS
FROM school_management;

Example 2: MySQL server ki connection details aur version check karo.

-- MySQL version check
SELECT VERSION();

-- Current user check
SELECT CURRENT_USER();

-- Current database check
SELECT DATABASE();

📊 Expected Output:

+-----------+
| VERSION() |
+-----------+
| 8.0.35 |
+-----------+

+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost |
+----------------+

+-------------------+
| DATABASE() |
+-------------------+
| school_management |
+-------------------+

⚠️ Common Mistakes:

• Mistake: MyISAM engine par banking system banana → Transactions support nahi hai, data corrupt ho sakta hai.
  Fix: Hamesha InnoDB use karo jab ACID compliance chahiye.
• Mistake: MySQL ko sirf ek language samajhna.
  Fix: MySQL ek RDBMS software hai, SQL uski language hai.
• Mistake: Connection close nahi karna → Server par unnecessary threads create hoti hain.
  Fix: Application code mein hamesha connection properly close karo.

💬 Interview Questions:

text

🔍 Definition: A Data Type defines the kind of value a column can hold in a MySQL table. It tells MySQL how much memory to allocate, what operations are allowed and how to validate the incoming data. Choosing the right data type is critical for performance and storage optimization.

🎯 Samjho Simple Bhasha Mein: Jaise real life mein har cheez ka ek type hota hai — naam text hota hai, age number hoti hai, date of birth date hoti hai — MySQL mein bhi har column ko batana padta hai ki woh kaunsa type ka data rakhega. Agar tumne age ke column mein "Hello" daal diya toh MySQL reject kar dega kyunki woh column sirf numbers expect karta hai.

💡 Important Rule: Hamesha smallest possible data type use karo jo tumhara data hold kar sake. Agar age store karni hai toh TINYINT (0-255) use karo, INT nahi — isse storage aur query performance dono improve hoti hai.

📊 Category 1: Numeric Data Types

Data Type	Storage	Range	Use Case
TINYINT	1 byte	-128 to 127	Age, Status flags
SMALLINT	2 bytes	-32,768 to 32,767	Year, Small counts
MEDIUMINT	3 bytes	-8M to 8M	Medium counters
INT	4 bytes	-2.1B to 2.1B	IDs, Salary, Quantity
BIGINT	8 bytes	-9.2 × 10¹⁸	Phone numbers, huge IDs
DECIMAL(p,s)	Variable	Exact precision	Money, Price (exact)
FLOAT	4 bytes	Approximate	Scientific calculations
DOUBLE	8 bytes	Approximate (larger)	High-precision calculations

📊 Category 2: String Data Types

Data Type	Max Length	Behavior	Use Case
CHAR(n)	255 chars	Fixed length (padded)	Gender (M/F), Country Code
VARCHAR(n)	65,535 chars	Variable length	Name, Email, Address
TEXT	65,535 chars	Large text storage	Comments, Descriptions
MEDIUMTEXT	16 MB	Medium large text	Blog posts, Articles
LONGTEXT	4 GB	Massive text	Books, Logs, XML data
ENUM	65,535 values	Predefined list	Status ('active','inactive')
BLOB	65,535 bytes	Binary Large Object	Images, Files, PDFs

📊 Category 3: Date & Time Data Types

Data Type	Format	Range	Use Case
DATE	YYYY-MM-DD	1000-01-01 to 9999-12-31	DOB, Joining Date
TIME	HH:MM:SS	-838:59:59 to 838:59:59	Login time, Duration
DATETIME	YYYY-MM-DD HH:MM:SS	1000 to 9999	Order timestamp
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970 to 2038	Auto-update, created_at
YEAR	YYYY	1901 to 2155	Graduation year

💻 Real-World Code — Table with All Data Types:

CREATE TABLE employees (
emp_id       INT PRIMARY KEY AUTO_INCREMENT,
first_name   VARCHAR(50) NOT NULL,
last_name    VARCHAR(50) NOT NULL,
email        VARCHAR(100) UNIQUE,
age          TINYINT UNSIGNED,
salary       DECIMAL(10,2),
gender       ENUM('Male', 'Female', 'Other'),
hire_date    DATE,
login_time   TIME,
created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
bio          TEXT,
profile_pic  BLOB
);

📊 Table Structure Output:

Field	Type	Null	Key	Default	Extra
emp_id	int	NO	PRI	NULL	auto_increment
first_name	varchar(50)	NO		NULL
last_name	varchar(50)	NO		NULL
email	varchar(100)	YES	UNI	NULL
age	tinyint unsigned	YES		NULL
salary	decimal(10,2)	YES		NULL
gender	enum('Male','Female','Other')	YES		NULL
hire_date	date	YES		NULL
login_time	time	YES		NULL
created_at	timestamp	YES		CURRENT_TIMESTAMP
bio	text	YES		NULL
profile_pic	blob	YES		NULL

text

💡 CHAR vs VARCHAR — Key Difference:
CHAR(10) → Hamesha 10 bytes use karega chahe tum 2 characters store karo (remaining spaces se pad ho jata hai).
VARCHAR(10) → Sirf utna space use karega jitna data hai + 1-2 bytes overhead. Agar "Hi" store kiya toh sirf 4 bytes lagenge.

Rule: Fixed-length data (Gender: M/F, Country Code: IN/US) → CHAR. Variable-length data (Name, Email) → VARCHAR.

⚠️ Common Mistakes:

• Mistake: Phone number ke liye INT use karna → Leading zeros hat jayenge (0912345 → 912345).
  Fix: Phone numbers ke liye hamesha VARCHAR(15) use karo.
• Mistake: Price/Money ke liye FLOAT use karna → Rounding errors aayenge (10.20 → 10.1999998).
  Fix: Money ke liye hamesha DECIMAL(10,2) use karo.
• Mistake: Har column ko VARCHAR(255) dena → Memory waste hoti hai.
  Fix: Realistic maximum length set karo based on actual data.
• Mistake: DATETIME aur TIMESTAMP ko same samajhna → TIMESTAMP 2038 ke baad fail hoga (Y2038 problem).
  Fix: Long-term dates ke liye DATETIME use karo.

💬 Interview Questions:

text

🔍 Definition: Constraints are rules enforced on table columns that restrict the type of data that can be inserted, updated or deleted. They ensure data integrity, accuracy and reliability by preventing invalid data from entering the database.

🎯 Samjho Simple Bhasha Mein: Socho tum ek form bhar rahe ho online. Name field blank nahi chhod sakte (NOT NULL), Aadhaar number unique hona chahiye (UNIQUE), har student ka roll number alag hona chahiye (PRIMARY KEY), aur age mein negative number nahi daal sakte (CHECK). Yeh saari restrictions hi "Constraints" hain — database level par lagti hain taaki galat data kabhi enter na ho.

💡 MySQL mein 6 Main Constraints hain:
1. NOT NULL — Column blank nahi reh sakta
2. UNIQUE — Har value alag honi chahiye
3. PRIMARY KEY — NOT NULL + UNIQUE (row identifier)
4. FOREIGN KEY — Dusri table se relationship
5. DEFAULT — Agar value na de toh automatic fill
6. CHECK — Custom condition lagao

🔹 Constraint 1: NOT NULL

Definition: Ensures that a column cannot have a NULL (empty) value. Every row must have a value for this column.

CREATE TABLE students (
student_id  INT,
name        VARCHAR(50) NOT NULL,  -- Name blank nahi hoga kabhi
email       VARCHAR(100) NOT NULL  -- Email bhi mandatory hai
);

-- ✅ Yeh chalega:

INSERT INTO students
VALUES (1, 'Rahul', 'rahul@gmail.com');

-- ❌ Yeh ERROR dega:

INSERT INTO students
VALUES (2, NULL, 'test@gmail.com');

-- ERROR 1048: Column 'name' cannot be null

🔹 Constraint 2: UNIQUE

Definition: Ensures all values in a column are different. No two rows can have the same value in a UNIQUE column. Unlike PRIMARY KEY, UNIQUE allows NULL values.

CREATE TABLE users (
user_id    INT,
email      VARCHAR(100) UNIQUE,     -- Har email alag hogi
aadhaar    VARCHAR(12) UNIQUE      -- Har Aadhaar unique hoga
);

-- ✅ Yeh chalega:

INSERT INTO users
VALUES (1, 'rahul@gmail.com', '123456789012');

-- ❌ Same email again → ERROR:

INSERT INTO users
VALUES (2, 'rahul@gmail.com', '987654321098');

-- ERROR 1062: Duplicate entry 'rahul@gmail.com' for key 'email'

🔹 Constraint 3: PRIMARY KEY

Definition: A PRIMARY KEY uniquely identifies each row in a table. It is the combination of NOT NULL + UNIQUE. A table can have only ONE primary key, but it can consist of multiple columns (Composite Key).

-- Single Column Primary Key
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
department VARCHAR(30)
);

-- Composite Primary Key (2 columns milkar unique)
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id) -- Dono milkar unique
);

-- AUTO_INCREMENT ke sath (Most Common)
CREATE TABLE products (
product_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2)
);

🔹 Constraint 4: FOREIGN KEY

Definition: A FOREIGN KEY creates a link between two tables. It references the PRIMARY KEY of another table, ensuring that the value in the foreign key column must exist in the referenced table. This maintains referential integrity.

💡 Simple Example: Socho ek departments table hai aur ek employees table hai. Har employee ka department valid hona chahiye — yaani woh departments table mein exist karna chahiye. Foreign Key yeh ensure karta hai.

-- Parent Table (Referenced Table)
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL
);

-- Child Table (Foreign Key wali table)
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

-- Insert valid data

INSERT INTO departments
VALUES (1, 'IT'), (2, 'HR'), (3, 'Sales');

-- ✅ Valid (dept_id 1 exists in departments)

INSERT INTO employees
VALUES (101, 'Rahul', 1);

-- ❌ ERROR (dept_id 99 does NOT exist in departments)

INSERT INTO employees
VALUES (102, 'Priya', 99);

-- ERROR 1452: Cannot add or update a child row: a foreign key constraint fails

🔹 Constraint 5: DEFAULT

Definition: Sets a default value for a column when no value is provided during INSERT. If the user does not specify a value, MySQL automatically fills in the default.

CREATE TABLE orders (
order_id     INT PRIMARY KEY AUTO_INCREMENT,
product      VARCHAR(50),
quantity     INT DEFAULT 1,           -- Default 1 piece
status       VARCHAR(20) DEFAULT 'Pending',  -- Default Pending
order_date   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Quantity aur status nahi diya → Default values lag jayengi

INSERT INTO orders (product)
VALUES ('Laptop');

-- Result: order_id=1, product='Laptop', quantity=1, status='Pending', order_date=current time

🔹 Constraint 6: CHECK

Definition: CHECK constraint validates data against a custom condition before allowing insertion. If the condition evaluates to FALSE, the insert/update is rejected. Available from MySQL 8.0.16+.

CREATE TABLE employees (
emp_id   INT PRIMARY KEY,
name     VARCHAR(50),
age      INT,
salary   DECIMAL(10,2),
CHECK (age >= 18 AND age 65),      -- Age 18-65 ke beech hi hogi
CHECK (salary > 0)                      -- Salary positive honi chahiye
);

-- ✅ Valid:

INSERT INTO employees
VALUES (1, 'Rahul', 25, 50000);

-- ❌ ERROR (age = 15, below 18):

INSERT INTO employees
VALUES (2, 'Priya', 15, 30000);

-- ERROR 3819: Check constraint is violated

📊 All Constraints — Quick Reference:

Constraint	Allows NULL?	Allows Duplicate?	Per Table Limit
NOT NULL	❌ No	✅ Yes	Multiple columns
UNIQUE	✅ Yes (1 NULL)	❌ No	Multiple columns
PRIMARY KEY	❌ No	❌ No	Only 1
FOREIGN KEY	✅ Yes	✅ Yes	Multiple columns
DEFAULT	✅ Yes	✅ Yes	Multiple columns
CHECK	✅ Yes	✅ Yes	Multiple conditions

⚠️ Common Mistakes:

• Mistake: Ek table mein 2 PRIMARY KEY banana → ERROR 1068: Multiple primary key defined.
  Fix: Composite primary key use karo: PRIMARY KEY (col1, col2).
• Mistake: Foreign Key lagana bina parent table banaye → Error aayega.
  Fix: Pehle parent table (referenced table) banao, phir child table.
• Mistake: CHECK constraint mein MySQL 5.7 par rely karna → MySQL 5.7 CHECK parse karta hai but enforce nahi karta!
  Fix: MySQL 8.0.16+ use karo for actual CHECK enforcement.
• Mistake: UNIQUE constraint mein multiple NULLs ko error samajhna → MySQL UNIQUE mein multiple NULLs allow karta hai.

💬 Interview Questions:

text

🔍 Definition: DDL (Data Definition Language) commands are used to define, modify and delete database structures (schemas). They work on the structure of tables, not on the data inside them. DDL commands are auto-committed — once executed, changes cannot be rolled back.

🎯 Samjho Simple Bhasha Mein: DDL commands se tum apne database ka structure banate ho. Jaise ek building banate waqt architect blueprint banata hai (CREATE), design change karta hai (ALTER), puri building giraa deta hai (DROP), ya andar ka saamaan saaf kar deta hai par building rehne deta hai (TRUNCATE). Data se inhe koi lena-dena nahi — yeh sirf structure handle karte hain.

💡 Important: DDL commands auto-commit hote hain. Yaani ek baar DROP TABLE run kiya toh ROLLBACK se wapas nahi aa sakta. Hamesha backup lo pehle!

🔹 CREATE — Database & Table Creation

-- Create Database
CREATE DATABASE company_db;

USE company_db;

-- Create Table with all features
CREATE TABLE employees (
emp_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
salary DECIMAL(10,2) DEFAULT 25000.00,
dept VARCHAR(30),
hire_date DATE,
CHECK (salary > 0)
);

-- Create Table If Not Exists (Safe way)
CREATE TABLE IF NOT EXISTS departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL
);

-- Verify Table Structure
DESCRIBE employees;

-- OR
DESC employees;

-- OR
SHOW COLUMNS
FROM employees;

🔹 ALTER — Table Structure Modification

-- 1. Add new column
ALTER TABLE employees ADD COLUMN phone VARCHAR(15);

-- 2. Add column at specific position
ALTER TABLE employees ADD COLUMN middle_name VARCHAR(30) AFTER name;

-- 3. Modify column data type
ALTER TABLE employees MODIFY COLUMN phone VARCHAR(20);

-- 4. Rename column
ALTER TABLE employees CHANGE COLUMN phone contact_number VARCHAR(20);

-- 5. Drop (delete) a column
ALTER TABLE employees DROP COLUMN middle_name;

-- 6. Rename table
ALTER TABLE employees RENAME TO staff;

-- 7. Add constraint to existing table
ALTER TABLE staff ADD CONSTRAINT chk_salary CHECK (salary >= 10000);

-- 8. Drop constraint
ALTER TABLE staff DROP CHECK chk_salary;

text

🔹 DROP — Permanent Deletion

-- Drop Table (structure + data dono delete)
DROP TABLE staff;

-- Safe Drop (error nahi aayega agar table exist nahi karta)
DROP TABLE IF EXISTS staff;

-- Drop Database (DANGER — poora database delete!)
DROP DATABASE company_db;

-- Safe Database Drop
DROP DATABASE IF EXISTS company_db;

text

🔹 TRUNCATE — Delete All Data, Keep Structure

-- Saara data delete, but table structure rehne do
TRUNCATE TABLE employees;

-- Note: AUTO_INCREMENT counter bhi reset ho jayega
-- Note: TRUNCATE ROLLBACK nahi ho sakta
-- Note: WHERE clause nahi use kar sakte (saara data delete hota hai)

📊 DROP vs TRUNCATE vs DELETE:

Feature	DROP	TRUNCATE	DELETE
Type	DDL	DDL	DML
Structure	Deleted ❌	Kept ✅	Kept ✅
Data	Deleted ❌	Deleted ❌	Deleted ❌
WHERE clause	❌ No	❌ No	✅ Yes
ROLLBACK	❌ No	❌ No	✅ Yes
Speed	Fastest	Fast	Slowest
AUTO_INCREMENT	N/A	Resets to 1	Continues

⚠️ Common Mistakes:

• Mistake: DROP TABLE production server par bina backup ke run karna → Data permanently lost.
  Fix: Hamesha mysqldump se backup lo pehle.
• Mistake: TRUNCATE aur DELETE ko same samajhna → TRUNCATE rollback nahi hota, DELETE hota hai.
  Fix: Jab selective delete karna ho toh DELETE use karo with WHERE.
• Mistake: ALTER TABLE se column drop karna bina check kiye ki uspar FOREIGN KEY toh nahi hai.
• Mistake: IF NOT EXISTS / IF EXISTS na likhna → Agar table already hai toh error aayega.

💬 Interview Questions:

text

🔍 Definition: DML (Data Manipulation Language) commands are used to manipulate the data stored inside tables. Unlike DDL which works on structure, DML works on the actual rows (records). DML commands can be rolled back using ROLLBACK if they are within a transaction.

🎯 Samjho Simple Bhasha Mein: DDL se tumne table (building) bana liya. Ab DML se tum us table mein data (furniture/saamaan) daalte ho (INSERT), update karte ho (UPDATE), ya delete karte ho (DELETE). DML ka kaam actual data se hai — structure se nahi.

💡 Key Point: DML commands auto-commit nahi hote (by default MySQL mein autocommit ON hota hai, but transaction mein use karein toh ROLLBACK possible hai). Production mein hamesha transaction ke andar DML use karo.

🔹 INSERT — Adding New Records

-- Method 1: Insert with ALL columns (order matters)

INSERT INTO employees

VALUES (1, 'Rahul Sharma', 'rahul@gmail.com', 55000, 'IT', '2024-01-15');

-- Method 2: Insert with SPECIFIC columns (safest method)

INSERT INTO employees (name, email, salary, dept)

VALUES ('Priya Singh', 'priya@gmail.com', 62000, 'HR');

-- Method 3: Insert MULTIPLE rows at once (Bulk Insert)

INSERT INTO employees (name, email, salary, dept, hire_date)

VALUES
('Amit Kumar', 'amit@gmail.com', 48000, 'Sales', '2024-02-01'),
('Sneha Patel', 'sneha@gmail.com', 71000, 'IT', '2024-03-10'),
('Ravi Verma', 'ravi@gmail.com', 53000, 'HR', '2024-04-20'),
('Kavita Joshi', 'kavita@gmail.com', 45000, 'Sales', '2024-05-15'),
('Deepak Rao', 'deepak@gmail.com', 68000, 'Finance', '2024-06-01');

-- Method 4: Insert from another table

INSERT INTO employees_backup
SELECT *
FROM employees
WHERE dept = 'IT';

📊 After INSERT — Table Data:

emp_id	name	email	salary	dept	hire_date
1	Rahul Sharma	rahul@gmail.com	55000.00	IT	2024-01-15
2	Priya Singh	priya@gmail.com	62000.00	HR	NULL
3	Amit Kumar	amit@gmail.com	48000.00	Sales	2024-02-01
4	Sneha Patel	sneha@gmail.com	71000.00	IT	2024-03-10
5	Ravi Verma	ravi@gmail.com	53000.00	HR	2024-04-20
6	Kavita Joshi	kavita@gmail.com	45000.00	Sales	2024-05-15
7	Deepak Rao	deepak@gmail.com	68000.00	Finance	2024-06-01

7 rows in set (0.00 sec)

🔹 UPDATE — Modifying Existing Records

-- Update single column for specific row

UPDATE employees

SET salary = 60000

WHERE emp_id = 3;

-- Update multiple columns at once

UPDATE employees

SET salary = 75000, dept = 'Management'

WHERE emp_id = 1;

-- Update with calculation (10% salary increment)

UPDATE employees

SET salary = salary * 1.10

WHERE dept = 'IT';

-- ⚠️ DANGER: Update WITHOUT WHERE → ALL rows update ho jayengi!

UPDATE employees

SET salary = 50000;
-- Sabki salary 50000 ho jayegi! ❌

🔹 DELETE — Removing Records

-- Delete specific row

DELETE
FROM employees

WHERE emp_id = 6;

-- Delete multiple rows with condition

DELETE
FROM employees

WHERE dept = 'Sales' AND salary 50000;

-- Delete with subquery

DELETE
FROM employees

WHERE dept IN (SELECT dept_name FROM closed_departments);

-- ⚠️ DANGER: DELETE WITHOUT WHERE → ALL rows delete!

DELETE
FROM employees;
-- Saara data gayab! (But structure rahega)

⚠️ Common Mistakes:

• Mistake #1 (CRITICAL): UPDATE ya DELETE mein WHERE clause bhool jaana → Saari rows affect ho jayengi!
  Fix: Pehle SELECT query run karo same WHERE ke saath to verify, phir UPDATE/DELETE chalao.
• Mistake: INSERT mein column count aur value count mismatch → Column count doesn't match value count.
  Fix: Column names explicitly likho aur count match karo.
• Mistake: String values mein quotes na lagana → Unknown column 'Rahul' in 'field list'.
  Fix: String values hamesha single quotes mein: 'Rahul'.
• Mistake: Bulk INSERT mein ek bhi row fail ho toh puri query fail → Use INSERT IGNORE to skip errors.

💬 Interview Questions:

text

🔍 Definition: DQL (Data Query Language) consists of the SELECT statement — the most used SQL command. It retrieves data from one or more tables without modifying anything. SELECT is read-only and forms the foundation of all data analysis in SQL.

🎯 Samjho Simple Bhasha Mein: SELECT ek tarah se "dikha do" command hai. Tum database se poochh rahe ho — "Bhai mujhe yeh data dikha do." Data modify nahi hota, sirf screen par aata hai. Yeh SQL ki sabse important command hai — 80% kaam SELECT se hota hai.

💡 Note: SELECT ki deep dive (WHERE, ORDER BY, GROUP BY, JOINs) Part 2 mein cover hogi. Yahan sirf basic syntax aur foundation cover karenge.

-- Select ALL columns and ALL rows
SELECT *
FROM employees;

-- Select specific columns only
SELECT name, salary, dept
FROM employees;

-- Select with alias (column renaming for display)
SELECT
name AS 'Employee Name',
salary AS 'Monthly Salary',
salary * 12 AS 'Annual Salary'

FROM employees;

-- Select DISTINCT values (no duplicates)
SELECT DISTINCT dept
FROM employees;

-- Select with basic WHERE filter
SELECT *
FROM employees
WHERE dept = 'IT';

-- Count total rows
SELECT COUNT(*) AS total_employees
FROM employees;

📊 Expected Output (Alias Query):

Employee Name	Monthly Salary	Annual Salary
Rahul Sharma	75000.00	900000.00
Priya Singh	62000.00	744000.00
Amit Kumar	60000.00	720000.00
Sneha Patel	78100.00	937200.00
Ravi Verma	53000.00	636000.00
Deepak Rao	68000.00	816000.00

text

💡 SELECT Execution Order (Important for Interviews!):
Hum likhte hain: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
But MySQL execute karta hai: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Yaani FROM sabse pehle run hota hai (table identify karo), phir WHERE (filter karo), phir grouping, phir SELECT (columns choose karo). Yeh order interviews mein bahut poocha jaata hai!

⚠️ Common Mistakes:

• Mistake: Production mein SELECT * use karna → Unnecessary columns fetch hote hain, performance slow.
  Fix: Hamesha specific columns select karo.
• Mistake: Alias mein spaces dena bina quotes ke → AS Employee Name ❌
  Fix: AS 'Employee Name' ya backticks: AS `Employee Name` ✅
• Mistake: SELECT ko data modify karne ke liye use karna → SELECT sirf read karta hai.

💬 Interview Questions:

text

🔍 Definition: DCL (Data Control Language) commands manage user permissions and access rights in MySQL. GRANT gives privileges to users; REVOKE removes privileges. These commands control WHO can do WHAT on WHICH database objects.

🎯 Samjho Simple Bhasha Mein: Socho tumhare office mein ek locker hai. Boss (root user) decide karta hai ki kaunsa employee locker khol sakta hai (GRANT), aur kaunsa nahi (REVOKE). Agar boss ne tumhe key di (GRANT), tum access kar sakte ho. Agar key wapas le li (REVOKE), access khatam. Database mein bhi user permissions aise hi kaam karti hain.

💡 MySQL Privilege Levels:
Global Level: Saare databases par apply hota hai → *.*
Database Level: Specific database ke saare tables par → company_db.*
Table Level: Specific table par → company_db.employees
Column Level: Specific columns par → GRANT SELECT (name, salary) ON ...

-- Step 1: Create a new user
CREATE USER 'analyst'@'localhost'
IDENTIFIED BY 'SecurePass123!';

-- Step 2: GRANT
SELECT permission (read-only access)
GRANT SELECT

ON company_db.employees
TO 'analyst'@'localhost';

-- Grant multiple privileges
GRANT SELECT,
INSERT,
UPDATE

ON company_db.*
TO 'analyst'@'localhost';

-- Grant ALL privileges (admin level)
GRANT ALL PRIVILEGES

ON company_db.*
TO 'admin_user'@'localhost';

-- Apply changes
FLUSH PRIVILEGES;

-- Check user's current privileges
SHOW GRANTS FOR 'analyst'@'localhost';

-- REVOKE specific permission
REVOKE INSERT, UPDATE
ON company_db.*
FROM 'analyst'@'localhost';

-- REVOKE all privileges
REVOKE ALL PRIVILEGES
ON company_db.*
FROM 'analyst'@'localhost';

-- Delete user completely
DROP USER 'analyst'@'localhost';

📊 SHOW GRANTS Output:

+--------------------------------------------------------------------+
| Grants for analyst@localhost |
+--------------------------------------------------------------------+
| GRANT USAGE
ON . TO 'analyst'@'localhost' |
| GRANT SELECT
ON company_db.* TO 'analyst'@'localhost' |
+--------------------------------------------------------------------+

📊 Common MySQL Privileges:

Privilege	Allows
SELECT	Read data from tables
INSERT	Add new rows
UPDATE	Modify existing rows
DELETE	Remove rows
CREATE	Create databases/tables
DROP	Delete databases/tables
ALL PRIVILEGES	Complete access (admin)

⚠️ Common Mistakes:

• Mistake: GRANT ke baad FLUSH PRIVILEGES na karna → Permissions apply nahi hongi.
  Fix: Hamesha GRANT/REVOKE ke baad FLUSH PRIVILEGES; run karo.
• Mistake: Sabko ALL PRIVILEGES de dena → Security risk!
  Fix: Principle of Least Privilege follow karo — sirf zaroori permissions do.
• Mistake: 'user'@'%' (any host) use karna production mein → Koi bhi IP se connect kar sakta hai.
  Fix: Specific IP restrict karo: 'user'@'192.168.1.100'

💬 Interview Questions:

text

🔍 Definition: TCL (Transaction Control Language) commands manage transactions in MySQL. A transaction is a set of SQL operations that must either ALL succeed (COMMIT) or ALL fail (ROLLBACK) — there is no partial execution. This ensures data consistency and follows the ACID properties.

🎯 Samjho Simple Bhasha Mein: Socho tum bank se 5000 rupees transfer kar rahe ho apne account se dost ke account mein. Step 1: Tumhare account se 5000 katne chahiye. Step 2: Dost ke account mein 5000 jaane chahiye. Agar Step 1 ho gaya par Step 2 fail ho gaya — toh tumhare paise gayab ho jayenge bina transfer hue! Transaction ensure karta hai ki DONO steps successful hon — warna dono cancel ho jayen (ROLLBACK).

💡 ACID Properties (Every Interview Question!):
A — Atomicity: Saari operations ya toh complete hon ya koi na ho (all or nothing).
C — Consistency: Transaction ke baad database valid state mein rahe.
I — Isolation: Multiple concurrent transactions ek dusre ko affect na karein.
D — Durability: COMMIT hone ke baad data permanently save ho jaye, system crash hone par bhi.

-- MySQL mein by default autocommit ON hota hai
-- Har individual query automatically commit ho jaati hai
SELECT @@autocommit;
-- Returns 1 (ON)

-- ========== BASIC TRANSACTION ==========

-- Start a transaction
START TRANSACTION;

-- Operation 1: Sender ka balance kam karo

UPDATE accounts
SET balance = balance - 5000
WHERE account_id = 101;

-- Operation 2: Receiver ka balance badao

UPDATE accounts
SET balance = balance + 5000
WHERE account_id = 202;

-- Agar sab sahi hai → Permanently save karo
COMMIT;

-- Agar kuch galat hua → Sab undo karo
-- ROLLBACK;

-- ========== SAVEPOINT EXAMPLE ==========

START TRANSACTION;

INSERT INTO employees (name, salary)
VALUES ('Rahul', 50000);

SAVEPOINT sp1;
-- Checkpoint 1 save karo

INSERT INTO employees (name, salary)
VALUES ('Priya', 60000);

SAVEPOINT sp2;
-- Checkpoint 2 save karo

INSERT INTO employees (name, salary)
VALUES ('Amit', 45000);

-- Oops! Amit ka data galat hai, sirf Amit wala undo karo
ROLLBACK TO sp2;
-- Amit delete, Rahul + Priya safe

COMMIT;
-- Rahul aur Priya permanently saved

-- ========== AUTOCOMMIT CONTROL ==========

-- Turn off autocommit (manual control)

SET autocommit = 0;

-- Ab har operation ke baad manually COMMIT karna padega

UPDATE employees
SET salary = 55000
WHERE emp_id = 1;

COMMIT;
-- Manually save

-- Turn autocommit back ON

SET autocommit = 1;

📊 Transaction Flow:

START TRANSACTION
│
├──→ Operation 1 (UPDATE/INSERT/DELETE)
├──→ SAVEPOINT sp1
├──→ Operation 2
├──→ SAVEPOINT sp2
├──→ Operation 3
│
├──→ Something went wrong?
│ │
│ ├── ROLLBACK TO sp2 (Undo till sp2, keep sp1 work)
│ ├── ROLLBACK TO sp1 (Undo till sp1)
│ └── ROLLBACK (Undo EVERYTHING)
│
└──→ Everything OK?
│
└── COMMIT (Save permanently, no going back!)

⚠️ Common Mistakes:

• Mistake: Transaction start karke COMMIT ya ROLLBACK bhool jaana → Lock lagti rehti hai, other users wait karte hain.
  Fix: Hamesha transaction ko COMMIT ya ROLLBACK se close karo.
• Mistake: DDL commands (CREATE, DROP, ALTER) transaction ke andar use karna → DDL auto-commit karta hai, transaction break ho jaata hai.
  Fix: DDL commands transaction ke bahar rakhiye.
• Mistake: ROLLBACK ko COMMIT ke baad try karna → COMMIT ke baad ROLLBACK kaam nahi karta.
  Fix: COMMIT karne se pehle verify karo ki sab sahi hai.
• Mistake: Autocommit OFF rakhna aur COMMIT bhool jaana → Data save nahi hoga, connection close hone par lost!
  Fix: Autocommit OFF hone par har important operation ke baad manually COMMIT karo.

💬 Interview Questions:

Category	Full Form	Commands	Purpose	Rollback?
DDL	Data Definition Language	CREATE, ALTER, DROP, TRUNCATE	Structure define/modify	❌ No
DML	Data Manipulation Language	INSERT, UPDATE, DELETE	Data add/modify/remove	✅ Yes
DQL	Data Query Language	SELECT	Data retrieve (read-only)	N/A
DCL	Data Control Language	GRANT, REVOKE	User permissions	❌ No
TCL	Transaction Control Language	COMMIT, ROLLBACK, SAVEPOINT	Transaction management	Controls it

Next: Data Insights MySQL Masterclass — Part 2

Part 2 mein hum cover karenge: SELECT Deep Dive — WHERE, AND/OR/NOT, BETWEEN, IN, LIKE, ORDER BY, LIMIT, DISTINCT with real-world queries aur interview questions ke saath Data Insights par.

Happy Querying & Keep Learning SQL! 🚀