These MySQL questions have been designed for various interviews, competitive exams and entrance tests. We have covered questions on both basic and advanced concepts which will help you improve your skills to face interview questions on MySQL.
Who is this MySQL interview questions designed for?
All the Database Developers, DBA, Tester, Web developers, Application Developers, Programmers and Software engineer will find these questions extremely useful. All freshers, BCA, BE, BTech, MCA and college students wanting to make a career in front end designing will be highly benefitted by these questions.
MySQL interview questions topics
This section covers MySQL topics like - Reasons to Use MySQL, different tables present in Mysql, MySQL Queries, MySQL Joins, MySQL TRIGGERS etc.
1. What is MySQL? In which language MySQL is written?
MySQL is an Oracle-backed open source RDBMS, based on Structured Query Language (SQL). It is often accessed using PHP to create powerful and dynamic server-side applications. It is pretty easy to master in compare to Oracle Database, or Microsoft SQL Server.
MySQL is written in C, C++.
Video : MySQL Interview Questions and Answers
2. Why should we use MySQL?
Top Reasons to Use MySQL:
The MySQL database server is very fast, reliable and easy to use.
It is open source and thus is free to use for developers and a small fee for enterprises.
It offers a solid data security layer that protects sensitive data from intruders.
It is scalable and can handle huge amount of data.
It is compatible with virtually every operating system.
It is very friendly to PHP, the most appreciated language for web development.
It has large and extensive community support that can help you manage your database.
3. What are the different tables present in Mysql?
There are mainly five types of tables present in MySQL.
MyISAM - It is the default database engine used in MySQL and is based on the sequential access method.
Heap - It is the table that is used for fast data access, but the data will be lost if the table or the system crashes.
Merge - Merge table type is added to treat multiple MyISAM tables as a single table so it removes the size limitation from MyISAM tables.
INNO DB - It is the table that supports transactions using the COMMIT and ROLLBACK commands.
ISAM - ISAM had been deprecated and removed from version 5.x. All of its functionality is replaced by MyISAM.
4. In Which Order MySQL executes Queries?
Each part of the SQL SELECT statement is executed sequentially, it's important to understand the order of execution as it can save us from unwanted results, and help us create queries that execute faster.
The order of SQL SELECT statement is as follows:
1. FROM and JOINs - Selects and joins your tables to base data.
2. WHERE - Filters the base data
3. GROUP BY - Aggregates the base data
4. HAVING - Filters the aggregated data
5. SELECT - Returns the final data
6. ORDER BY - Sorts the final data
7. LIMIT - Limits the returned data to a row count
5. What are MySQL Joins and explain different types of MySQL Joins?
Joins help retrieving data from two or more database tables using some interconnected common fields or keys among the selected tables. There are mainly three types joins present in MySQL.
a. Inner Join: It fetches the list of rows from specified tables that satisfy the given condition. It is the default join type.
Syntax:
SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
b. Left Join: It returns all rows from the left table even if there is no match in the right table.
Syntax:
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
c. Right Join: It returns all rows from the right table even if there is no match in the left table.
Syntax:
SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
6. How can you increase the performance of MySQL SELECT query?
Following are some techniques of query optimization:
Use indexes. You can significantly increase performance by using indexes.
Dont use "*" while you are using SELECT command. Also avoid unnecessary columns in SELECT clause.
DISTINCT and UNION need to be used solely if it is necessary.
You can use short table aliases while writing queries. A short alias is parsed more quickly than a long table name or alias.
Avoid the usage of the wildcard (%) at the start of a LIKE pattern. MySQL doesn't use an index for such a query./li>
7. How many TRIGGERS are allowed in MySql table?
MySQL supports triggers that are invoked automatically in response to the INSERT, UPDATE or DELETE event occurring in a table.
SIX triggers are allowed in MySql table. They are as follows:
BEFORE INSERT - It is invoked before an insert, or before an insert statement is executed.
AFTER INSERT - It gets invoked after an insert is implemented.
BEFORE UPDATE - It is invoked before the update is implemented.
AFTER UPDATE - It is invoked after an updation occurs.
BEFORE DELETE - It is invoked before a delete occurs, or before deletion statement is implemented.
AFTER DELETE - It is invoked after a delete occurs, or after a delete operation is implemented.
8. What are the different set operations available in MySQL?
The various set operations available in MySQL are as follows:
UNION – Combines the results from multiple SELECT queries and returns all the distinct rows. UNION doesn’t work with a column that has Text Data Type
UNION ALL – Returns all the rows from all the tables which meet the conditions of your specifics query. It allows duplicate values.
MINUS – Returns all the distinct rows selected by the first query but does not select the rows selected by the second query.
INTERSECT – Returns the intersection of both queries. If a record exists in both data sets, it will be included in the INTERSECT results.
9. How can you test for NULL values in a database?
A field with a NULL value is a field with no value. The conditions involving NULL are special. You cannot use = NULL or != NULL to look for NULL values in columns. To compare the fields with NULL values, you have to use the IS NULL and IS NOT NULL operator.
Syntax of IS NULL:
SELECT column_names FROM table_name WHERE column_name IS NULL;
Syntax of IS Not NULL:
SELECT column_names FROM table_name WHERE column_name IS NOT NULL;
10. Can you elaborate on BLOB and TEXT in MySQL?
BLOB stands for Binary Large Objects. It can be used to store binary data that means we can store pictures, videos, sounds etc.
The following are the four types of BLOB
TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB
TEXT is non-binary, character based string data type and is used for storing large number of strings. Normally a blog or news article would constitute to a TEXT field.
The following are the four types of TEXT
TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT
11. What is the difference between TRUNCATE and DELETE in MySQL?
DELETE operation deletes the data of a table but we can perform Commit and Rollback to retrieve data. 'Where' condition can be used along with a delete statement.
TRUNCATE is used to delete the data of a table permanently. You can't perform Commit and Rollback statement with TRUNCATE. Also, 'Where' condition can't be used along with TRUNCATE statement.
12. What is the difference between CHAR and VARCHAR?
Both CHAR and VARCHAR data types are used to store string data in the field of the table.
The differences between these data types are mentioned below:
CHAR stores the data or values in fixed length format. If the size of the string is smaller than the specified length, it is padded with space characters with space characters to match the specified length. On the other hand, VARCHAR stores values in variable lengths. Values are not padded with any characters. But 1 or 2 extra bytes are added to store the length of the data.
CHAR is used to store small data whereas VARCHAR is used to store large data.
