Mysql Interview Questions

Mysql Interview Questions | Freshers & Experienced

  • Arushi
  • 12th Feb, 2021
  • 791 Followers

Key Features of Mysql

Below are few major features of Mysql

Relational Database System

Client/Server Architecture

SQL compatibility

Free to download

Easily Scalable

High Performance

Compatibale with most of OS.

Allows transactions

Secure and Highly Flexible

Mysql Interview Questions

Q1. What is mysqlcheck do?

mysqlcheck is a command-line interface that allows you to check, repair, analyze and optimize the multiple tables.

Below are some commands:

  • mysqlcheck databaseName: verify all tables in a given database.
  • mysqlcheck --repair databaseName tableName - Repairs the mentioned table in the specified database.
  • mysqlcheck databaseName tableName - verify the specified table in the specified database.

Q2. What does tee command do in MySQL?

tee command is used to start the logging process and send the output to the console and text file.

Syntax:

mysql> tee /tmp/my.out;

Q3. Write an sql statement to count number of rows in table?

The SQL COUNT () command is used to count the number of rows. It returns the number of rows that satisfies the condition specified in where clause.

Syntax:

COUNT(*) returns all rows (duplicated as well null rows)
COUNT( [ALL|DISTINCT] expression ): returns Distinct rows only.

Example Query

select count(*) from posts where status='published'

Q4. What is maximum number of columns allowed in a table?

The hard limit is 4096 columns per table but the effective number of columns is less. The exact limit depends on various factors which are:

  • The maximum row size in a table limits the number of columns.
  • Storage requirements of columns put constraints on a number of columns.
  • Different storage engines have different restrictions on the number of columns.

Q5. What is use of IGNORE keyword in MySQL?

If you want to insert duplicate records in a table in the presence of a unique index, it will return an error. So, to discard it silently, use the INSERT IGNORE command instead of INSERT. It will tell MySQL to discard the record if it is duplicate.

Syntax

INSERT IGNORE INTO Table (field1, field2)
   -> VALUES( 'value1', 'value2');

Q6. What is difference between MyISAM Static and MyISAM Dynamic?

The types of the column can explain the difference in MyISAM static and MyISAM dynamic we use in our table.

When the column of the table varies by using specific formats like (VARCHAR, VARBINARY, TEXT, BLOB and their variants), it is called dynamic MyISAM.

Whereas in static MyISAM, the columns are fixed, and any type of format can not change the columns.

Q7. What are different type of Joins available in MySQL?

Joins in Mysql are used to retrieve the data from more than two tables. There are three types of joins available in MySQL:

  • MySQL INNER JOIN (simple join): It returns all rows from multiple tables when the join condition is satisfied.
  • MySQL LEFT OUTER JOIN (LEFT JOIN): It returns all rows from the left-hand table which is specified in the ON condition and satisfies the join condition.
  • MySQL RIGHT OUTER JOIN (RIGHT JOIN): It returns all rows from the left-hand table which is specified in the ON condition and satisfies the join condition.

Q8. What is difference between denormalization and normalization?

Difference between denormalization and normalization

Normalization is a technique to eliminate the redundant data by minimizing the insertion, deletion and update anomalies.

Denormalization is a technique to add the redundancy to improve the performance of an application In short, Denormalization is the inverse process of Normalization.

Q9. Write a query to find second highest salary of an employee?

Below is a simple MySQL query to find the second highest salary of an employee

SELECT name, MAX(salary) AS salary  
FROM table_name WHERE salary < (SELECT MAX(salary) FROM table_name);

Q10. How many rows will return from dual table?

As the Dual table has only one row so it returns only one row. DUAL tables are the special type of table that is used for evaluating expressions or calling functions.

Q11. What is the difference between TRUNCATE and DELETE commands?

Difference between TRUNCATE and DELETE commands

DELETE TRUNCATE
It is a DML command. It is a DDL command.
It deletes the data temporarily from the database. It deletes the data permanently from the database.
It won’t reinitialize the table. It will reinitialize the table.
Command is: Delete from < table name> Syntax is: Truncate table <table_name>

Q12. What are scrollable cursor?

The scrollable cursor is a flexible type of cursors which when placed in a table, the cursor can be moved forward, backward or even to the start of the table. But in the standard cursor, this facility is not available. The normal cursor does not allow us to move the cursor down and up of the table also.

Q13. What are HEAP Tables?

Heap tables also known as memory tables are tables without a Clustered Index. These tables are in memory and used for fast data retrieval. Data types like "BLOB" or "TEXT" are not supported by Heap tables.

Q14. What is difference between SUBSTR and INSTR?

Difference between SUBSTR and INSTR in Mysql

  • SUBSTR is used to retrieve the set of characters from a string and INSTR is used to find the position of a character in a string.
  • SUBSTR specifies the starting position, ending position and length of characters and INSTR specifies the character whose position you want to find.
  • SUBSTR returns the substring and INSTR returns the position of a character.

Q15. What is Pragma EXECPTION_INIT ? Explain its usage?

Pragma EXCEPTION_INIT allows you to instruct the compiler to combine your message to Oracle predefined message during compile time.

It is used for better readability of the program and helps you to handle exceptions in your way.

Q16. How to use mysqldump to create a copy of the database?

mysqldump is used to duplicate the database including the tables and the data with a new name.

Example:

mysqldump -uroot -p my_project -r my_project.sql

Q17. What is "i_am_a_dump" flag in MySql?

"i_am_a_dump" is a flag that supports MySQL engine when WHERE clause is missing. It refuses to perform any update or delete statements.

Q18. Explain advantages of InnoDB over MyISAM?

Advantages of InnoDB over MyISAM:

  • InnoDB supports ACID property whereas MyISAM doesn't support transactional integrity.
  • InnoDB uses row-level locking but MyISAM doesn't have rollback capabilities.
  • InnoDB allows foreign key constraints that ensure the integrity of the database.
  • InnoDB supports a buffer for data as well for indexes bu MyISAM support buffer only for indexes.
  • InnoDB is more restrictive than MyISAM to table corruption.

Q19. What is the default port for MySQL Server?

The default port no for MySQL server if 3306.

Q20. What is the difference between CHAR and VARCHAR?

Difference between CHAR and VARCHAR

  • CHAR is a fixed-length string whereas VARCHAR is a variable-length string.
  • CHAR padded unfilled space with blanks while in VARCHAR, only required characters occupy space.

Q21. Enlist the storage engine available in MySQL?

MySQL supports following storage engines

  • InnoDB
  • MyISAM
  • Memory
  • CSV
  • Merge
  • Archive
  • Federated
  • Blackhole
 

Q22. How to use the MySQL slow query log?

MYSQL slow query log is used to find those queries which take a long time to execute and selected for optimization.

To use Mysql slow query log you need to enable the slow query log by running the below query.

SET GLOBAL slow_query_log = 'ON'; 

Q23. What is the difference between BLOB and TEXT?

BLOB stands for Binary Large Objects used for storing binary data whereas TEXT is used for storing strings.

Q24. How many types of triggers are available in MySQL?

There are six different types of triggers in MySQL:

  • Before Update Trigger
  • After Update Trigger
  • Before Insert Trigger
  • After Insert Trigger
  • Before Delete Trigger
  • After Delete Trigger

Q25. What is the difference between FLOAT and DOUBLE?

Difference between FLOAT and DOUBLE

  • Float is a 32-bit IEEE 754 single-precision whereas double is a 64-bit IEEE 754 double precision Floating Point Number.
  • Float uses 8 bits for the exponent, and 23* for the value whereas double uses 11 bits for the exponent, and 52* bits for the value
  • The float has 7 decimal digits of precision whereas double has 15 decimal digits of precision.

Q26. How to get the current date in MySQL?

In MySQL, CURRENT_DATE () command is used to get the current date in the 'YYYY-MM-DD' format.

Syntax

SELECT CURRENT_DATE;

Q27. What is MySQL data directory?

MySQL data directory specifies the location, source, usage, destination of the data elements present in the database.

Q28. What are federated tables?

A federated table is a table that points to a table in another MySQL database instance. In short, it is a view of another table.

About Author :

  • Author of Mysql Interview Questions

    Arushi Pandey

    Arushi Pandey is senior PHP developer and have hand on experience on PHP Frameworks and tools.He loves to share and write programming content on internet.

Leave A Comment :

Valid name is required.

Valid name is required.

Valid email id is required.