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 car_name,
  owner.name AS owner_name
FROM car
JOIN owner
  ON car.owner_id = 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,
  • department_id – the ID of department where a given product is located,
  • shelf_id – the ID of the shelf of the given department where a given product is located,
  • producer_id – 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 product_name and department_name, respectively.

Stuck? Here's a hint!

Type:

SELECT
  product.name AS product_name,
  department.name AS department_name
FROM product
JOIN department
ON product.department_id = department.id

Console

Code editor

Result

TableConsole