SQL - Structured Query Language Joining Multiple Tables Questions and Answers

Question 1

A data analyst needs to generate a report listing all customers and their corresponding order dates. The report must include every customer, even those who have never placed an order. The two tables are `Customers` (with `CustomerID`, `CustomerName`) and `Orders` (with `OrderID`, `CustomerID`, `OrderDate`). Which SQL query correctly accomplishes this?

Accepted Answer

SELECT c.CustomerName, o.OrderDate FROM Customers c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID;

Answer

A `LEFT JOIN` (or `LEFT OUTER JOIN`) returns all records from the left table (`Customers`), and the matched records from the right table (`Orders`). If there is no match, the result is NULL on the side of the right table. This is the correct choice because the requirement is to include all customers, regardless of whether they have placed an order.

Question 2

Which of the following statements best describes the result of a `FULL OUTER JOIN` between a `Doctors` table and a `Patients` table on `Doctors.PatientID = Patients.PatientID`?

Accepted Answer

The result includes all doctors and all patients. If a doctor has no matching patient or a patient has no matching doctor, the corresponding columns will contain NULLs.

Answer

A `FULL OUTER JOIN` returns all records when there is a match in either the left or the right table. It effectively combines the results of both `LEFT JOIN` and `RIGHT JOIN`. Rows from either table that do not have a match in the other table will have NULL values for the columns from the unmatched table.

Question 3

You are writing a query to retrieve the names of products and the names of the categories they belong to. You have a `Products` table (with `ProductID`, `ProductName`, `CategoryID`) and a `Categories` table (with `CategoryID`, `CategoryName`). Which query will return only the products that have a matching category?

Accepted Answer

SELECT p.ProductName, c.CategoryName FROM Products p JOIN Categories c ON p.CategoryID = c.CategoryID;

Answer

An `INNER JOIN` (or simply `JOIN`) is the correct choice because it selects records that have matching values in both tables. It will only return products that have a valid, matching `CategoryID` in the `Categories` table, filtering out any products with a `CategoryID` that does not exist in the `Categories` table.

Question 4

A query needs to produce a list of every possible combination of `ShirtSize` from a `Sizes` table and `ShirtColor` from a `Colors` table to generate all potential inventory items. If the `Sizes` table has 5 rows and the `Colors` table has 10 rows, how many rows will the result set of a `CROSS JOIN` between these two tables contain?

Accepted Answer

50

Answer

A `CROSS JOIN` produces a Cartesian product of the two tables, meaning it combines each row from the first table with every row from the second table. The total number of rows is the number of rows in the first table multiplied by the number of rows in the second table (5 * 10 = 50). No `ON` clause is used with a `CROSS JOIN`.

Question 5

You need to write a query that lists all university departments and the professors in them. The report must include all departments, even those that currently have no professors assigned. You have two tables: `Departments` (`DepartmentID`, `DepartmentName`) and `Professors` (`ProfessorID`, `ProfessorName`, `DepartmentID`). Which query should you use?

Accepted Answer

SELECT D.DepartmentName, P.ProfessorName FROM Professors P RIGHT JOIN Departments D ON P.DepartmentID = D.DepartmentID;

Answer

A `RIGHT JOIN` returns all rows from the right table (`Departments`) and the matched rows from the left table (`Professors`). If a department has no professors, it will still appear in the list with a NULL value for `ProfessorName`. A `LEFT JOIN` with the tables swapped (`FROM Departments D LEFT JOIN Professors P...`) would also achieve the same result.

SQL Practice Test

SQL Practice Test

SQL - Structured Query Language Joining Multiple Tables Questions and Answers