oracle练习

--一
--1.创建表空间BankTS
create tablespace BankTS datafile 'f:\BankTS\BankTS.dbf' size 200M;  
--2.创建表、自增长主键、建立主外键、约束、建立UserInfo、CardInfo表测试数据
CREATE TABLE UserInfo  --用户信息表
(
  CustomerId  number NOT NULL,
  CustomerName varCHAR2(20) NOT NULL,
  PID varCHAR(18) NOT NULL,
  Telephone varCHAR(20) NOT NULL,
  Address VARCHAR(50)
);
alter table UserInfo add constraint PK_custemerId primary key(CustomerId)
 add constraint UK_PID UNIQUE(PID)
 add constraint CK_PID CHECK(LENGTH(PID)=18 OR LENGTH(PID)=15)
;
--三
--1.查询薪水不在1300到1600之间的员工
select * from emp where sal not BETWEEN 1300 and 1600;
--2.查询姓名以大写字母S开头的员工
SELECT * from emp where ename like 'S%';
--3.查询无佣金且工资大于1500的员工
SELECT * from emp where sal>1500 and comm is null;
--4.显示昨天，今天，明天的日期
select sysdate-1,sysdate,sysdate+1 from dual;
--5.查询1980年12月17日入职的员工
select * from emp where to_char(HIREDATE,'yyyy-MM-dd')='1980-12-17';
--6.按部门求出该部门平均工资，且平均工资大于2000元的部门，按部门平均工资降序排列
select DEPTNO,avg(sal) from emp  group by DEPTNO having avg(sal)>2000 order by avg(sal) desc;
--7.显示emp表中3-8条记录
select ROWNUM,emp.* from emp where ROWNUM<=8 MINUS select ROWNUM,emp.* from emp where ROWNUM<=2;
--8.查询员工表中工资最高的前3名
select rownum,t.EMPNO,t.ENAME,t.SAL from (select * from emp order by sal desc) t where rownum < 4;
--9.查询员工表中薪水大于本部门平均薪水的员工，输出格式
select e.EMPNO,e.ENAME,e.sal,t.AVGSAL from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) t where e.DEPTNO=t.DEPTNO and e.sal>t.avgsal;
--10.统计每年入职的员工个数，输出格式
 select count(*) Total,
         sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980",
         sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981",
         sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982",
        sum(decode(to_char(hiredate,'yyyy'),'1987',1,0)) "1987"
 from emp;

 

显示emp表中3-8条记录的三种方法：

（1）显示emp表中3-8条记录（方式一：使用集合减运算）

select rownum "伪列",emp.* from emp where rownum<=8
minus

select rownum,emp.* from emp where rownum<=2;

（2）显示emp表中3-8条记录（方式二：使用子查询，在from子句中使用）
select xx.*
from (select rownum ids,emp.* from emp where rownum<=8) xx 

where ids>2;

（3）显示emp表中3-8条记录（方式三：使用子查询，在from子句中使用）

select xx.*

from (select rownum ids,emp.* from emp where rownum!=9) xx 

where ids>2;

 

求5-10数据 EMP表
SELECT ROWNUM , A.* FROM ( 
    SELECT ROWNUM r,EMP.* FROM EMP     WHERE ROWNUM <= 10 
    ) A WHERE r > 5
    
求工资排名在5-10的员工
SELECT * FROM (
SELECT ROWNUM r , A.* FROM ( 
    SELECT EMP.* FROM EMP
        WHERE SAL IS NOT NULL ORDER BY SAL DESC
    ) A ) WHERE r BETWEEN 5 AND 10