MCQ(Multiple Choice Questions) on MySQL clause Count, Avg, and Sum

MySQL is among popular relational database management system which allows us to manage data efficiently. MySQL provides various function and clause to perform advanced calculations and aggregations on data. Three most commonly used functions in MySQL for aggregating data are Count, Avg, and Sum.Given below is short description about them followed by some MCQ question at last

1. Count Function

It is used to count the number of rows that satisfy a specific condition in a table. It is often used with the WHERE clause to filter the data before counting.

For example(let's consider here that our table name is "employees"):

SELECT COUNT(*) FROM employees;

It will return the total no. of rows in the "employees" table.

2. Avg Function

This function is used to calculate the average value of a numeric column in a table. It is often used with the WHERE clause to calculate the average of specific rows.

Example:

SELECT AVG(salary) FROM employees;

It will return the average salary of all employees in the "employees" table.

3. Sum Function

This function is used to calculate sum of values in a numeric column in table. It is also mainly used with the WHERE clause to calculate the sum of specific rows based on our requirements.

Example:

SELECT SUM(sales) FROM orders WHERE year = 2021;

This query will return the total sales for the year 2021 from the "orders" table.

MCQ Questions and Answers

1)Which function is used to count the number of rows in a table?

1. Sum

2. Avg

3. Count (Correct)

Explanation: The Count function is specifically designed to count the number of rows in a table.

2)Which function is used to calculate the average value of a numeric column?

1. Sum

2. Avg (Correct)

3. Count

Explanation: The Avg function is used to calculate the average value of a numeric column in a table.

3)Which function is used to calculate the sum of values in a numeric column?

1. Sum (Correct)

2. Avg

3. Count

Explanation: The Sum function is used to calculate the sum of values in a numeric column in a table.

4)Can the Count function be used with the WHERE clause?

1. Yes (Correct)

2. No

Explanation: The Count function can be used with the WHERE clause to count specific rows that satisfy a condition.

5)Can the Avg function be used with the WHERE clause?

1. Yes

2. No (Correct)

Explanation: The Avg function calculates the average value of a numeric column and does not require the WHERE clause.

6)Can the Sum function be used with the WHERE clause?

1. Yes (Correct)

2. No

Explanation: The Sum function can be used with the WHERE clause to calculate the sum of specific rows that satisfy a condition.

7)What does the Count function return if there are no matching rows?

1. 0 (Correct)

2. Null

3. 1

4. 2

Explanation: The Count function returns 0 if there are no matching rows.

8)What does the Avg function return if there are no matching rows?

1. 0

2. Null (Correct)

3. 1

4. 2

Explanation: The Avg function returns Null if there are no matching rows.

9)What does the Sum function return if there are no matching rows?

1. 0

2. Null (Correct)

3. 1

4. 2

Explanation: The Sum function returns Null if there are no matching rows.

Can the Count, Avg, and Sum functions be used together in a single query?

1. Yes

2. No (Correct)

Explanation: The Count, Avg, and Sum functions are used for different purposes and cannot be combined in a single query.