MySQL Interview Questions And Answers for Freshers

1. What is MySQL and How does it differ from other relational databases?

MySQL is an open-source relational database management system (RDBMS) that is widely used for managing structured data. It utilizes SQL (Structured Query Language) for querying and managing data. MySQL is known for its reliability, scalability, and performance, making it a popular choice for various applications

2. How to create a database in MySQL?

To create a database in MySQL, we can use the CREATE DATABASE statement followed by the name we want to give to our database. For example:

CREATE DATABASE mydatabase;

3. Difference between CHAR and VARCHAR data types.

• CHAR: Fixed-length character data type where the storage size is predefined. Trailing spaces are padded to reach the defined length.
• VARCHAR: Variable-length character data type where the storage size depends on the actual data length. No padding of spaces is done.

4. Explain the differences between SQL and MySQL?

5. What is the MySQL server’s default port?

3306 is MySQL server‘s default port.

6. How can we learn batch mode in MySQL?

Below is the syntax used to run batch mode.

mysql <batch-file>;

mysq <batch-file> mysql.out

7. How many different tables are present in MySQL?

There are 5 types of tables present in MySQL.

• Heap table
• merge table
• MyISAM table
• INNO DB table
• ISAM table

8. What are the differences between CHAR and VARCHAR data types in MySQL?

• Storage and retrieval have been different for CHAR and VARCHAR.
• Column length is fixed in CHAR but VARCHAR length is variable.
• CHAR is faster than VARCHAR.
• CHAR datatype can hold a maximum of 255 characters while VARCHAR can store up to 4000 characters.

9. What is Difference between CHAR_LENGTH and LENGTH?

LENGTH is byte count whereas CHAR_LENGTH is character count. The numbers are the same for Latin characters but different for Unicode and other encodings.

Syntax of CHAR_LENGTH:

SELECT CHAR_LENGTH(column_name) FROM table_name;

Syntax of LENGTH:

SELECT LENGTH(column_name) FROM table_name;

10. What do you understand by % and _ in the like statement?

‘_’ corresponds to only one character but ‘%’ corresponds to zero or more characters in the LIKE statement.

11. How many index columns can be created in a table?

There are 16 indexed columns can be created in a table.

12. What are string types available for columns?

There are six string types available for the column.

• SET
• BOLB
• TEXT
• ENUM
• CHAR
• VARCHAR

13. Explain the main difference between FLOAT and DOUBLE?

• FLOAT stored floating point number with 8 place accuracy. The size of FLOAT is 4 bytes.
• DOUBLE also stored floating point numbers with 18 place accuracy. The size of DOUBLE is 8 bytes.

14. Explain the differences between BOLB and TEXT.

BOLB:

A BOLB is a large object in binary form that can hold a variable amount of data. Sorting and comparing in BLOB values are case-sensitive.

There are four types of BOLB.

• TINYBOLB
• BOLB
• MEDIUMBOLB
• LONGBOLB

TEXT:

Sorting and comparison are performed in case-insensitive for TEXT values. we can also say a TEXT is case-insensitive BOLB.

There are four types of TEXT.

• TINYTEXT
• TEXT
• MEDIUMTEXT
• LONGTEXT

15. Explain the difference between having and where clause in MySQL.

• WHERE statement is used to filter rows but HAVING statement is used to filter groups.
• GROUP BY is not used with WHERE. HAVING clause is used with GROUP BY.

16. Explain REGEXP?

REGEXP is a pattern match where the pattern is matched anywhere in the search value.

For more detail you refer to our MySQL | Regular expressions Article.

17. How can we add a column in MySQL?

A column is a series of table cells that store a value for table’s each row. we can add column by using ALTER TABLE statement.

ALTER TABLE tab_name

ADD COLUMN col_name col_definition [FIRST|AFTER exist_col];

18. How to delete columns in MySQL?

We can remove columns in MySQL by using ALTER TABLE statement.

Syntax:

ALTER TABLE table_name DROP COLUMN column1, column2….;   

19. How to delete a table in MySQL?

We can delete a table by using DROP TABLE statement. This statement deletes complete data of table.

DROP TABLE table-name;

20. How are mysql_fetch_array() and mysql_fetch_object() different from each another?

mysql_fetch_array() Gets a result row as a related array or a regular array from database. mysql_fetch_object gets a result row as an object from the database.

21. How to get the top 10 rows?

The following query will be used to get top 10 rows.

SELECT * FROM table_name LIMIT 0,10;

22. How does NOW() differ from CURRENT_DATE()?

current year, month, and date with hours, minutes, and seconds is shown by using NOW() command while CURRENT_DATE shows current year current month, and current date.

Syntax:

SELECT NOW();

SELECT CURRENT_DATE();

23. What is the use of the ‘DISTINCT’ keyword in MySQL?

the DISTINCT keyword allows for the removal of all duplicate records and the retrieval of unique records. The DISTINCT keyword is used with the SELECT statement.

Syntax:

SELECT DISTINCT colu1, colum2..

FROM table_name;

24. Which storage engines are used in MySQL?

Storage engines are also called table types. Data is stored in a file using multiple techniques.

Below are some techniques.

• Locking Level
• Indexing
• Storage mechanism
• Capabilities and functions

25. How to create a table in MySQL?

The CREAT TABLE command will be used to create a table in MySQL.

Syntax:

CREATE TABLE ‘Employee’ (‘Employee_Name’ VARCHAR(128), ‘Employee_ID’ VARCHAR(128), ‘Employee_Salary’ VARCHAR(16), ‘Designation’ CHAR(4)) ;

26. How to insert data in MySQL table?

We can add data to a table using the INSERT INTO statement .

Syntax:

INSERT INTO table_name ( field1, field2, field3 )  

VALUES  ( value1, value2, value3 );  

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.