MYSQL Interview Questions

To create a database in MySQL, you can use the CREATE DATABASE statement followed by the desired database name. This command initializes a new database within your MySQL server.

Syntax:

CREATE DATABASE mydatabase;

This will create a new database named mydatabase. After creation, you can use the USE mydatabase; command to start working inside the new database context.

SQL (Structured Query Language) is a standard language used to manage and manipulate relational databases. MySQL is a popular open-source relational database management system (RDBMS) that uses SQL to query data. The table below highlights the key differences between them.

MySQL supports various types of tables depending on the storage engine used. A storage engine is responsible for handling the SQL operations for different types of tables. The commonly used storage engines in MySQL are:

• InnoDB – The default storage engine in MySQL. It supports transactions, row-level locking, and foreign key constraints.
• MyISAM – A legacy storage engine that is fast for read-heavy operations but lacks support for transactions and foreign keys.
• MEMORY – Stores all data in memory for fast access. Suitable for temporary tables.
• CSV – Stores data in CSV format files. Easy for data import/export.
• ARCHIVE – Optimized for storing large amounts of infrequently accessed, read-only data.

Each of these storage engines creates different types of tables based on their features and use cases. You can specify the storage engine when creating a table using the ENGINE keyword.

In MySQL, the LIKE statement is used for pattern matching in queries. Two special wildcard characters used with LIKE are % and _:

• %: Represents zero, one, or multiple characters. It is used when we want to match any sequence of characters.
• _: Represents a single character. It matches exactly one character in that position.

These wildcards are useful when filtering records based on partial matches.

Example:

SELECT * FROM Employees WHERE Name LIKE 'J%n';
-- Matches names starting with 'J' and ending with 'n', like 'John' or 'Jordan'

SELECT * FROM Employees WHERE Name LIKE 'J_n';
-- Matches names starting with 'J' and exactly one character after it, like 'Jon'

In MySQL, a table can have up to 16 indexed columns. This means you can define indexes on up to 16 different columns within a single table. Indexes help improve query performance by allowing faster lookups based on the indexed columns.

In MySQL, there are six main string data types available for columns:

• CHAR: Fixed-length string.
• VARCHAR: Variable-length string.
• TEXT: Large text data with different sizes (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT).
• BLOB: Binary Large Object for storing binary data like images or files.
• ENUM: A string object with a predefined set of values.
• SET: A string object that can hold zero or more values chosen from a list of permitted values.

BLOB (Binary Large Object) is used to store large amounts of binary data such as images, audio, or other multimedia files. BLOB data is case-sensitive and stored in a binary format, meaning sorting and comparison are done byte-by-byte. It is best suited for non-text binary data.

Types of BLOB: TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB

TEXT is used to store large amounts of text data. Unlike BLOB, TEXT values are case-insensitive and sorted lexicographically based on the character set. It is suitable for storing long strings, paragraphs, or textual data.

Types of TEXT: TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT

The WHERE clause is used to filter rows before any grouping is performed. It works on individual records and is used with SELECT, UPDATE, or DELETE statements to filter the results based on specific conditions.

The HAVING clause is used to filter groups after the GROUP BY clause has been applied. It operates on aggregated data and is commonly used with functions like COUNT, SUM, AVG, etc.

Key Difference: WHERE filters rows before grouping, while HAVING filters after grouping.

Example:

SELECT department, COUNT(*) as total_employees
FROM employees
WHERE active = 1
GROUP BY department
HAVING total_employees > 5;

REGEXP (Regular Expression) is used in MySQL to perform advanced pattern matching within string values. It allows for flexible search criteria using regular expression syntax, enabling you to match complex string patterns efficiently.

By default, REGEXP is case-insensitive and can match the specified pattern anywhere in the string.

Syntax:

SELECT * FROM table_name WHERE column_name REGEXP 'pattern';

Example: To find all names starting with 'A' and ending with 'n':

SELECT * FROM users WHERE name REGEXP '^A.*n$';

A column in MySQL is a vertical structure in a table that stores data for each row. To add a new column to an existing table, you can use the ALTER TABLE statement. This allows you to specify the column name, data type, and optionally, where to place the column (either at the beginning or after an existing column).

Syntax:

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

Example: To add a VARCHAR column named email after the name column:

ALTER TABLE users
ADD COLUMN email VARCHAR(255) AFTER name;

In MySQL, you can delete one or more columns from an existing table using the ALTER TABLE statement followed by the DROP COLUMN clause. This command permanently removes the specified columns and their data from the table.

Syntax:

ALTER TABLE table_name
DROP COLUMN column1,
DROP COLUMN column2;

Example: To delete a column named email from the users table:

ALTER TABLE users DROP COLUMN email;

In MySQL, you can delete an entire table using the DROP TABLE statement. This command permanently removes the table structure and all of its data from the database. Once executed, the table and its data cannot be recovered unless backed up beforehand.

Syntax:

DROP TABLE table_name;

Example: To delete a table named employees:

DROP TABLE employees;

The NOW() and CURRENT_DATE() functions in MySQL are both used to retrieve the current date and/or time, but they differ in what they return.

NOW(): This function returns the current date and time, including hours, minutes, and seconds.

SELECT NOW();

CURRENT_DATE(): This function returns only the current date (year, month, and day) without the time component.

SELECT CURRENT_DATE();

Use NOW() when you need the full timestamp and CURRENT_DATE() when you only need the date.

The DISTINCT keyword in MySQL is used to eliminate duplicate rows from the result set. When applied to a SELECT statement, it ensures that only unique combinations of the specified columns are returned.

Syntax:

SELECT DISTINCT column1, column2, ...
FROM table_name;

Example: If a column contains duplicate values and you only want each unique value once in the result set, DISTINCT is useful to filter them out automatically.

To find duplicate rows in a MySQL table, you can use the GROUP BY clause along with HAVING COUNT() to identify records with duplicate values in specific columns.

Example Query:

SELECT Name, Category
FROM Product
GROUP BY Name, Category
HAVING COUNT(*) > 1;

This query groups the rows based on Name and Category columns and returns only those combinations that appear more than once, indicating duplicates.

MySQL supports three main types of relationships between tables to organize and connect data efficiently:

1. One-to-One: In this relationship, each row in one table is linked to only one row in another table. It can be implemented using a unique foreign key in one of the tables.

2. One-to-Many: This is the most common relationship where a row in one table is related to multiple rows in another table. It is established by placing a foreign key in the table that represents the 'many' side.

3. Many-to-Many: In this relationship, multiple rows in one table are associated with multiple rows in another table. It is implemented using a junction (bridge) table that contains foreign keys referencing both tables.

We can use the INSERT statement to insert a date into a MySQL table. MySQL's default date format is YYYY-MM-DD. MySQL also provides several date-related data types such as:

• DATE
• DATETIME
• TIMESTAMP
• YEAR

Example Syntax:

INSERT INTO table_name (column_name, column_date)
VALUES ('DATE: Manual Date', '2023-05-20');

Make sure the column is defined with a compatible date type like DATE or DATETIME.

JOIN in MySQL is used to combine rows from two or more tables based on a related column between them. It helps in retrieving meaningful data from multiple tables using relationships defined by keys.

Types of JOIN in MySQL:

1. INNER JOIN: Returns only the rows where there is a match in both tables.

SELECT * FROM table1
INNER JOIN table2 ON table1.id = table2.id;

2. LEFT JOIN (LEFT OUTER JOIN): Returns all records from the left table and the matched records from the right table. If there is no match, NULL is returned from the right side.

SELECT * FROM table1
LEFT JOIN table2 ON table1.id = table2.id;

3. RIGHT JOIN (RIGHT OUTER JOIN): Returns all records from the right table and the matched records from the left table. If there is no match, NULL is returned from the left side.

SELECT * FROM table1
RIGHT JOIN table2 ON table1.id = table2.id;

4. FULL JOIN: MySQL does not support FULL JOIN natively. However, you can simulate it using UNION of LEFT and RIGHT JOINs.

SELECT * FROM table1
LEFT JOIN table2 ON table1.id = table2.id
UNION
SELECT * FROM table1
RIGHT JOIN table2 ON table1.id = table2.id;

A primary key in MySQL is a column or a group of columns that uniquely identifies each record in a table. It ensures that no two rows have the same value in the primary key column(s) and that the column does not contain NULL values.

To remove or drop a primary key from a table, you can use the ALTER TABLE statement with the DROP PRIMARY KEY clause.

Syntax:

ALTER TABLE table_name DROP PRIMARY KEY;

This command removes the primary key constraint from the specified table. Ensure the column does not have any dependent constraints before executing.

The primary key is a column (or a set of columns) in a table that uniquely identifies each row. It ensures that no two rows can have the same value in the primary key column(s), and it does not allow NULL values.

The foreign key is a column (or a set of columns) in one table that refers to the primary key in another table. It is used to maintain the referential integrity between two related tables.

Example:

-- Create a 'Departments' table with 'dept_id' as PRIMARY KEY
CREATE TABLE Departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(100)
);

-- Create an 'Employees' table with 'emp_id' as PRIMARY KEY
-- and 'dept_id' as FOREIGN KEY referring to Departments
CREATE TABLE Employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);

In this example, Departments.dept_id is a primary key because it uniquely identifies each department. Employees.dept_id is a foreign key because it refers to the dept_id in the Departments table, thus linking each employee to their department.

The DELETE command is used to remove specific rows from a table based on a WHERE condition. It is part of the Data Manipulation Language (DML) and can be rolled back if used within a transaction.

On the other hand, the TRUNCATE command is used to delete all rows from a table. It is part of the Data Definition Language (DDL) and performs the deletion much faster than DELETE by deallocating the data pages directly. However, it cannot be rolled back in most cases and does not fire triggers.

Example:

-- Deletes specific rows matching the condition
DELETE FROM employees WHERE department = 'HR';

-- Deletes all rows from the table, resets auto-increment counter
TRUNCATE TABLE employees;

Key Differences:

• DELETE can delete specific rows; TRUNCATE deletes all rows.
• DELETE is DML and supports WHERE clause; TRUNCATE is DDL and doesn’t.
• DELETE can be rolled back; TRUNCATE usually cannot.
• DELETE fires triggers; TRUNCATE does not.

InnoDB is a widely used storage engine for MySQL databases. It is known for providing high reliability and performance, making it the default storage engine in MySQL.

InnoDB supports essential database features such as:

• ACID compliance – Ensures data integrity through atomicity, consistency, isolation, and durability.
• Row-level locking – Improves performance in multi-user environments by locking only the necessary rows instead of entire tables.
• Foreign key constraints – Maintains referential integrity between tables.

InnoDB is owned and maintained by Oracle Corporation and is designed to handle large-scale transactional applications with high reliability and fault tolerance.

In MySQL, a TIMESTAMP is a data type that automatically records the date and time when a row is created or updated. It is commonly used for tracking changes in a table and maintaining audit logs.

When a column is defined with the TIMESTAMP type and set to DEFAULT CURRENT_TIMESTAMP, it will automatically store the current date and time when a new row is inserted. Additionally, if defined with ON UPDATE CURRENT_TIMESTAMP, it will also update the timestamp whenever the row is modified.

Example:

CREATE TABLE logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    message VARCHAR(255),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

This ensures the updated_at column always reflects the most recent time of change for each row.

ENUM is a special string object data type in MySQL that allows you to define a column with a set of predefined values. It is useful when a column should only contain one value from a fixed list, such as days of the week, status flags, or sizes.

Using ENUM helps to improve data integrity by restricting input values and can also reduce storage space compared to storing full strings.

Example:

CREATE TABLE size (
    name ENUM('Small', 'Medium', 'Large')
);

In this example, the name column can only accept the values 'Small', 'Medium', or 'Large'. Any other value will result in an error.

A heap table in MySQL, also known as a MEMORY table, is a type of table that stores all its data in memory rather than on disk. It is primarily used for fast, temporary data storage due to its high-speed access capabilities.

However, heap tables come with several limitations:

• No BLOB or TEXT fields: These data types are not allowed in heap tables.
• Limited operators: Only basic comparison operators such as =, <, >, <=, and >= can be used in queries.
• No AUTO_INCREMENT: Heap tables do not support auto-incremented columns.
• NOT NULL indexes: All indexed columns must be declared as NOT NULL.

Due to being stored in memory, data in heap tables is lost when the MySQL server restarts.

The maximum size of a heap table (also known as a MEMORY table) in MySQL can be controlled using the max_heap_table_size configuration variable. This setting defines the upper limit for the amount of memory that a single heap table can use.

To set the maximum heap table size, you can use the following command:

SET max_heap_table_size = 16777216;  -- sets limit to 16 MB

Note: This setting only applies to tables created using the MEMORY storage engine and must be set before creating the heap table. Also, this value can be set in the configuration file (my.cnf or my.ini) for permanent effect.

A view in MySQL is a virtual table that does not store data itself but displays data stored in one or more underlying tables. It acts as a stored SQL query that simplifies complex queries and enhances security by restricting access to specific rows or columns. Unlike tables, views do not physically exist in storage; they are dynamically generated upon query execution. If the underlying table data changes, the view automatically reflects those changes, since it depends on real-time data.

You can create a view using the following syntax:

CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

This command defines a view named view_name that selects specific columns from a table, optionally filtered by a condition.

In MySQL, images can be stored using the BLOB (Binary Large Object) data type. To save an image in the database, the image file must first be read and converted into binary format. This binary data is then inserted into a table column with a BLOB data type using an INSERT query. Although images can be stored directly in the database, it is generally recommended to store only the image path or URL in the database and save the actual image on the server’s filesystem for performance reasons.

Here's an example of the SQL syntax to store image data in a BLOB column:

CREATE TABLE Images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    data LONGBLOB
);

Then, the image can be inserted using a script (like Python, PHP, etc.) that reads the file in binary mode and inserts it into the table.

A trigger in MySQL is a set of procedural statements that are automatically executed in response to certain events on a specific table, such as inserting, updating, or deleting rows. Triggers help enforce rules, log changes, or automate system tasks without manual intervention. They are particularly useful for maintaining consistent data and implementing business logic at the database level.

MySQL supports six types of triggers, which are classified based on the timing (BEFORE or AFTER) and the event (INSERT, UPDATE, DELETE):

1. BEFORE INSERT
2. AFTER INSERT
3. BEFORE UPDATE
4. AFTER UPDATE
5. BEFORE DELETE
6. AFTER DELETE

Each trigger is associated with a table and executes automatically when the defined event occurs, ensuring actions are taken consistently across all relevant operations.

Normalization is a database design technique used to eliminate data redundancy and ensure data integrity. It involves organizing data in such a way that duplication is minimized and logical dependencies are properly enforced.

There are multiple levels of normalization, known as normal forms. Each normal form builds upon the previous one, and the first three are commonly used in practice:

1. First Normal Form (1NF):
   - Ensures that the table has no repeating groups or arrays. Each field should contain only atomic (indivisible) values.

2. Second Normal Form (2NF):
   - Builds on 1NF and ensures that all non-key attributes are fully functionally dependent on the entire primary key.

3. Third Normal Form (3NF):
   - Builds on 2NF and ensures that all non-key attributes are only dependent on the primary key, not on any other non-key attribute.

By applying these normal forms, the database becomes more efficient, consistent, and easier to maintain over time.

Indexes in MySQL can be created using several different methods. Indexes improve the speed of data retrieval operations on a table at the cost of additional storage and slower write operations. Here are the various ways to create an index:

• Using T-SQL Statements: We can explicitly create an index using SQL commands like CREATE INDEX or ALTER TABLE with the ADD INDEX clause.
• Using SQL Server Management Studio (SSMS): In SQL Server (not MySQL), we can create indexes visually by navigating to the table, right-clicking the Indexes node, and selecting New Index. However, MySQL Workbench or phpMyAdmin provides similar GUI tools for creating indexes.
• Via PRIMARY KEY or UNIQUE constraints: Defining a PRIMARY KEY or UNIQUE constraint automatically creates a unique index on the specified column(s) during table creation or alteration.

All these methods help improve performance for search and filter operations in large datasets.

Clustered Index:
A clustered index is a type of index that determines the physical order of data in a table. In other words, the rows in the table are stored on disk in the same order as the clustered index. Each table can have only one clustered index because the data rows themselves can only be sorted in one order. This type of index improves performance for range queries and sorting operations.

Non-Clustered Index:
A non-clustered index is a type of index where the data is stored separately from the index. The index contains pointers to the actual rows in the table. This allows for multiple non-clustered indexes per table and is useful for queries that filter or sort by columns other than the primary key.

The key difference is that a clustered index changes the way records are stored in the database, while a non-clustered index does not affect the physical order of the table’s data.