MYSQL Interview Questions?

MySQL database is used for projects of all levels of criticality. And why not. It is secure, inexpensive, well-supported with most platforms, performs fast, and can be scaled.

PHP Course


Basic MYSQL Interview Questions & Answers


Below are some of the top MYSQL interview questions for freshers (with proper answers).

1. What is MySQL?


MySQL is the world’s second most popular and most commonly used open-source relational database management system (RDBMS). It is based on a structured query language that Oracle supports. In addition, MySQL supports many operating systems, the most well-known of which are Windows, Linux, and UNIX.

Although the MySQL database can be used to create a wide range of applications, it is exclusively utilized for web applications. Therefore, it is a critical component of the open-source Lamp project.

MySQL was once owned by a for-profit company, MySQL AB, before being purchased by Sun Microsystems, which was later purchased by Oracle, which now controls MySQL.

2. What is LAMP Stack?


The LAMP stack is known for its versatility, cost-effectiveness, and strong community support, making it a popular choice for developing and hosting web applications and websites. Over time, other variations have emerged, such as the MEAN stack (MongoDB, Express.js, Angular, Node.js), which includes different components but serves a similar purpose in web development.

3. MySQL database is written in which language?


MySQL is primarily written in the C and C++ programming languages. C and C++ are commonly used for building the core components of database management systems like MySQL due to their performance, efficiency, and system-level capabilities.

4. What are the features of MySQL database?


MySQL is a popular open-source relational database management system (RDBMS) that offers several key features and capabilities:

  1. Flexible structure
  2. High performance
  3. Manageable and easy to use
  4. Replication and high availability
  5. Security and storage management
  6. Drivers
  7. Graphical Tools
  8. MySQL Enterprise Monitor
  9. MySQL Enterprise Security
  10. JSON Support
  11. Replication & High-Availability
  12. Manageability and Ease of Use
  13. OLTP and Transactions
  14. Geo-Spatial Support

5. What are the differences between MySQL and SQL?


SQL stands for Standard Query Language. It is used to interface with databases such as MySQL.

MySQL is a database that stores and protects numerous sorts of data. A PHP script is necessary to store and retrieve values from the database.

SQL is a programming language, but MySQL is a database.

SQL is used to create database management systems, whereas MySQL allows data handling, storage, deletion, and modification.

6. What are the differences between a database and a table?


There is a significant difference between a database and a table. The distinctions are as follows:

  1. Tables represent data division in a database, whereas a database represents a set of data and tables.
  2. Tables are used to group data about one another to produce a dataset. This dataset will be incorporated into the database. The data saved in any form in the table is a part of the database, but the opposite is not valid.

7. Why use the MySQL database server?


The core reason to choose the MYSQL server is that it is open source and can be used by developers and small businesses at no cost. There are several more reasons to use it that you must know as part of your preparation for MySQL technical interview questions and answers.

  1. The MySQL server is free and open source.
  2. MySQL’s community is vast and supportive. Thus any issues with MySQL are fixed as soon as feasible.
  3. MySQL has been available on the market for a while, and relatively stable versions are available. All bugs found in prior releases have been continuously removed, and each upgrade provides a more stable version.
  4. The MySQL database server is speedy, dependable, and simple to use. The software is simple to use and customize. On the internet, MySQL software is freely downloadable.

8. What are the various tables available in MySQL?


There are various tables in MySQL that can be used. However, MyISAM is the default database engine in MySQL. There are five different types of tables available:

  • MyISAM
  • Heap
  • Merge
  • INNODB
  • ISAM

9. How to create columns in MySQL?


A column is a set of cells in a table that holds one value for each row. For adding columns to an existing table in MySQL, you should use the ALTER TABLE command.

10. How to delete a table in MySQL database?


The Drop Table command in MySQL can be used to delete a table. This permanently deletes a table’s whole data set from the database, including structure and definition.

You must know that precautions are essential while deleting a table. It is because we are unable to retrieve the table in MySQL after using this command.

11. How to add a foreign key constraint in MySQL database?


To link two or more tables together, utilize a foreign key. To join the two tables, it matches the primary key field of another table. It enables us to form a parent-child bond with the tables.

There are two ways to add a foreign key to a table:

  1. Using the CREATE TABLE Statement
  2. Using the ALTER TABLE Statement

12. How to import a MySQL database?


Importing a database in MySQL is moving data from one location to another. However, it is a highly beneficial tool for backing up essential data or transferring data across sites.

For example, we have a database of contacts that must be kept in a secure location. So we must export it to a safe area, and if it is lost from its original site, we can restore it using import options.

We can import a database into MySQL in two ways:

  1. Command Line Tool
  2. MySQL Workbench

13. How to join two tables in MySQL?


We can join tables in MySQL using the JOIN clause. MySQL supports a variety of JOIN clauses. These clauses relate to many tables and return only records that have the same value and property in all of them.

The four most straightforward techniques to combine two or more tables in MySQL are as follows:

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

14. What is MySQL Workbench?


MySQL Workbench is a user interface for MySQL (GUI) applications for accessing and managing MySQL databases. Oracle created and maintained it, offering SQL creation, data migration, and complete administrative tools for server configuration, user management, backup, and so on.

In addition, this Server Administration can be used to generate new physical data models, E-R diagrams, and SQL development. It works with all major operating systems. MySQL Server versions 5.6 and higher include support for it.

It is primarily available in three editions, as listed below:

  1. Community Edition (Open Source, GPL)
  2. Standard Edition (Commercial)
  3. Enterprise Edition (Commercial)

15. What is a Stored Procedure in MySQL?


A stored procedure in MySQL is a group of SQL statements saved in the database. SQL queries such as INSERT, UPDATE, DELETE, and so on can be included in the stored procedure.

A procedure allows us to reuse the same code by running a single line. It saves the data dictionary in the database.

16. How to create a view in MySQL?


A view is a database object that derives its values from the base table. It is a virtual table produced by connecting one or more tables in a query. It functions similarly to the base table but contains no data. If the underlying table changes, the changes are mirrored in the View.

18. How to create a MySQL trigger?


A MySQL trigger is a code that performs a certain action in a database automatically invoked if certain events occur on a table or View in the database.

For example, it can be run whenever records are added to a table, or any columns are modified.

19. How to clear the console screen in MySQL?


Before version 8, it was impossible to clear the screen using MySQL in Windows. At the time, the only way to remove the screen was to exit the MySQL command-line tool and reopen MySQL.

20. How to create a new MySQL user?


In MySQL, a USER is a record in the USER-TABLE. It holds the login details, account privileges, and host information for the MySQL account so that the database can be managed.

A new user account can be created with the help of MySQL Establish User statement. It provides recent reports with authentication, SSL/TLS, resource limit, role, and password management characteristics.

21. How to check for USERS in MySQL?


To manage a database in MySQL, we need to see a list of all user accounts on a database server. The command below is used to get a list of all database server users:


22. How to insert a date into a table in MySQL database?


To add a date to a MySQL table, we can use the INSERT statement. MySQL supports date data types, including DATE, TIMESTAMP, DATETIME, and YEAR. The date format in MySQL is YYYY-MM-DD by default.

23. How to find the database size in MySQL?


To have information about the tables and databases, we can query the information_schema.tables table in MySQL. It will return data length, index length, collation, creation time, etc.

24. How does MySQL indexing work?


Indexing is the process of converting an unsorted list into an ordered list. It aids in increasing query efficiency when searching tables in MySQL. The indexing functions similarly to a book index.

For example, we have a book and wish to learn about search. Go through each page without indexing until the desired topic is located. It contains a collection of keywords that may be used to find the topic stated on pages. Then we may go directly to those pages without having to wade through all of them.

25. Who owns MySQL?


MySQL is the most widely used free and open-source database software under the GNU General Public License. MySQL AB, a Swedish firm, initially owned and sponsored it. It is presently owned by Sun Microsystems (formerly Oracle Corporation), which manages and improves the database.

26. In MySQL, how to view the database?

Viewing or listing the accessible databases is a regular operation while working with the MySQL server. For example, using the following command, we can inspect all of the databases on the MySQL server host:

27. How to enable auto increment in MySQL?

Auto Increment is a constraint that creates a unique number automatically when inserting a new entry into the table. It is typically used for a table’s primary key field.

28. What are the differences between TRUNCATE and DELETE in MySQL?


  1. DELETE is a DML command, and TRUNCATE is a DDL command.
  2. The Where command cannot be used with TRUNCATE QL, although it can be used with DELETE.
  3. TRUNCATE cannot be used in conjunction with indexed views, although DELETE may.
  4. To erase data from a table, use the DELETE command. It just deletes the rows of data from the table, but truncating is a dangerous operation that should be used with caution because it permanently deletes every row from a table.

29. How many triggers can be used in MySQL?


In the MySQL database, only six triggers are permitted to be used.

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

30. What is a heap table?

HEAP tables are tables that are stored in memory. When creating a heap table in MySQL, you must define the TYPE as HEAP. These are frequently referred to as memory tables. They are used for temporary high-speed storage. They don’t support BLOB or TEXT fields.

31. What are BLOB and TEXT in MySQL?


A huge binary object is referred to as a BLOB. It is used to store varying amounts of info.

The BLOB comes in four varieties.

  1. TINYBLOB
  2. BLOB
  3. Before Update
  4. MEDIUMBLOB
  5. LONGBLOB

The only distinction between these is the maximum length of data they can carry.

The BLOB TEXT is case-insensitive. The values of TEXT are non-binary strings (character strings). Character sets and values are saved and compared depending on the character set collation.

TEXT is classified into four categories.

  1. TINYTEXT
  2. TEXT
  3. MEDIUMTEXT
  4. LONGTEXT

32. What is a MySQL trigger?


A trigger is a sequence of instructions that run in response to certain occurrences.


33. What is the difference between a heap table and a temporary table?

Heap tables:

  1. They are present in memory and are utilized for temporary high-speed storage. They don’t support BLOB or TEXT fields.
  2. Heap tables do not support AUTOINCREMENT.
  3. Indexes should not be NULL.

Temporary tables:

They are used to store temporary data. However, it is sometimes advantageous to keep transitory data. Therefore, the temporary table is erased when the client session ends.

Main differences:

All clients share the heap tables. However, the temporary tables are not.

Heap tables are another storage engine, whereas temporary tables require a particular privilege (create a temporary table).


34. What’s the difference between FLOAT and DOUBLE?

FLOAT saves floating-point integers with up to 8 places of precision and allocates 4 bytes. DOUBLE, on the other hand, keeps floating-point integers with accuracy up to 18 places and allocates 8 bytes.

35. What are the disadvantages of MySQL?


Here are some drawbacks of MySQL:

  1. For large-scale databases, MySQL is inefficient.
  2. Versions below 5.0 do not support the COMMIT and STORED PROCEDURES capabilities.
  3. Transactions are not processed efficiently.
  4. MySQL’s functionality is heavily reliant on third-party extensions.
  5. Development is not a community-driven process.

36. What are the differences between CHAR and VARCHAR?


Here are the main differences between CHAR and VARCHAR:

  • In terms of storage and retrieval, CHAR and VARCHAR differ.
  • The length of a CHAR column is constant, whereas the size of a VARCHAR column is flexible.
  • The maximum number of characters CHAR data types can carry is 255, but VARCHAR data types can hold up to 4000.
  • VARCHAR is 50 percent slower than CHAR.
  • VARCHAR employs dynamic memory allocation, whereas CHAR uses static memory allocation.

37. What is the default MySQL port number?


MySQL’s default port is 3306.

38. What is REGEXP in MySQL?


REGEXP is a regular expression pattern match. A regular expression is a vital tool for specifying a pattern for a complex search.

It is essentially a customized text string used to describe a search pattern. To further comprehend it, consider a circumstance in which you search for.txt files in the file manager to list all text files. .*\.txt is the regex equivalent of .txt.

39. How many columns can be added to an index?


A typical table can have a maximum of 16 indexed columns.

40. What is MySQL SAVEPOINT statement?


A savepoint is a defined point in any transaction. SAVEPOINT is a MySQL statement used to create a named transaction savepoint with the name of the identifier.

41. What is SQLyog?


SQLyog is the most popular administrative GUI tool. It is the most widely used MySQL development and administration tool. It combines the functionality of MySQL administrator, phpMyadmin, and other programs.

42. What is the use of ENUMs in MySQL?


It is a data type in MySQL. By creating ENUMs, we allow the end-user to supply accurate input. If the user offers information not part of the ENUM-defined data, the query will not execute, and an error message stating “The incorrect Query” will be presented.

43. What is InnoDB?


InnoDB is a SQL storage database. InnoDB also supports ACID transactions and includes support for foreign keys. Initially held by InnobaseOY, it currently belongs to Oracle Corporation, which bought it in 2005.

44. What is Access Control List in MySQL?


An Access Control List is a set of permissions associated with a particular item. MySQL caches the Access Control Lists in memory, and anytime a user attempts to authenticate or execute a command; it checks the permissions required for the object. If the permissions are available, the execution succeeds.