创建了两张表和一些问题 , 基本上把oracle的基础语句都用上了, 适合新手练习与巩固知识. 可以自己在oracle中创建表然后练习.没有可视化界面的可以用我下面准备好的语句直接导入. EMP表 DEPT表 员工表建表语句: CREATE TABLE EMP ( 部门表建表语句: CREATE TABLE DEPT ( 员工表插入数据: INSERT INTO EMP VALUES ('Zhou润发', 10001, 10, '办事员', TO_DATE('20161130131322', 'YYYYMMDDHH24MISS'), 2500, 2400); 部门表插入数据: INSERT INTO DEPT VALUES ('市场部', 10, '北京'); 两张表已全部建好,自己用查询语句查看一下表结构和数据是否没问题. Select * From EMP Select * From DEPT 下面是题目:可以复制下来做, 做完再和我做答案对照一下, 当然, 有的题目可以用多种方法去做, 看你自己选择了. 还有一点需要注意的是:我是用的可视化界面输入的语句,别名中文不需要加引号,一开始我加引号老是报错,后来无意间去掉反而好了,不知道为什么.我开始学的时候,别名使用中文是需要加引号的,看你实际的情况吧!! 题目: --1.选择30部门的职员 答案: 1-- select ename,deptno from emp where deptno=30; select e.ename,e.empno,d.dname,e.job from emp e,dept d where e.deptno=d.deptno and e.job='办事员'; select * from emp where comm>sal order by comm desc; 4-- select * from emp where comm>sal*0.6; select * from emp e,dept d where e.deptno=10 and e.job='经理' and e.deptno=d.deptno select * from emp e,dept d where (e.deptno=10 and e.job='经理' and e.deptno=d.deptno) select * from emp e,dept d where e.sal>=2000 and e.job<>'办事员' and e.job<>'经理' and e.deptno=d.deptno; select distinct job from emp where comm>0; select ename,nvl(comm,0) comms from emp where nvl(comm,0)<2000; select * from emp where hiredate=last_day(hiredate); select * from emp where months_between(sysdate,hiredate)>12; select * from emp where hiredate<add_months(sysdate,-12); select * from emp where ename=initcap(ename); select * from emp where length(ename)=7; select * from emp where instr(ename,'Z')=0; select substr(ename,0,3) from emp select replace(ename,'Z','z') from emp select ename,add_months(hiredate,12) from emp select * from emp e,dept d where e.deptno=d.deptno order by e.ename select ename,trunc(months_between(sysdate,hiredate)/12,0) 服务年限 from emp order by 服务年限 desc select ename,hiredate from emp order by hiredate select ename,job,sal from emp order by job desc,sal asc select ename,extract(year from hiredate) 年份,extract(month from hiredate) 月份 from emp order by 年份,月份 select ename,to_char(hiredate,'yyyy') 年份,to_char(hiredate,'mm') 月份 from emp order by hiredate select ename,sal/30,trunc(sal/30,1),floor(sal/30),ceil(sal/30) from emp select * from emp where extract(month from hiredate)=1 select * from emp where to_char(hiredate,'mm')='01' select ename,floor(sysdate-hiredate) 入职天数 from emp order by 入职天数 desc select ename from emp where ename like '%i%'; select ename from emp where instr(ename,'i')>0 select ename,floor(months_between(sysdate,hiredate)/12) 服务总年数, floor(months_between(sysdate,hiredate)) 服务总月数, floor(sysdate-hiredate) 服务总天数 from emp order by 服务总天数; 最后送上一句励志的话 ~~ |
|