(SQL Query Question) The following image is of a database with the tables listed (foodname, foodgroup, nutrientname and nutrientamount) {Please ignore any other table, just these 4}

The table foodname contains a row for each food in the database. Each row has a FoodID, a unique identifier for the food, a FoodGroupID that identifies the group this food belongs to, an English (FoodDescription) and a French name (FoodDescriptionF) for the food.

The group table, foodgroup, contains a FoodGroupID and a name for the group in both English(FoodGroupName)and French (FoodGroupNameF) (e.g. Pork Products).

The nutrient name table, nutrientname, contains rows for each nutrient recorded, for example, fat, protein, and vitamin C. In addition, there is an identifier for each nutrient, NutrientNameID and the Unit that the nutrient is measured in (g, mcg, kCal, etc.).

The final table is the main one, nutrientamount. It links the tables foodname and nutrientname. For each food, there are multiple rows in the nutrientamount table, one for each nutrient. Each entry includes the FoodID, the NutrientNameID and how much of that nutrient is present.

Kindly write the following SQL queries to retrieve data from the above database:

1. Write a query to calculate how many food names in the Baked Products group start with the letters B, C, or D. Write this query using three different ways (without using "LEFT" function). One hint I was given was if I can write the "IN" another way.

I was able to find only one way of writing this query, but dont know how to write any other way.:

--OPTION 1 (THIS QUERY WORKS)

SELECT COUNT(FoodDescription)

FROM foodgroup, foodname

WHERE FoodGroupName = 'Baked Products'

AND foodgroup.FoodGroupID = foodname.FoodGroupID

AND SUBSTRING(FoodDescription, 1, 1) IN ('B', 'C', 'D');

This gives the result of COUNT(FoodDescription) = 276 with 1 row in set.

I also tried doing the above query the following way but it did not work:

Select count(*) from

(Select t1.* from foodname t1 inner join foodgroup t2 on t1.FoodGroupID=t2.FoodGroupID and t1.FoodGroupID=(select FoodGroupID from foodgroup where FoodGroupName='BakedProducts')) where FoodGroupID like '%B' OR '%C' OR '%D';

Please write the above query in two other ways that gets the same result as Option 1.

2. Retrieve the food ID and food names of foods that start with the same 20 characters. Make sure you do not count the same food twice. i.e. "CEREAL, CAPTAIN CRUNCH" will match itself, but we don't want to count that. Sort the results using the food name and food id.

I tried doing it this way but it did not work:

SELECT UPPER(LEFT(ScientificName,20)) as FoodName FROM foodtable GROUP BY UPPER(LEFT(ScientificName,20)) ORDER BY FoodName;

Was told that for this question, I need to find out when 2 or more food descriptions match with each other and everyone without counting with itself.

YIELD NAME YieldID YieldName YieldName YIELD AMOUNT FoodID YieldID YieldAmount YieldDateOfEntry REFUSE NAME RefuselD RefuseName RefuseName REFUSE AMOUNT FoodID Refusel RefuseAmount RefuseDateOfEntry FOOD GROUP FoodGroupID Food Group Code FoodGroupName FoodGroupNameF MEASURE NAME MeasurelD MeasureName MeasureNameF CONVERSION FACTOR FoodID MeasurelD ConversionFactor Value ConvFactor DateOfEntry FOOD NAME FoodID FoodCode FoodGroupID FoodSourceID FoodDescription FoodDescription CountryCode FoodDateOfEntry FoodDateOfPublication ScientificName FOOD SOURCE FoodSourceID FoodSource Code FoodSource Description FoodSource DescriptionF NUTRIENT NAME NutrientNameID NutrientCode NutrientSymbol Unit NutrientName NutrientNameF Tagname Nutrient Decimals NUTRIENT AMOUNT FoodID NutrientNameld Nutrient SourceID Nutrient Value StandardError NumberOfObservation Nutrient DateOfEntry NUTRIENT SOURCE Nutrient SourcelD Nutrient Source Code NutrientSourceDescription NutrientSource DescriptionF YIELD NAME YieldID YieldName YieldName YIELD AMOUNT FoodID YieldID YieldAmount YieldDateOfEntry REFUSE NAME RefuselD RefuseName RefuseName REFUSE AMOUNT FoodID Refusel RefuseAmount RefuseDateOfEntry FOOD GROUP FoodGroupID Food Group Code FoodGroupName FoodGroupNameF MEASURE NAME MeasurelD MeasureName MeasureNameF CONVERSION FACTOR FoodID MeasurelD ConversionFactor Value ConvFactor DateOfEntry FOOD NAME FoodID FoodCode FoodGroupID FoodSourceID FoodDescription FoodDescription CountryCode FoodDateOfEntry FoodDateOfPublication ScientificName FOOD SOURCE FoodSourceID FoodSource Code FoodSource Description FoodSource DescriptionF NUTRIENT NAME NutrientNameID NutrientCode NutrientSymbol Unit NutrientName NutrientNameF Tagname Nutrient Decimals NUTRIENT AMOUNT FoodID NutrientNameld Nutrient SourceID Nutrient Value StandardError NumberOfObservation Nutrient DateOfEntry NUTRIENT SOURCE Nutrient SourcelD Nutrient Source Code NutrientSourceDescription NutrientSource DescriptionF