MySQL - Interview Questions and Answers

71) Write a query to count the number of rows of a table in MySQL.

SELECT COUNT user_id FROM users;

72) Write a query to retrieve a hundred books starting from 20th.

SELECT book_title FROM books LIMIT 20, 100;

73) Write a query to select all teams that won either 1, 3, 5, or 7 games.

SELECT team_name FROM team WHERE team_won IN (1, 3, 5, 7);

74) What is the default port of MySQL Server?

The default port of MySQL Server is 3306.

75) How is the MyISAM table stored?

MyISAM table is stored on disk in three formats.

'.frm' file : storing the table definition
'.MYD' (MYData): data file
'.MYI' (MYIndex): index file

76) What is the usage of ENUMs in MySQL?

ENUMs are string objects. By defining ENUMs, we allow the end-user to give correct input as in case the user provides an input that is not part of the ENUM defined data, then the query won't execute, and an error message will be displayed which says "The wrong Query". For instance, suppose we want to take the gender of the user as an input, so we specify ENUM('male', 'female', 'other'), and hence whenever the user tries to input any string any other than these three it results in an error.

ENUMs are used to limit the possible values that go in the table:

For example:

CREATE TABLE months (month ENUM 'January', 'February', 'March'); INSERT months VALUES ('April').

77) What are the advantages of MyISAM over InnoDB?

MyISAM follows a conservative approach to disk space management and stores each MyISAM table in a separate file, which can be further compressed if required. On the other hand, InnoDB stores the tables in the tablespace. Its further optimization is difficult.

78) What are the differences between MySQL_fetch_array(), MySQL_fetch_object(), MySQL_fetch_row()?

Mysql_fetch_object is used to retrieve the result from the database as objects, while mysql_fetch_array returns result as an array. This will allow access to the data by the field names.

For example:

Using mysql_fetch_object field can be accessed as $result->name.

Using mysql_fetch_array field can be accessed as $result->[name].

Using mysql_fetch_row($result) where $result is the result resource returned from a successful query executed using the mysql_query() function.

Example:

 $result = mysql_query("SELECT * from students");   while($row = mysql_fetch_row($result))   {           Some statement;   }   

79) What is the use of mysql_close()?

Mysql_close() cannot be used to close the persistent connection. However, it can be used to close a connection opened by mysql_connect().

80) What is MySQL data directory?

MySQL data directory is a place where MySQL stores its data. Each subdirectory under this data dictionary represents a MySQL database. By default, the information managed my MySQL = server mysqld is stored in the data directory.

Interview :: MySQL