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

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

Answer

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

Answer

SELECT c.CustomerName, o.OrderDate FROM Customers c, Orders o;

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

The result includes only doctors who are matched with a patient and only patients who are matched with a doctor.

Answer

The result includes all doctors, but only patients who are matched with a doctor.

Answer

The result includes all patients, but only doctors who are matched with a patient.

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

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

Answer

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

Answer

SELECT p.ProductName, c.CategoryName FROM Products p CROSS JOIN Categories c;

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

10

Answer

15

Answer

It depends on the matching keys.

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

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

Answer

SELECT D.DepartmentName, P.ProfessorName FROM Departments D, Professors P WHERE D.DepartmentID = P.DepartmentID;

Answer

SELECT D.DepartmentName, P.ProfessorName FROM Departments D CROSS JOIN Professors P;

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?

Accepted 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;

Answer

SELECT c.CustomerName, o.OrderDate, p.ProductName FROM Customers c JOIN Orders o JOIN OrderDetails od JOIN Products p;

Answer

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;

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;