MySQL interview questions and answers 👇

  1. MySQL Interview Questions


MySQL Interview Questions

What is the difference between MySQL and SQL?

View answer

SQL is a query programming language that manages RDBMS. MySQL is a relational database management system that uses SQL. SQL is primarily used to query and operate database systems. MySQL allows you to handle, store, modify and delete data and store data in an organized way.

How to add columns in MySQL?

View answer

To add a new column to an existing table, you use the ALTER TABLE ADD COLUMN statement as follows:

ALTER TABLE table ADD [COLUMN] column_name column_definition [FIRST|AFTER existing_column];

What are foreign keys in MySQL?

View answer

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables. A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table. The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.

How to delete columns in MySQL?

View answer

The syntax to drop a column in a table in MySQL (using the ALTER TABLE statement) is: ALTER TABLE table_name DROP COLUMN column_name; table_name.

How to delete a row in MySQL?

View answer

To delete rows in a MySQL table, use the DELETE FROM statement: DELETE FROM products WHERE product_id=1; The WHERE clause is optional, but you'll usually want it, unless you really want to delete every row from the table.

What is MySQL Workbench?

View answer

MySQL Workbench is a bound together visual instrument for database modelers, designers, and DBAs. MySQL Workbench provides Data modelling, SQL, and server setup set of administrative tools. Basically MySQL workbench makes it possible to operate the database management system through GUI.

How to create a View in MySQL?

View answer

The basic syntax for creating a view in MySQL is as follows:

CREATE VIEW [db_name.]view_name [(column_list)] AS select-statement;

How to create a Trigger in MySQL?

View answer

We can create a new trigger in MySQL by using the CREATE TRIGGER statement. It is to ensure that we have trigger privileges while using the CREATE TRIGGER command. The following is the basic syntax to create a trigger:

CREATE TRIGGER trigger_name trigger_time trigger_event
ON table_name FOR EACH ROW
BEGIN
--variable declarations
--trigger code
END;

How does indexing work in MySQL?

View answer

In MySQL, there are different index types, such as a regular INDEX, a PRIMARY KEY, or a FULLTEXT index. You can achieve fast searches with the help of an index. Indexes speed up performance by ordering the data on disk to make it quicker to find your result or telling the SQL engine where to go to find your data.

What is the difference between TRUNCATE and DELETE in MySQL?

View answer

SQL Truncate command places a table and page lock to remove all records. Delete command logs entry for each deleted row in the transaction log. The truncate command does not log entries for each deleted row in the transaction log. Delete command is slower than the Truncate command.

What is the heap table?

View answer

A heap table refers to a table created with the MEMORY storage engine. MySQL supports several storage engines that store data in different ways. The MEMORY storage engine does not persist any data to disk; it holds data in RAM. If the MySQL server stops, all data in RAM is lost.

What is REGEXP?

View answer

MySQL REGEXP performs a pattern match of a string expression against a pattern. The pattern is supplied as an argument. If the pattern finds a match in the expression, the function returns 1, else it returns 0. If either expression or pattern is NULL, the function returns NULL.

What is the save point in MySQL?

View answer

The SAVEPOINT in MySQL is used for dividing (or) breaking a transaction into multiple units so that the user has a chance of roll backing the transaction up to a specified point. That means using Save Point we can roll back a part of a transaction instead of the entire transaction.

How do you backup a database in MySQL?

View answer

We can generate the backup of the MySQL database using any of the following methods:

  • Generate the backup using mysqldump utility
  • Generate Incremental backups using Binary Log
  • Generate backups using the Replication of Slaves

What is the usage of ENUMs in MySQL?

View answer

The ENUM data type in MySQL is a string object. It allows us to limit the value chosen from a list of permitted values in the column specification at the time of table creation. It is short for enumeration, which means that each column may have one of the specified possible values.

What is MySQL data directory?

View answer

The mysql directory corresponds to the mysql system schema, which contains information required by the MySQL server as it runs. This database contains data dictionary tables and system tables.

What is InnoDB?

View answer

InnoDB is a general-purpose storage engine that balances high reliability and high performance. In MySQL 5.6, InnoDB is the default MySQL storage engine. Unless you have configured a different default storage engine, issuing a CREATE TABLE statement without an ENGINE clause creates an InnoDB table.

What are federated tables?

View answer

Federated is a storage engine for the MySQL MariaDB relational database management system that allows to create a table that is a local representation of a foreign (remote) table. It uses the MySQL client library API as a data transport, treating remote tables as if they were located on the local server.

What is the difference between primary key and candidate key?

View answer

Primary Key is a unique and non-null key which identify a record uniquely in table. A table can have only one primary key. Candidate key is also a unique key to identify a record uniquely in a table but a table can have multiple candidate keys.

Why is MySQL so popular?

View answer

MySQL can run on very modest hardware and puts very little strain on system resources; many small users serve up information to their organizations by running MySQL on modest desktop systems. The speed with which it can retrieve information has made it a longstanding favorite of web administrators.

What are the different ways to join tables in MySQL?

View answer

  • INNER JOIN: Returns records that have matching values in both tables
  • LEFT JOIN: Returns all records from the left table, and the matched records from the right table
  • RIGHT JOIN: Returns all records from the right table, and the matched records from the left table
  • CROSS JOIN: Returns all records from both tables

What is CHAR and VARCHAR in MySQL?

View answer

CHAR is fixed length while VARCHAR is variable length. That means, a CHAR(x) string has exactly x characters in length, including spaces. A VARCHAR(x) string can have up to x characters and it cuts off trailing spaces, thus might be shorter than the declared length.

What is a LIKE statement?

View answer

The MySQL LIKE condition allows wildcards to be used in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement. This allows you to perform pattern matching.

How does DISTINCT work in MySQL?

View answer

When querying data from a table, you may get duplicate rows. To remove these duplicate rows, you use the DISTINCT clause in the SELECT statement.

You can specify one or more columns that you want to select distinct values after the SELECT DISTINCT keywords.

  • If you specify one column, the DISTINCT clause will evaluate the uniqueness of rows based on the values of that column.
  • If you specify two or more columns, the DISTINCT clause will use the values of these columns to evaluate the uniqueness of the rows.

Is there any upper limit for the number of columns in a table?

View answer

MySQL has hard limit of 4096 columns per table, but the effective maximum may be less for a given table. The exact column limit depends on several factors: The maximum row size for a table constrains the number (and possibly size) of columns because the total length of all columns cannot exceed this size.

How would you make a connection persistent in MySQL?

View answer

While making a connection request, if Mysql_pconnect is used rather than mysql_connect, then it can make the connection persistent. Here ‘p’ means persistent. The database connection is not closed every time.

What are the Numeric Data Types in MySQL?

View answer

MySQL has numeric data types for integer, fixed-point, floating-point, and bit values. Numeric types can be signed or unsigned, except BIT. A special attribute enables the automatic generation of sequential integer or floating-point column values, which is useful for applications that require a series of unique identification numbers.

What is BLOB in MySQL?

View answer

A Binary Large Object (BLOB) is a MySQL data type that can store binary data such as images, multimedia, and PDF files.

What are Triggers in MySQL?

View answer

A trigger is a task that executes in response to some predefined database event, such as after a new row is added to a particular table. Specifically, this event involves inserting, modifying, or deleting table data, and the task can occur either prior to or immediately following any such event. Triggers can be used for

  • Audit Trails
  • Validation
  • Referential integrity enforcement

What is the logical architecture of MySQL?

View answer

The architecture is as follows:

  • The top layer contains the services most network-based client/server tools or servers need such as connection handling, authentication, security, and so forth.
  • The second layer contains much of MySQL’s brains. This has the code for query parsing, analysis, optimization, caching, and all the built-in functions.
  • The third layer contains the storage engines that are responsible for storing and retrieving the data stored in MySQL.

What are Transaction Storage Engines in MySQL?

View answer

The storage engine (part of PHP’s software component) is the tool that allows users to store and manage data in a database with SQL queries for various table types and different languages. With MySQL 5, it is used mostly for general purpose storage.