MySQL Interview Questions

Last Updated : 22 Jul, 2024
Comments
Improve
Suggest changes
Like Article
Like
Save
Share
Report
News Follow

MySQL is a Free open-source Relational Database Management System(RDMS) that stores data in a structured tabular format using rows and columns. It uses Structured Query Language (SQL) for accessing, managing, and manipulating databases. It was originally developed by MySQL AB, a Swedish company, and is now owned by Oracle Corporation. It’s known for its high performance, reliability, and ease of use, making it one of the most popular databases in the world.

Here, we’ll cover 50+ MySQL Interview Questions with answers that are asked in Data Analyst/SQL Developer Interviews at MAANG and other high-paying companies. Whether you are a fresher or an experienced professional with 1, 5, or 10+ years of experience, this article gives you all the confidence you need to ace your next interview.

MySQL Interview Questions And Answers for Freshers

1. What is MySQL and How does it differ from other relational databases?

MySQL is an open-source relational database management system (RDBMS) that is widely used for managing structured data. It utilizes SQL (Structured Query Language) for querying and managing data. MySQL is known for its reliability, scalability, and performance, making it a popular choice for various applications

2. How to create a database in MySQL?

To create a database in MySQL, we can use the CREATE DATABASE statement followed by the name we want to give to our database. For example:

CREATE DATABASE mydatabase;

3. Difference between CHAR and VARCHAR data types.

  • CHAR: Fixed-length character data type where the storage size is predefined. Trailing spaces are padded to reach the defined length.
  • VARCHAR: Variable-length character data type where the storage size depends on the actual data length. No padding of spaces is done.

4. Explain the differences between SQL and MySQL?

SQL

MySQL

It is a structured query language that manages the relational database management system.

It is a relational database management system that uses SQL.

It is not an open-source language.

MySQL is an open-source platform. It allows access to anyone.

SQL supports XML and user defined functions.

It doesn’t support XML and any user defined functions

SQL can be implemented in various RDBMS such as PostgreSQL, SQLite, Microsoft SQL Server, and others.

MySQL is a specific implementation of an RDBMS that uses SQL for querying and managing databases.

SQL itself is not a product and doesn’t have a license. It’s a standard language.

MySQL is open-source and available under the GNU General Public License (GPL).

5. What is the MySQL server’s default port?

3306 is MySQL server‘s default port.

6. How can we learn batch mode in MySQL?

Below is the syntax used to run batch mode.

mysql <batch-file>;

mysq <batch-file> mysql.out

7. How many different tables are present in MySQL?

There are 5 types of tables present in MySQL.

  • Heap table
  • merge table
  • MyISAM table
  • INNO DB table
  • ISAM table

8. What are the differences between CHAR and VARCHAR data types in MySQL?

  • Storage and retrieval have been different for CHAR and VARCHAR.
  • Column length is fixed in CHAR but VARCHAR length is variable.
  • CHAR is faster than VARCHAR.
  • CHAR datatype can hold a maximum of 255 characters while VARCHAR can store up to 4000 characters.

9. What is Difference between CHAR_LENGTH and LENGTH?

LENGTH is byte count whereas CHAR_LENGTH is character count. The numbers are the same for Latin characters but different for Unicode and other encodings.

Syntax of CHAR_LENGTH:

SELECT CHAR_LENGTH(column_name) FROM table_name;

Syntax of LENGTH:

SELECT LENGTH(column_name) FROM table_name;

10. What do you understand by % and _ in the like statement?

‘_’ corresponds to only one character but ‘%’ corresponds to zero or more characters in the LIKE statement.

11. How many index columns can be created in a table?

There are 16 indexed columns can be created in a table.

12. What are string types available for columns?

There are six string types available for the column.

13. Explain the main difference between FLOAT and DOUBLE?

  • FLOAT stored floating point number with 8 place accuracy. The size of FLOAT is 4 bytes.
  • DOUBLE also stored floating point numbers with 18 place accuracy. The size of DOUBLE is 8 bytes.

14. Explain the differences between BLOB and TEXT.

BLOB:

A BLOB is a large object in binary form that can hold a variable amount of data. Sorting and comparing in BLOB values are case-sensitive.

There are four types of BLOB.

  • TINYBLOB
  • BLOB
  • MEDIUMBLOB
  • LONGBLOB

TEXT:

Sorting and comparison are performed in case-insensitive for TEXT values. we can also say a TEXT is case-insensitive BLOB.

There are four types of TEXT.

  • TINYTEXT
  • TEXT
  • MEDIUMTEXT
  • LONGTEXT

15. Explain the difference between having and where clause in MySQL.

  • WHERE statement is used to filter rows but HAVING statement is used to filter groups.
  • GROUP BY is not used with WHERE. HAVING clause is used with GROUP BY.

16. Explain REGEXP?

REGEXP is a pattern match where the pattern is matched anywhere in the search value.

For more detail you refer to our MySQL | Regular expressions Article.

17. How can we add a column in MySQL?

A column is a series of table cells that store a value for table’s each row. we can add column by using ALTER TABLE statement.

ALTER TABLE tab_name

ADD COLUMN col_name col_definition [FIRST|AFTER exist_col];

18. How to delete columns in MySQL?

We can remove columns in MySQL by using ALTER TABLE statement.

Syntax:

ALTER TABLE table_name DROP COLUMN column1, column2….;   

19. How to delete a table in MySQL?

We can delete a table by using DROP TABLE statement. This statement deletes complete data of table.

DROP TABLE table-name;

20. How are mysql_fetch_array() and mysql_fetch_object() different from each another?

mysql_fetch_array() Gets a result row as a related array or a regular array from database. mysql_fetch_object gets a result row as an object from the database.

21. How to get the top 10 rows?

The following query will be used to get top 10 rows.

SELECT * FROM table_name LIMIT 0,10;

22. How does NOW() differ from CURRENT_DATE()?

current year, month, and date with hours, minutes, and seconds is shown by using NOW() command while CURRENT_DATE shows current year current month, and current date.

Syntax:

SELECT NOW();

SELECT CURRENT_DATE();

23. What is the use of the ‘DISTINCT’ keyword in MySQL?

the DISTINCT keyword allows for the removal of all duplicate records and the retrieval of unique records. The DISTINCT keyword is used with the SELECT statement.

Syntax:

SELECT DISTINCT colu1, colum2..

FROM table_name;

24. Which storage engines are used in MySQL?

Storage engines are also called table types. Data is stored in a file using multiple techniques.

Below are some techniques.

  • Locking Level
  • Indexing
  • Storage mechanism
  • Capabilities and functions

25. How to create a table in MySQL?

The CREAT TABLE command will be used to create a table in MySQL.

Syntax:

CREATE TABLE ‘Employee’ (‘Employee_Name’ VARCHAR(128), ‘Employee_ID’ VARCHAR(128), ‘Employee_Salary’ VARCHAR(16), ‘Designation’ CHAR(4)) ;

26. How to insert data in MySQL table?

We can add data to a table using the INSERT INTO statement .

Syntax:

INSERT INTO table_name ( field1, field2, field3 )  

VALUES  ( value1, value2, value3 );  

MySQL Interview Questions

Intermediate MySQL Interview Questions and Answers

27. Write a statement to find duplicate rows In the MySQL table?

The below statement is used to find duplicate rows.

SELECT Table_Name, Category

FROM Product

GROUP BY Name, Category

HAVING COUNT(id) > 1;

28. What types of relationships are used in MySQL?

There are three types of relationships used in MySQL.

One-to-one: Elements with a one to one relationship can be included as columns in the table.

One-to-many: One to many or many to one relationships both are same. It will occur when one row in a table is related to multiple rows in different table.

Many-to-many: Many rows in a table are related to many rows in different table is called many to many relationship.

29. How to insert Date in MySQL?

We can use INSERT statement to insert date in MySQL table. MySQL default date format is YYYY-MM-DD. Automatic MySQL consist many data types to store dates.

  • DATE
  • DATETIME
  • TIMESTAMP
  • YEAR

Syntax:

INSERT INTO table_name (column_name, column_date) VALUES (‘DATE: Manual Date’, ‘2023-5-20’);   

30. What is join? Tell different join in MySQL.

Joins are used to connect two or more tables. It returns only same values in all tables.

There are four different ways to join MySQL tables.

  • Inner Join
  • left Join
  • Right Join
  • Full Join

31. What is a primary key? How to drop the primary key in MySQL?

A primary key in MySQL is a single field or a group of fields that are used to uniquely identify each record in a table. A primary key cannot be null or empty. ALTER TABLE statement is used to delete a primary key from a table.

Syntax:

ALTER TABLE table_name  DROP PRIMARY KEY;    

32. What is a heap table in MySQL?

A heap table is usually used for temporary and fast temporary storage.

  • BLOB or TEXT fields are not permitted in the heap table.
  • comparison operator like =, <,>, = >,=< can be used only.
  • Heap table didn’t support the AUTO_INCREMENT command.
  • Indexes should be NOT NULL in the heap table.

33. What is the main difference between the primary key and the candidate key?

The primary key uniquely identified each row of a table. only one primary key is available for a table.

For mor detail you can see: Difference between Primary and Candidate Key

34. What is the difference between DELETE and TRUNCATE commands in MySQL?

DELETE Command is used to delete rows from the table depending on given the condition. TRUNCATE command is used to DELETE all rows from the table. DELETE command is a Data Manipulation Language command. TRUNCATE command is a Data Definition Language command.

For More detail you can see : Difference between DELETE and TRUNCATE

35. What is InnoDB?

A SQL storage database is called InnoDB database. The InnoDB offers ACID transactions, row-level locking, and foreign key support. InnoDB is owned by Oracle Corporation.

36. What is the difference between UNION and UNION ALL in MySQL?

During combining the results of more than one SELECT statement, the UNION operator deletes duplicate rows between the various SELECT statements. The UNION ALL also combines the result set of more than one SELECT statement, but it does not delete duplicate rows.

37. What is a ‘timestamp’ in MySQL?

In MySQL, When a row is added to or updated in a table, a data type “timestamp” automatically records the time.

38. What is the use of ENUMs in MySQL?

ENUM is a string object that can be used when creating tables to specify a set of predefined values.

CREATE table size(name ENUM(‘Small’, ‘Medium’, ‘Large’);

For more detail refer to those article on Enumerator (Enum) in MySQL

39. How can you control max size of heap in MySQL?

MySQL config variable max_heap_table_size can be used to control the max size of heap.

Syntax:

SET max_heap_table_size = M

40. What is a view? How to create a view?

A database object that has no value is called view. Rows and columns exist in a view. A view is virtual table. it is created by combining one or more tables. The difference of a view and a table is that views are definition that build on other tables. If the underlying table changes, the View will also reflect those same changes.

The below syntax is used to create a view.

Syntax:

CREATE VIEW view_name AS    

SELECT columns    

FROM tables    

[WHERE conditions];    

41. Where MyISAM table will be stored and also give MyISAM formats of storage?

Every MyISAM table is stored on disk. 

There are three storage formats can be used .

  • The .frm file can be used to store table definition.
  • The .MYD( MYData) extension can be used for data files.
  • The .MYI(MYIndex) extension can be used to Index files.

42. How can we save images in MySQL?

In MySQL, Blobs can be used to store images. All database images are first converted into blobs then saved and then they will be added to the database, and finally, it will later be stored on the disk.

43. What are trigger and how many TRIGGERS are available in MySQL table?

A trigger is a procedural code in a database. Triggers are automatically triggered when specific events occur on a particular table. During column updating triggers are invoked automatically.

SIX triggers are available in MySQL table.

  • BEFORE INSERT
  • AFTER INSERT
  • BEFORE UPDATE
  • AFTER UPDATE
  • BEFORE DELETE
  • AFTER DELETE

For more detail you can see: Different types of MySQL Triggers (with examples) – GeeksforGeeks

44. What are the different characteristics of MySQL MyISAM Static and MyISAM Dynamic?

  • Width of all fields is fixed in MyISAM Static table whereas width of all fields is not fixed in MyISAM Dynamic. In MyISAM Dynamic table width will be like TEXT, BOLD, etc.
  • In case of corruption MyISAM static table is easy to store.

MySQL Interview Questions For Experienced

45. What are Access Control Lists?

A list of permissions known as an Access Control List is connected to an object. It is MySQL server security model helps in troubleshooting issues like users being unable to connect. MySQL holds the ACL’s cached in memory. ACL’s also called grant tables. MySQL verifies the authentication data and permissions against the ACLs. It predetermined order whenever a user tries to log in or execute a command.

46. What is Normalization and list the different types of normalization?

Normalization is used to avoid duplication and redundancy. it is a process of organizing data. There are many normal forms of normalization. which are also called successive levels. The first three regular forms are sufficient.

  • First Normal Form (1NF): There are no repeating groups within rows.
  • Second Normal form(2NF): Value of every supporting column depending on the whole primary key.
  • Third Normal Form(3NF): It depends only on the primary key and no other value of non-key column.

47. What are various ways to create an index?

There are many options to create an index as below:

  • T-SQL statements can be used to create an index.
  • The SQL Server Management Studio is available for use. we can use this to browse to the table where the index will be created, and then right-click on the Indexes node. We must select the New Index option over here.
  • We can identify the index indirectly by specifying the PRIMARY KEY and the UNIQUE constraint in the CREATE TABLE or ALTER TABLE statement.

48. What are a clustered index and a non clustered index?

Cluster Index: An index type used to arrange data in a table is called a clustered index. The table’s data are stored in a specific order based on the clustered index.

Non Cluster Index: A non-clustered index is also a type of index used to organize data in a table. The table’s data are not stored in a specific order based on the non clustered index.

For more details, Check our latest article on Difference between Clustered and Non-clustered index.

49. How to validate emails using a single query?

We can use the regular expressions function (REGEXP_LIKE) to validate emails. Below is the example of validate emails using a single query.

SELECT

Email

FROM

Vehicle

where NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+.[A-Z]{2,4}’, ‘i’);

For detail you can check our latest article on Regular expressions (Regexp).

50. How can you handle the –secure-file-priv in MySQL?

The MySQL Server is restricted from loading directories using the LOAD DATA INFILE command by the -secure-file-priv option. Use the SHOW VARIABLES LIKE “secure_file_priv” command to view the directory that has been configured.

There are two options to handle as below.

  • Either transfer your file to the directory that secure-file-priv specifies.
  • Or you can turn off secure-file-priv. This must be removed at the beginning and cannot be disabled later.

Conclusion

In conclusion, preparing well for MySQL interview questions is crucial for data analysts, data engineers, and business analysts. This guide provides important MySQL questions and answers to help you get ready for your interviews.

By studying these, you can show that you are skilled and ready for roles that require strong MySQL knowledge. This preparation will help you stand out in your field.



Previous Article
Next Article

Similar Reads

Functions in MySQL | Numeric, String and Date Time Functions in MySQL
In MySQL, functions play a crucial role in performing various operations on data, such as calculations, string manipulations, and date handling. These built-in functions simplify complex queries and data transformations, making it easier to manage and analyze data within a database. In this article, we will look at these different categories of MyS
4 min read
API Testing Interview Questions for 2024: Top 50 Interview Questions
API testing checks if application programming interfaces (APIs) work as expected in terms of functionality, performance, security, and reliability. Unlike UI testing, which focuses on how an app looks, API testing focuses on how it works behind the scenes. With APIs being essential in modern software, learning API testing is crucial for anyone in q
15+ min read
Active Directory Interview Questions - Top 50+ Questions and Answers for 2024
Active Directory (AD) is a crucial component of modern enterprise IT infrastructure, providing centralized authentication, authorization, and directory services for Windows-based networks. As organizations continue to rely heavily on AD for managing user identities, access controls, and network resources, the demand for skilled AD administrators an
15+ min read
Teacher Interview Questions - Top 70 Questions and Answers for 2024
Teaching is a noble profession that requires a unique blend of knowledge, skills, and passion. As educators, teachers play a crucial role in shaping the minds of future generations, fostering critical thinking, and nurturing the potential of each student. In today's rapidly evolving educational landscape, teachers must be prepared to meet diverse c
15+ min read
TCS Ninja Interview Experience and Interview Questions
Round 1: Online Test TCS Ninja happened first time this year. It was a Nationa Qualifier Test and hence there is so much pressure to do well. Else the chances of getting qualified are very less. The online test of TCS Ninja had 4 sections. Aptitude (20 questions - 40 mins) English (10 questions - 10 mins) Programming Concepts (10 questions - 20 min
2 min read
PHP | MySQL UPDATE Query
The MySQL UPDATE query is used to update existing records in a table in a MySQL database. It can be used to update one or more field at the same time. It can be used to specify any condition using the WHERE clause. Syntax : The basic syntax of the Update Query is - Implementation of Where Update Query : Let us consider the following table "Data" wi
2 min read
PHP | MySQL Database Introduction
What is MySQL? MySQL is an open-source relational database management system (RDBMS). It is the most popular database system used with PHP. MySQL is developed, distributed, and supported by Oracle Corporation. The data in a MySQL database are stored in tables which consists of columns and rows.MySQL is a database system that runs on a server.MySQL
4 min read
PHP | MySQL ( Creating Database )
What is a database? Database is a collection of inter-related data which helps in efficient retrieval, insertion and deletion of data from database and organizes the data in the form of tables, views, schemas, reports etc. For Example, university database organizes the data about students, faculty, and admin staff etc. which helps in efficient retr
3 min read
PHP | MySQL ( Creating Table )
What is a table? In relational databases, and flat file databases, a table is a set of data elements using a model of vertical columns and horizontal rows, the cell being the unit where a row and column intersect. A table has a specified number of columns, but can have any number of rows. Creating a MySQL Table Using MySQLi and PDO We have already
3 min read
PHP | MySQL Select Query
The SQL SELECT statement is used to select the records from database tables. Syntax : The basic syntax of the select clause is - To select all columns from the table, the [TEX] * [/TEX] character is used. Implementation of the Select Query : Let us consider the following table ' Data ' with three columns ' FirstName ', ' LastName ' and ' Age '. To
3 min read
PHP | MySQL Delete Query
The DELETE query is used to delete records from a database table. It is generally used along with the "Select" statement to delete only those records that satisfy a specific condition. Syntax : The basic syntax of the Delete Query is - Let us consider the following table "Data" with four columns ' ID ', ' FirstName ', ' LastName ' and ' Age '. To d
2 min read
PHP | MySQL ORDER BY Clause
The ORDER BY Clause can be used along with the SELECT statement to sort the data of specific fields in an ordered way. It is used to sort the result-set in ascending or descending order. Syntax : The basic syntax of the Order By clause is - Implementation of the Order By Clause : Let us consider the following table "Data" with three columns 'FirstN
3 min read
PHP | MySQL WHERE Clause
The WHERE Clause is used to filter only those records that are fulfilled by a specific condition given by the user. in other words, the SQL WHERE clause is used to restrict the number of rows affected by a SELECT, UPDATE or DELETE query. Syntax : The basic syntax of the where clause is - SELECT Column1 , Column2 , .... FROM Table_Name WHERE Conditi
3 min read
PHP | MySQL LIMIT Clause
In MySQL the LIMIT clause is used with the SELECT statement to restrict the number of rows in the result set. The Limit Clause accepts one or two arguments which are offset and count.The value of both the parameters can be zero or positive integers. Offset:It is used to specify the offset of the first row to be returned. Count:It is used to specify
3 min read
How to Connect to Mysql Server Using VS Code and Fix errors?
MySQL is a relational database management system based on SQL-Structured Query Language used for accessing and managing records in a database. It can be easily connected with programming languages such as Python, Java, and PHP to serve various purposes that require CRUD ( Create,Read,Update,Delete) operations. MySQL is based on a client – server ar
4 min read
MongoDB vs MySQL
Differences between MongoDB and MySQL on various parameters are discussed below: What are MongoDB and MySQL? MongoDB MySQL MongoDB is an open-source database developed by MongoDB, Inc. MongoDB stores data in JSON-like documents that can vary in structure. It is a popular NoSQL database. MySQL is a popular open-source relational database management
2 min read
RLIKE operator in MySQL
RLIKE : This operator in MySQL is used to performs a pattern match of a string expression against a pattern. Syntax : RLIKE pattern Parameters : This method accepts one parameter as mentioned in syntax. pattern -The pattern which we want to match against an expression. Various pattern and their usage are described below.PatternWhat the Pattern matc
2 min read
Queries using AND ,OR ,NOT operators in MySQL
AND, OR, NOT operators are basically used with WHERE clause in order to retrieve data from table by filtering with some conditions using AND, OR, NOT in MySQL.Here in this article let us see different queries on the student table using AND, OR, NOT operators step-by-step. Step-1:Creating a database university: CREATE DATABASE university; Step-2:Usi
2 min read
WEEKOFYEAR() Function in MySQL
WEEKOFYEAR() function in MySQL is used to find the week number for a given date. If the date is NULL, the WEEKOFYEAR function will return NULL. Otherwise, it returns the value of week which ranges between 1 to 53. Note: The WEEKOFYEAR() function considers the first week of the year to be the week that contains the first day of January. Also, the we
3 min read
EXP() Function in MySQL
EXP() function in MySQL is used to returns E raised to the power of a specified number. Here E(2.718281...) is the base of the natural logarithm. Syntax : EXP(X) Parameter : This method accepts one parameter as mentioned above in the syntax and described below : X – A specified number which will be used as a power of E. Returns : It returns E raise
2 min read
BIT_AND() function in MySQL
BIT_AND() : This function in MySQL is used to return the Bitwise AND of all bits in a given expression. It first converts all decimal values into binary values, and then perform bitwise and operation on those binary values. The BIT_AND() function works by performing a bitwise AND operation on each pair of corresponding bits in the binary representa
3 min read
MOD() Function in MySQL
The MOD() function in MySQL is used to find the remainder of one number divided by another. The MOD() function returns the remainder of dividend divided by divisor. if the divisor is zero, it returns NULL. Syntax: MOD(N, M) or N % M or N MOD M Parameter : MOD() function accepts two parameter as mentioned above and described below. N -The dividend i
4 min read
LTRIM() Function in MySQL
LTRIM() : This function in MySQL is used to remove leading spaces from a string. Syntax : LTRIM(str) Parameter : It accepts one parameter as mentioned above and described below as follows. str – The string from which we want to remove leading spaces. Returns : It returns a string after truncating all leading spaces. Example-1 : Removing all the lea
2 min read
How to Manipulate MySQL Storage Engines?
MySQL DBMS offers a wide range of storage engines which are basically software modules used to perform actions like CREATE, INSERT, READ and DELETE in DBMS. Depending upon the user's requirement, we can access various storage engines for MySQL DBMS. For MySQL 5.5 and later versions, the default storage engine is InnoDB.It is necessary to choose the
2 min read
Difference between MySQL and MS SQL Server
SQL is an acronym for Structured Query Language. It is used to access, manipulate and retrieve information from a database. MySQL is an open source Relational Database Management System (RDBMS) based on Structured Query Language (SQL). It runs on platforms like Linux, UNIX and Windows. SQL Server is owned and developed by Microsoft Corporation. The
2 min read
SOUNDS LIKE Function in MySQL
SOUNDS LIKE : This function in MySQL is used to compare the Soundex codes of a given two string expressions. It is used as SOUNDEX(expr1) = SOUNDEX(expr2) to retrieve strings that sound similar. Syntax : expr1 SOUNDS LIKE expr2 Parameter : It accepts two parameter as mentioned above and described below. expr1 - The first string which we want to com
2 min read
MySQL | Operator precedence
Operator precedence specifies the order in which operators are evaluated when two or more operators with different precedence are adjacent in an expression. For example, 1+2/3 gives the different result as compared to (1+2)/3. Just like all other programming languages C, C++, Java etc. MySQL also has a precedence rule. The following table describes
3 min read
MySQL | Hexadecimal Literals
Literal: It can be defined as the value given to variables or constants. Example: int f = 2 Here f is the variable and 2 is the numeric literal. Hexadecimal literal: These are the whole numbers whose base is 16 (in numbering system). Example: int y = X'12A' A hexadecimal literal should be preceded by 0x or X, its value is 12A, i.e., equivalent to 2
3 min read
MySQL | Creating stored function
The CREATE FUNCTION statement is used for creating a stored function and user-defined functions. A stored function is a set of SQL statements that perform some operation and return a single value. Just like Mysql in-built function, it can be called from within a Mysql statement. By default, the stored function is associated with the default databas
2 min read
Connecting to MySQL Using Command Options
In this article, we will learn to connect the MySQL database with a command line interface using command line options. To connect the MySQL database the community provides a command line tool called mysql which comes up with some command line arguments. To connect the MySQL we need to make sure that the mysqld is running on the system. you can run
2 min read
three90RightbarBannerImg