Frequently Asked MySQL Interview Questions (2021)

mysql interview questions & answers

We are living in a data-driven world. It is estimated that everyday businesses generate 2.5 quintillion bytes of digital data for various purposes. From business decisions to operations, everything relies on data.  Therefore, storing this data has become crucial for enterprises today.  

MySQL database is one such open-source Relational Database Management System for storing, accessing and managing data. It also opens a plethora of job opportunities in the field of database administration and management. If you want to explore a career in the MySQL database, we have compiled a list of frequently asked MySQL interview questions for you. 

Basic MySQL Interview Questions: 

1. What are Heap tables and what is their max size? 

Heap is a table created with a hashed index in the memory storage engine and is used temporarily for high-speed data storage.  

The data in the heap table is stored without any specific order. Hence, while retrieving from the heap, it is accessed in order of data pages and not in the order in which it was inserted. 

The size of the heap table is controlled by a variable: max_heap_table_size which has a default value of 16MB. You can set the max_heap_table_size based on the maximum size permitted for the system’s internal MEMORY tables.  

2. How to add new columns, and change column names in MySQL? 

Syntax to add new columns in MySQL: 

ALTER TABLE `table_name` ADD COLUMN `column_name` `data_type` 

Syntax to change column names: 

ALTER TABLE table_name 

RENAME COLUMN old_column_name TO new_column_name;

3. How to join tables in MySQL? 

MySQL JOIN command helps in retrieving data from multiple tables into a single table based on a related column in both of them. 

There are six types of MySQL JOINs: 

types of MySQL joins
Image credit: educba.com
  • Self Join: Table joins with itself 
  • Inner Join: Retrieves records that match in both table 
  • Left Join: Retrieves matching records from the right table and all records from the left table 
  • Right Join: Returns matching records from the left table and all records from the right table 
  • Cross Join: Provides cartesian product of joining tables, i.e. returns all possible combinations of records.  
  • Full-outer join: Retrieves all records from two tables that have a common column. 

An example MySQL query to perform inner join: 

The two tables are Customers and Orders, with CustomerID being the common column in both. 

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate 
FROM Orders 
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

4. How to drop the primary key in MySQL? 

A primary key is a single or a combination field identifier for each record in a table.  

Syntax to drop a primary key: 

ALTER TABLE table_name   

DROP PRIMARY KEY;

5. How do you create a Stored Procedure in MySQL? 

Syntax to create a Stored Procedure: 

CREATE_PROCEDURE procedure_name[(parameter datatype[, parameter datatype])] 

BEGIN 

     Body_section of SQL Statements 

END;

To execute the Stored Procedure: 

You use the CALL query to retrieve the saved Stored procedure. 

CALL stored_procedure_name(argument_list);

Learn more about Stored Procedures here. 

6. Differentiate between TRUNCATE and DELETE in MySQL. 

TRUNCATE  DELETE 
It is a Data Definition Language (DDL) command which deletes all rows without removing table structure from the database  It is a Data Manipulation Language (DML) command which also deletes the columns while keeping the table structure intact 
It does not reinitialize the table, so on entering the new rows, the AUTO_INCREMENT number starts after the last inserted row  It re-initializes the table and on entering the new rows, the AUTO_INCREMENT number starts from 1 
Cannot use the WHERE clause in TRUNCATE  Can use WHERE clause in a DELETE command 

  

You can’t use it with indexed views  Can use with indexed views 
It is a complex command that deletes data permanently  It is an easy command and deletes data from the table which is retrievable later 

 7. What are ‘triggers’? What are the types of triggers in MySQL? 

Triggers are special stored procedures that automatically invoke responses to an event. A trigger only invokes when the data changes in a table, unlike Stored Procedures which you can invoke explicitly. 

There are 2 types of triggers in MySQL: 

Row-level trigger: Fired when there are INSERT, UPDATE query in MySQL or DELETE statements on any rows.  

Statement level trigger: Fired on a table regardless of how many rows are changed. 

Interview Questions on MySQL Data Types 

1. Differentiate between CHAR and VARCHAR. 

CHAR  VARCHAR 
Contains non-binary strings and column length is fixed  Contains non-binary strings but column length can vary 
Max no of characters – 255  Maximum no of characters –4000 
Uses static memory allocation  Uses dynamic memory allocation 
Ideal to use when the data sizes for the column is consistent  Ideal to use when the size of data entries vary significantly. When the data length exceeds 8000 bytes, we use VARCHAR(MAX) 

2. What is the usage and advantage of ENUMs in MySQL? 

ENUMS are string objects that allow limiting the possible values of input data. For example, the following ENUM captures gender data: 

ENUM (‘male’, ‘female’, ‘other’) 

Hence, any input that is not among the values defined in the ENUM will be rejected. 

Advantages of ENUM data type: 

  • Compact data storage for a column with a limited set of possible values. The string values in ENUM are automatically used as a numeric index. 
  • Allows readable queries as it can translate the numbers back to the corresponding string. 
  • Accepts various data types like string, integer, floating-point, and decimal. 

3. Differentiate CHAR_LENGTH() and LENGTH()? 

Both CHAR_LENGTH() and LENGTH() return the length of a string. But a significant difference is that CHAR_LENGTH() returns the string length as the character count of a variable whereas LENGTH() returns the string length as a byte count.   

Example input: 

SELECT  
CHAR_LENGTH('HackerTrail'),  
LENGTH('HackerTrail'); 

Example output: 
+--------------------+---------------+ 

| CHAR_LENGTH('HackerTrail') | LENGTH('HackerTrail') | 

+--------------------+---------------+ 

|                  11 |             11 | 

+--------------------+---------------+

 

 

4. How many string types are available in MySQL?

 The string types available for columns are: 

  • SET 
  • BLOB 
  • ENUM 
  • CHAR 
  • TEXT 
  • VARCHAR 
  • BINARY 
  • VARBINARY 

5. Differences between MySQL Datetime and Timestamp Data Types 

DateTime  Timestamp 
Stores date-time information  This also stores date time but first converts the date time to UTC during storage and converts back from UTC to the current time zone during retrieval 

  

Range 1000-01-01 00:00:00′ to ‘9999-12-31 23:59:59’.  Range 1970-01-01 00:00:01′ UTC to ‘2038-01-19 03:14:07’ UTC. 

MySQL Interview Questions on Keys

1. What are concatenated primary keys? 

Concatenated primary keys are composite identifiers that are made from the combination of two or more columns in a table. 

Eg. Consider the following Product table: 

PRODUCT 
OrderID  ProdID  Quantity  SellingPrice  Discount  Shipped 

Here no single column can be used as a primary key because a single order can have multiple products and the same product can be in multiple orders. Therefore, the possible solution is to create a composite or concatenated key on ‘OrderID’ and ‘ProdID’.  

2. How to use “ORDER BY” on a primary key?  

The ‘Order by’ clause can be used only if you are selecting by the Primary Key of the table or an Index that sorts the rows of the table as required. 

MySQL Query syntax: 

SELECT * from Table_name ORDER BY primary_key;

3. List down the differences between the primary key and the candidate key. 

The differences between the primary key and candidate key are:

Primary Key  Candidate Key 
A unique and non-null identifier for a record in a table  A unique key for a record in a table however a table can have multiple candidate keys 
Value cannot be null  Value can be null 
It is a crucial part of a table or a relation  It signifies which key can be a primary key 
The primary key is always a  candidate key  Candidate key might or might not be a primary key 

4. Can we create a MySQL foreign key without a primary key?  

Yes, you can create a MySQL foreign key without a primary key, however, it must be a column with a unique constraint on it. 

MySQL DBA questions 

1. What are the different set operations available in MySQL?

  • UNION – This operation returns all distinct rows from the query 
  • UNION ALL – This operation returns all the distinct and duplicate rows from the query. 
  • MINUS – This operation uses two select statements. It returns all the rows from the first select statement that were not found in the second select. 
  • INTERSECT – this operation returns all rows selected from both queries. 

2. What is database black box and white box testing? 

Black Box testing: A testing approach to test functionality through interfaces based on given specifications or test cases. Black box test does not require to have in-depth knowledge of the technology used to implement the database. It includes data-mapping, verification of input and output data, and various functions like boundary value analysis and equivalence partitioning. 

White box testing: A testing approach to test the implementation part of a database.  It is used for module testing of database functions and checking of referential integrity rules. In-depth technical knowledge about the internal structure is a must as well as knowing how it works. It involves testing of database tables, schema, data model, triggers, SQL views, etc.  

To further illustrate this: 

  • CAR is the subject under test 
  • The driver is the black box tester. 
  • The mechanic is the white box tester. 
  • The driver will test the car based on the driving experience while a mechanic would be able to test on more sophisticated details internally which may not be visible or exposed to the driver. 

3. How to test for null values in a database?

Use the ISNULL operator in the SQL statement to check if a field has any null values.  

SELECT column_names FROM table_name WHERE column_name IS NULL;

4. How to display the highest salary from a SQL table?  

Use the SQL function MAX to display the highest salary. Here is the MySQL query for the same:  

SELECT MAX(salary) from EMPLOYEES;

5. How can you increase the performance of the MySQL SELECT query? 

Use the ‘LIMIT clause to limit the search in a table. 

Syntax: 

SELECT {fieldname(s) | *} FROM tableName(s) [WHERE condition] LIMIT  N;

Use LEFT or RIGHT MySQL JOIN to retrieve selective data from the tables but avoid OUTER JOIN. 

Syntax for LEFT JOIN:  

SELECT column_name(s) 

FROM table1 

LEFT JOIN table2 

ON table1.column_name = table2.column_name;
Syntax for RIGHT JOIN: 

SELECT column_name(s) 

FROM table1 

RIGHT JOIN table2 

ON table1.column_name = table2.column_name;

Use the WHERE clause to filter data in your SELECT statement. 

Syntax:  

SELECT field1, field2,...fieldN table_name1, table_name2… 

[WHERE condition1 [AND [OR]] condition2..... 

Avoid using the ‘*’ in Select statements. 

SELECT * FROM tablename;

Advanced MySQL Interview Questions 

1. What are the various ways to create indexes? 

There are two ways to create an index in MySQL: 

  • At the time of table creation using CREATE TABLE  
SYNTAX:

CREATE TABLE table_name 

(  

  column1 datatype [ NULL | NOT NULL ], 

  column2 datatype [ NULL | NOT NULL ], 

  ... 

  column_n datatype [ NULL | NOT NULL ], 

 

  INDEX index_name [ USING BTREE | HASH ] 

    (index_col1 [(length)] [ASC | DESC],  

     index_col2 [(length)] [ASC | DESC], 

     ... 

     index_col_n [(length)] [ASC | DESC]) 

);

 

  • Using CREATE INDEX to add indexes to existing tables 
SYNTAX: 

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name 

  [ USING BTREE | HASH ] 

  ON table_name 

    (index_col1 [(length)] [ASC | DESC],  

     index_col2 [(length)] [ASC | DESC], 

     ... 

     index_col_n [(length)] [ASC | DESC]);

 

2. Difference between B-tree and Hash Indexes. 

B-Tree  Hash Index 
Used for comparisons between columns like =, >, < , >=, <=, or BETWEEN operators  Used only for equality comparisons like =, <=, >= 
Searches next entry in the order  Can’t search for the next entry in the order 
Any leftmost prefix key can fetch rows.  Only whole keys can fetch rows. 

 

3. How can you filter the duplicate data while retrieving records from the table? 

The keyword DISTINCT filters duplicate data while retrieving data from the table. 

Here is the syntax:  

SELECT DISTINCT field_name from table_name;

 The digital world is building a culture that takes action based on data.  This also creates a need gap for experts who can store, access, and operate this data. If you wish to gain database expertise, our list of MySQL Interview questions is the right place for you to start. Moreover, as a part of your interview preparations, you can further read the SQL interview questions list. 

  

  

 

Responses

HACKERBUCK AWARDED