All MySQL Interview Questions
All MySQL interview questions with simple answers. Covers primary and foreign keys, JOINs, WHERE vs HAVING, DELETE vs TRUNCATE vs DROP, indexes, views, triggers, stored procedures, transactions and common query tasks.
What are aggregate functions in MySQL?
The examples use the students table from our MySQL tutorial: id, name, city, marks, course_id.
SELECT COUNT(*), SUM(marks), AVG(marks), MIN(marks), MAX(marks)
FROM students;
| COUNT(*) | SUM(marks) | AVG(marks) | MIN(marks) | MAX(marks) |
|---|---|---|---|---|
| 6 | 453 | 75.5000 | 45 | 92 |
Aggregate functions ignore NULL values (except COUNT(*)). See MySQL Aggregate Functions.
What is a view in MySQL?
CREATE VIEW top_students AS
SELECT name, marks FROM students WHERE marks >= 80;
SELECT * FROM top_students;
A view hides complex queries and can limit what columns a user can see. See MySQL Views.
What are the types of JOINs in MySQL?
| Type | Returns |
|---|---|
| INNER JOIN | Rows that match in both tables |
| LEFT JOIN | All rows from the left table, plus matches from the right |
| RIGHT JOIN | All rows from the right table, plus matches from the left |
| CROSS JOIN | Every row of the first table with every row of the second |
| Self join | A table joined with itself, using aliases (for example employees and their managers) |
MySQL has no FULL OUTER JOIN. You can create it with LEFT JOIN ... UNION ... RIGHT JOIN. See MySQL JOINs.
What is a transaction in MySQL? Explain ACID
A transaction is a group of statements that are saved together (COMMIT) or cancelled together (ROLLBACK). It needs the InnoDB engine.
START TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT;
| ACID | Meaning |
|---|---|
| Atomicity | All or nothing |
| Consistency | Data stays valid |
| Isolation | Transactions do not disturb each other |
| Durability | Saved data survives a crash |
See MySQL Transactions.
What is AUTO_INCREMENT in MySQL?
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50)
);
INSERT INTO students (name) VALUES ('Robin'), ('Maria');
SELECT LAST_INSERT_ID();
The ids are 1 and 2 without us writing them. LAST_INSERT_ID() gives the id of the last inserted row. Deleted numbers are not used again, and TRUNCATE restarts the counter.
What is the difference between primary key and unique key?
| Point | Primary key | Unique key |
|---|---|---|
| NULL allowed | No | Yes (it allows NULL values) |
| How many per table | One | Many |
| Purpose | Identifies each row | Stops duplicate values in a column, like email |
CREATE TABLE users (
id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE
);
See MySQL Constraints.
How to find the second highest marks in MySQL?
The examples use the students table from our MySQL tutorial: id, name, city, marks, course_id.
-- 1. with a subquery
SELECT MAX(marks) AS second_highest
FROM students
WHERE marks < (SELECT MAX(marks) FROM students);
-- 2. with LIMIT and OFFSET
SELECT DISTINCT marks
FROM students
ORDER BY marks DESC
LIMIT 1 OFFSET 1;
| second_highest |
|---|
| 88 |
The highest marks are 92 (Maria), so the second highest is 88 (Asha). For the Nth highest, change the OFFSET to N – 1. See Subqueries.
How to find duplicate records in a MySQL table?
The examples use the students table from our MySQL tutorial: id, name, city, marks, course_id.
SELECT city, COUNT(*) AS total
FROM students
GROUP BY city
HAVING COUNT(*) > 1;
| city | total |
|---|---|
| Delhi | 2 |
| Mumbai | 2 |
Delhi and Mumbai appear in two rows each. To see the full duplicate rows, join this result back with the table. To prevent duplicates in the future, add a UNIQUE constraint. See GROUP BY and HAVING.
What is a subquery in MySQL?
The examples use the students table from our MySQL tutorial: id, name, city, marks, course_id.
SELECT name, marks
FROM students
WHERE marks > (SELECT AVG(marks) FROM students);
The inner query returns the average marks (75.5), and the outer query returns the students above it: Robin, Maria, John and Asha. See MySQL Subqueries.
What is NULL in MySQL? Is NULL the same as 0 or an empty string?
NULL means “no value” or “unknown”. It is not zero and not an empty string.
SELECT NULL = NULL, NULL IS NULL, 0 IS NULL, '' IS NULL;
| NULL = NULL | NULL IS NULL | 0 IS NULL | ” IS NULL |
|---|---|---|---|
| NULL | 1 | 0 | 0 |
NULL = NULL gives NULL (not true). Always use IS NULL and IS NOT NULL. Aggregate functions ignore NULL, and COALESCE(column, 0) replaces NULL with a value.
What is the difference between DATETIME and TIMESTAMP?
| Point | DATETIME | TIMESTAMP |
|---|---|---|
| Range | 1000-01-01 to 9999-12-31 | 1970-01-01 to 2038-01-19 |
| Time zone | Stored as it is | Converted to UTC and back to the session time zone |
| Auto update | Possible with a default | Common for created_at and updated_at |
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
See MySQL Data Types and Date Functions.
What is a stored procedure in MySQL?
DELIMITER //
CREATE PROCEDURE get_students_by_city(IN city_name VARCHAR(50))
BEGIN
SELECT * FROM students WHERE city = city_name;
END //
DELIMITER ;
CALL get_students_by_city('Delhi');
A procedure saves code on the server, can take parameters, and can be reused by many programs. DELIMITER is only needed in the command line, so the ; inside the procedure does not end the command too early.
What is a trigger in MySQL?
CREATE TRIGGER log_deleted_student
AFTER DELETE ON students
FOR EACH ROW
INSERT INTO student_log (student_id, action)
VALUES (OLD.id, 'deleted');
Every time a row is deleted from students, the trigger adds a row to student_log. OLD is the row before the change and NEW is the row after it. Use triggers carefully, because they run hidden in the background.
How to count the number of rows in a table in MySQL?
The examples use the students table from our MySQL tutorial: id, name, city, marks, course_id.
SELECT COUNT(*) AS total FROM students;
SELECT COUNT(*) AS delhi FROM students WHERE city = 'Delhi';
SELECT COUNT(DISTINCT city) AS cities FROM students;
| total | delhi | cities |
|---|---|---|
| 6 | 2 | 4 |
The three results are 6 rows in the table, 2 students from Delhi and 4 different cities. See Aggregate Functions.
How to get the current date and time in MySQL?
SELECT NOW(), CURDATE(), CURTIME();
NOW() returns something like 2025-01-15 10:30:00, CURDATE() returns 2025-01-15 and CURTIME() returns 10:30:00. The values change every second. Use DEFAULT CURRENT_TIMESTAMP to save the creation time of a row. See Date Functions.
How to add a column to an existing table in MySQL?
ALTER TABLE students ADD COLUMN email VARCHAR(100);
ALTER TABLE students ADD COLUMN status VARCHAR(10) DEFAULT 'active' AFTER name;
Existing rows get NULL (or the default value) in the new column. To remove it use ALTER TABLE students DROP COLUMN email;. See MySQL ALTER TABLE.
- « Previous
- 1
- 2