随笔档案

create table dept(
       deptno  number(2) not null,
       dname  varchar2(14),
       loc varchar2(13)

)

create table emp(
       empno number(4) not null,
       ename varchar2(10),
       job varchar2(9),
       mgr number(4),
       hiredate date,
       sal number(7,2),
       comm number(7,2),
       depno number(2)
)

insert into dept(deptno,dname,loc) values(10,'ACCOUNTING','NEW YORK');
insert into dept(deptno,dname,loc) values(20,'RESERCH','DALLAS');
insert into dept(deptno,dname,loc) values(30,'SALES','CHICAGO');
insert into dept(deptno,dname,loc) values(40,'OPERATIONS','BOSTON');

select * from dept;

select * from emp;

insert into emp values(1,'SMITH','CLERK',1,sysdate,800,100,20);
insert into emp values(2,'ALLEN','SALSMAN',5,sysdate,1600,200,30);
insert into emp values(3,'WARK','SALSMAN',5,sysdate,1250,100,30);
insert into emp values(4,'JONES','MANAGER',0,sysdate,2975,500,20);
insert into emp values(5,'BLAKE','MANAGER',0,sysdate,2850,500,30);
insert into emp values(6,'MARTIN','SALSMAN',4,sysdate,1250,100,20);
insert into emp values(7,'CLARK','MANAGER',0,sysdate,2450,500,10);
insert into emp values(8,'SCOTT','ANALYST',4,sysdate,3000,300,20);
insert into emp values(9,'KING','PRESIDENT',7,sysdate,5000,1000,10);
insert into emp values(10,'ADAMS','CLERK',4,sysdate,1100,100,20);
insert into emp values(11,'JAMES','CLERK',4,sysdate,950,100,20);
insert into emp values(12,'FORD','ANALYST',4,sysdate,3000,300,20);
insert into emp values(13,'MILLER','CLERK',4,sysdate,1300,100,20);
insert into emp values(14,'FL_MILLER','CLERK',4,sysdate,1300,100,20);
update emp set emp.mgr = 4 where emp.empno = 1;

select ename,to_char(hiredate,'YYYY-MM-DD') from emp;

select * from emp;

select ename as "姓名",sal*12 as "年收入" from emp;

select ename,sal,comm,sal+nvl(comm,0) as "月收入" from emp;

select ename||'is a '||job as "Employee Detail" from emp;

select ename,sal from emp where sal > 2000;

select * from emp where lower(ename) = 'scott';

select * from emp where hiredate > to_date('2009-08-14','YYYY-MM-DD');

select * from emp where sal between 1000 and 2000;

select * from emp where ename like 'S%';

select * from emp where ename like '__A%';

select * from emp where ename like '%B_%' escape 'B';

select * from emp where sal in(800,1250);

update emp set mgr = null where empno = 1;

select * from emp where mgr is null;

select * from emp where ename = 'SMITH' and depno = 20;

select * from emp where sal > 2500 or job = 'MANAGER';

select * from emp where comm is not null;

select * from emp  order by mgr ;

select ename,sal*12 as "全年工资" from emp order by 2 desc;

create table employee(
       empno number(4) not null,
       ename varchar2(10),
       job varchar2(9),
       mgr number(4),
       hiredate date,
       sal number(7,2),
       comm number(7,2),
       depno number(2)
)

insert into employee(empno,ename,sal,depno) select empno,ename,sal,depno from emp where depno = 20;

select * from employee;

create table dep10(
       empno number(4) not null,
       ename varchar2(10),
       job varchar2(9),
       mgr number(4),
       hiredate date,
       sal number(7,2),
       comm number(7,2),
       depno number(2)
);
create table dep20(
       empno number(4) not null,
       ename varchar2(10),
       job varchar2(9),
       mgr number(4),
       hiredate date,
       sal number(7,2),
       comm number(7,2),
       depno number(2)
);
create table dep30(
       empno number(4) not null,
       ename varchar2(10),
       job varchar2(9),
       mgr number(4),
       hiredate date,
       sal number(7,2),
       comm number(7,2),
       depno number(2)
);
create table clerk(
       empno number(4) not null,
       ename varchar2(10),
       job varchar2(9),
       mgr number(4),
       hiredate date,
       sal number(7,2),
       comm number(7,2),
       depno number(2)
);
create table other(
       empno number(4) not null,
       ename varchar2(10),
       job varchar2(9),
       mgr number(4),
       hiredate date,
       sal number(7,2),
       comm number(7,2),
       depno number(2)
);
insert first
             when job = 'CLERK' then into clerk
            
             select * from emp;
            
            
select * from dep10;
select * from dep20;
select * from dep30;
select * from clerk;
select * from other;
select * from emp where ename = 'SCOTT';
select * from emp where ename = 'SMITH';

update emp set(job,sal,comm) = (select job,sal,comm from emp where ename = 'SMITH') where ename = 'SCOTT'