Using AVG with ROLLUP

Thursday, July 30th, 2009

SQL> – create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /

Table created.

SQL>
SQL> – prepare data
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               values (‘01‘,’Jason‘,    ’Martin‘,  to_date(‘19960725‘,’YYYYMMDD‘), to_date(‘20060725‘,’YYYYMMDD‘), 1234.56, ‘Toronto‘,  ’Programmer‘)
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values(‘02‘,’Alison‘,   ’Mathews‘, to_date(‘19760321‘,’YYYYMMDD‘), to_date(‘19860221‘,’YYYYMMDD‘), 6661.78, ‘Vancouver‘,’Tester‘)
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values(‘03‘,’James‘,    ’Smith‘,   to_date(‘19781212‘,’YYYYMMDD‘), to_date(‘19900315‘,’YYYYMMDD‘), 6544.78, ‘Vancouver‘,’Tester‘)
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values(‘04‘,’Celia‘,    ’Rice‘,    to_date(‘19821024‘,’YYYYMMDD‘), to_date(‘19990421‘,’YYYYMMDD‘), 2344.78, ‘Vancouver‘,’Manager‘)
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values(‘05‘,’Robert‘,   ’Black‘,   to_date(‘19840115‘,’YYYYMMDD‘), to_date(‘19980808‘,’YYYYMMDD‘), 2334.78, ‘Vancouver‘,’Tester‘)
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values(‘06‘,’Linda‘,    ’Green‘,   to_date(‘19870730‘,’YYYYMMDD‘), to_date(‘19960104‘,’YYYYMMDD‘), 4322.78,‘New York‘,  ’Tester‘)
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values(‘07‘,’David‘,    ’Larry‘,   to_date(‘19901231‘,’YYYYMMDD‘), to_date(‘19980212‘,’YYYYMMDD‘), 7897.78,‘New York‘,  ’Manager‘)
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values(‘08‘,’James‘,    ’Cat‘,     to_date(‘19960917‘,’YYYYMMDD‘), to_date(‘20020415‘,’YYYYMMDD‘), 1232.78,‘Vancouver‘, ’Tester‘)
  3  /

1 row created.

SQL>
SQL>
SQL>
SQL> – display data in the table
SQL> select * from Employee
  2  /

ID   FIRST_NAME LAST_NAME  START_DATE  END_DATE        SALARY CITY       DESCRIPTION
—- ———- ———- ———– ———– ———- ———- —————
01   Jason      Martin     1996-JUL-25 2006-JUL-25    1234.56 Toronto    Programmer
02   Alison     Mathews    1976-MAR-21 1986-FEB-21    6661.78 Vancouver  Tester
03   James      Smith      1978-DEC-12 1990-MAR-15    6544.78 Vancouver  Tester
04   Celia      Rice       1982-OCT-24 1999-APR-21    2344.78 Vancouver  Manager
05   Robert     Black      1984-JAN-15 1998-AUG-08    2334.78 Vancouver  Tester
06   Linda      Green      1987-JUL-30 1996-JAN-04    4322.78 New York   Tester
07   David      Larry      1990-DEC-31 1998-FEB-12    7897.78 New York   Manager
08   James      Cat        1996-SEP-17 2002-APR-15    1232.78 Vancouver  Tester

8 rows selected.

SQL>
SQL>
SQL>
SQL>
SQL> –Using AVG with ROLLUP
SQL>
SQL> SELECT city, description, AVG(salary)
  2  FROM employee
  3  GROUP BY ROLLUP(city, description);

CITY       DESCRIPTION     AVG(SALARY)
———- ————— ———–
Toronto    Programmer          1234.56
Toronto                        1234.56
New York   Tester              4322.78
New York   Manager             7897.78
New York                       6110.28
Vancouver  Tester              4193.53
Vancouver  Manager             2344.78
Vancouver                      3823.78
                             4071.7525

9 rows selected.

SQL>
SQL>
SQL>
SQL>
SQL> – clean the table
SQL> drop table Employee
  2  /

Table dropped.

SQL>
SQL>
           
       

Posted in Uncategorized | No Comments »

Rank row without analytical functions

Sunday, July 19th, 2009

SQL>
SQL>
SQL> – create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /

Table created.

SQL>
SQL> – prepare data
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               values (‘01‘,’Jason‘,    ’Martin‘,  to_date(‘19960725‘,’YYYYMMDD‘), to_date(‘20060725‘,’YYYYMMDD‘), 1234.56, ‘Toronto‘,  ’Programmer‘)
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values(‘02‘,’Alison‘,   ’Mathews‘, to_date(‘19760321‘,’YYYYMMDD‘), to_date(‘19860221‘,’YYYYMMDD‘), 2334.78, ‘Vancouver‘,’Tester‘)
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values(‘03‘,’James‘,    ’Smith‘,   to_date(‘19781212‘,’YYYYMMDD‘), to_date(‘19900315‘,’YYYYMMDD‘), 2334.78, ‘Vancouver‘,’Tester‘)
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values(‘04‘,’Celia‘,    ’Rice‘,    to_date(‘19821024‘,’YYYYMMDD‘), to_date(‘19990421‘,’YYYYMMDD‘), 2334.78, ‘Vancouver‘,’Manager‘)
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values(‘05‘,’Robert‘,   ’Black‘,   to_date(‘19840115‘,’YYYYMMDD‘), to_date(‘19980808‘,’YYYYMMDD‘), 2334.78, ‘Vancouver‘,’Tester‘)
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values(‘06‘,’Linda‘,    ’Green‘,   to_date(‘19870730‘,’YYYYMMDD‘), to_date(‘19960104‘,’YYYYMMDD‘), 2334.78,‘New York‘,  ’Tester‘)
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values(‘07‘,’David‘,    ’Larry‘,   to_date(‘19901231‘,’YYYYMMDD‘), to_date(‘19980212‘,’YYYYMMDD‘), 2334.78,‘New York‘,  ’Manager‘)
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values(‘08‘,’James‘,    ’Cat‘,     to_date(‘19960917‘,’YYYYMMDD‘), to_date(‘20020415‘,’YYYYMMDD‘), 2334.78,‘Vancouver‘, ’Tester‘)
  3  /

1 row created.

SQL>
SQL>
SQL>
SQL> – display data in the table
SQL> select * from Employee
  2  /

ID   FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY       DESCRIPTION
—- ———- ———- ——— ——— ———- ———- —————
01   Jason      Martin     25-JUL-96 25-JUL-06    1234.56 Toronto    Programmer
02   Alison     Mathews    21-MAR-76 21-FEB-86    2334.78 Vancouver  Tester
03   James      Smith      12-DEC-78 15-MAR-90    2334.78 Vancouver  Tester
04   Celia      Rice       24-OCT-82 21-APR-99    2334.78 Vancouver  Manager
05   Robert     Black      15-JAN-84 08-AUG-98    2334.78 Vancouver  Tester
06   Linda      Green      30-JUL-87 04-JAN-96    2334.78 New York   Tester
07   David      Larry      31-DEC-90 12-FEB-98    2334.78 New York   Manager
08   James      Cat        17-SEP-96 15-APR-02    2334.78 Vancouver  Tester

8 rows selected.

SQL>
SQL>
SQL>
SQL> – without analytical functions:
SQL>
SQL> SELECT id, first_name, os Salary, ROWNUM Toprank
  2  FROM
  3    (SELECT id, first_name, salary os
  4    FROM employee
  5    ORDER BY salary desc)
  6  ORDER BY first_name;

ID   FIRST_NAME     SALARY    TOPRANK
—- ———- ———- ———-
02   Alison        2334.78          2
04   Celia         2334.78          1
07   David         2334.78          7
03   James         2334.78          3
08   James         2334.78          4
01   Jason         1234.56          8
06   Linda         2334.78          6
05   Robert        2334.78          5

8 rows selected.

SQL>
SQL>
SQL>
SQL> – clean the table
SQL> drop table Employee;

Table dropped.

SQL>
SQL>
SQL>
           
       

Posted in Rank Row | No Comments »

Rollup function in group by clause

Wednesday, July 15th, 2009

SQL> – create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /

Table created.

SQL>
SQL> – prepare data
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               values (‘01‘,’Jason‘,    ’Martin‘,  to_date(‘19960725‘,’YYYYMMDD‘), to_date(‘20060725‘,’YYYYMMDD‘), 1234.56, ‘Toronto‘,  ’Programmer‘)
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values(‘02‘,’Alison‘,   ’Mathews‘, to_date(‘19760321‘,’YYYYMMDD‘), to_date(‘19860221‘,’YYYYMMDD‘), 6661.78, ‘Vancouver‘,’Tester‘)
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values(‘03‘,’James‘,    ’Smith‘,   to_date(‘19781212‘,’YYYYMMDD‘), to_date(‘19900315‘,’YYYYMMDD‘), 6544.78, ‘Vancouver‘,’Tester‘)
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values(‘04‘,’Celia‘,    ’Rice‘,    to_date(‘19821024‘,’YYYYMMDD‘), to_date(‘19990421‘,’YYYYMMDD‘), 2344.78, ‘Vancouver‘,’Manager‘)
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values(‘05‘,’Robert‘,   ’Black‘,   to_date(‘19840115‘,’YYYYMMDD‘), to_date(‘19980808‘,’YYYYMMDD‘), 2334.78, ‘Vancouver‘,’Tester‘)
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values(‘06‘,’Linda‘,    ’Green‘,   to_date(‘19870730‘,’YYYYMMDD‘), to_date(‘19960104‘,’YYYYMMDD‘), 4322.78,‘New York‘,  ’Tester‘)
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values(‘07‘,’David‘,    ’Larry‘,   to_date(‘19901231‘,’YYYYMMDD‘), to_date(‘19980212‘,’YYYYMMDD‘), 7897.78,‘New York‘,  ’Manager‘)
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values(‘08‘,’James‘,    ’Cat‘,     to_date(‘19960917‘,’YYYYMMDD‘), to_date(‘20020415‘,’YYYYMMDD‘), 1232.78,‘Vancouver‘, ’Tester‘)
  3  /

1 row created.

SQL>
SQL>
SQL>
SQL> – display data in the table
SQL> select * from Employee
  2  /

ID   FIRST_NAME LAST_NAME  START_DATE  END_DATE        SALARY CITY       DESCRIPTION
—- ———- ———- ———– ———– ———- ———- —————
01   Jason      Martin     1996-JUL-25 2006-JUL-25    1234.56 Toronto    Programmer
02   Alison     Mathews    1976-MAR-21 1986-FEB-21    6661.78 Vancouver  Tester
03   James      Smith      1978-DEC-12 1990-MAR-15    6544.78 Vancouver  Tester
04   Celia      Rice       1982-OCT-24 1999-APR-21    2344.78 Vancouver  Manager
05   Robert     Black      1984-JAN-15 1998-AUG-08    2334.78 Vancouver  Tester
06   Linda      Green      1987-JUL-30 1996-JAN-04    4322.78 New York   Tester
07   David      Larry      1990-DEC-31 1998-FEB-12    7897.78 New York   Manager
08   James      Cat        1996-SEP-17 2002-APR-15    1232.78 Vancouver  Tester

8 rows selected.

SQL>
SQL>
SQL>
SQL> SELECT city, SUM(salary)
  2  FROM employee
  3  GROUP BY ROLLUP(city);

CITY       SUM(SALARY)
———- ———–
New York      12220.56
Toronto        1234.56
Vancouver      19118.9
              32574.02

SQL>
SQL> – clean the table
SQL> drop table Employee
  2  /

Table dropped.

SQL>
SQL>
           
       

Posted in Uncategorized | No Comments »

If NULLS LAST is left out of the final ORDER BY, the effect will be lost

Sunday, June 28th, 2009

SQL>
SQL> – create demo table
SQL> create table Employee(
  2    empno              Number(3)  NOT NULL, – Employee ID
  3    ename              VARCHAR2(10 BYTE),   – Employee Name
  4    hireDate          DATE,                – Date Employee Hired
  5    orig_salary        Number(8,2),         – Orignal Salary
  6    curr_salary        Number(8,2),         – Current Salary
  7    region             VARCHAR2(1 BYTE)     – Region where employeed
  8  )
  9  /

Table created.

SQL>
SQL>
SQL> – prepare data for employee table
SQL> insert into Employee(empno,  ename,  hireDate,                   orig_salary, curr_salary, region)
  2                values(122,‘Alison‘,to_date(‘19960321‘,’YYYYMMDD‘), 45000,       NULL,       ‘E’)
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(123, ‘James‘,to_date(‘19781212‘,’YYYYMMDD‘), 23000,       32000,       ‘W’)
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(104,‘Celia‘,to_date(‘19821024‘,’YYYYMMDD‘), NULL,       58000,        ‘E’)
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(105,‘Robert‘,to_date(‘19840115‘,’YYYYMMDD‘), 31000,      NULL,        ‘W’)
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(116,‘Linda‘, to_date(‘19870730‘,’YYYYMMDD‘), NULL,       53000,       ‘E’)
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(117,‘David‘, to_date(‘19901231‘,’YYYYMMDD‘), 78000,       NULL,       ‘W’)
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(108,‘Jode‘,  to_date(‘19960917‘,’YYYYMMDD‘), 21000,       29000,       ‘E’)
  3  /

1 row created.

SQL>
SQL> – display data in the table
SQL> select * from Employee
  2  /
Hit a key to continue

     EMPNO ENAME      HIREDATE  ORIG_SALARY CURR_SALARY R
———- ———- ——— ———– ———– -
       122 Alison     21-MAR-96       45000             E
       123 James      12-DEC-78       23000       32000 W
       104 Celia      24-OCT-82                   58000 E
       105 Robert     15-JAN-84       31000             W
       116 Linda      30-JUL-87                   53000 E
       117 David      31-DEC-90       78000             W
       108 Jode       17-SEP-96       21000       29000 E

7 rows selected.

SQL>
SQL>
SQL>
SQL> – If NULLS LAST is left out of the final ORDER BY, the effect will be lost.
SQL>
SQL> SELECT empno, ename, curr_salary,
  2    RANK()
  3    OVER(ORDER BY curr_salary desc) salary
  4  FROM employee
  5  ORDER BY curr_salary desc;
Hit a key to continue

     EMPNO ENAME      CURR_SALARY     SALARY
———- ———- ———– ———-
       122 Alison                          1
       105 Robert                          1
       117 David                           1
       104 Celia            58000          4
       116 Linda            53000          5
       123 James            32000          6
       108 Jode             29000          7

7 rows selected.

SQL>
SQL>
SQL> – clean the table
SQL> drop table Employee;

Table dropped.

SQL>
SQL>
           
       

Posted in Uncategorized | No Comments »

Compute: Sum the salaries and report the sums at the break points

Sunday, June 28th, 2009

SQL> – create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /

Table created.

SQL>
SQL> – prepare data
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               values (‘01‘,’Jason‘,    ’Martin‘,  to_date(‘19960725‘,’YYYYMMDD‘), to_date(‘20060725‘,’YYYYMMDD‘), 1234.56, ‘Toronto‘,  ’Programmer‘)
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values(‘02‘,’Alison‘,   ’Mathews‘, to_date(‘19760321‘,’YYYYMMDD‘), to_date(‘19860221‘,’YYYYMMDD‘), 6661.78, ‘Vancouver‘,’Tester‘)
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values(‘03‘,’James‘,    ’Smith‘,   to_date(‘19781212‘,’YYYYMMDD‘), to_date(‘19900315‘,’YYYYMMDD‘), 6544.78, ‘Vancouver‘,’Tester‘)
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values(‘04‘,’Celia‘,    ’Rice‘,    to_date(‘19821024‘,’YYYYMMDD‘), to_date(‘19990421‘,’YYYYMMDD‘), 2344.78, ‘Vancouver‘,’Manager‘)
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values(‘05‘,’Robert‘,   ’Black‘,   to_date(‘19840115‘,’YYYYMMDD‘), to_date(‘19980808‘,’YYYYMMDD‘), 2334.78, ‘Vancouver‘,’Tester‘)
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values(‘06‘,’Linda‘,    ’Green‘,   to_date(‘19870730‘,’YYYYMMDD‘), to_date(‘19960104‘,’YYYYMMDD‘), 4322.78,‘New York‘,  ’Tester‘)
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values(‘07‘,’David‘,    ’Larry‘,   to_date(‘19901231‘,’YYYYMMDD‘), to_date(‘19980212‘,’YYYYMMDD‘), 7897.78,‘New York‘,  ’Manager‘)
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values(‘08‘,’James‘,    ’Cat‘,     to_date(‘19960917‘,’YYYYMMDD‘), to_date(‘20020415‘,’YYYYMMDD‘), 1232.78,‘Vancouver‘, ’Tester‘)
  3  /

1 row created.

SQL>
SQL>
SQL>
SQL> – display data in the table
SQL> select * from Employee
  2  /

ID   FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY       DESCRIPTION
—- ———- ———- ——— ——— ———- ———- —————
01   Jason      Martin     25-JUL-96 25-JUL-06    1234.56 Toronto    Programmer
02   Alison     Mathews    21-MAR-76 21-FEB-86    6661.78 Vancouver  Tester
03   James      Smith      12-DEC-78 15-MAR-90    6544.78 Vancouver  Tester
04   Celia      Rice       24-OCT-82 21-APR-99    2344.78 Vancouver  Manager
05   Robert     Black      15-JAN-84 08-AUG-98    2334.78 Vancouver  Tester
06   Linda      Green      30-JUL-87 04-JAN-96    4322.78 New York   Tester
07   David      Larry      31-DEC-90 12-FEB-98    7897.78 New York   Manager
08   James      Cat        17-SEP-96 15-APR-02    1232.78 Vancouver  Tester

8 rows selected.

SQL>
SQL>
SQL>
SQL> – Compute
SQL> – Sum the salaries and report the sums at the break points
SQL>
SQL> SET echo off
SQL> BREAK ON city skip1
SQL> COMPUTE sum of salary ON city
SQL> SET verify off
SQL> SELECT id, first_name, salary, city FROM employee
  2  ORDER BY city
  3  /

ID   FIRST_NAME     SALARY CITY
—- ———- ———- ———-
07   David         7897.78 New York
06   Linda         4322.78
                ———- **********
                  12220.56 sum

01   Jason         1234.56 Toronto
                ———- **********
                   1234.56 sum

05   Robert        2334.78 Vancouver
08   James         1232.78
03   James         6544.78
02   Alison        6661.78
04   Celia         2344.78
                ———- **********
                   19118.9 sum

8 rows selected.

SQL> CLEAR BREAKS
breaks cleared
SQL> CLEAR COMPUTES
computes cleared
SQL> CLEAR COLUMNS
columns cleared
SQL> SET verify on
SQL> SET echo on
SQL>
SQL>
SQL> – clean the table
SQL> drop table Employee
  2  /

Table dropped.

SQL>
SQL>
SQL>
SQL>
           
       

Posted in Uncategorized | No Comments »