Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.Challenge: Get Product Amount By Category | Joining Tables
Intermediate SQL
course content

Course Content

Intermediate SQL

Intermediate SQL

1. Grouping
GROUP BY ClauseOrder of StatementsChallenge: Total Ride Time for Each Subway LineAverage Student's Grade ChallengeSearching for the Top Math Students ChallengeHAVING ClauseSeveral Aggregate Functions in the QueryMore Complex Filtering
2. Nested Subqueries
Nested QueriesChallenge: Non-tech Department EmployeesUsing Inner Subqueries in the FROM SectionChallenge: Employees With More Than Average SalaryUNION ClauseChallenge: Combining Table Results
3. Joining Tables
JOIN 2 TablesChallenge: Get Category By Product PriceChallenge: Get Minimum Price By CategoryChallenge: Get Product Amount By CategoryLEFT, RIGHT and INNER JOINsChallenge: Table JoinChallenge: FULL JOIN Usage
4. DDL and DML in SQL
CREATE and ConstraintsALTER and INSERT OperationsDROP and TRUNCATEUPDATE and DELETE

book
Challenge: Get Product Amount By Category

Now the company has a new task for us.

A new supplier contacted us and said that they can supply goods from the categories Meat or Grains cheaply. We need to find out which products in these categories have less than 100 units left in stock.

Task
test

Swipe to show code editor

Your task is to find the product names and their quantities in stock. Join two tables: product and category.

Focus on products in the Meat or Grains categories. Use the OR operator for these categories and ensure the product quantity is less than 100 using the AND operator.

Sort the results by the name column with the ORDER BY clause.

Note

Wrap the OR condition in parentheses before applying the AND operator. This follows the standard order of operations in discrete mathematics.

Your result should have 2 columns: product_name and amount. Remember to set appropriate aliases!

Brief Instructions

  • Retrieve the product.name and product.amount columns from the product table.
  • Assign the alias product_name to the first column and amount to the second.
  • Join the category table on the common column product.category_id = category.id.
  • Use a WHERE clause requiring that category.name equals 'Meat' or 'Grains'.
  • Use the logical operator AND to also require that product.amount < 100.
  • Sort the results by product.name.

Solution

Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
Everything was clear?

How can we improve it?

Thanks for your feedback!

Section 3. Chapter 4
toggle bottom row

book
Challenge: Get Product Amount By Category

Now the company has a new task for us.

A new supplier contacted us and said that they can supply goods from the categories Meat or Grains cheaply. We need to find out which products in these categories have less than 100 units left in stock.

Task
test

Swipe to show code editor

Your task is to find the product names and their quantities in stock. Join two tables: product and category.

Focus on products in the Meat or Grains categories. Use the OR operator for these categories and ensure the product quantity is less than 100 using the AND operator.

Sort the results by the name column with the ORDER BY clause.

Note

Wrap the OR condition in parentheses before applying the AND operator. This follows the standard order of operations in discrete mathematics.

Your result should have 2 columns: product_name and amount. Remember to set appropriate aliases!

Brief Instructions

  • Retrieve the product.name and product.amount columns from the product table.
  • Assign the alias product_name to the first column and amount to the second.
  • Join the category table on the common column product.category_id = category.id.
  • Use a WHERE clause requiring that category.name equals 'Meat' or 'Grains'.
  • Use the logical operator AND to also require that product.amount < 100.
  • Sort the results by product.name.

Solution

Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
Everything was clear?

How can we improve it?

Thanks for your feedback!

Section 3. Chapter 4
Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
We're sorry to hear that something went wrong. What happened?
some-alt