10 Basic MySQL Interview Questions for Database Administrators

As a database administrator, you need to be well-versed in MySQL, one of the most popular open-source database management systems. Whether you are a beginner or an experienced professional, there are some basic MySQL interview questions that you should be prepared to answer. In this article, we'll cover some of the most common questions and provide examples to help you prepare for your next interview.

What is MySQL?

MySQL is an open-source relational database management system (RDBMS) that uses SQL (Structured Query Language) to manage and manipulate data. It was first released in 1995 and is currently owned by Oracle Corporation. MySQL is widely used in web applications, especially those built with PHP, and is known for its speed, reliability, and ease of use.

What are the Different Data Types Supported by MySQL?

MySQL supports several data types, including −

• Numeric data types − INT, BIGINT, FLOAT, DOUBLE, DECIMAL.

• Date and time data types − DATE, TIME, DATETIME, TIMESTAMP.

• String data types − CHAR, VARCHAR, TEXT, BLOB.

• Other data types − BOOLEAN, ENUM, SET.

Here are some examples −

• INT − Represents a whole number (e.g. 5, 10, 100).

• VARCHAR − Represents variable-length character strings (e.g. 'hello', 'world').

• TEXT − Represents large text values (e.g. blog posts, articles).

• DATE − Represents a date (e.g. '2023-04-03').

• TIMESTAMP − Represents a date and time (e.g. '2023-04-03 14:30:00').

What is a Primary Key in MySQL?

A primary key is a unique identifier for a row in a MySQL table. It is used to ensure that each row in a table is uniquely identifiable and can be accessed quickly. A primary key can consist of one or more columns, but it must be unique and cannot be null.

Here's an example −

CREATE TABLE users (
   id INT PRIMARY KEY,
   name VARCHAR(255) NOT NULL,
   email VARCHAR(255) NOT NULL UNIQUE
);

In this example, the 'id' column is the primary key for the 'users' table. It ensures that each user is uniquely identifiable by their ID.

What is a Foreign Key in MySQL?

A foreign key is a column or set of columns in a MySQL table that refers to the primary key of another table. It is used to establish a relationship between two tables and ensure that data is consistent between them. A foreign key constraint can be added to a table to enforce referential integrity.

Here's an example −

CREATE TABLE orders (
   id INT PRIMARY KEY,
   user_id INT,
   product_id INT,
   FOREIGN KEY (user_id) REFERENCES users(id),
   FOREIGN KEY (product_id) REFERENCES products(id)
);

In this example, the 'user_id' and 'product_id' columns are foreign keys that refer to the 'id' column in the 'users' and 'products' tables, respectively. This ensures that orders are associated with valid users and products.

What is a View in MySQL?

A view in MySQL is a virtual table that is based on the result of a SELECT statement. Views are often used to simplify complex queries and provide an abstracted view of the underlying data.

Here's an example −

CREATE VIEW user_orders AS
SELECT u.name, o.product_name, o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id;

In this example, we've created a view called 'user_orders' that returns the name of the user, the name of the product, and the order date for all orders. The view is based on a JOIN between the 'users' and 'orders' tables.

What is the Difference Between INNER JOIN and LEFT JOIN in MySQL?

Both INNER JOIN and LEFT JOIN are types of JOIN operations used to combine rows from two or more tables. The main difference between the two is that INNER JOIN only returns rows that have matching values in both tables, while LEFT JOIN returns all rows from the left table (the first table listed in the JOIN statement) and matching rows from the right table.

Here's an example −

Suppose we have two tables, 'users' and 'orders'. The 'users' table contains information about users, while the 'orders' table contains information about orders placed by those users.

To get a list of all users and their corresponding orders, we could use a LEFT JOIN −

SELECT u.name, o.product_name, o.order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

This query would return all users, regardless of whether they have any orders, and their corresponding orders, if any.

To get a list of users who have placed orders, we could use an INNER JOIN −

SELECT u.name, o.product_name, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

This query would only return users who have placed orders, and their corresponding orders.

What is Indexing in MySQL?

Indexing is a process of creating an index on one or more columns in a MySQL table to improve query performance. An index is a data structure that allows the database to quickly look up rows based on the values in the indexed columns.

Here's an Example

Suppose we have a table called 'products' that contains information about products, including a column called 'product_name'. To improve performance when searching for products by name, we could create an index on the 'product_name' column −

CREATE INDEX idx_products_product_name ON products(product_name);

This would create an index on the 'product_name' column in the 'products' table, allowing the database to quickly look up products by name.

What is the Difference Between CHAR and VARCHAR Data Types in MySQL?

Both CHAR and VARCHAR are string data types used to store character data in MySQL. The main difference between the two is that CHAR is a fixed-length data type, while VARCHAR is a variable-length data type.

Here's an Example

If we define a column as CHAR(10), it will always occupy 10 bytes of storage, regardless of the length of the data stored in the column. This means that if we store the string 'hello' in the column, it will be padded with spaces to occupy 10 bytes of storage.

If we define a column as VARCHAR(10), it will only occupy as much storage as necessary to store the data. This means that if we store the string 'hello' in the column, it will only occupy 5 bytes of storage.

In general, it's a good practice to use CHAR for columns that have a fixed length (e.g. postal codes), and VARCHAR for columns that have a variable length (e.g. product names).

What is a Subquery in MySQL?

A subquery is a query that is nested inside another query in MySQL. Subqueries can be used to retrieve data that will be used in the main query, or to filter data based on a condition.

Here's an Example

Suppose we have two tables, 'users' and 'orders'. The 'users' table contains information about users, while the 'orders' table contains information about orders placed by those users.

To get a list of all users who have placed orders, we could use a subquery −

SELECT name FROM users WHERE id IN (SELECT user_id FROM orders);

This query would first execute the subquery, which would return a list of user IDs from the 'orders' table. The main query would then use the IN operator to filter the 'users' table and return the names of users who have placed orders.

What is a Database Transaction in MySQL?

A database transaction in MySQL is a series of SQL statements that are executed as a single unit of work. Transactions are used to ensure that a group of SQL statements are executed in an atomic, consistent, isolated, and durable (ACID) manner.

Here's an Example

Suppose we have a table called 'accounts' that contains information about bank accounts, including a column called 'balance'. To transfer money from one account to another, we would need to update the balance of both accounts in a single transaction −

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

This transaction would deduct $100 from the account with ID 1 and add $100 to the account with ID 2, ensuring that the transaction is executed in an ACID manner.