SQL Query Basics for Ingredient Management
24 Questions
4 Views

SQL Query Basics for Ingredient Management

Created by
@GaloreArlington

Podcast Beta

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What is the SQL query to find the names of the ingredients supplied by a particular supplier?

SELECT name FROM ingredients WHERE supplierid = 'S001';

What is the SQL query to find the names of all ingredients supplied by two suppliers?

SELECT name FROM ingredients WHERE supplierid = 'S001' OR supplierid = 'S002';

What is the SQL query to find the company names of all suppliers who provided an ingredient with an inventory of fewer than 50?

SELECT company_name FROM suppliers WHERE supplierid IN (SELECT supplierid FROM ingredients WHERE inventory < 50);

What is the SQL query to find the average unit price for all items provided by a supplier?

<p>SELECT AVG(unitprice) AS avg_unit_price FROM ingredients WHERE supplierid = '';</p> Signup and view all the answers

What is the SQL query to find the names of all suppliers referred to by another supplier?

<p>SELECT company_name FROM suppliers WHERE referred_by = '';</p> Signup and view all the answers

What is the SQL query to find all ingredients with an inventory within 25% of the average inventory of ingredients?

<p>SELECT name FROM ingredients WHERE inventory BETWEEN ((SELECT AVG(inventory) FROM ingredients) * 0.75) AND ((SELECT AVG(inventory) FROM ingredients) * 1.25);</p> Signup and view all the answers

What is the SQL query to find the companies referred by a supplier and providing an ingredient in the milk food group?

<p>SELECT company_name FROM suppliers WHERE referred_by = '' AND supplierid IN (SELECT supplierid FROM ingredients WHERE foodgroup = 'milk');</p> Signup and view all the answers

What is the SQL query to find the name and price for all items using an ingredient supplied by a supplier?

<p>SELECT i.name, i.price FROM items i JOIN madewith mw ON i.itemid = mw.itemid JOIN ingredients ing ON mw.ingredientid = ing.ingredientid WHERE ing.supplierid = '';</p> Signup and view all the answers

What is the SQL query to find the names and inventory values for all ingredients with an inventory value greater than the total inventory value of all ingredients provided by a supplier?

<p>SELECT name, (inventory * unitprice) AS inventory_value FROM ingredients WHERE (inventory * unitprice) &gt; (SELECT SUM(inventory * unitprice) FROM ingredients WHERE supplierid = '');</p> Signup and view all the answers

What is the SQL query to find all ingredients supplied by someone other than a particular supplier?

<p>SELECT name FROM ingredients WHERE supplierid != '';</p> Signup and view all the answers

What is the SQL query to find the company name of small suppliers who do not provide any ingredients with large (>100) inventories?

<p>SELECT company_name FROM suppliers WHERE supplierid NOT IN (SELECT supplierid FROM ingredients WHERE inventory &gt; 100);</p> Signup and view all the answers

What is the SQL query to find the name and price of all items using an ingredient supplied by a particular supplier?

<p>SELECT i.name, i.price FROM items i JOIN madewith mw ON i.itemid = mw.itemid JOIN ingredients ing ON mw.ingredientid = ing.ingredientid WHERE ing.supplierid = '';</p> Signup and view all the answers

What is the SQL query to find all items that have a price greater than any salad item?

<p>SELECT name FROM items WHERE price &gt; (SELECT MAX(price) FROM items WHERE name LIKE '%salad%');</p> Signup and view all the answers

What is the SQL query to find the name of all ingredients supplied by two different suppliers?

<p>SELECT name FROM ingredients WHERE supplierid NOT IN ('', '');</p> Signup and view all the answers

What is the SQL query to find all ingredients not supplied by two different suppliers?

<p>SELECT name FROM ingredients WHERE supplierid NOT IN ('', '');</p> Signup and view all the answers

What is the SQL query to find the most expensive items?

<p>SELECT name FROM items WHERE price = (SELECT MAX(price) FROM items);</p> Signup and view all the answers

What is the SQL query to find items that contain 3 or more ingredients?

<p>SELECT i.name FROM items i JOIN madewith mw ON i.itemid = mw.itemid GROUP BY i.itemid HAVING COUNT(mw.ingredientid) &gt;= 3;</p> Signup and view all the answers

What is the SQL query to find the number of ingredients in a particular item?

<p>SELECT COUNT(mw.ingredientid) AS ingredient_count FROM items i JOIN madewith mw ON i.itemid = mw.itemid WHERE i.name = '';</p> Signup and view all the answers

What is the SQL query to find all suppliers who referred two or more suppliers?

<p>SELECT company_name FROM suppliers WHERE supplierid IN (SELECT referred_by FROM suppliers GROUP BY referred_by HAVING COUNT(supplierid) &gt;= 2);</p> Signup and view all the answers

What is the SQL query to find the meals containing an ingredient from a specific food group?

<p>SELECT m.name FROM meals m JOIN partof p ON m.mealid = p.mealid JOIN items i ON p.itemid = i.itemid JOIN madewith mw ON i.itemid = mw.itemid JOIN ingredients ing ON mw.ingredientid = ing.ingredientid WHERE ing.foodgroup = '';</p> Signup and view all the answers

What is the SQL query to find all suppliers who did not recommend any other vendor?

<p>SELECT company_name FROM suppliers WHERE supplierid NOT IN (SELECT referred_by FROM suppliers WHERE referred_by IS NOT NULL);</p> Signup and view all the answers

What is the SQL query to list the name and inventory value of each ingredient in two food groups?

<p>SELECT name, (inventory * unitprice) AS inventory_value FROM ingredients WHERE foodgroup IN ('', '');</p> Signup and view all the answers

What is the SQL query to find all suppliers who provided more ingredients than a specific supplier?

<p>SELECT company_name FROM suppliers WHERE supplierid IN (SELECT supplierid FROM ingredients GROUP BY supplierid HAVING COUNT(ingredientid) &gt; (SELECT COUNT(ingredientid) FROM ingredients WHERE supplierid = ''));</p> Signup and view all the answers

What is the SQL query to find the average inventory values for each supplier who recommends at least one different supplier?

<p>SELECT s.company_name, AVG(ing.inventory) AS avg_inventory FROM suppliers s JOIN ingredients ing ON s.supplierid = ing.supplierid WHERE s.supplierid IN (SELECT referred_by FROM suppliers);</p> Signup and view all the answers

Study Notes

SQL Queries for Ingredient and Supplier Management

  • Query ingredients supplied by a specific supplier using supplierid.
  • Retrieve names of ingredients supplied by two distinct suppliers with an OR condition.
  • Find supplier company names providing ingredients with fewer than 50 in inventory using a subquery.
  • Calculate the average unit price of items for a selected supplier with AVG(unitprice).
  • Identify suppliers referred by another supplier.
  • Locate ingredients with inventories within 25% of the average inventory using a BETWEEN clause.
  • List companies referred by a supplier that provide ingredients in the 'milk' food group.
  • Extract names and prices of items using ingredients supplied by a specific supplier.
  • Find names and inventory values of ingredients exceeding the total inventory value provided by a particular supplier.
  • Select ingredients supplied by any supplier except a specified supplier using the != operator.
  • Identify small suppliers that do not provide ingredients with inventories greater than 100.
  • Retrieve names and prices of items using ingredients from a certain supplier.
  • Find items priced higher than any salads by using a subquery for MAX(price) on salad items.
  • Select names of ingredients supplied by two different suppliers using NOT IN.
  • Retrieve names of ingredients not supplied by two different suppliers.
  • Find most expensive items in the inventory by using a MAX(price) condition.
  • List items with 3 or more ingredients using HAVING COUNT(mw.ingredientid) >= 3.
  • Count ingredients in a specified item through a COUNT aggregation.
  • Identify suppliers who referred two or more other suppliers along with their company names.
  • Find meals containing ingredients from a specific food group, involving multiple joins across tables.
  • Identify suppliers that have not recommended any other vendors using NOT IN.
  • List ingredient names and inventory values for two specified food groups through IN.
  • Retrieve suppliers that have provided more ingredients than a designated supplier using a subquery.
  • Calculate average inventory values for suppliers recommending at least one other supplier.

Query Techniques

  • Utilize JOIN statements to relate multiple tables.
  • Use nested SELECT statements for complex conditions.
  • Leverage aggregation functions like AVG, COUNT, and MAX for calculations.
  • Apply WHERE, HAVING, and BETWEEN for filtering data sets.

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Quiz Team

Description

Test your understanding of SQL queries related to ingredient management and supplier information. This quiz covers fundamental SELECT statements and filtering techniques using WHERE and subqueries. Perfect for beginners looking to master SQL basics.

More Like This

Use Quizgecko on...
Browser
Open
Browser
Browser