// 17 Essential MySQL Interview Questions
MySQL is a popular open-source relational database management system that is widely used in web applications. It is known for its reliability, scalability, and ease of use. As MySQL is a key component of many web applications, it has become a popular topic in interviews for web developers.
In this blog post, we will cover some of the most commonly asked MySQL interview questions and provide you with tips on how to answer them effectively. Whether you're a beginner or an experienced MySQL developer, this guide will help you prepare for your next MySQL interview.
Looking for Freelance MySQL Developers? Build your product with Flexiple's top-quality talent.
Hire a MySQL DeveloperHire NowA few noteworthy advantages of using MySQL are
- High Performance - MySQL was built with a lot of focus on power/ performance
- Flexibility - MySQL can be used on all operating systems
- Security - Good security and storage management system
- Query Caching - Increases the speed of MySQL drastically
- Replication - MySQL servers can be very easily replicated
Being a database management system, MySQL expands support to various numeric data types; the following list shows the same.
- TINYINT - Used for very small integers values (1 Byte)
- SMALLINT - Used for small integers (2 Bytes)
- MEDIUMINT - Stores Medium-Sized integers (3 Bytes)
- INT - Standard Integers values (4 Bytes)
- BIGINT - Larger Integers values (8 Bytes)
- DECIMAL - Stores fixed-point decimal values
- FLOAT - Single precision floating values
- DOUBLE - Double precision floating values
- BIT - Bit field values
In order to add a new column into an existing table, we use the ALTER TABLE statement. This statement is not only used to add columns but also to delete and modify columns in an existing table.
CALTER TABLE table_name
ADD COLUMN column_name data_type;
By default, MySQL adds a new column at the end of the table. In order to add a column in the middle of the table, the following query can be used.
ALTER TABLE table_name
ADD COLUMN column_name column_definition [FIRST|AFTER existing_column];
Note: Commonly asked MySQL Interview QuestionsWhile working with tables you would face many instances where you might want to delete a table. The DROP TABLE statement can be used to achieve this.
DROP TABLE table_name;
Apart from this basic functionality, the DROP TABLE statement can be used to delete specific tables.
- [TEMPORARY] - ensures that only temporary tables are deleted.
- [IF EXISTS] - deletes a table in case it exists.
The syntax is as follows:
DROP [TEMPORARY] TABLE [IF EXISTS] table_name
Note: Commonly asked MySQL Interview QuestionsWhile using MySQL’s workbench, data is imported using the interface. However, data can also be inserted using the following methods.
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES ( value1, value2,...valueN );
Note: Commonly asked MySQL Interview QuestionsWhile using the WHERE clause to filter records the LIKE operator is used to filter out records in a column that match a particular pattern. There are two wildcards used with the LIKE statement and they are
- The percentage sign (%) - Used to represent zero, one or more characters
- The underscore sign (_) represents a single character
Duplicate data is a common problem while working with large tables. A method to avoid displaying duplicate records the DISTINCT statement is used. This statement removes all the duplicate values.
SELECT DISTINCT something FROM tablename;
Note: Commonly asked MySQL Interview QuestionsThe various string types available in MySQL are:
- SET
- BLOB
- CHAR
- ENUM
- TEXT
- VARCHAR
A primary key is used to uniquely identify each row of a table and hence it must be declared in order to create a relationship between two tables. One or more fields can be declared as a primary key for a table.
The foreign key or referencing key is used to link one or more tables together. A foreign key matches the primary key field of another table.
Essentially meaning that a foreign key field in one table refers to a primary key in another table. Hence allowing us to identify each from the other table, maintaining referential integrity between the tables.
Note: Important MySQL Interview QuestionsThe CURRENT_DATE() method in MySQL returns only the date. The NOW() methods return the date and the time of the server. Because of this additional property, the NOW() method is preferred over the CURRENT_DATE() method.
Note: Commonly asked MySQL Interview QuestionsBinary Large Objects (BLOB) is an object that is used to store a variable amount of data. There are 4 types of BLOBs and they vary based on the size of data that they can store. BLOBs are commonly used to save images in MySQL.
- TINYBLOB
- BLOB
- MEDIUMBLOB
- LONGBLOB
Views in MySQL help you return a particular set of rows based on the query executed. Rather than returning all the relevant records, MySQL only returns the desired set of records.
Furthermore, aliases can be set to columns to make the data more readable.
The below code can be used to create a view
CREATE [OR REPLACE] VIEW view_name AS
SELECT columns
FROM tables
[WHERE conditions];
Note: Commonly asked MySQL Interview QuestionsTriggers are special procedures and cannot be called upon directly. They are automatically invoked in response to an event.
An example of such events could be when new rows or columns are created.
The various triggers allowed in MySQL are:
- Before Insert
- After Insert
- Before Update
- After Update
- Before Delete
- After Delete
MySQL supports three different types of relations between tables.
- One-to-One: When one table contains a record that corresponds to exactly one other record in another table it is called a one-to-one relationship.
- One-to-Many: When one table contains a record that corresponds to main other values in another table it is called a one-to-many relationship.
- Many-to-Many: When a table contains many records that are linked to many other records in another table it is called a many-to-many relation. To create such a relationship, a third table containing the same key column from each of the other tables must be created.
By default MySQL contains 5 different tables:
- MyISAM
- Heap
- Merge
- INNO DB
- ISAM
Once a relationship is identified, the JOIN statement is used to join tables based on the corresponding records. It essentially connects two tables and displays them as one.
The four common ways to join two or more tables in MySQL are:
- Inner Join
- Left Join
- Right Join
- Cross Join
- Left Join
Access Control List (ACL) is a list of permissions that are associated with an object. This list is the backbone for the security of a MySQL server.
ACLs are cached in the memory which allows users to quickly check for authentication. Eg: user login authentication.
Note: Commonly asked MySQL Interview Questions