MCQs on Joins and Data Retrieval Techniques | Data Relationships

5. Joins and Data Retrieval Techniques

Inner and Outer Joins (LEFT, RIGHT, FULL)

1. Which type of join returns only the matching rows from both tables?
   • A. LEFT JOIN
   • B. RIGHT JOIN
   • C. INNER JOIN
   • D. FULL JOIN
2. What does a LEFT JOIN return in SQL?
   • A. Only the rows that match from the right table
   • B. All rows from the left table and matching rows from the right table
   • C. Only the rows that match from both tables
   • D. All rows from both tables
3. Which SQL join returns all rows from the right table and matching rows from the left table?
   • A. LEFT JOIN
   • B. RIGHT JOIN
   • C. INNER JOIN
   • D. FULL JOIN
4. Which of the following joins returns all rows from both tables, with matching rows where available?
   • A. LEFT JOIN
   • B. RIGHT JOIN
   • C. INNER JOIN
   • D. FULL JOIN
5. What happens when you use an INNER JOIN on two tables with no matching rows?
   • A. Returns all rows from both tables
   • B. Returns no rows
   • C. Returns rows from the left table
   • D. Returns rows from the right table
6. In a FULL JOIN, what happens when there is no matching row in either table?
   • A. NULL values are returned
   • B. An error is thrown
   • C. Empty rows are returned
   • D. The query returns no rows
7. Which of the following is the correct syntax for a LEFT JOIN?
   • A. SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;
   • B. SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id;
   • C. SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;
   • D. SELECT * FROM table1 FULL JOIN table2 ON table1.id = table2.id;

Self Joins and Cross Joins

8. What is a self join in SQL?
   • A. Joining a table with itself
   • B. Joining a table with another table
   • C. Using a subquery
   • D. Joining two columns within the same table
9. Which SQL join can be used to join a table with itself?
   • A. SELF JOIN
   • B. CROSS JOIN
   • C. INNER JOIN
   • D. LEFT JOIN
10. A CROSS JOIN between two tables returns:
    • A. Rows with matching values in both tables
    • B. The Cartesian product of both tables
    • C. Only rows from the left table
    • D. Only rows from the right table
11. What is the result of a CROSS JOIN between two tables with 5 and 3 rows respectively?
    • A. 15 rows
    • B. 5 rows
    • C. 3 rows
    • D. 8 rows
12. In a self join, which clause is used to give different aliases to the same table?
    • A. ON
    • B. AS
    • C. JOIN
    • D. WHERE
13. What does a self join help to achieve in a table?
    • A. Comparing rows within the same table
    • B. Merging data from two different tables
    • C. Aggregating data from multiple tables
    • D. Joining multiple columns within one table

Working with Subqueries and Nested Queries

14. What is a subquery in SQL?
    • A. A query inside another query
    • B. A function inside a query
    • C. A stored procedure
    • D. A temporary table
15. When is a subquery used in SQL?
    • A. To retrieve a list of tables from the database
    • B. To perform operations on tables without using joins
    • C. To execute multiple queries simultaneously
    • D. To retrieve a single value that is used by the main query
16. What does a correlated subquery do?
    • A. Executes the subquery independently of the outer query
    • B. Uses values from the outer query in the subquery
    • C. Returns a static set of values
    • D. Executes after the outer query completes
17. Which of the following SQL queries uses a nested query?
    • A. SELECT * FROM employees WHERE id IN (SELECT id FROM departments);
    • B. SELECT * FROM employees;
    • C. SELECT id, name FROM employees;
    • D. SELECT COUNT(*) FROM employees;
18. In SQL, what is a common reason for using a nested query?
    • A. To compare two tables
    • B. To return a value for filtering results
    • C. To join tables with multiple conditions
    • D. To update rows based on aggregate functions
19. What does a subquery in the FROM clause return?
    • A. A temporary result set that is used for joining
    • B. A static result set
    • C. An aggregate value
    • D. A list of column names
20. Which of the following is a key characteristic of a non-correlated subquery?
    • A. It is dependent on the outer query
    • B. It is independent of the outer query
    • C. It contains a JOIN operation
    • D. It returns multiple values

Advanced Join Techniques (Using WITH Clause, Common Table Expressions)

21. What is the purpose of the WITH clause in SQL?
    • A. To define a temporary result set for use in a query
    • B. To join multiple tables directly
    • C. To perform complex aggregation
    • D. To update records in a table
22. A Common Table Expression (CTE) is defined using which keyword?
    • A. SELECT
    • B. FROM
    • C. WITH
    • D. JOIN
23. Which of the following describes a use case for a Common Table Expression (CTE)?
    • A. To define a temporary table that can be referenced later in the query
    • B. To filter results based on aggregate functions
    • C. To join tables without using an alias
    • D. To update data in the database
24. Which of the following is a valid syntax for a WITH clause?
    • A. WITH CTE AS (SELECT * FROM employees) SELECT * FROM CTE;
    • B. SELECT * FROM employees WITH CTE AS (SELECT * FROM departments);
    • C. WITH CTE SELECT * FROM employees;
    • D. SELECT * FROM employees WITH CTE;
25. What does a recursive CTE allow you to do?
    • A. Perform iterative queries on hierarchical data
    • B. Merge two unrelated tables
    • C. Perform an INNER JOIN between two tables
    • D. Filter data based on multiple conditions

Anti-joins and Semi-joins

26. What is the result of an anti-join in SQL?
    • A. Returns only rows from the left table that do not have matching rows in the right table
    • B. Returns matching rows from both tables
    • C. Returns all rows from the left and right tables
    • D. Returns all rows from the right table
27. In SQL, which join is used to return rows that exist in one table but not the other?
    • A. Anti-join
    • B. Semi-join
    • C. INNER JOIN
    • D. LEFT JOIN
28. A semi-join returns:
    • A. All matching rows from both tables
    • B. Rows from one table that have matching rows in another table
    • C. Rows from the left table only
    • D. Rows from the right table only
29. Which of the following joins can be used to identify rows in one table that do not exist in another table?
    • A. LEFT JOIN
    • B. RIGHT JOIN
    • C. ANTI-JOIN
    • D. INNER JOIN
30. What is the main difference between a semi-join and an inner join?
    • A. A semi-join returns only rows from one table, while an inner join returns rows from both tables
    • B. A semi-join returns no rows
    • C. A semi-join returns rows with matching values from both tables
    • D. An inner join is not used for filtering

Answers