MCQs on Joins and Relationships | SQL Server

Delve into SQL Server’s core concepts: INNER, LEFT, RIGHT, FULL OUTER, CROSS, and SELF JOINS. Learn to enforce referential integrity while managing data relationships effectively with real-world examples.

---

MCQs on SQL Server Joins and Relationships

Section 1: INNER JOIN, LEFT JOIN, RIGHT JOIN

1. What does the following query output?
   SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
   • A) Orders with no matching Customers
   • B) All Customers, with or without orders
   • C) Only Orders with matching Customers
   • D) All Orders
2. In a LEFT JOIN, which rows are included from the left table if no match exists in the right table?
   • A) All rows with NULLs for missing matches
   • B) Only matching rows
   • C) No rows
   • D) Only non-matching rows
3. Given the following query, what is the result?
   SELECT E.EmployeeName, D.DepartmentName FROM Employees E RIGHT JOIN Departments D ON E.DepartmentID = D.DepartmentID;
   • A) All employees, with or without departments
   • B) All departments, with NULL for missing employees
   • C) Only matching employees and departments
   • D) No results
4. Which JOIN returns rows when there is at least one match in both tables?
   • A) INNER JOIN
   • B) LEFT JOIN
   • C) RIGHT JOIN
   • D) FULL OUTER JOIN
5. If table A has 10 rows and table B has 8 rows, how many rows will a LEFT JOIN produce at maximum?
   • A) 10
   • B) 8
   • C) 18
   • D) 80

---

Section 2: FULL OUTER JOIN

6. A FULL OUTER JOIN returns:
   • A) Only matching rows from both tables
   • B) Rows from the first table only
   • C) Rows from the second table only
   • D) All rows from both tables, with NULLs for non-matching rows
7. What does the following query output?
   SELECT A.Col1, B.Col2 FROM TableA A FULL OUTER JOIN TableB B ON A.ID = B.ID;
   • A) Rows with NULLs from unmatched IDs in either table
   • B) Only rows with matching IDs
   • C) Rows only from TableA
   • D) Rows only from TableB
8. Which clause can you use with FULL OUTER JOIN to exclude NULL values?
   • A) WHERE
   • B) GROUP BY
   • C) HAVING
   • D) ORDER BY
9. How is a FULL OUTER JOIN different from an INNER JOIN?
   • A) Includes unmatched rows from both tables
   • B) Includes only matched rows
   • C) Excludes all NULL values
   • D) Does not return duplicates
10. If Table1 has 6 rows and Table2 has 4 rows, with no matching values, how many rows will a FULL OUTER JOIN produce?
    • A) 6
    • B) 4
    • C) 10
    • D) 24

---

Section 3: Cross Joins and Self Joins

11. What is the result of the following CROSS JOIN?
    SELECT * FROM TableA CROSS JOIN TableB;
    • A) Cartesian product of both tables
    • B) Rows with matching keys only
    • C) Rows from TableA only
    • D) Rows from TableB only
12. How many rows will the following query produce if Table1 has 4 rows and Table2 has 5 rows?
    SELECT * FROM Table1 CROSS JOIN Table2;
    • A) 9
    • B) 20
    • C) 4
    • D) 5
13. A self-join is primarily used to:
    • A) Combine rows within the same table
    • B) Create a Cartesian product
    • C) Enforce referential integrity
    • D) Join tables with no common columns
14. What is the purpose of aliasing in a self-join?
    • A) To avoid duplicate rows
    • B) To distinguish between table instances
    • C) To improve query performance
    • D) To prevent NULL values
15. Consider the following query
    SELECT E1.EmployeeName,
E2.ManagerName FROM Employees E1,
Employees E2 WHERE E1.ManagerID = E2.EmployeeID;
    What is this query an example of?
    • A) INNER JOIN
    • B) FULL OUTER JOIN
    • C) Self-join
    • D) CROSS JOIN

---

Section 4: Understanding Referential Integrity

16. Which SQL concept ensures that foreign key values match primary key values in the related table?
    • A) Referential integrity
    • B) Normalization
    • C) Data indexing
    • D) Partitioning
17. What happens if a referenced primary key is deleted without ON DELETE CASCADE?
    • A) The delete operation fails
    • B) The related foreign key is also deleted
    • C) NULL values are assigned to foreign keys
    • D) Referential integrity remains unaffected
18. Which SQL keyword enforces referential integrity?
    • A) UNIQUE
    • B) FOREIGN KEY
    • C) CHECK
    • D) PRIMARY KEY
19. In the context of referential integrity, what does “ON UPDATE CASCADE” do?
    • A) Updates related foreign key values
    • B) Prevents updates to primary keys
    • C) Deletes foreign key rows
    • D) Allows duplicate keys
20. Consider two tables: Orders (OrderID as primary key) and OrderDetails (OrderID as foreign key). What ensures all OrderDetails rows are deleted when an Order is deleted?
    • A) ON DELETE CASCADE
    • B) ON UPDATE CASCADE
    • C) FOREIGN KEY
    • D) PRIMARY KEY

---

Answer Key

Qno	Answer
1	C) Only Orders with matching Customers
2	A) All rows with NULLs for missing matches
3	B) All departments, with NULL for missing employees
4	A) INNER JOIN
5	A) 10
6	D) All rows from both tables, with NULLs for non-matching rows
7	A) Rows with NULLs from unmatched IDs in either table
8	A) WHERE
9	A) Includes unmatched rows from both tables
10	C) 10
11	A) Cartesian product of both tables
12	B) 20
13	A) Combine rows within the same table
14	B) To distinguish between table instances
15	C) Self-join
16	A) Referential integrity
17	A) The delete operation fails
18	B) FOREIGN KEY
19	A) Updates related foreign key values
20	A) ON DELETE CASCADE