SQL*Plus: Release 10.2.0.1.0 - Production on Thu Sep 13 11:29:06 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> CREATE TABLE airplanes (
  2  program_id     VARCHAR2(3),
  3  line_number    NUMBER(10),
  4  customer_id    VARCHAR2(4),
  5  order_date     DATE,
  6  delivered_date DATE)
  7  PCTFREE 0
  8  PCTUSED 99;

Table created.

SQL>
SQL> CREATE INDEX ix_program_id
  2  ON airplanes (program_id)
  3  PCTFREE 0;

Index created.

SQL>
SQL> DECLARE
  2   progid  VARCHAR2(3) := '000';
  3   lineno  NUMBER(10) := 0;
  4   custid  VARCHAR2(4) := 'AAL';
  5   orddate DATE;
  6   deldate DATE;
  7  BEGIN
  8    LOOP
  9      SELECT DECODE(progid,'000','737','737','747','747','757','757','767','767',
                       '777','777','999')
 10      INTO progid
 11      FROM dual;
 12
 13      IF progid = 999 THEN
 14        EXIT;
 15      END IF;
 16
 17      FOR x IN 1..50000
 18      LOOP
 19        lineno := lineno + 1;
 20
 21        SELECT DECODE(custid,'AAL','DAL','DAL','SAL','SAL','ILC','ILC','SWA','SWA',
                         'NWO','NWO','USAF','USAF','AAL')
 22        INTO custid
 23        FROM dual;
 24
 25        OrdDate := SYSDATE + lineno;
 26        DelDate := OrdDate + lineno + 100;
 27
 28        INSERT INTO airplanes
 29        (program_id, line_number, customer_id,
 30        order_date, delivered_date)
 31        VALUES
 32        (progid, lineno, custid, orddate, deldate);
 33      END LOOP;
 34      lineno := 0;
 35    END LOOP;
 36    COMMIT;
 37  END load_airplanes;
 38  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT program_id,
  2    DECODE(customer_id, 'AAL', 'American Airlines') AIRLINE,
  3    delivered_date
  4  FROM airplanes
  5  WHERE ROWNUM < 11;

PRO AIRLINE           DELIVERED
--- ----------------- ---------
737                   24-DEC-07
737                   26-DEC-07
737                   28-DEC-07
737                   30-DEC-07
737                   01-JAN-08
737                   03-JAN-08
737 American Airlines 05-JAN-08
737                   07-JAN-08
737                   09-JAN-08
737                   11-JAN-08

10 rows selected.

SQL> SELECT program_id,
  2         DECODE(customer_id,
  3                'AAL', 'American Airlines',
  4                'ILC', 'Intl. Leasing Corp.',
  5                'NWO', 'Northwest Orient',
  6                'SAL', 'Southwest Airlines',
  7                'SWA', 'Sweptwing Airlines',
  8                'USAF', 'U.S. Air Force') AIRLINE,
  9         delivered_date
 10  FROM airplanes
 11  WHERE ROWNUM < 11;

PRO AIRLINE             DELIVERED
--- ------------------- ---------
737                     24-DEC-07
737 Southwest Airlines  26-DEC-07
737 Intl. Leasing Corp. 28-DEC-07
737 Sweptwing Airlines  30-DEC-07
737 Northwest Orient    01-JAN-08
737 U.S. Air Force      03-JAN-08
737 American Airlines   05-JAN-08
737                     07-JAN-08
737 Southwest Airlines  09-JAN-08
737 Intl. Leasing Corp. 11-JAN-08

10 rows selected.

SQL> SELECT program_id,
  2         DECODE(customer_id,
  3               'AAL', 'American Airlines',
  4               'ILC', 'Intl. Leasing Corp.',
  5               'NWO', 'Northwest Orient',
  6               'SAL', 'Southwest Airlines',
  7               'SWA', 'Sweptwing Airlines',
  8               'USAF', 'United States Air Force',
  9               'Not Known') AIRLINE,
 10         delivered_date
 11  FROM airplanes
 12  WHERE ROWNUM < 11;

PRO AIRLINE                 DELIVERED
--- ----------------------- ---------
737 Not Known               24-DEC-07
737 Southwest Airlines      26-DEC-07
737 Intl. Leasing Corp.     28-DEC-07
737 Sweptwing Airlines      30-DEC-07
737 Northwest Orient        01-JAN-08
737 United States Air Force 03-JAN-08
737 American Airlines       05-JAN-08
737 Not Known               07-JAN-08
737 Southwest Airlines      09-JAN-08
737 Intl. Leasing Corp.     11-JAN-08

10 rows selected.

SQL> SELECT program_id,
  2         DECODE(customer_id, 'AAL', 'AAL') AMERICAN,
  3         DECODE(customer_id, 'DAL', 'DAL') DELTA,
  4         DECODE(customer_id, 'NWO', 'NWO') NORTHWEST,
  5         DECODE(customer_id, 'ILC', 'ILC') INTL_LEASING
  6  FROM airplanes
  7  WHERE rownum < 20;

PRO AME DEL NOR INT
--- --- --- --- ---
737     DAL
737
737             ILC
737
737         NWO
737
737 AAL
737     DAL
737
737             ILC
737
737         NWO
737
737 AAL
737     DAL
737
737             ILC
737
737         NWO

19 rows selected.

SQL> SELECT program_id,
  2         COUNT (AMERICAN) AAL,
  3         COUNT (DELTA) DAL,
  4         COUNT (NORTHWEST) NWO,
  5         COUNT(INTL_LEASING) ILC
  6  FROM (
  7     SELECT program_id,
  8            DECODE(customer_id, 'AAL', 'AAL') AMERICAN,
  9            DECODE(customer_id, 'DAL', 'DAL') DELTA,
 10            DECODE(customer_id, 'NWO', 'NWO') NORTHWEST,
 11            DECODE(customer_id, 'ILC', 'ILC') INTL_LEASING
 12     FROM airplanes)
 13  GROUP BY program_id;

PRO        AAL        DAL        NWO        ILC
--- ---------- ---------- ---------- ----------
737       7142       7143       7143       7143
747       7143       7143       7143       7143
767       7143       7143       7143       7143
777       7143       7143       7143       7142
757       7143       7143       7142       7143

SQL>

Download Code