1) BORDERPAT: Nested aggregate functions

By facility, show the total price of items received in

transfers, the # of transfers received, and the average dollar

amount of inventory received in transfers.

Hint: Create a subquery that sums the item prices grouped

by transfer #. Include the facility code for the facility that

received the transfer. You won't need any additional tables

in the outer query. The outer query will only use the fields

provided by the subquery.

Show output for the subquery then the full solution.

a) subquery to sum prices by transfer #.

b) outer query with subquery;

BORDERPAT ADDRESS BORDERPAT PERSONNEL BORDERPAT FACILITY PFADDRFACCODE CHAR (3 BYTE P PERCODE F PERFACCODE CHAR (3 BYTE P FACCODE CHAR (3 BYTE CHAR (6 BYTE P ADDRTYPE ADDRLINE1 ADDRLINE2 VARCHAR2 (10 BYTE) VARCHAR2 (30 BYTE) VARCHAR2 (30 BYTE) CHAR (9 BYTE) * FACNAME VARCHAR2 (40 BYTE PERFNAME PERLNAME PERPOSITION VARCHAR2 (30 BYTE VARCHAR2 (13 BYTE) VARCHAR2 (13 BYTE) FACTYPE VARCHAR2 (25 BYTE) F FACREGION CHAR (3 BYTE) F ADDRZIP PKFACILITY (FACCODE FK REGIONFACIFACREGION) PKADDRESS (ADDRFACCODE, ADDRTYPE) FKFACCODE (ADDRFACCODE) FKZIP (ADDRZIP) PKADDRESS (ADDRFACCODE, ADDRTYPE) PKPERCODE (PERCODE) FKPERFACCODE (PERFACCODE PKPERCODE (PERCODE) ? PK FACILITY (FACCODE ? ? BORDERPATINVENTORY VARCHAR2 (12 BYTE) VARCHAR2 (12 BYTE) BORDERPAT ZIPCODE INVINSNO INVMANUFNO VARCHAR2 (12 BYTE INVITEM INVDES INVMAKE INVMODEL INVCOST INVTYPE INVYEAR P * ZIPCODE CHAR (9 BYTE BORDERPAT TRANSFER VARCHAR2 (25 BYTE) VARCHAR2 (25 BYTE) VARCHAR2 (30 BYTE) VARCHAR2 (20 BYTE) NUMBER (8,2) VARCHAR2 (30 BYTE) NUMBER (4) CHAR (3 BYTE) * ZIPCITY VARCHAR2 (22 BYTE) *ZIPSTATE CHAR (4 BYTE PKZIP (ZIPCODE P TRANSNO F TRANSTOFAC F TRANSFROMFAC CHAR (3 BYTE NUMBER (5) CHAR (3 BYTE) TRANSTYPE TRANSREPORTNO VARCHAR2 (6 BYTE TRANSDATE TRANSPONO TRANSBOLNO VARCHAR2 (12 BYTE) ? PKZIP (ZIPCODE) DATE VARCHAR2 (8 BYTE) VARCHAR2 (8 BYTE) F INVLOC INVCOMMENT VARCHAR2 (100 BYTE PKTRANSN?(TRANSNO) FKTRANSFROMFAC (TRANSFROMFAC) FKTRANSTOFAC TRANSTOFAC) PKINVNO (INVNO) FKINVLOC (INVLOC) ? PKTRANSNOTRANNO) ? PKINVNO (INVNO) BORDERPAT TRANSFERDETAIL PF TRANSNO PF*INVNO NUMBER (5) VARCHAR2 (12 BYTE) TRANSDETPRICE NUMBER (5,2) PKTRANSDETAIL (TRANSNO, INVNO) FKINVNO (INVNO) ?FKTRANSN?(TRANSNO) ? PKTRANSDETAIL(TRANSNO, INVNO) BORDERPAT ADDRESS BORDERPAT PERSONNEL BORDERPAT FACILITY PFADDRFACCODE CHAR (3 BYTE P PERCODE F PERFACCODE CHAR (3 BYTE P FACCODE CHAR (3 BYTE CHAR (6 BYTE P ADDRTYPE ADDRLINE1 ADDRLINE2 VARCHAR2 (10 BYTE) VARCHAR2 (30 BYTE) VARCHAR2 (30 BYTE) CHAR (9 BYTE) * FACNAME VARCHAR2 (40 BYTE PERFNAME PERLNAME PERPOSITION VARCHAR2 (30 BYTE VARCHAR2 (13 BYTE) VARCHAR2 (13 BYTE) FACTYPE VARCHAR2 (25 BYTE) F FACREGION CHAR (3 BYTE) F ADDRZIP PKFACILITY (FACCODE FK REGIONFACIFACREGION) PKADDRESS (ADDRFACCODE, ADDRTYPE) FKFACCODE (ADDRFACCODE) FKZIP (ADDRZIP) PKADDRESS (ADDRFACCODE, ADDRTYPE) PKPERCODE (PERCODE) FKPERFACCODE (PERFACCODE PKPERCODE (PERCODE) ? PK FACILITY (FACCODE ? ? BORDERPATINVENTORY VARCHAR2 (12 BYTE) VARCHAR2 (12 BYTE) BORDERPAT ZIPCODE INVINSNO INVMANUFNO VARCHAR2 (12 BYTE INVITEM INVDES INVMAKE INVMODEL INVCOST INVTYPE INVYEAR P * ZIPCODE CHAR (9 BYTE BORDERPAT TRANSFER VARCHAR2 (25 BYTE) VARCHAR2 (25 BYTE) VARCHAR2 (30 BYTE) VARCHAR2 (20 BYTE) NUMBER (8,2) VARCHAR2 (30 BYTE) NUMBER (4) CHAR (3 BYTE) * ZIPCITY VARCHAR2 (22 BYTE) *ZIPSTATE CHAR (4 BYTE PKZIP (ZIPCODE P TRANSNO F TRANSTOFAC F TRANSFROMFAC CHAR (3 BYTE NUMBER (5) CHAR (3 BYTE) TRANSTYPE TRANSREPORTNO VARCHAR2 (6 BYTE TRANSDATE TRANSPONO TRANSBOLNO VARCHAR2 (12 BYTE) ? PKZIP (ZIPCODE) DATE VARCHAR2 (8 BYTE) VARCHAR2 (8 BYTE) F INVLOC INVCOMMENT VARCHAR2 (100 BYTE PKTRANSN?(TRANSNO) FKTRANSFROMFAC (TRANSFROMFAC) FKTRANSTOFAC TRANSTOFAC) PKINVNO (INVNO) FKINVLOC (INVLOC) ? PKTRANSNOTRANNO) ? PKINVNO (INVNO) BORDERPAT TRANSFERDETAIL PF TRANSNO PF*INVNO NUMBER (5) VARCHAR2 (12 BYTE) TRANSDETPRICE NUMBER (5,2) PKTRANSDETAIL (TRANSNO, INVNO) FKINVNO (INVNO) ?FKTRANSN?(TRANSNO) ? PKTRANSDETAIL(TRANSNO, INVNO)