17 Essential MySQL Interview Questions

A few noteworthy advantages of using MySQL are

1. High Performance - MySQL was built with a lot of focus on power/ performance
2. Flexibility - MySQL can be used on all operating systems
3. Security - Good security and storage management system
4. Query Caching - Increases the speed of MySQL drastically
5. 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.

1. TINYINT - Used for very small integers values (1 Byte)
2. SMALLINT - Used for small integers (2 Bytes)
3. MEDIUMINT - Stores Medium-Sized integers (3 Bytes)
4. INT - Standard Integers values (4 Bytes)
5. BIGINT - Larger Integers values (8 Bytes)
6. DECIMAL - Stores fixed-point decimal values
7. FLOAT - Single precision floating values
8. DOUBLE - Double precision floating values
9. BIT - Bit field values

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

1. TINYBLOB
2. BLOB
3. MEDIUMBLOB
4. 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];  

Triggers 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:

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

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.

MySQL supports three different types of relations between tables.

1. 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.
2. 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.
3. 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:

1. MyISAM
2. Heap
3. Merge
4. INNO DB
5. ISAM

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];

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

1. [TEMPORARY] - ensures that only temporary tables are deleted.
2. [IF EXISTS] - deletes a table in case it exists.

The syntax is as follows:

DROP [TEMPORARY] TABLE [IF EXISTS] table_name

While 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 );  

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:

1. Inner Join
2. Left Join
3. Right Join
4. Cross Join
5. Left Join

While 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

1. The percentage sign (%) - Used to represent zero, one or more characters
2. 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;

The various string types available in MySQL are:

1. SET
2. BLOB
3. CHAR
4. ENUM
5. TEXT
6. 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.

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