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

Get HP ProLiant 3000 PDF manuals and user guides 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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers
Appendix C
1998 Compaq Computer Corporation, All Rights Reserved
Doc No ECG156/0398
INSERT INTO SUM_PROMO_SALES0
SELECT
SUM(AMOUNT)
FROM
ALL_SALES0
WHERE
TYPE LIKE 'PROMO%';
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;