/* greenhouse Show all the amendments and if they have been used in plantings sh
ID: 3708572 • Letter: #
Question
/* greenhouse
Show all the amendments and if they have been used in plantings show how
many plantings.
Show the amendment code, name, and planting count.
Use JOIN to join the tables.
HINT: Keep in mind that some of the counts should be zero. If you don't see
any, what change to the SQL is needed?
*/
GREENHOUSE.TBLCROPHARVEST GREENHOUSE.TBLCROPPLANTING GREENHOUSETBLCROPPLANTINGAMEND NUMBER (11) NUMBER (11) VARCHAR2 (5 CHAR) HARVEST ID P AMENDID P * CROPPLANTING ID F?CROPVARID F* CROPPLANTINGID NUMBER (11) F*CROPPLANTINGID HARVEST DATE VARCHAR2 (6 CHAR) F?AMENDCODE VARCHAR2 (5 CHAR) NUMBER (1) FLOAT (126) FLOAT (126) VARCHAR2 (50 CHAR) VARCHAR2 (10 CHAR VARCHAR2 (255 CHAR VARCHAR2 (15 CHAR) AMENDUSED COMMENT COMBINED ROWS PLANTED IN ROW PLANT SPACING NUMBER (3) DATE PLANTED DATE FINISHED NUMBER OF PLANTS PRIMARY_HARVEST YIELD WEIGHT YIELD COUNT VARCHAR2 (75 CHAR) PK TBLCROPPLANTINGAMEND (AMENDID) TBLAMENDMENTTBLCROPPLANTINGAME (AMENDCODE TBLCROPPLANTINGTBLCROPPLANTING (CROPPLANTINGID) COMMENT FLOAT (126) FLOAT (126) VARCHAR2 (15 CHAR VARCHAR2 (18 CHAR VARCHAR2 (20 CHAR VARCHAR2 (255 CHAR) ? CROPVARIDICROPPLANTINGID) ? FKTBLAMENDMENTTBLCROPPLANTING (AMENDCODE) ? INDPLANTIDCODE (CROPPLANTINGID, AMENDCODE) ? PKTBLCROPPLANTINGAMENDAAMENDID) DESTINATION PODESIGNATED SEED TYPE DATE CREATED PK TBLCROPHARVEST (HARVEST ID) TBLCROPPLANTINGTBLCROPHARVEST (CROPPLANTINGID) PK-TBLCROPHARVEST (HARVEST-ID) SEEDER COMMENT GREENHOUSE.TBLAMENDMEN DATE CREATED P AMEND CODE VARCHAR2 (5 CHAR AUTO ROWNUM PK TBLCROPPLANTING (CROPPLANTING ID) ? AMENDMENT VARCHAR2 (20 CHAR DESCRIPTION VARCHAR2 (60 CHAR PLANT ID (CROPPLANTINGID) TBLBAY BEDTBLCROPPLANTING (BAY BED TBLCROPVARIETYTBLCROPPLANTING (CROPVARID) PK TBLAMENDMENT (AMEND CODE PK-TBLAMENDMENT(AMEND-CODE) GREENHOUSE.TBLCROPVARIETY P CROPVARID NUMBER (11) ? ? ? ? FK. TBLBAYBEDTBLCROPPLANTING (BAY-BED) NUMBER-OF-PLANTINGS (NUMBER-OF-PLANTS) PK. TBLCROPPLANTING (CROPPLANTINGID) PLANTID11 (CROPVARID) ROWNUM-(AUTO-ROWNUM) VARCHAR2 (50 CHAR VARCHAR2 (55 CHAR) VARIETY COMMENT VARCHAR2 (50 CHAR) PK TBLCROPVARIETY (CROPVARID) TBLCROPTBLCROPVARIETY (CROP) GREENHOUSE.TBLBAY BED - - ? P BAY_BED ZONE AREA COMMENT VARCHAR2 (50 CHAR) VARCHAR2 (6 CHAR) VARCHAR2 (20 CHAR) VARCHAR2 (3 CHAR) VARCHAR2 (15 CHAR) VARCHAR2 (25 CHAR) GREENHOUSE.TBLCROP FK TBLCROPTBLCROPVARIETY (CROP) IND CROPVARIETY (CROP, VARIETY) PK TBLCROPVARIETY (CROPVARID) VARIETY (VARIETY VARCHAR2 (50 CHAR) VARCHAR2 (20 CHAR) NUMBER (15,4) CROPTYPE - PK TBLCROP (CROP) PK TBLBAY BED (BAY BED PK-TBLBAY-BED (BAY-BED) ? PK_TBLCROP (CROP) ?Explanation / Answer
select AMENDID,CROPPLANTINGID,AMENDCODE,AMENDUSED,COMMENT_,COUNT(CROPPLANTINGID) as COUNT_OF_PLANTING FROM GREENHOUSE.TBLCROPPLANTINGAMEND INNER JOIN GREENHOUSE.TBLCROPPLANTING ON GREENHOUSE.TBLCROPPLANTING.CROPPLANTINGID=GREENHOUSE.TBLCROPPLANTINGAMEND.CROPPLANTINGID;
SELECT AMENDCODE,AMENDMENT,Sum(NUMBER_OF_PLANTS) from GREENHOUSE.TBLAMENDMENT INNER JOIN GREENHOUSE.TBLCROPPLANTINGAMEND ON GREENHOUSE.TBLAMENDMENT.AMENDCODE = GREENHOUSE.TBLCROPPLANTINGAMEND.AMENDCODE INNER JOIN GREENHOUSE.TBLCROPPLANTING ON GREENHOUSE.TBLCROPPLANTING.CROPPLANTINGID=GREENHOUSE.TBLCROPPLANTINGAMEND.CROPPLANTINGID;
The counts will be zero based on the data present in the tables only. If there is no condition matching or no data available in the the column, then the record count will be zero.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.