100 Mysql interview questions and answers

Ask a question
          

Mysql interview questions and answers

Part 1   Part 2   Part 3   Part 4   Part 5   Part 6

MySQL interview questions - posted on June 27, 2013 at 16:35 PM by Kshipra Singh

1. How many TRIGGERS are allowed in MySql table?

MySql table allows following 6 triggers:

-BEFORE INSERT
-AFTER INSERT
-BEFORE UPDATE
-AFTER UPDATE
-BEFORE DELETE and
-AFTER DELETE

2. Differentiate between FLOAT and DOUBLE.

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

3. Tell us something about Heap tables.

- HEAP tables are found in memory.
- They are used for high speed storage on temporary basis.

Some of their characteristics are:
- 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

4. How do you control the max size of a HEAP table?

- Maximum size of Heap table can be controlled using MySQL config variable called max_heap_table_size.

5. What are the advantages of MySQL in comparison to Oracle?

- MySQL is open source software available at zero cost.
- It is portable
- GUI with command prompt.
- Administration is supported by MySQL Query Browser

6. What does myisamchk do?

- It compresses the MyISAM tables, which reduces their disk or memory usage.

How can we convert between Unix & MySQL timestamps?

- MySQL timestamp can be converted into Unix timestamp using the command UNIX_TIMESTAMP.
- Unix timestamp can be converted into MySQL timestamp using the command FROM_UNIXTIME.

7. What is BLOB?

- BLOB stands for binary large object.
- It that can hold a variable amount of data.

There are four types of BLOB based on the maximum length of values they can hold:

- TINYBLOB
- BLOB
- MEDIUMBLOB
- LONGBLOB

8. What is TEXT?

TEXT is case-insensitive BLOB. The four types of TEXT are:

- TINYTEXT
- TEXT
- MEDIUMTEXT
- LONGTEXT

8. What is the difference between BLOB and TEXT?

- In BLOB sorting and comparison is performed in case-sensitive for BLOB values
- In TEXT types sorting and comparison is performed case-insensitive.

9. How is 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

10. Explain advantages of MyISAM over InnoDB?

- MyISAM follows a much more conservative approach to disk space management – storing each MyISAM table in a separate file, which can be further compresses, if required.
- InnoDB stores the tables in tablespace. Further optimization is difficult with them.

11. How would concatenate strings in MySQL?

With the use of - CONCAT (string1, string2, string3)

12. How would you get the current date in Mysql?

By using SELECT CURRENT_DATE();

13. How would you enter Characters as HEX Numbers?

- To enter characters as HEX numbers, you can enter HEX numbers with single quotes and a prefix of (X)
- Alternatively, just prefix HEX numbers with (Ox).

14. How are MySQL timestamps seen to a user?

- MySQL time stamps are seen to a user in a readable format : YYYY-MM-DD HH:MM:SS.

MySQL Interview April 01, 2011 at 11:10 am by Rajmeet Ghai
Explain about MySQL and its features.

Latest answer - MySQL is a relational database management system which........            
Read answer

What are the disadvantages of MySQL?

Latest answer  - MySQL does not support a very large database size as efficiently........
Read answer

What are the security recommendations while using MySQL?

Latest answer  - Access to the user table should never be given to avoid SQL injection attacks..........
Read answer

Describe MyISAM table.

Latest answer  - In MySQL MyISAM is the default storage engine. MyISAM tables........
Read answer

What is HEAP table?

Latest answer  - Tables that are present in the memory are called as HEAP tables. When creating a HEAP table........
Read answer

What is Query Cache in MySQL?

Latest answer  - Query Cache in MySQL is used in scenarios when the same queries need to be executed..........
Read answer

How is Exception Handling handled in MySQL?

Latest answer  - Exception handling means changing the usual expected flow of the code..........
Read answer

What are the Performance and Scalability characteristics of MySQL?

Latest answer  - MySQL has a unique storage engine architecture that makes it adaptable to most.........
Read answer

What are the limitations of mysql in Comparison of Oracle?

Latest answer  - Transactions are better supported in Oracle as compared to Mysql..........
Read answer

What is a Trigger in MySQL? Define different types of Trigger?

Latest answer  - A trigger is a set of code which is executed in response to some event. ..........
Read answer

What is the difference between CHAR_LENGTH and LENGTH?

Latest answer  - CHAR_LENGTH includes leading and trailing blanks and the string-termination.......
Read answer

Explain the difference between BOOL, TINYINT and BIT.

Latest answer  - BIT data type can store up to 8 bytes from My SQL version 5.0.3.......
Read answer

What are ENUMs used for in MySQL?

Latest answer  - Using Enum can restrict the number of values that can be allowed to go inside a table. If there is an ENUM for Colors .....
Read answer

Explain MySQL Aggregate Functions.

Latest answer  - Aggregate functions in MySQL are a group of functions that are used to operate.......
Read answer

Describe Transaction-Safe Table Types in MySQL

Latest answer  - While using transactions in MySQL a transaction –safe table type must.......
Read answer

Describe MySQL Connection using mysql binary.

Latest answer  - Establishing connection to MySQL database using Mysql binary........
Read answer

Explain advantages of MyISAM over InnoDB

Latest answer - MyISAM is faster than InnoDB in most of the cases.........
Read answer

MySQL - Stored Procedures and Triggers

Latest answer - When multiple applications need to perform common database operations........
Read answer

Primary Keys and Auto Increment Fields in MySQL

Latest answer - Primary key is used to uniquely identify a row in a table.........
Read answer

COMMIT and ROLLBACK in MySQL

Latest answer - A transaction in MySQL is a set of SQL statements written to perform a specific task.........
Read answer

ALTER command to add and drop INDEX in MySQL

Latest answer - An index in MySQL can be added using ALTER statement in multiple ways as shown........
Read answer

Describe MySQL Connection using PHP Script.

Latest answer  - Establishing connection to MySQL database using PHP can be done...........
Read answer

Test your mysql knowledge with our multiple choice questions!

How will you export tables as an XML file in MySQL?
What is the use of i-am-a-dummy flag in MySQL?
What are the differences between MySQL_fetch_array(), MySQL_fetch_object(), MySQL_fetch_row()?
What is difference between mysql_connect and mysql_pconnect?
What is MySQL data directory? How to determine the location of the data directory?
What you can use Regular Expression for in MySQL? Support your answer with an example.

MySQL Interview Jan 09, 2009 at 18:10 pm by Rajmeet Ghai

How will you export tables as an XML file in MySQL?

MYSQL’s query browser has a provision called “Export Result Set” which allows the tables to be exported as XML.

What is the use of i-am-a-dummy flag in MySQL?

Using the i-am-dummy flag makes the SQL engine refuse any Updates or deletes to execute if the WHERE clause is not present. It is very useful when using delete statements. Using i-am-dummy flag will not allow the following statement to execute:

Delete from employee;

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

Mysql_fetch_object returns 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. E.g. using mysql_fetch_object field can be accessed as $result->name and using mysql_fetch_array field can be accessed as $result->[name]. 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;
}

What is difference between mysql_connect and mysql_pconnect?

Mysql_connect() opens a new connection to the database while mysql_pconnect() opens a persistent connection to the database. This means that each time the page is loaded mysql_pconnect() does not open the database. Mysql_close() cannot be used to close the persistent connection. Though it can be used to close mysql_connect().

What is MySQL data directory? How to determine the location of the data directory?

MySQL stores its data on the disk on the data dictionary. Each subdirectory under this data dictionary represents a MySQL database, inside those directories. By default the information managed my MySQL = server mysqld is stored in data directory.A default location of data directory in windows is C:\mysql\data or C:\Program Files\MySQL\MySQL Server 5.0 \data..

What you can use Regular Expression for in MySQL? Support your answer with an example.

Regular expressions in MySql are used in queries for searching a pattern in a string.

  • * Matches 0 more instances of the string preceding it.
  • + matches 1 more instances of the string preceding it.
  • ? Matches 0 or 1instances of the string preceding it.
  • . Matches a single character.
  • [abc] matches a or b or z
  • | separates strings
  • ^ anchors the match from the start.

REGEXP can be used to match the input characters with the database.

Example:

The following statement retrieves all rows where column employee_name contains the text 1000 (example salary):

Select employee_name
From employee
Where employee_name REGEXP ‘1000’
Order by employee_name

“.” Can be used to match any single character. “|” can be used to match either of the two strings

MySQL disadvantages - September 30, 2009 at 18:00 pm by Vidya Sagar

How will you export tables as an XML file in MySQL?

From the command prompt type the following statement:

mysql -u test --xml -e 'SELECT * FROM t1' > t1.xml

where ‘ –u test ‘ is the user name, --xml indicates the type of the file is xml, -e for export

What is the use of i-am-a-dummy flag in MySQL?

The flag i-am-a-dummy flag makes the MySQL engine to deny the UPDATE and DELETE commands unless the WHERE clause is present.

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

The mysql_fetch_object() returns the result from the database as an object.

Ex: $result->name

The mysql_fetch_array() returns the result from the database as an associative array or numeric array or both by using mysql_NUM or mysql_ASSOC options.

EX: $result[0] ,$result['name']

The mysql_fetch_row() returns the result from the database as a numeric array.

Ex: $result[0]

What is difference between mysql_connect and mysql_pconnect?

Mysql_connect:

- Opens a new connection to the database
- The database connection can be closed
- Opens the page every time the page is loaded.

Mysql_pconnect:

- Opens a persistent connection to the database.
- The database connection can not be closed.
- The page need not be opened every time the page is loaded.

What is MySQL data directory? How to determine the location of the data directory?

MySQL data directory is most important location in which all MySQL databases are stored. The default data directory is located in the file mysql.lf.

If the out of the space is the issue, then the directory need to be moved another location. Before moving, the database need to be closed. After moving the MySQL configuration file need to be edited. Look for the ‘datadir’ entry and change the path to the new directory.

What you can use Regular Expression for in MySQL? Support your answer with an example.

Regular expressions are a set of characters. Regular expressions are used for finding certain sequences in strings.

The following are the regular expression characters:

- * Represents matching characters that are 0 or more occurrences of the string that precedes it.
- + Represents matching characters that are 1 or more occurrences of the string that precedes it.
- ? Represents matching characters that are 0 or 1 occurrences of the string that precedes it.
- . Represents single character pattern matching.
- [abc] Represents matching characters that is either a or b or c
- | Used for separation of strings
- ^ Finds the matching pattern starting from the beginning.

To match the input characters with database tables, REGEXP is used. For example:

Select cityname from territories where cityname REGEXP ‘^(jo)*’;

The above query returns all city names that has the substring ‘jo’.

Write your comment - Share Knowledge and Experience

Discussion Board
database

would like to know more about usage of join and its types. (with syntax)
how would you perform normalization on tables with example table.

CSE 06-26-2014 11:14 AM

question

how many type of tables in sqldb ? please describe them .

Durga prasad 04-6-2012 02:25 PM

MySQL interview questions and answers

How can you see all indexes defined for a table?
SHOW INDEX FROM table_name;

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


Raveesh 12-6-2011 05:16 AM

MySQL interview questions and answers

What is the default port for MySQL Server?
- 3306

What is the function of myisamchk?
- It compresses the MyISAM tables. This reduces their disk usage.

Explain CSV tables.
They special tables for which the data is saved into comma-separated values files. They cannot be indexed.


Paresh 12-6-2011 05:15 AM

MySQL interview questions and answers

I want to find out all databases starting with ‘test’, I have access to?
- SHOW DATABASES LIKE ‘test%’;

Explain % and _ inside LIKE statement?
- % corresponds to 0 or more characters
- _ is exactly one character.

I want to start and stop MySQL on Windows? How do I do that?
- net start MySQL, net stop MySQL

Venkat 12-6-2011 05:14 AM

 

Test your MySQL skills - MySQL (20 questions)
Test your PHP skills - PHP (39 questions)

 
Interview questions
 
Home | Login | About us | Sitemap | Contact us