1. What is the primary purpose of a SQL JOIN operation?
a) To create a new table in the database
b) To combine rows from two or more tables based on a related column
c) To delete rows from multiple tables simultaneously
d) To update columns in multiple tables
✅ Answer: b
2. Which clause specifies the condition for matching rows in a SQL JOIN?
a) WHERE
b) FROM
c) ON
d) SELECT
✅ Answer: c
3. What does the AS keyword do in a SQL JOIN query?
a) Filters the result set
b) Assigns a temporary name to a table or column
c) Specifies the join condition
d) Orders the result set
✅ Answer: b
4. Which of the following is true about INNER JOIN?
a) Returns all rows from both tables, with NULLs for non-matching rows
b) Returns only rows where there is a match in both tables
c) Returns all rows from the left table and matching rows from the right
d) Combines every row from both tables without a condition
✅ Answer: b
5. In the syntax SELECT c.first_name, o.order_id FROM Customers c JOIN Orders o ON c.customer_id = o.customer_id;, what does c represent?
a) A column name
b) A table alias
c) A primary key
d) A foreign key
✅ Answer: b
6. What will the following query return? SELECT c.first_name, o.order_id FROM Customers c LEFT JOIN Orders o ON c.customer_id = o.customer_id;
a) Only customers with orders
b) All customers, with NULL for order_id if they have no orders
c) All orders, with NULL for first_name if no customer matches
d) All possible combinations of customers and orders
✅ Answer: b
7. Which JOIN type is used to retrieve all rows from the right table, with NULLs for non-matching rows from the left table?
a) INNER JOIN
b) LEFT JOIN
c) RIGHT JOIN
d) FULL OUTER JOIN
✅ Answer: c
8. What is the result of a CROSS JOIN between a table with 5 rows and another with 3 rows?
a) 8 rows
b) 15 rows
c) 5 rows
d) 3 rows
✅ Answer: b
9. Which of the following queries uses a SELF JOIN?
a) SELECT c.first_name, o.order_id FROM Customers c JOIN Orders o ON c.customer_id = o.customer_id;
b) SELECT e1.first_name, e2.first_name FROM Employees e1 JOIN Employees e2 ON e1.manager_id = e2.employee_id;
c) SELECT c.first_name, o.order_id FROM Customers c LEFT JOIN Orders o ON c.customer_id = o.customer_id;
d) SELECT p.product_name, s.store_name FROM Products p CROSS JOIN Stores s;
✅ Answer: b
10. Which JOIN type is not natively supported in MySQL?
a) INNER JOIN
b) LEFT JOIN
c) RIGHT JOIN
d) FULL OUTER JOIN
✅ Answer: d
11. In the query SELECT c.first_name, o.order_date FROM Customers c JOIN Orders o ON c.customer_id = o.customer_id WHERE o.amount > 500;, what does the WHERE clause do?
a) Specifies the join condition
b) Filters rows after the join operation
c) Assigns aliases to tables
d) Combines multiple tables
✅ Answer: b
12. How many JOIN statements are needed to join 4 tables?
a) 2
b) 3
c) 4
d) 5
✅ Answer: b
13. Which of the following is a use case for a FULL OUTER JOIN?
a) Retrieving only matching records from two tables
b) Comparing two datasets completely, including non-matching rows
c) Listing all records from the left table, with NULLs for non-matching right table rows
d) Generating all possible combinations of rows
✅ Answer: b
14. What is the output of the query SELECT c.first_name, o.order_id FROM Customers c INNER JOIN Orders o ON c.customer_id = o.customer_id; if a customer has no orders?
a) The customer is included with a NULL order_id
b) The customer is excluded from the result
c) All customers are included, with NULL for non-matching orders
d) All orders are included, with NULL for non-matching customers
✅ Answer: b
15. Which of the following is equivalent to SELECT c.first_name, o.order_id FROM Customers c RIGHT JOIN Orders o ON c.customer_id = o.customer_id;?
a) SELECT c.first_name, o.order_id FROM Orders o LEFT JOIN Customers c ON c.customer_id = o.customer_id;
b) SELECT c.first_name, o.order_id FROM Customers c LEFT JOIN Orders o ON c.customer_id = o.customer_id;
c) SELECT c.first_name, o.order_id FROM Customers c INNER JOIN Orders o ON c.customer_id = o.customer_id;
d) SELECT c.first_name, o.order_id FROM Orders o FULL OUTER JOIN Customers c ON c.customer_id = o.customer_id;
✅ Answer: a
16. What is a key benefit of using table aliases in SQL JOINs?
a) Increases query performance
b) Simplifies queries with long table names or self-joins
c) Automatically indexes join columns
d) Allows joining tables without a condition
✅ Answer: b
17. Which JOIN type is most suitable for listing all products and their associated stores, even if some products are not in any store?
a) INNER JOIN
b) LEFT JOIN
c) RIGHT JOIN
d) CROSS JOIN
✅ Answer: b
18. What does the following query do? SELECT p.product_name, s.store_name FROM Products p CROSS JOIN Stores s;
a) Returns only matching products and stores
b) Returns all products with NULL for non-matching stores
c) Returns every possible combination of products and stores
d) Returns all stores with NULL for non-matching products
✅ Answer: c
19. In a SELF JOIN, why are aliases critical?
a) To improve query performance
b) To distinguish between the two instances of the same table
c) To specify the join condition
d) To filter the result set
✅ Answer: b
20. Which of the following is a performance consideration for SQL JOINs?
a) Always use FULL OUTER JOIN for better performance
b) Use indexes on join columns like primary and foreign keys
c) Include all columns in the SELECT clause for efficiency
d) Avoid using aliases to reduce query complexity
✅ Answer: b
21. Given the query SELECT c.first_name, o.order_date, od.product_id FROM Customers c JOIN Orders o ON c.customer_id = o.customer_id JOIN OrderDetails od ON o.order_id = od.order_id;, what type of JOIN is performed?
a) INNER JOIN
b) LEFT JOIN
c) RIGHT JOIN
d) FULL OUTER JOIN
✅ Answer: a
22. What happens if you omit the ON clause in a CROSS JOIN?
a) The query fails with a syntax error
b) The query produces a Cartesian product
c) The query returns only matching rows
d) The query returns NULL for all columns
✅ Answer: b
23. Which of the following queries will return all employees and their managers, including employees without managers?
a) SELECT e1.first_name, e2.first_name FROM Employees e1 INNER JOIN Employees e2 ON e1.manager_id = e2.employee_id;
b) SELECT e1.first_name, e2.first_name FROM Employees e1 LEFT JOIN Employees e2 ON e1.manager_id = e2.employee_id;
c) SELECT e1.first_name, e2.first_name FROM Employees e1 RIGHT JOIN Employees e2 ON e1.manager_id = e2.employee_id;
d) SELECT e1.first_name, e2.first_name FROM Employees e1 CROSS JOIN Employees e2;
✅ Answer: b
24. Why might a database engine optimize a JOIN query better than a subquery?
a) Subqueries always use indexes
b) JOINs clearly define relationships between tables
c) Subqueries are more readable
d) JOINs are slower for large datasets
✅ Answer: b
25. Which of the following is a disadvantage of using subqueries compared to JOINs?
a) Subqueries are always faster
b) Subqueries can be less efficient, especially correlated ones
c) Subqueries cannot filter data
d) Subqueries require more joins
✅ Answer: b
26. What is the output of SELECT c.first_name, o.order_id FROM Customers c FULL OUTER JOIN Orders o ON c.customer_id = o.customer_id; if some customers have no orders and some orders have no customers?
a) Only matching rows from both tables
b) All customers, with NULL for non-matching orders
c) All orders, with NULL for non-matching customers
d) All rows from both tables, with NULLs for non-matching rows
✅ Answer: d
27. Which query will find customers who placed orders over $100 using a subquery?
a) SELECT first_name FROM Customers WHERE customer_id IN (SELECT customer_id FROM Orders WHERE amount > 100);
b) SELECT first_name FROM Customers JOIN Orders ON customer_id = customer_id WHERE amount > 100;
c) SELECT first_name FROM Customers LEFT JOIN Orders ON customer_id = customer_id WHERE amount > 100;
d) SELECT first_name FROM Customers CROSS JOIN Orders WHERE amount > 100;
✅ Answer: a
28. What is a potential risk of using a NATURAL JOIN?
a) It is not supported in most databases
b) It implicitly joins tables based on same-named columns, which can lead to errors
c) It is slower than other JOIN types
d) It requires a WHERE clause
✅ Answer: b
29. Which of the following is true about the query SELECT c.first_name, o.order_id FROM Customers c, Orders o WHERE c.customer_id = o.customer_id;?
a) It is a modern syntax for INNER JOIN
b) It is a legacy syntax for INNER JOIN
c) It performs a LEFT JOIN
d) It performs a CROSS JOIN
✅ Answer: b
30. Which JOIN type is best for pairing every customer with every product, regardless of any condition?
a) INNER JOIN
b) LEFT JOIN
c) RIGHT JOIN
d) CROSS JOIN
✅ Answer: d
31. In the query SELECT c.first_name, o.order_date, od.quantity FROM Customers c JOIN Orders o ON c.customer_id = o.customer_id JOIN OrderDetails od ON o.order_id = od.order_id;, how many tables are joined?
a) 2
b) 3
c) 4
d) 5
✅ Answer: b
32. Which of the following is a best practice for writing SQL JOIN queries?
a) Use implicit join conditions in the WHERE clause
b) Always use explicit ON clauses for clarity
c) Avoid using table aliases
d) Include all columns in the SELECT clause
✅ Answer: b
33. What will happen if you run SELECT c.first_name, o.order_id FROM Customers c LEFT JOIN Orders o ON c.customer_id = o.customer_id WHERE o.order_id IS NULL;?
a) Returns customers with orders
b) Returns customers without orders
c) Returns all customers and their orders
d) Returns all orders with NULL customers
✅ Answer: b
34. Which of the following queries is most efficient for large datasets when finding customers with orders over $100?
a) SELECT first_name FROM Customers WHERE customer_id IN (SELECT customer_id FROM Orders WHERE amount > 100);
b) SELECT DISTINCT c.first_name FROM Customers c JOIN Orders o ON c.customer_id = o.customer_id WHERE o.amount > 100;
c) SELECT first_name FROM Customers WHERE customer_id = (SELECT customer_id FROM Orders WHERE amount > 100);
d) SELECT first_name FROM Customers CROSS JOIN Orders WHERE amount > 100;
✅ Answer: b
35. Why might you avoid deeply nested subqueries?
a) They are always faster than JOINs
b) They reduce query readability and performance
c) They are not supported in most databases
d) They require explicit ON clauses
✅ Answer: b
36. Which JOIN type would you use to list all orders, even if some have no associated customer data?
a) INNER JOIN
b) LEFT JOIN
c) RIGHT JOIN
d) FULL OUTER JOIN
✅ Answer: c
37. What is the purpose of the query SELECT e1.first_name AS employee, e2.first_name AS manager FROM Employees e1 LEFT JOIN Employees e2 ON e1.manager_id = e2.employee_id;?
a) Lists all employees and their coworkers
b) Lists all employees and their managers, including those without managers
c) Lists only employees with managers
d) Lists all managers and their employees
✅ Answer: b
38. Which of the following is a valid way to emulate a FULL OUTER JOIN in MySQL?
a) Use a CROSS JOIN with a WHERE clause
b) Use a UNION of LEFT and RIGHT JOINs
c) Use an INNER JOIN with NULL checks
d) Use a SELF JOIN with aliases
✅ Answer: b
39. What is the key difference between a JOIN and a subquery in terms of structure?
a) JOINs nest queries within each other, while subqueries combine tables directly
b) JOINs combine tables directly, while subqueries nest queries within another query
c) JOINs are always slower than subqueries
d) JOINs require a WHERE clause, while subqueries do not
✅ Answer: b
40. Which of the following queries will return the same result as SELECT c.first_name, o.order_id FROM Customers c INNER JOIN Orders o ON c.customer_id = o.customer_id;?
a) SELECT c.first_name, o.order_id FROM Customers c LEFT JOIN Orders o ON c.customer_id = o.customer_id;
b) SELECT c.first_name, o.order_id FROM Customers c, Orders o WHERE c.customer_id = o.customer_id;
c) SELECT c.first_name, o.order_id FROM Customers c RIGHT JOIN Orders o ON c.customer_id = o.customer_id;
d) SELECT c.first_name, o.order_id FROM Customers c CROSS JOIN Orders o;
✅ Answer: b
41. Which of the following is a use case for a CROSS JOIN?
a) Retrieving only matching rows from two tables
b) Generating all possible combinations of rows, like pairing products with stores
c) Including all rows from the left table, with NULLs for non-matching rows
d) Comparing rows within the same table
✅ Answer: b
42. What is the output of SELECT c.first_name, p.product_name FROM Customers c CROSS JOIN Products p WHERE p.product_name = 'Laptop'; if there is one 'Laptop' in Products?
a) Each customer paired with 'Laptop'
b) Only customers who ordered 'Laptop'
c) All products paired with each customer
d) Only 'Laptop' with NULL for customers
✅ Answer: a
43. Which of the following is a performance tip for SQL JOINs?
a) Always join as many tables as possible
b) Minimize the number of joined tables to reduce complexity
c) Use subqueries instead of JOINs for large datasets
d) Avoid indexing join columns
✅ Answer: b
44. Why might you prefer a JOIN over a subquery for multi-table queries?
a) JOINs are less readable but faster
b) JOINs are generally more efficient and clearer for combining multiple tables
c) JOINs are slower but more flexible
d) JOINs cannot handle complex conditions
✅ Answer: b
45. What will the query SELECT c.first_name, o.order_id FROM Customers c LEFT JOIN Orders o ON c.customer_id = o.customer_id WHERE o.amount > 500; return?
a) All customers with orders over $500
b) All customers, with NULL for orders under $500
c) Only customers with orders, regardless of amount
d) All orders over $500, with NULL for non-matching customers
✅ Answer: a
46. Which of the following is true about correlated subqueries?
a) They are always faster than JOINs
b) They execute for each row of the outer query, which can be slow
c) They are not supported in most databases
d) They cannot reference outer query columns
✅ Answer: b
47. What is the purpose of the query SELECT c.first_name, o.order_date, od.quantity FROM Customers c JOIN Orders o ON c.customer_id = o.customer_id JOIN OrderDetails od ON o.order_id = od.order_id;?
a) Lists all customers and their orders, regardless of order details
b) Lists customers, their orders, and order details for matching records
c) Lists all order details with NULL for non-matching customers
d) Lists all products paired with customers
✅ Answer: b
48. Which of the following is a database-specific variation mentioned in the document?
a) PostgreSQL does not support INNER JOIN
b) MySQL supports FULL OUTER JOIN natively
c) PostgreSQL supports advanced join types like NATURAL JOIN
d) MySQL requires aliases for all JOINs
✅ Answer: c
49. Which of the following queries will return customers who have not placed any orders, based on the sample tables?
a) SELECT c.first_name FROM Customers c INNER JOIN Orders o ON c.customer_id = o.customer_id;
b) SELECT c.first_name FROM Customers c LEFT JOIN Orders o ON c.customer_id = o.customer_id WHERE o.order_id IS NULL;
c) SELECT c.first_name FROM Customers c RIGHT JOIN Orders o ON c.customer_id = o.customer_id;
d) SELECT c.first_name FROM Customers c CROSS JOIN Orders o;
✅ Answer: b
50. Which of the following is a best practice for optimizing SQL JOIN performance?
a) Use NATURAL JOIN for simplicity
b) Avoid unnecessary columns in the SELECT clause to reduce data transfer
c) Always use correlated subqueries for filtering
d) Join tables without indexes
✅ Answer: b