Oracle创建表,修改表的结构

SQL> create table tb_sale(
  2  id number(10) primary key,
  3  saleDate date not null,
  4  sale number(10) not null);

SQL> select a.saleDate,a.sale,sum(a.sale) over(order by a.saledate) sumSale from tb_sale a ;
 
SALEDATE           SALE    SUMSALE
----------- ----------- ----------
2011-1-11           100        100
2011-1-12           100        200
2011-1-14           200        400
2011-1-15           200        600
 

 
SQL> create table sales(id number(10) primary key,empNo varchar2(10) not null,shopId varchar2(10) not null, sales number(10) not null, saleYear number(4) not null);
insert into sales values(1,'emp001',1,100,2000);
insert into sales values(2,'emp001',2,300,2002);
insert into sales values(3,'emp002',3,300,2004);
insert into sales values(4,'emp003',4,300,2002);
insert into sales values(5,'emp003',5,300,2008);
insert into sales values(7,'emp005',7,2200,2010);
insert into sales values(8,'emp005',7,2000,2010);
insert into sales values(9,'emp004',7,2000,2009);
 insert into sales values(10,'emp003',6,2000,2010);
阶段一：
select a.empNo,a.shopid,sum(a.sales) sumSales ,sum(sum(a.sales)) over(partition by a.shopid) shopSales from sales a group by a.empNo,a.shopId;
 
EMPNO      SHOPID       SUMSALES  SHOPSALES
---------- ---------- ---------- ----------
emp001     1                 100        100
emp001     2                 300        300
emp002     3                 300        300
emp003     4                 300        300
emp003     5                 300        300
emp003     6                2000       4200
emp006     6                2200       4200
emp004     7                2000       6200
emp005     7                4200       6200
 
9 rows selected

 
SQL>  create table stuScore(
  2  stuId number(10) primary key,
  3  stuName varchar2(20) not null,
  4   score number(10) not null);

 
SQL> insert into stuScore values(1000,'tudou',100);

 
SQL> truncate table stuScore;

 
SQL> alter table stuScore add maths number(3);

 
SQL> alter table stuScore add chinese number(3);

 
SQL> alter table stuScore add english number(3);

 
SQL> alter table stuScore drop column score;

SQL> alter table stuScore modify maths number(3) not null;
 

 
SQL> alter table stuScore modify chinese number(3) not null;
 

 
SQL> alter table stuScore modify english number(3) not null;
 

 
SQL> desc stuScore;
Name    Type         Nullable Default Comments
------- ------------ -------- ------- --------
STUID   NUMBER(10)                            
STUNAME VARCHAR2(20)                          
MATHS   NUMBER(3)                             
CHINESE NUMBER(3)                             
ENGLISH NUMBER(3)                             

SQL> insert into stuScore values(1,'tudou',100,100,100);

 
SQL> insert into stuScore values(2,'dou',90,70,100);

SQL> insert into stuScore values(3,'doudou',80,70,80);

SQL> insert into stuScore values(4,'doudoud',80,80,80);
 

SQL> insert into stuScore values(5,'doudoud',90,70,80);

SQL> insert into stuScore values(6,'doudoud',90,70,100);

SQL> insert into stuScore values(7,'li',100,100,100);
 

 
阶段二：
 
SQL> select a.stuName,(maths+english+chinese) sumScore,rank() over(order by (chinese+maths+english) desc) mingci   from stuScore a;
 
STUNAME                SUMSCORE     MINGCI
-------------------- ---------- ----------
tudou                       300          1
li                          300          1
dou                         260          3
doudoud                     260          3
doudoud                     240          5
doudoud                     240          5
doudou                      230          7
 
阶段三:
SQL> select a.ename,sum(sal), a.deptno from emp a group by rollup(a.deptno,a.ename);

阶段四：
select a.empNO ,a.ename ,lag(a.empNo) over(order by a.empNo) lag from emp a;

select decode(lag(deptNo) over(order by deptNo),deptNo,to_number(null),deptNo) dept ,ename from emp;
练习一：
select decode(lag(a.empNo) over(order by a.empNo),empNo,null,empNo) empNo,sum(a.sal) sumSale from emp a group by rollup(a.empNo);

练习二：
select a.empno,a.ename,a.sal,a.deptno,first_value(a.ename) over(partition by a.deptNo order by a.sal desc) ename from emp a where a.deptno in (20,30);