Mysql Interview Questions

1. What is the difference between primary key and candidate key?

Candidate Key – A Candidate Key can be any column or a combination of columns that can qualify as unique key in database. There can be multiple Candidate Keys in one table. Each Candidate Key can qualify as Primary Key.

Primary Key – A Primary Key is a column or a combination of columns that uniquely identify a record. Only one Candidate Key can be Primary Key.

2. What is the different between NOW() and CURRENT_DATE()?

NOW () command is used to show current year,month,date with hours,minutes and seconds.

CURRENT_DATE() shows current year,month and date only.

3. How many triggers are allowed in MySQL table?

Following are the triggers which are allowed in MySQL table.

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

4. How many columns can we create for index?

16

5. How to copy data from one table to another table?

NSERT INTO table2 (id,uid,changed,status,assign_status) SELECT id,uid,now(),'Pending','Assigned' FROM table1

6. How to Copy table without copying data?

CREATE TABLE users_bck SELECT * FROM users WHERE 1=0;

7. What is mysql?

MySQL is a freely available open source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL).

SQL is the most popular language for adding, accessing and managing content in a database. It is most noted for its quick processing, proven reliability, ease and flexibility of use. MySQL is an essential part of almost every open source PHP application. Good examples for PHP & MySQL-based scripts are WordPress, Joomla, Magento and Drupal.

8. List features of MySQL?

Relational Database Management System (RDBMS): MySQL is a relational database management system.

• Easy to use: MySQL is easy to use. You have to get only the basic knowledge of SQL. You can build and interact with MySQL with only a few simple SQL statements.
• It is secure: MySQL consist of a solid data security layer that protects sensitive data from intruders. Passwords are encrypted in MySQL.
• It is scalable: MySQL can handle almost any amount of data, up to as much as 50 million rows or more. The default file size limit is about 4 GB. However, you can increase this number to a theoretical limit of 8 TB of data.
• Compatibale on many operating systems: MySQL is compatible to run on many operating systems.
• Allows roll-back: MySQL allows transactions to be rolled back, commit and crash recovery.
• High Performance: MySQL is faster, more reliable and cheaper because of its unique storage engine architecture.
• High Flexibility: MySQL supports a large number of embedded applications which makes MySQL very flexible.
• High Productivity: MySQL uses Triggers, Stored procedures and views which allows the developer to give a higher productivity.

9. What is meant by transaction and ACID properties?

Transaction is logical unit of work where either all or none of the steps should be performed. ACID is the abbreviation for Atomicity, Consistency, Isolation, and Durability that are properties of any transaction.

10. What are Heap tables?

A heap is a table without a clustered index. One or more nonclustered indexes can be created on tables stored as a heap. Data is stored in the heap without specifying an order. Usually data is initially stored in the order in which is the rows are inserted into the table, but the Database Engine can move data around in the heap to store the rows efficiently; so the data order cannot be predicted. To guarantee the order of rows returned from a heap, you must use the ORDER BY clause. To specify the order for storage of the rows, create a clustered index on the table, so that the table is not a heap.

11. Clustered Tables vs Heap Tables

HEAP TABLE

• Data is not stored in any particular order
• Specific data can not be retrieved quickly, unless there are also non-clustered indexes
• Data pages are not linked, so sequential access needs to refer back to the index allocation map (IAM) pages
• Since there is no clustered index, additional time is not needed to maintain the index
• Since there is no clustered index, there is not the need for additional space to store the clustered index tree
• These tables have a index_id value of 0 in the sys.indexes catalog view

CLUSTERED TABLE

• Data is stored in order based on the clustered index key
• Data can be retrieved quickly based on the clustered index key, if the query uses the indexed columns
• Data pages are linked for faster sequential access
• Additional time is needed to maintain clustered index based on INSERTS, UPDATES and DELETES
• Additional space is needed to store clustered index tree
• These tables have a index_id value of 1 in the sys.indexes catalog view

12. Differentiate between FLOAT and DOUBLE?

Following are differences for FLOAT and DOUBLE:

• Floating point numbers are stored in FLOAT with eight place accuracy and it has four bytes.
• Floating point numbers are stored in DOUBLE with accuracy of 18 places and it has eight bytes.

13. What are the objects can be created using CREATE statement?

Following objects are created using CREATE statement:

• DATABASE
• EVENT
• FUNCTION
• INDEX
• PROCEDURE
• TABLE
• TRIGGER
• USER
• VIEW

14. What is the difference between primary key and unique key

While both are used to enforce uniqueness of the column defined but primary key would create a clustered index whereas unique key would create non-clustered index on the column. Primary key does not allow ‘NULL’ but unique key allows it.