SQL - Structured Query Language Joining Multiple Tables Questions and Answers 1 — 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?
- SELECT c.CustomerName, o.OrderDate FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID;
- SELECT c.CustomerName, o.OrderDate FROM Customers c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID; (Correct answer)
- SELECT c.CustomerName, o.OrderDate FROM Customers c RIGHT JOIN Orders o ON c.CustomerID = o.CustomerID;
- SELECT c.CustomerName, o.OrderDate FROM Customers c, Orders o;
Correct answer: SELECT c.CustomerName, o.OrderDate FROM Customers c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID;
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`?
- The result includes only doctors who are matched with a patient and only patients who are matched with a doctor.
- The result includes all doctors, but only patients who are matched with a doctor.
- The result includes all patients, but only doctors who are matched with a patient.
- 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. (Correct answer)
Correct 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.
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?
- SELECT p.ProductName, c.CategoryName FROM Products p JOIN Categories c ON p.CategoryID = c.CategoryID; (Correct answer)
- SELECT p.ProductName, c.CategoryName FROM Products p LEFT JOIN Categories c ON p.CategoryID = c.CategoryID;
- SELECT p.ProductName, c.CategoryName FROM Products p FULL OUTER JOIN Categories c ON p.CategoryID = c.CategoryID;
- SELECT p.ProductName, c.CategoryName FROM Products p CROSS JOIN Categories c;
Correct answer: SELECT p.ProductName, c.CategoryName FROM Products p JOIN Categories c ON p.CategoryID = c.CategoryID;
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?
- 10
- 15
- 50 (Correct answer)
- It depends on the matching keys.
Correct answer: 50
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?
- SELECT D.DepartmentName, P.ProfessorName FROM Departments D INNER JOIN Professors P ON D.DepartmentID = P.DepartmentID;
- SELECT D.DepartmentName, P.ProfessorName FROM Professors P RIGHT JOIN Departments D ON P.DepartmentID = D.DepartmentID; (Correct answer)
- SELECT D.DepartmentName, P.ProfessorName FROM Departments D, Professors P WHERE D.DepartmentID = P.DepartmentID;
- SELECT D.DepartmentName, P.ProfessorName FROM Departments D CROSS JOIN Professors P;
Correct answer: SELECT D.DepartmentName, P.ProfessorName FROM Professors P RIGHT JOIN Departments D ON P.DepartmentID = D.DepartmentID;
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.
Question 6: To fulfill an order report, you need to retrieve the customer's name, the order date, and the product name for every item in every order. This requires joining three tables: `Customers` (CustomerID, CustomerName), `Orders` (OrderID, CustomerID, OrderDate), and `OrderDetails` (OrderDetailID, OrderID, ProductID), and `Products` (ProductID, ProductName). Which query correctly joins these tables?
- SELECT c.CustomerName, o.OrderDate, p.ProductName FROM Customers c JOIN Orders o JOIN OrderDetails od JOIN Products p;
- SELECT c.CustomerName, o.OrderDate, p.ProductName FROM Customers c, Orders o, OrderDetails od, Products p WHERE c.CustomerID = o.CustomerID AND o.OrderID = od.OrderID AND od.ProductID = p.ProductID;
- SELECT c.CustomerName, o.OrderDate, p.ProductName FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID INNER JOIN OrderDetails od ON o.OrderID = od.OrderID INNER JOIN Products p ON od.ProductID = p.ProductID; (Correct answer)
- SELECT c.CustomerName, o.OrderDate, p.ProductName FROM Customers c OUTER JOIN Orders o ON c.CustomerID = o.CustomerID OUTER JOIN OrderDetails od ON o.OrderID = od.OrderID OUTER JOIN Products p ON od.ProductID = p.ProductID;
Correct answer: SELECT c.CustomerName, o.OrderDate, p.ProductName FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID INNER JOIN OrderDetails od ON o.OrderID = od.OrderID INNER JOIN Products p ON od.ProductID = p.ProductID;
To join multiple tables, you chain `JOIN` clauses together. The query correctly starts with `Customers`, joins to `Orders` on `CustomerID`, then joins that result to `OrderDetails` on `OrderID`, and finally joins that to `Products` on `ProductID`. Each `ON` clause correctly specifies the linking columns between the successive tables.
SQL - Structured Query Language Joining Multiple Tables Questions and Answers 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?