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);