Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

Data Analyst > CHAPTER 6: Building on Subqueries, Common Table Expressions, and Unions > Flashcards

CHAPTER 6: Building on Subqueries, Common Table Expressions, and Unions Flashcards

(91 cards)

Start Studying
1
Q

Q: What is a subquery in T-SQL?

A

A: A subquery is a nested query—a query within another query—that can return results for use in the main query.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Q: Where can subqueries be used in a SQL query?

A

A: Subqueries can be used in the SELECT, FROM, and WHERE clauses, or as part of other expressions.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Q: How is a subquery used in an IN list?

A

A: A subquery can replace a hard-coded list in the WHERE clause using the syntax:

SELECT <columns>
FROM <table>
WHERE <column> IN (SELECT <column> FROM <table>);</column></column></columns>

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Q: What is the difference between joining tables and using a subquery in the WHERE clause?

A

A: A join includes columns from both tables in the result, while a subquery returns a filtered list of values for comparison in the main query.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Q: How do you use a subquery to find rows not in another table?

A

A: Add NOT before IN in the query:

SELECT <columns>
FROM <table1>
WHERE <column> NOT IN (SELECT <column> FROM <table2>);</table2></column></column></table1></columns>

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Q: What happens if a subquery returns a NULL in a NOT IN clause?

A

A: If the subquery returns any NULL values, the NOT IN clause will return no rows due to SQL’s three-valued logic (TRUE, FALSE, UNKNOWN).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Q: How can you handle NULL values in a subquery?

A

A: Add a WHERE condition in the subquery to exclude NULL values:

SELECT <columns>
FROM <table1>
WHERE <column> NOT IN (SELECT <column> FROM <table2> WHERE <column> IS NOT NULL);</column></table2></column></column></table1></columns>

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Q: Why is it important to eliminate NULL in subqueries?

A

A: To ensure accurate results when using operators like NOT IN, which cannot handle NULL values correctly without additional filtering.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Q: When should you use a subquery instead of a join?

A

A: Use subqueries when you need to filter data without including additional columns from the related table in the result.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Q: Can subqueries return more than one column?

A

A: Subqueries in the WHERE clause must return only one column, but subqueries in other clauses (e.g., FROM) can return multiple columns.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Q: What is the purpose of the EXISTS keyword in T-SQL?

A

A: EXISTS checks whether a subquery returns any rows. If rows exist, it evaluates to TRUE for the outer query.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Q: How does the EXISTS clause work in the WHERE statement?

A

A: It uses a correlated subquery to determine if rows in the subquery match the outer query condition.

SELECT CustomerID, AccountNumber
FROM Sales.Customer AS Cust
WHERE EXISTS (SELECT 1 FROM Sales.SalesOrderHeader AS SOH WHERE SOH.CustomerID = Cust.CustomerID);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Q: What does NOT EXISTS do in T-SQL?

A

A: It returns TRUE for rows in the outer query if no rows in the subquery meet the condition.

SELECT CustomerID, AccountNumber
FROM Sales.Customer AS Cust
WHERE NOT EXISTS (SELECT 1 FROM Sales.SalesOrderHeader AS SOH WHERE SOH.CustomerID = Cust.CustomerID);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Q: Why can you use SELECT * or SELECT 1 in EXISTS subqueries?

A

A: The subquery only checks for the presence of rows, not the returned data. Both forms are functionally identical.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Q: What is the purpose of CROSS APPLY?

A

A: CROSS APPLY joins each row from the left table with the results of a subquery. The subquery runs once for each row in the left table and returns only rows that match.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Q: How does CROSS APPLY differ from a regular join?

A

A: CROSS APPLY allows you to use a subquery that can reference columns from the outer query, enabling row-by-row computation.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Q: What is OUTER APPLY in T-SQL?

A

A: OUTER APPLY works like a LEFT OUTER JOIN. It returns all rows from the left table, including rows where the subquery does not return results (those will have NULL values).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Q: Provide an example of using CROSS APPLY.

A

SELECT CustomerID, AccountNumber, SalesOrderID
FROM Sales.Customer AS Cust
CROSS APPLY (SELECT SalesOrderID FROM Sales.SalesOrderHeader AS SOH WHERE Cust.CustomerID = SOH.CustomerID) AS A;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Q: Provide an example of using OUTER APPLY.

A

SELECT CustomerID, AccountNumber, SalesOrderID
FROM Sales.Customer AS Cust
OUTER APPLY (SELECT SalesOrderID FROM Sales.SalesOrderHeader AS SOH WHERE Cust.CustomerID = SOH.CustomerID) AS A;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Q: When should you use EXISTS over a join or subquery in the WHERE clause?

A

A: Use EXISTS when you only need to verify the existence of matching rows in a subquery without returning actual data from it.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Q: What is the main difference between CROSS APPLY and OUTER APPLY?

A

A: CROSS APPLY behaves like an INNER JOIN, only returning rows where the subquery produces results, whereas OUTER APPLY behaves like a LEFT OUTER JOIN, returning all rows from the left table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Q: Why might EXISTS perform better than other techniques?

A

A: EXISTS stops evaluating as soon as it finds a match, making it efficient for large datasets with selective filtering.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

Q: What is the purpose of the UNION operator in T-SQL?

A

A: UNION combines the results of two or more queries into a single result set, removing duplicate rows by default.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

Q: What is the difference between UNION and UNION ALL?

A

A: UNION removes duplicate rows, while UNION ALL includes all rows, even duplicates, which improves performance if deduplication is unnecessary.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q: What must match between the queries in a UNION statement?
A: Each query must return the same number of columns, and the columns must have compatible data types.
26
Q: Provide the syntax for a simple UNION query.
SELECT col1, col2 FROM table1 UNION SELECT col1, col2 FROM table2;
27
Q: Why should you use UNION ALL when possible?
A: UNION ALL avoids the overhead of removing duplicates, which improves performance, especially with large datasets.
28
Q: Where must the ORDER BY clause be placed in a UNION query?
A: The ORDER BY clause must be placed at the end of the entire UNION query, not after individual queries. Example: SELECT col1 FROM table1 UNION SELECT col1 FROM table2 ORDER BY col1;
29
Q: How does SQL Server determine column names in a UNION query?
A: The column names and data types are determined by the first query in the UNION.
30
Q: What happens if the columns or data types don’t match between queries in a UNION?
A: SQL Server will throw an error if the column counts differ or if incompatible data types cannot be implicitly converted.
31
Q: Provide an example of a UNION query with duplicate elimination.
SELECT CustomerID FROM Sales.Customers UNION SELECT CustomerID FROM Sales.ArchivedCustomers;
32
Q: Provide an example of a UNION ALL query to combine results without deduplication.
SELECT ProductID, Name FROM Products_Current UNION ALL SELECT ProductID, Name FROM Products_Discontinued;
33
Q: What error will occur if the number of columns doesn’t match in a UNION query?
A: SQL Server will throw an error indicating a mismatch in the number of columns.
34
Q: Why is it important to use compatible data types in UNION queries?
A: SQL Server follows precedence rules, and incompatible types (e.g., mixing strings and integers) can cause errors or unexpected implicit conversions.
35
Q: What is a good use case for a UNION query?
A: Combining production and archived data or displaying data from different divisions of a company as a unified result.
36
Q: Provide an example of a UNION query that uses the same table with different WHERE clauses.
SELECT CustomerID, OrderDate FROM Orders WHERE Year(OrderDate) = 2023 UNION SELECT CustomerID, OrderDate FROM Orders WHERE Year(OrderDate) = 2022;
37
Q: What is the purpose of the EXCEPT operator in T-SQL?
A: EXCEPT returns rows from the first query that are not present in the second query, removing duplicates by default.
38
Q: Provide an example of an EXCEPT query.
SELECT BusinessEntityID FROM HumanResources.Employee EXCEPT SELECT BusinessEntityID FROM Person.Person;
39
Q: How does EXCEPT handle duplicates?
A: EXCEPT removes duplicates from the results unless explicitly overridden by using additional query constructs.
40
Q: When should you use EXCEPT in queries?
A: Use EXCEPT when you need to find rows in one query that do not exist in another query, often for identifying mismatches or gaps.
41
Q: What rules must be followed when using EXCEPT?
A: Both queries must return the same number of columns, and their data types must be compatible.
42
Q: What does the INTERSECT operator do in T-SQL?
A: INTERSECT returns rows that exist in both queries, removing duplicates by default.
43
Q: Provide an example of an INTERSECT query.
SELECT BusinessEntityID FROM HumanResources.Employee INTERSECT SELECT BusinessEntityID FROM Person.Person;
44
Q: How does INTERSECT handle duplicate rows?
A: INTERSECT automatically removes duplicate rows, ensuring only unique common rows are returned.
45
Q: When is it appropriate to use INTERSECT?
A: Use INTERSECT when you want to find common rows between two queries, such as overlapping records between two datasets.
46
Q: What are the requirements for columns and data types in an INTERSECT query?
A: The columns in both queries must match in number and have compatible data types.
47
Q: What is the difference between EXCEPT and INTERSECT?
A: EXCEPT returns rows from the first query that are not in the second query, while INTERSECT returns rows that are present in both queries.
48
Q: Can EXCEPT or INTERSECT include an ORDER BY clause?
A: Yes, but the ORDER BY clause must be placed after the entire EXCEPT or INTERSECT operation, not within the individual queries.
49
Q: How can EXCEPT be used for data quality checks?
A: EXCEPT can identify rows in one table that are missing in another, such as detecting missing foreign key references.
50
Q: How can INTERSECT assist in verifying data consistency?
A: INTERSECT can be used to find rows that are common across datasets, ensuring data alignment between two sources.
51
Q: What is a derived table in T-SQL?
A: A derived table is a subquery that appears in the FROM clause of a query and is treated like a temporary table.
52
Q: Why use a derived table?
A: Derived tables isolate part of a query's logic, making the query modular and easier to understand and manage.
53
Q: What is the syntax for using a derived table?
SELECT FROM (SELECT FROM ) AS ;
54
Q: Must a derived table be aliased?
A: Yes, a derived table always requires an alias to reference its columns in the outer query.
55
Q: Can a derived table contain multiple tables or a WHERE clause?
A: Yes, a derived table can include joins, multiple tables, and a WHERE clause.
56
Q: Can a derived table include an ORDER BY clause?
A: Only if the TOP keyword is used.
57
Q: Can a derived table reference columns not included in its SELECT list?
A: No, all columns needed for joins or output in the outer query must be explicitly included in the derived table's SELECT list.
58
Q: Provide an example of a derived table in an INNER JOIN.
SELECT c.CustomerID, s.SalesOrderID FROM Sales.Customer AS c INNER JOIN ( SELECT SalesOrderID, CustomerID FROM Sales.SalesOrderHeader ) AS s ON c.CustomerID = s.CustomerID;
59
Q: How can derived tables be combined with joins?
A: Derived tables can be used with any type of join, such as INNER JOIN, LEFT JOIN, CROSS JOIN, or FULL JOIN.
60
Q: Can derived tables reference a CTE?
A: Yes, a derived table can reference a CTE defined earlier in the query.
61
Q: Can a derived table contain another derived table?
A: Yes, derived tables can be nested within other derived tables for complex queries.
62
Q: What is one limitation of derived tables compared to other T-SQL techniques?
A: Derived tables cannot define their own CTEs within their scope.
63
Q: When is it beneficial to use a derived table?
A: Use derived tables to encapsulate logic, simplify joins, or perform intermediate calculations.
64
Q: What happens if a required column is omitted from the derived table's SELECT list?
A: The query will fail because the outer query cannot access columns that are not explicitly selected in the derived table.
65
Q: What is a Common Table Expression (CTE)?
A: A CTE is a temporary result set defined within a query that exists only for the duration of the query.
66
Q: How does a CTE differ from a derived table?
A: Unlike a derived table, a CTE is defined separately using the WITH keyword and can be referenced multiple times in the main query.
67
Q: What happens to a CTE after the main query runs?
A: A CTE goes out of scope and cannot be reused once the query has completed execution.
68
Q: What is the basic syntax for defining a CTE?
WITH AS (SELECT FROM
) SELECT FROM ;
69
Q: How do you define column aliases in a CTE?
A: Specify the aliases after the CTE name: WITH CTE_Name ([Column1], [Column2]) AS ( SELECT Column1, Column2 FROM Table ) SELECT * FROM CTE_Name;
70
Q: What is the required placement of the WITH keyword in a query?
A: The WITH keyword must be the first statement in the batch or be preceded by a semicolon if other statements appear before it.
71
Q: Can a CTE include joins, WHERE clauses, or expressions?
A: Yes, a CTE can include joins, filters, expressions, and even reference other tables or views.
72
Q: Provide an example of a CTE used for an INNER JOIN.
WITH Orders AS ( SELECT SalesOrderID, CustomerID, TotalDue + Freight AS Total FROM Sales.SalesOrderHeader ) SELECT c.CustomerID, o.SalesOrderID, o.Total FROM Sales.Customer AS c INNER JOIN Orders AS o ON c.CustomerID = o.CustomerID;
73
Q: What happens if a column alias is not defined in a CTE?
A: The column name from the CTE query is used, but defining aliases upfront can improve readability, especially for expressions.
74
Q: Can a CTE be reused across multiple queries?
A: No, a CTE is valid only for the query in which it is defined.
75
Q: Can a CTE reference another CTE?
A: Yes, one CTE can reference another within the same WITH clause.
76
Q: Is it possible to use ORDER BY in a CTE?
A: Yes, but only when the TOP keyword is used.
77
Q: How do you ensure the CTE is correctly referenced in complex queries?
A: Always use descriptive aliases and verify that required columns are included in the CTE's SELECT list.
78
Q: What advanced capabilities make CTEs superior to derived tables in some scenarios?
A: CTEs can be recursive, allowing them to solve hierarchical and iterative problems.
79
Q: What is one major use case for CTEs in advanced queries?
A: CTEs are commonly used for breaking down complex logic into manageable parts or simplifying nested queries.
80
Q: What is the main difference between UNION and UNION ALL?
A: UNION removes duplicate rows, while UNION ALL includes all rows, including duplicates.
81
Q: Why does UNION perform worse than UNION ALL?
A: UNION uses additional resources to eliminate duplicates, often employing a Hash Match operation to aggregate results.
82
Q: When should you use UNION ALL instead of UNION?
A: Use UNION ALL if you are certain there are no duplicates or duplicates are acceptable in the results.
83
Q: How does SQL Server process duplicate elimination for UNION?
A: It uses the Hash Match Aggregate operator, which can be resource-intensive for large datasets.
84
Q: Compare the estimated costs for UNION and UNION ALL from the example.
UNION query cost: 2.62912 UNION ALL query cost: 0.806502 UNION took ~77% of the resources in the batch.
85
Q: How can you view the performance difference between UNION and UNION ALL?
A: Enable Include Actual Execution Plan in SQL Server Management Studio or use Explain in Azure Data Studio.
86
Q: Why doesn’t wrapping a UNION ALL in a DISTINCT query improve performance?
A: The optimizer processes it like a UNION query internally, performing the same duplicate elimination steps.
87
Q: When should you use DISTINCT with UNION ALL?
A: Use DISTINCT in individual queries if duplicates exist within the individual datasets but not across them.
88
Q: Can using UNION ALL in a CTE or derived table help optimize performance?
A: No, unless the underlying queries ensure no duplicates. Otherwise, using DISTINCT or UNION negates performance gains.
89
Q: What is a best practice for deciding between UNION and UNION ALL?
A: Always prefer UNION ALL if you don’t need to eliminate duplicates, as it improves performance by avoiding unnecessary operations.
90
Q: What is a common operation used by SQL Server to remove duplicates in a UNION query?
A: The Hash Match Aggregate operation.
91
Q: How can you combine improved performance and duplicate elimination if necessary?
A: Use DISTINCT within individual queries and combine them with UNION ALL.

Data Analyst (6 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions