MySQL Interview Questions Preparation Course
- 1.5 hours on-demand video
- 2 articles
- Full lifetime access
- Access on mobile and TV
- Certificate of Completion
Get your team access to 4,000+ top Udemy courses anytime, anywhere.Try Udemy for Business
- Understand MySQL
- Learn important concepts of MySQL
- Answer interview questions on MySQL
- Demand higher salary or promotion based on the knowledge gained!!
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:
We can use following query in MySQL to get the current time as well as date:
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.
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:
Q. What are the objects that can be created by CREATE statement in MySQL?
We can create following objects by CREATE statement in MySQL:
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:
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.
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)"
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. 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.
- Basic MySQL experience
- Familiar with MySQL
MySQL is one of the most popular and useful technology in Data Science and Software Development world. Big companies like Amazon, Netflix, Google etc use MySQL. This course is designed to help you achieve your goals in Software Development and Data Science field. Data Engineer and Software Engineers with MySQL knowledge may get more salary than others with similar qualifications without MySQL knowledge.
In this course, you will learn how to handle interview questions on MySQL in Software Development. I will explain you the important concepts of MySQL.
You will also learn the benefits and use cases of MySQL in this course.
What is the biggest benefit of this course to me?
Finally, the biggest benefit of this course is that you will be able to demand higher salary in your next job interview.
It is good to learn MySQL for theoretical benefits. But if you do not know how to handle interview questions on MySQL, you can not convert your MySQL knowledge into higher salary.
What are the topics covered in this course?
We cover a wide range of topics in this course. We have questions on MySQL, MySQL queries, MySQL deep concepts and MySQL tricky questions etc.
How will this course help me?
By attending this course, you do not have to spend time searching the Internet for MySQL interview questions. We have already compiled the list of most popular and latest MySQL Interview questions.
Are there answers in this course?
Yes, in this course each question is followed by an answer. So you can save time in interview preparation.
What is the best way of viewing this course?
You have to just watch the course from beginning to end. Once you go through all the videos, try to answer the questions in your own words. Also mark the questions that you could not answer by yourself. Then, in second pass go through only the difficult questions. After going through this course 2-3 times, you will be well prepared to face a technical interview in MySQL field.
What is the level of questions in this course?
This course contains questions that are good for a Fresher to an Architect level. The difficulty level of question varies in the course from a Fresher to an Experienced professional.
What happens if MySQL concepts change in future?
From time to time, we keep adding more questions to this course. Our aim is to keep you always updated with the latest interview questions on MySQL.
What are the sample questions covered in this course?
Sample questions covered in this course are as follows:
- How will you calculate the number of days between two dates in MySQL?
- What are the different types of Triggers in MySQL?
- What are the differences between Heap table and temporary table in MySQL?
- What is a Heap table in MySQL?
- What is the difference between BLOB and TEXT data type in MySQL?
- What will happen when AUTO_INVREMENT on an INTEGER column reaches MAX_VALUE in MySQL?
- What are the advantages of MySQL as compared with Oracle DB?
- What are the disadvantages of MySQL?
- What is the difference between CHAR and VARCHAR datatype in MySQL?
- What is the use of 'i_am_a_dummy flag' in MySQL?
- How can we get current date and time in MySQL?
- What is the difference between timestamp in Unix and MySQL?
- How will you limit a MySQL query to display only top 10 rows?
- What is automatic initialization and updating for TIMESTAMP in a MySQL table?
- How can we get the list of all the indexes on a table?
- What is SAVEPOINT in MySQL?
- 17. What is the difference between ROLLBACK TO SAVEPOINT and RELEASE SAVEPOINT?
- How will you search for a String in MySQL column?
- How can we find the version of the MySQL server and the name of the current database by SELECT query?
- What is the use of IFNULL() operator in MySQL?
- How will you check if a table exists in MySQL?
- How will you see the structure of a table in MySQL?
- What are the objects that can be created by CREATE statement in MySQL?
- 24. How will you see the current user logged into MySQL connection?
- How can you copy the structure of a table into another table without copying the data?
- What is the difference between Batch and Interactive modes of MySQL?
- How can we get a random number between 1 and 100 in MySQL?
- What is the difference between Primary key and Candidate key?
- What is a Federated table in MySQL?
- How will you get the comma separated values for a column in a MySQL Group query?
- How can we get the results from a table in a random order in MySQL?
- How can we prevent duplicate records in a MySQL table?
- How will you eliminate duplicate values from a query result in MySQL?
- How will you get the size of data in a MySQL table?
- What is the option in Amazon Web Services for MySQL?
- How can we manage multiple MySQL servers?
- How will you migrate your SQL Server database to MySQL?
- What is the difference between MySQL and Oracle?
- What is the order of privileges in MySQL?
- Is it ok to mention a table name in lower case while creation and in uppercase while retrieving data from MySQL DB?
- Why this query does not work in MySQL?
- What is mysqldump?
- What is the limitation of mysqldump?
- What are user-defined variables in MySQL?
- How will you optimize a SELECT query in MySQL?
- What are the different optimization techniques in MySQL?
- How can we optimize INSERT query in MySQL?
- How will you make MySQL system secure against attackers?
- How can you protect your MySQL server against Denial of Service attacks?
- Absolute beginner in MySQL
- Anyone who wants to appear in MySQL interview
- Software Engineer, Sr. Software Engineer, Member Technical Staff, Expert
- Software Architect, Development Manager, Director
- Anyone who wants to learn MySQL