MySQL is an open-source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL). Companies including GitHub, NASA, NetFlix, Twitter, YouTube use this popular database management system to take advantage of its intuitive features such as robust transactional support, scalability, flexibility etc.
Here is a list of top 15 MySQL interview questions one must prepare in 2021.
1| What are ACLs in MySQL?
Solution: An Access Control List or ACL is a list of permissions associated with a system resource. MySQL utilises security based on the ACLs for all connections and similar operations.
Know more here.
2| Difference between CHAR and VARCHAR in MySQL
Solution: CHAR is fixed length while VARCHAR is variable length. The CHAR and VARCHAR vary in the way they are stored as well as retrieved. They also differ in the maximum length. Unlike VARCHAR, the length of a CHAR column is fixed to the dimension one declares while creating the table.
Know more here.
3| How does DISTINCT work in MySQL? How MySQL Optimises DISTINCT?
Solution: MySQL DISTINCT clause is usually used to eliminate the duplicate records from the table and fetch only the unique records. It is only used with the SELECT statement in MySQL. In most cases, a DISTINCT clause can be considered as a special case of GROUP BY. Due to this equivalence, the optimisations applicable to GROUP BY queries can be also applied to queries with a DISTINCT clause.
Know more here.
4| How do you differentiate between Oracle and MySQL?
Solution: Oracle is a multi-model database with a single, integrated back-end, while MySQL is an open-source relational database management system (RDBMS). Oracle supports multiple data models like document, graph, relational, and key-value within the database. On the other hand, MySQL uses tables, constraints, triggers, roles, stored procedures and views as the core components.
Know more here.
5| How to test for NULL values in a database?
Solution: A null value is usually utilised in databases to signify a missing or unknown value. MySQL interprets the NULL value differently from other data types. To compare the fields with NULL values, one must use the “IS NULL” or “IS NOT NULL” operator.
Know more here.
6| What is a foreign key? How to implement the same in MySQL?
Solution: A foreign key is used to link two tables together. MySQL supports foreign keys, that permit cross-referencing related data across tables, and foreign key constraints, which help keep the related data consistent.
The essential syntax for defining a foreign key constraint in a CREATE TABLE or ALTER TABLE statement includes the following:
[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name, …)
REFERENCES tbl_name (col_name,…)
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
Know more here.
7| What is Database White Box Testing & Black Box Testing?
Solution: Database testing is considered to be the most significant part of software testing. It helps in examining the overall functionality of a particular software. The white box testing essentially deals with the internal structure of a specific database, where the specification details are usually hidden from the user. On the other hand, the black box testing usually entails the testing of interfaces, which is followed by the integration of the database, including the mapping of data, verifying the incoming data and other such.
Know more here.
8| Which drivers are necessary for MySQL?
Solution: MySQL provides standards-based drivers for JDBC, ODBC, and .Net enabling developers to build database applications in their language of choice. The important drivers developed and maintained by the MySQL Community are-
- ADO.NET Driver for MySQL (Connector/NET)
- ODBC Driver for MySQL (Connector/ODBC)
- JDBC Driver for MySQL (Connector/J)
- C Driver for MySQL (Connector/C)
- C API for MySQL (mysqlclient)
- C++ Driver for MySQL (Connector/C++)
- Python Driver for MySQL (Connector/Python)
- Node.js Driver for MySQL (Connector/Node.js)
Know more here.
9| What is a trigger in MySQL? How can we use it?
Solution: A trigger is a named database object associated with a table. It kicks in when a particular event occurs in the table. Applications for triggers include perform checks of values to be slotted into a table or to perform calculations on values linked to an update. A trigger is defined to activate when a statement updates, deletes or insert rows in the associated table. Such operations are known as the trigger events.
Know more here.
10| How can we run MySQL in batch mode?
Solution: To run MySQL in batch mode, you need to write the following statement:
shell> mysql < batch-file
If you are running mysql under Windows and have some special characters in the file that cause problems, you can do this:
C:\> mysql -e “source batch-file”
Know more here.
11| What is the default port for MySQL Server?
Solution: The default port for MySQL server is 3306.
12| What are the various types of tables present in Mysql?
Solution: MySQL supports two different kinds of tables: transaction-safe tables that include InnoDB; and BDB and non-transaction-safe tables that include HEAP, ISAM, MERGE, and MyISAM). The default table type in MySQL is MyISAM.
When you create a new table, you can tell MySQL the table type it should use. MySQL will always create a .frm file to hold the table and column definitions. Depending on the table type, the index and data will be stored in other files.
Know more here.
13| How can you convert timestamps to date time in MySQL?
Solution: One can convert the timestamp to date time with the help of FROM_UNIXTIME() function.
14| How to add columns to a table in MySQL
Solution: To add a new column to an existing table, you can use the ALTER TABLE ADD COLUMN statement as follows:
ALTER TABLE table
ADD [COLUMN] column_name column_definition [FIRST|AFTER existing_column];
To add two or more columns to a table at the same time, you can write the following:
ALTER TABLE table
ADD [COLUMN] column_name_1 column_1_definition [FIRST|AFTER existing_column],
ADD [COLUMN] column_name_2 column_2_definition [FIRST|AFTER existing_column],
…;
Know more here.
15| How to import and export MySQL databases?
Solution: You can export a MySQL database to a file by using phpMyAdmin or the mysqldump table/database command line program. Also, the MySQL database can be exported from the command line using the mysqldump table/database program.
On the other hand, after creating a new database in cPanel, one can import the contents of the database by using phpMyAdmin or the mysql command line program.
Know more here.
