sql.txt

一些sql的查询语句

select job,min(sal),avg(sal),max(sal)
from emp
where job not like 'PRESID%'
group by job 
having avg(sal)>(
                 select min(avg(sal))
                 from emp
                 group by job
                 );

select empno,ename,sal,job
from   emp
where  sal>any(
               select avg(sal)
               from emp
               group by job
               );
select empno, ename,sal,job
from   manager
where  (sal,job)in(
                  select max(sal),job
                  from  manager
                  group by job 
                  );


update manager
set sal=1300
where empno = 7521;

select e.empno,e.ename,e.sal,e.deptno,d.loc
from emp e join dept d on(e.deptno= d.deptno)
order by d.loc;

select e.empno,e.ename,e.sal,e.deptno,d.loc
from emp e,dept d
where e.deptno=d.deptno and e.sal>=1500
order by d.loc;

select empno,ename,sal,emp.deptno,loc
from emp,dept
where emp.deptno=dept.deptno
order by loc;

select job,avg(sal)
from emp
group by job
having avg(sal)>1500
order by 2;

select avg(comm),sum(comm),job,count(comm)
from emp
group by job;

select empno,ename,sal,job
from emp
where job=(select job from emp where ename='SMITH')
          and sal<=(select sal from emp where ename ='ADAMS');


select job,min(sal),avg(sal),max(sal)
from emp
where job not like 'PRESIO%'
group by job
having avg(sal)>(select MIN(avg(sal))
                from emp
                group by job
                );

select empno,ename,job,sal
from emp
where sal in(select max(sal)
             from emp
             group by job
             )
      and job<>'CLERK'
      and job not like 'PRES%';

select empno,ename,sal,job
from manager
where (sal,job)in(
                  select max(sal),job
                  from manager
                  group by job);

UPDATE emp
SET sal=sal*0.9;

CREATE TABLE product
  ( p_code NUMBER(6),
    p_name varchar(30),
    p_desc varchar(100),
    p_price number(5,12)
    
  )

CREATE TABLE worker
AS
SELECT empno,ename name,job,sal+NVL(comm,0) income
FROM emp
WHERE job NOT IN('MANAGER','PRESIDENT')
//在表中加入一个新的列
ALTER TABLE worker
ADD (hiredate DATE);

// 修改已经存在的列
ALTER TABLE worker
MODIFY (hiredate DEFAULT SYSDATE);

// 修改表结构
alter table worker
drop column hiredate;
// 把一列设为无用
alter table worker set unused (income);
// 取消无用设置
alter table worker drop unused columns;
// 为表加注释
comment on table worker
is 'asasasasasasasasa'
//删除表
drop table worker
// 利用子查询向表中插入数据
insert into sales(code,name,salary,commission) 
    select empno,ename,sal,comm
    From  emp
    where job like 'SAL%';

insert into emp(empno,ename,sal)
values (10,'sasa',2000)

// 利用多列子查询来修改表
update emp
set (job,sal) = (select job,sal from emp where empno = 7369)
where job = '保安'
// 删除表中的数据
delete from emp
where (job = 'MANAGER' and sal>2500)
      or (job = 'SALESMAN' and sal>1300)