MySql interview questions and answers for Experts

Question 1:  What Is MySQL?

Answer 1: MySQL is a very fast, multi-threaded, multi-user, and robust SQL (Structured Query Language) database server. MySQL is free software.

Question 2:  How many TRIGGERS are allowed in MySql table?

Answer 2: MySql table allows Six triggers in MySQL database and they are:

1.  BEFORE INSERT
2. AFTER INSERT
3. BEFORE UPDATE
4. AFTER UPDATE
5. BEFORE DELETE
6. AFTER DELETE

Question 3:  Differentiate between FLOAT and DOUBLE.

Answer 3: Doubles are just like floats, except for the fact that they are twice as large. This allows for a greater accuracy.

Question 4:  What is Heap tables in MySql.

Answer 4: 

- 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

Question 5:  How do you control the max size of a HEAP table?

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

Question 6:  What is the difference between Primary Key and Unique Key?

Answer 6: Primary and Unique Key Both is implemented for Uniqueness of the column. Primary Key creates a clustered index of column where as an Unique creates unclustered index of column. Moreover, Primary Key doesn’t allow NULL value, however Unique Key does allows one NULL value.

Question 7:  How to store picture file in the database. What Object type is used?

Answer 7:  Database Object Type ‘Blob’ is recommended for store picture in a Database.

Question 8: How to Update database permissions/privilages.

Answer 8:  mysql> flush privileges;

Question 9: What is the Difference between INNODB and MYISAM?

Answer 9: The main difference between MyISAM and InnoDB is that InnoDB supports transaction
InnoDB supports some newer features: Transactions, row-level locking, foreign keys

MYISAM:
1. MYISAM supports Table-level Locking
2. MyISAM designed for need of speed
3. MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS
4. MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
5. MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done.

INNODB:
1. InnoDB supports Row-level Locking
2. InnoDB designed for maximum performance when processing high volume of data
3. InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
4. InnoDB stores its tables and indexes in a tablespace
5. InnoDB supports transaction. You can commit and rollback with InnoDB

Question 10:  What is the difference between mysql_fetch_object() and mysql_fetch_array()?

Answer 10: The mysql_fetch_object() function collects the first single matching record
mysql_fetch_array() collects all matching records from the table in an array.

Question 11: Define MYSQL fetures.

Answer 11: It is a very powerful program in its own right. It handles a large subset of the functionality of the most expensive and powerful database packages.
It uses a standard form of the well-known SQL data language.
It is released under an open-source license.
It works on many operating systems and with many languages.
It works very quickly and works well even with large data sets.
PHP ships with a number of functions designed to support MySQL databases

Question 12:  What are the steps you handle to edit a table in phpMyAdmin?

Answer 12: Select the table from the table list on the left side of the SQL screen. The table appears in a spreadsheet like format in the main part of the screen. We can edit the contents of the table in this window.
Edit or delete a record by clicking the appropriate icon displayed near the record.
Add a row by clicking the corresponding link near the bottom of the table.
Leave the cell you edited or press the Enter key. Any changes you make on the table data are automatically converted into the appropriate SQL code.

What's the difference between HAVING and WHERE?

WHERE is for filtering query results based on condition.

HAVING is for applying a filter on results of an aggregate function. In absence of aggregate function it functions same as WHERE.

Question 2: SQL Query to find second highest salary of Employee?

SELECT MAX(Salary) FROM Employee WHERE Salary NOT IN (select MAX(Salary) from Employee );

Question 2: SQL Query to find Max Salary from each department.

SELECT DeptID, MAX(Salary) FROM Employee  GROUP BY DeptID.

Question 5: Write a SQL Query to print the name of distinct employee whose DOB is between 01/01/1990 to 31/12/2000.

SELECT DISTINCT EmpName FROM Employees WHERE DOB  BETWEEN ‘01/01/1960’ AND ‘31/12/1975’;

Question 2: SQL Query to find duplicate Employees.

SELECT * FROM Employees GROUP BY `name` HAVING COUNT(*)>1

Question 5: Write a SQL Query to fetch alternate records from table. (even records only.)

SELECT * FROM Employees WHERE rowid IN ( SELECT decode(mod(rownum,2),0,rowid, null) FROM Employees )

Question 2:  what is index in sql?

An index can be created in a table to find data more quickly and efficiently.
The users cannot see the indexes, they are just used to speed up searches/queries.


SQL CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name ON table_name (column_name)