SQL QUIZ -ADBMS

What does the ROW_NUMBER function do in SQL?

• The ROW_NUMBER function calculates the total number of rows in a result set.

• The ROW_NUMBER function groups rows based on specified criteria.

• The ROW_NUMBER function filters rows based on a condition.

• The ROW_NUMBER function assigns a unique sequential integer to rows in a result set.

In a school competition, students are ranked based on their scores. How does the RANK function differ from ROW_NUMBER in assigning these ranks?

• ROW_NUMBER allows ties and skips ranks; RANK assigns unique numbers to each row.

• RANK assigns unique numbers and skips ties; ROW_NUMBER allows ties and ranks them equally.

• RANK allows ties and skips ranks; ROW_NUMBER does not allow ties and assigns unique numbers.

• RANK and ROW_NUMBER both allow ties but RANK assigns higher numbers to tied values.

What is the purpose of the DENSE_RANK function?

• To sort rows in ascending order based on a specific column.

• To assign unique ranks to each row regardless of ties.

• To calculate the average of a set of values.

• To assign ranks to rows in a result set without gaps for ties.

Saisha is analyzing sales data for her online store. She wants to calculate the total sales for each product category while also considering the sales made in different regions. Explain the use of the PARTITION BY clause in window functions in this context.

• The PARTITION BY clause allows window functions to operate on subsets of data, enabling calculations within defined groups.

• The PARTITION BY clause is used to sort data in ascending order.

• The PARTITION BY clause is only applicable to aggregate functions.

• The PARTITION BY clause combines all rows into a single group for calculations.

In a class of students, how does the ORDER BY clause affect window functions when calculating their scores?

• The ORDER BY clause specifies the order of rows for window function calculations.

• The ORDER BY clause determines the number of rows returned by the window function.

• The ORDER BY clause changes the data type of the window function results.

• The ORDER BY clause is used to filter rows before window function calculations.

Can you give an example of aggregating with window functions?

• An example of aggregating with window functions is using SUM() OVER (ORDER BY column1) to calculate a running total.

• Utilizing MAX() OVER (ORDER BY column3) to get the maximum value in a dataset.

• Applying COUNT() OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) for total counts.

• Using AVG() OVER (PARTITION BY column2) to find the average value.

In a class of students, if Avyaan and Aarav both scored the same marks in a test, what will the ROW_NUMBER function return for them?

• The same row number for each duplicate value.

• No row number will be assigned to duplicates.

• A unique row number for each row, even with duplicates.

• A single row number for all duplicate values.

How does the RANK function handle ties in ranking?

• The RANK function always assigns the highest rank to the first occurrence of a value.

• The RANK function treats all values as unique, regardless of ties.

• The RANK function assigns the same rank to tied values and skips subsequent ranks.

• The RANK function assigns different ranks to tied values without skipping any ranks.

What is the output of DENSE_RANK when there are ties?

• DENSE_RANK assigns different ranks to tied values.

• DENSE_RANK assigns the same rank to tied values and continues with the next consecutive rank.

• DENSE_RANK skips ranks for tied values entirely.

• DENSE_RANK assigns the highest rank to all tied values.

How can you use PARTITION BY with multiple columns?

• You can only use PARTITION BY with a single column.

• Use PARTITION BY column1 AND column2 in your SQL query.

• Use PARTITION BY (column1, column2) in your SQL query.

• PARTITION BY (column1; column2) is the correct syntax.