MySQL Questions

1. How do you update data in MySQL?

To update data in MySQL, you use the UPDATE statement combined with the SET
clause to define new values, and a WHERE clause to target specific rows
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Important Note: Always use the WHERE clause when updating data. If you omit it,
every single row in your table will be updated with the new values.
2. How do you delete data from MySQL?
To delete data from MySQL, you use the DELETE FROM statement combined with
a WHERE clause to target specific rows.
For removing data, you have three primary methods depending on your goal:
i. Removing Specific Rows (DELETE)
Use this when you want to remove specific records while keeping the rest of your
table intact.

DELETE FROM table_name

WHERE condition;
ii. Emptying an Entire Table Safely (TRUNCATE)
If you want to clear all data from a table completely, use TRUNCATE. It is much
faster than DELETE because it drops and recreates the table instead of deleting rows
one by one.
TRUNCATE TABLE table_name;
 Resets Auto-Increments: This resets your ID counters back to 1.
 No WHERE Clause: You cannot use a WHERE condition with truncate.
 Cannot Roll Back: This operation usually cannot be undone using transaction
rollbacks.
iii. Deleting the Table Entirely (DROP)
If you want to destroy both the data and the table structure permanently, use DROP.
DROP TABLE table_name;
3. How do you insert data into a MySQL table?
To insert data into a MySQL table, you use the INSERT INTO statement combined with
the VALUES clause.
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
 4. What is a key in MySQL?
In MySQL, a key (also called an index) is a specialized database attribute
applied to one or more columns to identify rows quickly, enforce unique
constraints, and establish relationships between different tables.

Think of a key like an index at the back of a textbook: instead of reading every
page to find a topic, you look at the index to jump straight to the correct page.

1. Primary Key (PRIMARY KEY)

A primary key uniquely identifies each record in a table. [1]
 Rules: It must contain completely unique values, and it cannot contain NULL
values. A table can only have one primary key.
 Analogy: Your passport number or a student ID.
CREATE TABLE students (
student_id INT AUTO_INCREMENT,
first_name VARCHAR(50),
PRIMARY KEY (student_id) -- Uniquely identifies each student
);
2. Unique Key (UNIQUE)
A unique key ensures that all values in a column are distinct from one another. [1]
 Rules: Unlike a primary key, a unique key can accept NULL values (and even
multiple NULL values depending on the configuration). You can have multiple
unique keys on a single table.
 Analogy: Your phone number or email address (everyone has a unique one,
but some people might not have one on file).
CREATE TABLE users (
user_id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE -- No two users can share the same email
);

3. Foreign Key (FOREIGN KEY)

A foreign key links two tables together. It is a column in one table that points to the
PRIMARY KEY of another table, maintaining referential integrity.
 Rules: It prevents invalid data from being inserted into the foreign key
column, as the value must already exist in the parent table.
 Analogy: A receipt row that references a specific product_id. Without that
product existing in your inventory table, the receipt wouldn't make sense.
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

[Link] Key (Compound Key)

A composite key is a primary key or unique key that consists of two or more
columns combined together.
 Rules: Individual columns within the composite key can have duplicate
values, but the combination of all columns must be completely unique.
 Analogy: A classroom seat assignment. Multiple students can sit in row "A",
and multiple students can sit in seat "4", but only one student can sit in the
combination of "Row A, Seat 4".

CREATE TABLE course_enrollments (

student_id INT,
course_id INT,
enrollment_date DATE,
PRIMARY KEY (student_id, course_id) -- A student can only enroll in a specific
course once
);
5. Alternate / Candidate Key
 Candidate Key: Any column or set of columns that could qualify to be a primary key
because it contains unique, non-null data.
 Alternate Key: The candidate keys that you did not choose as your primary key. For
example, if you choose student_id as the primary key, then the student's
social_security_number or email becomes the alternate key.
5. What is the LIKE statement?
The LIKE operator in MySQL is used in a WHERE clause to search for a specific
pattern within a text column.
While the standard equals sign (=) looks for an exact match, LIKE allows you to
perform fuzzy matching or wildcard searches (similar to using a search bar).

1. The Percent Sign (%)

The % wildcard represents zero, one, or multiple characters.
Example
Pattern Meaning
Matches

Starts with 'Apple',

'A%'
"A" 'Alex', 'A'

Ends with 'Banana',

'%A'
"A" 'Amanda', 'A'

Contains "A" 'Banana',

'%A%'
anywhere 'Apple', 'Car'

2. The Underscore Sign (_)

 The _ wildcard represents a single, specific
character.
Example
Pattern Meaning
Matches

3 letters, starts with C, 'Cat', 'Cot',

'C_t'
ends with t 'Cut'

Exactly 3 letters, ends 'Jim', 'Sam',

'__m'
with m 'Yam'

Starts with A, has any

'Albert',
'A_b_'% char, then b, any char,
'Arbor'
then anything

Common Code Examples

[Link] emails from a specific domain
SELECT * FROM users
WHERE email LIKE '%@[Link]';
[Link] products containing a search keyword
SELECT * FROM products
WHERE product_name LIKE '%wireless%';
3. Finding phone numbers with a specific area code pattern
-- Finds numbers like 555-1234, 555-9999, etc.
SELECT * FROM customers
WHERE phone_number LIKE '555-____';
6. What are foreign keys?
A foreign key is a column (or a collection of columns) in one database table that
uniquely points to a column (usually the primary key) in another table.

Its primary purpose is to link two tables together and maintain referential
integrity, which prevents your database from ending up with broken, "orphaned"
data.

How to Create a Foreign Key

Scenario 1: Creating a Table with a Foreign Key

CREATE TABLE orders (

order_id INT AUTO_INCREMENT PRIMARY KEY,
order_date DATE,
 customer_id INT, -- Must be the same data type as the parent table's ID

-- Defining the constraint

FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Scenario 2: Adding a Foreign Key to an Existing Table

ALTER TABLE orders
ADD CONSTRAINT fk_order_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
Why Use Foreign Keys? (Data Integrity)
Foreign keys act as strict structural guardrails for your database by enforcing these golden
rules:
1. No Ghost References: You cannot insert a row in the child table with a foreign key
value that doesn't exist in the parent table. (e.g., You cannot create an order for
customer_id = 999 if Customer 999 doesn't exist).
2. No Accidental Deletions: You cannot delete a row from the parent table if a child
table is still relying on it. (e.g., MySQL will block you from deleting Alice's account if
she has active orders).
7. What is the difference between a LEFT JOIN and a RIGHT JOIN?
The fundamental difference between a LEFT JOIN and a RIGHT JOIN is which
table's data is guaranteed to appear in the results when no matching
record is found in the other table.
Both are types of Outer Joins, meaning they return matching rows plus
unmatched rows from one side of the relationship.
🔍 Quick Definitions
 LEFT JOIN: Returns all rows from the left table, and the matched rows from
the right table. If there is no match, the right side will display NULL.
 RIGHT JOIN: Returns all rows from the right table, and the matched rows
from the left table. If there is no match, the left side will display NULL. [1, 2, 3,
4, 5]
The "Left" table is the one written before the JOIN keyword, and the "Right"
table is the one written after it.

8. How do you create a database?

To create a database in MySQL, you use the CREATE DATABASE statement.
CREATE DATABASE database_name;

The Safe Method (IF NOT EXISTS)

CREATE DATABASE IF NOT EXISTS my_company_db;
9. How do you change a column name?
To change a column name in MySQL, you use the ALTER TABLE statement.
Depending on your MySQL version, you have two clear ways to do this:
ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;

10. What is MySQL?

MySQL is an open-source Relational Database Management System
(RDBMS) that uses Structured Query Language (SQL) to manage, store, and
retrieve data.
It acts as a digital filing cabinet for applications, organizing information into
structured tables consisting of rows and columns.

 My: Named after co-founder Michael Widenius's daughter, My.

 SQL (Structured Query Language): The universal programming language
used to talk to the database (to insert, read, update, or delete data).
 Relational: It organizes data into separate tables instead of one giant list. You
can link these tables together using "keys" (like connecting a customers table to
an orders table).

How Does MySQL Work? (The Client-Server Model)

MySQL runs on a client-server architecture:
1. The Server (MySQL): Sits on a computer or cloud environment where the
actual data files reside. It listens for requests, processes complex logic, and
keeps data secure.
2. The Clients: Applications or user tools (like a WordPress website, a mobile
app, or desktop software like MySQL Workbench).
3. The Interaction: The client sends an SQL query (e.g., "Show me all products
under ₹500"), and the MySQL server instantly executes the search and sends
the matching data back.

Why is MySQL So Popular?

MySQL is one of the most widely used databases in the world because of a few
major advantages:
 Open-Source & Free: Anyone can download and use it without paying
licensing fees.
 High Performance: It is optimized to handle millions of data rows and
complete queries in milliseconds.
 Rock-Solid Security: It features advanced encryption, user access controls,
and strict data verification features.
 The "LAMP" Stack Backbone: It is the default database engine for global
platforms like WordPress, Facebook, YouTube, and Netflix.

11. What is the difference between MySQL and SQL?

The difference between SQL and MySQL is the difference between a language
and a software program. [1]
The easiest analogy is to think of SQL as the English language, and MySQL as
a specific book written in English.
📊 Direct Comparison
SQL (Structured Query
Feature MySQL
Language)
 A query language used to
A database software program
What is it? communicate with
that uses SQL.
databases.
To define, manipulate, and To store, organize, secure, and
Purpose
query data. manage data.
The language standards The software updates frequently
Updates change rarely (managed by with new features and security
ISO/ANSI). patches.
It is an open language
Owned and maintained by
Ownership standard; no single company
Oracle Corporation.
owns it.

12. What is the difference between a database and a table?

The difference between a database and a table is a matter of hierarchy and
containment: a database is the entire container, while a table is a specific
structured list inside that container.
The easiest analogy is to think of a database as a digital filing cabinet, and
a table as a specific folder or spreadsheet stored inside that cabinet.

📊 Direct Comparison

Feature Database Table

The outer container that The inner structure where

What is it?
holds all related data assets. actual records are stored.

Contains multiple tables, Contains a strict grid layout

Structure views, users, keys, and of columns (attributes) and
security settings. rows (records).

Example ecommerce_db customers, orders, products

SQL CREATE DATABASE CREATE TABLE customers

Command store_db; (...);
 13. What’s the difference between CHAR and VARCHAR?
The fundamental difference between CHAR and VARCHAR is how they store
data and manage memory in your database.
The easiest analogy is to think of CHAR as a fixed-size wooden box, and
VARCHAR as a flexible, expanding plastic bag.
📊 Direct Comparison

Feature CHAR(M) VARCHAR(M)

Type Fixed-length string. Variable-length string.

Storage Always uses the maximum allocated Only uses the exact space needed for the
behavior size, padding empty spaces with blanks. text, plus 1–2 bytes for length prefixing.

Maximum
Up to 255 characters. Up to 65,535 characters.
Length

Faster lookups (fixed memory jumps are Slower lookups (the engine has to calculate
Performance
highly optimized). where each row ends).

14. What is the difference between an INT and a FLOAT?

The fundamental difference between an INT and a FLOAT is whether they
can store decimal points.
The easiest analogy is to think of an INT as a counting number (like
counting people) and a FLOAT as a measuring number (like measuring
temperature or distance).

📊 Direct Comparison
Feature INT (Integer) FLOAT (Floating-Point)

Whole
Data Type Fractional/Decimal numbers.
numbers only.

Decimals? No. Yes.

Approximate (uses scientific

Accuracy 100% exact.
rounding logic).

Storage Size Always 4 bytes. Always 4 bytes.

Example
-5, 0, 42, 1005 -1.5, 3.14159, 99.9, 0.002
Values

15. Is MySQL case sensitive?

The answer to whether MySQL is case-sensitive is: It depends on what you
are looking at.
Different parts of MySQL handle case sensitivity in completely different ways,
depending on your operating system and configuration.

1. Database and Table Names (Depends on OS)

 In MySQL, database and table names correspond to actual folders and files
on your computer's hard drive. Because of this, case sensitivity depends
entirely on the Operating System running your MySQL server:
 Linux / Unix: Case-sensitive. A table named Employees is completely
different from a table named employees.
 Windows: Case-insensitive. MySQL will treat Employees and employees as
the same table.
 macOS: Case-insensitive by default (because macOS uses a case-
insensitive file system layout).
💡 Best Practice: To avoid massive headaches when moving a database from
a Windows development machine to a live Linux server, always write
database and table names in lowercase using underscores (e.g.,
user_profiles, order_details).

16. How would you add a column to a MySQL table?

To add a column to an existing MySQL table, you use the ALTER TABLE
statement combined with the ADD clause.

ALTER TABLE table_name

ADD column_name data_type constraints;

17. How would you delete a column in a MySQL table?

To delete a column from an existing MySQL table, you use the ALTER TABLE
statement combined with the DROP COLUMN clause.
ALTER TABLE table_name
DROP COLUMN column_name;
18. How do you join tables in MySQL?
To join tables in MySQL, you use the JOIN clause inside a SELECT statement.
Joins allow you to link rows from two or more tables together based on a related
column between them (usually a Foreign Key linking to a Primary Key).
The 4 Primary Types of Joins
1. INNER JOIN (The Default)
Returns rows only when there is a matching value in both tables. If a row in the
first table doesn't have a matching record in the second table, it is completely
hidden from the results.
 Analogy: A list of students and their assigned lockers. Students without
lockers and empty lockers are left off the list.
SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;
 2. LEFT JOIN (or LEFT OUTER JOIN)
Returns all rows from the left table, plus the matching rows from the right table.
If there is no match on the right side, the results will display NULL.
 Analogy: A list of all registered customers. If a customer hasn't placed an
order yet, their order information will just show up blank (NULL).
SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;
3. RIGHT JOIN (or RIGHT OUTER JOIN)
Returns all rows from the right table, plus the matching rows from the left table. If
there is no match on the left side, the results will display NULL.
 Analogy: The exact opposite of a LEFT JOIN. (Note: Developers rarely use
RIGHT JOIN because you can achieve the exact same layout by swapping
the table order in a LEFT JOIN).
SELECT customers.customer_name, orders.order_id
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;
4. CROSS JOIN
Returns the Cartesian product of the two tables, meaning it matches every single
row from the first table with every single row from the second table. It does not
use an ON clause because it creates all possible combinations.
 Analogy: Matching 3 t-shirt colors with 3 sizes to generate a list of all 9
possible product variants.
SELECT [Link], sizes.size_name
FROM products
CROSS JOIN sizes;
19. Can the primary key of a table be dropped?
Yes, you can drop a primary key in MySQL, but how you do it depends on
whether the column uses the AUTO_INCREMENT property. Because a primary
key enforces data uniqueness, removing it requires altering the table's
structural constraints.
Here is exactly how to do it safely based on your table layout.

Scenario 1: The Primary Key is NOT Auto-Incremented

If your primary key column does not automatically generate numbers, you can
drop it instantly with a single command:
 ALTER TABLE table_name
DROP PRIMARY KEY;

🛑 Scenario 2: The Primary Key IS Auto-Incremented (The Catch)

If your primary key column is set to AUTO_INCREMENT, running the drop
command above will trigger a MySQL Error:
"Incorrect table definition; there can be only one auto column and it must be
defined as a key."
In MySQL, an auto-incrementing column cannot exist without being a key.
To successfully drop the primary key, you must strip away the
AUTO_INCREMENT
-- Step 1: Modify the column to remove AUTO_INCREMENT (redefine its data
type)
ALTER TABLE users
MODIFY user_id INT;

-- Step 2: Now you can safely drop the primary key constraint
ALTER TABLE users
DROP PRIMARY KEY;

20. Does a primary key need to be a unique ID?

Yes, a primary key must absolutely contain completely unique values. By
definition, its entire purpose is to serve as a foolproof identifier so the database
engine can pin down one exact row without any risk of confusion.

However, a primary key does not need to be a random or auto-incrementing

serial number (like 1, 2, 3 or a UUID).

The Two Core Rules of a Primary Key

For any column—or group of columns—to qualify as a primary key, it must obey
two strict laws enforced by MySQL:
1. Uniqueness: No two rows in that table can ever share the identical primary
key value.
2. No Nulls: The value cannot be left blank (NULL). Every single record must
have a valid identifier.

21. What is DISTINCT in MySQL?

The DISTINCT keyword in MySQL is used inside a SELECT statement to remove
duplicate rows from your query results. It forces MySQL to scan the specified
columns and return only the completely unique values.

SELECT DISTINCT column_name

FROM table_name;

22. What is the difference between mysql_connect and mysql_pconnect?

The fundamental difference between mysql_connect() and mysql_pconnect() in PHP
is how the connection to the database is handled after the script finishes executing.
📊 Direct Comparison
Feature mysql_connect() mysql_pconnect()
Connection
Non-persistent (Standard). Persistent.
Type
Checks for an already open
Opens a fresh connection
Lifecycle connection first before creating a
every time the script runs.
new one.
Closes automatically when the Stays open in the background
When it Closes script ends, or via even after the script finishes
mysql_close(). executing.
Completely ignored; the
mysql_close() Successfully closes the
connection is kept alive for future
Impact connection.
requests.

23. What is a stored procedure?

A stored procedure is a prepared collection of SQL statements that is saved
directly inside your MySQL database server.
Instead of writing and sending the same complex SQL queries over and over
again from your backend application (like Python, PHP, or [Link]), you
compile the code directly on the database engine. You can then execute the
entire routine with a single command line.
Think of a stored procedure like a macro in Excel or a custom function in
programming: you write the logic once, save it, and call it by its name
whenever you need it.

DELIMITER //

CREATE PROCEDURE GiveRaise(

IN emp_id INT, -- Input parameter: The employee to update
IN raise_amount DECIMAL(10,2) -- Input parameter: The dollar amount to
add
)
BEGIN
-- 1. Update the employee's record
UPDATE employees
SET salary = salary + raise_amount
WHERE employee_id = emp_id;

-- 2. Track the action in an audit log table

INSERT INTO salary_logs (employee_id, change_amount, change_date)
VALUES (emp_id, raise_amount, NOW());
END //

DELIMITER ;
 How to Execute It:
CALL GiveRaise(105, 5000.00);

📥 Working with Parameters

Stored procedures can accept and pass back values using three types of
parameters:
 IN (Default): Passes a value into the procedure from your application.
 OUT: Passes a calculated value out of the procedure back to your application
(like a return statement).
 INOUT: A single parameter that passes a value in, modifies it inside, and
passes the updated value back out.

🌟 Why Use Stored Procedures?

 Reduced Network Traffic: If your application needs to run 5 separate queries
sequentially to process an order, sending 5 individual requests over the
internet creates network lag. With a stored procedure, you send one CALL
request, and all 5 queries execute locally inside the server.
 Centralized Business Logic: If multiple applications (e.g., a web app, a
mobile app, and an internal dashboard) all need to calculate tax or process a
registration, saving that logic inside a stored procedure ensures they all use
the exact same rules.
 Enhanced Security: You can restrict user permissions so that your backend
code cannot directly touch or read sensitive tables (like credit_cards). Instead,
you only give the application permission to execute a specific stored
procedure that carefully controls what data is exposed or modified.

🛑 The Disadvantages to Keep in Mind

 Difficult to Debug: MySQL does not have robust, built-in debugging tools for
stored procedures. If a complex procedure fails mid-way, finding the broken
line can be tedious. [1]
 Server Overhead: Running heavy logical loops or computations inside a
stored procedure consumes the database server's CPU and memory. It is
often cheaper and easier to scale out backend application servers than it is to
scale a central database engine

24. What is a view in MySQL?

A view in MySQL is a virtual table based on the result set of an SQL
statement.
It looks and acts exactly like a real database table—complete with rows and
columns—but it does not store any data physically on disk. Instead, it
serves as a saved query shortcut that dynamically pulls fresh data from the
underlying physical tables (known as base tables) every time you query it.

CREATE VIEW active_customer_orders AS

SELECT
c.customer_id,
c.customer_name,
 o.order_id,
o.order_date,
o.total_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE [Link] = 'Completed';

2. Querying the View

Now, you can treat this view exactly like a normal table. You can even filter it
further with standard WHERE or ORDER BY clauses:
sql
SELECT * FROM active_customer_orders
WHERE total_amount > 500;

🌟 Why Use Views?

 Simplifies Complex Queries: Views hide messy database structures.
Instead of forcing junior developers or analytics tools to understand complex
multi-table joins, subqueries, and mathematical calculations, you can present
them with a clean, pre-flattened view.
 Enhanced Security (Data Masking): You can restrict user permissions so
that an employee cannot read a sensitive base table (like employees).
Instead, you can give them access to a view that selectively displays
harmless columns (like name and department) while completely excluding
sensitive data (like salary and social_security_number).
 Consistent Data Layout: If you restructure your underlying tables (e.g.,
splitting a giant users table into user_auth and user_profiles), you can create
a view that mimics the old table layout. This keeps your backend application
code from breaking during a database redesign.
🔄 Can You Update Data Through a View?
Sometimes, but with strict limitations. MySQL allows you to run INSERT,
UPDATE, or DELETE statements on a view, which will pass through and modify the
underlying base tables. However, a view is updatable only if it has a direct 1-to-1
relationship with a single base table.
A view is NOT updatable if the query used to create it contains any of the following:
 Joins linking multiple tables
 Aggregate functions (like SUM(), AVG(), COUNT())
 The DISTINCT keyword
 GROUP BY or HAVING clauses
 Set operations like UNION

🛑 Dropping a View
DROP VIEW active_customer_orders;
25. How do you create a trigger in MySQL?
To create a trigger in MySQL, you use the CREATE TRIGGER statement.
A trigger is a named database object that is associated with a specific table. It
activates automatically ("fires") when a defined event—such as an INSERT,
UPDATE, or DELETE—occurs on that table.

DELIMITER //

CREATE TRIGGER trigger_name

{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name FOR EACH ROW
BEGIN
-- Your SQL logic / commands go here
END //

DELIMITER ;

 BEFORE vs. AFTER: Determines if the trigger runs before MySQL validates and writes
the change to disk, or after the change is finalized.
 FOR EACH ROW: Ensures that if a query modifies 10 rows, the trigger logic executes 10
individual times (once for each affected record).
 NEW and OLD Keywords: Triggers let you access the data being handled.
NEW.column_name refers to the incoming/updated value. OLD.column_name refers to
the original value before the change.

📋 Practical Real-World Examples

Example 1: An AFTER INSERT Audit Logger
Imagine you want to track whenever a new product is added to your store
inventory. You can set up a trigger to automatically log that event into an audit
history table

DELIMITER //

CREATE TRIGGER log_new_product

AFTER INSERT ON products
FOR EACH ROW
BEGIN
-- Insert a history tracking row using the NEW keyword
INSERT INTO inventory_audit (product_id, action, action_date)
VALUES (NEW.product_id, 'CREATED', NOW());
END //

DELIMITER ;

Example 2: A BEFORE UPDATE Data Guard

Imagine you want to prevent a user's account balance from ever being
updated to a negative value. A BEFORE trigger can intercept the value and
adjust it on the fly before it hits the database file.
 DELIMITER //

CREATE TRIGGER enforce_min_balance

BEFORE UPDATE ON user_wallets
FOR EACH ROW
BEGIN
-- If the incoming new balance is negative, force reset it to 0
IF [Link] < 0.00 THEN
SET [Link] = 0.00;
END IF;
END //

DELIMITER ;

🧹 How to View and Remove Triggers

If you need to audit your active triggers or completely delete one, use these
commands:

-- See all active triggers in your current database

SHOW TRIGGERS;

-- Permanently delete a trigger

DROP TRIGGER IF EXISTS enforce_min_balance;

26. When can triggers be used in MySQL?

Triggers in MySQL are best used when you need automated, un-
bypassable actions to happen directly inside your database, regardless of
which application, user, or script is modifying the data.
Because they run automatically behind the scenes, they act as the ultimate
safety net for your data layer.
Here are the most common scenarios where triggers are used in real-world
development:

1. Advanced Data Validation and Security Guards (BEFORE)

While MySQL has standard constraints (like NOT NULL or CHECK), triggers can handle
complex, conditional validation rules before data is saved.
 Preventing Invalid Updates: You can block a query or automatically adjust values if
they violate business rules (e.g., preventing an employee's salary from ever being
decreased, or forcing a user's password reset token to be saved in lowercase).
 Validating Across Tables: You can check data in another table before allowing an
insertion (e.g., blocking a customer from checking out an item if their account_status in
the users table is flagged as 'Suspended').

2. Creating Complete Audit Trails and Logs (AFTER)

Triggers are the gold standard for tracking history because a user cannot bypass them,
even if they write queries directly in a terminal client.
  Tracking Changes: When a row is modified or deleted, an AFTER UPDATE or AFTER
DELETE trigger can copy the old values ([Link]) and the new values
([Link]) into a historical logging table.
 Security Compliance: Logging exactly who modified a row and when it happened for
financial, healthcare, or sensitive corporate records.
3. Automated Data Synchronization and Accumulation
You can use triggers to automatically update summary tables so your application doesn't
have to calculate heavy aggregates on the fly.
 Stock Inventory Control: When an item is added to an order_items table, an AFTER
INSERT trigger can automatically subtract that quantity from the main products inventory
table.
 Running Totals: When a new post is added to a forum, a trigger can increment a
total_posts counter column directly inside the users profile table.

4. Enforcing Complex Referral Integrity

If your relationship rules are too complex for standard ON DELETE CASCADE foreign keys,
triggers give you full control over how cleanup is handled.

 Soft Deletes: Instead of permanently deleting a record, a trigger can intercept a DELETE
request, cancel it, and instead run an UPDATE statement to flip a column status to
is_deleted = 1.

🚫 When should you NOT use triggers?

While powerful, senior database architects avoid triggers in the following scenarios:

 Heavy Performance Bottlenecks: Because triggers execute FOR EACH ROW, if you
run a bulk update changing 100,000 rows, the trigger will execute 100,000 individual
times. This can cause massive server lag.

 Hidden Business Logic: If your backend application code (Python, [Link], etc.) is
trying to debug a data change, but a hidden database trigger is altering values silently in
the background, it can take hours to figure out why the data isn't matching up.

 Calling External Services: Triggers cannot send emails, hit API endpoints, or talk to
external servers. They must stay strictly contained within MySQL

27. What are the different data types available in MySQL?

MySQL offers a wide array of data types categorized by the kind of information
they store. Choosing the correct data type is critical because it optimizes memory
storage and improves index lookups [🚀, ].
Here are the different data types available in MySQL, broken down by their core
categories:
🔢 1. Numeric Data Types
Used for counting numbers, tracking IDs, storing monetary values, and collecting
sensor data [INT].
 INT (Integer): A standard whole number [INT]. Can handle both positive and
negative values [INT]. (Most common for database IDs) [🎯].
 TINYINT: A very small whole number. Commonly used to store 0 or 1 to act
as a boolean toggle (True/False).
  BIGINT: An extremely large whole number. Used for high-volume data like
global financial transactions or massive auto-increment values.
 FLOAT / DOUBLE: Approximate numbers with decimal points [FLOAT, 📋].
Ideal for scientific measurements, percentages, or GPS coordinates [FLOAT,
🎯].
 DECIMAL(M, D): An exact, fixed-point decimal number [🛑]. Must always be
used for money/prices to prevent tiny floating-point rounding errors [🛑].
🔤 2. String (Text) Data Types
Used for storing text, varying from short single letters to entire paragraphs
 CHAR(M): A fixed-length string up to 255 characters [CHAR]. Highly
optimized for speed if your data is always the exact same length (e.g., State
codes like NY or country codes like US) [CHAR, 📊, 🎯].
 VARCHAR(M): A variable-length string up to 65,535 characters [CHAR, 📊].
Saves storage space because it shrinks or expands depending on the text
length (e.g., usernames, emails) [CHAR, , 🎯].
 TEXT: Used for massive blocks of unstructured paragraphs (e.g., blog posts,
product reviews). It stores up to 65,535 characters externally from the core
row structure.
 BLOB (Binary Large Object): Used to store binary data directly inside the
table, such as raw images, PDFs, or encrypted text streams.
📅 3. Date and Time Data Types
Used to track creation timestamps, log events, or manage calendars.
 DATE: Stores only a date value using the standard format: YYYY-MM-DD.
 TIME: Stores only a time value using the standard format: HH:MM:SS.
 DATETIME: Stores a combined date and time string using the format: YYYY-
MM-DD HH:MM:SS. It remains unchanged regardless of time-zone shifts.
 TIMESTAMP: Stores a combined date and time, but it automatically converts
the entry to UTC for storage and shifts back to your local server timezone
upon retrieval. Perfect for automated creation tracks like created_at fields.
 YEAR: A specialized, ultra-lightweight type used to store a 4-digit calendar
year (e.g., 2026).

📦 4. Specialized Data Types

  ENUM(...): A string object that forces a column to accept values chosen
strictly from a predefined list of allowed string values (e.g., ENUM('Small',
'Medium', 'Large')).
 JSON: A highly efficient datatype that allows you to store fully formatted
JSON documents directly into a column, enabling NoSQL-like flexible queries
on unstructured key-value objects [].
28. What is the Difference between CHAR_LENGTH and LENGTH?
The fundamental difference between CHAR_LENGTH and LENGTH in MySQL is
how they measure text: CHAR_LENGTH counts the number of characters,
while LENGTH counts the number of bytes. [1, 2, 3, 4]
For basic English letters, numbers, and symbols, both functions return the
exact same result. However, for multi-byte text—such as emojis, accented
characters, or Asian characters—the results will be completely different.

📊 Direct Comparison
Feature CHAR_LENGTH(string) LENGTH(string)
What it
The total character count. The total byte count.
measures
Yes. It counts letters No. It strictly reports
Encoding
regardless of how many bytes physical storage size
aware?
they consume. on disk.
English
Returns 3 Returns 3
text ('abc')
Returns 4 (in
Emoji text
Returns 1 standard utf8mb4
('🚀')
encoding)

🎯 When to Use Which?

 Use CHAR_LENGTH() (or its alias CHARACTER_LENGTH()) when
validating user input limitations. For example, if a Twitter-like text column has
a visible limit of 280 characters, you want to count individual letters, not the
invisible bytes under the hood.
 Use LENGTH() when analyzing server storage constraints, checking network
transmission sizes, or dealing with raw binary data columns like BLOB or
BINARY types.

29. What do you understand by % and _ in the like statement?

In a MySQL LIKE statement, % and _ are wildcard characters used to build search
patterns for filtering text. They act as placeholders when you do not know the exact
text you are searching for.

1. The Percent Sign (%)

The % wildcard represents any number of characters, including zero characters,
one character, or multiple characters.
 'A%': Matches any text that starts with A.
o Matches: 'Apple', 'Alex', 'A'
 '%A': Matches any text that ends with A.
 o Matches: 'Banana', 'Amanda', 'A'
 '%A%': Matches any text that contains A anywhere in the middle, beginning,
or end.
o Matches: 'Banana', 'Apple', 'Car'

-- Finds all customers whose email address ends with @[Link]

SELECT * FROM customers
WHERE email LIKE '%@[Link]';

2. The Underscore Sign (_)

The _ wildcard represents exactly one single character. It is strict; it cannot
represent zero characters or multiple characters.
 'C_t': Matches exactly 3 letters, starting with C and ending with t.
o Matches: 'Cat', 'Cot', 'Cut'
o Will NOT match: 'Cart' (too long) or 'Ct' (too short)
 '__m': Matches exactly 3 letters ending in m.
o Matches: 'Jim', 'Sam', 'Yam'
 'A_b_'%: Starts with A, followed by any single character, then a b, followed by
another single character, and then anything else.
o Matches: 'Albert', 'Arbor'

-- Finds product codes that are exactly 5 characters long and start with 'PROD'
-- (e.g., 'PROD1', 'PROD2', 'PRODA')
SELECT * FROM products
WHERE product_code LIKE 'PROD_';

💡 Combining Both Wildcards

-- Matches text where the second letter must be 'a', and it can end with anything
SELECT * FROM employees
WHERE first_name LIKE '_a%';
-- Matches: 'James', 'Mary', 'Gary'

30. Explain the main difference between FLOAT and DOUBLE?

The main difference between FLOAT and DOUBLE in MySQL is their precision
and storage size.
Both are used to store numbers with decimal points, but DOUBLE can store
much larger numbers with twice the accuracy of a FLOAT.

📊 Direct Comparison
Feature FLOAT DOUBLE

Single precision (approx. Double precision (approx.

Precision
7 decimal digits). 15 decimal digits).

Storage
4 bytes 8 bytes
Size

Lower accuracy; rounds Higher accuracy; holds

Accuracy
numbers sooner. precise fractions longer.
 31. Explain the difference between HAVING and WHERE clause in MySQL.
The fundamental difference between the WHERE and HAVING clauses in MySQL
is when they filter your data during execution.
The easiest rule of thumb is: WHERE filters individual rows before data is
grouped, while HAVING filters summaries after data is grouped.

📊 Direct Comparison
Feature WHERE Clause HAVING Clause
Execution Runs first. Filters raw table Runs last. Filters aggregated
Order rows. results.
Almost always requires a GROUP
Used with... Can be used with any query.
BY clause.
Aggregate Cannot use aggregates (like
Can use aggregate functions freely.
Functions SUM, AVG, COUNT).
Filtering out broken items Counting the boxes, then throwing
Analogy
before counting box weights. away boxes heavier than 10kg.

32. What is a default constraint in MySQL? How do you set a default value
for a column?
A default constraint in MySQL is a structural rule applied to a column that
automatically fills in a predefined value if a new row is inserted without
specifying data for that column.
It acts as a fallback system, ensuring that your columns are never accidentally
left blank or NULL when an application uploads incomplete data records
[Omitting Columns (Auto-Increment & Defaults), 2]

How to Set a Default Value

You can assign a default constraint either when creating a brand-new table or
by modifying an existing one. [1, 2, 3]
1. Setting a Default Value During Table Creation
To set a default value, append the DEFAULT keyword followed by your
chosen value directly after the column's data type definition:

CREATE TABLE users (

user_id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
-- Text default values must be wrapped in single quotes
account_status VARCHAR(20) DEFAULT 'Active',
-- Numeric default values do not use quotes
loyalty_points INT DEFAULT 0,
-- Automatically captures the exact time the row was inserted
signup_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
2. Adding a Default Value to an Existing Table
If the table already exists, use the ALTER TABLE statement combined with the
ALTER COLUMN syntax to apply or change the constraint:
ALTER TABLE users
ALTER account_status SET DEFAULT 'Pending';
33. What is a temporary table in SQL?
A temporary table in SQL is a special type of table that allows you to store
and process intermediate result sets temporarily.
Unlike a permanent table, which remains in the database until you explicitly
drop it, a temporary table is automatic, private, and short-lived. It is deleted
automatically by the database engine when your session or connection
closes.

🌟 Key Characteristics of Temporary Tables

 Session Isolation: A temporary table is visible only to the user who created
it. If ten different developers connect to the same database simultaneously,
they can all create a temporary table named #temp_users without conflicting
or seeing each other's data.
 Automatic Cleanup: You don't have to worry about cleaning up after
yourself. The moment your database connection drops or your script finishes
executing, the database permanently erases the table and its data from
memory/disk.
 Performance Optimization: They are typically stored in a specialized, high-
speed storage space (like tempdb in SQL Server or Memory storage engines
in MySQL), making them incredibly fast to read and write

-- 1. Create the temporary table

CREATE TEMPORARY TABLE temp_sales_summary (
category_name VARCHAR(50),
total_sales DECIMAL(10,2)
);
-- 2. Insert data into it (often from a heavy query)
INSERT INTO temp_sales_summary
SELECT category, SUM(price)
FROM order_details
GROUP BY category;
-- 3. Query it like a normal table
SELECT * FROM temp_sales_summary WHERE total_sales > 10000;
34. How does NOW() differ from CURRENT_DATE()?
The fundamental difference between NOW() and CURRENT_DATE() in MySQL is
the granularity of the data they return: NOW() captures both the date and the
exact time, while CURRENT_DATE() only captures the calendar date.

📊 Direct Comparison
Feature NOW() CURRENT_DATE()

Data Returned Date AND Time Date ONLY

Standard Format 'YYYY-MM-DD HH:MM:SS' 'YYYY-MM-DD'

Data Type DATETIME DATE

Example Output '2026-08-07 15:21:00' '2026-08-07'

Alternative Aliases CURRENT_TIMESTAMP(), LOCALTIME() CURDATE()

How They Work in Queries

SELECT NOW(), CURRENT_DATE();

 NOW() returns: 2026-08-07 15:21:00 (Perfect for logging high-precision system

events)
 CURRENT_DATE() returns: 2026-08-07 (Perfect for general calendar
calculations)

35. What is the use of the DISTINCT keyword in MySQL?

The DISTINCT keyword in MySQL is used inside a SELECT statement to eliminate
duplicate rows from your query results [What is DISTINCT in MySQL?]. It forces
MySQL to scan your data and return only the completely unique values [What is
DISTINCT in MySQL?].
Think of it like looking at a list of visitors to an office: if John Doe enters the building
three times, a standard list shows his name three times, but a DISTINCT list will show
his name exactly once.

📋 Main Uses of DISTINCT

1. Finding Unique Values in a Single Column
If you query a column that has repeating data (like a list of cities where your
customers live), running a standard SELECT will list every single row. Adding
DISTINCT gives you a clean, deduplicated directory [What is DISTINCT in MySQL?,
1]:
-- Returns a list of unique countries where you have active clients
SELECT DISTINCT country
FROM customers;
2. Combining DISTINCT with COUNT() to Get Totals
If you don't want to see the actual names of the items, but want to know how many
unique items exist, you can wrap DISTINCT inside the COUNT() function:

-- Tells you exactly how many unique products have been sold, ignoring repeat sales
SELECT COUNT(DISTINCT product_id)
FROM order_items;

3. Filtering Across Multiple Columns

When you pass multiple columns to DISTINCT, MySQL checks the combination of
those columns. A row is only hidden if the exact combination of all selected fields
repeats.

-- Returns unique city and state combinations

SELECT DISTINCT city, state
FROM addresses;

36. What is a subquery in MySQL? Explain with an example.

A subquery (also known as an inner query or nested query) is an SQL
query embedded inside another SQL query. [1, 2]
The inner subquery executes first, passes its results to the outer query (the
main query), and then the outer query runs using those results. Think of a
subquery as a way to break a complex, multi-step question into a single SQL
statement.

SELECT column_name
FROM table_name
WHERE column_name = (SELECT column_name FROM table_name WHERE
condition);
└───────────────────┬───────────────────┘
The Subquery

SELECT employee_name, salary

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

How MySQL processes this statement:

1. It executes the inner subquery first and finds that the average salary is 50000.
2. It replaces the subquery with that number behind the scenes.
3. It executes the final outer query as: SELECT employee_name, salary FROM
employees WHERE salary > 50000;.

⚡ Different Ways to Use Subqueries

1. Returning Multiple Values (IN Operator)
If your inner subquery returns a list of multiple rows instead of a single number, you
must use the IN operator instead of the equals sign (=).
Scenario: Find all customers who have placed an order.

SELECT customer_name, email

FROM customers
WHERE customer_id IN (SELECT DISTINCT customer_id FROM orders);

2. Subquery in the FROM Clause (Derived Table)

You can use a subquery to generate a temporary, virtual table on the fly, and then query from
it. You must give this nested subquery an alias (a nickname) for it to work.

SELECT MAX(dept_averages.avg_sal)
FROM (SELECT department, AVG(salary) AS avg_sal FROM employees GROUP
BY department) AS dept_averages;

3. Correlated Subqueries (Advanced)

A correlated subquery is an inner query that relies on data from the outer query to
run. It executes row-by-row, which can be slower but is highly powerful. [1, 2, 3, 4, 5]
Scenario: Find employees who earn more than the average salary of their specific
department.
SELECT e1.employee_name, [Link], [Link]
FROM employees e1
WHERE [Link] > (SELECT AVG([Link]) FROM employees e2 WHERE
[Link] = [Link]);
Subqueries vs. Joins
Most subqueries can be rewritten using a JOIN [How do you join tables in MySQL?].
 Subqueries are usually easier to read and write because they follow a natural
human thought process step-by-step.
 Joins are generally faster and more highly optimized by the MySQL engine,
especially on large datasets
37. What is a Heap (MEMORY) Table in MySQL?
A Heap table—formally known as the MEMORY storage engine in modern
MySQL—is a special-purpose table whose rows are stored entirely in RAM
(random-access memory) rather than on a hard drive or SSD.
Because reading and writing data in RAM is exponentially faster than disk I/O,
MEMORY tables provide lightning-fast query execution speeds.

Key Characteristics of a MEMORY Table

 Volatile Data Layer: The most critical trade-off is that all data rows are lost
if the MySQL server restarts, crashes, or loses power. However, the
 structure of the table (the columns and data definitions) is preserved on disk;
it simply reopens as an empty table upon restart. [1, 2, 3]
 Hash Indexing by Default: Unlike standard InnoDB tables which use B-Tree
indexes, MEMORY tables default to Hash indexes. This makes single-value
lookups (e.g., WHERE id = 502) incredibly rapid, though it makes range
searches (e.g., WHERE age > 21) less efficient. [1, 2, 3, 4, 5]
 Global Accessibility: Unlike a traditional TEMPORARY table (which is
private to the connection script that built it), a MEMORY table behaves like a
normal table. It is shared across all active client connections until it is
manually dropped. [1, 2]
 Fixed-Width Rows: To ensure rapid memory allocation, rows are stored in a
fixed-length format. If you use VARCHAR columns, MySQL will internally treat
them as fixed-width CHAR data types.

-- Creating a high-speed memory table for user session caching

CREATE TABLE user_sessions (
session_id VARCHAR(64) NOT NULL,
user_id INT NOT NULL,
last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
PRIMARY KEY (session_id)
) ENGINE = MEMORY; -- Tells MySQL to hold this exclusively in RAM

38. What is the difference between DELETE and TRUNCATE in MySQL?

The fundamental difference between DELETE and TRUNCATE in MySQL is how they
remove data and interact with the database system.
The easiest analogy is to think of a notebook: DELETE is like using an eraser to rub
out specific lines one by one, while TRUNCATE is like tearing out the entire page
and replacing it with a fresh, blank sheet.

📊 Direct Comparison
Feature DELETE TRUNCATE
DDL (Data Definition
Operation DML (Data Manipulation Language).
Language). Treats data as a
Type Treats data as individual rows.
structural object.
Yes. Can delete specific rows based No. It always wipes out the
Using WHERE
on a condition. entire table completely.
Slower. Deletes rows one by one, Lightning Fast. Drops the
Speed checking constraints and logging each physical table structure and
row. recreates it instantly.
Auto- Preserves the counter. If you delete Resets the counter. Resets
Increment ID ID 10, the next inserted ID will still be your primary key counter
 11. back to 1.
Fires triggers. Activates any
Does NOT fire triggers.
BEFORE or AFTER DELETE triggers
Triggers Bypasses row-by-row
[How do you create a trigger in
triggers completely.
MySQL?].
Cannot be rolled back in
Can be rolled back. Safe to use
Transactions most storage engines (e.g.,
inside active transactions.
InnoDB).

39. What is the difference between UNION and UNION ALL in MySQL?
The fundamental difference between UNION and UNION ALL in MySQL is how they
handle duplicate rows across the combined result sets.
The easiest rule of thumb is: UNION cleans your data by filtering out duplicate
entries, while UNION ALL blindly glues the datasets together as quickly as
possible.

📊 Direct Comparison
Feature UNION UNION ALL
Keeps duplicates. Returns
Duplicate Removes duplicates. Returns
every single row from both
Rows only unique, distinct rows.
tables.
Slower. MySQL must internally Lightning Fast. It simply
Performance
sort and scan the data to filter out appends the rows without doing
Speed
duplicates. any data analysis.
Higher. Requires extra RAM to
Minimal. Requires no internal
Memory Usage track and deduplicate the
processing overhead.
records.

Visualizing the Difference (With Code Examples)

Imagine you have two simple tables: Employees and Contractors, both containing
a list of cities where they live.
 Employees Cities: New York, London, Bengaluru
 Contractors Cities: London, Tokyo, Bengaluru
1. Using UNION
sql
SELECT city FROM employees
UNION
SELECT city FROM contractors;
Use code with caution.
The Result: It merges the lists, identifies that "London" and "Bengaluru" appear in
both tables, and strips away the duplicates to leave a unique directory
 New York, London, Bengaluru, Tokyo (4 rows)

2. Using UNION ALL

SELECT city FROM employees

UNION ALL
SELECT city FROM contractors;
The Result: It simply copies and pastes the rows back-to-back without inspecting the
text:
 New York, London, Bengaluru, London, Tokyo, Bengaluru (6 rows)

40. What is a TIMESTAMP in MySQL?

A TIMESTAMP in MySQL is a specialized date and time data type used to track the
exact moment an event occurs. It stores values combining both a date and a time
in the standard format: 'YYYY-MM-DD HH:MM:SS'.
Its most distinct feature is its timezone awareness, making it the absolute gold
standard for tracking system events, user actions, and audit logs.

🌟 The Defining Feature: Automatic Timezone Conversion

Unlike other date types, a TIMESTAMP automatically adapts to where your
application server or users are physically located:
1. When Saving Data: MySQL converts the time from your current local session
timezone into Coordinated Universal Time (UTC) before saving it to disk.
2. When Reading Data: MySQL converts the UTC value back into the local
timezone of the active database connection.
Real-World Scenario:
Imagine an app server in New York (EST) inserts a record at 12:00:00.
 MySQL converts it and saves it on disk as 17:00:00 (UTC).
 If a developer in London (BST) queries that exact same row, MySQL
automatically shifts the time and displays it to them as 18:00:00.

📊 TIMESTAMP vs. DATETIME

While they look identical on your screen, they behave very differently under the
hood: [1, 2]
Feature TIMESTAMP DATETIME
Timezone No. Stores the static text
Yes. Converts to UTC and back.
Aware? exactly as typed.
Storage Size 4 bytes 5 bytes (in modern MySQL)
Supported '1970-01-01 00:00:01' UTC to '2038- '1000-01-01 00:00:00' to
Range 01-19 03:14:07' UTC '9999-12-31 23:59:59'

41. What is an Index in MySQL?

An Index in MySQL is a powerful database structure designed to speed up the
retrieval of rows from a table.
Think of a database index exactly like the index at the back of a textbook:
instead of flipping through every single page from the beginning to find a specific
keyword (a "Full Table Scan"), you look up the word in the index to instantly find
the exact page number and jump straight to it.

Why Are Indexes Crucial?

Without an index, if you search for a user by their email address in a table containing
10 million rows, MySQL has to read every single row from top to bottom to make
sure it doesn't miss a match. This takes substantial time and drains server CPU
resources.
With an index on the email column, MySQL creates a highly organized internal
lookup map. It can pinpoint the exact matching row in just a few micro-steps,
completing the query instantly.

The Main Types of Indexes in MySQL

1. Primary Key Index (PRIMARY KEY)

When you declare a column as a primary key, MySQL automatically creates a
special index called a Clustered Index [🚀]. The actual physical data rows of the table
are sorted and stored on disk in the exact order of this primary key [⚠️Heavy
Structural Risks to Consider]. Every table can have only one primary key [Primary
Key (PRIMARY KEY)].

2. Unique Index (UNIQUE)

Enforces a strict constraint that ensures no two rows can share the identical value in
that column [Unique Key (UNIQUE)]. Behind the scenes, it builds an optimized index
tree for ultra-fast validation lookups [Unique Key (UNIQUE), 🚀].

CREATE UNIQUE INDEX idx_user_email ON users(email);

3. Single-Column Index (Regular Index)

A standard index applied to a single frequently searched column to boost query
performance.

CREATE INDEX idx_lastname ON employees(last_name);

4. Composite Index (Multiple-Column Index)

An index built on two or more columns combined. This is incredibly useful if you
have queries that consistently filter data using multiple specific criteria together.

-- Highly optimized for: WHERE status = 'Active' AND country = 'India'

CREATE INDEX idx_status_country ON customers(status, country);

5. Full-Text Index (FULLTEXT)

Specialized index used for complex text searches inside large text columns (like a
blog body or a product description field) [⚡ Performance Warning]. It allows you to
run fuzzy, search-engine-style queries using keywords instead of rigid LIKE
'%keyword%' statements [⚡ Performance Warning].

How to View and Remove Indexes

-- See all active indexes on a specific table

SHOW INDEX FROM customers;

-- Permanently remove an index if it is slowing down inserts

ALTER TABLE customers DROP INDEX idx_lastname;

42. What is a Cursor in MySQL?

 In MySQL, a cursor is a database object used to loop through, inspect, and
manipulate a query's result set one row at a time.
Standard SQL queries are "set-based," meaning they handle all matching rows
simultaneously (e.g., updating 1,000 profiles in one flash query). A cursor,
however, changes this behavior into a "row-by-row" procedural operation. Think
of a cursor like a programming loop (such as a for-each loop) designed
specifically for database rows.

The Properties of MySQL Cursors

MySQL cursors have three strict architectural traits:
1. Asensitive: The database engine may or may not make a temporary copy of
the data. Because it might use the live table data directly, modifying the table
while looping can cause unpredictable bugs. [1]
2. Read-Only: You cannot use a cursor to directly edit or update a row through
the cursor pointer itself. (If you need to update a row inside the loop, you must
execute a standard, separate UPDATE statement). [1, 2, 3]
3. Non-Scrollable: You can only move forward through the data rows step-by-
step, from top to bottom. You cannot skip rows, jump backward, or jump
directly to a specific row index.

The 4-Step Lifecycle of a Cursor

A cursor can only be used inside Stored Procedures or Stored Functions. It
follows a strict 4-step sequence: DECLARE → OPEN → FETCH → CLOSE.

Here is a complete, working example of a stored procedure that uses a cursor to

loop through a customers table and merge their names into a single text string:

DELIMITER //

CREATE PROCEDURE CreateCustomerEmailList(

OUT email_list TEXT
)
BEGIN
-- 1. Declare variables to store data during the loop
DECLARE finished INT DEFAULT 0;
DECLARE current_email VARCHAR(100) DEFAULT "";

-- 2. DECLARE the cursor, mapping it to a specific SELECT query

DECLARE email_cursor CURSOR FOR
SELECT email FROM customers WHERE status = 'Active';

-- 3. Declare a NOT FOUND handler to flip the 'finished' flag when rows run out
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

-- Clear the output variable to start fresh

SET email_list = "";

-- 4. OPEN the cursor (this executes the SELECT query behind the scenes)
OPEN email_cursor;
 -- 5. Create the loop block
get_emails: LOOP

-- FETCH the data from the current row into our local variable
FETCH email_cursor INTO current_email;

-- If the handler detected no more rows, break out of the loop

IF finished = 1 THEN
LEAVE get_emails;
END IF;

-- Business Logic: Append the email to our list separated by a comma

SET email_list = CONCAT(current_email, "; ", email_list);

END LOOP get_emails;

-- 6. CLOSE the cursor to free up server memory resources

CLOSE email_cursor;

END //

DELIMITER ;

43. What are Transactions in MySQL?

A transaction in MySQL is a sequential execution of one or more SQL operations
treated as a single, indivisible unit of work.
The fundamental rule of a transaction is simple: either everything succeeds together,
or everything fails together, and the database resets to its original state.
The classic real-world analogy is a bank transfer. If you send ₹1,000 from your
account to a friend, two steps must happen:
1. Deduct ₹1,000 from your balance (UPDATE).
2. Add ₹1,000 to your friend's balance (UPDATE).
If the server crashes or loses power exactly halfway between step 1 and step 2, your
money would vanish into thin air. A transaction prevents this disaster by ensuring
that if step 2 fails, step 1 is automatically canceled and undone.

The 4 Pillars of Transactions: ACID Properties

For a database engine to support reliable transactions, it must strictly enforce four
core principles known as ACID:
 A - Atomicity ("All or Nothing"): Every single query inside the transaction
block must execute flawlessly. If even one query triggers an error, the entire
operation is discarded, and the database rolls back to the beginning.
 C - Consistency: A transaction can only take the database from one valid,
healthy state to another, strictly enforcing all structural rules, keys, and
constraints.
 I - Isolation: Multiple users can modify the database simultaneously without
interfering with one another. Uncommitted changes made inside your active
transaction are completely invisible to other database connections.
  D - Durability: Once a transaction is successfully completed, its changes are
permanently written to the physical storage disk. Even if the server instantly
crashes or loses power a millisecond later, the data is safe.
(Note: In MySQL, transactions are only supported by the InnoDB storage engine.
The legacy MyISAM engine does not support them).

The 3 Core Transaction Commands

By default, MySQL runs in a mode called autocommit. This means every single
query you type is treated as its own instant, permanent transaction. To bundle
multiple queries together manually, you must bypass this behavior using these
commands:
1. START TRANSACTION (or BEGIN): Tells MySQL to pause autocommit and
treat all following queries as an uncommitted draft.
2. COMMIT: Tells MySQL that everything went perfectly. It permanently saves
all the draft changes to disk and makes them visible to the rest of the world.
3. ROLLBACK: The emergency cancel button. It instantly erases all changes
made since the transaction started, restoring the rows to their original state.

-- 1. Open the secure transaction block

START TRANSACTION;

-- 2. Run the first account update

UPDATE bank_accounts
SET balance = balance - 1000.00
WHERE user_id = 101;

-- 3. Run the second account update

UPDATE bank_accounts
SET balance = balance + 1000.00
WHERE user_id = 102;

-- 4. If both steps succeeded without an error, lock it in permanently

COMMIT;

-- 🛑 Alternatively: If something broke mid-way, you would execute:

-- ROLLBACK;

🛑 Advanced Control: Savepoints

START TRANSACTION;

INSERT INTO inventory (item_name) VALUES ('Item A');

SAVEPOINT checkpoint_1; -- Set a restore point here

INSERT INTO inventory (item_name) VALUES ('Item B');

-- Uh oh, Item B was a mistake! Let's undo just that step

ROLLBACK TO checkpoint_1;

-- Now commit, saving only Item A

COMMIT;

44. What is Normalization?

Normalization is a structured database design process used to organize tables in a
relational database.
Its primary goals are to eliminate data redundancy (storing the same data in
multiple places) and ensure data integrity (ensuring data dependencies make
logical sense).
The easiest analogy is to think of normalization as organizing a messy wardrobe:
instead of throwing shoes, coats, and socks into one massive box, you separate
them into dedicated shelves and hangers, linking them together systematically.

🛑 The Problem: A Non-Normalized Table

Imagine an online school system that puts all its data into a single, giant spreadsheet
table:
Student_Courses Table:
Student_ID Student_Name Course_Name Instructor_Name Instructor_Room
Computer
101 Alice Smith Dr. John Jones Room 404
Science
101 Alice Smith Advanced Math Prof. Sarah Lee Room 201
Computer
102 Bob Miller Dr. John Jones Room 404
Science
This non-normalized design causes major headaches called Data Anomalies:

 Insertion Anomaly: You cannot add a new instructor to your system until a
student actually registers for their course.
 Deletion Anomaly: If Bob Miller drops his "Computer Science" class, you
accidentally delete Dr. John Jones and his room location from your database
entirely.
 Update Anomaly: If Dr. John Jones moves to Room 500, you have to find
and update his room number in hundreds of individual student rows. Missing
just one row corrupts your data.

The Normal Forms (Stages of Normalization)

Normalization happens in progressive stages called Normal Forms. To reach a
higher stage, your database must satisfy all the rules of the previous stages.
1. First Normal Form (1NF): Atomic Values
 The Rule: Every table cell must contain a single, indivisible (atomic) value,
and there must be no repeating groups or columns of data.
 Bad Layout: Storing Courses = 'Math, Science' in a single cell.
 The Fix: Split them into separate rows so each cell holds exactly one item.

2. Second Normal Form (2NF): Remove Partial Dependencies

 The Rule: The table must already be in 1NF, and all non-key columns must
depend entirely on the whole primary key (crucial for composite keys).
 Look at our example: In our composite-key table, Student_Name only
depends on Student_ID, not on the Course_Name.
 The Fix: Break the data into two separate tables: a Students table and a
Registrations table.
3. Third Normal Form (3NF): Remove Transitive Dependencies
 The Rule: The table must be in 2NF, and no column should depend on
another non-key column.
 Look at our example: Instructor_Room depends on Instructor_Name, which
then depends on the Course_Name. This is a domino effect.
 The Fix: Move instructors and rooms into their own dedicated Instructors ta

45. What is a Composite Index in MySQL?

An Composite Index (also known as a Multiple-Column Index) is an index built
on two or more columns of a table combined into a single lookup map.
While a standard index accelerates searches based on just one field, a
composite index is designed to speed up queries that consistently filter data
using multiple criteria simultaneously.

The Left-to-Right Rule (The Most Critical Concept)

The most vital rule to understand about composite indexes is that column order
matters immensely. MySQL builds composite indexes as an ordered tree
structure from left to right.
Imagine you create a composite index on a customers table with this layout:

CREATE INDEX idx_status_country ON customers(status, country);

46. What is the difference between primery key and unique key?
The main difference between a primary key and a unique key is that a primary key
uniquely identifies every single row in a database table and strictly forbids NULL
values, whereas a unique key prevents duplicate entries in a specific column but
allows for NULL values. Furthermore, a table can only have one primary key, but it
can have multiple unique keys.

Quick Comparison
Feature Primary Key Unique Key
Quantity per Multiple allowed (up to 999 in SQL
Exactly one
Table Server)
Null Values Strictly NOT NULL Allows NULL (typically only one)
Default Indexing Clustered Index Non-Clustered Index
Primary Purpose Enforces entity identity Prevents duplicate data entry
Highly discouraged /
Modifiability Values can be updated freely
restricted