Top 50 MySQL Interview Questions and Answers

In this article, we’ve covered 50 Basic MySQL Questions, again we are here with another set interview questions for intermediate users. As we said earlier these questions can be asked in Job Interviews.

MySQL is a multi-threaded or multi-user database management system having more than 11 million installations. It is the world’s second-largest most popular and widely used open-source database.

Using MySQL is free of cost for developers, but enterprises have to buy the licensed version. It is supported by the Oracle and based on Structured Query Language. It supports a wide range of operating systems like Windows, Linux, and Unix, etc.

MySQL Interview Questions and Answers for Intermediates

1. What is MySQL?

MySQL is an open-source DBMS which is built, supported and distributed by MySQL AB (now acquired by Oracle).

SQL stands for Structured Query Language. SQL is a programming language designed especially for managing data in Relational Database Management System (RDBMS).

2. 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.
• It includes different backends.
• Administrative tools.
• It includes a wide range of programming interfaces.

3. What is RDBMS? Explain its features?

A Relational Database Management System (RDBMS) is the most widely used database Management System based on the Relational Database model.

Features of RDBMS:-

• Stores data in tables.
• Creation and Retrieval of Table is allowed through SQL.
• Tables have rows and column.

4. What is Data Mining?

Data Mining is a subcategory of Computer Science which aims at extraction of information from set of data and transform it into Human Readable structure, to be used later.

5. In which language MySQL is written?

MySQL is written in C, and C++ language, and its SQL parser are written in YACC language.

6. What are Heap tables?

These are available in memory for high-speed storage temporarily. It does not allow BLOB or text fields; only comparison operators are allowed. Heap tables and indexes do not support an auto-increment function and should be NOT NULL.

7. What is the difference between Primary Key and Unique Key?

Both Primary and Unique Key is implemented for the Uniqueness of the column. Primary Key creates a clustered index of column whereas a Unique creates an unclustered index of the column. Moreover, Primary Key doesn’t allow NULL value, however, Unique Key does allow one NULL value.

8. Differentiate between FLOAT and DOUBLE?

Following are differences for FLOAT and DOUBLE:-

• To store float numbers, the FLOAT keyword is used with four byes and eight bits accuracy.
• To store float numbers, the DOUBLE keyword is used with eight bytes and 18 bits accuracy.

9. What do you understand by the term ISAM in MySQL?

ISAM means Indexed Sequential Access Method. It is used to store and retrieve data on secondary devices like tapes.

10. What are the difference between and MyISAM and InnoDB?

These are most commonly used storage engine in MySQL are MyISAM and InnoDB.

11. Differentiate CHAR_LENGTH and LENGTH?

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

12. Is MySQL case-sensitive?

Mysql is not case sensitive.

13. Explain the difference between primary key and candidate key in Mysql?

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

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

14. What are indexes in a Database. What are the types of indexes?

Indexes are the quick references for fast data retrieval of data from a database. There are two different kinds of indexes.

Clustered Index

1. Only one per table.
2. Faster to read than non clustered as data is physically stored in index order.

Non­clustered Index

1. Can be used many times per table.
2. Quicker for insert and update operations than a clustered index.

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

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

16. How many TRIGGERS are possible in MySql?

There are only six triggers are allowed to use in MySQL database and they are.

1. Before Insert
2. After Insert
3. Before Update
4. After Update
5. Before Delete
6. After Delete

17. What is the difference between delete, drop and truncate?

Delete

• It removes Some or All rows from a table.
• A WHERE clause is used to remove particular rows according to the matched condition. All rows will be deleted when we did not use Where condition in Query.
• It removes rows one by one at a time.
• It can be used with indexed views.

Drop

• It removes a table from the database.
• All table's rows, indexes, and privileges will also be removed when we used this command.
• The operation cannot be rolled back.

Truncate

• It removes all rows from a table.
• It does not require a WHERE clause.
• Truncate cannot be used with indexed views.
• It is performance-wise faster.

18. How to get the current MySQL version?

Here is the command to get the current MySQL version:

SELECT VERSION ();

19. What is MYSQL function?

MySQL has many built-in functions. This reference contains a string, numeric, date, and some advanced functions in MySQL.

MySQL String Functions

There are so many MySQL string functions. Mostly used functions are:-

• LOWER Converts a string to lower-case
• UPPER Converts a string to upper-case
• SUBSTR Extracts a substring from a string (starting at any position)
• STRCMP Compares two strings
• SUBSTRING Extracts a substring from a string (starting at any position)
• LENGTH returns the length of a string (in bytes)

MySQL Numeric Functions

There are so many MySQL numeric functions. Mostly used functions are:-

• DIV Used for integer division
• COUNT Returns the number of records returned by a select query
• AVG Returns the average value of an expression
• ABS Returns the absolute value of a number
• LEAST Returns the smallest value of the list of arguments
• GREATEST Returns the greatest value of the list of arguments
• POWER Returns the value of a number raised to the power of another number

MySQL Date Functions

There are so many MySQL date functions. Mostly used functions are:-

• DATE Extracts the date part from a datetime expression
• DATEDIFF Returns the number of days between two date values
• DAY Returns the day of the month for a given date
• NOW Returns the current date and time
• TIME Extracts the time part from a given time/datetime
• YEAR Returns the year part for a given date

20. What do you mean by % and _ in the LIKE statement?

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

21. How to find which auto increment operation was applied to the Last Insert?

This operator returns the last value assigned by the auto-increment operator and there is no need to specify the table name.

LAST INSERT_ID

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

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

SELECT * FROM employee WHERE emp_name REGEXP "^b";
SELECT * FROM employee WHERE emp_name LIKE "%b";

23. How to convert a string to utf 8 in MySQL?

Here’s a code script to convert string data to UTF 8 in your database:

header('Content-Type: text/plain; charset=utf-8');
$databasesToConvert = [ 'database1' */ ];
$typesToConvert = [ 'char', 'varchar', 'tinytext', 'mediumtext', 'text', 'longtext'];
CONVERT(CAST(CONVERT(`{$column}` USING english) AS binary) USING utf8)";
$query = "UPDATE `{$table}` SET " . join( ', ', $converts );
echo "\n", $query, "\n";

24. How to get the total number of rows affected by a query?

The total number of rows affected by a query can be given as:

SELECT COUNT (user_id) FROM users;

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

NOW () command is used to show the current year, month, date with hours, minutes, and seconds.

CURRENT_DATE() shows current year,month and date only.

26. What are the comparison operators that can be used for a column in MySQL?

These are AND, OR LIKE operators to use with SELECT statement.

27. What are the nonstandard string types?

Following are Non-Standard string types:

• TINY TEXT
• TEXT
• MEDIUM TEXT
• LONGTEXT

28. How to compare mysql_fetch_array and mysql_fetch_object?

Here are the notable differences among mysql_fetch_array and mysql_fetch_object:

• mysql_fetch_array returns a result row as a regular array or associated array from the database.
• mysql_fetch_object returns a result row as an object from the database.

29. How to display top 50 rows?

In MySql, top 50 rows are displayed by using this following query:-

SELECT * FROM LIMIT 0,50;

30. How MySQL Optimizes DISTINCT?

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

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

31. What are the different tables present in MySQL?

Total 5 types of tables are present:

• MyISAM
• Heap
• Merge
• INNO DB
• ISAM

32. How many columns can be used to create indexes?

A total of 16 indexed columns can be used to create indexes.

33. What will happen when a column is set to auto increment and reaches its maximum value?

It will stop incrementing and produces errors for further inserts.

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

In MyISAM static, all the fields will have a 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.

35. How to store binary data in MySQL?

Binary data can be stored in Mysql in different ways such as

• The short length of binary data can be stored in the form of string as a VARCHAR
• A BLOB data type allows users to store an arbitrary amount of binary data.

36. How to make a copy values from one column to another in Mysql?

This is easly possible by this syntax:-

UPDATE `your_table_name` SET new_field=old_field

37. 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

38. What storage engines are used in MySQL?

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

Technique involves:

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

39. What is an Access Control List in MySQL?

An Access Control List (ACL) is a set of permission associated with an object. It is the basis of the server’s security model and helps in troubleshooting problems like users are not able to connect, not able to access data, etc.

40. What do you understand by DDL, DCL, and DML in MySQL?

DDL

In MySQL, DDL is the short form for Data Definition Language, which is used in database schemas and descriptions while deciding how data should reside in the database.

Here’s a list of DDL Queries:

• CREATE
• ALTER
• DROP
• TRUNCATE
• COMMENT
• RENAME

DCL

DCL means Data Control Language that includes commands like GRANT, REVOKE, etc.

Here’s a list of queries for DCL:

• GRANT
• REVOKE

DML

DML means Data Manipulation Language that includes commands like SELECT, INSERT, etc.

Here is the list of DML Queries:

• SELECT
• INSERT
• UPDATE
• DELETE
• MERGE
• CALL
• EXPLAIN PLAN
• LOCK TABLE

41. How will you define joins in MySQL?

MySQL uses joins to query data from two or more tables. There is a total of four types of joins in MySQL. These are Inner join, Full join, Right join, and left join. Inner join return rows even if there is one single match in both the tables.

There are three types of MySQL joins:-

MySQL INNER JOIN

SELECT columns FROM table_1 INNER JOIN table_2 ON table_1.column = table_2.column;

MySQL LEFT JOIN

SELECT columns FROM table_1 LEFT JOIN table_2 ON table_1.column = table_2.column;

MySQL RIGHT JOIN

SELECT columns FROM table_1 RIGHT JOIN table_2 ON table_1.column = table_2.column;

42. How to compare transaction and ACID properties?

A transaction is a logical unit of work where all or none of the steps are performed. ACID means Atomicity, Consistency, Isolation, and Durability that can be defined as properties of a transaction.

43. How to create view in MySQL?

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

CREATE VIEW Syntax:-

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

44. How to create Index in MySQL?

The CREATE INDEX statement is used to create indexes in tables.

Indexes are used to retrieve data from the database more quickly than otherwise. The users cannot see the indexes, they are just used to speed up searches/queries.

CREATE INDEX Syntax

CREATE INDEX index_name
ON table_name (column1, column2, ...);

45. What is the use of CONCAT() in Mysql?

It is used to concatenate two or more strings.

SELECT CONCAT('BestInterview', ' ', 'Question') AS 'Name';

46. How to write optimized query in MySQL?

The optimized query can be written with the use of the following clause- ‘where’, ‘order-by’ and group-by’. Apart from theses clause like statements with union clause is also used for the optimization of the queries.

47. What are the advantages of Mysql?

• Data Security
• It is used to retrieve large amounts of records from a database quickly and efficiently.
• On-Demand Scalability
• High Performance
• Excellent Uptime
• Reduced Total Cost of Ownership

48. In Mysql, what is the default storage engine?

InnoDB

49. List data types in Mysql? Explain

Mysql supports various data types. Some most common used data types are given below:-

• CHAR
• VARCHAR
• TINYTEXT
• TEXT
• INT
• FLOAT
• DATE
• DATETIME
• TIMESTAMP
• TIME
• ENUM
• DECIMAL
• TINYINT
• LONGTEXT etc

50. Explain the difference between procedure and function in MySQL?

Functions are generally used for computations, but procedures are usually used for executing business logic.

The function returns only 1 value, but the procedure can return multiple values.

The function can be called directly by SQL statement like select functionName from dual, but procedures cannot be called directly.

Conclusion

In this article, we’ve covered 50 Basic MySQL Questions, again we are here with another set interview questions for intermediate users. As we said earlier these questions can be asked in Job Interviews.