Test Your MySQL Group Rank Knowledge with This Quiz

Progress

True / False

In MySQL, the RANK() function can be used to assign a unique rank number to each row within a partition of a result set.

True False

The RANK() function can assign the same rank to rows with identical values within the partition, not necessarily a unique rank.

True / False

MySQL’s GROUP BY clause is used to aggregate data across multiple rows.

True False

The GROUP BY clause groups rows that have the same values into summary rows, like "find the number of customers in each country".

True / False

The ROW_NUMBER() function in MySQL assigns a unique sequential integer to rows within a partition of a result set, starting at 1.

True False

The ROW_NUMBER() function assigns a unique number to each row within the partition, starting from 1.

True / False

Question: MySQL 8.0 introduced the DENSE_RANK() function.

True False

The DENSE_RANK() function was introduced in MySQL 8.0, which provides rank values without gaps in the ranking.

True / False

In MySQL, the RANK() function will generate consecutive rank values, without any gaps, even if there are ties.

True False

The RANK() function will leave gaps in the ranking sequence if there are ties (e.g., if two rows are tied for rank 1, the next rank will be 3).

True / False

In MySQL, you can use the RANK() function without a PARTITION BY clause.

True False

The RANK() function can be used without the PARTITION BY clause, but it will treat the entire result set as a single partition.

True / False

MySQL's NTILE() function can be used to assign rows to a specified number of buckets and is helpful in ranking.

True False

The NTILE() function distributes rows into a specified number of buckets, which can be useful in ranking and distribution tasks.

True / False

In MySQL, the RANK() function and DENSE_RANK() function will always produce the same result.

True False

The RANK() function produces ranks with gaps if there are ties, whereas the DENSE_RANK() function produces consecutive ranks without gaps.

True / False

The RANK() function in MySQL can be combined with the GROUP BY clause to rank groups of rows based on aggregated values.

True False

You can use the RANK() function with the GROUP BY clause to rank groups of rows based on aggregated values, though it may require subqueries or CTEs to achieve the desired result.

Q10

True / False

In MySQL certification exams, understanding how RANK(), DENSE_RANK(), and ROW_NUMBER() functions differ is crucial for optimizing query performance.

True False

Knowledge of these ranking functions and their differences is essential for certification exams as they can impact query results and performance optimization.

Q11

Single Choice

Which MySQL function is commonly used to assign a rank within a subgroup of rows in a result set?

COUNT() RANK() GROUP BYSUM()

The RANK() function is used to assign a rank to each row within a result set based on the specified columns

Q12

Single Choice

What SQL clause must be used in conjunction with the RANK() function to rank rows within subgroups?

ORDER BY GROUP BY WHEREHAVING

The ORDER BY clause is used to define the order of the rows within each subgroup, which is essential for assigning ranks.

Q13

Single Choice

Which MySQL function assigns the same rank to rows with identical values and skips the next rank(s)?

ROW_NUMBER() DENSE_RANK() NTILE()RANK()

The RANK() function assigns the same rank to identical values, and the next rank is incremented based on the number of identical values.

Q14

Single Choice

What will be the rank of Alice if you apply the following SQL query?

SQL Code

Given the following table sales:

Salesperson	Region	Sales
Alice	East	500
Bob	East	700
Carol	East	500
Dave	West	450
Eve	West	700

SELECT Salesperson, Sales,
 RANK() OVER (PARTITION BY Region ORDER BY Sales DESC) AS Rank
FROM sales;

1 2 34

Alice and Carol both have the same sales in the East region, so they both get the same rank, which is 2 since Bob has the highest sales.

Q15

Single Choice

. In a ranking query, how would you ensure that gaps in ranks do not occur when there are ties in a subgroup?

Use ROW_NUMBER() Use RANK() Use DENSE_RANK()Use NTILE()

DENSE_RANK() assigns ranks without gaps between the numbers, even when there are ties.

Q16

Single Choice

What will be the output for Jill if you use the following SQL query?

SQL Code

Given the following table employees:

Employee	Department	Salary
John	HR	60000
Jane	HR	75000
Jack	IT	80000
Jill	IT	75000
Joe	IT	75000
SELECT Employee, Department, Salary,
 DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank
FROM employees;

1 2 34

Jill and Joe have the same salary in the IT department, and so both are ranked 2nd. Since DENSE_RANK() is used, the ranks will be continuous.

Q17

Single Choice

What will be the rank of the Fridge if the following SQL query is executed?

SQL Code

Consider the following table products:

Product	Category	Price
Laptop	Electronics	1000
Phone	Electronics	800
TV	Electronics	1200
Blender	Kitchen	200
Toaster	Kitchen	150
Fridge	Kitchen	700

SELECT Product, Category, Price,
 RANK() OVER (PARTITION BY Category ORDER BY Price DESC) AS Rank
FROM products;

1 2 34

Within the Kitchen category, the Fridge has the highest price, so it gets a rank of 1.

Q18

Single Choice

What is the difference between ROW_NUMBER() and RANK() in MySQL when applied within subgroups?

ROW_NUMBER() skips ranks for ties while RANK() does not ROW_NUMBER() assigns a unique number to each row while RANK() skips numbers after ties Both functions always assign the same rank to identical rowsThere is no difference

ROW_NUMBER() gives each row a unique number, regardless of duplicates, while RANK() assigns the same rank to identical rows and skips the next rank(s).

Q19

Single Choice

What will be the output for Anne if the following SQL query is executed?

SQL Code

Consider the following sales table:

Salesperson	Region	Sales
Anne	North	400
Beth	North	500
Claire	North	500
Dave	South	700
Ella	South	700

SELECT Salesperson, Region, Sales,
 DENSE_RANK() OVER (PARTITION BY Region ORDER BY Sales DESC) AS Rank
FROM sales;

1 2 34

In the North region, Beth and Claire are ranked 1st because they have the highest sales. Anne, with the next highest sales, is ranked 3rd because DENSE_RANK() does not skip ranks

Q20

Single Choice

You need to assign ranks to employees within their departments and also rank the departments based on the highest employee salary in each department. Which of the following SQL queries accomplishes this?

SELECT Employee, Department, Salary, RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS EmployeeRank, DENSE_RANK() OVER (ORDER BY MAX(Salary) OVER (PARTITION BY Department) DESC) AS DepartmentRank FROM employees; SELECT Employee, Department, Salary, DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS EmployeeRank, RANK() OVER (ORDER BY MAX(Salary) OVER (PARTITION BY Department) DESC) AS DepartmentRank FROM employees; SELECT Employee, Department, Salary, RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS EmployeeRank, RANK() OVER (ORDER BY MAX(Salary) OVER (PARTITION BY Department) DESC) AS DepartmentRank FROM employees;SELECT Employee, Department, Salary, DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS EmployeeRank, DENSE_RANK() OVER (ORDER BY MAX(Salary) OVER (PARTITION BY Department) DESC) AS DepartmentRank FROM employees;

The query uses RANK() for both ranking within the department and across departments. The subquery MAX(Salary) OVER (PARTITION BY Department) is used to rank departments based on their highest salary.

Q21

Multiple Choice

Which SQL query ranks employees based on their salaries within each department using the RANK() function?

SQL Code

SELECT employee_name, department_id, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;

SELECT employee_name, department_id, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank FROM employees; SELECT employee_name, department_id, salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM employees; SELECT employee_name, department_id, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary ASC) AS rank FROM employees;SELECT employee_name, department_id, salary, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank FROM employees;

The RANK() function ranks employees within each department based on their salaries in descending order. The PARTITION BY clause groups the employees by department.

Q22

Multiple Choice

Identify the SQL query that uses DENSE_RANK() to assign ranks to students based on their scores within each class.

SQL Code

SELECT student_name, class_id, score,
DENSE_RANK() OVER (PARTITION BY class_id ORDER BY score DESC) AS rank
FROM students;

SELECT student_name, class_id, score, DENSE_RANK() OVER (PARTITION BY class_id ORDER BY score DESC) AS rank FROM students; SELECT student_name, class_id, score, RANK() OVER (PARTITION BY class_id ORDER BY score DESC) AS rank FROM students; SELECT student_name, class_id, score, DENSE_RANK() OVER (ORDER BY score DESC) AS rank FROM students;SELECT student_name, class_id, score, ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY score DESC) AS rank FROM students;

The DENSE_RANK() function assigns ranks to students within each class based on their scores in descending order, without gaps in the ranking sequence.

Q23

Multiple Choice

Which SQL query ranks products based on their sales within each category using the ROW_NUMBER() function?

SQL Code

SELECT product_name, category_id, sales,
ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rank
FROM products;

SELECT product_name, category_id, sales, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rank FROM products; SELECT product_name, category_id, sales, RANK() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rank FROM products; SELECT product_name, category_id, sales, ROW_NUMBER() OVER (ORDER BY sales DESC) AS rank FROM products;SELECT product_name, category_id, sales, DENSE_RANK() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rank FROM products;

The ROW_NUMBER() function assigns a unique rank to each product within its category based on sales in descending order, with no ties.

Q24

Multiple Choice

Determine the SQL query that uses the RANK() function to assign ranks to employees based on their performance scores within each department.

SQL Code

SELECT employee_name, department_id, performance_score,
RANK() OVER (PARTITION BY department_id ORDER BY performance_score DESC) AS rank
FROM employees;

SELECT employee_name, department_id, performance_score, RANK() OVER (PARTITION BY department_id ORDER BY performance_score DESC) AS rank FROM employees; SELECT employee_name, department_id, performance_score, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY performance_score DESC) AS rank FROM employees; SELECT employee_name, department_id, performance_score, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY performance_score DESC) AS rank FROM employees;SELECT employee_name, department_id, performance_score, RANK() OVER (ORDER BY performance_score DESC) AS rank FROM employees;

The RANK() function ranks employees within each department based on their performance scores in descending order, with gaps in the ranking sequence for ties.

Q25

Multiple Choice

Which SQL query uses DENSE_RANK() to rank products based on their ratings within each supplier group?

SQL Code

SELECT product_name, supplier_id, rating,
DENSE_RANK() OVER (PARTITION BY supplier_id ORDER BY rating DESC) AS rank
FROM products;

SELECT product_name, supplier_id, rating, DENSE_RANK() OVER (PARTITION BY supplier_id ORDER BY rating DESC) AS rank FROM products; SELECT product_name, supplier_id, rating, RANK() OVER (PARTITION BY supplier_id ORDER BY rating DESC) AS rank FROM products; SELECT product_name, supplier_id, rating, ROW_NUMBER() OVER (PARTITION BY supplier_id ORDER BY rating DESC) AS rank FROM products;SELECT product_name, supplier_id, rating, DENSE_RANK() OVER (ORDER BY rating DESC) AS rank FROM products;

The DENSE_RANK() function assigns ranks to products within each supplier group based on their ratings in descending order, ensuring no gaps in the ranking sequence.

Q26

Multiple Choice

Which SQL query ranks students based on their marks within each subject using the RANK() function?

SQL Code

SELECT student_name, subject_id, marks,
RANK() OVER (PARTITION BY subject_id ORDER BY marks DESC) AS rank
FROM student_scores;

SELECT student_name, subject_id, marks, RANK() OVER (PARTITION BY subject_id ORDER BY marks DESC) AS rank FROM student_scores; SELECT student_name, subject_id, marks, DENSE_RANK() OVER (PARTITION BY subject_id ORDER BY marks DESC) AS rank FROM student_scores; SELECT student_name, subject_id, marks, ROW_NUMBER() OVER (PARTITION BY subject_id ORDER BY marks DESC) AS rank FROM student_scores;SELECT student_name, subject_id, marks, RANK() OVER (ORDER BY marks DESC) AS rank FROM student_scores;

The RANK() function assigns ranks to students within each subject based on their marks in descending order, allowing for ties and gaps in the ranking.

Q27

Multiple Choice

Identify the SQL query that uses ROW_NUMBER() to assign unique ranks to employees based on their years of experience within each department.

SQL Code

SELECT employee_name, department_id, experience_years,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY experience_years DESC) AS rank
FROM employees;

SELECT employee_name, department_id, experience_years, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY experience_years DESC) AS rank FROM employees; SELECT employee_name, department_id, experience_years, RANK() OVER (PARTITION BY department_id ORDER BY experience_years DESC) AS rank FROM employees; SELECT employee_name, department_id, experience_years, ROW_NUMBER() OVER (ORDER BY experience_years DESC) AS rank FROM employees;SELECT employee_name, department_id, experience_years, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY experience_years DESC) AS rank FROM employees;

The ROW_NUMBER() function assigns unique ranks to employees within each department based on their years of experience in descending order, with no ties.

Q28

Multiple Choice

Which SQL query uses RANK() to determine the top 3 performers in each department based on their performance scores?

SQL Code

SELECT * FROM (
SELECT employee_name, department_id, performance_score,
RANK() OVER (PARTITION BY department_id ORDER BY performance_score DESC) AS rank
FROM employees
) AS ranked_data
WHERE rank <= 3;

SELECT * FROM ( SELECT employee_name, department_id, performance_score, RANK() OVER (PARTITION BY department_id ORDER BY performance_score DESC) AS rank FROM employees ) AS ranked_data WHERE rank <= 3; SELECT * FROM ( SELECT employee_name, department_id, performance_score, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY performance_score DESC) AS rank FROM employees ) AS ranked_data WHERE rank <= 3; SELECT * FROM ( SELECT employee_name, department_id, performance_score, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY performance_score DESC) AS rank FROM employees ) AS ranked_data WHERE rank <= 3;SELECT employee_name, department_id, performance_score FROM employees WHERE performance_score IN ( SELECT performance_score FROM employees GROUP BY department_id ORDER BY performance_score DESC LIMIT 3 );

The RANK() function ranks employees within each department based on performance scores. The outer query then filters the top 3 performers in each department.

Q29

Multiple Choice

Determine the SQL query that ranks salespersons based on their total sales within each region using DENSE_RANK().

SQL Code

SELECT salesperson_name, region_id, total_sales,
DENSE_RANK() OVER (PARTITION BY region_id ORDER BY total_sales DESC) AS rank
FROM sales_data;

SELECT salesperson_name, region_id, total_sales, DENSE_RANK() OVER (PARTITION BY region_id ORDER BY total_sales DESC) AS rank FROM sales_data; SELECT salesperson_name, region_id, total_sales, RANK() OVER (PARTITION BY region_id ORDER BY total_sales DESC) AS rank FROM sales_data; SELECT salesperson_name, region_id, total_sales, ROW_NUMBER() OVER (PARTITION BY region_id ORDER BY total_sales DESC) AS rank FROM sales_data;SELECT salesperson_name, region_id, total_sales, DENSE_RANK() OVER (ORDER BY total_sales DESC) AS rank FROM sales_data;

The DENSE_RANK() function ranks salespersons within each region based on their total sales in descending order, ensuring no gaps in the ranking sequence.

Q30

Multiple Choice

Which SQL query uses ROW_NUMBER() to find the top 5 products by revenue within each category?

SQL Code

SELECT * FROM (
SELECT product_name, category_id, revenue,
ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY revenue DESC) AS rank
FROM products
) AS ranked_products
WHERE rank <= 5;

SELECT * FROM ( SELECT product_name, category_id, revenue, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY revenue DESC) AS rank FROM products ) AS ranked_products WHERE rank <= 5; SELECT * FROM ( SELECT product_name, category_id, revenue, RANK() OVER (PARTITION BY category_id ORDER BY revenue DESC) AS rank FROM products ) AS ranked_products WHERE rank <= 5; SELECT * FROM ( SELECT product_name, category_id, revenue, DENSE_RANK() OVER (PARTITION BY category_id ORDER BY revenue DESC) AS rank FROM products ) AS ranked_products WHERE rank <= 5;SELECT product_name, category_id, revenue FROM products WHERE revenue IN ( SELECT revenue FROM products GROUP BY category_id ORDER BY revenue DESC LIMIT 5 );

The ROW_NUMBER() function assigns a unique rank to each product within its category based on revenue in descending order, and the outer query filters the top 5 products in each category.

MySQL Database Quiz Questions