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.

Here is my code so far

CREATE TABLE GARDEN (

VEGGIEID INTEGER NOT NULL,

"NAME" VARCHAR(15) NOT NULL,

VARIETY VARCHAR(20) NOT NULL,

SEASON VARCHAR(20) NOT NULL,

DAYSTOHARVEST INTEGER NOT NULL CHECK (DAYSTOHARVEST < 80)

WATERING DECIMAL(4, 2) NOT NULL CHECK (WATERING <120)

PRIMARY KEY ("NAME", VARIETY, SEASON, VEGGIEID, DAYSTOHARVEST, WATERING)

 

);

INSERT INTO RCS.GARDEN (VEGGIEID, "NAME", VARIETY, SEASON, DAYSTOHARVEST, WATERING)

VALUES (5, 'carrot', 'Danvers', 'winter', 75, 1.00);

INSERT INTO RCS.GARDEN (VEGGIEID, "NAME", VARIETY, SEASON, DAYSTOHARVEST, WATERING)

VALUES (12, 'carrot', 'Imperator', 'winter', 77, 1.00);

INSERT INTO RCS.GARDEN (VEGGIEID, "NAME", VARIETY, SEASON, DAYSTOHARVEST, WATERING)

VALUES (1, 'carrot', 'Nantes', 'winter', 60, 0.80);

INSERT INTO RCS.GARDEN (VEGGIEID, "NAME", VARIETY, SEASON, DAYSTOHARVEST, WATERING)

VALUES (8, 'cucumber', 'Cocozelle', 'summer', 55, 0.80);

INSERT INTO RCS.GARDEN (VEGGIEID, "NAME", VARIETY, SEASON, DAYSTOHARVEST, WATERING)

VALUES (3, 'cucumber', 'Marketmore', 'summer', 68, 0.80);

INSERT INTO RCS.GARDEN (VEGGIEID, "NAME", VARIETY, SEASON, DAYSTOHARVEST, WATERING)

VALUES (13, 'cucumber', 'national', 'spring', 55, 0.80);

INSERT INTO RCS.GARDEN (VEGGIEID, "NAME", VARIETY, SEASON, DAYSTOHARVEST, WATERING)

VALUES (15, 'peas', 'Alaska', 'fall', 55, 1.20);

INSERT INTO RCS.GARDEN (VEGGIEID, "NAME", VARIETY, SEASON, DAYSTOHARVEST, WATERING)

VALUES (6, 'peas', 'Sugar daddy', 'fall', 62, 1.20);

INSERT INTO RCS.GARDEN (VEGGIEID, "NAME", VARIETY, SEASON, DAYSTOHARVEST, WATERING)

VALUES (16, 'pumpkin', 'Conneticut', 'summer', 79, 1.00);

INSERT INTO RCS.GARDEN (VEGGIEID, "NAME", VARIETY, SEASON, DAYSTOHARVEST, WATERING)

VALUES (9, 'spinach', 'Bloomsdale', 'fall', 36, 1.25);

INSERT INTO RCS.GARDEN (VEGGIEID, "NAME", VARIETY, SEASON, DAYSTOHARVEST, WATERING)

VALUES (7, 'spinach', 'Hierloom', 'fall', 45, 1.25);

INSERT INTO RCS.GARDEN (VEGGIEID, "NAME", VARIETY, SEASON, DAYSTOHARVEST, WATERING)

VALUES (4, 'squash', 'Straightneck', 'spring', 54, 1.00);

INSERT INTO RCS.GARDEN (VEGGIEID, "NAME", VARIETY, SEASON, DAYSTOHARVEST, WATERING)

VALUES (11, 'tomato', 'Jelly Bean', 'spring', 70, 1.10);

INSERT INTO RCS.GARDEN (VEGGIEID, "NAME", VARIETY, SEASON, DAYSTOHARVEST, WATERING)

VALUES (14, 'tomato', 'Red Cherry', 'spring', 70, 1.10);

INSERT INTO RCS.GARDEN (VEGGIEID, "NAME", VARIETY, SEASON, DAYSTOHARVEST, WATERING)

VALUES (10, 'tomato', 'Sweetie', 'spring', 70, 1.10);

INSERT INTO RCS.GARDEN (VEGGIEID, "NAME", VARIETY, SEASON, DAYSTOHARVEST, WATERING)

VALUES (2, 'tomatos', 'Beefsteak', 'spring', 79, 1.10);

 

Ch4Ch5VeggieDatabaseA6.1DBGarden.docAssignmentA6.1DBGarden for 2016This DB will also be used in week 5 as the target for other SQL questions. We will refer tothistable in other contexts as well, so, loading the data will be a one-time task ( except for lateredits !! :-) )For this assigment,A6.1DBGarden,I will ask you to buildand query it using the built in NBtools.Assignment details:Build a database , the table below, and send in a screen shot of the create,insert code, and aquery that prints out the name, variety, season, and daysToHarvest, sorted by name,embedded within a work document.In your table creation code, include checks on season, daysToHarvest, and wateringScenario: You are a consultant to home gardeners who ask you to advise them as to what to growand when to plant.You will show them a database that consists of the following sketches for tablesthat you will make available for their particular situation( to be revised as needed)The vegetable name will be something like Peas, and the variety will be something like Wando.plantingSeason, specific to the Tucson/Phoenix area, is an enumeration {spring, summer, fall,winter}veggieID : Int// the key for this tabledaysToHarvest : Int Check < 80 ( a required check in the table)watering : Decimal(4,2) is the estimated total water needed for a unit of planting area, Check <100.00All entries in the table arerequired, no nulls allowed.Database : MyGarden // use whatever name you wishTable 1. VeggiePropertiesRR( veggieID, veggieName,variety, plantingSeason, daysToHarvest, watering)Use the data supplied in the table belowVeggiePropertiesRRveggieIDnamevarietyseasondaysToHarvestwatering(inch/week)1carrotNanteswinter600.82tomatoBeefsteakspring791.13cucumberMarketmore76summer680.84squashStraightneckspring5415carrotDanverswinter7516peasSugar Daddyfall621.27spinachHierloomfall451.258cucumberCocozellesummer550.8Arizona State UniversityPolytechnic Campus

• 1494219652-Ch4Ch5VeggieDatabaseA6.1DBGarden1 1.doc

ComputerScienceExpert

(11)

Status NEW Posted 08 May 2017 04:05 AM My Price 11.00

-----------

Attachments

file 1494219655-Solutions file 2.docx preview (51 words )

H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly -----------onl-----------ine----------- an-----------d g-----------ive----------- yo-----------u e-----------xac-----------t f-----------ile----------- an-----------d t-----------he -----------sam-----------e f-----------ile----------- is----------- al-----------so -----------sen-----------t t-----------o y-----------our----------- em-----------ail----------- th-----------at -----------is -----------reg-----------ist-----------ere-----------d o-----------n -----------THI-----------S W-----------EBS-----------ITE-----------. ----------- Th-----------ank----------- yo-----------u -----------

Not Rated(0)

• Buy Answer