Top 100 MySQL Interview Questions And Answers

If you are looking for a job in PHP and MySQL these top 100 MySQL interview questions will be conducive. MySQL interview questions will be serviceable if you are applying to companies such as Uber, Airbnb, Shopify, Amazon, Twitter, Udemy, etc.  

MySQL Interview Questions and Answers

Table of Contents

1. What is MySQL database? 

MySQL server is an open-source relational database management system (RDBMS) backed by oracle, based on SQL. 

Usually assessed using PHP. It is written in C, C++ language. 

2. Why should we use MySQL database? 

These MySQL interview questions are best suited for beginners and intermediate users.

  • Mysql server is fast, reliable and easy to use
  • Protects sensitive data from intruders 
  • Scalable and can handle huge amount of data
  • Compatible with almost every OS
  • PHP friendly 
  • Large and extensive community support

3. What are the different tables present in the Mysql database?

Listed below are disparate MySQL table: 

  • MyISAM –  default database engine 
  • Heap – used for fast database access 
  • Merge – removes the size limitation from MYISAM tables
  • InnoDB – supports transaction using COMMIT and Rollback 
  • ISAM – deprecated and removed from version 5.x

4. Illustrate the order of MySQL Query execution. 

It is one of the most dealt MySQL interview questions  

  • From and Joins
  • Where
  • Group by
  • Having
  • Select
  • Order By
  • Limit 

5. What are the different types of Mysql Joins?

This MySQL interview questions could be answered in the below-mentioned way: 

  • Inner Join : SELECT column_name(s) FROM table1 INNER JOIN table 2 ON table1.column_name = table2.column_name;
  • Left Join: SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
  • Right Join: SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table2.column_name = table2.column_name;
See also  Top 100 JavaScript Interview Questions And Answers

6. How to improve the performance of MySQL SELECT Query?

Now this question is one of the MySQL interview questions that should be understood carefully.

  • Use Indexes
  • Don’t use “*” 
  • Avoid unnecessary columns 
  • Use DISTINCT and Union solely if necessary 
  • Use short table aliases 
  • Avoid wildcard (%) at the start of LIKE pattern 

7. How many triggers are allowed in MySQL tables?

This is among beginner MySQL interview questions.

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

8. Mention different set operations available in MySQL 

  • UNION : Combines results from SELECT queries. Returns all distinct rows. 
  • UNION ALL: Returns all rows from all tables meeting the query. 
  • MINUS: Returns all distinct rows selected by the first query but not the second query.
  • INTERSECT: Intersection of both the queries 

9. How to test null value in a database? 

A null value is a field with no value present in that particular field. Moreover, the primary key does not allow null value. 

  • = NULL or !=NULL can’t be used
  • IS NULL: SELECT column_names FROM table_name WHERE column_name IS NULL;
  • IS NOT NULL: SELECT column_names FROM table_name WHERE column_name IS NOT NULL;

10. Illustrate the difference between BLOB and TEXT

BLOB is Binary Large Objects. It is used to store binary data

  • Examples: TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB

TEXT is  Non-binary, character-based string data type 

  • Example: TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT

11. Mention the difference between DELETE vs TRUNCATE 

DELETETRUNCATE
Deletes the data of a table Deletes the data permanently 
Data can be retrieved by Commit and Rollback Commit and Rollback not possible
‘Where’ condition can be used‘Where’ condition can’t be used

12. Differentiate CHAR vs VARCHAR 

CHARVARCHAR
Stores the data in fixed-length format Stores values in variable length 
Strings smaller than specified length are padded with space charactersNo padding of spaces 
Used for small data Used to store large data 

13. Compare MySQL and SQL server.

CriteriaMySQLSQL
Developed byOracleMicrosoft 
Programmed inC and C++Mainly C++, but some parts in C 
PlatformsSupports many platforms Supports only Linux and Windows
SyntaxComplex syntax Simpler and easy to use syntax

14. What is SQL server?

Microsoft SQL server is a structured query language that is one of the database management systems ( DBMS) and is designed by Microsoft. DBMS are computer software applications with the capability of interacting with users, various other applications, and databases. The objective of SQL Server is capturing and analyzing data and managing the definition, querying, creation, updating, and administration of the database. 

15. What are the features of MySQL?

It provides cross-platform support, a wide range of interfaces for application programming, and has many stored procedures like triggers and cursors that help in managing the MySQL database. 

16. How to check the MySQL version? 

We can check the MySQL version on linux using the below command: 

mysql -v (Linux)

SHOW VARIABLES LIKE “%version%” ; (Windows)

17. What is the traditional network library for a system?

In either Windows or POSIX systems, the named pipes provide ways of inter-process communications to connect different processes running on the same machine. It dispenses with the necessity of using network stack, and data can be sent without affecting the performance. 

18. What is the default port for MySQL server ? 

The default port for MySQL server is 3306. Another standard default port is 1433 in TCP/IP for SQL Server.

19. What do DDL, DML, and DCL stand for? 

DDL is the abbreviation for Data Definition Language dealing with database schemas, as well as the description of how data resides in a database. For instance CREATE TABLE command. DML denotes Data Manipulation Language which includes commands such as SELECT, INSERT, etc. DCL stands for Data Control Language and includes commands like GRANT, REVOKE etc. 

20. What is a join in MYSQL? 

This is among the beginner interview questions. In MYSQL joins are used to query data from two or more tables. The query is made using the relationship between certain columns existing in the table. There are four types of joins in MYSQL. 

MySQL Interview Questions - Join in MySql

21. What are the common MYSQL functions?

 Common MYSQL functions are as follows:

NOWO: The function for returning the current date and time as a single value CURRDATEO: The function for returning the current date or time

CONCAT(X, Y): The function to concatenate two strings values creating a single string output 

DATEDIFF (X, Y): The function to determine the difference between two dates

22. What is the difference between CHAR and VARCHAR?

When a table is created, CHAR is used to define the fixed length of the table and columns. The length value could be in the range of 1-255. The VARCHAR command is used to adjust the column and table lengths as required. 

23. What are Heap Tables ? 

This is among the intermediate interview questions. Heap tables are in-memory tables used for high-speed temporary storage. But, TEXT or BLOB fields are not allowed within them. They also do not support AUTO INCREMENT. It works as a temporary table and it uses the indexes that make it faster than another table type. 

24. What is the syntax for concatenating tables in MYSQL?

In mysql database server the syntax for concatenating database table in MYSQL:

CONCAT(string 1, string 2, string 3) 

25. What is the limit of indexed columns that can be created for a table?  

The maximum limit of indexed columns that can be created for any table is 16. 

26. What are the different types of strings used in database columns in MySQL? 

In MySQL, the different types of strings that can be used for database columns are SET, BLOB, VARCHAR, TEXT, ENUM and CHAR. 

27. How to add columns in MySQL? 

A column is a series of cells in a table that stores one value for each row in a table. We can add columns in an existing table using the ALTER TABLE statement as follows:

ALTER TABLE table_name

    ADD COLUMN column_name column_defination

[FIRST|AFTER existing_column];

28. How can you change the root password if it is lost?

In such cases when the password is lost, the user should start the DB with skip-grants-table and then change the password. Thereafter, with the new password, the user should restart the DB in a normal mode. 

29. How to resolve the problem of the data disk that is full? 

When the data disk is full and overloaded, the way out is to create a soft link and move the .frm and the .idb files into that link location. 

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

Basically, DELETE TABLE is a logged operation, and every row deleted is logged. Therefore, the process is usually slow. TRUNCATE TABLE also deletes rows in a table, but it will not log any of the rows deleted. The process is faster here in comparison. TRUNCATE TABLE can be rolled back and is functionally similar to the DELETE statement without a WHERE clause.

31. What is the use of ENUM in MySQL? 

The use of ENUM will limit the values that can go into a table. For example, a user can create a table giving specific month values and other month values would not enter into the table. 

See also  Top 100 Ansible Interview Questions And Answers

32. How to define the testing of network layers in MySQL? 

For this, it is necessary to review the layered architecture and determine hardware and software configuration dependencies with respect to application put to test. 

33. What is the difference between primary key and unique key ?  

While both are used to enforce the uniqueness of the column defined, the primary key would create a clustered index, whereas the unique key would create a non-clustered index on the column. The primary key does not allow ‘NULL’, but the unique key does.

img 617d992446ea3

34. How can you restart SQL Server in the single user or the minimal configuration modes? 

The command line SQLSERVER.EXE used with ‘-m’ will restart SQL Server into single-user mode and the same with ‘-f’ will restart it in the minimal configuration mode.

35. What are the differences between a primary key and a foreign key? 

Primary KeyForeign Key 
It helps in the unique identification of data in a databaseIt helps establish a link between tables
There can only be one primary key for a table There can be more than one foreign key for a table 
Primary key attributes cannot have  duplicate values in a tableDuplicate values are acceptable for a foreign key 
Null values are not acceptable Null values are acceptable 
We can define primary key constraints for temporarily created tablesIt cannot be defined for temporary tables
The primary key index is automatically created The index is not created automatically 

36. What is the TIMESTAMP datatype ? 

TIMESTAMP in mysql server helps in row versioning. Row versioning is a type of concurrency that allows retaining the value until it is committed in the database. It shows the instant time of any event. It consists of both the date and time of the event. Also, TIMESTAMP helps in backing up data during the failure of a transaction. 

37. What is the difference between BLOB and TEXT? 

BLOB is a binary large object holding huge data. Four types of BLOBs are TINYBLOB, BLOB, MEDIBLOB, and LONGBLOB. TEXT is a case-sensitive BLOB. Four types of TEXT are TINY TEXT, MEDIUMTEXT, and LONG TEXT. 

38. How do you login to MySQL using the Unix shell? 

We can login through these command:

# [mysql dir]/bin/ mysql -h <hostname> -u <username> -p <password>

39. How can you find out the version of the installed MySQL? 

  • SELECT version();
  • SHOW VARIABLES LIKE “%version%”;

40. What are the advantages and disadvantages of using MySQL?

There are several advantages of MySQL which are making it a more popular database system now.

Advantages:

  • It is well-known for its reliable and secure database management system. Transactional tasks of the website can be done more securely by using this software. 
  • It supports different types of storage engines to store the data and it works faster for this feature.
  • It can handle millions of queries with a high-speed transactional process.
  • It supports many advanced level database features, such as multi-level transaction, data integrity, and deadlock identification.

Disadvantages: 

  • It is hard to make MySQL scalable.
  • It is not suitable for a very large type of database.
  • The uses of stored routine and triggers are limited to MySQL.
  • Temporary tables uses

41. How to create a table in MySQL?

CREATE TABLE [IF NOT EXISTS] `TableName` (`fieldname`dataType [optional parameters]) ENGINE = storage Engine;

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

 A DISTINCT keyword is used to filter the duplicate data from the table while retrieving the records from a table.

43. What is the difference between NOW() and CURRENT_DATE()?

Both NOW() and CURRENT_DATE() are built-in MySQL methods. NOW() is used to show the current date and time of the server and CURRENT_DATE() is used to show only the date of the server.

44. Which MySQL function is used to concatenate strings?

CONCAT() function is used to combine two or more string data. 

45. How can you change the name of any existing table by using the SQL statement?

The following SQL command is used to rename an existing table of the database.

RENAME TABLE table_name TO new_name;

46. How can you retrieve a portion of any column value by using a select query?

 SUBSTR() function is used to retrieve the portion of any column.

47. How can you rename and remove any column of a table?

To rename column: 

ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name column_defination.

48. What is an index?How can an index be declared in MySQL? 

An index is a data structure of a MySQL table that is used to speed up the queries. It is used by the database search engine to find out the records faster. One or more fields of a table can be used as an index key. Index key can be assigned at the time of table declaration or can be assigned after creating the table. 

  • Create INDEX:
CREATE INDEX [index name] ON [table name]([column name]);
  • List all indexes:
SHOW INDEXES FROM[table name];

49. How many columns can be used for creating an Index? 

A maximum of 16 indexed columns can be created for any standard table. 

50. What is the view? How can you create and drop views in MySQL? 

A view works as a virtual table that is used to store the query and returns a result set when it is called. An updatable view is also supported by MySQL.

CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

Update View:

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2,
FROM table_name
WHERE condition;

Drop View:

DROP VIEW view_name;

51. How can you change the password of a MySQL user?

SET PASSWORD statement is used to change the password of a MySQL user.

Syntax:

  • SET PASSWORD FOR ‘username’@’hostname’= PASSWORD(‘password’);
  • SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘123456’)

52. Explain the different types of MySQL joins.

  • Inner join: It is a default join. It returns records when the values match in joining tables.
  • Left outer join: It returns all the records from the left table based on the matched records from the right table. 
  • Right outer join: It returns all the records from the right table based on matched records from the left table. 
  • Full outer join: It returns all the records that match from the left or right table. 

53. How can you retrieve a particular number of records from a table?

A LIMIT clause is used with the SQL statement to retrieve a particular number of records from a table. From which record and how many records will be retrieved are defined by the LIMIT clause. 

54. How can you count the total number of records of any table?  

COUNT() function is used to count the total number of records of any table. 

55. What is a storage engine? What are the differences between InnoDB and MyISAM engines?

One of the major components of MySQL server is the storage engine for doing different types of database operations. Each database table created is based on the specific storage engine MySQL supports two types of storage engine i.e. transactional and non-transactional. InnoDB is the default storage engine of MySQL which is a transactional storage engine. MyISAM storage engine is a non-transactional storage engine. 

  • MyISAM supports FULLTEXT index but InnoDB doesn’t support FULLTEXT index. 
  • MyISAM is faster and InnoDB is slower. 
  • InnoDB supports ACID(Atomicity, Consistency, Isolation, and Durability) properties but MyISAM doesn’t. 
  • InnoDB supports row-level locking and MyISAM supports table-level locking.

56. How can you display the Maximum salary in SQL?

To display the maximum salary in SQL, you can use the inbuilt function called MAX().

See also  Top 100 Ansible Interview Questions And Answers

57. How to display Nth highest salary from a table in a MySQL Query?

Query:

SELECT DISTINCT(salary)FROM employee ORDER BY salary DESC LIMIT n-1,1

So if you want to find out the 2nd highest salary, consider the below query. 

SELECT DISTINCT(salary) FROM employee ORDER BY salary DESC LIMIT 1,1

58. What is SQL Query to find the maximum salary of each department? 

SELECT dept_id, MAX(salary) FROM employee GROUP BY dept_id; 

59. How do you find all Employees with their managers?(Consider there is a manager id also in Employee table)

SELECT m.emp_name as Employee, e.emp_name as Manager FROM employee e, employee m WHERE m.manager_id =e.id;

60. How to find the count of duplicate rows? 

SELECT id, COUNT(id) from employee 
Group by id 
Having COUNT(id)>1
Order by COUNT (id) desc;

61. How to remove duplicate rows from the table? 

DELETE e FROM employee e
INNER JOIN employee e2
WHERE e.id < e2.id
AND e.emp_name = e2.emp_name
AND e.dept_id = e2.dept_id;

62. In which language is MySQL written?

MySQL is written in C and C++ programming and SQL parser written in yacc.

63. How do you start MySQL on Linux? 

  • /etc/init.d/mysql start command is used to start MySQL on Linux. 

64. Explain the difference between MySQL and MySQL interfaces in PHP.

  • Mysqli is the object-oriented version of mysql library functions used in PHP. 
  • Mysql_connect()
  • Mysqli_connect()

65. What does the tee command do in MySQL?

Tee followed by a filename turns on MySQL logging to a specified file. It can be stopped by a command note. 

66. How do you change the password for an existing user in MySQLAdmin? 

Mysqladmin -u root -p password “newpassword”

67. How to use mysqldump to create a copy of a database?

Mysqldump -h mysqlhost -u username -p mydatabasename > dbdump.sql

68. What does mysqlcheck do ?   

Mysqlcheck is a client program that checks the integrity of database tables.

69. What mysql -u john -p command does? 

  • Mysql -u john -p command will prompt for the password for user john before allowing access to the database management system. 
  • If your database server requires a username and password to gain access the -u and -p command-line options. 

70. What are the technical features of MySQL? 

MySQL database software is a client or server system which includes :

  • Multi Threaded SQL server supporting various client programs and libraries.
  • Different backend 
  • Wide range of application programming interfaces
  • Administrative tools

Top MySQL Interview Questions and Answers

71. Differentiate between FLOAT and DOUBLE.

  • Floating point numbers are stored in FLOAT with eight place accuracy and it has four bytes. 
  • Floating points numbers are stored in DOUBLE with accuracy of 18 places and it has eight bytes. 

72. Differentiate CHAR_LENGTH and LENGTH?

CHAR_LENGTH is a character count whereas the Length is byte count. The numbers are the same for latin characters but they are different for unicode and other encodings.

73. How to represent ENUMs and SET internally?

ENUMs and SET are used to represent powers of two because of storage optimizations. 

74. Define REGEXP?

REGEXP is a pattern match that matches patterns anywhere in the search value. 

75. Mention string types available for columns. 

The string types are: 

  • SET
  • BLOB
  • ENUM
  • CHAR
  • TEXT
  • VARCHAR

76. What storage engines are used in MySQL? 

Storage engines are called the table types and data is stored in files using various techniques.

Technique involves:

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

77. What are the drivers in MySQL?

Following are the drivers available in MySQL:

  • PHP Driver
  • JDBC Driver 
  • ODBC Driver
  • C WRAPPER
  • PYTHON Driver
  • PERL Driver 
  • RUBY Driver
  • CAP11PHP Driver
  • Ado.net5.mxj

78. What does a TIMESTAMP do on UPDATE CURRENT_TIMESTAMP data type?

The TIMESTAMP column is updated with Zero when the table is created. The CURRENT_TIMESTAMP modifier updates the timestamp field to the current time whenever there is a change in other fields of the table. 

79. What is the difference between primary key and candidate key? 

Every row of the table is identified uniquely by the primary key. There is only one primary key for a table. 

The primary key is also a candidate key. By common convention, candidate key can be designated as primary and can be used for any foreign key references. 

80. What does myisamchk do? 

It compresses the MyISAM tables, which reduces their disk or memory usage. 

Top MySQL Interview Questions and Answers

81. How do you control the maximum size of a HEAP table?

Maximum size of the HEAP table can be controlled by the MySQL config variable called max_heap_table_size.

82. What is the difference between MyISAM Static and MyISAM Dynamic?

In MyISAM Static all the fields will have fixed width. The Dynamic MyISAM table will have fields like TEXT, BLOB, etc. to accommodate the data types with various lengths. 

MyISAM Static would be easier to restore in case of corruption. 

83. What are federated tables? 

Federated tables are the ones which allow access to the tables located on other databases on other servers. 

84. What if a table has one column defined as TIMESTAMP? 

Timestamp field gets the current TIMESTAMP whenever the row gets altered.

85. What happens when the column is set to AUTO INCREMENT and if you reach maximum value in the table? 

It stops incrementing. Any further inserts are going to produce an error, since the key has been used already. 

86. How can we find out which auto increment was assigned on Last insert? 

LAST_INSERT_ID will return the last value assigned by Auto_increment and it is not required to specify the table name.  

87. How can you see all indexes defined for a table?

Indexes are defined for the table by: 

SHOW INDEX FROM <tablename> ;

88. What do you mean by % and _in the LIKE statement? 

% corresponds to 0 or more characters, _ is exactly one character in the LIKE statement. 

89. How can we convert between Unix & MySQL timestamps? 

  • UNIX_TIMESTAMP is the command which converts from MySQL timestamp to Unix timestamp 
  • FROM_UNIXTIME is the command which converts from Unix timestamp to MySQL timestamp.

Top MySQL Interview Questions and Answers

90. What are the column comparisons operators?

The = , <> , <=, <, >=, >, <<,>>, <=> , AND, OR, or LIKE operators are used in column comparisons in SELECT statements. 

91. How can we get the number of rows affected by query?  

Number of rows can be obtained by SELECT COUNT (user_id)  FROM users;

92. Is MySQL query case sensitive?

No, it’s not. 

  • SELECT VERSION(), CURRENT_DATE;
  • seleCt vErsion(), current_DATE;
  • SeLect version(), current_date;

All these examples are the same. It is not case sensitive. 

93. What is the difference between the LIKE and REGEXP operators? 

LIKE and REGEXP operators are used to express ^ and %.

  • SELECT * FROM employee WHERE emp_name REGEXP “^b”;
  • SELECT * FROM employee WHERE emp_name LIKE “%b”.

94. What is the difference between mysql_fetch_array and mysql_fetch_object? 

Following are the differences between mysql_fetch_array and mysql_fetch_object: 

  • mysql_fetc_array() – Returns a result row as an associated array or a regular array from the database. 
  • Mysql_fetch_object – Returns as a result row as object from database. 

95. How can we run batch mode in MySQL?

Following commands are used to run in batch mode: 

  • Mysql;
  • Mysql mysql.out

96. Where will MyISAM tables be stored and also give their formats of storage? 

Each MyISAM table is stored on disk in three formats: 

  • The ‘.frm’ file stores the table definition
  • The data file has a ‘.MYD’ (MYData) extension
  • The index file has a ‘.MYI’ (MYIndex) extension 

97. What is ISAM? 

ISAM is abbreviated as Indexed Sequential Access Method. It was developed by IBM to store and retrieve data on secondary storage systems like tapes.  

98. What is InnoDB?

InnoDB is a transaction-safe storage engine developed by innobase, which is an Oracle Corporation now. 

99. How MySQL Optimizes DISTINCT? 

DISTINCT is converted to a GROUP BY on all columns and it will be combined with the ORDER BY clause. 

  • SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;

100. How to enter characters as HEX numbers? 

If you want to enter characters as HEX numbers, you can enter HEX numbers with single quotes and a prefix of (X), or just prefix HEX numbers with (OX).

A HEX number string will be automatically converted into a character string if the expression context is a string. 

These top MySQL interview questions are helpful in Tech or coding interviews as SQL is a programming language designed especially for managing data in the Relational Database Management System. These interview questions and answers are selected on the basis so that the candidate can clear their interview on the first attempt.