MySQL

1] if I delete a row
1a
2b
3c
If I delete b and pk is autoincrement then the output is
1a
2c
Or
1a
2c

 id | name
 -----------
 1 |a
 3 |c
- If you want them to be continuous (1,2...), you’d have to re-sequence manually using an
update or by truncating and reinserting.

1. Reset Auto Increment (only affects new inserts, not old rows)
ALTER TABLE your_table AUTO_INCREMENT = 1;

2. Resequence IDs (renumber all rows)

If you really want continuous numbering after deletions, you can do:

SET @count = 0;
UPDATE your_table SET id = (@count:=@count+1) ORDER BY id;

 This will reset IDs starting from 1 and increment by 1.

 After this, you may also reset auto_increment:

ALTER TABLE your_table AUTO_INCREMENT = 1;

 Resequencing is not recommended in production databases if IDs are used as primary keys or foreign
keys (because changing them breaks references).
 It’s only fine in practice/demo cases where IDs don’t matter.
Q2. If we set a table column as default using alter then it display in table
Or it just display empty?


If we insert the only one value the it display like

Schedule delete
-- Enable the event scheduler
SET GLOBAL event_scheduler = ON;

-- Create procedure (example)

DELIMITER //
CREATE PROCEDURE temp_proc()
BEGIN
SELECT 'This is temporary';
END //
DELIMITER ;

-- Create event to drop it after 15 minutes

CREATE EVENT drop_temp_proc
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 15 MINUTE
DO
DROP PROCEDURE IF EXISTS temp_proc;
 Index is applied only on tables (and their columns), not on the whole database.

 A database is just a container for tables, views, procedures, etc. Indexing is meaningful only when applied
to data stored in tables.

 You can create indexes on:

 Single column
 Multiple columns (composite index)
 Unique indexes (no duplicate values allowed)
 Full-text indexes (for text searching)
 Primary key / Foreign key (automatically create indexes)

1. What is an Index?

 An index in MySQL is like the index in a book.

 Instead of reading every page (row), the DB engine checks the index to quickly find the data.
 It makes searching, filtering, joining, sorting faster.

⚠️Downside:

 Indexes take extra memory/disk space.

 They also slow down INSERT/UPDATE/DELETE because the index has to be updated too.

🔹 2. Why do we use Indexes?

 Faster WHERE lookups

 Faster JOIN between tables
 Speed up ORDER BY, GROUP BY
 Help in constraints (PRIMARY KEY, UNIQUE)
 Optimize functions like MAX() / MIN()

🔹 3. How to Create Indexes?

There are 3 ways:

1. CREATE INDEX
2. CREATE INDEX idx_name ON employees(lastname);

3. While creating table

4. CREATE TABLE employees(
5. id INT PRIMARY KEY,
6. lastname VARCHAR(50),
7. INDEX(lastname)
8. );

9. ALTER TABLE
10. ALTER TABLE employees ADD INDEX (lastname);
🔹 4. Types of Indexes

1. Primary Key
o Unique + Not NULL.
o Example: id INT PRIMARY KEY
2. Unique Index
o Values must be unique (can have one NULL).
o Example: ALTER TABLE users ADD UNIQUE INDEX(email);
3. Normal Index
o Speeds up search, values can repeat.
o Example: CREATE INDEX idx_city ON users(city);
4. Fulltext Index
o Used for searching text (like Google search in a column).
o Example:
o CREATE FULLTEXT INDEX idx_desc ON products(description);

5. Spatial Index
o Used for geographic data (maps, GIS). Rarely used.
6. Descending Index (MySQL 8+)
o Speeds up queries like “latest 10 records”.
o Example:
o CREATE INDEX idx_date_desc ON orders(order_date DESC);

🔹 5. USE INDEX Hint

 Normally MySQL chooses the best index automatically.

 But if you want to force MySQL to use a specific index:

SELECT *
FROM customers USE INDEX(idx_name_fl)
WHERE contactFirstName LIKE 'A%';

 Good for optimization when MySQL picks the wrong one.

1. Create a New User

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

 'newuser' → username
 'localhost' → means the user can connect only from the same machine
 'password' → user’s login password

👉 If you want the user to connect from anywhere:

CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';

🔹 2. Grant Permissions

 Grant all privileges (full access like root):

GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';

(*.* means all databases and all tables)

 Grant limited permissions (example: only on one table):

GRANT SELECT, INSERT ON test.emp TO 'newuser'@'localhost';

 Grant with ability to give permissions to others:

GRANT SELECT, INSERT ON mydb.dept TO 'user3'@'localhost' WITH GRANT OPTION;

🔹 3. Apply the Changes

FLUSH PRIVILEGES;

This reloads privileges so changes take effect.

🔹 4. Common Permissions

 ALL PRIVILEGES → everything

 CREATE → create databases/tables
 DROP → delete databases/tables
 DELETE → delete rows
 INSERT → add rows
 SELECT → read data
 UPDATE → modify rows
 GRANT OPTION → allow user to grant privileges to others

👉 Example: Give only SELECT on a table:

GRANT SELECT ON mydb.dept TO 'user2'@'localhost';

🔹 5. Revoke Permissions
REVOKE INSERT ON test.dept FROM 'newuser'@'localhost';

(Notice we use FROM instead of TO)

🔹 6. Check User’s Current Permissions

SHOW GRANTS FOR 'newuser'@'localhost';

🔹 7. Delete User
DROP USER 'newuser'@'localhost';
🔹 8. Login as That User
mysql -h localhost -u newuser -p

(then enter password when asked)

✅ Summary (Easy to Remember):

 CREATE USER → make new account

 GRANT → give permissions
 REVOKE → remove permissions
 FLUSH PRIVILEGES → apply changes
 SHOW GRANTS → check permissions
 DROP USER → delete user

Yes ✅ a table can have multiple UNIQUE keys, but only one Primary Key.

The number of levels of nested subqueries (query inside query) depends on the database system:

 MySQL → Allows up to 255 levels of nested subqueries.

 Oracle → The limit is generally 255 subquery nesting levels.
 SQL Server → Supports up to 32 levels of nesting.
 PostgreSQL → No hard documented limit, but practically it’s constrained by memory and performance.
You have a table Users with a composite primary key (id, email). You want to create a table Orders that
refers to Users.

1. Write the SQL statement to create Orders with a foreign key referencing Users.
2. Explain why you cannot reference only id as a foreign key in this case.
3. What would happen if you try to insert a row in Orders with a user_id and user_email combination
that does not exist in Users?
 You can include up to 16 columns in a single index (composite index).
 The total index length (sum of all indexed columns) must not exceed:
o 3072 bytes for InnoDB (MySQL 5.7+ and MySQL 8.x).
o In older MySQL versions, the limit was 767 bytes.
  We cant use nester aggregate function
What is the difference between Table scan and seek ?

Answer:

 Table Scan:
o The database reads every row in the table sequentially to find matching records.
o Happens when no useful index exists.
o Slower for large tables because it checks all rows.
 Index Seek:
o The database directly navigates through the index to find the required rows.
o Very fast because it avoids scanning the entire table.
o Used when proper indexes exist on the filtered/search columns.

What is a heap (table on a heap)?

 A heap in SQL Server is a table without a clustered index.

 Data in a heap is stored in an unordered format, in pages as it is inserted.

 Rows can only be located using:

 RID (Row Identifier) lookups or

 Non-clustered indexes (if they exist).

 Advantages: Faster for bulk inserts and staging tables.

 Disadvantages: Slower for searches and updates because there’s no logical order, which often leads to more
page reads and fragmentation.

What happens when Clustered Index is created?

○ First, a B-Tree of a CI will be created in the background.
○ Then it will physically pull the data from the heap memory and physically sort the data based
on the clustering key.
○ Then it will store the data in the leaf nodes.
○ Now the data is stored in your hard disk in a continuous manner.

What is Fragmentation .?

 Definition: Fragmentation in databases refers to the way data pages become disorganized over time as rows
are inserted, updated, or deleted.

 Types:

 Internal Fragmentation: Empty space inside a data page (e.g., row deletes/updates leaving gaps).
 External Fragmentation: Logical order of pages does not match their physical order, causing more I/O.

 Impact: Increases disk I/O, slows down query performance, and wastes storage.

 Fix: Use Index Reorganize (for mild fragmentation) or Index Rebuild (for heavy fragmentation)

Query optimization

To optimize SQL queries, I focus on using proper indexes, keeping statistics updated, avoiding SELECT *,
reducing cursors and triggers, maintaining indexes to prevent fragmentation, and preferring joins over
subqueries. For large tables, I use partitioning and rely on Profiler/DTA for deeper tuning

What is the difference between WHERE and HAVING clause?

 WHERE → filters before aggregation.

 HAVING → filters after aggregation.

What are clustered and non-clustered indexes?

 Clustered → data stored physically in sorted order.

  Non-clustered → separate structure pointing to data.

 What is normalization? Why is it used?

 Breaking large tables into smaller ones to remove redundancy and ensure data integrity.

 What is denormalization? When would you use it?

 Adding redundancy for faster reads (e.g., reporting, analytics).