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.
| Engine | Features | Use |
|---|---|---|
| InnoDB (default) | Transactions, foreign keys, row locking, crash recovery | Almost everything |
| MyISAM | Fast reads, table locking, no transactions or foreign keys. Old | Rare, old systems |
| MEMORY | Keeps data in RAM. Lost on restart | Temporary fast tables |
| CSV | Stores data in CSV files | Data exchange |
| ARCHIVE | Compressed, only INSERT and SELECT | Logs |
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;
| 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;
| 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;
| 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;
| 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;
| 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?
| Point | SQL | MySQL |
|---|---|---|
| What it is | A language (standard) for databases | A database management system (software) |
| Used for | Writing queries | Storing data and running queries |
| Examples | SELECT, INSERT | MySQL, 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?
| Command | Removes | WHERE? | Can be rolled back? |
|---|---|---|---|
DELETE | Chosen rows | Yes | Yes, inside a transaction |
TRUNCATE | All rows, table stays. Counter restarts | No | No |
DROP | The table (structure and data) | No | No |
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.
| Point | WHERE | HAVING |
|---|---|---|
| Filters | Rows | Groups |
| Runs | Before GROUP BY | After GROUP BY |
| Aggregate functions | Not allowed | Allowed |
SELECT city, COUNT(*) AS students
FROM students
WHERE marks > 50
GROUP BY city
HAVING COUNT(*) > 1;
| 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?
| JOIN | Returns |
|---|---|
INNER JOIN | Only the rows that match in both tables |
LEFT JOIN | All 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;
| 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?
| Point | CHAR(n) | VARCHAR(n) |
|---|---|---|
| Length | Fixed. Always n characters, padded | Variable, up to n characters |
| Space | Always n | Only the text length (plus 1 or 2 bytes) |
| Best for | Values of the same length: country code, PIN | Names, 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,JOINandORDER BY. - Indexes use space and make
INSERT,UPDATEandDELETEa 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.
| Form | Rule |
|---|---|
| 1NF | Every cell has one value (no lists in a cell). Every row is unique |
| 2NF | 1NF, and every column depends on the whole primary key |
| 3NF | 2NF, 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?
| Point | UNION | UNION ALL |
|---|---|---|
| Duplicates | Removed | Kept |
| Speed | Slower (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.