Mockpreps
🧑💼
🪴
Login now and master your exams with Mockpreps’ mock tests!
Top Interview Questions
.NET is a free, open-source, cross-platform framework developed by Microsoft for building a wide range of applications, including web, desktop, mobile, cloud, gaming, and IoT solutions. First introduced in the early 2000s, .NET has evolved significantly over the years, culminating in the modern .NET 7 and .NET 8 releases that unify the framework under a single platform.
At its core, .NET provides a runtime environment, libraries, and tools that simplify application development and deployment across different platforms, such as Windows, macOS, Linux, Android, and iOS.
The .NET framework was initially released in 2002 as a Windows-only development framework. It aimed to provide developers with a consistent programming model, improved security, and better integration with Windows applications. Early versions of .NET relied heavily on the Common Language Runtime (CLR) and supported languages like C#, Visual Basic .NET (VB.NET), and C++/CLI.
In 2016, Microsoft introduced .NET Core, a cross-platform, modular, and high-performance iteration of the .NET framework. Unlike the original framework, .NET Core allowed applications to run on Windows, Linux, and macOS. With the release of .NET 5 in 2020, Microsoft unified .NET Framework and .NET Core into a single platform simply called .NET, with subsequent versions (.NET 6, 7, and upcoming .NET 8) continuing this unified approach.
The .NET architecture is composed of three main layers: the Common Language Runtime (CLR), the Base Class Library (BCL), and the application layer.
Common Language Runtime (CLR)
The CLR is the execution engine of .NET. It provides key services such as:
Memory Management: Automatic garbage collection to free unused objects.
Exception Handling: Structured error handling for robust applications.
Type Safety: Ensures that code cannot access memory in unsafe ways.
Security: Code access security, validation, and role-based authentication.
Just-In-Time (JIT) Compilation: Converts Intermediate Language (IL) code to native machine code at runtime.
Base Class Library (BCL)
The BCL provides a rich set of reusable classes, interfaces, and value types that simplify programming. It includes functionality for:
File I/O and streams
Collections (like lists, dictionaries, and arrays)
String manipulation
Networking
Data access (e.g., ADO.NET)
XML and JSON processing
Security and cryptography
Multithreading and asynchronous programming
Application Layer
The application layer represents the actual applications built on top of .NET. Developers can use various frameworks and libraries to create different types of applications, including:
ASP.NET Core for web applications and APIs
Windows Forms and WPF for desktop applications
Xamarin/.NET MAUI for mobile applications
Blazor for interactive web applications using C# instead of JavaScript
ML.NET for machine learning applications
.NET supports multiple programming languages, making it a language-agnostic platform. The primary languages include:
C# – The most popular .NET language, designed for simplicity, modern features, and safety. It’s widely used for web, desktop, and mobile development.
VB.NET – Evolved from classic Visual Basic, it is more verbose and user-friendly for beginners.
F# – A functional-first language that also supports object-oriented and imperative programming paradigms.
C++/CLI – Used primarily for interoperability with native C++ code in .NET applications.
Because of the Common Language Runtime, code written in one .NET language can interact seamlessly with code written in another language.
The .NET ecosystem offers a wide variety of frameworks and libraries:
ASP.NET Core:
A modern, cross-platform framework for building web apps and APIs. It supports MVC (Model-View-Controller), Razor Pages, and Blazor for interactive client-side applications.
Entity Framework Core (EF Core):
An Object-Relational Mapping (ORM) tool for .NET that simplifies database operations, allowing developers to interact with databases using C# objects.
.NET MAUI (Multi-platform App UI):
Allows developers to create native mobile and desktop apps from a single codebase for Android, iOS, macOS, and Windows.
Blazor:
Enables full-stack web development with C#, running both client-side in the browser via WebAssembly and server-side.
ML.NET:
Provides machine learning capabilities to .NET developers without leaving the .NET ecosystem.
NuGet:
The package manager for .NET, which allows developers to share and reuse code through third-party or internal packages.
One of .NET’s biggest strengths today is cross-platform development. Originally tied to Windows, .NET now allows developers to build and run applications on Linux, macOS, Android, iOS, and Windows. This is made possible by:
.NET Runtime: Platform-specific runtimes for executing .NET code.
Abstraction libraries: These allow developers to write platform-independent code.
.NET MAUI: Enables UI code sharing across platforms.
.NET offers several advantages for developers and enterprises:
Performance:
With advancements in .NET Core and .NET 7/8, applications are faster due to improvements in JIT compilation and memory management.
Scalability:
Ideal for both small apps and enterprise-level systems with complex architecture requirements.
Security:
Built-in features like role-based security, cryptography libraries, and secure coding practices.
Productivity:
Modern IDEs like Visual Studio and Visual Studio Code offer robust debugging, refactoring, and code analysis tools.
Language Interoperability:
Multiple languages work together seamlessly on the .NET platform.
Community and Ecosystem:
A strong open-source community ensures continuous improvement and rich libraries through NuGet packages.
.NET is versatile and widely adopted in many domains:
Web Applications:
Using ASP.NET Core and Blazor, developers can build dynamic, scalable, and secure websites and APIs.
Desktop Applications:
Windows Forms and WPF are used for creating rich desktop experiences.
Mobile Applications:
Xamarin and .NET MAUI allow building mobile apps with a shared codebase across Android and iOS.
Cloud Services:
Integration with Microsoft Azure enables cloud-native apps, microservices, and serverless computing.
Game Development:
Unity, a popular game engine, uses C# and .NET for scripting and game logic.
IoT and Embedded Systems:
.NET can be used for smart devices and IoT applications.
With the release of .NET 8 and beyond, Microsoft is focusing on:
Performance optimizations for high-speed applications.
Improved cross-platform support with .NET MAUI and Blazor.
Cloud-native development with tight integration with Azure.
Enhanced developer productivity with better tooling and support for AI-assisted coding.
The future of .NET is positioned as a unified, cross-platform framework that enables developers to build any type of application using a single, modern platform.
Answer:
MySQL is an open-source relational database management system (RDBMS) that uses Structured Query Language (SQL) to manage databases. It is widely used for web applications, like WordPress, and supports multi-user access.
Key points:
Developed by Oracle Corporation (originally by MySQL AB).
Stores data in tables (rows and columns).
Supports transactions, indexing, and foreign key constraints.
Answer:
| Feature | SQL | MySQL |
|---|---|---|
| Definition | SQL is a query language used to communicate with databases. | MySQL is a database software that uses SQL to manage data. |
| Function | Used for queries, data manipulation, and retrieval. | Provides storage, retrieval, and management of data. |
| Type | Language | RDBMS software |
| Example | SELECT * FROM users; |
MySQL Server software running on a system |
Answer:
Relational databases: Stores data in tables with relationships (MySQL supports this).
InnoDB: Default storage engine in MySQL, supports transactions and foreign keys.
MyISAM: Older storage engine, faster for read-heavy operations, but no foreign key support.
Memory: Stores data in RAM for faster access.
Answer:
a) Numeric Types: INT, BIGINT, DECIMAL, FLOAT, DOUBLE
b) String Types: CHAR, VARCHAR, TEXT, BLOB
c) Date/Time Types: DATE, DATETIME, TIMESTAMP, TIME, YEAR
Example:
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
salary DECIMAL(10,2),
join_date DATE
);
Answer:
Primary Key: A column (or set of columns) that uniquely identifies each row in a table. Example: id in employees.
Foreign Key: A column in one table that refers to the primary key of another table, enforcing referential integrity.
Example:
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
Answer:
Indexes improve query performance by allowing MySQL to find data faster.
Types of Indexes:
PRIMARY KEY: Unique index, automatically created for primary key.
UNIQUE INDEX: Ensures all values are unique.
FULLTEXT INDEX: For text-based search.
INDEX (Regular): Non-unique, used for fast searching.
Example:
CREATE INDEX idx_name ON employees(emp_name);
Answer:
Joins combine rows from two or more tables based on a related column.
Types:
INNER JOIN: Returns only matching rows.
LEFT JOIN: Returns all rows from left table and matching rows from right table.
RIGHT JOIN: Returns all rows from right table and matching rows from left table.
FULL OUTER JOIN: Not directly supported in MySQL, but can be simulated with UNION.
Example:
SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
CHAR and VARCHAR?| Feature | CHAR | VARCHAR |
|---|---|---|
| Storage | Fixed length | Variable length |
| Padding | Padded with spaces | No padding |
| Usage | Small, fixed-length strings | Longer or variable-length strings |
Answer:
Normalization is a process of organizing data in a database to reduce redundancy and improve data integrity.
Normal Forms:
1NF: Each column has atomic values, no repeating groups.
2NF: 1NF + every non-key column depends on the whole primary key.
3NF: 2NF + no transitive dependency between columns.
Answer:
Create Database: CREATE DATABASE db_name;
Create Table: CREATE TABLE table_name (...);
Insert Data: INSERT INTO table_name VALUES (...);
Select Data: SELECT * FROM table_name;
Update Data: UPDATE table_name SET column=value WHERE condition;
Delete Data: DELETE FROM table_name WHERE condition;
Drop Table: DROP TABLE table_name;
DELETE and TRUNCATE| Feature | DELETE | TRUNCATE |
|---|---|---|
| Deletes | Specific rows | All rows |
| Logging | Logged (slower) | Minimal logging (faster) |
| Auto-increment | Does not reset | Resets auto-increment |
| Rollback | Can rollback | Usually cannot rollback in MySQL |
Answer:
A transaction is a set of SQL operations executed as a single unit. Transactions ensure ACID properties:
Atomicity: All operations succeed or none.
Consistency: Database remains consistent.
Isolation: Transactions don’t interfere with each other.
Durability: Once committed, changes persist.
Commands:
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- or ROLLBACK;
| Feature | MyISAM | InnoDB |
|---|---|---|
| Transaction Support | No | Yes |
| Foreign Key | No | Yes |
| Locking | Table-level | Row-level |
| Performance | Faster for read-heavy | Better for write-heavy and transactions |
Answer:
SELECT * FROM employees ORDER BY salary DESC LIMIT 5;
LIMIT N is used to fetch the first N rows.
Use OFFSET to skip rows: LIMIT 5 OFFSET 10;
WHERE and HAVING?| Feature | WHERE | HAVING |
|---|---|---|
| Usage | Filters rows before grouping | Filters groups after aggregation |
| Example | SELECT * FROM employees WHERE salary>5000; |
SELECT dept_id, AVG(salary) FROM employees GROUP BY dept_id HAVING AVG(salary)>5000; |
Answer:
Stored Procedure: A set of SQL statements that can perform operations, may or may not return values.
Function: SQL code that returns a single value and can be used in queries.
Example:
DELIMITER //
CREATE PROCEDURE GetEmployeeCount()
BEGIN
SELECT COUNT(*) FROM employees;
END //
DELIMITER ;
Answer:
A view is a virtual table based on the result of a SQL query.
It does not store data physically but simplifies complex queries.
Example:
CREATE VIEW employee_view AS
SELECT emp_name, dept_id FROM employees WHERE salary > 5000;
Backup: mysqldump -u username -p db_name > backup.sql
Restore: mysql -u username -p db_name < backup.sql
Answer:
A trigger is a set of actions executed automatically when a specified event occurs on a table (INSERT, UPDATE, DELETE).
Example:
CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
SET NEW.join_date = NOW();
Answer:
SELECT emp_name, COUNT(*)
FROM employees
GROUP BY emp_name
HAVING COUNT(*) > 1;
CHAR and VARCHAR?Answer:
CHAR: Fixed-length, pads with spaces if string is shorter than defined length, faster for small text.
VARCHAR: Variable-length, stores only the characters entered + 1 or 2 bytes for length, saves space for larger text.
Example:
CREATE TABLE test_char_varchar (
col_char CHAR(10),
col_varchar VARCHAR(10)
);
UNION and UNION ALL?| Feature | UNION | UNION ALL |
|---|---|---|
| Removes duplicates | Yes | No |
| Performance | Slower (removes duplicates) | Faster |
| Use Case | Combine datasets without duplicates | Combine all datasets including duplicates |
Answer:
Temporary tables are created with CREATE TEMPORARY TABLE and are only visible during the session.
Automatically dropped when the session ends.
Example:
CREATE TEMPORARY TABLE temp_employees AS
SELECT * FROM employees WHERE salary > 5000;
INNER JOIN: Returns rows that match in both tables.
LEFT JOIN (LEFT OUTER JOIN): Returns all rows from left table, matched rows from right table, NULL if no match.
RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from right table, matched rows from left table.
SELF JOIN: A table joins itself.
CROSS JOIN: Returns Cartesian product of two tables.
Example:
SELECT e.emp_name, m.emp_name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;
DELETE, TRUNCATE, and DROP?| Feature | DELETE | TRUNCATE | DROP |
|---|---|---|---|
| Deletes | Specific/all rows | All rows | Table itself |
| Logging | Fully logged | Minimal logging | Not logged |
| Rollback | Can rollback | Usually cannot | Cannot rollback |
| Triggers | Invokes triggers | No triggers | No triggers |
Answer:
Use indexes on frequently searched columns.
Avoid SELECT *, only fetch required columns.
Use JOINs efficiently.
Avoid subqueries when a JOIN works better.
Use EXPLAIN to analyze query execution plan.
Use LIMIT for pagination.
Example:
EXPLAIN SELECT e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;
Answer:
Constraints enforce rules on table columns:
PRIMARY KEY: Unique identifier.
FOREIGN KEY: Ensures referential integrity.
UNIQUE: Ensures unique values.
NOT NULL: Column must have a value.
CHECK: Restricts column values (MySQL 8.0+).
DEFAULT: Sets default value.
Example:
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50) NOT NULL,
dept_id INT,
salary DECIMAL(10,2) DEFAULT 0
);
| Feature | Stored Procedure | Trigger | Function |
|---|---|---|---|
| Called | Explicitly | Automatically | Explicitly or in query |
| Return | Can return multiple result sets | No return | Must return value |
| Use | Complex operations | Auto actions on events | Reusable calculation |
HAVING and WHERE?WHERE filters rows before aggregation.
HAVING filters after aggregation (used with GROUP BY).
Example:
SELECT dept_id, AVG(salary)
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > 5000;
Answer:
SELECT emp_name, COUNT(*)
FROM employees
GROUP BY emp_name
HAVING COUNT(*) > 1;
Transactions allow multiple SQL operations to run as a single unit (ACID properties).
Commands: START TRANSACTION, COMMIT, ROLLBACK.
Example:
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
| Feature | InnoDB | MyISAM |
|---|---|---|
| Transactions | Supported | Not supported |
| Foreign keys | Supported | Not supported |
| Locking | Row-level | Table-level |
| Performance | Good for write-heavy | Good for read-heavy |
SELECT * FROM employees ORDER BY salary DESC LIMIT 5;
A virtual table based on the result of a query.
Simplifies complex queries, does not store data physically.
Example:
CREATE VIEW high_salary_emps AS
SELECT emp_name, salary FROM employees WHERE salary > 5000;
INNER JOIN and OUTER JOIN?| Feature | INNER JOIN | OUTER JOIN |
|---|---|---|
| Returns | Only matching rows | Matching + unmatched rows |
| Types | Only one type | LEFT, RIGHT, FULL |
| Use Case | Strict match | Include all records even if no match |
Column in one table referring to primary key in another table.
Ensures referential integrity.
Example:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
emp_id INT,
FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
);
AUTO_INCREMENT and SEQUENCE?AUTO_INCREMENT: MySQL automatically increments value in a table column.
SEQUENCE: Not supported directly in MySQL (common in Oracle/PostgreSQL).
CHAR and TEXT?| Feature | CHAR | TEXT |
|---|---|---|
| Length | Fixed | Variable |
| Indexing | Can be indexed | Only first 255 chars can be indexed |
| Storage | Small data | Large text data |
NVARCHAR and VARCHAR?NVARCHAR stores Unicode characters, allowing multiple languages.
VARCHAR stores non-Unicode characters.
Use IS NULL or IS NOT NULL to filter.
Use IFNULL(column, default_value) to replace NULL values.
Example:
SELECT IFNULL(salary, 0) FROM employees;
BETWEEN and IN?BETWEEN checks a range: salary BETWEEN 1000 AND 5000
IN checks multiple discrete values: dept_id IN (1, 2, 3)
Index columns used in WHERE and JOIN.
Use proper data types.
Normalize tables.
Avoid unnecessary subqueries.
Use caching.
Analyze queries with EXPLAIN.
Backup:
mysqldump -u username -p db_name > backup.sql
Restore:
mysql -u username -p db_name < backup.sql
Triggers execute automatically on table events (INSERT, UPDATE, DELETE).
Example:
CREATE TRIGGER before_insert_emp
BEFORE INSERT ON employees
FOR EACH ROW
SET NEW.join_date = NOW();
GROUP BY and ORDER BY?| Feature | GROUP BY | ORDER BY |
|---|---|---|
| Purpose | Groups rows for aggregation | Sorts rows |
| Syntax | GROUP BY column |
ORDER BY column ASC/DESC |
| Use Case | Aggregates like SUM, AVG | Sort result set |
PRIMARY KEY and UNIQUE KEY?| Feature | PRIMARY KEY | UNIQUE KEY |
|---|---|---|
| Null Values | Not allowed | Allowed |
| Number per Table | Only one | Multiple |
| Uniqueness | Always unique | Unique values |
MySQL and MariaDB?MariaDB is a fork of MySQL, fully compatible, faster in some cases, and open-source.
Supports more storage engines and features like sequence objects.
SELECT VERSION();
CREATE INDEX idx_emp_name ON employees(emp_name);
RANK(), DENSE_RANK(), and ROW_NUMBER()?RANK(): Assigns rank with gaps for ties.
DENSE_RANK(): Assigns rank without gaps.
ROW_NUMBER(): Sequential number, no ties considered.
Example:
SELECT emp_name, salary, RANK() OVER (ORDER BY salary DESC) as rank
FROM employees;
Answer:
MySQL is an open-source relational database management system (RDBMS) that uses SQL (Structured Query Language) for managing databases.
Key Features:
Open-source and free: Community edition is free; enterprise edition offers advanced features.
Cross-platform support: Runs on Linux, Windows, macOS.
High performance: Optimized query processing and indexing.
Scalability: Can handle large datasets and high concurrent connections.
Replication & clustering: Supports master-slave replication and clustering for high availability.
ACID compliance: Ensures transaction reliability.
Multiple storage engines: InnoDB, MyISAM, Memory, etc.
Advanced Tip: For a 4-year experienced candidate, emphasize understanding InnoDB vs MyISAM, and why InnoDB is preferred for transactional systems.
Answer:
MySQL supports multiple storage engines, each optimized for different scenarios:
| Storage Engine | Features |
|---|---|
| InnoDB | Transaction support, foreign key constraints, row-level locking, ACID compliant. |
| MyISAM | Fast read operations, no transaction support, table-level locking, smaller footprint. |
| Memory | Stores data in RAM for fast access, volatile, non-persistent. |
| CSV | Stores data in CSV format; useful for exporting/importing. |
| Archive | Efficient for storing large amounts of historical data; compressed format. |
| NDB Cluster | Distributed database for high availability and fault tolerance. |
Advanced Tip: For production systems with heavy transactions, InnoDB is preferred due to row-level locking and foreign key support.
Answer:
ACID ensures reliable database transactions:
Atomicity: Transaction is all-or-nothing. Either all operations succeed, or none.
Consistency: Database moves from one valid state to another; integrity constraints are preserved.
Isolation: Concurrent transactions don’t interfere; controlled by isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable).
Durability: Once committed, data persists even after a crash.
Real-time scenario: In banking apps, transferring money between accounts must be atomic and isolated to prevent inconsistencies.
Answer:
Indexes improve query performance by reducing search time.
Types of Indexes:
Primary Key Index: Unique identifier for rows; automatically indexed.
Unique Index: Ensures column values are unique.
Regular/Non-unique Index: Speeds up queries but allows duplicate values.
Full-text Index: Efficient for text searching in large text fields.
Composite Index: Index on multiple columns; improves queries with multiple conditions.
Spatial Index: For geographic data types (e.g., GIS).
Advanced Tip: Over-indexing can degrade write performance, so indexes must be chosen carefully based on query patterns.
INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.Answer:
| Join Type | Description | Example |
|---|---|---|
| INNER JOIN | Returns only matching rows from both tables | SELECT * FROM A INNER JOIN B ON A.id=B.id; |
| LEFT JOIN | Returns all rows from left table + matching rows from right table; NULL if no match | SELECT * FROM A LEFT JOIN B ON A.id=B.id; |
| RIGHT JOIN | Returns all rows from right table + matching rows from left table; NULL if no match | SELECT * FROM A RIGHT JOIN B ON A.id=B.id; |
| FULL OUTER JOIN | Returns all rows from both tables; NULL for missing matches (MySQL doesn’t natively support it; use UNION) |
SELECT * FROM A LEFT JOIN B ON A.id=B.id UNION SELECT * FROM A RIGHT JOIN B ON A.id=B.id; |
Advanced Tip: For 4-year experience, highlight query optimization using JOINs with indexed columns.
Answer:
| Isolation Level | Description | Phenomena Allowed |
|---|---|---|
| Read Uncommitted | Dirty reads allowed | Dirty reads, non-repeatable reads, phantom reads |
| Read Committed | Only committed data visible | Non-repeatable reads, phantom reads |
| Repeatable Read | Same query returns same result within transaction | Phantom reads possible |
| Serializable | Highest isolation; transactions executed sequentially | Prevents all anomalies but reduces concurrency |
Advanced Tip: MySQL InnoDB default is Repeatable Read, which uses MVCC (Multi-Version Concurrency Control) for consistent reads.
CHAR and VARCHAR?Answer:
| Feature | CHAR | VARCHAR |
|---|---|---|
| Storage | Fixed length | Variable length |
| Padding | Padded with spaces | No padding |
| Performance | Faster for fixed-length data | Slower for variable-length data |
| Max Length | 0–255 bytes | 0–65535 bytes (depends on row size) |
Scenario: Use CHAR for fixed-length fields like ISO country codes; VARCHAR for variable fields like names or emails.
Answer:
Stored Procedure: Precompiled SQL code executed on demand; can have input/output parameters.
Example: CREATE PROCEDURE GetEmployee(IN dept_id INT) BEGIN SELECT * FROM employees WHERE dept_id = dept_id; END;
Function: Returns a single value; used in SQL queries.
Example: CREATE FUNCTION GetFullName(emp_id INT) RETURNS VARCHAR(50) ...
Trigger: Automatically executes when an event occurs (INSERT, UPDATE, DELETE).
Example: Update audit log table when a record is inserted.
Advanced Tip: Avoid heavy logic in triggers for high-performance systems to reduce latency.
Answer: Key strategies:
Indexing: Use indexes on columns used in WHERE, JOIN, ORDER BY.
EXPLAIN: Analyze query execution plan to detect bottlenecks.
Avoid SELECT *: Select only required columns.
Use LIMIT: To reduce fetched rows when only subset needed.
Denormalization: For frequently joined tables, sometimes denormalize.
Partitioning: Split large tables into smaller ones for faster queries.
Caching: Use query cache or application-level cache.
Real-time scenario: For a reporting system with millions of rows, partitioning by date improves query speed.
DELETE, TRUNCATE, and DROP| Command | Description | Performance | Rollback |
|---|---|---|---|
| DELETE | Deletes rows based on condition | Slower, row-by-row | Can rollback |
| TRUNCATE | Deletes all rows, resets auto-increment | Fast, table-level operation | Cannot rollback in MySQL |
| DROP | Deletes entire table structure | Fast | Cannot rollback |
Answer:
View: Virtual table representing result of a query.
Example: CREATE VIEW EmployeeView AS SELECT name, salary FROM employees WHERE dept_id=1;
Advantages:
Simplifies complex queries.
Security: Hide sensitive columns.
Limitations:
Cannot index views (except in materialized views, which MySQL doesn’t support natively).
Cannot use ORDER BY unless LIMIT is present.
Performance depends on underlying query complexity.
Answer:
Replication allows one server (master) to replicate data to other servers (slaves).
Types:
Master-Slave: One master, multiple read-only slaves; good for scaling reads.
Master-Master: Two masters replicate to each other; supports read/write on both.
Group Replication / Cluster: Multiple nodes with automatic failover and consistency.
Advanced Tip: Monitor replication lag (SHOW SLAVE STATUS) to ensure slaves are in sync.
Answer:
Detect: Use SHOW ENGINE INNODB STATUS;
Prevent:
Keep transactions short.
Access tables in consistent order.
Use SELECT ... FOR UPDATE wisely.
Apply proper indexing.
Resolve: Rollback one of the transactions to break deadlock.
Scenario: Two users updating the same rows in reverse order can cause deadlock; reordering updates can prevent it.
UNION and UNION ALL| Feature | UNION | UNION ALL |
|---|---|---|
| Removes duplicates | Yes | No |
| Performance | Slower | Faster |
| Use-case | When distinct results needed | When duplicates are allowed, faster queries |
Answer:
Backup:
Using mysqldump: mysqldump -u user -p dbname > backup.sql
Physical backup: Copy data files (with caution).
Restore:
Using mysql: mysql -u user -p dbname < backup.sql
Advanced: For large databases, use Percona XtraBackup or MySQL Enterprise Backup to avoid downtime.
Use EXPLAIN to check query plans.
Properly index frequently queried columns.
Avoid heavy joins on large tables; consider denormalization or caching.
Use connection pooling in applications.
Enable query cache (if MySQL version supports).
Monitor slow query logs.
Answer:
Partitioning splits a large table into smaller, manageable pieces for better performance. Each partition acts like a sub-table.
Types of Partitioning:
Range Partitioning: Rows are assigned to partitions based on a range of values.
Example: PARTITION BY RANGE (YEAR(order_date)).
List Partitioning: Rows are assigned based on a predefined list of values.
Example: PARTITION BY LIST (region_id).
Hash Partitioning: Rows are assigned based on a hash function.
Useful for evenly distributing rows.
Key Partitioning: Similar to hash but uses MySQL’s internal hashing.
Composite Partitioning: Combines multiple partitioning strategies.
Scenario: Large e-commerce order tables can be partitioned by year to improve query speed for recent data.
InnoDB and MyISAM| Feature | InnoDB | MyISAM |
|---|---|---|
| Transactions | Supports ACID | Not supported |
| Locking | Row-level | Table-level |
| Foreign Keys | Supported | Not supported |
| Crash Recovery | Automatic | Manual |
| Storage | Larger | Smaller |
| Use Case | OLTP (transactional) | OLAP (read-heavy reporting) |
Advanced Tip: For high-concurrency applications like banking, InnoDB is preferred because it supports row-level locking.
Answer:
A foreign key links two tables together, enforcing referential integrity.
Example:
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);
Ensures that customer_id in Orders exists in Customers.
Advanced Tip: Avoid cascading deletes in huge tables as it can slow performance; handle in application logic if needed.
Answer:
Deadlock: Occurs when two or more transactions are waiting for each other to release locks.
Detection: SHOW ENGINE INNODB STATUS;
Prevention:
Keep transactions short.
Access tables in the same order.
Use consistent indexing.
Apply SELECT ... FOR UPDATE only when necessary.
Real-time scenario: Two users updating the same rows in reverse order can cause deadlock. Reordering updates or using retry logic can prevent it.
CHAR, VARCHAR, TEXT, and BLOB| Data Type | Description | Max Size | Use-case |
|---|---|---|---|
| CHAR | Fixed-length string | 0-255 bytes | Country code, fixed IDs |
| VARCHAR | Variable-length string | 0-65535 bytes (row size dependent) | Names, emails |
| TEXT | Large text storage | 65,535 bytes | Articles, descriptions |
| BLOB | Binary data | 65,535 bytes | Images, files |
Advanced Tip: VARCHAR + proper indexing is more efficient than TEXT for search queries.
EXPLAIN and how it is usedAnswer:
EXPLAIN shows the execution plan for a query, helping in optimization.
Key Columns:
type → join type (ALL, index, ref, eq_ref).
key → index used.
rows → estimated number of rows scanned.
Extra → additional information (Using where, Using filesort, Using temporary).
Scenario:
EXPLAIN SELECT * FROM Orders o JOIN Customers c ON o.customer_id = c.customer_id WHERE c.region='APAC';
Helps identify if proper indexes are being used.
DELETE, TRUNCATE, and DROP (detailed)| Command | Description | Rollback | Locks | Speed |
|---|---|---|---|---|
| DELETE | Deletes rows matching a condition | Yes | Row-level | Slower |
| TRUNCATE | Deletes all rows, resets auto-increment | No | Table-level | Fast |
| DROP | Deletes table entirely | No | Table-level | Fastest |
Advanced Tip: Use TRUNCATE instead of DELETE for large tables when you don’t need rollback.
Types of Replication:
Asynchronous: Master writes changes; slaves replicate with delay.
Semi-synchronous: Master waits for at least one slave to confirm write.
Group Replication: Multi-master, fault-tolerant replication.
Commands to check replication:
SHOW SLAVE STATUS\G; → Shows replication lag and errors.
SHOW MASTER STATUS; → Current binary log position.
Advanced Tip: Monitor replication lag for read-heavy systems to avoid stale reads.
Stored Procedure: Precompiled code executed manually.
Function: Returns a single value, used in queries.
Trigger: Automatic execution based on table events (INSERT, UPDATE, DELETE).
Scenario:
Trigger updates audit logs automatically when a record is inserted.
Function calculates employee age from dob column for queries.
Stored procedure fetches top N employees for a department.
Advanced Tip: Avoid heavy logic in triggers; can slow down high-concurrency applications.
UNION and UNION ALL| Feature | UNION | UNION ALL |
|---|---|---|
| Removes duplicates | Yes | No |
| Performance | Slower | Faster |
| Use-case | Distinct results | Include duplicates |
Scenario: UNION ALL is better for logging tables to fetch all events quickly.
Answer:
Full-text index: Used for searching text in large columns.
Syntax:
CREATE FULLTEXT INDEX idx_article ON Articles(content);
SELECT * FROM Articles WHERE MATCH(content) AGAINST('database' IN NATURAL LANGUAGE MODE);
Advanced Tip: Use boolean mode for more complex queries (+, -, *).
Logical Backup: mysqldump → portable, slower for large DB.
Physical Backup: Copy .ibd and .frm files → faster, needs server downtime.
Incremental Backup: Only changed data since last backup → saves storage.
Restore:
mysql -u user -p dbname < backup.sql
Advanced Tip: For production, use Percona XtraBackup for hot backups without downtime.
Example:
START TRANSACTION;
UPDATE Accounts SET balance = balance - 100 WHERE id = 1;
UPDATE Accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
Ensures atomicity: money is either transferred fully or not at all.
Advanced Tip: For high-concurrency systems, use proper isolation (REPEATABLE READ) to avoid dirty reads.
Answer:
Enable slow query log:
SET GLOBAL slow_query_log = 'ON';
Use EXPLAIN to analyze execution plan.
Add indexes to columns used in WHERE and JOIN.
Avoid SELECT *; fetch only needed columns.
Consider query refactoring or denormalization.
Advanced Tip: Partition large tables and use caching for reporting-heavy queries.
Answer:
A composite index is an index on multiple columns of a table. It helps optimize queries that filter on more than one column.
Example:
CREATE INDEX idx_customer ON Orders(customer_id, order_date);
SELECT * FROM Orders WHERE customer_id=101 AND order_date='2025-12-25';
Optimized because the index covers both columns.
Advanced Tip: The order of columns in a composite index matters. Queries filtering on the leading column(s) benefit the most.
WHERE and HAVING| Feature | WHERE | HAVING |
|---|---|---|
| Filtering | Applied before grouping | Applied after grouping (GROUP BY) |
| Used with | Rows | Aggregated results |
| Example | SELECT * FROM Orders WHERE amount>100; |
SELECT customer_id, SUM(amount) FROM Orders GROUP BY customer_id HAVING SUM(amount)>1000; |
Advanced Tip: Avoid using HAVING when WHERE can filter rows earlier; it improves performance.
EXPLAIN join typesALL: Full table scan → slow, avoid for large tables.
index: Full index scan → better than ALL.
ref: Index lookup using equality on a non-unique column.
eq_ref: One row per join → best performance; used for primary/unique keys.
const/system: Constant lookup → very fast.
Scenario: Using EXPLAIN helps identify if your JOINs are using indexes or doing full table scans.
Answer:
A view is a virtual table based on a SELECT query.
Example:
CREATE VIEW ActiveCustomers AS
SELECT id, name FROM Customers WHERE status='Active';
Advantages:
Simplifies complex queries
Security: hide columns
Reusable queries
Limitations:
Cannot index standard views (materialized views not natively supported).
Performance depends on underlying query complexity.
Scenario: Reporting systems often use views to provide pre-filtered data to the frontend.
EXPLAIN ANALYZEAnswer:
EXPLAIN ANALYZE executes the query and provides actual runtime statistics, unlike EXPLAIN which only shows estimated rows.
Helps identify queries that appear optimized but perform poorly in reality.
Example:
EXPLAIN ANALYZE SELECT * FROM Orders WHERE customer_id=101;
Advanced Tip: Use for production query tuning, especially on large tables.
INNER JOIN and CROSS JOIN| Feature | INNER JOIN | CROSS JOIN |
|---|---|---|
| Returns | Matching rows | Cartesian product of all rows |
| Use-case | Common relational joins | Rare; mainly testing or generating combinations |
| Example | SELECT * FROM A INNER JOIN B ON A.id=B.id; |
SELECT * FROM A CROSS JOIN B; |
Advanced Tip: Avoid CROSS JOIN in large tables—it can produce millions of rows.
Foreign Key: Ensures referential integrity between two tables.
Actions:
ON DELETE CASCADE → Deletes child rows when parent is deleted
ON UPDATE CASCADE → Updates child rows when parent changes
Scenario:
A Orders table has customer_id referencing Customers. If a customer is deleted, either restrict deletion or cascade deletion depending on business logic.
Advanced Tip: Cascades in large tables can slow operations; handle via application logic if necessary.
Replication: Copying data from master to slave to scale reads and provide high availability.
Common issues:
Replication lag → slaves are behind master
Duplicate entries → in asynchronous multi-master setups
Conflicts → simultaneous writes in master-master replication
Commands:
Check slave status: SHOW SLAVE STATUS\G;
Check master position: SHOW MASTER STATUS;
Advanced Tip: Monitor lag in real-time systems like e-commerce to prevent stale reads.
Row-level locks: Locks specific rows → high concurrency (InnoDB)
Table-level locks: Locks entire table → less concurrency (MyISAM)
Advisory locks: Application-defined locks using GET_LOCK()
Deadlocks: Two transactions waiting on each other; resolve using ROLLBACK and retry logic
Scenario: In banking apps, row-level locks prevent multiple users from overdrawing the same account.
Enable slow query log:
SET GLOBAL slow_query_log = 'ON';
Use EXPLAIN to check query plan
Add indexes to frequently queried columns
Avoid SELECT *; select only needed columns
Refactor queries to reduce nested joins
Use partitioning and caching for large tables
Advanced Tip: Consider denormalization for reporting-heavy systems if joins are too expensive.
DELETE and TRUNCATE in depth| Feature | DELETE | TRUNCATE |
|---|---|---|
| Row deletion | Conditional | All rows |
| Transaction log | Logs each row → slower | Minimal logging → faster |
| Auto-increment reset | No | Yes |
| Rollback | Possible | Not possible in MySQL |
Scenario: Use TRUNCATE for clearing staging tables quickly.
Partitioning: Splits tables into smaller chunks
Indexing: Essential for filtering billions of rows
Sharding: Distribute tables across multiple servers
Caching: Use Redis/Memcached to reduce repeated queries
Archiving: Move historical data to cheaper storage
Advanced Tip: For 4-year experienced candidates, mention practical experience with partitioning and query optimization for large datasets.
Temporary Table: Exists only for the session; automatically dropped at the end.
Example:
CREATE TEMPORARY TABLE TempOrders AS
SELECT * FROM Orders WHERE order_date='2025-12-25';
Useful for intermediate query results.
Advanced Tip: Use in complex reporting queries to avoid recalculating joins multiple times.
Trigger: Automatically executes when an event occurs (INSERT, UPDATE, DELETE)
Example:
CREATE TRIGGER after_order_insert
AFTER INSERT ON Orders
FOR EACH ROW
INSERT INTO AuditLog(order_id, action) VALUES (NEW.order_id, 'INSERT');
Helps maintain audit trails and data integrity.
Advanced Tip: Avoid heavy computations inside triggers to maintain high write performance.
AUTO_INCREMENT behaviorUsed for generating unique sequential IDs
Behavior:
In InnoDB, values are incremented even if a transaction rolls back
Can be reset using:
ALTER TABLE Orders AUTO_INCREMENT = 1000;
Scenario: Useful for order numbers, invoice IDs, and customer IDs.
Answer:
MVCC (Multi-Version Concurrency Control) allows MySQL (InnoDB) to handle concurrent reads and writes without locking rows for reads.
How it works:
When a row is updated, InnoDB keeps multiple versions of the row.
Each transaction sees a snapshot of data based on its transaction ID.
Older versions are stored in undo logs.
Benefits:
Readers don’t block writers
Writers don’t block readers
High performance in high-concurrency systems
Real-time example:
In an e-commerce app, users can view products while other users update inventory without blocking reads.
Answer:
A covering index is an index that contains all columns needed by a query, so MySQL doesn’t need to access the table data.
Example:
CREATE INDEX idx_cover ON Orders(customer_id, order_date, amount);
SELECT order_date, amount
FROM Orders
WHERE customer_id = 101;
Benefits:
Faster queries
Reduced disk I/O
Advanced Tip:
Check Extra: Using index in EXPLAIN output.
Answer:
Query cache stores query results in memory and returns them for identical queries.
Why deprecated (MySQL 8+):
Global lock caused contention
Poor performance in write-heavy systems
Modern alternative:
Application-level caching (Redis, Memcached)
MySQL buffer pool optimization
Interview Tip:
Mention that query cache should not be used in modern systems.
Answer:
The InnoDB buffer pool caches:
Data pages
Index pages
Benefits:
Reduces disk I/O
Improves query performance
Best Practice:
Allocate 60–70% of server RAM to buffer pool for dedicated DB servers.
Command:
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
| Feature | Optimistic Locking | Pessimistic Locking |
|---|---|---|
| Lock timing | At commit time | Immediately |
| Performance | High | Lower |
| Use-case | Low conflict systems | High conflict systems |
| MySQL example | Version column | SELECT ... FOR UPDATE |
Real-time scenario:
Inventory systems often use pessimistic locking to avoid overselling.
SELECT … FOR UPDATEAnswer:
Locks selected rows for update until transaction ends.
Example:
START TRANSACTION;
SELECT * FROM Accounts WHERE id=1 FOR UPDATE;
UPDATE Accounts SET balance=balance-100 WHERE id=1;
COMMIT;
Use-case:
Financial transactions
Inventory updates
Answer:
Sharding splits data across multiple databases to handle very large datasets.
Types:
Range-based sharding
Hash-based sharding
Directory-based sharding
Real-time example:
User data split across servers based on user_id % N.
Advanced Tip:
MySQL does not support sharding natively—handled at application level.
Answer:
Writes go to master
Reads go to replica/slave
Benefits:
Improved performance
Horizontal scaling
Challenge:
Replication lag may cause stale reads
Solution:
Route critical reads to master.
Answer:
Binary log records all data changes.
Uses:
Replication
Point-in-time recovery
Auditing
Types:
STATEMENT
ROW (recommended)
MIXED
Command:
SHOW BINARY LOGS;
Answer:
Restores database to a specific time using:
Full backup
Binary logs
Steps:
Restore full backup
Replay binlogs up to required timestamp
Use-case:
Accidental delete in production.
| Feature | Clustered Index | Secondary Index |
|---|---|---|
| Data storage | Data stored with index | Points to clustered index |
| Count | Only one | Multiple |
| Example | Primary key | Unique, normal index |
Advanced Tip:
Choose short primary keys to improve performance.
Answer:
Index that exists but is ignored by optimizer.
Use-case:
Test impact of removing an index without dropping it.
Example:
ALTER TABLE Orders ALTER INDEX idx_old INVISIBLE;
Answer:
Histograms help optimizer understand data distribution.
Benefit:
Better query plans for skewed data.
Example:
ANALYZE TABLE Orders UPDATE HISTOGRAM ON status;
Answer:
Avoid blocking operations.
Techniques:
Online DDL
pt-online-schema-change
gh-ost
Real-time scenario:
Adding column to a 100M-row table without downtime.
Answer:
Reuses DB connections to avoid overhead.
Benefits:
Reduced latency
Better resource usage
Tools:
Application-level pools
ProxySQL
| Issue | Solution |
|---|---|
| High CPU | Optimize queries, add indexes |
| Slow queries | Enable slow query log |
| Deadlocks | Short transactions, consistent access |
| Replication lag | Optimize writes, upgrade hardware |
| Disk full | Purge logs, archive data |
✔ Proper indexing strategy
✔ Use EXPLAIN / EXPLAIN ANALYZE
✔ Tune buffer pool
✔ Monitor slow queries
✔ Avoid long transactions
✔ Use caching
✔ Handle replication lag
✔ Regular backups & PITR
