Oracle数据库查询练习

--SQL练习查询训练(今天才学习的数据库练习)

--解锁scott用户

oracle 有关emp表的简单查询练习题 

使用scott/tiger用户下的emp表和dept表完成下列练习，表的结构说明如下 

emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号) 

dept部门表(deptno部门编号/dname部门名称/loc地点)

--如果scott用户是锁定的状态 下面是解锁代码

alter user scott account unlock identified by 123456; --解锁后密码改为123456

grant dba to scott; --授权scott用户为超级用户(当然这里是练习)

--1. 查询工资大于1200的员工姓名和工资

     select ename,sal,nvl2(comm,comm,0) from emp where sal  > 1200;

 

--2. 查询员工号为7934的员工的姓名和部门号

select ename,deptno from emp where empno = 7934;

 

--3. 选择工资不在5000到12000的员工的姓名和工资 

select ename,sal from emp where sal < 5000 or sal > 12000;

select ename,sal from emp where sal not between 5000 and 12000;

 

--4. 选择雇用时间在1981-02-01到1981-05-01之间的员工姓名，职位(job)和雇用时间,按从早到晚排序.

select ename,job,hiredate from emp where hiredate between to_date('1981-02-01','yyyy-MM-dd') and to_date('1981-05-01','yyyy-MM-dd') order by hiredate asc;

 

--5. 选择在20或10号部门工作的员工姓名和部门号

select ename,deptno from emp where deptno in (20,10);

 

--6. 选择在1987年雇用的员工的姓名和雇用时间

select ename,hiredate from emp where to_char(hiredate,'yyyy') = '1987';

 

--7. 选择公司中没有管理者的员工姓名及job

select ename,job from emp where mgr is null;

 

--8. 选择公司中有奖金 (COMM不为空,且不为0) 的员工姓名，工资和奖金比例,按工资逆排序,奖金比例逆排序.

select ename,sal,comm / sal from emp where comm is not null and comm  > 0 order by sal desc,comm / sal desc;

 

--SQL练习训练一

--1、检索dept表中的所有列信息

select * from scott.dept;

 

--2、检索emp表中的员工姓名、月收入及部门编号

select ename "姓名",sal+nvl(comm,0) as "月收入",deptno 部门编号 from emp;

 

--3、检索emp表中员工姓名、及雇佣时间（雇佣时间按照yyyy-mm-dd显示）

select ename 姓名,to_char(hiredate,'yyyy-mm-dd') 入职时间 from emp;

  

   /*注意：第一个时间是日期类型的，在Oracle的查询界面它的旁边带有一个日历。

   第二个时间是字符型的。

   易错点：不要将YYYY-MM-DD使用双引号*/

--5、使用distinct去掉重复行。

   --检索emp表中的部门编号及工种，并去掉重复行。

   select distinct deptno,job from emp;

--6、使用表达式来显示列

   --检索emp表中的员工姓名及全年的月收入

   select ename 姓名, (sal+nvl(comm,0))*12 年收入 from emp;

--7、使用列别名

   --用姓名显示员工姓名，用年收入显示全年月收入。

      select ename 姓名, (sal+nvl(comm,0))*12 年收入 from emp;

--8、连接字符串

      select ename || '年收入是：' || (sal+nvl(comm,0))*12 "老总发工资" from emp;

--9、使用WHERE子句

   --检索月收入大于2000的员工姓名及月收入。

   select * from (select ename "姓名",sal+nvl(comm,0) as salary,deptno 部门编号 from emp) where salary > 2000;

   

   --检索月收入在1000元到2000元的员工姓名、月收入及雇佣时间。

   select * from (select ename "姓名",sal+nvl(comm,0) as salary,hiredate 入职时间 from emp) 

   where salary >= 1000 and salary <= 2000;

   

   select * from (select ename "姓名",sal+nvl(comm,0) as salary,hiredate 入职时间 from emp)

    where salary between 1000 and 2000;

 

--10、like的用法：

    --检索以S开头的员工姓名及月收入。

    select ename "姓名",sal+nvl(comm,0) as salary,hiredate 入职时间 from emp where ename like 'S%';

    --检索员工姓名中的第三个字符是A的员工姓名及月收入。

    select ename "姓名",sal+nvl(comm,0) as salary,hiredate 入职时间 from emp where ename like '__A%';

 

--11、在WHERE条件中使用IN操作符

    --检索emp表中月收入是800的或是1250的员工姓名及部门编号

    select ename "姓名",sal+nvl(comm,0) as salary,hiredate 入职时间,deptno 部门编号 from emp 

    where sal+nvl(comm,0) in (800,1250);

    

    select ename "姓名",sal+nvl(comm,0) as salary,hiredate 入职时间,deptno 部门编号 from emp 

    where sal+nvl(comm,0) = 800 or sal+nvl(comm,0) = 1250;

    

--12、在WHERE条件中使用逻辑操作符(AND、OR、NOT)

    --显示在部门20中岗位是CLERK的所有雇员信息

    select * from emp where deptno = 20 and job = 'CLERK';

    

    --显示工资高于2500或岗位为MANAGER的所有雇员信息

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

 

--13、查询表中是空值的数据

    --检索emp表中有提成的员工姓名、月收入及提成。

    select ename,sal,comm from emp where comm is not null and comm > 0;

 

--14、使用ORDER BY子句，进行排序。

    --检索emp表中部门编号是30的员工姓名、月收入及提成，并要求其结果按月收入升序、然后按提成降序显示。

     select ename,sal,nvl2(comm,comm,0) from emp where deptno = 30 order by sal asc,nvl2(comm,comm,0) desc;

 

工资 ＝ 薪金 ＋ 佣金

--1、选择部门30中的雇员

select * from emp where deptno = 30;

 

--2、列出所有办事员的姓名、编号和部门

select ename,empno,emp.deptno,dept.dname from emp left join dept on emp.deptno = dept.deptno where job = 'CLERK';

 

--3、找出佣金高于薪金的雇员

select * from emp where comm is not null and comm > sal;

 

--4、找出佣金高于薪金60%的雇员

select * from emp where comm is not null and comm / sal  > 0.6;

select * from emp where comm is not null and comm > sal * 0.6;

 

--5、找出部门10中所有经理和部门20中的所有办事员的详细资料

select * from emp where deptno = 10 and job = 'MANAGER'

union all

select * from emp where deptno = 20 and job = 'CLERK';

 

select * from emp where (deptno = 10 and job = 'MANAGER') or (deptno = 20 and job = 'CLERK');

 

--6、找出部门10中所有经理、部门20中所有办事员，既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料

select * from emp where (deptno = 10 and job = 'MANAGER') or (deptno = 20 and job = 'CLERK') or (job not in('MANAGER','CLERK') and sal >= 2000);

 

--7、找出收取佣金的雇员的不同工作

select distinct job from emp where comm is not null and comm > 0;

 

--8、找出不收取佣金或收取的佣金低于100的雇员 

select * from emp where comm is null or comm < 100;

 

--9、找出各月倒数第三天受雇的所有雇员

--last_day获取当前日期所在月的最后一天

select * from emp where hiredate  = last_day(hiredate)-2;

 

--10、找出早于25年之前受雇的雇员

select * from emp where months_between(sysdate,hiredate) > 31 * 12;

 

--11、显示只有首字母大写的所有雇员的姓名

select * from emp;

select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename) -1)) from emp;

update emp set ename = upper(substr(ename,1,1))||lower(substr(ename,2,length(ename) -1)) where empno = 7369;

select * from emp where ename = upper(substr(ename,1,1))||lower(substr(ename,2,length(ename) -1));

 

--12、显示正好为6个字符的雇员姓名 length

select * from emp where length(ename) = 6;

 

--13、显示不带有'R'的雇员姓名 not like

select * from emp where ename not like '%R%';

 

--14、显示所有雇员的姓名的前三个字符 substr

select substr(ename,1,3) from emp;

 

--15、显示所有雇员的姓名，用a替换所有'A' replace

select replace(ename,'A','a') from emp;

 

--16、显示所有雇员的姓名以及满10年服务年限的日期 add_months

select ename from emp where months_between(sysdate,hiredate) >= 372;

 

--17、显示雇员的详细资料，按姓名排序

select * from emp order by ename;

 

--18、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面 ASC

select * from emp order by hiredate asc;

 

--19、显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而工作相同时按薪金升序

select * from emp order by job desc,sal asc;

 

--20、显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，将最早年份的项目排在最前面 ASC

select ename,to_char(hiredate,'yyyy'),to_char(hiredate,'MM') from emp order by to_char(hiredate,'MM'),to_char(hiredate,'yyyy') asc;

 

--21、显示在一个月为30天的情况下所有雇员的日薪金

select round(sal / 30,2) from emp;

 

--22、找出在（任何年份的）2月受聘的所有雇员

select * from emp where to_char(hiredate,'MM') = 2;

 

--23、对于每个雇员，显示其加入公司的天数

select round(sysdate - hiredate) from emp;

 

--24、显示姓名字段的任何位置，包含 "A" 的所有雇员的姓名 instr

select * from emp where  instr(ename,'A') > 0;

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

 

--25、以年、月和日显示所有雇员的服务年限

select round(sysdate-hiredate)/365,mod(round(sysdate-hiredate),365)/30,mod(round(sysdate-hiredate),30) from emp;