programming beginner 10 sample questions

Joins MCQ Practice Test

SQL operations that combine rows from two or more tables based on related columns.

Q1. Given students(student_id, name), grades(student_id, course_id, grade), and courses(course_id, name), which join conditions correctly link all three tables?

Explanation: The grades junction table holds both student_id and course_id, so it must be joined to each parent on the matching key.

Q2. A database contains three tables: “orders”, “customers”, and “products”. The “orders” table has foreign keys “customer_id” and “product_id”. Which type of SQL join would be used to retrieve all orders for a specific customer, along with the products in those orders?

Explanation: This question requires the use of an INNER JOIN to retrieve all orders for a specific customer, along with the products in those orders. The INNER JOIN will return only the rows where there is a match in both tables.

Q3. A database contains two tables: “orders” and “customers”. The “orders” table has columns for “order_id”, “customer_id”, and “order_date”. The “customers” table has columns for “customer_id”, “name”, and “email”. Which type of join would you use to retrieve all orders for each customer, including the customer’s name and email?

Explanation: A Left Join would return all orders for each customer, even if the customer does not have any orders. This is because all records from the left table (“orders”) are returned, and the matching records from the right table (“customers”) are added if present.

Q4. A database contains two tables, “orders” and “customers”. The “orders” table has a column “customer_id” that references the “id” column in the “customers” table. Which type of join would be used to retrieve all orders for a specific customer, given that we know the customer's “id”?

Explanation: An inner join is used to retrieve only the rows that have matching values in both tables. In this case, it would retrieve only the orders that match the specified customer.

Q5. A database contains two tables, “orders” and “customers”. The “orders” table has columns “order_id”, “customer_id”, and “order_date”. The “customers” table has columns “customer_id”, “customer_name”, and “customer_email”. Which SQL join type would you use to retrieve all orders for a specific customer, given that the “customer_id” is common to both tables?

Explanation: An Inner Join is used to retrieve data from two tables where the join condition is met. In this case, we want to retrieve all orders for a specific customer, so we need to match the “customer_id” column in both tables.

Q6. A database contains two tables, “orders” and “customers”. The “orders” table has columns “order_id”, “customer_id”, and “total”. The “customers” table has columns “customer_id”, “name”, and “email”. Which SQL join would be used to retrieve the total amount spent by each customer, including the customer’s name and email?

Explanation: This question requires the use of an INNER JOIN to link the orders table with the customers table based on the customer_id. The INNER JOIN will return only the rows where there is a match in both tables, ensuring that each customer is only listed once with their total amount spent.

Q7. A database contains two tables, “employees” and “projects”. The “employees” table has columns for “employee_id”, “name”, and “department”. The “projects” table has columns for “project_id”, “project_name”, and “employee_id”. What type of join would be used to retrieve all employees who are assigned to a project with a “project_name” of “Marketing Campaign”?

Explanation: An Inner Join would be used to retrieve all employees who are assigned to a project with a “project_name” of “Marketing Campaign”. This is because an Inner Join only returns rows where there is a match in both tables.

Q8. A database contains two tables: 'orders' and 'customers'. The 'orders' table has a column named 'customer_id' that references the 'id' column in the 'customers' table. Which SQL join type is used to combine the two tables based on the matching 'customer_id' and 'id' columns?

Explanation: An INNER JOIN is used when you want to combine rows from two tables where the join condition is met. In this case, the 'customer_id' in the 'orders' table matches the 'id' in the 'customers' table, so the rows from both tables are combined.

Q9. A database designer wants to retrieve all employees who have a matching department name in a separate table. The department table has a column named 'dept_id' which is a foreign key to the 'department_id' column in the employees table. Which SQL join would be used in this scenario?

Explanation: An INNER JOIN is used to retrieve records that have matching values in both tables. In this case, the designer wants to retrieve all employees who have a matching department name, so an INNER JOIN is the correct choice.

Q10. A database has two tables, “orders” and “customers”. The “orders” table has a column named “customer_id” that references the “id” column in the “customers” table. What type of join is used to combine rows from both tables where the “customer_id” in the “orders” table matches the “id” in the “customers” table?

Explanation: An Inner Join is used to combine rows from two tables where the join condition is met. In this case, the Inner Join will return only the rows from the “orders” table where the “customer_id” matches the “id” in the “customers” table.

That was just a sample. Sign up to unlock the full question bank with timed tests and certificates.

Sign Up Free
Joins MCQ Practice Test | Mind Hustle