Top 10 MySQL Interview Questions and Answers

1. What is MySQL?

MySQL is an open-source relational database management system. As with other relational databases, MySQL stores data in tables made up of rows and columns. Users can define, manipulate, control, and query data using Structured Query Language, more commonly known as SQL.

2. What are some of the advantages of using MySQL?

Top 8 Advantages of MySQL

1. Ease of use: MySQL is known for being easy to use and set up, making it a popular choice for developers who are new to database management.
2. It is a cost-effective solution, as it is available as open-source software and can be downloaded and used for free.
3. MySQL is highly scalable and can handle a large amount of data efficiently.
4. It has a large user community, which means a lot of support and resources are available online, including documentation, forums, and tutorials.
5. MySQL is highly customizable and can be easily integrated with other applications and platforms.
6. It is a secure database management system with data encryption and user authentication to protect data from unauthorized access.
7. MySQL has a high performance and is able to handle a large number of concurrent connections, making it suitable for use in high-traffic web applications.
8. It is a reliable database management system, with a proven track record of stability and uptime.

3. What are some of the common MySQL commands?

Some common MySQL commands are:

• CREATE – To create Table

• INSERT – To insert data

• JOIN – To join tables

• DELETE – To delete a row from a table

• ALTER – To alter database or table

• BACKUP – to back up a table

• DROP – To delete a database or table

• CREATE INDEX – to add indexing to a column in a table

• GRANT – To change user privileges

• TRUNCATE – Empty a table

• EXIT – to exit

4. What are Heap tables?

HEAP tables are present in memory and they are used for high speed storage on temporary

basis.

• BLOB or TEXT fields are not allowed

• Only comparison operators can be used =, <,>, = >,=<

• AUTO_INCREMENT is not supported by HEAP tables

• Indexes should be NOT NULL

5. How to create an Index in MySQL?

In MySQL, there are different index types, such as a regular INDEX, a PRIMARY KEY, or a FULLTEXT index. You can achieve fast searches with the help of an index. Indexes speed up performance by either ordering the data on disk so it's quicker to find your result or, telling the SQL engine where to go to find your data.

Example: Adding indexes to the history table:

ALTER TABLE history ADD INDEX(author(10));

ALTER TABLE history ADD INDEX(title(10));

ALTER TABLE history ADD INDEX(category(5));

ALTER TABLE history ADD INDEX(year);

DESCRIBE history

6. What are the Numeric Data Types in MySQL?

Here are numeric data types for integer, fixed-point, floating-point, and bit values in MySQL. Except for BIT, the other numeric data types can be signed or unsigned.

Examples: 

INT - Standard Integer

TINYINT - Very Small Integer

SMALLINT - Small Integer

MEDIUMINT - Medium-sized Integer

BIGINT - Large Integer

DECIMAL - Fixed-point number

FLOAT - Single-precision floating-point number

DOUBLE - Double-precision floating-point number

BIT - Bit-field

7. What are the Temporal Data Types in MySQL?

MySQL provides temporal data types for date and time, as well as a combination of date and time. These are:

DATE - A date value in CCYY-MM-DD Format

TIME - A Time value in hh : mm :ss format

DATETIME - Date and time value in CCYY-MM-DD hh : mm :ss format

TIMESTAMP - A timestamp value in CCYY-MM-DD hh : mm :ss format

YEAR - A year value in CCYY or YY format

8. What are MySQL “Views”?

In MySQL, a view consists of a set of rows that is returned if a particular query is executed. This is also known as a ‘virtual table’. Views make it easy to retrieve the way of making the query available via an alias.
The advantages of views are:

• Simplicity

• Security

• Maintainability

9. What are MySQL Triggers?

A trigger is a task that executes in response to some predefined database event, such as after a new row is added to a particular table. Specifically, this event involves inserting, modifying, or deleting table data, and the task can occur either prior to or immediately following any such event.
Triggers have many purposes, including:

• Audit Trails

• Validation

• Referential integrity enforcement

10. How many different tables are present in MySQL?

• MyISAM is the default table that is based on the sequential access method.

• Heap is the table that is used for fast data access, but the data will be lost if the table or the system crashes. 

• InnoDB is the table that supports transactions using the COMMIT and ROLLBACK commands.

• BDB can support transactions similar to InnoDB, but the execution is slower.