oracle 实验三 单表查询

1. 启动 SQL*Plus

用 scott/tiger 登录。查询 scott 用户下的所有表，在本实验中，我们将使用这些表。

表：EMP

列名	含义
EMPNO	雇员号
ENAME	雇员姓名
JOB	职务
MGR	经理代码
HIREDATE	受雇日期
SAL	薪金
COMM	佣金
DEPTNO	部门号

表：DEPT

列名	含义
DEPTNO	部门号
DNAME	部门名
LOC	部门所在地

表：SALGRADE

列名	含义
GRADE	薪金等级
LOSAL	最低工资
HISAL	最高工资

 

2 用ＳＱＬ语句完成下列查询，填写相应的查询语句。

  ( 注意，在 SQL*Plus 下，每写完一个 SQL 的查询，都可以通过直接拷贝，将你所写的 SQL 语句复制到该实验报告中 )

(1)      选择部门 30 中的雇员。

        Select * from emp where deptno =30; 

 

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

Select ename ,empno ,deptno from emp where job=’CLERK’;

(3)      找出佣金高于薪金的雇员。 

Select * from emp where comm>sal;

(4)      找出佣金高于薪金 60% 的雇员。 

Select * from emp where comm> sal * 0.6;

 

 

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

Select * from emp where deptno=10 and job=’MANAGER’

Union

Select * from emp where deptno=20 and job=’CLERK’;

 

 

(6)      找出部门 10 中所有经理、部门 20 中所有办事员以及既不是经理又不是办事员但其薪金大于或等于 2000 的所有雇员的详细资料。

Select * from emp where deptno=10 and job=’MANAGER’

Union

Select * from emp where deptno=20 and job=’CLERK’

Union

Select * from emp where job<>’MANAGER’ and job<>’CLERK’ and sal>=2000;

 

or 

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

or deptno=20 and job='CLERK'

or sal>=2000 and job not in ('MANAGER','CLERK');

 

 

(7)      找出收取佣金的雇员的不同工作。

Select job from emp where comm Is not null ;

 

 

(8)      找出不收取佣金或收取的佣金低于 100 的雇员。

Select * from emp where comm=null or comm <100;

 

 

(9)      找出各月最后一天受雇的所有雇员。 

select * from emp where

Substr(to_char(last_day(hiredate)),1,2)=substr(to_char(hiredate),1,2);

 

or

select * from emp where Hiredate=last_day(hiredate);

(10)  找出早于 12 年之前受雇的雇员。 

 Select * from emp where

 to_number(substr(to_char(sysdate),8,2))-to_number(substr(to_char(hiredate),8,2)) +100>12;

 

or

 

Select * from emp where hiredate<=add_months(sysdate,-144)

 

(11) 显示只有首字母大写的所有雇员的姓名。 

select * from emp where ename = initcap(ename);

 

(12)  显示正好为 15 个字符的雇员姓名。

Select ename from emp where length(ename)=15;

 

 

(13)  显示不带有“ R ”的雇员姓名。

Select ename from emp where ename not like ‘%R%’;

 

 

(14)  显示所有雇员的姓名的前三个字符。

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

   

 

(15)  显示所有雇员的姓名，用 a 替换所有“ A ”。

//select replace(ENAME,' 替换后字符串 ',' 被替换字符串 ') from emp;

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

or

select  translate(name,'A','a') from emp;

 

 

(16)  显示所有雇员的姓名以及满 10 年服务年限的日期。

  SELECT ename,add_months(hiredate,120) from emp;

 

(17)  显示雇员的详细资料，按姓名排序。

Select * from emp order by ename;

 

(18)  显示雇员姓名，根据其服务年限，将最老的雇员排在最前面。

Select ename from emp order by (sysdate-hiredate) desc;

 

or

Select ename from emp order by hiredate;

 

(19)  显示所有雇员的姓名、工作和薪金，按工作内的工作的降序顺序排序，同工作按薪金排序。

Select ename ,job,sal from emp order by job desc , sal;

 

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

Select ename,to_char(hiredate,'YYYY') as YYYY,to_char(hiredate,'MM') as MM from emp order by MM,YYYY

 

 

 

(21)  显示在一个月为 30 天的情况下所有雇员的日薪金，忽略卢比余数。

select ename ,round(sal/30,0) as day_sal from emp;

 

 

(22)  对于每个雇员，显示其加入公司的天数。

Select ename , trunc( sysdate-hiredate ) from emp;

or

 

select ename ,floor(sysdate-hiredate) from emp;

 

(23)  显示姓名字段的任何位置包含“ A ”的所有雇员的姓名。

Select ename from emp where ename like '%A%';

 

(24)  以年、月和日显示所有雇员的服务年限。 

         select ename ,hiredate,floor(margin/12) as years,

         floor(mod(margin,12)) as months,

         floor((mod(margin,12)-floor(mod(margin,12)))*31) as days, sysdate 

         from (select ename ,hiredate, months_between(sysdate,hiredate) as margin from emp);