Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

JOINs in MS SQL Server

Healthy Shop database
3. Get to know the Product table
MULTIPLE JOINs – Exercises

Instruction

Here comes the most important table: Product! It contains all products that are sold throughout the store, along with information on how to locate them.

Quick reminder: pay attention to the columns that have the same names as those from another table. For instance:

SELECT
  Car.Name AS CarName,
  Owner.Name AS OwnerName
FROM Car
JOIN Owner
  ON Car.OwnerId = Owner.Id

The column Name is available in both the Car and Owner tables. In the query, we prefix each column's name with the name of the table it comes from (Car.Name, Owner.Name). This way, the database knows which column we want to select.

Then, we use aliases to make the resulting query more readable.

Exercise

The Product table has six columns:

  • Id – the ID of a given product,
  • Name – the name of a given product,
  • DepartmentId – the ID of department where a given product is located,
  • ShelfId – the ID of the shelf of the given department where a given product is located,
  • ProducerId – the ID of a company that manufactures a given product, and
  • Price – the price of a given product.

Okay, let's dive right in! Show the name of the product together with the name of the department where it is located.

Rename the columns to ProductName and DepartmentName, respectively.

Stuck? Here's a hint!

Type: 

SELECT
  Product.Name AS ProductName,
  Department.Name AS DepartmentName
FROM Product
JOIN Department
  ON Product.DepartmentId = Department.Id