///to practice all the query, see the handbook codes

given by harry try these queries on those tables.///

What is a Database Management System
(DBMS)?
A Database Management System (DBMS) is software that interacts with
end users, applications, and the database itself to capture and analyze
data. It allows for the creation, retrieval, updating, and management of
data in databases.

CRUD:

C: CREATE

R: READ

U: UPDATE

D: DELETE

RELATIONAL DATABASE MANAGEMENT SYSTEM(RDBMS)

 Follows a fixed and predefined schema
 Data is organized into structure tables with rows and columns
 Ensures data integrity through constraints and relationships
 Utilizes SQL (structured query language) for defining and
manipulating data.
 Well-suited for applications requiring complex queries and
transactional consistency
 Ex: MySQL, Postgre SQL, SQL server.

NON-RELATIONAL DATABASE MANAGEMENT SYSTEM(NoSQL)

 Provides flexible data storage and modelling.
 Does not enforce a fixed or consistent schema
 SQL is not mandatory for data manipulation and querying
 Ideal for use cases where read operations are more frequent than
write operations
 Ex: MongoDB, Cassandra, Redis, neo4j
 Getting Started with MySQL

What is a Database?
A database is a container that stores related data in an organized way. In
MySQL, a database holds one or more tables.

A database is a way to store data in a format which makes it easily

accessible.

Think of it like:

•Folder analogy:

•A database is like a folder.

•Each table is a file inside that folder.
•The rows in the table are like the content inside each file.

Step 1: Create a Database

CREATE DATABASE startersql;

After creating the database, either:

•Right-click it in MySQL Workbench and select “Set as Default

Schema”, or
•Use this SQL command:

USE startersql;

//dono mese kuch bhi kar skte hai for using the specific database
Step 2: Create a Table
Now we’ll create a simple table:
users

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
);

This table will store basic user info.

If you want to check kitne columns bane hai and uska datatype,

Just write:

SELECT * FROM users;

Step 3: Drop the Database

You can delete the entire database (and all its tables) using:

DROP DATABASE startersql;

Be careful — this will delete everything in that database.

Data Types Explained

• •
• : Integer type, used for whole numbers.
INT

: Variable-length string, up to 100 characters.

VARCHAR(100)
•
ENUM : A string object with a value chosen from a list of permitted
values. eg.
gender ENUM('Male', 'Female',
'Other')

DATE : Stores date values. Eg

date_of_birth
DATE

•TIMESTAMP: Stores date and time, automatically set to the

current timestamp when a row is created
•BOOLEAN:Stores TRUE or FALSE values, often used for flags like
is_active.

[[
In SQL, the BOOLEAN data type is used to store logical values,
representing either TRUE or FALSE. It's commonly used for flags
like is_active or is_deleted to indicate the status of a record.
 Database Support:
 PostgreSQL, PL/SQL: Have a
dedicated BOOLEAN type.
 MySQL, Oracle: May use TINYINT or BIT types for
Boolean values. use TINYINT(1) (e.g., MySQL), where 0 is
typically FALSE and 1 is TRUE.
]]

DECIMAL(10, : Stores exact numeric data

values, useful for financial data. The first number
2)
is the total number of digits, and the second is the
number of digits after the decimal point.

 DATE - format YYYY-MM-DD

 DATETIME - format: YYYY-MM-DD HH:MI:SS
Constraints Explained
• : Automatically generates a unique number for each
• AUTO_INCREMENTrow.

• : Uniquely identifies each row in the table.

PRIMARY
KEY
• NOT NULL : Ensures a column cannot have a NULL
value.
•
UNIQUE : Ensures all values in a column are different.

DEFAULT : Sets a default value for a column if no value is provided. eg.

created_at TIMESTAMP DEFAULT , is_active
CURRENT_TIMESTAMP BOOLEAN
DEFAULT
TRUE

##MySQL me kise bhi line kea age agr ap double dash lagate hsai wo comment out ho
jati hai

Ex:

--SELECT*FROM USERS

It will be ignored
Working with Tables in MySQL

Selecting Data from a Table

Select All Columns

SELECT * FROM users;

users
This fetches every column and every row from the table.

Select Specific Columns

SELECT name, email FROM users;

This only fetches

name an columns from all rows.
the d
email

Renaming a Table
To rename an existing table:

RENAME TABLE users TO customers;

To rename it back:

RENAME TABLE customers TO users;

Altering a Table
You can use to modify an existing table.
ALTER
TABLE
Add a Column

ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT TRUE;

Drop a Column

ALTER TABLE users DROP COLUMN is_active;

Modify a Column Type

ALTER TABLE users MODIFY COLUMN name VARCHAR(150);

#VARCHAR(150) SE VARCHAR(100) MODIFY KARNE KE LIYE I USE THIS

Move a Column to the First Position

To move a column (e.g., ) to the first position:
email

ALTER TABLE users MODIFY COLUMN email VARCHAR(100) FIRST;

To move a column after another column (e.g., move after ):

gender
ALTER TABLE users MODIFY COLUMN gender ENUM('Male', name
'Female', 'Other') AFTER
name;
Inserting Data into MySQL Tables
To add data into a table, we use the statement.
INSERT INTO

Insert Without Specifying Column Names (Full

Row Insert)
This method requires you to provide values for all columns in order,
except
columns with default valuesAUTO_INCREMENT
or .

INSERT INTO users VALUES

(1, 'Alice', 'alice@example.com', 'Female', '1995-05-14', DEFAULT);

Not recommended if your table structure might change (e.g., new

columns added later).

Insert by Specifying Column Names (Best

Practice)
This method is safer and more readable. You only insert into specific
columns.

INSERT INTO users (name, email, gender, date_of_birth)

VALUES ('Bob', 'bob@example.com', 'Male', '1990-11-23');

or for multiple rows:

INSERT INTO users (name, email, gender, date_of_birth)

VALUES ('Bob', 'bob@example.com', 'Male', '1990-11-23'),
('Charlie', 'charlie@example.com', 'Other', '1988-02-17');
The remaining columns (which ) and
like is
id AUTO_INCREMENT created_at
(which has a default) are automatically handled by MySQL.

Insert Multiple Rows at Once

INSERT INTO users (name, email, gender, date_of_birth) VALUES

('Charlie', 'charlie@example.com', 'Other', '1988-02-17'),
('David', 'david@example.com', 'Male', '2000-08-09'),
('Eva', 'eva@example.com', 'Female', '1993-12-30');

This is more efficient than inserting rows one by one.

Querying Data in MySQL using
SELECT
The statement is used to query data from a table.
SELECT

Basic Syntax

SELECT column1, column2 FROM table_name;

To select all columns:

SELECT * FROM users;

Filtering Rows withWHERE

Equal To

SELECT * FROM users WHERE gender = 'Male';

Not Equal To

SELECT * FROM users WHERE gender != 'Female';

-- or
SELECT * FROM users WHERE gender <> 'Female';
Greater Than / Less Than

SELECT * FROM users WHERE date_of_birth < '1995-

01-01'; SELECT * FROM users WHERE id > 10;

Greater Than or Equal / Less Than or Equal

SELECT * FROM users WHERE id >= 5;

SELECT * FROM users WHERE id <= 20;

Working with NULL

IS NULL

SELECT * FROM users WHERE date_of_birth IS NULL;

IS NOT NULL

SELECT * FROM users WHERE date_of_birth IS NOT NULL;

BETWEEN

SELECT * FROM users WHERE date_of_birth BETWEEN '1990-01-01' AND '2000-12-31';

IN

SELECT * FROM users WHERE gender IN ('Male', 'Other');

LIKE (Pattern Matching)

SELECT * FROM users WHERE name LIKE 'A%'; -- Starts with A

SELECT * FROM users WHERE name LIKE '%a'; -- Ends with a
SELECT * FROM users WHERE name LIKE '%li%'; -- Contains 'li'

AND / OR

SELECT * FROM users WHERE gender = 'Female' AND date_of_birth > '1990-
01-01';

SELECT * FROM users WHERE gender = 'Male' OR gender =

'Other';

ORDER BY

SELECT * FROM users ORDER BY date_of_birth ASC;

SELECT * FROM users ORDER BY name DESC;

LIMIT

SELECT * FROM users LIMIT 5; -- Top 5 rows

SELECT * FROM users LIMIT 10 OFFSET 5; -- Skip first 5 rows, then get next 10
SELECT * FROM users LIMIT 5, 10; -- Get 10 rows starting from the 6th row
(Same as
above)

SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

UPDATE - Modifying Existing Data
The statement is used to change values in one or more rows.
UPDATE

Basic Syntax

UPDATE table_name
SET column1 = value1, column2 =
value2 WHERE condition;

Example: Update One Column

UPDATE users
SET name = 'Alicia'
WHERE id = 1;

id = 1 to “Alicia”.
This changes the name of the user with

Example: Update Multiple Columns

UPDATE users
SET name = 'Robert', email =
'robert@example.com' WHERE id = 2;

Without WHERE Clause (Warning)

UPDATE users
SET gender = 'Other';
 This updates every row in the table. Be very careful when omitting
WHERE
the clause.

Quick Quiz: Practice Your

UPDATE Skills
Try answering or running these queries based onusers
your table.

1. id = 5 Update the salary of

user with to ₹70,000.

UPDATE users
SET salary = 70000
WHERE id = 5;

2. Change the name of the user with email

aisha@example.co
to Aisha . m
Khan
UPDATE users
SET name = 'Aisha Khan'
WHERE email = 'aisha@example.com';

3. Increase salary by ₹10,000 for all users whose

salary is less than ₹60,000.

UPDATE users
SET salary = salary +
10000 WHERE salary <
60000;
4. Set the gender of userIshaan to Other .

UPDATE users
SET gender = 'Other'
WHERE name =
'Ishaan';

5. Reset salary of all users to ₹50,000 (Careful - affects all

rows).

UPDATE users
SET salary = 50000;

Note: This query will overwrite salary for every user. Use with caution!
DELETE - Removing Data from a
Table
DELETE
The statement removes rows from a table.

Basic Syntax

DELETE FROM
table_name WHERE
condition;

Example: Delete One Row

DELETE FROM users

WHERE id = 3;

Delete Multiple Rows

DELETE FROM users

WHERE gender = 'Other';

Delete All Rows (but keep table structure)

DELETE FROM users;

Drop the Entire Table (use with caution)

DROP TABLE users;

 This removes the table structure and all data permanently.

Best Practices
•Always use unless you’re intentionally updating/deleting
everything.
WHERE
•Consider
SELECTwith the WHEREclause first to confirm
running a will be same what
affected:

SELECT * FROM users WHERE id = 3;

•Always back up important data before performing destructive

operations.
MySQL Constraints
Constraints in MySQL are rules applied to table columns to ensure the
accuracy, validity, and integrity of the data.

1. UNIQUE Constraint
Ensures that all values in a column are different.

Example (during table creation):

CREATE TABLE users (

id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE

);

Add UNIQUE using ALTER :

TABLE
ALTER TABLE users
ADD CONSTRAINT unique_email UNIQUE (email);

2. NOT NULL Constraint

Ensures that a column cannot contain NULL values.
Example:

CREATE TABLE users (

id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL

);

Change an existing column to NOT NULL:

ALTER TABLE users

MODIFY COLUMN name VARCHAR(100) NOT NULL;

Make a column nullable again:

ALTER TABLE users

MODIFY COLUMN name VARCHAR(100) NULL;

3. CHECK Constraint
Ensures that values in a column satisfy a specific condition.

Example: Allow only dates of birth after Jan 1, 2000

ALTER TABLE users

ADD CONSTRAINT chk_dob CHECK (date_of_birth > '2000-01-01');

Naming the constraintchk_dob

( ) helps if you want to drop it later.

4. DEFAULT Constraint
Sets a default value for a column if none is provided during insert.
Example:

CREATE TABLE users (

id INT PRIMARY KEY,
is_active BOOLEAN DEFAULT TRUE

);

Add DEFAULT usingALTER :

TABLE
ALTER TABLE users
ALTER COLUMN is_active SET DEFAULT TRUE;

5. PRIMARY Constraint
KEY
Uniquely identifies each row. Must be NOT NULL and UNIQUE.

Example:

CREATE TABLE users (

id INT PRIMARY KEY,
name VARCHAR(100)
);

Add later with ALTER :

TABLE
ALTER TABLE users
ADD PRIMARY KEY (id);

6. AUTO_INCREMEN
T
Used with to automatically assign the next number.
PRIMARY
KEY
Example:

CREATE TABLE users (

id INT AUTO_INCREMENT PRIMARY
KEY, name VARCHAR(100)
);

id in
Each new row gets the next available integer value .

Summary Table
Constraint Purpose

UNIQUE Prevents duplicate values

NOT NULL Ensures value is not NULL

CHECK Restricts values using a condition

DEFAULT Sets a default value

PRIMARY KEY Uniquely identifies each row

AUTO_INCREMENT Automatically generates unique numbers

SQL Functions (MySQL)
SQL functions help you analyze, transform, or summarize data in your
tables.

We’ll use theusers table which includes:

•
id , name , email , gender , date_of_birth, salary , created_at

1. Aggregate Functions
These return a single value from a set of rows.

COUNT()

Count total number of users:

SELECT COUNT(*) FROM users;

Count users who are Female:

SELECT COUNT(*) FROM users WHERE gender = 'Female';

MIN() and MAX()

Get the minimum and maximum salary:

SELECT MIN(salary) AS min_salary, MAX(salary) AS max_salary FROM users;

 SUM()

Calculate total salary payout:

SELECT SUM(salary) AS total_payroll FROM users;

AVG()

Find average salary:

SELECT AVG(salary) AS avg_salary FROM users;

Grouping with GROUP BY

Average salary by gender:

SELECT gender, AVG(salary) AS

avg_salary FROM users
GROUP BY gender;

2. String Functions

LENGTH()

Length of user names:

SELECT name, LENGTH(name) AS name_length FROM users;

#SARE USER KE NAME KI LENGTH MIL JAYEGI

 LOWER() and UPPER()

Convert names to lowercase or uppercase:

SELECT name, LOWER(name) AS lowercase_name FROM

users; SELECT name, UPPER(name) AS uppercase_name
FROM users;

#NAME LI LOWERCASE AND UPPERCASE AA JAYEGI

CONCAT()

Combine name and email:

SELECT CONCAT(name, ' <', email, '>') AS user_contact FROM users;

3. Date Functions

NOW()

Current date and time:

SELECT NOW();

EX:
SELECT id,
gender,
LOWER(name) as lower,
concat(LOWER(name),’5677’) as username,
NOW() AS time,
LENGTH(name) as name_len
FROM users;

YEAR() , MONTH() , DAY()

 Extract parts of :
date_of_birth

SELECT name, YEAR(date_of_birth) AS birth_year FROM users;

#if you write YEAR(NOW) it will give 2025….

1. FLOOR() Function : The FLOOR() function returns the largest integer value which is
less than or equal to a number.

Example-1 : When the argument holds a positive number.

SELECT FLOOR(21.53);

Output :

21

Example-2 : When the argument holds a negative number.

SELECT FLOOR(-21.53);

Output :

-22

CEILING() Function : The CEILING() function returns the smallest integer value which
is greater than or equal to a number.

Example-1 : When the argument holds a positive number.

SELECT CEILING(21.53);

Output :

22
Example-2 : When the argument holds a negative number.

SELECT CEILING(-21.53);

Output :

-21
DATEDIFF(
)
Find number of days between today and birthdate:

SELECT name, DATEDIFF(CURDATE(), date_of_birth) AS days_lived FROM users;

TIMESTAMPDIFF(
)
Calculate age in years:

SELECT name, TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS age FROM users;

4. Mathematical Functions

ROUND() , FLOOR() , CEIL()

SELECT salary,
ROUND(salary) AS
rounded, FLOOR(salary)
AS floored,
CEIL(salary) AS ceiled
FROM users;

MOD()

Find even or odd user IDs:

SELECT id, MOD(id, 2) AS remainder FROM users;

5. Conditional Functions

IF()

SELECT name, gender,

IF(gender = 'Female', 'Yes', 'No') AS
is_female FROM users;

Summary Table
Function Purpose

COUNT() Count rows

SUM() Total of a column

AVG() Average of values

MIN() / MAX() Lowest / highest value

LENGTH() String length

CONCAT() Merge strings

YEAR() / DATEDIFF() Date breakdown / age

ROUND() Rounding numbers

IF() Conditional logic

MySQL Transactions and
AutoCommit
By default, MySQL operates in AutoCommit mode. This means that every
SQL
statement is treated as a transaction and is committed automatically.
However, for more control over when changes are saved, you can turn
AutoCommit off and
manage transactions manually.

1. Disabling AutoCommit
When AutoCommit is off, you can explicitly control when to commit or
rollback changes.

To disable AutoCommit:

SET autocommit = 0;

This turns off AutoCommit, meaning that changes you make won’t be
saved to the database unless you explicitly tell MySQL to commit them.

Important: Until you executeCOMMIT

a , your changes are not permanent.

2. COMMIT — Save Changes to the Database

Once you’ve made changes and you’re confident that everything is
correct, you can
use the COMMIT command to save those changes.

To commit a transaction:

COMMIT
;
This saves all the changes made since COMMIT or ROLLBACK . After this
the last point, the changes become
permanent.

3. ROLLBACK — Revert Changes to the Last Safe

Point
If you make an error or decide you don’t want to save your changes, you
can
rollback the transaction to its previous state.

To rollback a transaction:

ROLLBACK
;

This undoes all changes since the COMMIT

last or ROLLBACK .

Example Workflow
Here’s a simple example of COMMIT an ROLLBACK in a transaction:
using d

1.Turn off AutoCommit:

SET autocommit = 0;

2.Make some changes (e.g., updating a salary):

UPDATE users SET salary = 80000 WHERE id = 5;

3.Decide whether to commit or rollback:

1.If you’re happy with the changes, run:

COMMIT
;

2.If you’re not happy and want to revert the changes, run:
 ROLLBACK
;

4. Enabling AutoCommit Again

If you want to turn AutoCommit back on (so that every statement is
automatically committed), you can do so with:

SET autocommit = 1;

Best Practices
•Use COMMIT when you want to make changes permanent.
•Use
ROLLBACK to discard changes if something goes wrong.
•Consider disabling AutoCommit when performing complex updates
to avoid saving partial or incorrect data.
 Understanding PRIMARY in MySQL
A
KEY
is a constraint in SQL that uniquely identifies each row in a
table.
PRIMARY
KEY
It is one of the most important concepts in database design.

What is a Primary Key?

•A PRIMARY :
KEY
•Must be unique
•Cannot be NULL
•Is used to identify rows in a table
•Can be a single column or a combination of columns(COMPOSITE
KEY)
•Each table can have only one primary key

Example:

CREATE TABLE users (

id INT AUTO_INCREMENT PRIMARY
KEY, name VARCHAR(100)
);

How Is PRIMARY Different from UNIQUE ?

KEY
At first an might seem similar since both
glance, d
PRIMARY UNIQUE prevent
KEY But there are important differences:
duplicate values.
 Feature PRIMARY KEY UNIQUE

Must be
Yes Yes
unique

Allows Yes (one or more NULLs

No
NULL allowed)
values
How many
Only one per table Can have multiple
allowed

Required Recommended,
Optional
by table often required

Dropping Cannot be easily Can be dropped anytime

dropped

Example with UNIQUE

CREATE TABLE users (

id INT AUTO_INCREMENT PRIMARY
KEY, email VARCHAR(100) UNIQUE,
name VARCHAR(100)
);

In this example:

• id is the unique identifier for each row.

•
email must be unique, but is not the primary key.

Can I Drop a PRIMARY KEY?

Yes, but it is more restricted than dropping a constraint.
UNIQUE

ALTER TABLE users DROP PRIMARY KEY;

 This may fail if the primary key is being used elsewhere (like in a
foreign key or auto_increment column).

To drop a UNIQUE constraint:

ALTER TABLE users DROP INDEX email;

Auto Increment
In MySQL, is often used with attribute to
a the
PRIMARY AUTO_INCREMENT
KEYgenerate unique values for new rows.
automatically

CREATE TABLE users (

id INT AUTO_INCREMENT PRIMARY
KEY, name VARCHAR(100)
);

This means that every time you insert a new row, MySQL will
automatically assign a
unique value to theid column. You can change the starting value of
AUTO_INCREMENT usin
g:

ALTER TABLE users AUTO_INCREMENT = 1000;

Key
Takeaways
•Use for the main identifier of a row.
PRIMARY
•Use KEY
UNIQUE for enforcing non-duplicate values in other columns (like
or email
phone).
•You can have only one primary key, but you can have
many unique constraints.
 Foreign Keys in MySQL
A foreign key is a column that creates a link between two tables. It
ensures that the value in one table must match a value in another
table.

This is used to maintain data integrity between related data.

Why Use Foreign Keys?

Imagine this scenario:

You have a users table. Now you want to store each user’s address.
Instead of
users
putting address columns table, you create a
inside the
separate using a foreign
addresses table, and link it to
users
key.

Creating a Table with a Foreign Key

Let’s create an table where each address belongs to a user.
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),
FOREIGN KEY (user_id) REFERENCES users(id)
);
Explanation:
• user_id is a foreign key.
•It references
the idcolumn in the table.
users
•This ensures that every address must be linked to a valid user.

Dropping a Foreign Key

To drop a foreign key, you need to know its constraint name. MySQL
auto- generates it if you don’t specify one, or you can name it
yourself:

CREATE TABLE addresses (

id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,

CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id)

);

To drop it:

ALTER TABLE addresses

DROP FOREIGN KEY fk_user;

Adding a Foreign Key Later (Using ALTER)

Suppose the foreign key was not defined during table creation. You can
add it
later usingALTER :
TABLE

ALTER TABLE addresses

ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);
Adding ON DELETE Action
By default, if you delete a user that has related addresses, MySQL will
throw an
error. You can control this behavior with
ON DELETE .

Example withON DELETE :

CASCADE
If you want addresses to be automatically deleted when the user is
deleted:

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
);

Or alter it later:

ALTER TABLE addresses

ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE
CASCADE;

IF CONSTRAINT KA NAME NHI DUNGI TOH AUTOMATICALLY USSE NAME DILA DIYA
JAYEGA…

Other ON DELETE Options

ON DELETE
Behavior
Option

CASCADE Deletes all related rows in child table

SET NULL Sets the foreign key to NULL in the child table

RESTRICT Prevents deletion of parent if child exists (default)

Summary
•Foreign keys connect tables and enforce valid references.
•You can create them inline or with
ALTER .
TABLE
•You can drop them by name.
•Use ON DELETE to control what happens when the parent row is
deleted.

EX

ON DELETE CASCADE:
If I write
DELETE FROM ADDRESSES WHERE ID=2;
//SO tushar ka address delete karne se kya tushar delete ho jayega no
way

But if I
DELETE FROM USERS WHERE ID=2;
//Agr main tushar ko delete karunga toh then uska address bhi delete
ho jayega because tushar hi nhi raha toh,so jo tushar ko reference
kar raha hai addresses table me,wo bhi delete ho jayega because on
delete cascade likha hai….

SQL JOINs in MySQL

In SQL, JOINs are used to combine rows from two or more tables based on
related columns — usually a foreign key in one table referencing a
primary key in another.

We’ll use the following two tables:

users table
id name

1 Aarav

2 Sneha

3 Raj

addresses table
id user_id city

1 1 Mumbai

2 2 Kolkata

3 4 Delhi

Note: user_id is a foreign key that references

users.id .

1. INNER JOIN
Returns only the matching rows from both tables.
 SELECT users.name,
addresses.city FROM users
INNER JOIN addresses ON users.id = addresses.User_id;

Output:
name city

Aarav Mumbai

Sneha Kolkata

Raj is excluded because there is no matching address.

Delhi is excluded
because itsuser_id (4) is not inusers .

Visual Representation:

user addresse
s s

| 1 | 1
| |
| 2 | 2
| |
| | | |

=> only matching

pairs

2. LEFT JOIN
Returns all rows from the left table ( ), and matching rows from the
right users
table (addresses ). If no match is found, NULLs are returned.

SELECT users.name,
addresses.city FROM users
LEFT JOIN addresses ON users.id = addresses.user_id;
Output:
name city

Aarav Mumbai

Sneha Kolkata

Raj NULL

Raj is shown even though he doesn’t have an address.

Visual Representation:

user addresse
s s

| 1 | 1
| |
| 2 | 2
| |
| 3 | |
|
=> all users + matched addresses (or
NULL)

3. RIGHT JOIN
Returns all rows from the right table ( ), and matching rows from
the addresses
left table (users ). If no match is found, NULLs are returned.

SELECT users.name,
addresses.city FROM users
RIGHT JOIN addresses ON users.id = addresses.user_id;

Output:
name city

Aarav Mumbai

Sneha Kolkata
 name city

NULL Delhi

Delhiis shown even though it points to user_id

a that doesn’t exist.

Visual Representation:

user addresse
s s

| 1 | 1
| |
| 2 | 2
| |
| | | 4
|
=> all addresses + matched users (or
NULL)

Summary Table
JOIN Type Description

INNER JOIN Only matching rows from both tables

LEFT JOIN All rows from left table + matching from right

RIGHT JOIN All rows from right table + matching from left
SQL UNION and UNION
ALL
Th
UNION operator in SQL is used to combine the result sets of two
e
SELECT or more statements. It removes duplicates by default.

If you want to include all rows including duplicates, use .

UNION ALL

Example Scenario
You already have users
a table for active users. Now, we’ll create an
admin_users table to store users who are administrators or have special
roles. We
will then combine the names from both tables using
UNION .

Step 1: Create the

admin_user Table
s
CREATE TABLE admin_users
( id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
gender ENUM('Male', 'Female', 'Other'),
date_of_birth DATE,
salary INT
);
Step 2: Insert Sample Data into
admin_user
s
INSERT INTO admin_users (id, name, email, gender, date_of_birth, salary)
VALUES (101, 'Anil Kumar', 'anil@example.com', 'Male', '1985-04-12',
60000),
(102, 'Pooja Sharma', 'pooja@example.com', 'Female', '1992-09-20', 58000),
(103, 'Rakesh Yadav', 'rakesh@example.com', 'Male', '1989-11-05', 54000),
(104, 'Fatima Begum', 'fatima@example.com', 'Female', '1990-06-30', 62000);

Step 3: UseUNION to Combine Data

Let’s combine the active and admin user names.

SELECT name FROM

users UNION
SELECT name FROM admin_users;

This returns a single list of unique names from both tables.

UNION Example
ALL
If you want to keep duplicate names (if any), use ALL .
UNION

SELECT name FROM

users UNION ALL
SELECT name FROM admin_users;

Using More Than One Column

You can also select multiple columns as long
SELECT queries return the
as both
same number of columns with compatible
types.
 SELECT name, salary FROM users
UNION
SELECT name, salary FROM admin_users;

Adding separate roles

SELECT name, 'User' AS role FROM

users UNION
SELECT name, 'Admin' AS role FROM admin_users;

Using Order By withUNION

SELECT name FROM

users UNION
SELECT name FROM admin_users
ORDER BY name;

Rules of UNION
1.The number of columns and their data types must
SELECT
match in all statements.
2. UNION removes duplicates by default.
3. UNION ALL keeps duplicates.
When to Use UNION
•When you have two similar tables (like current and archived data).
•When you need to combine filtered results (e.g., high-salary users
from two sources).
•When performing cross-category reporting.

Summary
Operator Behavior

UNION Combines results, removes duplicates

UNION ALL Combines results, keeps duplicates

Self JOIN in MySQL
A Self JOIN is a regular join, but the table is joined with itself.

This is useful when rows in the same table are related to each other. For
example, when users refer other users, and we store the ID of the person
who referred them
in the sameusers table.

Step 1: Add areferred_by_i Column

d
We’ll extend the existing
users table to include a column called
referred_by_id,
which holds theid of the user who referred them.

ALTER TABLE users

ADD COLUMN referred_by_id INT;

This column:

•Will be NULL for users who were not referred.

•Will contain theid of another user who referred them.

Step 2: Insert Referral Data (Optional)

Assuming is the first user, and referred others:
id = 1

UPDATE users SET referred_by_id = 1 WHERE id IN (2, 3); -- User 1 referred

Users 2
and 3
UPDATE users SET referred_by_id = 2 WHERE id -- User 2 referred User
= 4; 4
Step 3: Use a Self JOIN to Get Referrer Names
We want to get each user’s name along with the name of the person who
referred them.

SELECT
a.id,
a.name AS user_name,
b.name AS referred_by
FROM users a
INNER JOIN users b ON a.referred_by_id = b.id;

Explanation:
• refers to the user being queried.
a
• b refers to the user who referred
them.
in referred_by_id are also
• LEFT JOIN is used so that users NULL
with included.

Sample Output:
id user_name referred_by

1 Aarav NULL

2 Sneha Aarav

3 Raj Aarav

4 Fatima Sneha

Summary
•Use Self JOIN when you need to join a table with itself.
•In referral-based relationships, store the referrer’s
id in the same
table.
•Use aliases aand bto differentiate the two instances of the same
like table.
MySQL Views
A view in MySQL is a virtual table based on the resultSELECT
of a query. It does
not store data itself — it always reflects the current data in the base

tables. Views are useful when:

•You want to simplify complex queries

•You want to reuse logic
•You want to hide certain columns from users
•You want a “live snapshot” of filtered data

Creating a View
Suppose we want a view that lists all users earning more than ₹70,000.

CREATE VIEW high_salary_users

AS SELECT id, name, salary
FROM users
WHERE salary > 70000;

////select * from bhi likh skti ho if sare columns chhiye

Querying the View

SELECT * FROM high_salary_users;

This will return all users from the

users table where salary is above ₹70,000.
Demonstrating That a View is Always Up-To-Date
Let’s see what happens when the underlying data changes.

Step 1: View before update

SELECT * FROM high_salary_users;

Output:

id name salary

2 Sneha 75000

5 Fatima 80000

Step 2: Update a user’s salary

UPDATE users
SET salary = 72000
WHERE name = 'Raj';

Step 3: Query the view again

SELECT * FROM high_salary_users;

New Output:

id name salary

2 Sneha 75000

5 Fatima 80000

3 Raj 72000

Notice how Raj is now included in the view — without updating the
view
itself. That’s because views always reflect live data from the original
table.
Dropping a View
To remove a view:

DROP VIEW high_salary_users;

Summary
•Views act like saved SELECT queries
•Views are not duplicated data
•Changes to base tables are reflected automatically
•Great for simplifying complex queries or creating filtered access
 MySQL Indexes
Indexes in MySQL are used to speed up data retrieval. They work like the
index of a book — helping the database engine find rows faster,
especially for searches, filters, and joins.

Viewing Indexes on a Table

To see the indexes on a table, use:

SHOW INDEXES FROM users;

users
This shows all the indexes currently defined table, including the
on the automatically created primary key
index.

Creating a Single-Column
Index email . You can speed this
up
Suppose you’re frequently searching users
by their
by indexing the column.
email

CREATE INDEX idx_email ON users(email);

What this does:

•Creates an index named
idx_email
•Improves performance of queries like:

SELECT * FROM users WHERE email = 'example@example.com';

Important Notes
•Indexes consume extra disk space
•Indexes slow
, , and operations slightly
down INSERT UPDATE DELETE
(because the index must be updated)
•Use indexes only when needed (i.e., for columns WHERE , JOIN ,
used in
ORDER BY )

Creating a Multi-Column Index

If you often query users using
gender and salary , a multi-column index
both is
more efficient than separate
indexes.

CREATE INDEX idx_gender_salary ON users(gender, salary);

Usage Example:

SELECT * FROM users

WHERE gender = 'Female' AND salary > 70000;

This query can take advantage of the combined index on and salary .
gender

//it will execute faster as we have made the index of gender and salary…

Index Order Matters

(gender,
For a multi-column index on :
salary)
•This works efficiently:

WHERE gender = 'Female' AND salary > 70000

 •But this may not use the index effectively:

WHERE salary > 70000

gender
Because the first column in the index ( ) is missing in the filter.

Dropping an Index
To delete an index:

DROP INDEX idx_email ON users;

Summary
Feature Description

SHOW INDEXES View current indexes on a table

CREATE INDEX Create single or multi-column indexes

DROP INDEX Remove an index

Use when Query performance on large tables is a concern

Avoid on Columns that are rarely queried or always unique

Subqueries in MySQL
A subquery is a query nested inside another query. Subqueries are useful
for breaking down complex problems into smaller parts.

They can be used in:

•
SELECT statemen
• WHEREts clauses
• FROM clauses

Example Scenario: Salary Comparison

Suppose we want to find all users who earn more than the average salary
of all users.

Scalar Subquery Example

This subquery returns a single value — the average salary — and we
compare each user’s salary against it.

SELECT id, name, salary

FROM users
WHERE salary > (
SELECT AVG(salary) FROM users
);

Explanation:
•The inner SELECT AVG(salary) FROM returns the average
query: users salary.
 •The outer query selects all users with a salary greater than that
average.

Subquery withIN
Now let’s say we want to find users who have been referred by someone
who earns more than ₹75,000.

SELECT id, name, referred_by_id

FROM users
WHERE referred_by_id IN (
SELECT id FROM users WHERE salary > 75000
);

Explanation:
•The inner query:SELECT id FROM users WHERE salary > returns a
75000 who earn more than
of user IDs (referrers)
₹75,000.
list is in that list.
•The outer query selects users whose
referred_by_id

Other Places Subqueries Are Used

You can also use subqueries:

•Insid
SELECT columns (called scalar
e
subqueries) clause to create
•In FROM
the derived tables

Example in :
SELECT

SELECT name, salary,

(SELECT AVG(salary) FROM users) AS
average_salary FROM users;

This shows each user’s salary along with the overall average.
Summary
Subquery Type Use Case

Scalar Subquery Returns one value (e.g. AVG, MAX)

Subquery with IN Returns multiple values

Subquery in SELECT Shows related calculated value

Subquery in FROM Acts as a virtual table

Subqueries are powerful tools when filtering based on computed or

dynamic conditions.

Ex:

SELECT id, name, referred_by_id

FROM users
WHERE referred_by_id IN (
SELECT id FROM users WHERE salary < (SELECT AVG(salary) FROM users)
);

////ye mujhe wo users mil gaye hai jo refer kiye gye hue hai aise users ke
dwara jo average salary se kam paise kamate hai…..
GROUP BY and HAVING in MySQL
The GROUP BY clause is used to group rows that have the same values in
specified columns. It is typically used with aggregate functions like SUM ,
COUNT
,
AVG , MIN , or MAX .

Th
HAVING clause is used to filter groups after aggregation —
e
WHERE similar to how filters individual rows.

Example Table:users
Assume this is your table:
users

id name gender salary referred_by_id

1 Aarav Male 80000 NULL

2 Sneha Female 75000 1

3 Raj Male 72000 1

4 Fatima Female 85000 2

5 Priya Female 70000 NULL

GROUP BY Example: Average Salary by Gender

SELECT gender, AVG(salary) AS

average_salary FROM users
GROUP BY gender;
Explanation:
•This groups users by gender.
•Then calculates the average salary for each group.

GROUP BY with COUNT

Find how many users were referred by each user:

SELECT referred_by_id, COUNT(*) AS

total_referred FROM users
WHERE referred_by_id IS NOT
NULL GROUP BY referred_by_id;

Output:
referred_by_id total_referred

1 2

2 1

HAVING Clause: Filtering Groups

Let’s say we only want to show genders where the average salary is
greater than
₹75,000.

SELECT gender, AVG(salary) AS

avg_salary FROM users
GROUP BY gender
HAVING AVG(salary) > 75000;
Why WHERE ?
not
is used before grouping.
• WHERE
is used after groups are formed — it’s the only way to filter
• HAVING
aggregated values.

Another Example: Groups with More Than 1

Referral

SELECT referred_by_id, COUNT(*) AS

total_referred FROM users
WHERE referred_by_id IS NOT
NULL GROUP BY referred_by_id
HAVING COUNT(*) > 1;

Summary
Can use
Clause Purpose
aggregates?

WHERE Filters rows before grouping No

GROUP
Groups rows based on column values N/A
BY

HAVING Filters groups after aggregation Yes

Us to organize data, to filter those groups based on

e
GROUP BY and HAVING
aggregate conditions.

ROLLUP
To get subtotals and grand totals, you can use :
ROLLUP
 SELECT gender, COUNT(*) AS
total_users FROM users
GROUP BY gender WITH ROLLUP;

Explanation:
•This will give you a count of users by gender, along with a grand
total for all users.
 Stored Procedures in MySQL
A stored procedure is a saved SQL block that can be executed later.
It’s useful when you want to encapsulate logic that can be reused
multiple times — like queries, updates, or conditional operations.

Why Change the Delimiter?

By default, MySQL uses; to end SQL statements.

But when defining stored procedures, we use

; inside the procedure as
well. This
can confuse MySQL. To avoid this, we temporarily change the delimiter
(e.g. to
$$or // ) while creating the procedure.

Syntax for Creating a Stored Procedure

DELIMITER $
$

CREATE PROCEDURE
procedure_name()
BEGIN

-- SQL statements go here

END$$

DELIMITER
;

After the procedure is created, we reset the delimiter back to .

;
Creating a Procedure with Input Parameters
Let’s say you want to create a stored procedure that inserts a new
users user into the table.

Example:

DELIMITER $
$

CREATE PROCEDURE AddUser(

IN p_name VARCHAR(100),
IN p_email VARCHAR(100),
IN p_gender ENUM('Male', 'Female',
'Other'), IN p_dob DATE,
IN p_salary INT
)
BEGIN
INSERT INTO users (name, email, gender, date_of_birth,
salary) VALUES (p_name, p_email, p_gender, p_dob,
p_salary);

DELIMITER
;

AddUser that accepts five input parameters.

This creates a procedure named

Calling the Procedure

You can call the procedure using:

CALL AddUser('Kiran Sharma', 'kiran@example.com', 'Female', '1994-06-15',

72000);

This will insert the new user into the table.

users
Notes
•Input parameters are declared usingIN
the keyword.
•Stored procedures are stored in the database and can be reused.

Viewing Stored Procedures

SHOW PROCEDURE STATUS WHERE Db = 'startersql';

Dropping a Stored Procedure

DROP PROCEDURE IF EXISTS AddUser;

Summary
Command Purpose

DELIMITER $$ Temporarily change statement

delimiter
CREATE PROCEDURE Defines a new stored procedure

CALL procedure_name(...) Executes a stored procedure

DROP PROCEDURE Removes an existing procedure

Triggers in MySQL
A trigger is a special kind of stored program that is automatically
executed
(triggered) when a specific event occurs in a table — INSERT , UPDATE , or
such as
DELETE .

Triggers are commonly used for:

•Logging changes
•Enforcing additional business rules
•Automatically updating related data

Basic Trigger Structure

CREATE TRIGGER
trigger_name AFTER INSERT
ON table_name FOR EACH
ROW
BEGIN
-- statements to execute
END;
Triggers can be fired:

• or
BEFORE AFTER an event
•On INSERT ,
UPDATE , or DELETE

Scenario: Log Every New User Insertion

Suppose we want to log every time a new user is inserted into the
users table.
We’ll create a separate table called
user_log to store log entries.
 Step 1: Create the Log Table

CREATE TABLE user_log (

id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,

name VARCHAR(100),
created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create the Trigger

We now define a trigger that runs after a new user is added.

DELIMITER $
$

CREATE TRIGGER after_user_insert

AFTER INSERT ON users
FOR EACH ROW
BEGIN
INSERT INTO user_log (user_id,
name) VALUES (NEW.id, NEW.name);
END$$

DELIMITER
;

INSERT INTO USERS(name,email,gender,date_of_birth,salary)

Values(‘rohan,’rohan2@gmail.com’,’male’,’2007-04-04’,54000);

Select * from user_log;

Explanatio
n:
means the trigger fires after the user is inserted.
• AFTER
INSERT
•
NEW refers to the new row being added table.
to the
users
•We insert the new user’s ID and name into the table.
user_log
Step 3: Test the Trigger

CALL AddUser('Ritika Jain', 'ritika@example.com', 'Female', '1996-03-12',

74000);

Now check theuser_log table:

SELECT * FROM user_log;

You should see Ritika’s info automatically logged.

///Checking it by already bana hua stored procedure se

Dropping a Trigger
If you need to remove a trigger:

DROP TRIGGER IF EXISTS after_user_insert;

Summary
Trigger Component Description

BEFORE / AFTER When the trigger runs

INSERT / UPDATE /
What kind of action triggers it
DELETE

NEW.column Refers to the new row (for INSERT ,

UPDATE )

OLD.column Refers to the old row (for UPDATE , DELETE

)
FOR EACH ROW Executes for each affected row
More on MySQL
This section covers some essential MySQL features and operators that
help you write more powerful and flexible queries.

1. Logical Operators
Logical operators are used to combine multiple conditions in aclause.
WHERE

Operator Description Example

salary > 50000 AND gender =

AND All conditions must be
true 'Male'

At least one condition is gender = 'Male' OR gender =

OR
true 'Other'

NOT Reverses a condition NOT gender = 'Female'

2. Add a Column to an Existing Table

Use to add a column:
ALTER
TABLE

ALTER TABLE users

ADD COLUMN city VARCHAR(100);

This adds a new column

city to users table.
named the
3. Wildcard Operators
Wildcards are used with the operator for pattern matching in text.
LIKE

Wildcard Description Example

Matches any
% WHERE name LIKE 'A%' (starts with
sequence A)

Matches a single WHERE name LIKE '_a%' (second

_
character letter is ‘a’)

4. LIMIT with OFFSET OFFSET

is used to limit the number of rows skips a number of

LIMIT
returned. rows before starting to return rows.

SELECT * FROM users

ORDER BY id
LIMIT 5 OFFSET 10;

This skips the first 10 rows and returns the

next 5. Alternative syntax:

SELECT * FROM users

ORDER BY id
LIMIT 10, 5;

This also skips 10 and returns 5 (syntax:

LIMIT offset, ).
count

5. DISTINCT Keyword
is used to return only unique values.
DISTINCT
 SELECT DISTINCT gender FROM users;

Returns a list of unique gender values fromusers

the table.

6. TRUNCATE Keyword
removes all rows from a table, but keeps the table structure.
TRUNCATE

TRUNCATE TABLE users;

•Faster thanDELETE FROM

users
•Cannot be rolled back (unless in a transaction-safe environment)

7.CHANGE vs MODIFY Column

Bot an are used to alter existing columns in a table, but
h
CHANGE d MODIFY they
work slightly differently.

CHANGE: Rename and change datatype

ALTER TABLE users

CHANGE COLUMN city location VARCHAR(150);

This
city to location and changes its type.
renames

MODIFY: Only change datatype

ALTER TABLE users

MODIFY COLUMN salary BIGINT;

This changes only the datatypesalary

of .