Oracle PL/SQL/Analytical Functions/Cumulative Sum

Материал из SQL эксперт
Перейти к: навигация, поиск

Add an analytic order by clause

   <source lang="sql">

SQL> SQL> create table ord(

 2           order_no               integer          primary key
 3          ,cust_no                integer
 4          ,order_date             date not null
 5          ,total_order_price      number(7,2)
 6          ,deliver_date           date
 7          ,deliver_time           varchar2(7)
 8          ,payment_method         varchar2(2)
 9          ,emp_no                 number(3,0)
10          ,deliver_name           varchar2(35)
11          ,gift_message           varchar2(100)
12  );

Table created. SQL> SQL> SQL> insert into ord(order_no,cust_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)

 2           values(1,1,"14-Feb-2002", 23.00, "14-Feb-2002", "12 noon", "CA",1, null, "Gift for wife");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(2,1,"14-Feb-2003", 510.98, "14-feb-2003", "5 pm", "NY",7, "Rose Ted", "Happy Valentines Day to Mother");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(3, 2,"14-Feb-2004", 315.99, "14-feb-2004", "3 pm", "VS",2, "Ani Forest", "Happy Valentines Day to Father");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(4, 2,"14-Feb-1999", 191.95, "14-feb-1999", "2 pm", "NJ",2, "O. John", "Happy Valentines Day");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(5, 6,"4-mar-2002", 101.95, "5-mar-2002", "2:30 pm", "MO"   , 2, "Cora", "Happy Birthday from John");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(6, 9,"7-apr-2003", 221.95, "7-apr-2003", "3 pm", "MA", 2, "Sake Keith", "Happy Birthday from Joe" );

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(7, 9,"20-jun-2004", 315.95, "21-jun-2004", "12 noon", "BC", 2, "Jessica Li", "Happy Birthday from Jessica");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values (8, 12, "31-dec-1999", 135.95, "1-jan-2000", "12 noon", "DI",      3, "Larry", "Happy New Year from Lawrence");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values (9, 12, "26-dec-2003", 715.95, "2-jan-2004", "12 noon", "SK",7, "Did", "Happy Birthday from Nancy" );

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(10, 4, sysdate-1, 119.95, sysdate+2, "6:30 pm", "VG",2, "P. Jing", "Happy Valentines Day to Jason");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(11, 2, sysdate, 310.00, sysdate+2, "3:30 pm", "DC",2, "C. Late", "Happy Birthday Day to Jack");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)

 2           values(12, 7, sysdate-3, 121.95, sysdate-2, "1:30 pm", "AC",2, "W. Last", "Happy Birthday Day to You");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)

 2           values(13, 7, sysdate, 211.95, sysdate-4, "4:30 pm", "CA",2, "J. Bond", "Thanks for hard working");

1 row created. SQL> SQL> SQL> select order_no, trunc(order_Date), total_order_price,

 2       sum(total_order_price) over (partition by trunc(order_date)
 3                                    order by order_no ) cume_top
 4  from ord
 5  order by order_date
 6  /
 ORDER_NO TRUNC(ORDER_DATE)    TOTAL_ORDER_PRICE   CUME_TOP

-------------------- ----------------- ----------
        4 14-FEB-1999 00:00:00            191.95     191.95
        8 31-DEC-1999 00:00:00            135.95     135.95
        1 14-FEB-2002 00:00:00                23         23
        5 04-MAR-2002 00:00:00            101.95     101.95
        2 14-FEB-2003 00:00:00            510.98     510.98
        6 07-APR-2003 00:00:00            221.95     221.95
        9 26-DEC-2003 00:00:00            715.95     715.95
        3 14-FEB-2004 00:00:00            315.99     315.99
        7 20-JUN-2004 00:00:00            315.95     315.95
       12 13-JUN-2008 00:00:00            121.95     121.95
       10 15-JUN-2008 00:00:00            119.95     119.95
       11 16-JUN-2008 00:00:00               310        310
       13 16-JUN-2008 00:00:00            211.95     521.95

13 rows selected. SQL> SQL> drop table ord; Table dropped. SQL> SQL> --

</source>
   
  


cumulative total_order_price for all orders.

   <source lang="sql">

SQL> SQL> SQL> create table ord(

 2           order_no               integer          primary key
 3          ,cust_no                integer
 4          ,order_date             date not null
 5          ,total_order_price      number(7,2)
 6          ,deliver_date           date
 7          ,deliver_time           varchar2(7)
 8          ,payment_method         varchar2(2)
 9          ,emp_no                 number(3,0)
10          ,deliver_name           varchar2(35)
11          ,gift_message           varchar2(100)
12  );

Table created. SQL> SQL> SQL> insert into ord(order_no,cust_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)

 2           values(1,1,"14-Feb-2002", 23.00, "14-Feb-2002", "12 noon", "CA",1, null, "Gift for wife");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(2,1,"14-Feb-2003", 510.98, "14-feb-2003", "5 pm", "NY",7, "Rose Ted", "Happy Valentines Day to Mother");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(3, 2,"14-Feb-2004", 315.99, "14-feb-2004", "3 pm", "VS",2, "Ani Forest", "Happy Valentines Day to Father");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(4, 2,"14-Feb-1999", 191.95, "14-feb-1999", "2 pm", "NJ",2, "O. John", "Happy Valentines Day");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(5, 6,"4-mar-2002", 101.95, "5-mar-2002", "2:30 pm", "MO"   , 2, "Cora", "Happy Birthday from John");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(6, 9,"7-apr-2003", 221.95, "7-apr-2003", "3 pm", "MA", 2, "Sake Keith", "Happy Birthday from Joe" );

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(7, 9,"20-jun-2004", 315.95, "21-jun-2004", "12 noon", "BC", 2, "Jessica Li", "Happy Birthday from Jessica");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values (8, 12, "31-dec-1999", 135.95, "1-jan-2000", "12 noon", "DI",      3, "Larry", "Happy New Year from Lawrence");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values (9, 12, "26-dec-2003", 715.95, "2-jan-2004", "12 noon", "SK",7, "Did", "Happy Birthday from Nancy" );

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(10, 4, sysdate-1, 119.95, sysdate+2, "6:30 pm", "VG",2, "P. Jing", "Happy Valentines Day to Jason");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(11, 2, sysdate, 310.00, sysdate+2, "3:30 pm", "DC",2, "C. Late", "Happy Birthday Day to Jack");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)

 2           values(12, 7, sysdate-3, 121.95, sysdate-2, "1:30 pm", "AC",2, "W. Last", "Happy Birthday Day to You");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)

 2           values(13, 7, sysdate, 211.95, sysdate-4, "4:30 pm", "CA",2, "J. Bond", "Thanks for hard working");

1 row created. SQL> SQL> SQL> SQL> select order_no, order_Date, total_order_price,

 2       sum(total_order_price) over (order by order_no) cume_top
 3  from ord
 4  order by order_no
 5  /
 ORDER_NO ORDER_DATE           TOTAL_ORDER_PRICE   CUME_TOP

-------------------- ----------------- ----------
        1 14-FEB-2002 00:00:00                23         23
        2 14-FEB-2003 00:00:00            510.98     533.98
        3 14-FEB-2004 00:00:00            315.99     849.97
        4 14-FEB-1999 00:00:00            191.95    1041.92
        5 04-MAR-2002 00:00:00            101.95    1143.87
        6 07-APR-2003 00:00:00            221.95    1365.82
        7 20-JUN-2004 00:00:00            315.95    1681.77
        8 31-DEC-1999 00:00:00            135.95    1817.72
        9 26-DEC-2003 00:00:00            715.95    2533.67
       10 15-JUN-2008 18:00:10            119.95    2653.62
       11 16-JUN-2008 18:00:11               310    2963.62
       12 13-JUN-2008 18:00:12            121.95    3085.57
       13 16-JUN-2008 18:00:13            211.95    3297.52

13 rows selected. SQL> SQL> drop table ord; Table dropped. SQL> --

</source>
   
  


Display a cumulative total_order_price for all orders(Partition by the truncated order_date

   <source lang="sql">

SQL> SQL> SQL> create table ord(

 2           order_no               integer          primary key
 3          ,cust_no                integer
 4          ,order_date             date not null
 5          ,total_order_price      number(7,2)
 6          ,deliver_date           date
 7          ,deliver_time           varchar2(7)
 8          ,payment_method         varchar2(2)
 9          ,emp_no                 number(3,0)
10          ,deliver_name           varchar2(35)
11          ,gift_message           varchar2(100)
12  );

Table created. SQL> SQL> SQL> insert into ord(order_no,cust_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)

 2           values(1,1,"14-Feb-2002", 23.00, "14-Feb-2002", "12 noon", "CA",1, null, "Gift for wife");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(2,1,"14-Feb-2003", 510.98, "14-feb-2003", "5 pm", "NY",7, "Rose Ted", "Happy Valentines Day to Mother");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(3, 2,"14-Feb-2004", 315.99, "14-feb-2004", "3 pm", "VS",2, "Ani Forest", "Happy Valentines Day to Father");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(4, 2,"14-Feb-1999", 191.95, "14-feb-1999", "2 pm", "NJ",2, "O. John", "Happy Valentines Day");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(5, 6,"4-mar-2002", 101.95, "5-mar-2002", "2:30 pm", "MO"   , 2, "Cora", "Happy Birthday from John");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(6, 9,"7-apr-2003", 221.95, "7-apr-2003", "3 pm", "MA", 2, "Sake Keith", "Happy Birthday from Joe" );

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(7, 9,"20-jun-2004", 315.95, "21-jun-2004", "12 noon", "BC", 2, "Jessica Li", "Happy Birthday from Jessica");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values (8, 12, "31-dec-1999", 135.95, "1-jan-2000", "12 noon", "DI",      3, "Larry", "Happy New Year from Lawrence");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values (9, 12, "26-dec-2003", 715.95, "2-jan-2004", "12 noon", "SK",7, "Did", "Happy Birthday from Nancy" );

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(10, 4, sysdate-1, 119.95, sysdate+2, "6:30 pm", "VG",2, "P. Jing", "Happy Valentines Day to Jason");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(11, 2, sysdate, 310.00, sysdate+2, "3:30 pm", "DC",2, "C. Late", "Happy Birthday Day to Jack");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)

 2           values(12, 7, sysdate-3, 121.95, sysdate-2, "1:30 pm", "AC",2, "W. Last", "Happy Birthday Day to You");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)

 2           values(13, 7, sysdate, 211.95, sysdate-4, "4:30 pm", "CA",2, "J. Bond", "Thanks for hard working");

1 row created. SQL> SQL> SQL> SQL> select order_no, trunc(order_Date), total_order_price,

 2       sum(total_order_price) over (partition by trunc(order_date) ) cume_top
 3  from ord
 4  order by order_date
 5  /
 ORDER_NO TRUNC(ORDER_DATE)    TOTAL_ORDER_PRICE   CUME_TOP

-------------------- ----------------- ----------
        4 14-FEB-1999 00:00:00            191.95     191.95
        8 31-DEC-1999 00:00:00            135.95     135.95
        1 14-FEB-2002 00:00:00                23         23
        5 04-MAR-2002 00:00:00            101.95     101.95
        2 14-FEB-2003 00:00:00            510.98     510.98
        6 07-APR-2003 00:00:00            221.95     221.95
        9 26-DEC-2003 00:00:00            715.95     715.95
        3 14-FEB-2004 00:00:00            315.99     315.99
        7 20-JUN-2004 00:00:00            315.95     315.95
       12 13-JUN-2008 00:00:00            121.95     121.95
       10 15-JUN-2008 00:00:00            119.95     119.95
       11 16-JUN-2008 00:00:00               310     521.95
       13 16-JUN-2008 00:00:00            211.95     521.95

13 rows selected. SQL> SQL> drop table ord; Table dropped. SQL> SQL> --

</source>
   
  


Display cumulative total_order_price and cumulative by customer

   <source lang="sql">

SQL> SQL> create table ord(

 2           order_no               integer          primary key
 3          ,cust_no                integer
 4          ,order_date             date not null
 5          ,total_order_price      number(7,2)
 6          ,deliver_date           date
 7          ,deliver_time           varchar2(7)
 8          ,payment_method         varchar2(2)
 9          ,emp_no                 number(3,0)
10          ,deliver_name           varchar2(35)
11          ,gift_message           varchar2(100)
12  );

Table created. SQL> SQL> SQL> insert into ord(order_no,cust_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)

 2           values(1,1,"14-Feb-2002", 23.00, "14-Feb-2002", "12 noon", "CA",1, null, "Gift for wife");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(2,1,"14-Feb-2003", 510.98, "14-feb-2003", "5 pm", "NY",7, "Rose Ted", "Happy Valentines Day to Mother");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(3, 2,"14-Feb-2004", 315.99, "14-feb-2004", "3 pm", "VS",2, "Ani Forest", "Happy Valentines Day to Father");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(4, 2,"14-Feb-1999", 191.95, "14-feb-1999", "2 pm", "NJ",2, "O. John", "Happy Valentines Day");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(5, 6,"4-mar-2002", 101.95, "5-mar-2002", "2:30 pm", "MO"   , 2, "Cora", "Happy Birthday from John");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(6, 9,"7-apr-2003", 221.95, "7-apr-2003", "3 pm", "MA", 2, "Sake Keith", "Happy Birthday from Joe" );

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(7, 9,"20-jun-2004", 315.95, "21-jun-2004", "12 noon", "BC", 2, "Jessica Li", "Happy Birthday from Jessica");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values (8, 12, "31-dec-1999", 135.95, "1-jan-2000", "12 noon", "DI",      3, "Larry", "Happy New Year from Lawrence");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values (9, 12, "26-dec-2003", 715.95, "2-jan-2004", "12 noon", "SK",7, "Did", "Happy Birthday from Nancy" );

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(10, 4, sysdate-1, 119.95, sysdate+2, "6:30 pm", "VG",2, "P. Jing", "Happy Valentines Day to Jason");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(11, 2, sysdate, 310.00, sysdate+2, "3:30 pm", "DC",2, "C. Late", "Happy Birthday Day to Jack");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)

 2           values(12, 7, sysdate-3, 121.95, sysdate-2, "1:30 pm", "AC",2, "W. Last", "Happy Birthday Day to You");

1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)

 2           values(13, 7, sysdate, 211.95, sysdate-4, "4:30 pm", "CA",2, "J. Bond", "Thanks for hard working");

1 row created. SQL> SQL> SQL> SQL> select order_no, cust_no, total_order_price,

 2  sum(total_order_price) over (partition by cust_no order by order_no) cust_total,
 3  sum(total_order_price) over (order by cust_no, order_no) cume_total
 4  from ord
 5  order by cust_no
 6  /
 ORDER_NO    CUST_NO TOTAL_ORDER_PRICE CUST_TOTAL CUME_TOTAL

---------- ----------------- ---------- ----------
        1          1                23         23         23
        2          1            510.98     533.98     533.98
        3          2            315.99     315.99     849.97
        4          2            191.95     507.94    1041.92
       11          2               310     817.94    1351.92
       10          4            119.95     119.95    1471.87
        5          6            101.95     101.95    1573.82
       12          7            121.95     121.95    1695.77
       13          7            211.95      333.9    1907.72
        6          9            221.95     221.95    2129.67
        7          9            315.95      537.9    2445.62
        8         12            135.95     135.95    2581.57
        9         12            715.95      851.9    3297.52

13 rows selected. SQL> SQL> drop table ord; Table dropped. SQL> SQL> --

</source>
   
  


Performing a Cumulative Sum

   <source lang="sql">

SQL> CREATE TABLE all_sales (

 2    year INTEGER,
 3    month INTEGER,
 4    prd_type_id INTEGER,
 5    emp_id INTEGER ,
 6    amount NUMBER(8, 2)
 7  );

Table created. SQL> SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,1    ,1          ,21    ,16034.84);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,2    ,1          ,21    ,15644.65);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,3    ,2          ,21    ,20167.83);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,4    ,2          ,21    ,25056.45);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,5    ,2          ,21    ,NULL);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,6    ,1          ,21    ,15564.66);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,7    ,1          ,21    ,15644.65);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,8    ,1          ,21    ,16434.82);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,9    ,1          ,21    ,19654.57);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,10   ,1          ,21    ,21764.19);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,11   ,1          ,21    ,13026.73);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,12   ,2          ,21    ,10034.64);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,1    ,2          ,22    ,16634.84);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,1    ,2          ,21    ,26034.84);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,2    ,1          ,21    ,12644.65);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,3    ,1          ,21    ,NULL);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,4    ,1          ,21    ,25026.45);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,5    ,1          ,21    ,17212.66);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,6    ,1          ,21    ,15564.26);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,7    ,2          ,21    ,62654.82);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,8    ,2          ,21    ,26434.82);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,9    ,2          ,21    ,15644.65);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,10   ,2          ,21    ,21264.19);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,11   ,1          ,21    ,13026.73);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,12   ,1          ,21    ,10032.64);

1 row created. SQL> SQL> select * from all_sales;

     YEAR      MONTH PRD_TYPE_ID     EMP_ID     AMOUNT

---------- ----------- ---------- ----------
     2006          1           1         21   16034.84
     2006          2           1         21   15644.65
     2006          3           2         21   20167.83
     2006          4           2         21   25056.45
     2006          5           2         21
     2006          6           1         21   15564.66
     2006          7           1         21   15644.65
     2006          8           1         21   16434.82
     2006          9           1         21   19654.57
     2006         10           1         21   21764.19
     2006         11           1         21   13026.73
     2006         12           2         21   10034.64
     2005          1           2         22   16634.84
     2005          1           2         21   26034.84
     2005          2           1         21   12644.65
     2005          3           1         21
     2005          4           1         21   25026.45
     2005          5           1         21   17212.66
     2005          6           1         21   15564.26
     2005          7           2         21   62654.82
     2005          8           2         21   26434.82
     2005          9           2         21   15644.65
     2005         10           2         21   21264.19
     2005         11           1         21   13026.73
     2005         12           1         21   10032.64

25 rows selected. SQL> SQL> --Performing a Cumulative Sum SQL> SQL> SELECT

 2   month, SUM(amount) AS month_amount,
 3   SUM(SUM(amount)) OVER
 4    (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
 5    AS cumulative_amount
 6  FROM all_sales
 7  GROUP BY month
 8  ORDER BY month;
    MONTH MONTH_AMOUNT CUMULATIVE_AMOUNT

------------ -----------------
        1     58704.52          58704.52
        2      28289.3          86993.82
        3     20167.83         107161.65
        4      50082.9         157244.55
        5     17212.66         174457.21
        6     31128.92         205586.13
        7     78299.47          283885.6
        8     42869.64         326755.24
        9     35299.22         362054.46
       10     43028.38         405082.84
       11     26053.46          431136.3
       12     20067.28         451203.58

12 rows selected. SQL> SQL> SQL> drop table all_sales; Table dropped. SQL> SQL>

      </source>
   
  


Sum over and order by

   <source lang="sql">

SQL> SQL> create table employee(

 2           emp_no                 integer         primary key
 3          ,lastname               varchar2(20)    not null
 4          ,firstname              varchar2(15)    not null
 5          ,midinit                varchar2(1)
 6          ,street                 varchar2(30)
 7          ,city                   varchar2(20)
 8          ,state                  varchar2(2)
 9          ,zip                    varchar2(5)
10          ,zip_4                  varchar2(4)
11          ,area_code              varchar2(3)
12          ,phone                  varchar2(8)
13          ,salary                 number(3)
14          ,birthdate              date
15          ,hiredate               date
16          ,title                  varchar2(20)
17          ,dept_no                integer
18          ,mgr                    integer
19          ,region                 number
20          ,division               number
21          ,total_sales            number
22  );

Table created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)

 2               values (1,"Anderson","Nancy","N","33 Ave","London","NY","11111","1111","212","234-1111",3.75,"21-mar-1927","1-feb-1947","Sales Manager",2,null,100,10,40000);

1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)

 2               values (2,"Last","First","F","12 Ave","Paris","CA","22222","2222","221","867-2222",7.75,"14-feb-1976","15-mar-1985","Sales Clerk",2,1,100,10,10000);

1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)

 2               values (3,"Wash","Georgia","G","1 Street14","Barton","NJ","33333","3333","214","340-3333",11.50,"2-jul-1977","21-apr-2004","Designer",1,2,100,10,40000);

1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)

 2               values (4,"Bush","Dave","D","56 Street","Island","RI","44444","4444","215","777-4444",21.65,"15-may-1945","2-aug-1975","Designer",1,2,100,10,40000);

1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)

 2               values (5,"Will","Robin","W","56 Street","Island","MA","55555","5555","216","777-5555",24.65,"10-dec-1980","2-aug-2007","Designer",1,5,100,10,40000);

1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)

 2               values (6,"Pete","Mona","M","13 Ave","York","MO","66666","6666","217","111-6666",9,"14-feb-1966","15-mar-1985","Sales Clerk",2,5,100,10,40000);

1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)

 2               values (7,"Roke","John","J","67 Ave","New York","BC","77777","7777","218","122-7777",10.00,"14-jun-1955","15-mar-1975","Accountant",3,2,100,10,40000);

1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)

 2               values (8,"Horry","Tedi","T","1236 Lane","Newton","NY","88888","8888","219","222-8888",13.00,"10-jun-1955","15-aug-1985","Sales Representative",3,2,100,10,50000);

1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)

 2                values (9,"Bar","Candi","C","400 East Street","Yorken","NY","99999","9999","220","321-9999",12.00,"10-oct-1933","15-jan-1969","Sales Representative",3,5,100,10,35000);

1 row created. SQL> SQL> select lastname , dept_no, salary,

 2       sum(salary) over (order by dept_no, lastname) running_total
 3      ,sum(salary) over (partition by dept_no order by lastname) dept_total
 4  from employee
 5  order by dept_no, lastname;

LASTNAME DEPT_NO SALARY RUNNING_TOTAL DEPT_TOTAL


---------- ---------- ------------- ----------

Bush 1 22 22 22 Wash 1 12 34 34 Will 1 25 59 59 LASTNAME DEPT_NO SALARY RUNNING_TOTAL DEPT_TOTAL


---------- ---------- ------------- ----------

Anderson 2 4 63 4 Last 2 8 71 12 Pete 2 9 80 21 Bar 3 12 92 12 Horry 3 13 105 25 Roke 3 10 115 35 9 rows selected. SQL> SQL> drop table employee; Table dropped. SQL> --

</source>
   
  


Sum over partition demo

   <source lang="sql">

SQL> SQL> create table employee(

 2           emp_no                 integer         primary key
 3          ,lastname               varchar2(20)    not null
 4          ,firstname              varchar2(15)    not null
 5          ,midinit                varchar2(1)
 6          ,street                 varchar2(30)
 7          ,city                   varchar2(20)
 8          ,state                  varchar2(2)
 9          ,zip                    varchar2(5)
10          ,zip_4                  varchar2(4)
11          ,area_code              varchar2(3)
12          ,phone                  varchar2(8)
13          ,salary                 number(3)
14          ,birthdate              date
15          ,hiredate               date
16          ,title                  varchar2(20)
17          ,dept_no                integer
18          ,mgr                    integer
19          ,region                 number
20          ,division               number
21          ,total_sales            number
22  );

Table created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)

 2               values (1,"Anderson","Nancy","N","33 Ave","London","NY","11111","1111","212","234-1111",3.75,"21-mar-1927","1-feb-1947","Sales Manager",2,null,100,10,40000);

1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)

 2               values (2,"Last","First","F","12 Ave","Paris","CA","22222","2222","221","867-2222",7.75,"14-feb-1976","15-mar-1985","Sales Clerk",2,1,100,10,10000);

1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)

 2               values (3,"Wash","Georgia","G","1 Street14","Barton","NJ","33333","3333","214","340-3333",11.50,"2-jul-1977","21-apr-2004","Designer",1,2,100,10,40000);

1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)

 2               values (4,"Bush","Dave","D","56 Street","Island","RI","44444","4444","215","777-4444",21.65,"15-may-1945","2-aug-1975","Designer",1,2,100,10,40000);

1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)

 2               values (5,"Will","Robin","W","56 Street","Island","MA","55555","5555","216","777-5555",24.65,"10-dec-1980","2-aug-2007","Designer",1,5,100,10,40000);

1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)

 2               values (6,"Pete","Mona","M","13 Ave","York","MO","66666","6666","217","111-6666",9,"14-feb-1966","15-mar-1985","Sales Clerk",2,5,100,10,40000);

1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)

 2               values (7,"Roke","John","J","67 Ave","New York","BC","77777","7777","218","122-7777",10.00,"14-jun-1955","15-mar-1975","Accountant",3,2,100,10,40000);

1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)

 2               values (8,"Horry","Tedi","T","1236 Lane","Newton","NY","88888","8888","219","222-8888",13.00,"10-jun-1955","15-aug-1985","Sales Representative",3,2,100,10,50000);

1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)

 2                values (9,"Bar","Candi","C","400 East Street","Yorken","NY","99999","9999","220","321-9999",12.00,"10-oct-1933","15-jan-1969","Sales Representative",3,5,100,10,35000);

1 row created. SQL> SQL> SQL> select lastname , dept_no, salary,

 2      sum(salary) over (partition by dept_no order by lastname) dept_total
 3  from employee
 4  order by salary, lastname;

LASTNAME DEPT_NO SALARY DEPT_TOTAL


---------- ---------- ----------

Anderson 2 4 4 Last 2 8 12 Pete 2 9 21 Roke 3 10 35 Bar 3 12 12 Wash 1 12 34 Horry 3 13 25 Bush 1 22 22 Will 1 25 59 9 rows selected. SQL> SQL> drop table employee; Table dropped. SQL> SQL> --

</source>
   
  


The use of ROWS UNBOUNDED PRECEDING to implicitly indicate the end of the window is

   <source lang="sql">

SQL> CREATE TABLE all_sales (

 2    year INTEGER,
 3    month INTEGER,
 4    prd_type_id INTEGER,
 5    emp_id INTEGER ,
 6    amount NUMBER(8, 2)
 7  );

Table created. SQL> SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,1    ,1          ,21    ,16034.84);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,2    ,1          ,21    ,15644.65);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,3    ,2          ,21    ,20167.83);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,4    ,2          ,21    ,25056.45);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,5    ,2          ,21    ,NULL);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,6    ,1          ,21    ,15564.66);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,7    ,1          ,21    ,15644.65);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,8    ,1          ,21    ,16434.82);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,9    ,1          ,21    ,19654.57);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,10   ,1          ,21    ,21764.19);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,11   ,1          ,21    ,13026.73);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,12   ,2          ,21    ,10034.64);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,1    ,2          ,22    ,16634.84);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,1    ,2          ,21    ,26034.84);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,2    ,1          ,21    ,12644.65);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,3    ,1          ,21    ,NULL);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,4    ,1          ,21    ,25026.45);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,5    ,1          ,21    ,17212.66);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,6    ,1          ,21    ,15564.26);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,7    ,2          ,21    ,62654.82);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,8    ,2          ,21    ,26434.82);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,9    ,2          ,21    ,15644.65);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,10   ,2          ,21    ,21264.19);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,11   ,1          ,21    ,13026.73);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,12   ,1          ,21    ,10032.64);

1 row created. SQL> SQL> select * from all_sales;

     YEAR      MONTH PRD_TYPE_ID     EMP_ID     AMOUNT

---------- ----------- ---------- ----------
     2006          1           1         21   16034.84
     2006          2           1         21   15644.65
     2006          3           2         21   20167.83
     2006          4           2         21   25056.45
     2006          5           2         21
     2006          6           1         21   15564.66
     2006          7           1         21   15644.65
     2006          8           1         21   16434.82
     2006          9           1         21   19654.57
     2006         10           1         21   21764.19
     2006         11           1         21   13026.73
     2006         12           2         21   10034.64
     2005          1           2         22   16634.84
     2005          1           2         21   26034.84
     2005          2           1         21   12644.65
     2005          3           1         21
     2005          4           1         21   25026.45
     2005          5           1         21   17212.66
     2005          6           1         21   15564.26
     2005          7           2         21   62654.82
     2005          8           2         21   26434.82
     2005          9           2         21   15644.65
     2005         10           2         21   21264.19
     2005         11           1         21   13026.73
     2005         12           1         21   10032.64

25 rows selected. SQL> SQL> --The use of ROWS UNBOUNDED PRECEDING to implicitly indicate the end of the window is the current row: SQL> SQL> SELECT

 2   month, SUM(amount) AS month_amount,
 3   SUM(SUM(amount)) OVER (ORDER BY month ROWS UNBOUNDED PRECEDING) AS
 4    cumulative_amount
 5  FROM all_sales
 6  WHERE month BETWEEN 6 AND 12
 7  GROUP BY month
 8  ORDER BY month;
    MONTH MONTH_AMOUNT CUMULATIVE_AMOUNT

------------ -----------------
        6     31128.92          31128.92
        7     78299.47         109428.39
        8     42869.64         152298.03
        9     35299.22         187597.25
       10     43028.38         230625.63
       11     26053.46         256679.09
       12     20067.28         276746.37

7 rows selected. SQL> SQL> SQL> drop table all_sales; Table dropped. SQL>

      </source>