/* Advanced SQL Greenhouse Database Introduction */ /* Get a count of the records in each table. */ select count(*) as "tblCrop Rows" from tblCrop; select count(*) as "tblCropVariety Rows" from tblCropVariety; select count(*) as "tblBay_Bed Rows" from tblBay_Bed; select count(*) as "tblCropPlanting Rows" from tblCropPlanting; select count(*) as "tblAmendment Rows" from tblAmendment; select count(*) as "tblCropPlantingAmend Rows" from tblCropPlantingAmend; select count(*) as "tblCropHarvest Rows" from tblCropHarvest; /* List contents of smaller tables */ select crop_type, crop from tblCrop order by crop_type, crop; select area, zone, sector, bay_bed, size from tblbay_bed order by area, zone, sector, bay_bed; select amend_code, amendment, description from tblAmendment order by amend_code; /* Example SQL: Show how many crops have been planted in the bay-bed 01A1. */ select count(*) as "01A1 Crop Count" from tblCropPlanting where bay_bed = '01A1'; select tblCropPlanting.cropVarID, crop, variety, date_planted from tblCropPlanting join tblCropVariety on tblCropPlanting.cropVarID = tblCropVariety.cropVarID where bay_bed = '01A1' order by crop, variety, date_planted;