Mysql Interview Questions | Freshers & Experienced

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.

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;

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'

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.

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');

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.

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.

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.

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);

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.

Difference between TRUNCATE and DELETE commands

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.

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.

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.

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.

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

"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.

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.

The default port no for MySQL server if 3306.

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.

MySQL supports following storage engines

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

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'; 

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

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

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.

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

Syntax

SELECT CURRENT_DATE;

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

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.