MySQL Interview Questions For Experienced

45. What are Access Control Lists?

A list of permissions known as an Access Control List is connected to an object. It is MySQL server security model helps in troubleshooting issues like users being unable to connect. MySQL holds the ACL’s cached in memory. ACL’s also called grant tables. MySQL verifies the authentication data and permissions against the ACLs. It predetermined order whenever a user tries to log in or execute a command.

46. What is Normalization and list the different types of normalization?

Normalization is used to avoid duplication and redundancy. it is a process of organizing data. There are many normal forms of normalization. which are also called successive levels. The first three regular forms are sufficient.

• First Normal Form (1NF): There are no repeating groups within rows.
• Second Normal form(2NF): Value of every supporting column depending on the whole primary key.
• Third Normal Form(3NF): It depends only on the primary key and no other value of non-key column.

47. What are various ways to create an index?

There are many options to create an index as below:

• T-SQL statements can be used to create an index.
• The SQL Server Management Studio is available for use. we can use this to browse to the table where the index will be created, and then right-click on the Indexes node. We must select the New Index option over here.
• We can identify the index indirectly by specifying the PRIMARY KEY and the UNIQUE constraint in the CREATE TABLE or ALTER TABLE statement.

48. What are a clustered index and a non clustered index?

Cluster Index: An index type used to arrange data in a table is called a clustered index. The table’s data are stored in a specific order based on the clustered index.

Non Cluster Index: A non-clustered index is also a type of index used to organize data in a table. The table’s data are not stored in a specific order based on the non clustered index.

For more details, Check our latest article on Difference between Clustered and Non-clustered index.

49. How to validate emails using a single query?

We can use the regular expressions function (REGEXP_LIKE) to validate emails. Below is the example of validate emails using a single query.

SELECT

Email

FROM

Vehicle

where NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+.[A-Z]{2,4}’, ‘i’);

For detail you can check our latest article on Regular expressions (Regexp).

50. How can you handle the –secure-file-priv in MySQL?

The MySQL Server is restricted from loading directories using the LOAD DATA INFILE command by the -secure-file-priv option. Use the SHOW VARIABLES LIKE “secure_file_priv” command to view the directory that has been configured.

There are two options to handle as below.

• Either transfer your file to the directory that secure-file-priv specifies.
• Or you can turn off secure-file-priv. This must be removed at the beginning and cannot be disabled later.

MySQL is a Free open-source Relational Database Management System(RDMS) that stores data in a structured tabular format using rows and columns. It uses Structured Query Language (SQL) for accessing, managing, and manipulating databases. It was originally developed by MySQL AB, a Swedish company, and is now owned by Oracle Corporation. It’s known for its high performance, reliability, and ease of use, making it one of the most popular databases in the world.

Here, we’ll cover 50+ MySQL Interview Questions with answers that are asked in Data Analyst/SQL Developer Interviews at MAANG and other high-paying companies. Whether you are a fresher or an experienced professional with 1, 5, or 10+ years of experience, this article gives you all the confidence you need to ace your next interview.