Top 61 MySQL Interview Questions And Answers

Question 1

Describe `BLOB` in MySQL. What is it used for?

Accepted Answer

**BLOB** or Binary Large Object can be used to store binary data in MySQL. Sometimes binary data like images need to be stored in SQL databases. For example you might want to store user photos along with other user details in the database table. Binary data of the user photo can be saved as a **BLOB**. By using **BLOB**, we will not require separate storage for images. **BLOB** helps in removing complexity and providing portability in such cases.

Question 2

What is an `AGGREGATE` function. Name few aggregate functions used in MySQL.

Accepted Answer

Aggregate functions are functions which are used to get a single summary value like minimum, maximum or average of a group of values.

> **COUNT**, **SUM**, **AVG**, **MIN**, **MAX** are examples of MySQL aggregate functions.

**COUNT** function is used to count number of entries returned by **SELECT** query. Similarly  **SUM**, **AVG**, **MIN** and **MAX** can be used to calculate sum, average, minimum and maximum of a set of values returned by database query.

Question 3

Explain _foreign key constraint_ in MySQL

Accepted Answer

A **Foreign key constraint** ensures cross referencing of values between two tables. A column or group of columns in a child table references a column or group of columns in another table. **Foreign key constraint** is defined in child table telling about the table and column(s) which are being referred. Foreign key constraint ensures referential integrity. Foreign key columns need to be indexed in MySQL, so an index is automatically created on foreign key column if index is not created on the column while creating a table with foreign key constraint.

Question 4

How are `VARCHAR` and `CHAR` different. Talk about cases where you will use one over other.

Accepted Answer

Both **CHAR** and **VARCHAR** data types store characters up to specified length.

1. **CHAR** stores characters of fixed length while **VARCHAR** can store characters of variable length. 
2. Storage and retrieval of data is different in **CHAR** and **VARCHAR**. 
3. **CHAR** internally takes fixed space, and if stored character length is small, it is padded by trailing space characters. **VARCHAR** has 1 or 2 byte prefix along with stored characters. 
4. **CHAR** has slightly better performance. 
5. **CHAR** has memory allocation equivalent to the maximum size specified while **VARCHAR** has variable length memory allocation.

Question 5

What is _self referencing_ foreign key? Give an example.

Accepted Answer

A foreign key which is stored in a table itself is called to be **self referencing foreign key**.

For example consider an *Employee* database table. It has *employee_id* as primary key as well as a *manager_id* which is *employee_id* of his manager. If we create a foreign key constraint, as a manager is also an employee, *manager_id* will reference to *empolyee_id* in the same table. The *Employee* table with self referencing foreign key *manager_id* can be created using below statement.

```sql
CREATE TABLE `Employee`( 
`name` VARCHAR(25) NOT NULL, 
`employee_id` CHAR(9) NOT NULL, 
`manager_id` CHAR(9) NOT NULL, 
`salary` decimal(10,2) NULL,  
PRIMARY KEY(`employee_id`),
FOREIGN KEY (manager_id) REFERENCES employee(employee_id) ON DELETE CASCADE
);
```

Question 6

Explain `DEFAULT` constraint in MySQL

Accepted Answer

**DEFAULT** constraint provides a default value to a column. In case a row is inserted into the table and no value is provided to the column, the default value is taken.

Consider a table Customer created using statement below.
```sql
CREATE TABLE `Customer`(
	`customerid` CHAR(5) NOT NULL,  
	`name` VARCHAR(25) NOT NULL, 
	`country` VARCHAR(25) NOT NULL, 
	PRIMARY KEY(`customerid`)); 
```
If we run **INSERT** command as  below, we will get error as country name is not provided.
```sql
INSERT INTO `Customer` (`customerid`, `name`) VALUES ('12345','William Jones'); 
```
To fix this problem you can add a DEFAULT constraint using command below. Default value USA will be taken on running **INSERT** command above.
```sql
ALTER TABLE Customer ALTER country SET DEFAULT 'USA';
```

Question 7

What is the difference between _Data Definition Language (DDL)_ and _Data Manipulation Language (DML)_?

Accepted Answer

* **Data definition language (DDL)** commands are the commands which are used to define the database. **CREATE**, **ALTER**, **DROP** and **TRUNCATE** are some common DDL commands.

* **Data manipulation language (DML)** commands are commands which are used for manipulation or modification of data. **INSERT**, **UPDATE** and **DELETE** are some common DML commands.

Question 8

What is the difference between `TRUNCATE` and `DELETE`?

Accepted Answer

* **DELETE** is a Data Manipulation Language(DML) command. It can be used for deleting some specified rows from a table. **DELETE** command can be used with **WHERE** clause.

* **TRUNCATE** is a Data Definition Language(DDL) command. It deletes all the records of a particular table. **TRUNCATE** command is faster in comparison to **DELETE**. While **DELETE** command can be rolled back, **TRUNCATE** can not be rolled back in MySQL.

Question 9

What are different _integer data types_ in MySQL? How can you use _unsigned integer_ in MySQL?

Accepted Answer

MySQL has different INT data types with different storage requirements including:

* **TINYINT** (1 byte) , 
***SMALLINT** (2 byte), 
* **MEDIUMINT** (3 byte), 
* **INT** (4 byte) and 
* **BIGINT** (8 byte).

All the INT types can be used as signed or unsigned. You can write UNSIGNED after data type to tell if it is unsigned.

For example below command will create a *Student* table which can have mark as unsigned **SMALLINT** value.
```sql
CREATE TABLE `Student`(
`name` VARCHAR(25) NOT NULL, 
`marks` SMALLINT UNSIGNED);
```

Question 10

What is _Primary Key Constraint_ and _Unique Key Constraints_?

Accepted Answer

**Primary key** is a column or a group of columns in table which uniquely identifies a row in database. A primary key column can not have `NULL` value. Values in primary key column or columns must be unique so that a row or entry can be uniquely identified using primary key.

**Unique key constraint** ensures that values entered in a column are unique. Although primary key values will be unique, you might want to ensure that some other column also has non-duplicate entries. For example if we have multiple users and want to ensure that no two users with same email id are added to the table, we can put **UNIQUE** key constraint on *email_id* column. `NULL` values are ignored in case of Unique key constraint which means `NULL` is allowed value for email id in this case.

Top 61 MySQL Interview Questions

What is a VIEW in MySQL. How can you create and query a view?

What is the difference between Data Definition Language (DDL) and Data Manipulation Language (DML)?

What is the difference between TRUNCATE and DELETE?

What is an AGGREGATE function. Name few aggregate functions used in MySQL.

Describe BLOB in MySQL. What is it used for?

How are VARCHAR and CHAR different. Talk about cases where you will use one over other.

What is Primary Key Constraint and Unique Key Constraints?

What is self referencing foreign key? Give an example.

Explain foreign key constraint in MySQL

Explain DEFAULT constraint in MySQL

What are different integer data types in MySQL? How can you use unsigned integer in MySQL?

What is the use of DELIMETER command in MySQL?

What are key constraints. What different types of constraints are there in MySQL?

What are REPEAT, LOOP and WHILE statements used for?

What is difference between BLOB and TEXT in MySQL?

What happens if a parent row which is referenced by child row is being deleted in case of foreign key constraint?

What are different TEXT data types in MySQL. What is difference between TEXT and VARCHAR?

What is MySQL Workbench?

What is the use of IN and BETWEEN in MySQL queries?

What different stored objects are supported in MySQL?

What is a trigger. What are different type of triggers in MySQL?

What is index in MySQL? What is advantage of index?

What is the difference between commands create database and create schema in MySQL?

What is mysqldump?

Both TIMESTAMP and DATETIME are used to store data and time. Explain difference between them and when should one be used?

What is Stored Function in MySQL. How are they different from Stored Procedure?

What are Derived Columns. What possible problems can a derived column pose?

Explain the use of FEDERATED tables in MySQL

How can VIEW be used to provide security layer for your app?

Explain GRANT command in MySQL

What is cursor used in MySQL? What are properties of MySQL cursor?

What are some advantages and disadvantages of stored procedures in MySQL?

What is faster, one big query or many small queries?

What is autocommit in MySQL? Can you run a transaction without disabling autocommit?

What is Memory Storage Engine in MySQL? What are heap tables?

What is master-slave replication in MySQL? What are its advantages?

What is advantage of FULLTEXT over LIKE for performing text search in MySQL?

Compare MySQL and PostgresSQL

What are differences between MyISAM and InnoDB database engines commonly used in MySQL?

What is database engine or storage engine? Mention few storage engines supported by MySQL and their use.

What does OPTIMIZE TABLE command do in MySQL?

Which partitioning types does MySQL support?

What are some major differences between MySQL and Oracle database?

What are the differences between MongoDB and MySQL?

Why you should never use GUIDs as part of clustered index?

How do you make schema changes to a live database without downtime?

Which is better for JOIN & INSERT - PostgreSQL or MySQL?

What is the use of SAVEPOINT in MySQL?

How many tables can a trigger associate to in MySQL? Can a trigger be associated to a view?

What happens to a trigger in MySQL if an operation which trigger is associated with fails? Does the trigger execute?

What is difference between horizontal and vertical partitioning? Does MySQL support both horizontal and vertical partitioning?

How can you delete one or multiple columns from a MySQL database table?

Find duplicate values in a SQL table

What happens if a duplicate entry is already there when adding constraint?

What is AUTO INCREMENT in MySQL? Explain with an example.

How can you add one or multiple columns after a certain column in an existing MySQL table?

How can ENUM be used in MySQL. Give an example.

Write a query to concatenate and display all DISTINCT names in a single row

Provide an example of UPSERT logic using MySQL

Remove duplicate rows from a table

A multiple column index is created over firstName, lastName, city columns of a Customer table. Will this index be used for SELECT queries based on only first_name, only last_name or only city values?

What is a `VIEW` in MySQL. How can you create and query a view?

What is the difference between `TRUNCATE` and `DELETE`?

What is an `AGGREGATE` function. Name few aggregate functions used in MySQL.

Describe `BLOB` in MySQL. What is it used for?

How are `VARCHAR` and `CHAR` different. Talk about cases where you will use one over other.

Explain `DEFAULT` constraint in MySQL

What is the use of `DELIMETER` command in MySQL?

What are `REPEAT`, `LOOP` and `WHILE` statements used for?

What is difference between `BLOB` and `TEXT` in MySQL?

What are different `TEXT` data types in MySQL. What is difference between `TEXT` and `VARCHAR`?

What is the use of `IN` and `BETWEEN` in MySQL queries?

What is the difference between commands `create database` and `create schema` in MySQL?

What is `mysqldump`?

Both `TIMESTAMP` and `DATETIME` are used to store data and time. Explain difference between them and when should one be used?

Explain the use of `FEDERATED` tables in MySQL

How can `VIEW` be used to provide security layer for your app?

Explain `GRANT` command in MySQL

What is `autocommit` in MySQL? Can you run a transaction without disabling autocommit?

What is advantage of `FULLTEXT` over `LIKE` for performing text search in MySQL?

What does `OPTIMIZE TABLE` command do in MySQL?

Which is better for `JOIN` & `INSERT` - PostgreSQL or MySQL?

What is the use of `SAVEPOINT` in MySQL?

What is `AUTO INCREMENT` in MySQL? Explain with an example.

How can `ENUM` be used in MySQL. Give an example.

Write a query to concatenate and display all `DISTINCT` names in a single row

Provide an example of `UPSERT` logic using MySQL

A multiple column index is created over `firstName`, `lastName`, `city` columns of a `Customer` table. Will this index be used for `SELECT` queries based on only `first_name`, only `last_name` or only `city` values?