HP ProLiant 3000 DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 43
Create Table Revenue0 Supplier_no Integer, Total_revenue Number20
View all HP ProLiant 3000 manuals
Add to My Manuals
Save this manual to your list of manuals |
Page 43 highlights
DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers INSERT INTO SUM_PROMO_SALES0 SELECT SUM(AMOUNT) FROM ALL_SALES0 WHERE TYPE LIKE 'PROMO%'; Appendix C Query 14 - Variant C (Con't.) INSERT INTO SUM_ALL_SALES0 SELECT SUM(AMOUNT) FROM ALL_SALES0; SELECT 100.00*PROMO_AMOUNT/ALL_AMOUNT AS PROMO_REVENUE FROM SUM_PROMO_SALES0, SUM_ALL_SALES0; DROP TABLE ALL_SALES0; DROP TABLE SUM_PROMO_SALES0; DROP TABLE SUM_ALL_SALES0; Query 15 - Variant B CREATE TABLE REVENUE0 (SUPPLIER_NO INTEGER, TOTAL_REVENUE NUMBER(20, 2)) tablespace TS_S; INSERT INTO REVENUE0 SELECT L_SUPPKEY, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) FROM LINEITEM WHERE L_SHIPDATE >= TO_DATE('1997-10-01','YYYY-MM-DD') AND L_SHIPDATE < ADD_MONTHS(TO_DATE('1997-10-01','YYYY-MM-DD'),3) GROUP BY L_SUPPKEY; SELECT S_SUPPKEY, S_NAME, S_ADDRESS, S_PHONE, TOTAL_REVENUE FROM SUPPLIER, REVENUE0 WHERE S_SUPPKEY = SUPPLIER_NO AND TOTAL_REVENUE = (SELECT MAX(TOTAL_REVENUE) FROM REVENUE0 ) ORDER BY S_SUPPKEY; DROP TABLE REVENUE0; © 1998 Compaq Computer Corporation, All Rights Reserved Doc No ECG156/0398