SQL commands to find min, max, etc This is an exercise to try out the SQL comman

Question

SQL commands to find min, max, etc

This is an exercise to try out the SQL commands, of Group By and aggregation functions, Sum Avg, Max, Min See the attachment for examples of how to do this: SQLGroupBY.doc Referring to the file ( you have worked with already) Ch4Ch5VeggieDatabaseA6.1DBGarden.doc 0. Find the overall average days to harvest

1. Find the Max overall days to harvest as well as the Min

1. Find the Average days to harvest, grouping by veggie name

2. Do this for max, min days to harvest grouping by veggie name.

3. Find the Average days to harvest, group by season. Show your SQL code , embed it in a .doc and submit.

mysql> CREATE TABLE VeggiePropertiesRR

(

veggieID int NOT NULL,

veggieName varchar(27) NOT NULL,

variety varchar(27) NOT NULL,

plantingSeason varchar(27) NOT NULL,

daysToHarvest int,

watering DECIMAL(4,2),

CONSTRAINT chk_VeggiePropertiesRR check (daysToHarvest<80 ,w atering<120.00)

)

Explanation / Answer

1. Find the overall average days to harvest

select AVG(daysToHarvest) from VeggiePropertiesRR;

//AVG is the aggregate function for the average

2. Find the Max overall days to harvest as well as the Min

select MAX(daysToHarvest),MIN(daysToHarvest) from VeggiePropertiesRR;

//MAX and MIN are the aggregate functions for maximum and minimum

3. Find the Average days to harvest, grouping by veggie name

select veggieName,AVG(daysToHarvest) from VeggiePropertiesRR group by veggieName;

//We are grouping by veggieName, hence veggieName needs to be selected

4. Do this for max, min days to harvest grouping by veggie name.

select veggieName,MAX(daysToHarvest) from VeggiePropertiesRR group by veggieName;
select veggieName,MIN(daysToHarvest) from VeggiePropertiesRR group by veggieName;

5. Find the Average days to harvest, group by season. Show your SQL code , embed it in a .doc and submit.

select plantingSeason,AVG(daysToHarvest) from VeggiePropertiesRR group by plantingSeason;

//We are grouping it by plantingSeason now.

Please let me know if you face any issue, will be glad to help. Have a good day.

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.