Test your knowledge of SQL joins, aggregations, deduplication, rollups, and upserts. This quiz helps you review important SQL concepts for building effective pipeline transforms and data workflows.
Which SQL join type returns only the rows that have matching values in both tables, such as when combining orders and customers?
Explanation: INNER JOIN returns rows that have matching values in both joined tables, making it useful for combining related data. LEFT JOIN includes all rows from the left table, even if there is no match, while FULL JOIN combines all rows from both sides, with NULLs if there is no match. OUTER JOIN is a general term and is not a specific operator in SQL syntax.
What SQL clause should you use to roll up total sales amounts by region from a transactions table?
Explanation: GROUP BY groups rows sharing the same value in specified columns, commonly used with aggregate functions to create rollups. ORDER BY is for sorting results. HAVING filters groups after aggregation, and DISTINCT eliminates duplicate rows without aggregation.
If you want to count the number of unique customer IDs in a sales table, which SQL function should you use?
Explanation: COUNT(DISTINCT ...) counts unique, non-null values in a column, perfect for deduplicating results. SUM adds up values, AVG returns the mean, and COUNT(*) counts all rows, including duplicates.
If you use a LEFT JOIN between users and orders, what will happen to users with no orders?
Explanation: LEFT JOIN includes all rows from the left table (users) and fills in NULLs for right table (orders) columns where no match exists. They are not excluded (that's INNER JOIN), nor would they have zeroes unless specifically assigned. Duplication only happens if multiple matches exist.
Which query type is used to provide subtotals and grand totals, such as sales per region plus an overall total?
Explanation: ROLLUP is a GROUP BY extension that adds subtotal and total rows to aggregation results. PIVOT transforms rows to columns, UNION combines results of multiple queries, and INTERSECT returns common rows from queries.
What SQL keyword can be used in a SELECT query to remove duplicate rows from the returned result set?
Explanation: DISTINCT returns only unique rows in the result set. UNIQUE is not a SQL keyword for this purpose, though it's a constraint on columns. FILTER and GROUP serve other roles, like conditional aggregation and grouping.
In database terminology, what does an 'upsert' typically do during data transformations?
Explanation: An upsert inserts new records or updates existing ones if they conflict with primary keys, enabling merge-like functionality. Deleting rows, selecting rows, and renaming columns are unrelated operations.
To select the most recent order for each customer, which SQL construct is commonly required?
Explanation: Window functions, such as ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...), help pick the latest entry per group. CROSS JOIN creates all combinations; CASCADE and TRIGGER relate to other database features.
When deduplicating rows based on email in a users table, which SQL technique is often used?
Explanation: A self-join allows comparing rows within the same table to find or remove duplicates. RIGHT JOIN relates to joining two different tables, DROP TABLE deletes data structures, and MINUS returns set differences.
Which aggregate function would you use to calculate total revenue by joining orders and products tables?
Explanation: SUM is used to add together numeric values, such as total revenue. COUNT tallies rows, MEDIAN is not standard in all SQL dialects, and LIST is not a common aggregate function.
What does a FULL OUTER JOIN between tables A and B return?
Explanation: FULL OUTER JOIN gives every row from both tables, placing NULLs in columns where no match exists. It does not restrict to just matching or non-matching rows, nor does it limit the result to just table A.
After grouping sales by item, how do you filter groups to show only those with totals above 100?
Explanation: HAVING filters the results after aggregation, such as groups of sales totals. WHERE operates before grouping, INTO creates new tables, and AS is used for renaming (aliasing).
What is created when you perform a CROSS JOIN between two small tables?
Explanation: A CROSS JOIN produces every possible combination of rows, known as a Cartesian product. It does not filter or match rows, nor is it related to uniqueness or nulls for unmatched data.
To show all customers, even those without any sales, along with their sales if available, which JOIN is appropriate?
Explanation: LEFT JOIN includes all customers and attaches available sales, using NULLs where none exist. INNER JOIN only returns matches, RIGHT JOIN is the reverse direction, and NATURAL JOIN automatically joins on columns with the same name but doesn't specifically include all records from one side.
Which function can help ensure only a single unique row is selected for each group in an aggregation transformation?
Explanation: ROW_NUMBER() assigns a unique sequential integer to rows within a partition, helping pick one row per group. SUM() aggregates values, FIRST() is not a standard SQL function, and COLLATE is used for sorting and comparison settings.
Which SQL operator can you use in conjunction with GROUP BY to find duplicate email addresses in a contact list?
Explanation: HAVING COUNT(*) u003E 1 filters grouped results to show only values that occur more than once, essential for finding duplicates. ORDER BY simply sorts data, MINUS shows differences between result sets, and OVERLAP is not standard for deduplication.