MySQL Interview Questions Preparation Course

Q.    What are the differences between Heap table and temporary table in MySQL?

• I.         Duration: Heap tables are stored in memory. Therefore a Heap table remains in existence even if the session is disconnected. When we restart Database, Heap tables get cleaned up.
• II.         Temporary tables are valid only during a session. Once the session is disconnected, temporary table is cleaned up. 
• III.         Privilege: We need special privilege to create a Temporary table. Heap tables are just another form of storage in MySQL.
• IV.         Sharing: Temporary tables are not shared between clients. Each connection will have a unique temporary table. But Heap tables can be shared between clients.

Q.    What is a Heap table in MySQL?

In MySQL there are tables that are present in memory. These are called Heap tables. During table creation we specify TYPE as HEAP for HEAP tables. 

Heap tables provide very fast access to data.

We can not store BLOB or TEXT datatype in a HEAP table. These tables also do not support AUTO_INCREMENT.

Once we restart the Database, data in HEAP tables is lost.

Q.    What is the difference between BLOB and TEXT data type in MySQL?

BLOB is a Binary large Object. We can store a large amount of binary data in a BLOB data type column. TEXT is non-binary, character based string data type. We can store text data in TEXT column. We have to define a character set with a TEXT column. TEXT can be easily converted into plain text.

BLOB has four types: TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB. Where as, TEXT has its own four types: TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT.

Q.   What will happen when AUTO_INCREMENT on an INTEGER column reaches MAX_VALUE in MySQL?

Once a column reaches the MAX_VALUE, the AUTO_INCREMENT stops working. It gives following error in log:

 ERROR: 1467 (HY000): Failed to read auto-increment value from storage engine

Q.    What are the disadvantages of MySQL?

Some of the main disadvantages of MySQL are as follows:

Dependent on Additional S/W: MySQL has less number of features in standard out-of-box version. So we have to add additional software to get more features. It gets difficult to find, decide and use the additional software with MySQL.

SQL Compliance: MySQL is not full SQL compliant. Due to this developers find it difficult to cope with the syntax of SQL in MySQL.

Transaction handling: Some users complain that DB transactions are not handled properly in MySQL.

Q.    What is the use of 'i_am_a_dummy flag' in MySQL?

In MySQL, there is falg "ia_am_a_dummy" that can be used to save beginner developers from erroneous query like 'DELETE FROM table_name'. If we run this query it will delete all the data from table names table_name. 

With 'i_am_a_dummy flag', MySQL will not permit running such a query. It will prompt user to create a query with WHERE clause so that only specific data is deleted. 

We can achieve similar functionality with 'safe_updates' option in MySQL.

This flag also works on UPDATE statement to restrict updates on a table without WHERE clause.

Q.    How can we get current date and time in MySQL?

We can use following query in MySQL to get the current date:

SELECT CURRENT_DATE();  

We can use following query in MySQL to get the current time as well as date:

SELECT NOW();

Q.   What is the difference between timestamp in Unix and MySQL?

In Unix as well as in MySQL, timestamp is stored as a 32-bit integer. 

A timestamp is the number of seconds from the Unix Epoch on January 1st, 1970 at UTC.

In MySQL we can represent the timestamp in a readable format. 

Timestamp format in MySQL is YYYY-MM-DD HH:MM:SS

Q.    How can we get the list of all the indexes on a table?

We can use following command to get the list of all the indexes on a table in MySQL:

SHOW INDEX FROM table_name;

At maximum we can use 16 columns in a multi-column index of table.

Q.   What is SAVEPOINT in MySQL?

SAVEPOINT is a statement in SQL. We can use SAVEPOINT <savepoint_name> statement to create a point of time in a Database transaction with a name. Later we can use this savepoint to rollback the transaction upto that point of time.

Q.    How can we find the version of the MySQL server and the name of the current database by SELECT query?

We can use built in functions VERSION() and DATABASE() in MySQL to get the version of MySQL server and the name of database in MySQL.

Query is as follows:

SELECT VERSION(), DATABASE();

Q.   What is the use of IFNULL() operator in MySQL?

We use IFNULL operator in MySQL to get a non-null value for a column with null value.

IFNULL(expr1, expr2) 

If expr1 is not null then expr1 is returned. If expr1 is null then expr2 is returned.

Eg. SELECT name, IFNULL(id,'Unknown') AS 'id' FROM user;

If id is not null then id is returned. If id is null then Unknown is returned.

Q.    How will you check if a table exists in MySQL? 

We can use CHECK TABLE query to see the existence of a table in MySQL.

Query is as follows:

CHECK TABLE <table_name>;

Q.   How will you see the structure of a table in MySQL? 

We can use DESC query to see the structure of a table in MySQL. It will return the name of columns and their datatype in a table.

Query is as follows:

DESC <table_name>;

Q.    What are the objects that can be created by CREATE statement in MySQL?

We can create following objects by CREATE statement in MySQL:

DATABASE

USER

TABLE

INDEX 

VIEW

TRIGGER

EVENT

FUNCTION

PROCEDURE

Q.   How will you see the current user logged into MySQL connection?

We can use USER() command to get the user logged into MySQL connection. 

Command is as follows:

SELECT USER();

Q.    How can you copy the structure of a table into another table without copying the data?

It is a trick question. But it has practical use in day to day work.

Query for this is as follows:

CREATE TABLE table_name AS SELECT * FROM USER WHERE 1 > 2;

In this example condition in WHERE clause will be always false. Due to this no data is retrieved by SELECT query.

Q.   What is the difference between Batch and Interactive modes of MySQL?

In Interactive mode, we use command line interface and enter queries one by one. MySQL will execute the query and return the result in command line interface. 

In Batch mode of MySQL we can write all the queries in a SQL file. Then we can run this SQL file from MySQL command line or from Scheduler Job. MySQL will execute all the queries and return the result.

Q.    How can we get a random number between 1 and 100 in MySQL?

In MySQL we have a RAND() function that returns a random number between 0 and 1.

SELECT RAND();

If we want to get a random number between 1 and 100, we can use following query:

SELECT RAND() * 100;

Q.   What is the difference between Primary key and Candidate key?

We use Primary key to uniquely identify a row in a table. There is only one Primary key in a table.

There can be other keys in a table that uniquely identify a row. But they all may not be labelled as a Primary key.

All the keys that are candidate for a primary key are called Candidate key.

Q.    How will you eliminate duplicate values from a query result in MySQL?

MySQL provides DISTINCT keyword to remove duplicates from a query result. With DISTINCT keyword, a value will be displayed only once.

Eg. SELECT DISTINCT NAME FROM EMPLOYEE;

This query will return each name in Employee table only once.

Q.   How will you get the size of data in a MySQL table?

In MySQL, there is an information_schema that contains the TABLES object. We can run a query on TABLES to get the size of data in a table.

Eg. If we have a table named EMPLOYEE, the query to get the size of data will be as follows:

SELECT table_name "Table Name",sum( data_length + index_length ) / 1024 / 1024 "Table Size (MB)"

FROM information_schema.TABLES  

WHERE table_name='EMPLOYEE';

Q.    What is the option in Amazon Web Services for MySQL?

Amazon Web Services provides a MySQL compatible database product named Aurora. 

Amazon Aurora with MySQL is claimed to be five time faster performance than regular MySQL. 

It is a high end commercial grade relational database based on MySQL. It is very cost effective as compared to other enterprise databases.

Q.     How can we manage multiple MySQL servers?

MySQL provides MySQL Enterprise Monitoring and Advisory Service to manage multiple servers. We can monitor health, security, availability and performance of multiple MySQL servers from a consolidated dashboard with this service.

It also shows color coded charts that help in finding and resolving issues quickly.

Q.    Why this query does not work in MySQL? 

mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;

Unlike other databases, MySQL is case-sensitive in most of the platforms. In the above query we are using my_table and MY_TABLE to refer same table. This is not allowed in MySQL.

Q.   What is mysqldump?

MySQL provides a data backup utility called mysqldump. We can use it for creating logical backup of a database. Logical backup is a set of SQL statements that can be executed to recreate the database with original object definitions and table data.

mysqldump can also be used to create database dump in csv, xml, json etc formats. 

Q.    What is the limitation of mysqldump?

Since mysqldump is a logical backup it is not useful for taking backup of a database with large tables. mysqldump loads an entire table's data in memory before writing it to dump. Since memory is a limited resource, it is not a scalable option for dumping large tables.

In such a scenario, it is preferable to take physical backups of data.

Q.   Can we run Javascript or Python code in MySQL?

MySQL provides a tool called mysqlsh to run Javascript or Python code in a MySQL shell. We can use it to execute the code from Javascript and Python.

On calling mysqlsh, a new shell is created within regular shell. In the new shell we can write and run Javascript/Python code.

Q.    What are user-defined variables in MySQL?

In MySQL we can create user-defined variables. We can store a value in a user-defined variable in one statement and later use it in subsequent statements. 

User-defined variables are specific to a session. A user-defined variable can not be shared among multiple clients.

The name of user-defined variable can have maximum 64 characters and it is case-insensitive.

Q.    What are the different optimization techniques in MySQL?

Some of the optimization techniques in MySQL are as follows:

• Range Optimization: In this case we can use a single index to retrieve the subset of data from a table. Now the server has to work on less amount of data.
• Index Optimization: In this method several range scans can be merged into one result. So index scans from same table can be merged into one dataset.
• Engine Condition Pushdown Optimization: When we have a direct comparison between a non-indexed column and constant, MySQL optimizer will push the condition to storage engine for evaluation. 

• Index Condition Pushdown Optimization: This is another optimization for selecting rows from a table by using an index.

Q.    How can we optimize INSERT query in MySQL?

We can use following techniques to optimize INSERT queries in MySQL:

• I.         Values: For inserting multiple rows in a table we can pass multiple VALUES as a list in an INSERT statement. It is faster than using single row INSERT per statement.
• II.         Default: If we have default value set on a column then we should not try to insert default value. We can just insert data with non-default value.
• III.         LOAD_DATA_INFILE: We can use LOAD_DATA_INFILE to insert data from a text file into MySQL. 

Q.    How can you protect your MySQL server against Denial of Service attacks?

In a Denial of Service attack, a malicious user may load the server with so many unwanted requests that the system becomes very slow and almost unusable to most of the other genuine users.

We can use following techniques to protect our MySQL server against any Denial of Service attacks:

• We should modify dynamic URLs with %22 ("), %23 (#) etc.
• We should modify data type in dynamic URLs from number to character format.
• We should check the size of data before passing it to MySQL. If there is larger amount of data than the average size, it should raise red flag.
• We should use different users for application connectivity to database than the administrative user.
• We can also enable strict sql mode to enable system to be more restrictive of the values it accepts.