Oracle第三章练习

---------------------第三章课后作业-----------------------------
--1.查询入职时间在1982-7-9之后，并且不从事SALESMAN工作的员工姓名、入职时间、职位。
SELECT ename,hiredate,job FROM emp 
 WHERE hiredate>'9-7月-1982' and job<>'SALESMAN';

--2.查询员工姓名的第三个字母是a的员工姓名。
SELECT ename FROM emp
 WHERE ename like '__a%';

--3.查询除了10、20号部门以外的员工姓名、部门编号。
SELECT ename,deptno FROM emp
 WHERE deptno not in(10,20);

--4.查询部门号为30号员工的信息，先按工资降序排序，再按姓名升序排序。 
SELECT ename,deptno,sal FROM emp
 WHERE deptno=30 order by sal DESC,ename ASC;

--5.查询没有上级的员工(经理号为空)的员工姓名。
SELECT ename FROM emp
 WHERE mgr is null;

--6.查询工资大于等于4500并且部门为10或者20的员工的姓名\工资、部门编号。
SELECT ename,sal,deptno FROM emp
 WHERE sal>4500 AND deptno IN(10,20);

--------------------第四章课后作业-------------------------------
--1.计算2000年1月1日到现在有多少月，多少周（四舍五入）。
SELECT round(months_between(sysdate,'01-1月-2000'))months,round((sysdate-to_date('01-1月-2000','dd-mon-yyyy'))/7) weeks
 FROM dual;
 
--2.查询员工ENAME的第三个字母是A的员工的信息(使用2个函数)。
SELECT * FROM emp where substr(ename,3,1)='A';
SELECT * FROM emp WHERE instr(ename,'A')=3;

--3.使用trim函数将字符串‘hello’、‘  Hello ’、‘bllb’、‘ hello    ’分别处理得到下列字符串ello、Hello、ll、hello。
SELECT trim('h' from 'hello') "ello",trim(' ' from 'Hello') "Hello",trim('b' from 'bllb') "ll",trim(' ' from 'hello')"hello"
 FROM dual;

--4.将员工工资按如下格式显示：123,234.00 RMB 。
SELECT empno,ename,job,to_char(sal,'999,999.00') sal FROM emp;

--5.查询员工的姓名及其经理编号，要求对于没有经理的显示“No Manager”字符串。
SELECT ename,nvl2(mgr,to_char(mgr),'No Manager') mgr
 FROM emp;

--6.将员工的参加工作日期按如下格式显示：月份/年份。 
SELECT ename,to_char(hiredate,'mm/yyyy')hiredate 
 FROM emp;

--7.在员工表中查询出员工的工资，并计算应交税款：如果工资小于1000,税率为0，如果工资大于等于1000并小于2000，税率为10％，如果工资大于等于2000并小于3000，税率为15％，如果工资大于等于3000，税率为20％。
SELECT ename,sal,(CASE trunc(sal/1000)
                       WHEN 0 THEN sal*0
                       WHEN 1 THEN sal*0.1
                       WHEN 2 THEN sal*0.15
                       ELSE sal*0.2
                       END) tax
    FROM emp;

--8.创建一个查询显示所有雇员的 ename和 sal。格式化sal为 15 个字符长度，用 $ 左填充，列标签 SALARY。
SELECT ename,lpad(sal,15,'$') 
 FROM emp;
 
------------------------实验２    使用WHERE和ORDER BY子句--------
--实验要求
--（１）掌握WHERE子句的用法。
--（２）掌握ORDER BY子句的用法。
--实验步骤
--（１）创建一个查询显示工资大于2850美元的雇员的姓名及工资
SELECT ename,sal FROM emp 
 WHERE sal>2850;
--（２）显示工资不在1500到2850美元之间的雇员的姓名及工资
SELECT ename,sal 
 FROM emp 
  WHERE sal not between 1500 and 2850;
--（３）显示在10和30部门工作并且工资大于1500美元的雇员的姓名和工资，列标题显示为Employee和Monthly Salary 
SELECT ename,sal 
 FROM emp 
  WHERE sal>1500 AND deptno IN(10,30);
--（４）显示奖金比工资多10％以上的雇员的姓名、工资及奖金。
SELECT ename,sal,comm 
 FROM emp 
  WHERE sal*1.1<comm;
--思考练习题
--１．创建一个查询显示雇员编号为7566的雇员的姓名和部门编号。
SELECT ename,deptno 
 FROM emp 
  WHERE empno=7566;
--２．显示受雇时间在February 20，1981和May 1，1981之间的雇员的姓名、工资、及受雇时间，并以受雇时间升序排列。
SELECT ename,sal,hiredate 
 FROM emp 
  WHERE hiredate BETWEEN '20-2月-1981'  AND '1-3月-1981' ORDER BY hiredate ASC;
--３．显示在10号和30号部门工作的雇员的姓名及其部门编号，并以字母顺序排列。
SELECT ename,deptno 
 FROM emp 
  WHERE deptno IN (10,30) ORDER BY ename;
--４．显示所有受雇于1982年的雇员的姓名和受雇时间。
SELECT ename,hiredate 
 FROM emp 
  WHERE TO_CHAR(hiredate,'YYYY')=1982;
--５．显示没有上级管理员的雇员的姓名及其工作。
SELECT ename,job 
 FROM emp 
  WHERE mgr IS NULL;
--６．显示能挣得奖金的雇员的姓名、工资、奖金，并以工资和奖金降序排列。
SELECT ename,sal,comm 
 FROM emp 
  WHERE comm>0 ORDER BY sal DESC,  comm DESC;
--７．显示姓名中第三个字母为A的雇员的姓名。
SELECT ename 
 FROM emp 
  WHERE ename LIKE '__A%';
--８．显示姓名中两次出现字母L并且在30部门工作或者其管理员编号是7782的雇员的姓名。
SELECT ename 
 FROM emp 
  WHERE ename LIKE '%L%L%' AND (deptno=30 OR mgr=7782);
--９．显示工作为Clerk或Analyst并且工资不等于$1000、$3000、$5000的雇员的姓名、工作及工资。
SELECT ename,job,sal 
 FROM emp 
  WHERE LOWER(job) IN ('clerk','analyst')  AND  sal NOT IN (1000,3000,5000);

------------------实验３    在SELECT语句中使用单行函数--------
--实验要求
--掌握常用的单行函数的用法。
--实验步骤
--（１）显示雇员的编号、姓名、工资以及工资增长15％后的整数值（以New Salary作为列标题）
SELECT empno,ename,sal,sal*1.15 "New Salary" 
 FROM emp;
--（２）在(1)的基础上，增加一个列Increase显示new salary和salary的差值
SELECT empno,ename,sal,sal*1.15 "New Salary",sal*0.15 "Increase" 
 FROM emp;
--（３）显示雇员姓名并以*为指示符代表工资数额（列标题为EMPLOYEE_AND_THEIR_SALARIES），一个指示符代表一百美元，并以工资数额降序排列。
SELECT ename||RPAD('*',TRUNC(sal/100),'*') AS "EMPLOYEE_AND_THEIR_SALARIES" 
 FROM emp;    
--思考练习题
--１．编写一个显示当期日期的查询，列标题为Date。
SELECT sysdate "Date" 
 FROM dual;
--２．显示雇员姓名、受雇日期及工资复审日期（复审日期为受雇后6个月后的第一个星期一），复审日期以列标题REVIEW显示。
SELECT ename,hiredate,TO_CHAR(NEXT_DAY(ADD_MONTHS(hiredate,6),'星期一') ,
'DAY","DDSPTH" of "MONTH","YYYY','nls_date_language=american') 
  AS "REVIEW" 
   FROM emp;
--３．显示每个雇员的姓名并计算出从受雇日期起到目前一共工作了多少个月，以列标题MONTHS_WORKED显示月数（四舍五入），输出以月数升序排列。
SELECT ename,ROUND(MONTHS_BETWEEN(sysdate,hiredate) )"MONTHS_WORKED" 
 FROM emp ORDER BY "MONTHS_WORKED";
--４．编写一个以下面的形式输出的查询：
--<employee name> earns <salary> monthly but wants <3 times salary>.
--列标题为Dream Salaries。
SELECT ename ||' earns '||sal||' monthly but wants '||sal*3 AS "Dream Salaries" 
 FROM emp;

--５．创建一个查询显示每个雇员的姓名及工资（列标题为SALARY），工资的显示模式为：占15个字符的宽度，对于不够位数的以$填充。
SELECT ename,LPAD(sal,15,'$') "SALARY" 
 FROM emp;
--６．编写一个查询显示名字以J、A、M开头的雇员的姓名及姓名所占的字符数，姓名的显示格式为第一个字母大写其他字母小写，为每个列设置合适的列标题。
SELECT ename,hiredate,TO_CHAR(hiredate,'DAY') "DAY" 
 FROM emp ORDER BY TO_CHAR(hiredate,'D');
--７．显示雇员的姓名、受雇日期及受雇当天是星期几（列标题为DAY），并以DAY升序排列
--创建一个查询显示雇员姓名及其奖金（列标题为COMM），如果该雇员不能获得奖金则以“No Commission.”显示。
SELECT ename,  CASE WHEN comm>0 THEN TO_CHAR(comm,'9999999') 
 ELSE 'No Commission' END "COMM" 
  FROM emp;