Unit 3 - Practice Quiz

The COUNT() function returns the number of rows that matches a specified criterion. COUNT(*) returns the total number of rows in a table.

The MIN() aggregate function is used to find the minimum (smallest) value in a set of values or a column.

An INNER JOIN selects records that have matching values in both tables, effectively finding the intersection of the two tables based on the join condition.

A LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table, and the matched rows from the right table. The result is NULL from the right side if there is no match.

The UNION operator is used to combine the result sets of two or more SELECT statements. By default, it removes duplicate rows from the combined result set.

The INTERSECT operator returns the common rows that appear in the result sets of two or more SELECT statements.

A view is a stored query that acts as a virtual table. It does not store data itself but displays data from one or more underlying tables.

The CREATE VIEW statement is the standard SQL command used to define and create a new view in the database.

A subquery, also known as an inner query or nested query, is a query that is embedded within the WHERE, FROM, or SELECT clause of another SQL query.

This is a classic example of a subquery. The inner query (SELECT AVG(salary) FROM employees) is executed first, and its result is used by the outer query to filter employees.

The Projection operation, denoted by pi (), selects specified columns (attributes) from a relation, discarding the other columns.

The Selection operator () is used to filter tuples (rows) based on a condition, which is functionally equivalent to the WHERE clause in an SQL query.

The OVER() clause is mandatory for all window functions. It defines the 'window' or set of rows over which the function operates.

ROW_NUMBER() assigns a unique integer to every row within its partition, starting from 1. It is often used for pagination or identifying unique rows.

A hash collision occurs when the hash function generates the same output (hash value) for two distinct inputs (keys). Hashing systems must have a strategy to handle collisions.

Hashing is used to quickly locate a data record given its search key. The hash function computes an address in the hash table where the record should be stored or found.

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Instead of scanning the entire table, the database can use the index to find the location of the data quickly.

The CREATE INDEX statement is the standard SQL command used to create an index on one or more columns of a table to improve query performance.

The query optimizer's role is to analyze an SQL query and determine the most efficient way to execute it, considering factors like indexes, joins, and data distribution, to minimize resource usage and response time.

An execution plan (or query plan) is an ordered set of steps used to access data in a SQL relational database. It details how the database will perform tasks like scanning tables, using indexes, and joining data.

The query first groups the rows by Salesperson.

• For Alice: SUM(Amount) is 100 + 200 = 300, and COUNT(*) is 2.
• For Bob: SUM(Amount) is 150 + 50 = 200, and COUNT(*) is 2.
• For Charlie: SUM(Amount) is 120, and COUNT(*) is 1.

The HAVING clause then filters these groups.

• For Alice: SUM(Amount) > 200 (300 > 200 is true) AND COUNT(*) >= 2 (2 >= 2 is true). So, Alice is included.
• For Bob: SUM(Amount) > 200 (200 > 200 is false). So, Bob is excluded.
• For Charlie: SUM(Amount) > 200 (120 > 200 is false) AND COUNT(*) >= 2 (1 >= 2 is false). So, Charlie is excluded.

Therefore, only 'Alice' is returned.

Aggregate functions like SUM, AVG, COUNT(column_name), etc., ignore NULL values in their calculations. COUNT(*) is a special case that counts all rows in the table or group, regardless of NULL values. COUNT(commission_pct) will only count the rows that have a non-null value for the commission percentage. COUNT(DISTINCT ...) also ignores NULLs.

A LEFT JOIN initially includes all rows from the left table (Employees). However, the WHERE clause is applied after the join operation. The condition d.dept_name = 'Sales' will filter the joined result set. For employees not in the 'Sales' department, d.dept_name would be NULL, and NULL = 'Sales' evaluates to unknown (false for filtering purposes). This effectively transforms the LEFT JOIN into an INNER JOIN for this specific query.

This problem requires comparing a row in a table with another row in the same table, a classic use case for a self-join.

• Option A uses an explicit JOIN syntax to join the Employee table to itself, aliased as e (for employee) and m (for manager).
• Option C uses the older, implicit comma-join syntax, which is functionally the same as the JOIN in Option A.
• Option B uses a correlated subquery, which for each employee e, looks up their manager's salary. All three approaches will yield the same correct result.

Let's call the set of distinct rows in T1 as D1. .
Let's call the set of distinct rows in T2 as D2. .
T1 and T2 share 3 common rows. This means .
The result of the UNION is the set .
.

The query groups rows by customer_id and then applies two filtering conditions in the HAVING clause.

1. Group for customer_id 101: COUNT(DISTINCT product_category) is 2 ('Books', 'Electronics'). SUM(order_value) is 50 + 150 + 30 = 230. Both conditions 2 > 1 and 230 > 200 are true. This customer_id is kept.
   Recalculating with a better query: HAVING COUNT(DISTINCT product_category) > 1 AND AVG(order_value) < 150
2. Group for customer_id 101: COUNT(DISTINCT product_category) = 2. AVG(order_value) = 230 / 3 = 76.67. Both 2 > 1 and 76.67 < 150 are true. So 101 is selected.
3. Group for customer_id 102: COUNT(DISTINCT product_category) = 2. AVG(order_value) = 275 / 2 = 137.5. Both 2 > 1 and 137.5 < 150 are true. So 102 is selected.
4. Group for customer_id 103: COUNT(DISTINCT product_category) = 1. The first condition 1 > 1 is false. So 103 is not selected.
   Ok, my original query was fine, I'll stick with it. Let's re-verify my original explanation. My explanation was faulty.
5. Group for customer_id 101: Distinct categories = 2 ('Books', 'Electronics'). Sum = 230. Conditions 2 > 1 (True) AND 230 > 200 (True). Result: 101 is selected.
6. Group for customer_id 102: Distinct categories = 2 ('Electronics', 'Home Goods'). Sum = 275. Conditions 2 > 1 (True) AND 275 > 200 (True). Result: 102 is selected.
7. Group for customer_id 103: Distinct categories = 1 ('Books'). Sum = 80. Condition 1 > 1 is False. Result: 103 is not selected.
   Therefore, the correct answer is 101, 102. Let me change the question to make only one answer correct to avoid ambiguity in options.
   New query: HAVING COUNT(DISTINCT product_category) = 2 AND SUM(order_value) < 250
8. 101: COUNT(DISTINCT)=2, SUM=230. Conditions 2=2 (T) AND 230 < 250 (T). 101 is selected.
9. 102: COUNT(DISTINCT)=2, SUM=275. Conditions 2=2 (T) AND 275 < 250 (F). 102 is not selected.
10. 103: COUNT(DISTINCT)=1. Condition 1=2 (F). 103 is not selected.

sql
SELECT customer_id
FROM Orders
GROUP BY customer_id
HAVING COUNT(DISTINCT product_category) = 2 AND SUM(order_value) < 250;

COUNT(*) counts all rows, giving the total number of projects. COUNT(completion_date) counts only the non-NULL values in the completion_date column, correctly identifying completed projects. COUNT(DISTINCT employee_id) counts the number of unique employee IDs, correctly identifying the number of distinct employees involved. The other options misuse the COUNT function.

A FULL OUTER JOIN returns all rows when there is a match in one of the tables.

• The row with id=2 exists in both tables, so it will produce one joined row: (2, 'B', 2, 'X').
• The row with id=1 exists only in TableA, so it will produce a row with NULLs for TableB columns: (1, 'A', NULL, NULL).
• The row with id=3 exists only in TableB, so it will produce a row with NULLs for TableA columns: (NULL, NULL, 3, 'Y').
  In total, there will be 3 rows.

In a self-join like FROM Employees e JOIN Employees m ON e.manager_id = m.id, the join condition e.manager_id = m.id itself handles the exclusion. If an employee e has a NULL manager_id, the join condition NULL = m.id will not evaluate to true for any row m, so that employee will be naturally excluded from an INNER JOIN result. The condition WHERE e.manager_id IS NOT NULL would be redundant in an INNER JOIN but necessary for an outer join if you wanted to filter after the join.

The EXCEPT operator takes the distinct rows from the first query and returns only the ones that do not appear in the second query's result.

1. First, the distinct rows from T1 are determined: {10, 20, 30}.
2. The distinct rows from T2 are {20, 30, 40}.
3. The operator then removes rows from the first set that are present in the second set. It calculates {10, 20, 30} EXCEPT {20, 30, 40}.
4. The rows 20 and 30 are removed, leaving only {10}. The query returns 1 row.

This operation is known as a symmetric difference. The first part, (StudentsA EXCEPT StudentsB), finds all students who are in A but not in B. The second part, (StudentsB EXCEPT StudentsA), finds all students who are in B but not in A. The UNION operator combines these two disjoint sets to produce the final desired result. The other options find the union (all unique students), intersection (only students in both), or union with duplicates.

A view is generally updatable if each row in the view corresponds to exactly one row in a single base table. The use of GROUP BY and aggregate functions (SUM) means a row in the view (customer_id, total_spent) represents multiple rows from the Orders table. The database cannot determine how to unambiguously insert a new record into the base table through such a view. Therefore, it is not updatable.

A materialized view pre-computes and physically stores its result set. This makes querying the view very fast, as the underlying complex query doesn't need to be re-executed. The main drawback is that the stored data does not automatically update when the base tables change. It must be manually or automatically refreshed, which means the data can be stale between refreshes. A standard view, in contrast, always provides real-time data but may be slower to query.

This is a correlated subquery using EXISTS. For each product p in the outer query, the inner query checks if there is at least one corresponding row in OrderItems that satisfies the condition quantity > 100. The EXISTS operator returns true if the subquery returns one or more rows, and false otherwise. The SELECT 1 is an optimization; the actual values returned by the subquery do not matter, only whether any rows are returned. Therefore, it finds products that appear in any single order with a quantity over 100.

Logically, a non-correlated subquery (like one often used with IN) can be evaluated independently and just once. Its result set is materialized and then used by the outer query. A correlated subquery (like the one typically used with EXISTS) has a dependency on the outer query's current row, so it must be logically re-evaluated for each row processed by the outer query. While modern optimizers can often rewrite these queries, this represents the fundamental conceptual difference in their execution flow.

The query first filters the Employee relation and then projects the desired columns. In relational algebra:

1. The WHERE clause corresponds to the selection operator (). The conditions can be combined: .
2. The SELECT clause corresponds to the projection operator (). This is applied to the result of the selection: .
   Combining these gives the correct expression. Applying projection before selection is incorrect because the dept_id and salary attributes would be lost before they could be used for filtering.

For the set-based relational algebra operators Union, Intersection, and Difference (Except), the relations must be union-compatible. This means two things: 1) they must have the same arity (number of attributes/columns), and 2) the data types (domains) of the i-th attribute in the first relation must be compatible with the i-th attribute in the second relation. The names of the attributes do not have to be the same.

RANK() assigns the same rank to rows with tied values, but then skips the next rank(s) in the sequence. Here, Bob and Chloe both get rank 2, and the next rank assigned is 4.

• DENSE_RANK() would produce 1, 2, 2, 3 (it doesn't skip ranks).
• ROW_NUMBER() would produce 1, 2, 3, 4 (it assigns a unique number to each row regardless of ties).
• NTILE(4) would divide the rows into 4 buckets, producing 1, 2, 3, 4.

This requires comparing an employee's salary with the salary from the previous row in a sorted set.

• PARTITION BY department is used to create separate windows for each department.
• ORDER BY salary DESC sorts employees within each department from highest to lowest salary.
• LAG(salary, 1) accesses the salary from the previous row in the ordered partition, which corresponds to the employee with the next highest salary. LEAD would access the next row (next lowest salary).

In Extendible Hashing, the directory's size is determined by the global depth, d. When a bucket with a local depth d' equal to the global depth d becomes full and needs to split, its records are rehashed. The local depth of the two new buckets becomes d+1. Since there is now a bucket with a local depth greater than the global depth, the directory must be doubled in size, and the global depth is incremented to d+1.

1. First, calculate the initial hash value for the key 25: h(25) = 25 mod 8 = 1. The key should ideally go into Bucket 1.
2. The problem states that Bucket 1 is already occupied. With linear probing, we check the next sequential bucket.
3. Bucket 2 is checked. It is also occupied.
4. Bucket 3 is checked. It is also occupied.
5. Bucket 4 is checked. Assuming it is empty, the key 25 will be placed in Bucket 4.

B+ Tree indexes are ordered structures that are highly efficient for range queries (e.g., using BETWEEN, <, >). The leaf nodes of the tree are linked together, allowing the database to find the start of the range and then simply scan along the leaf-level pages to find all matching entries. A Hash index is only efficient for equality lookups (=) and cannot be used effectively for range queries. Full-text and Spatial indexes are for specialized data types and searches.

A composite index on (country, city) is optimal because the index entries will be sorted first by country, and then by city. The database can perform a single, efficient lookup to find the start of the 'USA' entries and then scan a small portion of that index to find the 'New York' entries. A (city, country) index would be less efficient as it would have to scan all cities named 'New York' worldwide before filtering by country. Two separate indexes are less efficient as the database would have to fetch results from both and find the intersection, which is more work.

This optimization is called "subquery unnesting" or "subquery flattening". The optimizer rewrites the IN clause, which uses a subquery, into an explicit JOIN. This often allows the planner to consider more efficient join algorithms (like hash join or merge join) and better join orders, typically resulting in a more performant execution plan than evaluating the subquery for each row or materializing its full result set first.

A primary heuristic in query optimization is to perform the most selective operations first. A highly selective join is one that produces a very small number of rows relative to the input sizes. By joining S and T first, the optimizer creates a very small intermediate result set (only 10 rows). This small set is then joined with the large R table. The alternative, (R JOIN S), would create a large intermediate result (20,000 rows) that must then be joined with T, which is computationally more expensive.

The key is how aggregate functions handle NULLs.

• COUNT(*) (A) counts all rows in the group.
• COUNT(Amount) (B) counts only the rows where Amount is NOT NULL.
• AVG(Amount) (D) calculates SUM(Amount) / COUNT(Amount), effectively ignoring NULL values completely.
• The expression for C, SUM(Amount) / COUNT(*), uses the sum of non-NULL amounts but divides by the total number of rows (including those with NULL amounts). This effectively treats the NULL amounts as zero in the average calculation, which is different from how AVG() works. Therefore, C and D will produce different results if NULL values exist.

This is a complex self-join and anti-join problem. Let's analyze the correct option:

1. FROM Employees E JOIN Managers M1 ON E.ID = M1.ManagerID: This part selects all employees who are listed as a ManagerID for someone else. This effectively finds all managers.
2. LEFT JOIN Managers M2 ON E.ID = M2.ID: This joins the results (the managers) back to the Managers table on the employee's own ID. For any given employee, this join will find their own entry in the Managers table.
3. WHERE M2.ManagerID IS NULL: This is the crucial filter. After the LEFT JOIN, if M2.ManagerID is NULL, it means one of two things: either the employee wasn't in the Managers table at all, or their ManagerID field was NULL. The first join already ensures we are only considering managers. Therefore, this condition filters for managers whose own ManagerID is NULL, which is the definition of a top-level manager. The other options are flawed; for instance, B incorrectly excludes managers who might manage someone but also have a NULL ManagerID entry.

Let's check the options. 3, 4, 5, 2. My result is 4. Why could the answer be 3? Let me trace again very carefully.

• T1 EXCEPT T2 -> {1}. One row.
• T2 EXCEPT T1 -> {4}. One row.
• (Result 1) UNION ALL (Result 2) -> {1}, {4}. Two rows.
• T1 INTERSECT T2 -> {2}, {3}. Two rows.
• (Result of UNION ALL) UNION (Result of INTERSECT) -> {1}, {4} \cup {2}, {3}. Resulting set is {1, 2, 3, 4}. This has 4 rows.

Let's re-read again. (A except B) union all (B except A) is the symmetric difference. (A intersect B) is the intersection. A union of these two things is the total set of unique elements across both tables. The set of unique elements is {1, 2, 3, 4}. The count is 4.

The choice between join algorithms depends heavily on table sizes, indexes, and available memory.

• A Hash Join requires building a hash table on one of the tables (usually the smaller one). This has a significant startup cost, as the entire build-side table must be read and processed. Its strength is scanning large inputs without indexes.
• An Indexed Nested Loop Join iterates through the rows of the outer table and, for each row, performs an efficient index seek on the inner table. This is extremely efficient if the outer table is small and the inner table has a suitable index.

In the scenario described in the correct option: Table A is very small, so the outer loop will run only a few times. Table B has an index on id and val, so for each of the few rows from A, the lookup into B will be nearly instantaneous. The total cost is very low. A Hash Join, in contrast, would still have to build a hash table from table A and then scan all of table B, which would be much more work than the few index seeks required by the nested loop. The predicate B.val < 100 can be efficiently applied using the index as well. The other options describe scenarios where Hash Join is often superior (large tables with no indexes) or competitive (highly selective joins, tables in memory).