MySQL Interview Questions and Answers

Last updated on Feb 07, 2024
  • Share
MySQL Interview Questions

MYSQL is one of the most successful and leading pathways toward data administration careers. You will be amazed to know that this database service holds a total of 46.83% share in the relational database market. So to make your career and prepare for interviews in this field would be a good choice. Today we will help with these interviews by providing you with some MYSQL Interview Questions and Answers. MYSQL is one of the most popular database technologies used in the database industry. Popular companies like Microsoft, Amazon, Oracle, and IBM are using this technology on a vast basis to manage and store their data. According to one survey, nowadays more than 90% of organizations are using OSDBMS i.e. Open Source Database Management System. But to handle this database, professionals must know the MYSQL programming language.

Quick Facts About MySQL
What is the latest version of MySQL? 8.0.22 released in 19th October 2020
When was MySQL first released? 23 May 1995
Who developed MySQL? Oracle Corporation.
What language does MySQL use? C and C++ language
About License GNU Public License
Official Website https://www.mysql.com

MySQL Query Interview Questions

Here in this article, we will be listing frequently asked MySQL Interview Questions and Answers with the belief that they will be helpful for you to gain higher marks. Also, to let you know that this article has been written under the guidance of industry professionals and covered all the current competencies.

Q1. What is the difference between delete, drop and truncate?
Answer
DELETE DROP TRUNCATE
It removes Some or All rows from a table. It removes a table from the database. It removes all rows from a table.
A WHERE clause is used to remove particular rows according to the matched condition. All rows will be deleted when we did not use Where condition in Query. It does not require a WHERE clause. It does not require a WHERE clause
The DELETE command is Data Manipulation Language Command The DROP command is Data Definition Language Command. The TRUNCATE command is a Data Definition Language Command.
The DELETE command is more sluggish as compared to the DROP, and the TRUNCATE commands as it removes the rows according to the condition. The DROP Command offers better performance than the DELETE Command but not as in comparison to the Truncate Command since the DROP command erases all tables from databases immediately after the rows have been deleted. The TRUNCATE command performs better that the DROP or DELETE because it removes all records from the table with no condition.
The Integrity Constraints remain same for the DELETE command. The Integrity Constraints disappear with this DROP command. The Integrity Constraints are not removed from the TRUNCATE command.
DELETE operation can be rolled back. DROP operation cannot be rolled back. TRUNCATE operation cannot be rolled back.
DELETE FROM TableName WHERE condition; DROP TABLE table_name; TRUNCATE TABLE table_name;
Q2. Write a query to get the first_name, middle_name and last_name in a single column as “name”. The first name, middle_name and the last name must be separated with space.
id first_name middle_name last_name phone
101 Ram Krit Singh 9873393635
102 Lalit Kumar Verma 9873393636
103 Shyam Singh Verma 9873393637
Answer

SELECT CONCAT(first_name, ' ', middle_name, '', last_name) as name FROM `customers`;

Q3. What are the difference between and MyISAM and InnoDB?
Answer

These are most commonly used storage engines in MySQL are MyISAM and InnoDB.

Difference between MyISAM and InnoDB

  • MyISAM no longer supports transactions, however, InnoDB supports transactions.
  • MyISAM helps Table-level Locking, however, InnoDB supports Row-level Locking.
  • MyISAM helps full-text search, however InnoDB does not.
  • MyISAM is designed for the need for speed but InnoDB is designed for the most performance.
  • MyISAM does now not aid overseas keys, but InnoDB helps foreign keys.
  • We can use commit and rollback with InnoDB however not in MyISAM.
  • MyISAM does no longer assist ACID (Atomicity, Consistency, Isolation, and Durability) however InnoDB supports the ACID property.
  • In the InnoDB table, the AUTO_INCREMENT field is a section of the index but is now not in MyISAM.
  • MyISAM stores its tables, data, and indexes in the disk area with the usage of a separate table name.FRM, desk name.MYD and table name.MYI however InnoDB stores its tables and indexes in a tablespace.
Q4. What is the difference between the primary and unique key in Mysql?
Answer
Primary Key Unique Key
A table can hold only one primary key It can be more than one unique key in one table
A Primary Key cannot be NULL. A Unique key can have NULL.
Q5. Write a query to find the number of customers whose registered in between two dates?
Answer

SELECT COUNT(*) FROM customers WHERE created_at BETWEEN '2022-03-24 17:32:05' AND '2022-03-25 17:32:05';

Q6. What are the different ways to optimize a MySQL query?
Answer
  • Don't use "sp_" while creating a stored procedure.
  • Don't use "*" while you are using the SELECT command. Also avoid unnecessary columns in SELECT clause.
  • You can use table aliases while writing queries.
  • Avoid the usage of the wildcard (%) at the starting of a predicate.
  • DISTINCT and UNION need to be used solely if it is necessary.
Q7. Write a query to get all details of customers whose name starts with an alphabet ‘A’?
Answer

SELECT * FROM customers WHERE name LIKE 'A%';

Q8. Write a query to get minimum and maximum salaries from the Employee table?
id name email salary
101 Umesh Singh [email protected] 20000
102 Sonu Singh [email protected] 10000
103 Avinash [email protected] 7000
104 Manish [email protected] 60000
105 Rekha [email protected] 5000
106 Shiv [email protected] 20000
Answer

SELECT name, email, min(salary) as minSalary, max(salary) as maxSalary from employee;

Q9. Write a query to find the Nth highest salary from the table without using limit keyword in MySQL?
Answer

SELECT name, salary FROM Employee AS emp1 WHERE N-1 = (SELECT COUNT(DISTINCT salary) FROM Employee emp2 WHERE emp2.salary > emp1.salary);

Q10. Write a query to find a list of invalid email address from a table in MySQL?

Table: customers

id name email
101 Umesh Singh umesh.singh
102 Sonu Singh [email protected]
103 Avinash [email protected]
104 Manish bestinterviewquestion.com
106 Shiv [email protected]
Answer

SELECT * FROM `customers` WHERE `email` NOT REGEXP '^[^@]+@[^@]+\.[^@]{2,}$';

Conclusion

In MYSQL Database, data is stored in tabular form and that is why we also call it a Relational Database Management System. With the help of it, users can develop an infinite number of applications but it is mainly used for web applications. Talking about platforms, it can run on various platforms like LINUX, UNIX, and WINDOWS and is reliable, fast, and very easy to use. We hope these questions will help you to clear your core concepts regarding MYSQL. Let us help you with some short and important tips which might help you with your interviews-

  • MYSQL is a programming language, so always avoid YES and NO answers.
  • If you don’t know the answer, simply say I don't know rather than pretending you know.
  • Always carry a pen and notepad with you.
  • If you are stuck somewhere don’t panic, just stay calm and think out loud.
  • Go with the expectation that you will reject a lot, as “Failure is the key to Success”.
Reviewed and verified by Umesh Singh
Umesh Singh

My name is Umesh Singh having 11+ experience in Node.JS, React JS, Angular JS, Next JS, PHP, Laravel, WordPress, MySQL, Oracle, JavaScript, HTML and CSS etc. I have worked on around 30+ projects. I lo...