MCQs on Advanced Joins and Subqueries | PostgreSQL Database

PostgreSQL’s advanced features, such as joins, subqueries, and Common Table Expressions (CTEs), empower developers to build complex queries efficiently. This guide presents 30 MCQs to strengthen your PostgreSQL expertise.

Advanced Joins and Subqueries | PostgreSQL

CROSS JOIN and FULL OUTER JOIN

1. What is the result of a CROSS JOIN in PostgreSQL?
   • a) Cartesian product of two tables
   • b) Only matching rows from two tables
   • c) Left-side rows and matching right-side rows
   • d) Right-side rows and matching left-side rows
2. Which statement correctly applies a CROSS JOIN?
   • a) SELECT * FROM table1, table2;
   • b) SELECT * FROM table1 CROSS JOIN table2;
   • c) SELECT * FROM table1 JOIN table2 USING(column_name);
   • d) Both a and b
3. What happens in a FULL OUTER JOIN when no matching rows exist in one table?
   • a) Rows from both tables are excluded
   • b) NULL is returned for non-matching rows
   • c) Only matching rows are included
   • d) Rows from the left table are prioritized
4. Which of the following combines FULL OUTER JOIN with a filter condition?
   • a) WHERE clause
   • b) ON clause
   • c) HAVING clause
   • d) Both a and b
5. What is a major difference between INNER JOIN and FULL OUTER JOIN?
   • a) INNER JOIN excludes unmatched rows; FULL OUTER JOIN includes them with NULLs
   • b) INNER JOIN includes all unmatched rows
   • c) FULL OUTER JOIN excludes duplicate rows
   • d) There is no difference
6. Which join type creates a Cartesian product?
   • a) CROSS JOIN
   • b) FULL OUTER JOIN
   • c) INNER JOIN
   • d) NATURAL JOIN
7. How does PostgreSQL handle duplicate rows in a CROSS JOIN?
   • a) They are eliminated automatically
   • b) They appear in the result set
   • c) They are flagged as errors
   • d) Duplicate rows cannot exist

Subqueries and Correlated Subqueries

8. What is the key feature of a correlated subquery?
   • a) It depends on values from the outer query
   • b) It executes independently of the outer query
   • c) It always returns a single value
   • d) It is always used with GROUP BY
9. Where can subqueries be used in a SQL statement?
   • a) In the SELECT clause
   • b) In the WHERE clause
   • c) In the FROM clause
   • d) All of the above
10. What happens if a subquery returns multiple rows in a WHERE clause?
    • a) An error occurs
    • b) The query succeeds if the operator allows multiple rows
    • c) Only the first row is used
    • d) Subqueries cannot return multiple rows
11. How do correlated subqueries execute in PostgreSQL?
    • a) Once for each row of the outer query
    • b) Only once for the entire outer query
    • c) Parallel to the outer query
    • d) Independently of the outer query
12. Which operator is typically used with subqueries that return a single value?
    • a) =
    • b) IN
    • c) EXISTS
    • d) LIKE
13. Which keyword is used to check for the presence of rows in a subquery?
    • a) EXISTS
    • b) IN
    • c) ANY
    • d) ALL
14. What does the ANY keyword do in subqueries?
    • a) Compares a value against any value in a subquery result
    • b) Requires all subquery results to match
    • c) Ensures the subquery returns one row
    • d) Eliminates duplicates from the result

Common Table Expressions (CTEs)

15. What keyword is used to define a Common Table Expression in PostgreSQL?
    • a) WITH
    • b) AS
    • c) CTE
    • d) USING
16. What is a major benefit of using CTEs?
    • a) They simplify complex queries by breaking them into reusable blocks
    • b) They execute faster than subqueries
    • c) They allow multiple joins in one query
    • d) They avoid the use of indexes
17. Which of the following is true about CTEs?
    • a) They are temporary and exist only during the execution of a query
    • b) They persist in the database after execution
    • c) They are stored as views
    • d) They require an index
18. How can multiple CTEs be defined in a single query?
    • a) Separate them with commas in the WITH clause
    • b) Use a ; between each CTE
    • c) Define each in a separate query
    • d) Use nested WITH statements
19. Can a CTE be recursive in PostgreSQL?
    • a) Yes, using the RECURSIVE keyword
    • b) No, recursion is not supported
    • c) Only for numeric data types
    • d) Only in stored procedures
20. What does a recursive CTE require to prevent infinite loops?
    • a) A termination condition
    • b) An index on the columns
    • c) A maximum recursion depth
    • d) A primary key
21. Which part of a CTE defines the anchor member in a recursive query?
    • a) The initial SELECT statement
    • b) The RECURSIVE keyword
    • c) The UNION ALL clause
    • d) The WHERE clause

Recursive Queries

22. What is the primary purpose of recursive queries in PostgreSQL?
    • a) Querying hierarchical data
    • b) Improving query performance
    • c) Simplifying aggregation
    • d) Avoiding NULL values
23. Which keyword is required for recursive queries in PostgreSQL?
    • a) RECURSIVE
    • b) LOOP
    • c) REPEAT
    • d) ITERATE
24. How does a recursive query process data?
    • a) Starts with a base case and iteratively applies the recursive case
    • b) Processes all rows simultaneously
    • c) Uses parallel threads to iterate rows
    • d) Combines all rows in a single step
25. What type of data is commonly handled by recursive queries?
    • a) Hierarchical data such as organizational structures
    • b) Time-series data
    • c) Randomized data
    • d) Static lookup tables
26. What operator is typically used in recursive queries?
    • a) UNION ALL
    • b) JOIN
    • c) NATURAL JOIN
    • d) FULL OUTER JOIN
27. In a recursive query, where is the recursion terminated?
    • a) In the WHERE clause of the recursive part
    • b) At the base case of the recursion
    • c) After 100 iterations automatically
    • d) When a NULL value is encountered
28. Can recursive queries reference multiple tables?
    • a) Yes, they can include multiple joins
    • b) No, they are limited to one table
    • c) Only in nested recursion
    • d) Only with aggregate functions
29. What happens if a recursive query lacks a termination condition?
    • a) It results in an infinite loop
    • b) It throws a syntax error
    • c) It executes partially
    • d) The query is ignored
30. How do you limit the number of recursion levels in PostgreSQL?
    • a) Use the MAXRECURSION option
    • b) Set a limit in the WHERE clause
    • c) Use a termination condition in the recursive query
    • d) Recursion depth cannot be limited

Answer Key