SQL Challenge Quiz Quiz

Test your knowledge of SQL techniques for finding the median salary in a database table, with questions covering window functions, aggregation, and practical query logic. This quiz is ideal for those preparing for interviews or enhancing SQL analytics skills.

1. Understanding the Median in Salaries

   What does the median salary represent in a data set of employee salaries, for example [40,000, 45,000, 50,000]?

   1. The average of all salary values
   2. The middle salary value when all are sorted
   3. The highest salary in the list
   4. The sum of all salaries divided by 2

2. PERCENTILE_CONT Usage

   Which SQL function computes a specific percentile, such as the median salary, in a modern SQL Server version?

   1. PERSENTILE_CNT
   2. GROUP_BY
   3. COUNT_DISTINCT
   4. PERCENTILE_CONT

3. OFFSET-FETCH For Median

   When finding the median salary using OFFSET-FETCH, why is sorting the salary column important?

   1. Because it deletes duplicate values
   2. Because it eliminates NULL values
   3. Because the position of the median depends on sorted order
   4. Because it increases the total count

4. Traditional Median Calculation

   In older SQL versions, you calculate the median salary by assigning row numbers in both ascending and descending order. What is this method primarily used for?

   1. Summing all salary values
   2. Sorting employees alphabetically
   3. Counting the total number of employees
   4. Identifying the rows in the middle of the sorted dataset

5. Handling Even Number of Rows

   If an employee table has an even number of salary rows, like [40,000, 45,000, 50,000, 60,000], how is the median salary typically calculated?

   1. By selecting any random salary
   2. By picking the lowest salary
   3. By averaging the two middle salary values
   4. By summing the first and last salaries

6. Window Functions and Median

   Which SQL clause is necessary to assign a unique sequence to each row, such as when using ROW_NUMBER() to find the median salary?

   1. ORDER BY
   2. HAVING
   3. SUM
   4. DISTINCT

7. GROUP BY in Median Calculation

   Why might you use 'GROUP BY (SELECT NULL)' in a PERCENTILE_CONT query for median salary?

   1. To filter out NULL salaries from the results
   2. To return a single aggregated row without grouping by any column
   3. To count unique salary values
   4. To group results by employee department

8. Identifying Data Skew

   Why is the median salary preferred over the average in a dataset with a few very high salaries and many low salaries?

   1. Because it ignores all duplicate salaries
   2. Because it always gives a higher result
   3. Because databases process median faster than average
   4. Because the median is not affected by extreme outliers

9. Basic Table Definition

   Which of the following columns would NOT be necessary in an employee salary table used for median calculations?

   1. hire_date
   2. salary
   3. department
   4. employee_id

10. AVG Function in Median Queries

    In the traditional median calculation, why is AVG() used in the final SELECT when there could be two middle salary values?

    1. To select the highest salary
    2. Because AVG always returns the median automatically
    3. To count the number of salaries
    4. To calculate the mean of the two middle salaries for even row counts