Technical interview questions and answers are essential when preparing for a MySQL Interview because companies want to evaluate your understanding of database queries, joins, functions, table design, indexing, and optimization techniques. MySQL is widely used in web applications, making it one of the most important skills for developers, database engineers, and backend programmers. Companies like TCS, Wipro, Infosys, Cognizant, and Accenture frequently include MySQL questions in technical rounds and placement tests. This guide provides the most asked MySQL interview questions with explanations to help freshers and job seekers strengthen their SQL fundamentals. Preparing these questions will help you perform confidently in SQL coding tests, backend interviews, and campus placements.
Database developers can broaden their expertise through DBMS theory and MS SQL Server implementation
1. What's MySQL ?
Answer: MySQL (pronounced "my ess cue el") is an open source relational database management system (RDBMS) that uses Structured Query Language (SQL), the most popular language for adding, accessing, and processing data in a database. Because it is open source, anyone can download MySQL and tailor it to their needs in accordance with the general public license. MySQL is noted mainly for its speed, reliability, and flexibility. ...
Show Answer
Hide Answer
2. What is DDL, DML and DCL ?
Answer: If you look at the large variety of SQL commands, they can be divided into three large subgroups. Data Definition Language deals with database schemas and descriptions of how the data should reside in the database, therefore language statements like CREATE TABLE or ALTER TABLE belong to DDL. DML deals with data manipulation, and therefore includes most common SQL statements such SELECT, INSERT, etc. Data Control Language includes commands such as GRANT, and mostly concerns with rights, permissi
Show Answer
Hide Answer
3. How do you get the number of rows affected by query?
Answer: SELECT COUNT (user_id) FROM users would only return the number of user_ids.
Show Answer
Hide Answer
4. If the value in the column is repeatable, how do you find out the unique values?
Answer: Use DISTINCT in the query, such as SELECT DISTINCT user_firstname FROM users; You can also ask for a number of distinct values by saying SELECT COUNT (DISTINCT user_firstname) FROM users;
Show Answer
Hide Answer
5. How do you return the a hundred books starting from 25th?
Answer: SELECT book_title FROM books LIMIT 25, 100. The first number in LIMIT is the offset, the second is the number.
Show Answer
Hide Answer
6. You wrote a search engine that should retrieve 10 results at a time, but at the same time youd like to know how many rows therere total. How do you display that to the user?
Answer:
SELECT SQL_CALC_FOUND_ROWS page_title FROM web_pages LIMIT 1,10; SELECT FOUND_ROWS(); The second query (not that COUNT() is never used) will tell you how many results therere total, so you can display a phrase "Found 13,450,600 results, displaying 1-10". Note that FOUND_ROWS does not pay attention to the LIMITs you specified and always returns the total number of rows affected by query.
Show Answer
Hide Answer
7. How would you write a query to select all teams that won either 2, 4, 6 or 8 games?
Answer: SELECT team_name FROM teams WHERE team_won IN (2, 4, 6, 8)
Show Answer
Hide Answer
8. How would you select all the users, whose phone number is null?
Answer: SELECT user_name FROM users WHERE ISNULL(user_phonenumber);
Show Answer
Hide Answer
9. What does this query mean: SELECT user_name, user_isp FROM users LEFT JOIN isps USING (user_id) ?
Answer: Its equivalent to saying SELECT user_name, user_isp FROM users LEFT JOIN isps WHERE users.user_id=isps.user_id
Show Answer
Hide Answer
10. How do you find out which auto increment was assigned on the last insert?
Answer: SELECT LAST_INSERT_ID() will return the last value assigned by the auto_increment function. Note that you dont have to specify the table name.
Show Answer
Hide Answer
11. What does i-am-a-dummy flag to do when starting MySQL?
Answer: Makes the MySQL engine refuse UPDATE and DELETE commands where the WHERE clause is not present.
Show Answer
Hide Answer
12. On executing the DELETE statement I keep getting the error about foreign key constraint failing. What do I do?
Answer: What it means is that so of the data that youre trying to delete is still alive in another table. Like if you have a table for universities and a table for students, which contains the ID of the university they go to, running a delete on a university table will fail if the students table still contains people enrolled at that university. Proper way to do it would be to delete the offending data first, and then delete the university in question. Quick way would involve running SET foreign_key_ch
Show Answer
Hide Answer
13. When would you use ORDER BY in DELETE statement?
Answer: When youre not deleting by row ID. Such as in DELETE FROM techpreparation_com_questions ORDER BY timestamp LIMIT 1. This will delete the most recently posted question in the table techpreparation_com_questions.
Show Answer
Hide Answer
14. How can you see all indexes defined for a table?
Answer: SHOW INDEX FROM techpreparation_questions;
Show Answer
Hide Answer
15. How would you change a column from VARCHAR(10) to VARCHAR(50)?
Answer: ALTER TABLE techpreparation_questions CHANGE techpreparation_content techpreparation_CONTENT VARCHAR(50).
Show Answer
Hide Answer
16. How would you delete a column?
Answer: ALTER TABLE techpreparation_answers DROP answer_user_id.
Show Answer
Hide Answer
17. How would you change a table to InnoDB?
Answer: ALTER TABLE techpreparation_questions ENGINE innodb;
Show Answer
Hide Answer
18. When you create a table, and then run SHOW CREATE TABLE on it, you occasionally get different results than what you typed in. What does MySQL modify in your newly created tables?
Answer: 1. VARCHARs with length less than 4 become CHARs
2. CHARs with length more than 3 become VARCHARs.
3. NOT NULL gets added to the columns declared as PRIMARY KEYs
4. Default values such as NULL are specified for each column
Show Answer
Hide Answer
19. How do I find out all databases starting with ‘tech to which I have access to?
Answer: SHOW DATABASES LIKE ‘tech%;
Show Answer
Hide Answer
20. How do you concatenate strings in MySQL?
Answer: CONCAT (string1, string2, string3)
Show Answer
Hide Answer
21. How do you get a portion of a string?
Answer: SELECT SUBSTR(title, 1, 10) from techpreparation_questions;
Show Answer
Hide Answer
22. whats the difference between CHAR_LENGTH and LENGTH?
Answer: The first is, naturally, the character count. The second is byte count. For the Latin characters the numbers are the same, but theyre not the same for Unicode and other encodings.
Show Answer
Hide Answer
23. How do you convert a string to UTF-8?
Answer: SELECT (techpreparation_question USING utf8);
Show Answer
Hide Answer
24. What do % and _ mean inside LIKE statement?
Answer: % corresponds to 0 or more characters, _ is exactly one character.
Show Answer
Hide Answer
25. What does + mean in REGEXP?
Answer: At least one character. Appendix G. Regular Expressions from MySQL manual is worth perusing before the interview.
Show Answer
Hide Answer
26. How do you get the month from a timestamp?
Answer: SELECT MONTH(techpreparation_timestamp) from techpreparation_questions;
Show Answer
Hide Answer
27. How do you offload the time/date handling to MySQL?
Answer: SELECT DATE_FORMAT(techpreparation_timestamp, ‘%Y-%m-%d) from techpreparation_questions; A similar TIME_FORMAT function deals with time.
Show Answer
Hide Answer
28. How do you add three minutes to a date?
Answer: ADDDATE(techpreparation_publication_date, INTERVAL 3 MINUTE)
Show Answer
Hide Answer
29. whats the difference between Unix timestamps and MySQL timestamps?
Answer: Internally Unix timestamps are stored as 32-bit integers, while MySQL timestamps are stored in a similar manner, but represented in readable YYYY-MM-DD HH:MM:SS format.
Show Answer
Hide Answer
30. How do you convert between Unix timestamps and MySQL timestamps?
Answer: UNIX_TIMESTAMP converts from MySQL timestamp to Unix timestamp, FROM_UNIXTIME converts from Unix timestamp to MySQL timestamp.
Show Answer
Hide Answer
31. What are ENUMs used for in MySQL?
Answer: You can limit the possible values that go into the table. CREATE TABLE months (month ENUM ‘January, ‘February, ‘March,…); INSERT months VALUES (April);
Show Answer
Hide Answer
32. How are ENUMs and SETs represented internally?
Answer: As unique integers representing the powers of two, due to storage optimizations.
Show Answer
Hide Answer
33. How do you start and stop MySQL on Windows?
Answer: net start MySQL, net stop MySQL
Show Answer
Hide Answer
35. Explain the difference between mysql and mysql interfaces in PHP?
Answer: mysqli is the object-oriented version of mysql library functions.
Show Answer
Hide Answer
37. What does tee command do in MySQL?
Answer: tee followed by a filename turns on MySQL logging to a specified file. It can be stopped by command note.
Show Answer
Hide Answer
38. Can you save your connection settings to a conf file?
Answer: Yes, and name it ~/.my.conf. You might want to change the permissions on the file to 600, so that Its not readable by others.
Show Answer
Hide Answer
39. How do you change a password for an existing user via mysqladmin?
Answer: mysqladmin -u root -p password "newpassword"
Show Answer
Hide Answer
40. Use mysqldump to create a copy of the database?
Answer: mysqldump -h mysqlhost -u username -p mydatabasename > dbdump.sql
Show Answer
Hide Answer
41. Have you ever used MySQL Administrator and MySQL Query Browser?
Answer: Describe the tasks you accomplished with these tools.
Show Answer
Hide Answer
42. What are some good ideas regarding user security in MySQL?
Answer: There is no user without a password. There is no user without a user name. There is no user whose Host column contains % (which here indicates that the user can log in from anywhere in the network or the Internet). There are as few users as possible (in the ideal case only root) who have unrestricted access.
Show Answer
Hide Answer
43. Explain the difference between MyISAM Static and MyISAM Dynamic. ?
Answer: In MyISAM static all the fields have fixed width. The Dynamic MyISAM table would include fields such as TEXT, BLOB, etc. to accommodate the data types with various lengths. MyISAM Static would be easier to restore in case of corruption, since even though you might lose some data, you know exactly where to look for the beginning of the next record.
Show Answer
Hide Answer
44. What does myisamchk do?
Answer: It compressed the MyISAM tables, which reduces their disk usage.
Show Answer
Hide Answer
45. Explain advantages of InnoDB over MyISAM?
Answer: Row-level locking, transactions, foreign key constraints and crash recovery.
Show Answer
Hide Answer
46. Explain the differences between INNER JOIN and LEFT JOIN
Answer: INNER JOIN returns only the rows with matching values in both tables, while LEFT JOIN returns all rows from the left table and the matched rows from the right table, filling in NULLs where there is no match.
Show Answer
Hide Answer
47. What are stored procedures and what are their advantages
Answer: Stored procedures are precompiled collections of SQL statements that can be executed as a single unit. Their advantages include improved performance, code reusability, and enhanced security through encapsulation.
Show Answer
Hide Answer
48. Describe how indexing works in MySQL
Answer: Indexes in MySQL are data structures that improve the speed of data retrieval by providing quick access paths to records. They can be unique or non-unique and can be created on one or more columns.
Show Answer
Hide Answer
49. What is a foreign key and how is it used
Answer: A foreign key is a field that links to the primary key of another table, establishing a relationship between the two. It ensures referential integrity by restricting actions that would lead to orphaned records.
Show Answer
Hide Answer
50. How do you optimize a slow-performing query
Answer: To optimize a slow-performing query, analyze the query execution plan, use appropriate indexes, avoid SELECT *, minimize joins, and consider data caching strategies.
Show Answer
Hide Answer
51. What is the difference between UNION and UNION ALL
Answer: UNION combines the results of two or more queries while removing duplicate records, whereas UNION ALL includes all records from both queries, retaining duplicates.
Show Answer
Hide Answer
52. Explain the concept of ACID properties in databases
Answer: ACID stands for Atomicity, Consistency, Isolation, and Durability, which are the key properties that ensure reliable transactions in a database system.
Show Answer
Hide Answer
53. What are indexes and how many types of indexes are there in MySQL
Answer: Indexes are used to speed up the retrieval of rows by creating a pointer to the data. MySQL supports various index types including PRIMARY, UNIQUE, INDEX, FULLTEXT, and SPATIAL.
Show Answer
Hide Answer
54. How do you handle deadlocks in MySQL
Answer: Deadlocks in MySQL can be handled by using appropriate isolation levels, implementing retry logic in applications, and avoiding long transactions that hold locks for extended periods.
Show Answer
Hide Answer
55. What are triggers and how are they used in MySQL
Answer: Triggers are database operations that are automatically performed in response to certain events on a table, such as INSERT, UPDATE, or DELETE, allowing for automated business logic.
Show Answer
Hide Answer
56. What is normalization and what are its advantages
Answer: Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. Its advantages include reduced data anomalies and improved data organization.
Show Answer
Hide Answer
57. Explain the difference between a primary key and a unique key
Answer: A primary key uniquely identifies each record in a table and cannot accept NULL values, while a unique key also ensures uniqueness but can accept one NULL value.
Show Answer
Hide Answer
58. Describe the use of transactions in MySQL
Answer: Transactions in MySQL are used to group a set of operations into a single logical unit of work, ensuring that either all operations succeed or none at all, maintaining data integrity.
Show Answer
Hide Answer
59. What are views and why are they useful
Answer: Views are virtual tables created by querying one or more base tables. They are useful for simplifying complex queries, providing security, and encapsulating underlying data structures.
Show Answer
Hide Answer
60. How can you improve MySQL performance
Answer: MySQL performance can be improved by optimizing queries, using proper indexing, configuring server settings, and partitioning large tables.
Show Answer
Hide Answer
61. What are subqueries and how are they different from joins
Answer: Subqueries are queries nested within another query, enabling complex filtering. Unlike joins, which combine rows from different tables, subqueries can return a single value or a result set.
Show Answer
Hide Answer
62. What is denormalization and when would you use it
Answer: Denormalization is the process of combining tables to improve read performance. It is used when read performance is prioritized over write performance and data consistency.
Show Answer
Hide Answer
63. Explain the difference between MyISAM and InnoDB storage engines
Answer: MyISAM is a non-transactional engine optimized for read-heavy operations, while InnoDB supports transactions, foreign keys, and row-level locking, making it suitable for high-concurrency applications.
Show Answer
Hide Answer
64. What are the different types of joins in SQL
Answer: The different types of joins in SQL include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN, each serving a different purpose in combining rows from tables.
Show Answer
Hide Answer
65. How does MySQL handle memory management
Answer: MySQL uses buffer pools for InnoDB to manage memory allocation effectively, caching frequently accessed data, and using memory for query execution and temporary tables.
Show Answer
Hide Answer
66. What is the purpose of the EXPLAIN statement in MySQL
Answer: The EXPLAIN statement provides insight into how MySQL executes a query, including information about table access methods and the order of operations, aiding in query optimization.
Show Answer
Hide Answer
67. How do you back up and restore a MySQL database
Answer: Backups can be created using the mysqldump command or MySQL Workbench, saving data in SQL format. Restoration can be done using the mysql command to execute the backup file.
Show Answer
Hide Answer
68. What is partitioning in MySQL and its benefits
Answer: Partitioning in MySQL involves dividing a large table into smaller, more manageable pieces without physically separating data, improving performance and maintenance.
Show Answer
Hide Answer
69. How do you perform full-text search in MySQL
Answer: Full-text search can be performed by creating a FULLTEXT index on columns and using the MATCH() AGAINST() syntax to query the indexed text efficiently.
Show Answer
Hide Answer