MySQL Interview Questions
& Ans
1. Explain the difference between DELETE, TRUNCATE, and DROP commands in MySQL.
2. What are the different types of joins in MySQL, and when would you use each one?
3. How do you use the WHERE clause in MySQL? Can you use multiple conditions in the WHERE
clause?
4. What is a primary key?
5. How would you retrieve records from a table that have duplicate values in a column?
6. What is the use of the GROUP BY clause? Provide an example where it is useful.
7. How do you handle NULL values in MySQL queries?
8. How do you write an SQL query to find the second-highest salary from a table?
9. What are subqueries in MySQL? Can you provide an example where subqueries are used?
10. What is a foreign key, and why is it important in relational databases?
11. How would you validate the performance of a database query in a QA environment?
12. You are given a large table with millions of rows. How would you test for performance issues
with a specific query or database operation?
13. Which tools have you used to connect to a MySQL database during testing? (e.g., MySQL
Workbench, DBeaver, etc.)
14. Have you used any test automation tools that integrate with MySQL, such as Selenium, JMeter,
or Postman? How did you set them up?
Explain the difference between DELETE, TRUNCATE, and DROP
commands in MySQL.
DELETE: Removes rows from a table based on a WHERE condition. It logs each row deleted
and can be rolled back within a transaction.
o Example: DELETE FROM employees WHERE id = 1;
TRUNCATE: Removes all rows from a table without logging individual row deletions. It's
faster than DELETE and cannot be rolled back.
o Example: TRUNCATE TABLE employees;
DROP: Deletes the entire table (or database), including both data and structure. It’s a
permanent action and cannot be undone.
o Example: DROP TABLE employees;
2. What are the different types of joins in MySQL, and
when would you use each one?
INNER JOIN: Returns only the rows where there are matching values in both tables. Used
when you need data that exists in both tables.
LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table, and matching rows from
the right table. Unmatched rows from the right table return NULL.
RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from the right table, and matching rows
from the left table. Unmatched rows from the left table return NULL.
3. How do you use the WHERE clause in MySQL? Can you
use multiple conditions in the WHERE clause?
The WHERE clause is used to filter records based on a condition. You can use multiple
conditions by combining them with logical operators like AND, OR, and NOT.
Example:
SELECT * FROM employees WHERE age > 30 AND
department = 'HR';
4. What is a primary key?
A primary key is a column (or a combination of columns) in a table that uniquely identifies
each row. It ensures that no two rows have the same value for the primary key column(s)
and disallows NULL values.
Example:
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100)
);
5. How would you retrieve records from a table that
have duplicate values in a column?
You can use the GROUP BY clause with the HAVING clause to identify duplicate values.
Example:
SELECT email, COUNT(*) FROM employees GROUP BY email
HAVING COUNT(email) > 1;
6. What is the use of the GROUP BY clause? Provide an
example where it is useful.
The GROUP BY clause is used to group rows that have the same values in specified columns.
It’s often used with aggregate functions (e.g., SUM, COUNT, AVG).
Example:
SELECT department, COUNT(*) AS total_employees FROM
employees GROUP BY department;
7. How do you handle NULL values in MySQL queries?
To handle NULL values, use IS NULL or IS NOT NULL in your WHERE clause.
Example:
SELECT * FROM employees WHERE manager_id IS NULL;
Additionally, functions like IFNULL or COALESCE can replace NULL values with a default value.
SELECT name, IFNULL(manager_id, 'No Manager') FROM
employees;
8. How do you write an SQL query to find the second-
highest salary from a table?
Example query:
SELECT MAX(salary) FROM employees WHERE salary <
(SELECT MAX(salary) FROM employees);
9. What are subqueries in MySQL? Can you provide an
example where subqueries are used?
A subquery is a query inside another query. It’s useful when you need to filter data based on
the result of another query.
Example:
SELECT name FROM employees WHERE department_id
= (SELECT department_id FROM departments WHERE
department_name = 'HR');
10. What is a foreign key, and why is it important in
relational databases?
A foreign key is a column (or set of columns) in a table that creates a relationship between
two tables. It refers to the primary key in another table, ensuring referential integrity by
enforcing that the value in the foreign key must match a value in the referenced primary key.
Example:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES
customers(customer_id)
);
11. How would you validate the performance of a
database query in a QA environment?
To validate the performance of a database query, you can:
o Use EXPLAIN: Run the query with EXPLAIN to get details about how the query is
executed (e.g., indexes used, table scans).
o Check Query Execution Time: Measure how long the query takes to run using
SELECT NOW() or built-in timing tools.
o Analyze Index Usage: Ensure that appropriate indexes are being used.
o Test with a realistic data volume: Use a dataset that mimics production data to test
how the query performs under actual load.
12. Which tools have you used to connect to a MySQL
database during testing?
Common tools to connect to MySQL during testing:
o MySQL Workbench: A graphical user interface (GUI) tool for database design,
development, and management.
o DBeaver: A universal database tool that supports MySQL and other databases.
o HeidiSQL: Another lightweight tool for managing MySQL databases.
o Command Line Interface (CLI): For directly executing queries and managing
databases.
13. Have you used any test automation tools that
integrate with MySQL, with Selenium? How did you
set them up?
Selenium: You can use Selenium for end-to-end testing with MySQL by integrating
database queries to validate UI data. You can use a MySQL connector in your automation
script to run queries.
o Example:
Set up the MySQL JDBC driver in your Selenium project.
Use Java or Python scripts to connect to the database, run SQL queries,
and verify results with what is displayed on the UI.
SQL - Repeated -Interview-
Questions
===================================================================================
1.Get all data from the table
2.Get particular data from a table
3.find the max salary
4.find the second max salary from the salary table ***
5.how you connect the database in java
6.print the original data from a table
7.order a product from high to low
8.find the fifth highest salary from the salary table **
9.join the two tables using order-id
10.join the three tables using product id
