MySQL Interview Questions
In this article on frequently asked MySQL Interview Questions, I am going to discuss the top questions related to MySQL that may be asked in your interview. These questions are self-picked after doing some good research and my personal experience in this field. For your better understanding, I have divided the article into the following sections:
1. What is Database?
A database is an organized collection of data, generally stored in a central location and accessed electronically from a computer system.
2. What is the database model?
A database model is a type of data model that determines the logical structure of a database.
3. Which type of database management system represents relations using tables?
Relational DBMS. This a common type of database which stores data in forms of tables. Relational DBMS is preferred to use because its characteristic that one or more table record may be linked to one or more record in another table.
4. Does MySQL support multiple database storage engines?
Yes, MySQL supports multiple database storage engines.
For example: InnoDB, MyISAM, MEMORY, MERGE, CSV, FEDERATED etc.
5. What is the logic model of a database?
Logic model of a database is concerned with developing the database model taking into consideration the physical implementation.
MySQL Database Interview Questions
6. Is database development lifecycle important to the implementation of a database system?
Yes. The database development lifecycle is very important to the successful implementation of a database system and to make dependencies logical.
The three main types of normalization are 1NF, 2NF, and 3NF.
The other types of normalization (rarely used) are BCNF, 4NF, 5NF.
8. What is Normalization?
Normalization is a database design technique used to minimize data redundancy and duplication of data in the database.
9. What is a composite key?
A composite key is a primary key that consists of more than one field that uniquely identifies a record.
10. What is the default port for MySQL server?
The default port for MySQL Server is 3306. Apart from this, another standard default port for the SQL Server in TCP/IP is 1433.
PHP MySQL Interview Questions
11. Do you know about the different set operations available in MySQL?
The various set operations available in MySQL are as follows:
- UNION – This operation returns combined rows from multiple SELECT statements into a single result set except duplicate rows.
- UNION ALL – This operation returns all the rows selected by SELECT statements and this result is inclusive of all duplicate rows.
- MINUS – This operation returns all the distinct rows selected by the SELECT statement but does not select the rows selected by the second SELECT statement.
- INTERSECT – This operation returns all the distinct rows selected by multiple queries.
12. Explain the order of SQL SELECT statement?
The order of a SQL SELECT statement is as follows:
- SELECT < columns >
- FROM < table >
- WHERE < condition > (optional)
- GROUP BY < column > (optional)
- HAVING < condition > (optional)
- ORDER BY < column name/ coumn alias > (optional)
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.
13. Can you list the data types supported in MySQL?
A complete list of categorized MySQL data types is as follows:
- Numeric – TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE, REAL, BIT, BOOLEAN, SERIAL
- Date and time – DATE, DATETIME, TIMESTAMP, TIME, YEAR
- String – CHAR, VARCHAR, TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, BINARY, VARBINARY, TINYBLOB, MEDIUMBLOB, BLOB, LONGBLOB, ENUM, SET
- Spatial – GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION
14. Can you encrypt and decrypt the data present in MySQL? How?
Yes, it is possible to encrypt and decrypt the data present in MySQL with the help of MySQL functions AES_ENCRYPT() and AES_DECRYPT().
We can use AES_ENCRYPT() to encrypt the data with a key and in case of decryption, we have to use AES_DECRYPT() with the same key. Read More.
15. How will you check for NULL values in a database?
A NULL value is a field when no value present in that particular field. Since the NULL value cannot be compared to any other NULL values, we cannot use the comparison operators such as =, <, or <>. To compare the fields with NULL values, we have to use IS NULL and IS NOT NULL operator to check if the column is NULL or NOT NULL respectively.
Below is the Syntax of IS NULL and IS NOT NULL.
/** To check NULL **/
SELECT <column_names> FROM <table_name> WHERE <column_name> IS NULL;
/** To check Not NULL **/
SELECT <column_names> FROM <table_name> WHERE <column_name> IS NOT NULL;