← Back to Interview List

MySQL Interview Questions and Answers

MySQL Interview Questions and Answers | MySQL Interview Preparation | Freshers & Experienced

Interviewer What is Normalization and Denormalization in MySQL?
You Normalization is a technique to eliminate redundant data and avoid anomalies like insert, update, and delete anomalies. It divides large tables into logical smaller tables. Denormalization is the reverse process, aiming to improve read/search performance by merging tables and reducing joins, at the cost of possible redundancy.
Interviewer Can you explain the First, Second, and Third Normal Forms?
You First Normal Form requires all columns to have atomic values, a primary key, and no repeating groups. Second Normal Form requires First Normal Form plus removing partial dependencies so each non-key column depends on the full primary key only. Third Normal Form requires Second Normal Form plus removing transitive dependencies, so non-key columns do not depend on other non-key columns.
Interviewer What is the difference between Primary Key and Foreign Key?
You Primary Key uniquely identifies each row and cannot be NULL or duplicate. Foreign Key is a column that is a Primary Key in another table and is used to set a relationship between tables. Foreign Keys can be NULL and are not required in every table.
Interviewer Difference between Primary Key, Candidate Key, and Unique Key?
You Primary Key: unique, not null, only one allowed. Candidate Key: any column or set of columns that can qualify as a unique key; can have multiple per table, but only one is chosen as primary. Unique Key: enforces uniqueness for a column, allows NULLs, can have both string and integer types.
Interviewer What is a Trigger, and how do you create it in MySQL?
You A Trigger is a stored procedure that runs automatically when an event such as INSERT, UPDATE, or DELETE occurs on a table. For example, you can create a BEFORE INSERT trigger on a table to set a creation time field, or AFTER UPDATE to log changes.
Interviewer Explain Transactions in MySQL and how to implement them?
You A Transaction is a logical unit that groups multiple statements like SELECT, INSERT, UPDATE, DELETE. They can be committed if successful, or rolled back if any error occurs. Syntax: START TRANSACTION; ... statements ... COMMIT; or use ROLLBACK; if error.
Interviewer What is an Index? How is it declared in MySQL?
You An index improves the speed of query operations by enabling fast lookup of rows. By default, primary keys are indexed. To create an index: CREATE INDEX idx_name ON table_name (column_name);
Interviewer What is a View, and how do you create and drop a view in MySQL?
You A View is a virtual table representing the result of a query. Create one with CREATE VIEW view_name AS SELECT ...; Drop it with DROP VIEW view_name;
Interviewer Explain Inner Join, Left Join, Right Join, and Cross Join.
You Inner Join returns matching rows from both tables. Left Join returns all records from the left table and matching records from the right table. Right Join returns all records from the right table and matching records from the left. Cross Join returns the cartesian product—all combinations of rows from both tables.
Interviewer Difference between UNION and UNION ALL?
You UNION combines results and removes duplicates. UNION ALL combines results and retains duplicates.
Interviewer What are Aliases in MySQL?
You Aliases assign a temporary name to a table or column to make queries easier to read. Use "AS" keyword, e.g., SELECT col AS alias.
Interviewer What is a MySQL Subquery?
You A Subquery is a query within another query, typically used in WHERE or FROM clause to provide results for the main query.
Interviewer Explain BLOB vs TEXT data types in MySQL.
You BLOB is used to store binary data like images or files, cannot have character set. TEXT stores string data. Both can handle large data, but they are handled differently by MySQL.
Interviewer What is the difference between CHAR and VARCHAR?
You CHAR is fixed length; storage is always the defined length, even for short data. VARCHAR is variable length, saving space for shorter strings.
Interviewer InnoDB vs MyISAM storage engines: differences?
You InnoDB supports transactions, row-level locking, foreign keys, and crash recovery. MyISAM is faster for read-heavy workloads but does not support transactions or foreign keys and uses table-level locking.
Interviewer What are Truncate vs Delete operations?
You TRUNCATE deletes all rows and resets indexes, cannot be rolled back in most setups. DELETE removes rows meeting a condition and can be rolled back if in a transaction.
Interviewer What is a Stored Procedure and a Stored Function?
You A Stored Procedure is a set of SQL statements with optional input/output parameters, invoked with CALL. A Stored Function returns a single value and can be used in expressions.
Interviewer Self-referencing foreign key: what is it?
You A self-referencing foreign key is when a foreign key in a table references its own primary key. Common in organizational charts or tree structures.
Interviewer How does the LIKE operator differ from REGEXP in MySQL?
You LIKE is used for simple pattern matching with % and _. REGEXP allows complex pattern matching using regular expressions.
Interviewer How does query cache work in MySQL?
You MySQL query cache stores the result of SELECT statements. If the exact query is repeated and no underlying data has changed, MySQL can return results from the cache, improving performance.
Interviewer Explain the SAVEPOINT statement in MySQL transactions.
You SAVEPOINT marks a point within a transaction to which you can later roll back. Useful for partial rollbacks within transactions.
Interviewer How do import and export work in MySQL?
You Import is done via LOAD DATA INFILE or by using phpMyAdmin. Export uses SELECT INTO OUTFILE or tools like mysqldump. Both help backup, restore, or move data between servers.
Interviewer Difference between IFNULL and ISNULL functions?
You IFNULL returns a replacement value if the expression is NULL. ISNULL checks if a value is NULL and returns true or false.
Interviewer What are Aggregate Functions in MySQL?
You Aggregate functions include COUNT(), SUM(), AVG(), MAX(), MIN(). They process sets of rows and return a single value.
Interviewer How can ENUM be used in MySQL?
You ENUM restricts a column to a set of predefined values, providing data integrity and saving space.
Interviewer Explain the GRANT command in MySQL.
You GRANT assigns user privileges on databases, tables, or columns. Example: GRANT SELECT, INSERT ON db.table TO 'user'@'host';
Interviewer What are common MySQL commands?
You Some basic commands include SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, DROP TABLE, ALTER TABLE.
Interviewer What is the difference between OLTP and OLAP database systems?
You OLTP (Online Transaction Processing) systems are optimized for transaction-oriented operations, with highly normalized tables and frequent updates. OLAP (Online Analytical Processing) is designed for analysis and reporting, often using denormalized tables.
Interviewer What is sharding in the context of MySQL?
You Sharding divides large tables into smaller, more manageable pieces called shards, which are spread across multiple servers to improve scalability and performance.
Interviewer What types of JOINs are supported by MySQL and when would you use each?
You MySQL supports INNER JOIN, LEFT JOIN, RIGHT JOIN, and CROSS JOIN. INNER JOIN gives common rows, LEFT and RIGHT JOIN return all rows from one table plus matches from another, and CROSS JOIN combines every row from both tables.
Interviewer Describe how UNION and UNION ALL are different when combining SELECT results.
You UNION merges multiple SELECT results and removes duplicates. UNION ALL merges them but keeps duplicates. This impacts speed and data inclusion.
Interviewer How do you use subqueries, and can you give a practical scenario?
You A subquery is a SELECT inside another query, like filtering employees who earn more than company average: SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
Interviewer Explain the different data types available in MySQL for handling large amounts of text or files.
You For large text, use TEXT; for binary data like images, use BLOB. VARCHAR and CHAR are for small/medium text where length is known or fixed.
Interviewer How would you enforce data integrity using constraints in MySQL?
You Data integrity is enforced using PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK constraints in CREATE TABLE statements, ensuring valid and consistent data.
Interviewer What is a composite Primary Key?
You A composite primary key uses more than one column to uniquely identify a row, defined at table creation with PRIMARY KEY (col1, col2).
Interviewer How do you perform backup and restore operations in MySQL?
You Backup is done with mysqldump or SELECT INTO OUTFILE; restore uses mysql command-line tool or LOAD DATA INFILE. Regular backups are critical for disaster recovery.
Interviewer Describe how to optimize a database query in MySQL.
You You analyze the query with EXPLAIN, add indexes, avoid SELECT *, and break complex queries into smaller ones. Caching frequently used results also helps.
Interviewer What are slow query logs and how do you enable them?
You The slow query log records queries taking longer than a threshold. You enable it with SET GLOBAL slow_query_log=1; and configure log file and time as needed.
Interviewer How does MySQL handle concurrency control and locking?
You MySQL uses locks to manage concurrent access: row-level for InnoDB and table-level for MyISAM. Proper transaction management avoids deadlocks and improves consistency.
Interviewer What is replication, and how can it benefit a MySQL deployment?
You Replication copies data from one MySQL server (master) to others (slaves) for redundancy, load balancing, backup, or disaster recovery. It helps scale reads and improve uptime.
Interviewer How do you secure a MySQL server?
You I set strong passwords, disable remote root login, restrict user privileges, enable firewalls, and use SSL for connections. I also keep the MySQL server patched and audit user activity.
Interviewer How do you find duplicate values in a column?
You You use GROUP BY and HAVING COUNT(*) > 1. Example: SELECT col, COUNT(*) FROM table GROUP BY col HAVING COUNT(*) > 1;
Interviewer What’s the use of AUTO_INCREMENT in MySQL?
You AUTO_INCREMENT automatically generates a sequential unique number for new rows, often used for primary key columns.
Interviewer How do binary and non-binary string types differ?
You Binary types like BINARY and VARBINARY store raw bytes and are case sensitive; non-binary types like CHAR and VARCHAR store text and use collations.
Interviewer What is the default storage engine in recent MySQL versions?
You InnoDB is the default storage engine, supporting transactions, foreign keys, and better crash recovery.
Interviewer Can you explain foreign key ON DELETE CASCADE and ON UPDATE CASCADE actions?
You ON DELETE CASCADE automatically deletes child rows when a parent row is deleted; ON UPDATE CASCADE updates child rows when parent key changes, maintaining referential integrity.