Question 1

What is MySQL?

Accepted Answer

MySQL is an open-source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL). It is one of the most popular databases in the world, known for its reliability, ease of use, and performance. It follows a client-server architecture and is a core component of the LAMP (Linux, Apache, MySQL, PHP/Python/Perl) stack.

Question 2

What are the main features of MySQL?

Accepted Answer

Primary features of MySQL include:
1. **Relational Database System**: Stores data in tables with rows and columns.
2. **High Performance**: Optimized for fast read and write operations.
3. **Scalability**: Can handle large datasets and high traffic with ease.
4. **ACID Compliance**: Supports transactions via InnoDB engine.
5. **Security**: Multi-layered security with host-based verification and encrypted connections.

Question 3

What is the difference between MySQL and SQL?

Accepted Answer

SQL (Structured Query Language) is a standard language used to interact with databases. MySQL is an actual software project (an RDBMS) that implements SQL. 
- **SQL**: A language.
- **MySQL**: A database engine that understands that language.

Question 4

What is the default port for MySQL?

Accepted Answer

The default port for MySQL is **3306**.
Code Example:
mysql -h localhost -P 3306 -u root -p

Question 5

What is a database in MySQL?

Accepted Answer

In MySQL, a database is a container that holds a collection of related tables, views, stored procedures, and other database objects. You use the `CREATE DATABASE` command to create one.
Code Example:
CREATE DATABASE interview_db;

Question 6

What are the common data types in MySQL?

Accepted Answer

MySQL supports several categories of data types:
1. **Numeric**: INT, DECIMAL, FLOAT, DOUBLE.
2. **String**: CHAR, VARCHAR, TEXT, BLOB.
3. **Date and Time**: DATE, DATETIME, TIMESTAMP, YEAR.
4. **Spatial**: GEOMETRY, POINT.

Question 7

What is the difference between CHAR and VARCHAR?

Accepted Answer

**CHAR** is fixed-length. If you define CHAR(10) and store 'ABC', it will use 10 bytes and pad the rest with spaces.
**VARCHAR** is variable-length. If you define VARCHAR(10) and store 'ABC', it will only use 3 bytes plus one byte for length prefix.
VARCHAR is generally preferred for saving space unless the data length is truly constant (like country codes).

Question 8

How do you create a table in MySQL?

Accepted Answer

You use the `CREATE TABLE` statement followed by the table name and column definitions.
Code Example:
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Question 9

What is a Primary Key?

Accepted Answer

A Primary Key is a column (or set of columns) that uniquely identifies each row in a table. 
- It cannot contain NULL values.
- It must be unique.
- A table can have only one primary key.

Question 10

What is a Foreign Key?

Accepted Answer

A Foreign Key is a column that creates a link between two tables. It refers to the Primary Key of another table, ensuring referential integrity by preventing orphaned records.

Question 11

What is the difference between DELETE and TRUNCATE?

Accepted Answer

- **DELETE**: A DML command. It deletes rows one by one and can be filtered with a WHERE clause. It is slower and records each row deletion in the transaction log.
- **TRUNCATE**: A DDL command. It removes all rows from a table by deallocating the pages. It is much faster but cannot use a WHERE clause and resets the AUTO_INCREMENT counter.

Question 12

What is a Unique Constraint?

Accepted Answer

A Unique Constraint ensures that all values in a column are distinct. Unlike Primary Keys, a table can have multiple Unique Constraints, and they *can* accept one NULL value (in MySQL).

Question 13

How do you add a new column to an existing table?

Accepted Answer

You use the `ALTER TABLE` statement with the `ADD` clause.
Code Example:
ALTER TABLE users ADD phone_number VARCHAR(15);

Question 14

What is the auto_increment attribute?

Accepted Answer

The `AUTO_INCREMENT` attribute allows a unique number to be generated automatically when a new record is inserted into a table. This is often used for the Primary Key column.

Question 15

How do you insert data into a MySQL table?

Accepted Answer

You use the `INSERT INTO` statement.
Code Example:
INSERT INTO users (username, email) 
VALUES ('john_doe', 'john@example.com');

Question 16

What is the WHERE clause used for?

Accepted Answer

The `WHERE` clause is used to filter records that fulfill a specified condition in SELECT, UPDATE, or DELETE statements.
Code Example:
SELECT * FROM users WHERE username = 'john_doe';

Question 17

What is the LIMIT clause?

Accepted Answer

The `LIMIT` clause is used to specify the number of records to return. This is useful for pagination or limiting the load on the database.
Code Example:
SELECT * FROM users LIMIT 10 OFFSET 20;

Question 18

How do you sort results in MySQL?

Accepted Answer

You use the `ORDER BY` clause, followed by `ASC` (ascending) or `DESC` (descending).
Code Example:
SELECT * FROM users ORDER BY created_at DESC;

Question 19

What are Aggregate Functions?

Accepted Answer

Aggregate functions perform a calculation on a set of values and return a single value. 
Common ones include: `COUNT()`, `SUM()`, `AVG()`, `MAX()`, and `MIN()`.

Question 20

What is the use of the DISTINCT keyword?

Accepted Answer

The `DISTINCT` keyword is used to return only unique (different) values from a column.
Code Example:
SELECT DISTINCT country FROM users;

Question 21

What is a NULL value?

Accepted Answer

A NULL value represents missing or unknown data. It is *not* the same as zero or an empty string. You test for it using `IS NULL` or `IS NOT NULL`.

Question 22

How do you update existing data in a table?

Accepted Answer

You use the `UPDATE` statement. Always use a WHERE clause unless you want to update all rows!
Code Example:
UPDATE users 
SET email = 'new_email@example.com' 
WHERE id = 1;

Question 23

How do you delete a database or a table?

Accepted Answer

You use the `DROP` command. This is permanent and cannot be undone.
Code Example:
DROP TABLE users;
DROP DATABASE interview_db;

Question 24

What is the LIKE operator?

Accepted Answer

The `LIKE` operator is used in a WHERE clause to search for a specified pattern in a column. 
- `%` represents zero, one, or multiple characters.
- `_` represents a single character.
Code Example:
SELECT * FROM users WHERE username LIKE 'j%';

Question 25

What is the default storage engine for MySQL?

Accepted Answer

Since MySQL 5.5, the default storage engine is **InnoDB**. It supports ACID transactions, row-level locking, and foreign keys.

Question 26

How do you explain Inner Join in MySQL?

Accepted Answer

An Inner Join returns rows only when there is at least one match in both tables. It is used to combine related data that exists in multiple tables based on a common column.
Code Example:
SELECT orders.id, users.username 
FROM orders 
INNER JOIN users ON orders.user_id = users.id;

Question 27

Explain Left Join and Right Join.

Accepted Answer

- **LEFT JOIN**: Returns all rows from the left table and matched rows from the right table. If no match is found, NULL values are returned for columns of the right table.
- **RIGHT JOIN**: Returns all rows from the right table and matched rows from the left table. If no match is found, NULL values are returned for columns of the left table.

Question 28

What is a Cross Join?

Accepted Answer

A Cross Join returns the Cartesian product of the two tables, meaning it joins every row from the first table with every row from the second table. This is generally avoided unless specifically needed for generating combinations.

Question 29

How do you use the GROUP BY clause?

Accepted Answer

The `GROUP BY` clause is used to group rows that have the same values into summary rows, like "find the number of users in each country". It's often used with aggregate functions.
Code Example:
SELECT country, COUNT(id) 
FROM users 
GROUP BY country;

Question 30

What is the difference between WHERE and HAVING?

Accepted Answer

- **WHERE**: Filters rows *before* they are grouped.
- **HAVING**: Filters groups *after* they are formed. It is specifically used with aggregate functions.

MySQL Database Interview Master

Difficulty Filters

Tools & Options

What is MySQL?

What are the main features of MySQL?

What is the difference between MySQL and SQL?

What is the default port for MySQL?

What is a database in MySQL?

What are the common data types in MySQL?

What is the difference between CHAR and VARCHAR?

How do you create a table in MySQL?

What is a Primary Key?

What is a Foreign Key?

What is the difference between DELETE and TRUNCATE?

What is a Unique Constraint?

How do you add a new column to an existing table?

What is the auto_increment attribute?

How do you insert data into a MySQL table?

What is the WHERE clause used for?

What is the LIMIT clause?

How do you sort results in MySQL?

What are Aggregate Functions?

What is the use of the DISTINCT keyword?

What is a NULL value?

How do you update existing data in a table?

How do you delete a database or a table?

What is the LIKE operator?

What is the default storage engine for MySQL?

How do you explain Inner Join in MySQL?

Explain Left Join and Right Join.

What is a Cross Join?

How do you use the GROUP BY clause?

What is the difference between WHERE and HAVING?

What is an Index in MySQL?

What are the different types of Indexes in MySQL?

What is a View in MySQL?

What are Stored Procedures?

What is a Trigger?

What is a Transaction in MySQL?

What are COMMIT and ROLLBACK?

What is the difference between a Function and a Stored Procedure?

How do you check current running queries in MySQL?

What is a Full-Text Search?

What is Query Optimization?

How do you use the EXPLAIN keyword?

What is a Subquery?

What is the difference between UNION and UNION ALL?

What is the COALESCE() function?

What is a Composite Index?

How do you find the second highest salary from a table?

What is Referential Integrity?

What are the ON DELETE CASCADE and ON UPDATE CASCADE options?

What is the difference between NOW() and CURDATE()?

What are the different isolation levels in MySQL?

Explain the ACID properties in MySQL.

What is Deadlock in MySQL and how to handle it?

What is the difference between Optimistic and Pessimistic Locking?

What is a Binary Log (Binlog) in MySQL?

How does MySQL Replication work?

What is the difference between Statement-Based and Row-Based Replication?

What is horizontal and vertical scaling in MySQL?

What is Sharding?

Explain Query Cache in MySQL.

What is a Buffer Pool in InnoDB?

What are Hidden Columns in MySQL 8.0?

What is the difference between MyISAM and InnoDB?

How to perform a backup of a MySQL database?

What is slow query log?

Explain Window Functions in MySQL 8.0.

What are Common Table Expressions (CTEs)?

What is JSON support in MySQL?

Explain the concept of 'Covering Index'.

What is the purpose of the Performance Schema?

What is an 'Upsert' in MySQL?

How to secure a MySQL installation?

What is the difference between a Temporary Table and a Derived Table?

What is 'Ghost Rows' or 'Phantom Read'?

What is adaptive hash index?

How to find duplicate records in a table?

How to delete duplicate records keeping only one?