Advanced SQL Concepts Quiz for Developers Quiz

Test your knowledge of advanced SQL concepts with this easy-level quiz. Ideal for developers preparing for SQL interviews, these questions cover subqueries, joins, group functions, aggregate operations, and more.

1. Salary Ranking by Department

   Which SQL clause is commonly used to find the maximum value of a column, such as the highest salary in each department?

   1. SUM
   2. COUNT
   3. MIN
   4. MAX

   Explanation: The MAX function returns the highest value in a column, such as the maximum salary in each department. MIN gives the lowest value, COUNT returns the total number of rows, and SUM adds up all the values. Only MAX directly determines the greatest amount.

2. Calculating Department Averages

   If you want to calculate the average salary for each department in SQL, which aggregate function should you use?

   1. COUNT
   2. STDEV
   3. SUM
   4. AVG

   Explanation: AVG calculates the average of a numeric column, perfect for finding average salaries. SUM totals values, COUNT counts rows, and STDEV computes standard deviation. Only AVG gives the arithmetic mean required here.

3. Group Aggregation

   What SQL clause allows you to apply aggregate functions like SUM or AVG for groups of rows, such as by department?

   1. WHERE
   2. DISTINCT
   3. GROUP BY
   4. ORDER BY

   Explanation: GROUP BY collects rows into groups for aggregate calculations like MAX or AVG per category. ORDER BY sorts results, WHERE filters them, and DISTINCT removes duplicates. The only clause enabling grouped aggregation is GROUP BY.

4. Comparing Salaries to Department Averages

   Which SQL statement calculates each employee's salary difference from their department's average?

   1. SELECT e.name, e.salary, d.avg_salary, (e.salary - d.avg_salary) AS salary_diff FROM Employee e JOIN (SELECT department_id, AVG(salary) AS avg_salary FROM Employee GROUP BY department_id) d ON e.department_id = d.department_id;
   2. SELECT name, salary, AVG(salary) AS avg_salary FROM Employee;
   3. SELECT name, salary, SUM(salary) FROM Employee GROUP BY department_id;
   4. SELECT name, salary, salary * 2 FROM Employee;

   Explanation: The first statement joins employee data to department averages and calculates the difference. The second lacks a join and doesn't compute a difference. The third uses SUM instead of AVG. The fourth only doubles salary, not calculating a difference from a group average.

5. Using Subqueries for Filtering

   When wanting to use the result of a group operation to filter rows in the outer query, which SQL technique is commonly employed?

   1. Subquery
   2. UNION operator
   3. ORDER BY clause
   4. HAVING keyword

   Explanation: A subquery is used to calculate a value, like a department average, and join or compare it in the outer query. ORDER BY sorts but doesn't filter. UNION merges result sets. HAVING filters grouped results but is not always used for embedding aggregations as new columns.

6. Purpose of the JOIN Clause

   What is the main purpose of using a JOIN clause in SQL queries involving employees and departments?

   1. Filter results after groups are formed
   2. Remove duplicate rows from a result set
   3. Rename columns in the final output
   4. Combine rows from different tables based on related columns

   Explanation: JOIN combines data from tables using related columns, such as department_id. HAVING filters after grouping, DISTINCT removes duplicates, and aliases (not JOIN) rename columns. Only JOIN creates rows combining information from two tables.

7. Identifying Top Performers by Group

   How would you write a query to list employees who have the highest salary within their department?

   1. Just select all employees with salary above a certain amount
   2. Use a subquery that finds the maximum salary per department and join it to employee records
   3. Count all employees in each department
   4. Use GROUP BY name and select maximum salary overall

   Explanation: A subquery per department identifying the max salary and joining to employee data returns top earners. Simply filtering above a threshold doesn't ensure they're department leaders. Grouping by name isn't useful here, and counting doesn't help find salary leaders.

8. Difference Between WHERE and HAVING

   Which clause in SQL is used to filter groups after aggregation, unlike WHERE which filters rows before aggregation?

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

   Explanation: HAVING applies conditions to groups after aggregation, such as filtering departments by average salary. WHERE filters rows before aggregation. ORDER BY sorts results, and DISTINCT removes duplicates; neither filters after aggregation.

9. Meaning of SELECT *

   What does SELECT * do in an SQL statement such as SELECT e.* FROM Employee e?

   1. Selects duplicate values only
   2. Selects no columns
   3. Selects all columns from the Employee table
   4. Selects only the name column

   Explanation: SELECT * returns every column for the table or alias specified. It does not limit the selection to the name, remove duplicates, or result in zero columns. The asterisk is a wildcard meaning 'all columns.'

10. Finding a Department's ID

    If you want to find the department_id of an employee, which SQL clause should you use to specify which employee you need?

    1. WHERE
    2. GROUP BY
    3. DISTINCT
    4. ORDER BY

    Explanation: WHERE is used to filter rows and find specific entries, such as department_id for a given employee. ORDER BY is for sorting, GROUP BY is for aggregation, and DISTINCT removes duplicates, none of which help in directly filtering for one employee.

11. Calculating Maximum Values By Category

    Which statement will return the highest salary for each department in a table called Employee?

    1. SELECT * FROM Employee WHERE MAX(salary);
    2. SELECT salary FROM Employee WHERE salary u003E 0;
    3. SELECT MAX(salary) FROM Employee;
    4. SELECT department_id, MAX(salary) FROM Employee GROUP BY department_id;

    Explanation: The correct query groups by department and selects the maximum salary in each group. The second only gives one overall maximum. The third lists salaries above zero, and the fourth is syntactically incorrect.

12. Aggregating With Aliased Tables

    Why are table aliases like 'e' and 'd' used in SQL queries with joins?

    1. To enforce unique constraints
    2. To run queries faster
    3. To make queries shorter and easier to read, especially with multiple tables
    4. To sort data automatically

    Explanation: Aliases such as 'e' and 'd' help shorten queries and clarify references, especially with self-joins or complex statements. They do not sort, enforce uniqueness, or inherently improve speed.

13. Using Arithmetic in SELECT Statements

    How can you calculate a new column value like salary difference in the SELECT clause?

    1. By omitting aggregate functions
    2. By writing salary = avg_salary
    3. By using GROUP BY alone
    4. By using arithmetic expressions like (e.salary - d.avg_salary)

    Explanation: You can calculate new column values directly in SELECT using expressions like (salary - avg_salary). GROUP BY organizes data but doesn’t calculate new columns by itself. Writing salary = avg_salary checks equality, not difference, and omitting aggregates ignores the group context.

14. Purpose of Aggregate Functions

    What is the main use of aggregate functions like SUM, AVG, and MAX in SQL?

    1. To filter rows before selecting data
    2. To calculate summary statistics from groups or sets of rows
    3. To concatenate string values
    4. To convert text to numbers

    Explanation: Aggregate functions summarize data, such as totals or averages, from multiple rows. They do not filter, concatenate, or convert data types, but are central to grouped calculations.

15. Joining on Multiple Conditions

    Why might a join use more than one condition, such as 'ON e.department_id = d.department_id AND e.salary = d.max_salary'?

    1. To sort the data after joining
    2. To create new tables
    3. To further narrow join results to ensure only matching rows are returned
    4. To remove null values automatically

    Explanation: Using multiple conditions in a join restricts rows to exact matches, like ensuring employees match both department and salary. Sorting, null removal, and table creation require different syntax, not join conditions.

16. Using Derived Tables (Subqueries in FROM)

    What is the benefit of using a derived table (subquery in the FROM clause) in an SQL query?

    1. It allows you to treat the results of a subquery like a regular table in the main query
    2. It prevents data from being grouped
    3. It creates a permanent table in the database
    4. It automatically removes duplicate rows

    Explanation: Derived tables let you reference subquery results like any table, making complex logic possible. They don't stop grouping, create permanent tables, or always remove duplicates unless you use DISTINCT.