A list of top frequently asked MySQL interview questions and answers are given below :
What are the different tables present in MySQL?
There are many tables that remain present by default. But, MyISAM is the default database engine used in MySQL. There are five types of tables that are present:
- INNO DB
Mention the advantages of the InnoDB over MyISAM?
- The Advantages include,
- Row-level locking
- Crash Recovery
- Foreign key Constraints.
What is the difference between CHAR and VARCHAR?
A list of differences between CHAR and VARCHAR:
- CHAR is variable-length whereas VARCHAR is of fixed length.
- CHAR and VARCHAR types are different in storage and retrieval.
- CHAR column length is fixed to the length that is declared while creating a table. The length value ranges from 1 and 255.
- When CHAR values are stored when they are right-padded using spaces to a specific length. Trailing spaces are removed when CHAR values are retrieved.
- CHAR uses static memory allocation whereas VARCHAR uses dynamic memory allocation.
- CHAR is 50% faster than VARCHAR.
What is the difference between TRUNCATE and DELETE in MySQL?
TRUNCATE is a DDL command, DELETE is a DML command.
It is not possible to use Where command with TRUNCATE but you can use it with DELETE command.
TRUNCATE cannot be used with indexed views whereas DELETE can be used with indexed views.
The DELETE command is used to delete data from a table. It only deletes the rows of data from the table while, truncate is very dangerous command and should be used carefully because it deletes every row permanently from a table.
How many Triggers are possible in MySQL?
There are only six Triggers allowed to use in MySQL database.
- Before Insert
- After Insert
- Before Update
- After Update
- Before Delete
- After Delete
What is a trigger in MySQL?
A trigger is a set of codes that executes in response to some events.
What is heap table?
Tables that are present in memory is known as HEAP tables. When you create a heap table in MySQL, you should need to specify the TYPE as HEAP. These tables are commonly known as memory tables. They are used for high-speed storage on a temporary basis. They do not allow BLOB or TEXT fields.
What is the difference between heap table and temporary table?
Heap tables are found in memory. They are used for high-speed storage on a temporary basis. They do not allow BLOB or TEXT fields.
Heap tables do not support AUTO_INCREMENT.
Indexes should be NOT NULL.
The temporary tables are used to keep the transient data. Sometimes it is beneficial in cases to hold temporary data. The Temporary table is deleted after the current client session terminates.
The heap tables are shared among clients while temporary tables are not shared.
Heap tables are just another storage engine, while for temporary tables you need a special privilege (create temporary table).
What is the difference between FLOAT and DOUBLE?
FLOAT stores floating point numbers with accuracy up to 8 places and allocates 4 bytes, on the other hand DOUBLE stores floating point numbers with accuracy up to 18 places and allocates 8 bytes.
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.
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.
What is the difference between primary key and candidate key?
To identify each row of a table, a primary key is used. For a table, there exists only one primary key.
A candidate key is a column or a set of columns which can be used to uniquely identify any record in the database without having to reference any other data.