Top MySQL Interview Questions (2025)

Question: What is the purpose of the GROUP BY clause in MySQL?

Answer:

The GROUP BY clause in MySQL is used to group rows that have the same values in specified columns into summary rows, often used with aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX() to perform calculations on each group. Essentially, it allows you to aggregate data across multiple rows and summarize it into a smaller set of results.

Purpose of the GROUP BY Clause:

1. Group Rows: It organizes rows in a result set into groups based on the values in one or more columns.
2. Aggregation: It enables the use of aggregate functions to perform calculations on each group of rows. Without GROUP BY, aggregate functions apply to the entire result set.
3. Summarize Data: It helps in summarizing large datasets by categorizing them into groups and applying aggregate functions to each group, making it easier to analyze and draw insights.

How It Works:

• The GROUP BY clause groups rows based on the values of one or more columns.
• You can then apply aggregate functions to summarize data for each group (e.g., counting the number of rows in each group, calculating the average value in each group).

Syntax:

SELECT column1, column2, aggregate_function(column3)
FROM table_name
GROUP BY column1, column2;

• column1, column2: The columns by which you want to group the data.
• aggregate_function(column3): The aggregate function (like COUNT(), SUM(), etc.) applied to the grouped data.

Example:

Consider a table sales with the following columns: salesperson, region, and sales_amount. You want to calculate the total sales for each salesperson.

SELECT salesperson, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY salesperson;

This query groups the data by the salesperson column and calculates the sum of sales_amount for each salesperson.

Use of Multiple Columns in GROUP BY:

You can also group by multiple columns. For example, if you want to calculate the total sales by both salesperson and region:

SELECT salesperson, region, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY salesperson, region;

This groups the rows by both salesperson and region and calculates the total sales for each salesperson in each region.

Key Points:

1. Aggregate Functions: The GROUP BY clause is commonly used with aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX() to compute summaries for each group of rows.

2. Grouping Multiple Columns: You can group by more than one column, and the rows are grouped according to unique combinations of values in those columns.

3. Sorting: Although GROUP BY organizes data into groups, it does not automatically sort the result. You can use the ORDER BY clause to sort the results of a grouped query.

   Example:

   SELECT salesperson, SUM(sales_amount) AS total_sales
   FROM sales
   GROUP BY salesperson
   ORDER BY total_sales DESC;

Example with Aggregate Functions:

Here’s an example showing different aggregate functions used with GROUP BY:

SELECT department, COUNT(*) AS total_employees, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

This query will:

• Group employees by department.
• Count the total number of employees in each department.
• Calculate the average salary for each department.

Summary:

• GROUP BY is used to group rows based on column values, typically for applying aggregate functions (e.g., COUNT(), SUM(), AVG(), MIN(), MAX()).
• It helps to summarize large datasets into smaller, meaningful groups and allows for aggregation and analysis at a higher level.

Question: What is the difference between CHAR and VARCHAR data types?

Answer:

The CHAR and VARCHAR data types in MySQL are both used to store strings (text), but they have significant differences in how they store and manage data. Here’s a detailed comparison between the two:

1. Storage Format:

• CHAR:
  • The CHAR data type is used to store fixed-length strings.
  • If the string is shorter than the defined length, MySQL will pad it with spaces to make it match the specified length.
  • For example, if you define a CHAR(10) column and store the value "apple", it will be stored as "apple " (with 5 trailing spaces).
• VARCHAR:
  • The VARCHAR data type is used to store variable-length strings.
  • It only stores the actual characters you provide, without padding. The storage length is determined by the number of characters in the string.
  • For example, if you define a VARCHAR(10) column and store the value "apple", it will be stored as "apple" (without padding).

2. Storage Efficiency:

• CHAR:
  • Since CHAR is a fixed-length type, it always uses the specified amount of space, regardless of the actual string length.
  • This can lead to wasted space when storing shorter strings. For instance, a CHAR(100) field will always use 100 bytes of storage, even if the stored string is only 10 characters long.
• VARCHAR:
  • VARCHAR is more storage-efficient because it only uses as much space as needed for the actual string data, plus a small overhead to store the length of the string.
  • However, there is still a small overhead for storing the length of the string (1 or 2 bytes depending on the string length).

3. Performance:

• CHAR:
  • CHAR can be faster for fixed-length data because the database knows exactly how much space each entry will occupy, so there’s no need for length calculations.
  • It’s suitable for storing data that is always the same length, such as country codes, zip codes, etc.
• VARCHAR:
  • VARCHAR is generally slower than CHAR for fixed-length data because it has to store the actual length of the string and handle variable lengths.
  • It is more appropriate for fields that store strings of varying lengths, like names or email addresses, where the length of the data is unpredictable.

4. Use Cases:

• CHAR:
  • Best suited for storing fixed-length data where the length of the string is consistent.
  • Examples:
    • Fixed-length codes (e.g., country codes, status codes, fixed-length identifiers like ZIP codes).
    • Data that will always be of a particular length, such as phone numbers (with country code and area code).
• VARCHAR:
  • Ideal for storing variable-length data where the length of the string can vary significantly.
  • Examples:
    • Names, addresses, descriptions, and email addresses.
    • Any data where the string length is not fixed and can vary from one entry to another.

5. Maximum Length:

• CHAR:
  • The maximum length for a CHAR field is 255 characters.
• VARCHAR:
  • The maximum length for a VARCHAR field is 65,535 characters, but the actual limit depends on the character set used and the maximum row size in the database. In practice, it’s often much less than this.

6. Example Usage:

• CHAR Example:

  CREATE TABLE employees (
    employee_code CHAR(5)
  );

  In this example, employee_code will always have 5 characters, and any shorter string will be padded with spaces.

• VARCHAR Example:

  CREATE TABLE employees (
    employee_name VARCHAR(100)
  );

  In this example, employee_name can hold a string up to 100 characters, but if the name is shorter, it will only take up the space required for the actual string plus some storage for the length.

7. Trailing Spaces:

• CHAR:
  • The CHAR type pads trailing spaces to ensure the string is of the defined length. When retrieving data, trailing spaces are typically removed unless explicitly requested with TRIM().
• VARCHAR:
  • The VARCHAR type does not pad the string with spaces, and there are no trailing spaces to worry about.

8. Performance with Dynamic Data:

• CHAR:
  • More efficient for static data with a fixed length, as there’s no need to handle varying string lengths.
• VARCHAR:
  • More flexible and efficient for dynamic data with varying string lengths.

Summary of Differences:

Feature	CHAR	VARCHAR
Storage	Fixed-length, always uses the defined size.	Variable-length, stores only actual characters.
Storage Efficiency	Can waste space with short strings.	More efficient as it stores only the actual data.
Performance	Faster for fixed-length data.	Slightly slower for variable-length data.
Best Use Case	Fixed-length data (e.g., country codes, ZIP codes).	Variable-length data (e.g., names, addresses).
Maximum Length	255 characters.	65,535 characters (depending on row size and charset).
Trailing Spaces	Pads with spaces.	Does not pad with spaces.
Example	CHAR(10) for a fixed-length identifier.	VARCHAR(100) for a name or address.

Key Takeaway:

• Use CHAR when you know the data will always have a fixed length (e.g., country codes, state abbreviations, etc.).
• Use VARCHAR for data that can vary in length, such as names, emails, or descriptions, to save storage space and improve performance for varying-length strings.

Read More

If you can’t get enough from this article, Aihirely has plenty more related information, such as MySQL interview questions, MySQL interview experiences, and details about various MySQL job positions. Click here to check it out.