Most Important MySQL Interview Questions

1. What’s the difference between primary key and unique key?

Index: An index is a structure in a table that orders the data. It allows the database to access data quickly (In MySQL its implemented using B-tree algorithms). Primary Key: This is an index that cannot be NULL, Primary Keys are used in building relationships between tables in a database. (an index is automatically created on the primary key). The difference between primary and ordinary keys is that there can be multiple keys, but only one primary key. Unique Key: Unique and Index are same, the difference is, in Unique, duplicate are not allowed in any circumstances and that is enforced by database server. Primary key(s) qualify to be Unique on basis of their uniqueness. In case, your table has 2 primary keys means that the 2 fields together form one unique key. Each field by itself may have repeating values, but both primary keys combined together must be unique.

2. List of MySQL Commands:

alter table, analyze table, backup table, begin, check table, commit, create database, create function, create index, create table, delete, describe, drop database, dop function, drop index, drop table, explain, flush, grant, insert, join, kill, load data infile, lock tables, optimize table, rename table, repair table, replace, restore table, revoke, rollback, select, set, set transaction, show, truncate, unlock tables, update, use.

3. how many ways we can find the current date using mysql?

date() and now()

4. How many values can the SET function of MySQL take?

Mysql set can take zero or more values but at the maximum it can take 64 values

5. Explain Normalization concept

Normalization helps to reduce redendence. Ist normlization gives primary key, 2 nd foreign key.

6. How can we repair a MySQL table?

The syntex for repairing a mysql table is REPAIR TABLENAME, [TABLENAME, ], [Quick],[Extended] or $sql = "REPAIR TABLE `Table_Name`"; or REPAIR TABLE table_name TO new_tabel_name [,table_name1 TO new_tabel_name1,..] ; This command will repair the table specified if the quick is given the mysql will do a repair of only the index tree if the extended is given it will create index row by row.

7. What is the maximum length of a table name, database name, and fieldname in MySQL?

maximum lenghth of Names of database, table, columns are: database- 64 table -64 columns-64 alias-255

8. What are the other commands to know the structure of table using MySQL commands except explain command?

describe table_name;

9. How many tables will create when we create table, what are they?

3 files, *.frm, *.MYD, *.MYI

10. What is the purpose of the following files having extensions 1) .frm 2) .myd 3) .myi? What do these files contain?

data’s are stored in name.Myd table Structure are name.$frm Index tables are name.myi

11. What is maximum size of a database in MySQL?

no limit database. database containing 50,000,000 records, Table size: 64TB~16PB, Rows in a table: 2^64, table Indexes: 32, column size: 16MB~4GB, columns in a table: 1,000 , Row size: 4GB

12. Give the syntax of Grant and Revoke commands?

The generic syntax for grant is as following > GRANT [rights] on [database/s] TO [username@hostname] IDENTIFIED BY [password] now rights can be a) All privilages b) combination of create, drop, select, insert, update and delete etc. we can grant rights on all databse by usingh *.* or some specific database by database.* or a specific table by database.table_name username@hotsname can be either username@localhost, username@hostname and username@% where hostname is any valid hostname and % represents any name, the *.* any condition password is simply the password of user The generic syntax for revoke is as following > REVOKE [rights] on [database/s] FROM [username@hostname] now rights can be as explained above a) All privilages b) combination of create, drop, select, insert, update and delete etc. username@hotsname can be either username@localhost, username@hostname and username@% where hostname is any valid hostname and % represents any name, the *.* any condition