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 MySQL Storage Engines?

A storage engine is the part of MySQL that decides how a table stores, reads and locks its data. You can choose an engine for each table.

EngineFeaturesUse
InnoDB (default)Transactions, foreign keys, row locking, crash recoveryAlmost everything
MyISAMFast reads, table locking, no transactions or foreign keys. OldRare, old systems
MEMORYKeeps data in RAM. Lost on restartTemporary fast tables
CSVStores data in CSV filesData exchange
ARCHIVECompressed, only INSERT and SELECTLogs
CREATE TABLE logs (
  id INT PRIMARY KEY,
  message TEXT
) ENGINE = InnoDB;

SHOW ENGINES;                       -- list of engines
SHOW TABLE STATUS WHERE Name = 'logs';   -- engine of a table

Use InnoDB unless you have a special reason. It is needed for transactions and foreign keys.

How to select Top 10 records in MySQL?

MySQL does not have the TOP keyword of SQL Server. Use LIMIT with ORDER BY.

SELECT name, marks
FROM students
ORDER BY marks DESC
LIMIT 10;

This returns the 10 students with the highest marks. With only 6 rows in the table, it returns all of them, from the highest marks to the lowest. Without ORDER BY the rows that you get are not guaranteed. See MySQL LIMIT.

Write a MySQL query to select students with less than 50% marks

The examples use the students table from our MySQL tutorial: id, name, city, marks, course_id.

SELECT * FROM students WHERE marks < 50;
Tutorials Class - Output Window
id name city marks course_id
6 Neha Mumbai 45 2

If the marks are out of 100, then marks below 50 are below 50 percent. Use <= to include 50. See MySQL WHERE.

Write a MySQL query to select students with marks between 70 and 80

The examples use the students table from our MySQL tutorial: id, name, city, marks, course_id.

SELECT * FROM students WHERE marks BETWEEN 70 AND 80;
Tutorials Class - Output Window
id name city marks course_id
3 John Pune 78 1

BETWEEN includes 70 and 80 themselves. It is the same as marks >= 70 AND marks <= 80. See IN, BETWEEN and IS NULL.

Write a query to fetch top N records

The examples use the students table from our MySQL tutorial: id, name, city, marks, course_id.

SELECT name, marks
FROM students
ORDER BY marks DESC
LIMIT 3;
Tutorials Class - Output Window
name marks
Maria 92
Asha 88
Robin 85

Change the number after LIMIT to get the top N. For the lowest N use ORDER BY marks ASC. See MySQL LIMIT.

Write a query to find the names of employees that begin with ‘S’

Use LIKE with the wildcard %. Here we use an employees table with a name column.

SELECT name
FROM employees
WHERE name LIKE 'S%';

'S%' means: starts with S, followed by anything. For names that end with S use '%S', and for names that contain S anywhere use '%S%'. See MySQL LIKE.

Write a query to create a new table which consists of data and structure copied from the other table

-- structure and data
CREATE TABLE students_backup AS
SELECT * FROM students;

-- only the structure (empty table, with indexes)
CREATE TABLE students_empty LIKE students;

-- structure first, then copy the data
INSERT INTO students_empty SELECT * FROM students;

CREATE TABLE ... AS SELECT copies the columns and the data, but it does not copy the primary key, indexes and auto increment. CREATE TABLE ... LIKE copies the full structure, including indexes, but no data. Use WHERE in the SELECT to copy only some rows.

Write a query to retrieve the first four characters of name from the student table

The examples use the students table from our MySQL tutorial: id, name, city, marks, course_id.

SELECT name, LEFT(name, 4) AS first_four
FROM students;
Tutorials Class - Output Window
name first_four
Robin Robi
Maria Mari
John John
Asha Asha
Vikram Vikr
Neha Neha

SUBSTRING(name, 1, 4) gives the same result. Names shorter than four letters are returned as they are. See MySQL String Functions.

Write a query to select if column has no/empty value

The examples use the students table from our MySQL tutorial: id, name, city, marks, course_id.

-- rows with no value (NULL)
SELECT name FROM students WHERE course_id IS NULL;
Tutorials Class - Output Window
name
Vikram

NULL and an empty string are different. To find both, check both:

SELECT * FROM users
WHERE email IS NULL OR email = '';

WHERE column = NULL never returns a row, because NULL is not equal to anything. Use IS NULL and IS NOT NULL. See IN, BETWEEN and IS NULL.

What is MySQL?

MySQL is a free and open source relational database management system (RDBMS). It stores data in tables (rows and columns), and you work with the data using SQL.

SELECT name, marks FROM students WHERE marks > 80;

It is used by many websites, including WordPress, and works with PHP, Python, Java, Node.js and other languages. See MySQL Introduction.

What is the difference between SQL and MySQL?

PointSQLMySQL
What it isA language (standard) for databasesA database management system (software)
Used forWriting queriesStoring data and running queries
ExamplesSELECT, INSERTMySQL, PostgreSQL, SQL Server and Oracle are products that all use SQL

You write SQL, and MySQL runs it. Each product adds small differences to the standard language.

What is a primary key in MySQL?

A primary key is a column (or group of columns) that uniquely identifies each row. It cannot be NULL and cannot repeat. A table can have only one primary key.

CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);

A primary key also gets an index automatically, so searches by it are fast. See MySQL Constraints.

What is a foreign key in MySQL?

A foreign key is a column in one table that points to the primary key of another table. It links the two tables and stops wrong values.

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  course_id INT,
  FOREIGN KEY (course_id) REFERENCES courses(id)
);

Now a student cannot get a course_id that does not exist in courses, and a course that has students cannot be deleted by mistake. The tables must use InnoDB. See MySQL JOINs.

What is the difference between DELETE, TRUNCATE and DROP?

CommandRemovesWHERE?Can be rolled back?
DELETEChosen rowsYesYes, inside a transaction
TRUNCATEAll rows, table stays. Counter restartsNoNo
DROPThe table (structure and data)NoNo
DELETE FROM students WHERE id = 6;
TRUNCATE TABLE students;
DROP TABLE students;

TRUNCATE is faster than DELETE without WHERE. See DELETE and DROP TABLE.

What is the difference between WHERE and HAVING?

The examples use the students table from our MySQL tutorial: id, name, city, marks, course_id.

PointWHEREHAVING
FiltersRowsGroups
RunsBefore GROUP BYAfter GROUP BY
Aggregate functionsNot allowedAllowed
SELECT city, COUNT(*) AS students
FROM students
WHERE marks > 50
GROUP BY city
HAVING COUNT(*) > 1;
Tutorials Class - Output Window
city students
Delhi 2

First WHERE removes Neha (45). Then the rows are grouped by city. Only Delhi has more than one student left. See GROUP BY and HAVING.

What is the difference between INNER JOIN and LEFT JOIN?

JOINReturns
INNER JOINOnly the rows that match in both tables
LEFT JOINAll rows of the left table, and the matching rows of the right table (NULL if none)
SELECT s.name, c.course_name
FROM students s
LEFT JOIN courses c ON s.course_id = c.id
WHERE s.id IN (1, 5)
ORDER BY s.id;
Tutorials Class - Output Window
name course_name
Robin Python
Vikram NULL

Vikram has no course. With INNER JOIN he would not appear at all. See MySQL JOINs.

What is the difference between CHAR and VARCHAR in MySQL?

PointCHAR(n)VARCHAR(n)
LengthFixed. Always n characters, paddedVariable, up to n characters
SpaceAlways nOnly the text length (plus 1 or 2 bytes)
Best forValues of the same length: country code, PINNames, emails, titles
country_code CHAR(2),       -- IN, US, UK
name         VARCHAR(100)    -- any name up to 100 characters

Use VARCHAR for most text. See MySQL Data Types.

What is an index in MySQL?

An index helps MySQL find rows quickly, without reading every row of the table, like the index of a book helps you find a page.

CREATE INDEX idx_city ON students (city);

EXPLAIN SELECT * FROM students WHERE city = 'Delhi';
  • Use indexes on columns used in WHERE, JOIN and ORDER BY.
  • Indexes use space and make INSERT, UPDATE and DELETE a little slower, so do not index everything.

See MySQL Indexes.

What is normalization in MySQL?

Normalization means organising data in tables so that the same data is not stored in many places. It saves space and avoids mistakes when data changes.

FormRule
1NFEvery cell has one value (no lists in a cell). Every row is unique
2NF1NF, and every column depends on the whole primary key
3NF2NF, and columns do not depend on other non-key columns

Example: instead of writing the course name in every student row, we keep a courses table and store only a course_id in students. If the name changes, you change it in one place. See MySQL JOINs.

What is the difference between UNION and UNION ALL?

PointUNIONUNION ALL
DuplicatesRemovedKept
SpeedSlower (has to find duplicates)Faster
SELECT city FROM students
UNION
SELECT city FROM students;       -- 4 rows

SELECT city FROM students
UNION ALL
SELECT city FROM students;       -- 12 rows

Both queries must have the same number of columns. See MySQL UNION.