MySQL Interview Questions with Answers

MySQL is the most famous relational database management system. It is open source. Here is some common important question and answer in MySQL. Hope it will help you to build successful carrier.

What’s MySQL?
MySQL is an open source SQL Database Management System. It is built, distributed, and supported by MySQL AB (Now Oracle Corporation).  MySQL AB is a commercial company, founded by the MySQL developers.

Why we use MySQL Database Server?
MySQL Database Server is very fast, reliable, and easy to use. It is open source. That’s why anybody can modify it based on requirements.  Anybody can download the MySQL software from the Internet and use it without any cost.

Is Mysql query is case sensitive?
No,mysql queries are not case sensitive.

How many columns can be used for creating Index?
Maximum of 16 indexed columns can be created for any table.

What are the technical features of MySQL Server?
MySQL is a client/server system. It contains multi-threaded SQL server that supports different backends, different client programs and libraries, application programming interfaces (APIs), and administrative tools.

What is the default port for MySQL Server?
The default port is 3306.

What is Heap table?
HEAP tables are created in memory. They are used for high speed storage on temporary basis. They do not allow BLOB or TEXT fields. Only comparison operators like =, <,>, = >,=< , can be used with them. AUTO_INCREMENT is not supported by HEAP tables.  Indexes should be NOT NULL.

What is MyISAM table?
MyISAM is a one kind of table. In MySQl the default storage engine is MyISAM. MyISAM tables store data values with the low byte first. It is very reliable. We can recover data if there is a hardware failure.
It stored on disk in three formats.

• .frm file- storing the table definition
• .MYD file – data file
• .MYI file- index file

What is BLOB?
BLOB stands for binary large object. It can hold a variable amount of data. There are four types of BLOB-
TINYBLOB, BLOB, MEDIUMBLOB , LONGBLOB

What is TEXT?
TEXT is case-insensitive BLOB. There are four types of TEXT- TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT

What is the difference between BLOB and TEXT?
In BLOB sorting and comparison is performed in case-sensitive.  In TEXT types sorting and comparison is performed case-insensitive.

What is Query Cache in MySQL?
Query Cache in MySQL is used in scenarios when the same queries need to be executed on the same data set. These queries also return the same result. Query cache is most useful when there are tables that are not expected to change very often. It is important to note that the query cache does not return old data. If the tables are modified, any important entries in the query cache are flushed.

How many TRIGGERS are allowed in MySql table?
MySQL allows 6 triggers like-

• BEFORE INSERT
• AFTER INSERT
• BEFORE UPDATE
• AFTER UPDATE
• BEFORE DELETE
• AFTER DELETE

How do you get the current version of MySQL?
SELECT VERSION();

What are the String types are available for a column?
CHAR, VARCHAR, BLOB, TEXT, ENUM, and SET

Differentiate between FLOAT and DOUBLE.
FLOAT stores floating point numbers with accuracy up to eight places and has four bytes.
DOUBLE stores floating point numbers with accuracy upto 18 places and has eight bytes.

How we concatenate strings in MySQL?
CONCAT (string1, string2, string3)

How would you get the current date in Mysql?
SELECT CURRENT_DATE();

What are the disadvantages of MySQL?

• MySQL does not support a very large database size as efficiently
• MySQL does not support ROLE, COMMIT, and Stored procedures in versions less than 5.0
• Transactions are not handled very efficiently

What are the security recommendations while using MySQL?

• Access to the user table should never be given to avoid SQL injection attacks
• Privileges such as GRANT and REVOKE must be made use of
• SHOW GRANTS can be used to see the list of users who have access
• Never run the MySQL server as the Unix root user

What are the drivers in MySQL?
There are eleven drivers. Five from MySQL communities and six from MySQL AB.

• PHP Driver
• JDBC Driver
• ODBC Driver
• C WRAPPER
• PYTHON Driver
• PERL Driver
• RUBY Driver
• CAP11PHP Driver
• Ado.net5.mxj

What are Joins in MySql.
The different joins in mysql are:-

• Inner Join– Return rows when there is at least one match in both tables.
• Left Join–  Return all rows from the left table, even if there are no matches in the right table.
• Right Join–  Return all rows from the right table, even if there are no matches in the left table.
• Full Join– Return row

Can the database be renamed in MySQL?
No.

How to change the root password if the root password is lost?
Start the Database with –skip-grants-table. Change the password and restart the db in  normal mode.

How do you restrict the users in mysql?
By grant and revoke commands.

What do _ and % mean inside LIKE statement?
_ is exactly one character.
% corresponds to 0 or more characters.

Difference between mysql_fetch_array and mysql_fetch_object.
mysql_fetch_object-Fetch a result row as an OBJECT.
mysql_fetch_array- Fetch a result row as an associative ARRAY, a numeric array, or both

How do you change a password for an existing user via mysqladmin.
Using command
mysqladmin -u root -p password “newpassword”

How do you start MySQL on Linux.
/etc/init.d/mysql start

How do you login to MySql using Unix shell.
Using command
# [mysql dir]/bin/mysql -h hostname -u <UserName> -p <password>

How can we see all indexes defined for a table?
SHOW INDEX FROM <tablename>

How do you find out slow queries in mysql?
By enabling the slow query log as follows
SET GLOBAL slow_query_log = 1

How can we get the number of rows affected by query?
SELECT COUNT (ID) FROM users;

How to display top 100 rows from table?
SELECT * table_name FROM LIMIT 0,100

Difference between NOW() and CURRENT_DATE()
NOW () command – shows current year,month,date with hours,minutes and seconds
CURRENT_DATE() -shows current year,month and date only

What are the different database engines available in MySQL?

• MyISAM
• INNODB
• Memory
• Federated
• CSV

What are the best installation practices for MySQL?

• Binary Installation
• RPM Installation
• Source Code compilation
• After installation you should change the location of bin logs and data files on the different physical disks

Which RAID level is best suited for MySQL?
RAID 10

How do we upgrade from one mysql version to another mysql version?
Create a slave on newer version and change it to MASTER.

How many types of logs are there in mysql?
General Log, Error Log, Binary Log and Slow Query Log