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;
Tutorials Class - Output Window
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?

TypeReturns
INNER JOINRows that match in both tables
LEFT JOINAll rows from the left table, plus matches from the right
RIGHT JOINAll rows from the right table, plus matches from the left
CROSS JOINEvery row of the first table with every row of the second
Self joinA 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;
ACIDMeaning
AtomicityAll or nothing
ConsistencyData stays valid
IsolationTransactions do not disturb each other
DurabilitySaved 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?

PointPrimary keyUnique key
NULL allowedNoYes (it allows NULL values)
How many per tableOneMany
PurposeIdentifies each rowStops 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;
Tutorials Class - Output Window
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;
Tutorials Class - Output Window
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;
Tutorials Class - Output Window
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?

PointDATETIMETIMESTAMP
Range1000-01-01 to 9999-12-311970-01-01 to 2038-01-19
Time zoneStored as it isConverted to UTC and back to the session time zone
Auto updatePossible with a defaultCommon 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;
Tutorials Class - Output Window
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.