Advanced SQL NOTE Answers to Review Questions | Contains 54 Questions & Answers

Advanced SQL NOTE Answers to Review Questions

Chapter 8 Advanced SQL

Chapter 8

Advanced SQL NOTE

 Answers to Review Questions

1. What is a CROSS JOIN? Give an example of its syntax.

2. What three join types are included in the OUTER JOIN classification?

3. Using tables named T1 and T2, write a query example for each of the three join types you

described in Question 10. Assume that T1 and T2 share a common column named C1.

4. What is a subquery, and what are its basic characteristics?

5. What are the three types of results a subquery can return?

6. What is a correlated subquery? Give an example.

7. Explain the difference between a regular subquery and a correlated subquery.

8. What does it mean to say that SQL operators are set-oriented?

9. The relational set operators UNION, INTERSECT, and MINUS work properly only if the

relations are union-compatible. What does union-compatible mean, and how would you check

for this condition?

10. What is the difference between UNION and UNION ALL? Write the syntax for each.

ID Name

11. Suppose that you have two tables, EMPLOYEE and EMPLOYEE_1. The EMPLOYEE table

contains the records for three employees: Alice Cordoza, John Cretchakov, and Anne

McDonald. The EMPLOYEE_1 table contains the records for employees John Cretchakov and

Mary Chen. Given that information, what is the query output for the UNION query? (List the

query output.)

12. Given the employee information in Question 11, what is the query output for the UNION ALL

query? (List the query output.)

13. Given the employee information in Question 11, what is the query output for the INTERSECT

query? (List the query output.)

14. Given the employee information in Question 1, what is the query output for the MINUS

query? (List the query output.)

30

15. Why does the order of the operands (tables) matter in a MINUS query but not in a UNION

query?

16. What MS Access/SQL Server function should you use to calculate the number of days between

the current date and January 25, 1999?

17. What Oracle function should you use to calculate the number of days between the current date

and January 25, 1999?

18. Suppose that a PRODUCT table contains two attributes, PROD_CODE and VEND_CODE.

Those two attributes have values of ABC, 125, DEF, 124, GHI, 124, and JKL, 123, respectively.

The VENDOR table contains a single attribute, VEND_CODE, with values 123, 124, 125, and

126, respectively. (The VEND_CODE attribute in the PRODUCT table is a foreign key to the

VEND_CODE in the VENDOR table.) Given that information, what would be the query

output for:

a. A UNION query based on these two tables?

b. A UNION ALL query based on these two tables?

c. An INTERSECT query based on these two tables?

d. A MINUS query based on these two tables?

19. What string function should you use to list the first three characters of a company’s

EMP_LNAME values? Give an example, using a table named EMPLOYEE.

20. What is an Oracle sequence? Write its syntax.

1. Window Functions vs. Aggregate Functions

• Aggregate Functions (e.g., SUM, AVG, COUNT) group multiple rows into a single output row.
• Window Functions (e.g., ROW_NUMBER(), RANK(), LEAD()) perform calculations across a set of rows related to the current row without collapsing them into one row.
• Key Clause: Window functions use the OVER() clause to define the data "window" (partitioning and ordering). 

2. CTE (Common Table Expressions) vs. Subqueries 

• CTE: Defined using the WITH clause, it creates a named temporary result set that is more readable and can be referenced multiple times in a single query.
• Subquery: An inner query nested inside another statement. CTEs are generally preferred for complex, multi-step logic due to better readability and maintenance.
• Recursive CTE: A specialized CTE that references itself, useful for querying hierarchical data like org charts. 

3. Indexing & Optimization

• Clustered Index: Determines the physical order of data in a table; there can only be one per table.
• Non-Clustered Index: A separate structure from the data rows that contains pointers to the data; multiple are allowed per table.
• Optimization: Improving slow queries involves analyzing Execution Plans (EXPLAIN), ensuring proper indexing, and avoiding "non-sargable" queries (e.g., using functions on indexed columns in a WHERE clause). 

4. Advanced Joins & Set Operations

• Self-Join: Joining a table to itself, often to compare rows within the same table (e.g., finding employees and their managers in the same Employees table).
• UNION vs. UNION ALL: UNION removes duplicate rows between result sets, while UNION ALL retains all rows, making it faster.
• CROSS JOIN: Returns the Cartesian product (every possible combination) of rows from two tables. 

5. Data Integrity & Constraints

• ACID Properties: Ensures reliable transactions through Atomicity (all or nothing), Consistency (valid state), Isolation (concurrent safety), and Durability (permanent changes).
• DELETE vs. TRUNCATE vs. DROP:
  • DELETE: Removes specific rows; logged and reversible via rollback.
  • TRUNCATE: Removes all rows from a table but keeps the structure; faster and uses fewer resources.
  • DROP: Completely removes the table structure and its data from the database. 

6. Complex Functions

• COALESCE vs. ISNULL: COALESCE is a standard SQL function that returns the first non-null value from a list of multiple arguments, whereas ISNULL is often database-specific (like SQL Server) and only handles two arguments.
• RANK() vs. DENSE_RANK(): Both assign ranks based on ordering. If there is a tie, RANK() skips subsequent numbers (e.g., 1, 2, 2, 4), while DENSE_RANK() does not (e.g., 1, 2, 2, 3). 

For further practice, you can find interactive challenges on platforms like HackerRank or review more in-depth documentation on Microsoft Learn and Oracle Docs.