Most Asked MySQL Interview Questions and Answers
How many TRIGGERS are allowed in MySql table?
SIX triggers are allowed in MySql table. They are as follows:
- BEFORE INSERT
- AFTER INSERT
- BEFORE UPDATE
- AFTER UPDATE
- BEFORE DELETE and
- AFTER DELETE
How to represent ENUMs and SETs internally?
ENUMs and SETs are used to represent powers of two because of storage optimizations.
What is the difference between MyISAM Static and MyISAM Dynamic?
In MyISAM static all the fields will have fixed width. The Dynamic MyISAM table will have fields like TEXT, BLOB, etc. to accommodate the data types with various lengths.
MyISAM Static would be easier to restore in case of corruption.
What is the difference between mysql_fetch_array and mysql_fetch_object?
Following are the differences between mysql_fetch_array and mysql_fetch_object:
mysql_fetch_array() -Returns a result row as an associated array or a regular array from database.
mysql_fetch_object – Returns a result row as object from database.
What are the nonstandard string types?
Following are Non-Standard string types:
What is the usage of ENUMs in MySQL?
ENUM is a string object used to specify set of predefined values and that can be used during table creation.
Create table size(name ENUM(‘Small’, ‘Medium’,’Large’);
What are federated tables?
Federated tables which allow access to the tables located on other databases on other servers.
How can we run batch mode in mysql?
Following commands are used to run in batch mode:
What are all the Common SQL Function?
CONCAT(A, B) – Concatenates two string values to create a single string output. Often used to combine two or more fields into one single field.
FORMAT(X, D) – Formats the number X to D significant digits.
CURRDATE(), CURRTIME() – Returns the current date or time.
NOW() – Returns the current date and time as one value.
MONTH(), DAY(), YEAR(), WEEK(), WEEKDAY() – Extracts the given data from a date value.
HOUR(), MINUTE(), SECOND() – Extracts the given data from a time value.
DATEDIFF(A, B) – Determines the difference between two dates and it is commonly used to calculate age
SUBTIMES(A, B) – Determines the difference between two times.
FROMDAYS(INT) – Converts an integer number of days into a date value.
REGEXP is a pattern match in which matches pattern anywhere in the search value.
What, if a table has one column defined as TIMESTAMP?
Timestamp field gets the current timestamp whenever the row gets altered.
Where MyISAM table will be stored and also give their formats of storage?
Each MyISAM table is stored on disk in three formats:
- The ‘.frm’ file stores the table definition
- The data file has a ‘.MYD’ (MYData) extension
- The index file has a ‘.MYI’ (MYIndex) extension
Explain Access Control Lists.
An ACL (Access Control List) is a list of permissions that is associated with an object. This list is the basis for MySQL server’s security model and it helps in troubleshooting problems like users not being able to connect.
MySQL keeps the ACLs (also called grant tables) cached in memory. When a user tries to authenticate or run a command, MySQL checks the authentication information and permissions against the ACLs, in a predetermined order.
Difference between CHAR and VARCHAR?
Following are the differences between CHAR and VARCHAR:
- CHAR and VARCHAR types differ in storage and retrieval
- CHAR column length is fixed to the length that is declared while creating table. The length value ranges from 1 and 255
- When CHAR values are stored then they are right padded using spaces to specific length. Trailing spaces are removed when CHAR values are retrieved.
What happens when the column is set to AUTO INCREMENT and if you reach maximum value in the table?
It stops incrementing. Any further inserts are going to produce an error, since the key has been used already.
What are the different tables present in MySQL?
Total 5 types of tables are present:
- INNO DB
MyISAM is the default storage engine as of MySQL .