5️⃣ Test MySQL (Optional)
Run the following command in MySQL Workbench:
SHOW DATABASES;
If databases are displayed → 🎉 Success!
Common Problems & Fixes
___________________________________________________________________________________
Now Let’s Start the MySQL Course
Lesson 1: Creating Your First Table
Use the following SQL query:
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
gender ENUM('Male','Female','Other'),
date_of_birth DATE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
View Data
SELECT * FROM users;
Select Specific Columns
SELECT id, email FROM users;
🎉 Congratulations! You have run your first SQL program.
Output: 1 row affected
___________________________________________________________________________________
Lesson 3: Writing & Saving SQL Scripts
Writing SQL Scripts
Write SQL queries inside MySQL Workbench.
Use comments to make scripts easy to understand
-- This query creates a new table
-- This query selects all records
Saving SQL Scripts
Click File → Save Script.
Choose a folder and file name (example: users_table.sql).
Click Save.
CREATE DATABASE starteraql;
USE startersql;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
gender ENUM('Male','Female','Other'),
date_of_birth DATE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
SELECT * FROM users;
___________________________________________________________________________________
DATATYPES AND CONSTRAINTS in MYSQL;
✅ Types of Constraints in MySQL
Constraints in MySQL are rules applied to table columns to ensure data accuracy, consistency, and reliability.
1️⃣ NOT NULL Constraint
Ensures that a column cannot contain NULL values.
Example:
CREATE TABLE students (
id INT,
name VARCHAR(50) NOT NULL
);
Use case: Name must always be provided.
________________________________________________________________________________
2️⃣ UNIQUE Constraint
Ensures all values in a column are different.
Example:
CREATE TABLE users (
email VARCHAR(100) UNIQUE
);
Use case: Email ID must be unique for each user.
___________________________________________________________________________________
3️⃣ PRIMARY KEY Constraint
Example:
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50)
);
Use case: Employee ID as unique identifier.
_________________________________________________________________________________
4️⃣ FOREIGN KEY Constraint
Creates a relationship between two tables.
Example:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT,
FOREIGN KEY (user_id) REFERENCES users(id)
);
Use case: Ensures order belongs to a valid user.
__________________________________________________________________________________
5️⃣ CHECK Constraint
Ensures values meet a specific condition.
Example:
CREATE TABLE students (
age INT CHECK (age >= 18)
);
Use case: Age must be 18 or above.
_________________________________________________________________________________
6️⃣ DEFAULT Constraint
Assigns a default value when no value is provided.
Example:
CREATE TABLE accounts (
status VARCHAR(20) DEFAULT 'active'
);
Use case: Account is active by default
_______________________________________________________________________________
7️⃣ AUTO_INCREMENT Constraint
Automatically increases numeric values.
Example:
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(100)
);
Use case: Auto-generate unique IDs.
__________________________________________________________________________________
8️⃣ ENUM Constraint
Allows only predefined values.
Example:
CREATE TABLE users (
gender ENUM('Male','Female','Other')
);
Use case: Restrict gender values.
__________________________________________________________________________________
9️⃣ INDEX Constraint
Improves data retrieval speed.
Example:
CREATE INDEX idx_email ON users(email);
Use case: Faster search on email column.
Why Constraints Are Important
___________________________________________________________________________________
SELECT * from users;
SELECT email,gender from users;
RENAME TABLE users to programmers;
SELECT * from programmers;
RENAME TABLE programmers TO users;
__________________________________________________________________________________
Comment the code to not run the Program use --
-- SELECT * from users; // -- for comments ignore the code
________________________________________________________________________________
Adding a column
ALTER TABLE users ADD column is_active BOOLEAN DEFAULT true;
SELECT * from users;
Drop a Column //delete a program
ALTER TABLE users DROP COLUMN is_active;
SELECT * from users;
MODIFY COLUMN TYPE
ALTER TABLE users MODIFY COLUMN name VARCHAR(150);
SELECT * from users;
ALTER TABLE users MODIFY COLUMN email VARCHAR(100) AFTER id;
ALTER TABLE users MODIFY COLUMN date_of_birth datetime FIRST;
SELECT * from users;
___________________________________________________________________________________
INSERTING DATA IN MYSQL
Crud = CREATE , READ, DELETE ,UPDATE
Congratulations WE CREATED OUR 1st TABLE
USE StarterSql;
SELECT * FROM users;
INSERT INTO users (created_at, id, email, name, gender, status)
VALUES
('2025-11-12', 1, 'KrishYT@gmail.com', 'Krishna', 'Male', DEFAULT),
('2025-04-03', 2, 'YashYT@gmail.com', 'YashYT', 'Male', DEFAULT);
___________________________________________________________________________________
USING STARTER SQL
CREATING THE TABLE OF 50 STUDENTS
Roll No
-
Name
-
Year
-
Date of Birth
-
Gender (Male / Female)
Including create table → insert data → display data, and how to save the SQL file in MySQL Workbench
Step 1: Select your database
USE StarterSql;
___________________________________________________________________________________
If StarterSql does not exist, create it:
CREATE DATABASE StarterSql;
USE StarterSql;
___________________________________________________________________________________
Step 2: Create students table
CREATE TABLE students (
roll_no INT PRIMARY KEY,
name VARCHAR(50),
year INT,
date_of_birth DATE,
gender ENUM('Male','Female')
);
___________________________________________________________________________________
Now refresh Schemas → StarterSql → Tables
___________________________________________________________________________________
Step 3: Insert 50 Indian Student Data Samples
INSERT INTO students (roll_no, name, year, date_of_birth, gender) VALUES
(1,'Aarav Sharma',1,'2006-01-15','Male'),
(2,'Ananya Patil',1,'2006-02-10','Female'),
(3,'Rohan Verma',1,'2006-03-12','Male'),
(4,'Sneha Kulkarni',1,'2006-04-05','Female'),
(5,'Aditya Singh',1,'2006-05-18','Male'),
(6,'Pooja Deshmukh',1,'2006-06-09','Female'),
(7,'Kunal Joshi',1,'2006-07-22','Male'),
(8,'Neha Pawar',1,'2006-08-14','Female'),
(9,'Rahul Yadav',1,'2006-09-03','Male'),
(10,'Priya Chavan',1,'2006-10-28','Female'),
(11,'Siddharth More',2,'2005-01-17','Male'),
(12,'Isha Jadhav',2,'2005-02-21','Female'),
(13,'Omkar Patil',2,'2005-03-11','Male'),
(14,'Kavya Naik',2,'2005-04-09','Female'),
(15,'Nikhil Bhosale',2,'2005-05-19','Male'),
(16,'Shruti Patwardhan',2,'2005-06-24','Female'),
(17,'Akash Kale',2,'2005-07-15','Male'),
(18,'Rutuja Sawant',2,'2005-08-06','Female'),
(19,'Sanket Shinde',2,'2005-09-29','Male'),
(20,'Aishwarya Kulkarni',2,'2005-10-13','Female'),
(21,'Yash Rajput',3,'2004-01-08','Male'),
(22,'Pallavi Thakur',3,'2004-02-18','Female'),
(23,'Tejas Gaikwad',3,'2004-03-23','Male'),
(24,'Mayuri Patil',3,'2004-04-12','Female'),
(25,'Ritesh Pandey',3,'2004-05-27','Male'),
(26,'Komal Mishra',3,'2004-06-04','Female'),
(27,'Amit Kumar',3,'2004-07-19','Male'),
(28,'Nandini Joshi',3,'2004-08-31','Female'),
(29,'Vishal Tiwari',3,'2004-09-16','Male'),
(30,'Shivani Saxena',3,'2004-10-25','Female'),
(31,'Harsh Mehta',4,'2003-01-20','Male'),
(32,'Riya Malhotra',4,'2003-02-07','Female'),
(33,'Manish Gupta',4,'2003-03-14','Male'),
(34,'Swati Agarwal',4,'2003-04-28','Female'),
(35,'Deepak Chauhan',4,'2003-05-09','Male'),
(36,'Bhavana Iyer',4,'2003-06-21','Female'),
(37,'Saurabh Mishra',4,'2003-07-02','Male'),
(38,'Tanvi Kulkarni',4,'2003-08-17','Female'),
(39,'Pratik Solanki',4,'2003-09-26','Male'),
(40,'Meenal Joshi',4,'2003-10-11','Female'),
(41,'Ravi Shankar',5,'2002-01-30','Male'),
(42,'Shreya Banerjee',5,'2002-02-15','Female'),
(43,'Ankit Saxena',5,'2002-03-05','Male'),
(44,'Poonam Rawat',5,'2002-04-18','Female'),
(45,'Karan Malviya',5,'2002-05-29','Male'),
(46,'Ishita Roy',5,'2002-06-10','Female'),
(47,'Mohit Jain',5,'2002-07-24','Male'),
(48,'Rashmi Kulkarni',5,'2002-08-08','Female'),
(49,'Vikas Soni',5,'2002-09-14','Male'),
(50,'Payal Gupta',5,'2002-10-19','Female');
___________________________________________________________________________________
Step 4: Display the data (Run Table)
or limit:
SELECT * FROM students LIMIT 50;
__________________________________________________________________________________
Step 5: Save SQL File (Very Important)
In MySQL Workbench
Click File → Save SQL Script As
Name it:
Save anywhere (Desktop / D Drive)
Later you can open & run it again
___________________________________________________________________________________
Correct Query (Show only Male students)
Option 1: Using !=
SELECT * FROM students WHERE gender != 'Female';
Option 2: Direct Male filter
SELECT * FROM students WHERE gender = 'Male';
✅ Other Useful Practice Queries
1)Show only Female students
SELECT * FROM students WHERE gender = 'Female';
2)Students born between two dates
SELECT * FROM students
WHERE date_of_birth BETWEEN '2004-01-01' AND '2006-12-31';
3)Sort by Year
SELECT * FROM students ORDER BY year;
4)Count Male & Female
SELECT gender, COUNT(*)
FROM students
GROUP BY gender;
5)Limit result
SELECT * FROM students LIMIT 10;
___________________________________________________________________________________
SELECT * FROM users WHERE gender='MALE' OR salary>'65000' ORDER BY date_of_birth DESC LIMIT 5;
___________________________________________________________________________________
UPDATING THE DATA
USE startersql;
UPDATE users SET salary = 45000 WHERE id=1;
UPDATE users SET salary = 45000, email = 'krishYT@krish.com' WHERE id = 1;
UPDATE users SET salary=salary+10000 WHERE salary<60000;
DELETE FROM users WHERE salary<65000;
DELETE FROM users WHERE id = 3;
SELECT * FROM users;
___________________________________________________________________________________
1️⃣ Delete a record
DELETE FROM users WHERE id = 3;
What it does?
--> Deletes the row from the users table
Only the row where id = 3 is removed
Important
___________________________________________________________________________________
2️⃣ Add a CHECK constraint
ALTER TABLE users
ADD CONSTRAINT chk_dob
CHECK (date_of_birth > '1920-01-01');
What it does
-
Adds a rule (constraint) named chk_dob
-
Ensures date_of_birth must be after 1 Jan 1920
Why it is used
'1910-05-10' → Not allowed
'1934-03-20' → Allowed
__________________________________________________________________________________
3️⃣ Insert a record
INSERT INTO users (name, email, gender, date_of_birth, salary) VALUES
(NULL, 'RAJ12@gmail.com', 'Male', '1934-03-20', 55000.00);
What it does
Note
__________________________________________________________________________________
4️⃣ Count total users
SELECT COUNT(*) FROM users;
__________________________________________________________________________________
5️⃣ Count female users
SELECT COUNT(*) FROM users WHERE gender = "Female";
What it does
____________________________________________________________________________________________
6️⃣ Count male users
SELECT COUNT(*) FROM users WHERE gender = "Male";
What it does
___________________________________________________________________________________________
7️⃣ Find minimum & maximum salary
SELECT
MIN(salary) AS min_salary,
MAX(salary) AS max_salary
FROM users;
What it does
____________________________________________________________________________________________
Query 1: Rounding salary values
SELECT salary,
ROUND(salary) AS rounded,
FLOOR(salary) AS floored,
CEIL(salary) AS ceiled
FROM users;
What it does
This query shows how different rounding functions work on the salary column from the users table.
Explanation of each column
CEIL(salary) (or CEILING)
→ Rounds up to the nearest whole number
-
Example: 45000.1 → 45001
FLOOR(salary)
→ Rounds down to the nearest whole number ________________________________________________________________________
Query 2: Checking if a user is female
SELECT name, gender,
IF(gender = 'Female','Yes','No') AS is_female
FROM users;
What it does
This query checks the gender of each user and tells whether the user is female or not.
🔍 Explanation
_________________________________________________________________________________
Auto Commit AND TRANSACTIONS
1. USE Startersql;
USE Startersql;
Meaning
2. SET autocommit = 0;
SET autocommit = 0;
Meaning
-
Turns OFF auto-commit mode.
-
Changes made to the database will NOT be saved automatically.
-
You must use COMMIT to permanently save changes.
-
You can use ROLLBACK to undo changes.
Default behavior in MySQL:
3. SELECT * FROM users;
SELECT * FROM users;
Meaning
-
Displays all records from the users table.
-
This command does not change data, so it is not affected by commit or rollback.
Meaning
Important:
5. COMMIT;
COMMIT;
Meaning
-
Permanently saves all changes made since the last commit.
-
After commit, changes cannot be rolled back.
6.DELETE FROM users WHERE id = 6;
DELETE FROM users WHERE id = 6;
Meaning
What happens depends on COMMIT / ROLLBACK
_______________________________________________________________
Understanding Primary key in MySQL
What is a PRIMARY KEY?
A PRIMARY KEY is a column (or group of columns) that uniquely identifies each row in a table.
Think of it like:
Rules of PRIMARY KEY
A PRIMARY KEY column must:
-
Be UNIQUE
→ No two rows can have the same value
-
NOT be NULL
→ Every row must have a value
-
Only one PRIMARY KEY per table
(But it can contain multiple columns → composite key)
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(100)
);
id is the PRIMARY KEY
Each id value must be unique and not NULL
Composite PRIMARY KEY (Multiple Columns)
Used when one column alone is not enough.
CREATE TABLE orders (
order_id INT,
product_id INT,
PRIMARY KEY (order_id, product_id)
);
Adding PRIMARY KEY to Existing Table
ALTER TABLE users
ADD PRIMARY KEY (id);
Why PRIMARY KEY is Important?
-
Ensures data integrity
-
Makes data fast to search
-
Required for FOREIGN KEY relationships
-
Prevents duplicate records
__________________________________________________________________
What is a FOREIGN KEY?
A FOREIGN KEY is a connection between two tables.
“This value in my table must already exist in another table.”
You cannot add marks for a student who doesn’t exist.
Why do we need FOREIGN KEY?
SQL Example
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE marks (
marks_id INT PRIMARY KEY,
student_id INT,
marks INT,
FOREIGN KEY (student_id) REFERENCES students(student_id)
);
__________________________________________________________
Why ON DELETE CASCADE is powerful?
DELETE FROM users WHERE id = 1;
All related rows in addresses will be automatically deleted.
____________________________________________________________________
USE startersql;
SHOW TABLES;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
) ENGINE=InnoDB;
CREATE TABLE addresses (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
street VARCHAR(255),
city VARCHAR(100),
state VARCHAR(100),
pincode VARCHAR(10),
CONSTRAINT fk_user
FOREIGN KEY (user_id)
REFERENCES users(id)
ON DELETE CASCADE
) ENGINE=InnoDB;
DELETE FROM users WHERE id = 1;
USE startersql;
DROP TABLE IF EXISTS addresses;
CREATE TABLE addresses (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
street VARCHAR(255),
city VARCHAR(100),
state VARCHAR(100),
pincode VARCHAR(10),
CONSTRAINT fk_user FOREIGN KEY (user_id) references users(id) ON DELETE CASCADE
);
_________________________________________________________________________
-- 1️⃣ Use database
USE startersql;
-- 2️⃣ Drop tables if already exist (child first, then parent)
DROP TABLE IF EXISTS addresses;
DROP TABLE IF EXISTS users;
-- 3️⃣ Create USERS table (Parent)
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE
) ENGINE=InnoDB;
-- 4️⃣ Create ADDRESSES table (Child with Foreign Key)
CREATE TABLE addresses (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
street VARCHAR(255),
city VARCHAR(100),
state VARCHAR(100),
pincode VARCHAR(10),
CONSTRAINT fk_user
FOREIGN KEY (user_id)
REFERENCES users(id)
ON DELETE CASCADE
) ENGINE=InnoDB;
-- 5️⃣ Insert RANDOM users
INSERT INTO users (name, email) VALUES
('Rahul Sharma', 'rahul.sharma@gmail.com'),
('Neha Verma', 'neha.verma@gmail.com'),
('Aakash Patil', 'aakash.patil@gmail.com'),
('Pooja Singh', 'pooja.singh@gmail.com'),
('Suresh Nair', 'suresh.nair@gmail.com');
-- 6️⃣ Insert RANDOM addresses
INSERT INTO addresses (user_id, street, city, state, pincode) VALUES
(1, 'Flat 12, Lotus Apartments', 'Delhi', 'Delhi', '110001'),
(2, 'B-45, Green Park', 'Jaipur', 'Rajasthan', '302017'),
(3, 'Near City Mall, MG Road', 'Bengaluru', 'Karnataka', '560001'),
(4, 'House No. 22, Lake View Road', 'Bhopal', 'Madhya Pradesh', '462001'),
(5, 'A-9, Palm Grove', 'Kochi', 'Kerala', '682011');
-- 7️⃣ View tables
SELECT * FROM users;
SELECT * FROM addresses;
-- 8️⃣ Test ON DELETE CASCADE (delete a random user)
DELETE FROM users WHERE id = 3;
-- 9️⃣ Check result after delete
SELECT * FROM users;
SELECT * FROM addresses;
____________________________________________________________________________
-- 1️⃣ Use database
USE startersql;
-- 2️⃣ Drop tables if already exist (child first, then parent)
DROP TABLE IF EXISTS addresses;
DROP TABLE IF EXISTS users;
-- 3️⃣ Create USERS table (Parent)
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE
) ENGINE=InnoDB;
-- 4️⃣ Create ADDRESSES table (Child with Foreign Key)
CREATE TABLE addresses (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
street VARCHAR(255),
city VARCHAR(100),
state VARCHAR(100),
pincode VARCHAR(10),
CONSTRAINT fk_user
FOREIGN KEY (user_id)
REFERENCES users(id)
ON DELETE CASCADE
) ENGINE=InnoDB;
-- 5️⃣ Insert RANDOM users
INSERT INTO users (name, email) VALUES
('Rahul Sharma', 'rahul.sharma@gmail.com'),
('Neha Verma', 'neha.verma@gmail.com'),
('Aakash Patil', 'aakash.patil@gmail.com'),
('Pooja Singh', 'pooja.singh@gmail.com'),
('Suresh Nair', 'suresh.nair@gmail.com');
-- 6️⃣ Insert RANDOM addresses
INSERT INTO addresses (user_id, street, city, state, pincode) VALUES
(1, 'Flat 12, Lotus Apartments', 'Delhi', 'Delhi', '110001'),
(2, 'B-45, Green Park', 'Jaipur', 'Rajasthan', '302017'),
(3, 'Near City Mall, MG Road', 'Bengaluru', 'Karnataka', '560001'),
(4, 'House No. 22, Lake View Road', 'Bhopal', 'Madhya Pradesh', '462001'),
(5, 'A-9, Palm Grove', 'Kochi', 'Kerala', '682011');
-- 7️⃣ View tables
SELECT * FROM users;
SELECT * FROM addresses;
-- 8️⃣ Test ON DELETE CASCADE (delete a random user)
DELETE FROM users WHERE id = 3;
-- 9️⃣ Check result after delete
SELECT * FROM users;
SELECT * FROM addresses;
__________________________________________________________________________
SQL joins
MySQL JOINs — Simple & Clear Explanation
In MySQL, JOINs are used to combine rows from two or more tables based on a related column (usually a primary key ↔ foreign key).
| id | user_id | city |
| -- | ------- | ----- |
| 1 | 1 | Delhi |
| 2 | 2 | Pune |
1️⃣ INNER JOIN
Syntax
SELECT columns
FROM table1
INNER JOIN table2
ON condition;
Example
SELECT users.id, users.name, addresses.city
FROM users
INNER JOIN addresses
ON users.id = addresses.user_id;
| id | name | city |
| -- | ----- | ----- |
| 1 | Rahul | Delhi |
| 2 | Neha | Pune |
2️⃣ LEFT JOIN
What it does
Returns all rows from LEFT table
If no match → NULL on right side
SELECT users.id, users.name, addresses.city
FROM users
LEFT JOIN addresses
ON users.id = addresses.user_id;
| id | name | city |
| -- | ------ | ----- |
| 1 | Rahul | Delhi |
| 2 | Neha | Pune |
| 3 | Aakash | NULL |
3️⃣ RIGHT JOIN
SELECT users.id, users.name, addresses.city
FROM users
RIGHT JOIN addresses
ON users.id = addresses.user_id;
| id | name | city |
| -- | ----- | ----- |
| 1 | Rahul | Delhi |
| 2 | Neha | Pune |
4️⃣ FULL JOIN
SELECT users.id, users.name, addresses.city
FROM users
LEFT JOIN addresses ON users.id = addresses.user_id
UNION
SELECT users.id, users.name, addresses.city
FROM users
RIGHT JOIN addresses ON users.id = addresses.user_id;
5️⃣ SELF JOIN
When to use
When a table joins with itself
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.id;
6️⃣ CROSS JOIN
SELECT users.name, addresses.city
FROM users
CROSS JOIN addresses;
_________________________________________________________________________
When to Use UNION in MySQL?
What is UNION?
UNION is used to combine the results of two or more SELECT queries into one result set.
Important:
BASIC RULES OF UNION
All SELECT statements must have:
1️⃣ Same number of columns
2️⃣ Same data types (compatible)
3️⃣ Same column order
1️⃣ When you want data from DIFFERENT tables with SAME structure
Example
Employees from two tables:
SELECT name, email FROM current_employees
UNION
SELECT name, email FROM ex_employees;
2️⃣ When you want to REMOVE duplicate rows
SELECT city FROM users
UNION
SELECT city FROM addresses;
3️⃣ When MySQL does NOT support a feature (FULL JOIN)
SELECT u.id, u.name, a.city
FROM users u
LEFT JOIN addresses a
ON u.id = a.user_id
UNION
SELECT u.id, u.name, a.city
FROM users u
RIGHT JOIN addresses a
ON u.id = a.user_id;
4️⃣ When combining results with DIFFERENT CONDITIONS
SELECT name, email FROM users WHERE city = 'Mumbai'
UNION
SELECT name, email FROM users WHERE city = 'Delhi';
5️⃣ UNION ALL – When you WANT duplicates (faster)
| UNION | UNION ALL |
| ------------------ | ---------------- |
| Removes duplicates | Keeps duplicates |
| Slower | Faster |
SELECT city FROM users
UNION ALL
SELECT city FROM addresses;
______________________________________________________________________
Views In MYSQL
Why Views are Used
Simplify complex queries
Improve security (hide columns)
Reuse queries
Logical data abstraction
Basic Syntax
CREATE VIEW view_name AS
SELECT columns
FROM table_name
WHERE condition;
Example (Using users table)
| id | name | email | salary |
| -- | ----- | --------------------------------- | ------ |
| 1 | Rahul | [Rahul@gmail.com](Rahul@gmail.com) | 80000 |
| 2 | Neha | [neha@gmail.com](neha@gmail.com) | 60000 |
| 3 | Amit | [amit@gmail.com](amit@gmail.com) | 90000 |
1️⃣ Create a View
CREATE VIEW rich_users AS
SELECT id, name, salary
FROM users
WHERE salary > 70000;
2️⃣ Use (Query) a View
SELECT * FROM rich_users;
| id | name | salary |
| -- | ----- | ------ |
| 1 | Rahul | 80000 |
| 3 | Amit | 90000 |
3️⃣ View is Always Updated
UPDATE users SET salary = 65000 WHERE id = 1;
SELECT * FROM rich_users;
4️⃣ Drop a View
5️⃣ Replace / Modify a View
CREATE OR REPLACE VIEW rich_users AS
SELECT id, name
FROM users
WHERE salary > 60000;
6️⃣ Update Data Through a View (Sometimes)
Allowed when:
Single table
No JOIN
No GROUP BY
No DISTINCT
UPDATE rich_users
SET salary = 85000
WHERE id = 3;
7️⃣ Types of Views
CREATE VIEW v_simple AS
SELECT * FROM users;
Complex View
CREATE VIEW v_complex AS
SELECT city, COUNT(*)
FROM addresses
GROUP BY city;
_______________________________________________________________________________
MySQL INDEXES
What is an Index?
An index is a special data structure that speeds up SELECT queries by allowing MySQL to find rows faster.
Basic Syntax
Create an index
CREATE INDEX idx_name ON users(name);
Drop an index
DROP INDEX idx_name ON users;
Show indexes
Types of Indexes in MySQL
1️⃣ PRIMARY KEY Index
-
Automatically created
-
Unique + Not NULL
-
Only one per table
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50)
);
2️⃣ UNIQUE Index
CREATE UNIQUE INDEX idx_email ON users(email);
3️⃣ NORMAL (NON-UNIQUE) Index
CREATE INDEX idx_salary ON users(salary);
4️⃣ COMPOSITE (MULTI-COLUMN) Index
CREATE INDEX idx_name_salary ON users(name, salary);
Works for:
WHERE name = 'Krishna'
WHERE name = 'Krishna' AND salary > 50000
Does NOT work for:
5️⃣ FULLTEXT Index
CREATE FULLTEXT INDEX idx_bio ON users(bio);
SELECT * FROM users
WHERE MATCH(bio) AGAINST ('developer');
6️⃣ FOREIGN KEY Index
-
Automatically created
-
Speeds up joins
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT,
FOREIGN KEY (user_id) REFERENCES users(id)
);
Example: Without vs With Index
Without Index
SELECT * FROM users WHERE email = 'a@gmail.com';
With Index
CREATE INDEX idx_email ON users(email);
Check if Index is Used
EXPLAIN SELECT * FROM users WHERE email = 'a@gmail.com';
_____________________________________________________________________________________
Subqueries in MYSQL
Subqueries in MySQL – Simple & Clear Explanation
A subquery is a query inside another query.It helps you get data based on the result of another query.
Also called nested query.
SELECT column
FROM table
WHERE column = (SELECT column FROM table);
Inner query runs first
Outer query uses its result
1️⃣ Single-Row Subquery
Returns one value
Example:
SELECT name
FROM users
WHERE salary > (
SELECT salary FROM users WHERE name = 'Krishna'
);
2️⃣ Multiple-Row Subquery
Returns multiple values
Use with IN, ANY, ALL
Example using IN
SELECT name
FROM users
WHERE department_id IN (
SELECT id FROM departments WHERE location = 'Pune'
);
Example using ANY
SELECT name
FROM users
WHERE salary > ANY (
SELECT salary FROM users WHERE department_id = 2
);
Example using ALL
SELECT name
FROM users
WHERE salary > ALL (
SELECT salary FROM users WHERE department_id = 2
);
3️⃣ Subquery in SELECT Clause
SELECT name,
(SELECT AVG(salary) FROM users) AS avg_salary
FROM users;
4️⃣ Subquery in FROM Clause (Derived Table)
SELECT department, avg_salary
FROM (
SELECT department, AVG(salary) AS avg_salary
FROM users
GROUP BY department
) AS dept_avg;
5️⃣ Correlated Subquery
Depends on outer query row
Runs for each row ❗
Example:
SELECT name, salary
FROM users u
WHERE salary > (
SELECT AVG(salary)
FROM users
WHERE department_id = u.department_id
);
Subquery with UPDATE
UPDATE users
SET salary = salary + 5000
WHERE id IN (
SELECT id FROM employees WHERE performance = 'Excellent'
);
DELETE FROM users
WHERE id NOT IN (
SELECT user_id FROM orders
);
_______________________________________________________________________________
GROUPS BY & HAVING IN MY SQL
AND PROCEDURES IN MYSQL
Stored Procedures in MySQL
A stored procedure is a saved block of SQL statements that you can run again and again.
Basic Structure of a Procedure
MySQL Stored Procedures (BEGIN – END, GROUP BY, HAVING)
What is a Stored Procedure?
A stored procedure is a saved SQL program that runs on the database server.
BEGIN – END
BEGIN
-- SQL statements
END
GROUP BY :- Used to group rows that have the same values.
HAVING
Used to filter groups
WHERE filters rows
HAVING filters groups
FULL PRACTICAL EXAMPLE
Table NOT created initially
20 members
Use GROUP BY + HAVING inside PROCEDURE
1️⃣ Create Database
CREATE DATABASE companydb;
USE companydb;
2️⃣ Create Stored Procedure
0 Comments
if you have any doubts. please let me know