SQL Queries for Ingredient Management
23 Questions
0 Views

Choose a study mode

Play Quiz
Study Flashcards
Spaced Repetition
Chat to lesson

Podcast

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 name 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 name 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 = 'S001';</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 = 'S001';</p> Signup and view all the answers

What is the SQL query to find all the 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 who were referred by a supplier and provide an ingredient in the milk food group?

<p>SELECT company_name FROM suppliers WHERE referred_by = 'S004' AND supplierid IN (SELECT supplierid FROM ingredients WHERE ingredientid IN (SELECT ingredientid FROM ingredients WHERE name = '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 = 'S001';</p> Signup and view all the answers

What is the SQL query to find the names and inventory value 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 = 'S001');</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 != 'S001';</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 = 'S001';</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 IN ('S001', 'S002');</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 ('S001', 'S002');</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 the 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 = 'Tomato Soup';</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 = 'Grains';</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 ('Grains', 'Dairy');</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 = 'S001'));</p> Signup and view all the answers

Study Notes

SQL Queries for Ingredient and Supplier Management

  • Retrieve ingredient names from a specific supplier using the SELECT statement with a WHERE clause filtering by supplierid.
  • Fetch all ingredient names supplied by two different suppliers with an OR logical operator in the condition.
  • Identify supplier company names that provided ingredients with an inventory count below 50, employing a subquery for filtering.
  • Calculate the average unit price for all items from a specified supplier using the AVG function.
  • Obtain names of suppliers that were referred by another specific supplier utilizing the WHERE clause for referred_by.
  • Find ingredients whose inventory levels are within 25% of the average inventory across all ingredients using a BETWEEN condition.
  • List companies referred by a supplier that supply ingredients in the milk food group through nested queries.
  • Extract names and prices of items that contain specific ingredients supplied by a targeted supplier using JOIN operations.
  • Determine ingredient names alongside their inventory values, filtered to those exceeding the total inventory value supplied by a particular supplier.
  • Compile a list of ingredients not associated with a specific supplier using the != operator.
  • Find small suppliers who do not provide any ingredients with an inventory count over 100 through a NOT IN condition.
  • Fetch names and prices of items containing ingredients provided by a specific supplier using JOIN operations.
  • Identify items priced higher than any salad item through a comparative subquery.
  • Gather names of ingredients supplied by two designated suppliers utilizing the IN operator.
  • List ingredients that are not supplied by two specified suppliers using the NOT IN operator.
  • Retrieve names of the most expensive items by comparing prices with a subquery extracting the maximum price.
  • Acquire names of items that consist of three or more ingredients by grouping and filtering with the HAVING clause.
  • Count the number of ingredients in a specific item by using the COUNT function along with a JOIN.
  • Compile a list of suppliers who referred two or more vendors utilizing a conditional grouping and HAVING clause.
  • Collect meals containing ingredients from a specified food group through multiple JOINs across meals, items, and ingredients.
  • Identify suppliers who did not recommend any other vendor using the NOT IN condition for those with no referrals.
  • List name and inventory value of each ingredient within two selected food groups using a simple WHERE filter.
  • Discover suppliers providing more ingredients than a specified supplier by employing grouping and comparison of counts.
  • Calculate average inventory values for each supplier who refers at least one different supplier using aggregation and filtering.

Studying That Suits You

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

Quiz Team

Description

Test your knowledge of SQL with this quiz focused on ingredient and supplier management. You will be challenged to retrieve information using various SQL statements and syntax, including SELECT, WHERE, and subqueries. Enhance your understanding of SQL operations in the context of inventory and supplier relationships.

More Like This

SQL Queries: Chapters 5 &amp; 6
10 questions

SQL Queries: Chapters 5 &amp; 6

AffordableFluorite avatar
AffordableFluorite
SQL Queries Test - Generators 3.0
12 questions

SQL Queries Test - Generators 3.0

EnthralledDaisy avatar
EnthralledDaisy
SQL Query Basics for Ingredient Management
24 questions

SQL Query Basics for Ingredient Management

GaloreArlington avatar
GaloreArlington
Use Quizgecko on...
Browser
Open
Browser
Browser