Basic & Advanced MySQL Interview Questions with Answers



MySQL RDBMS is the most popular opne source database used mainly with PHP and another online applications. Here is some most commonly asked interview qyestions collections about MySQL. Download Complete PHP Faqs in PDF.If you are a PHP programmer or an online software developer, you should be thorough with basic RDBMS ( Relational Databse Management system ). The questions you can expect from this topic are about

  • Creating, deleting, altering Databases and tables
  • Connection with database
  • Primary keys , index keys and foreign keys
  • How you start designing tables as per the requirement
  • Selection query
  • Optimizing the queries with left and right join
  • Aggregating functions like SUM
  • Selecting the unique rows
  • avoiding the duplicate rows
  • Search in Mysql table
  • Technical features and General knowledge topics about MySQL

Most of the companies are working with this MySQL tables and there are a lot of job chances in this field as database  administrator  and programmer. Please go through all of these followed questions with their answers before attending the interview.

About MySQL

MySQL is an open-source relational database management system (RDBMS). The name is derived from a combination of “My” which stands for the name of the daughter of the co-founder Michael Widenius, and “SQL” which is stands for Structured Query Language.

MySQL is owned by Oracle, though initially owned and sponsored by a single for-profit firm, the Swedish company MYSQLAB. The MYSQL development project makes its source code accessible under the GNU General Public License terms, including a range of propriety agreements. You can get various paid editions with additional functionality for propriety use.

MySQL interview questions and answers

Writing MySQL exam can be a bit tricky. But if you are getting set for an interview, here are a few possible questions you might be asked and their answers.

  1. What is MySQL?

Ans.

MySQL is an open-source DBMS that is built, supported and distributed by MySQL AB. It is now owned by Oracle.

  1. Why do we use MySQL?

Ans.

MySQL database server is dependable, fast and user-friendly. One can download the software from the internet and as freeware.

  1. What is the default port for MySQL Server?

Ans.

The default port for MySQL server is 3306

  1. What is REGEXP?

Ans.

REGEXP is a pattern match in which matches pattern anywhere in the search value

  1. What does a TIMESTAMP do on UPDATE CURRENT_TIMESTAMP data type?

Ans.

When the table is created, Zero is used for updating the TIMESTAMP column. UPDATE CURRENT_TIMESTAMP modifier updates the timestamp field to current time whenever other fields of the table are changed.

  1. What does myisamchk do?

Ans.

It is used for compressing the MyISAM tables thereby reducing their disk or memory usage.

  1. What do you understand by federated tables?

Ans.

They let you have access to the tables which are located on other databases on other servers.

  1. How do we find out the auto increment that was assigned on Last insert?

Ans.

LAST_INSERT_ID will return the last value assigned by Auto increment and it not mandatory for the table name to be specified.

  1. How do you login to MySQL using Unix shell?

Ans.

To login, we can make use of this command:

#[mysql dir]/mysql –h hostname –u<UserName> -p<password>

  1. Differentiate between CHAR_LENGTH and LENGTH

Ans.

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

  1. How can you see all the indexes defined for a table?

Ans.

You can define indexes for the table by:

SHOW INDEX FROM <tablename>;

  1. What are the column comparisons operators?

Ans.

The =, <>, <=, <, >=, >, <<, >>, <=>, AND, OR, or LIKE operators are used in column comparisons in SELECT statements

  1. What is InnoDB?

Ans.

InnoDB is a transaction safe storage engine developed by Inno Oy, now Oracle Corporation.

  1. How many columns can you use in creating Index?

Ans.

You can only create a maximum of 16 indexed columns for any standard table

  1. What do you mean by % and _ in the LIKE statement?

Ans.

% corresponds to 0 or more characters. But _ is exactly one character in the LIKE statement.

  • 2_What’s MySQL
  • 3_Why use the MySQL Database Server
  • 4_What is the technical features of MySQL Server
  • 5_What are the column comparisons operators
  • 6_How do you get the number of rows affected by query
  • 7_What are HEAP tables in MySQL
  • 8_How do you return the a hundred books starting from 25th
  • 9_How would you write a query to select all teams that won either 2 4 6 or 8 games