Oracle课后练习-3

 

1. 登录到SQL*Plus，初始用户名为：scott，口令为tiger

   conn scott/tiger

    

2. 分别显示emp、dept、salgrade表结构。

   desc emp;
   desc dept;
   desc salgrade;

    

3. 用一个逗号和一个空格将雇员姓名和雇员工作连接后作为一个列输出，显示的列标题为Employee and Title。

   显示如下：

   Employee and Title

   ---------------------

   SMITH, CLERK

   ALLEN, SALESMAN

   WARD, SALESMAN

   JONES, MANAGER

   MARTIN, SALESMAN

   ……

   Select ename||', '||job "Employee and Title" from emp;

    

4. 查询显示每个雇员的雇员编号、姓名、工作、受雇日期。再用命令将该SQL语句保存成脚本文件lab05_4.sql，并用命令再次执行该脚本文件。

   select empno,ename,job,hiredate from emp;
   save url;
   start url;
   

5. 用命令创建一个脚本文件lab05_5.sql，实现查询显示工资大于2850美元的雇员的姓名及工资,并用命令执行该脚本文件。

   要求：1)工资显示格式如下：$2,000.0 2)将列格式命令一起放入脚本文件中

   ed url;
   start url;
   
   --以下是lab05_5.sql--
   col sal format $9,999.9
   select ename,sal from emp where sal>2850
   /

    

6. 在30号部门工作或者有上级领导的雇员中，查询姓名里出现两次字母L的所有雇员姓名、所在部门编号以及其领导编号。

   select ename,deptno,mgr 
   from emp
   Where ename like '%L%L%' and (deptno=30 or mgr is not null);

    

7. 显示姓名中含有倒数第三个字母为I的雇员的姓名、工资。

   select ename,sal from emp where ename like '%I__';

    

8. 求出管理员的人数（列名为Number of Managers），而不显示具体的管理员是谁。

   select count(distinct MGR) as "Number of Managers" from emp;

    

9. 显示雇员的编号、姓名、工资以及工资增长15%后的整数值（该值截取成整数值，并以New Salary作为列标题）。

   select EMPNO,ENAME,SAL,trunc(SAL*1.15,0) as "New Salary" from EMP;

    

10. 显示部门号为10和30的所有雇员的姓名、工资、奖金及实际收入（工资奖金之和即为实际收入），并以工资升序、奖金降序排列。

    select ename,sal,comm,sal+nvl(comm,0) "实际收入"
    from emp where deptno in (10,30)
    order by sal,comm desc ;

     

11. 按照工作种类（job）进行分组，显示各个组内工资的最高（Maximum）、最低（Minimum）、合计（Sum）和平均值（Average），平均工资四舍五入到小数点后两位。

    select job,MAX(sal),Min(sal),Sum(sal),ROUND(avg(sal),2) 
    from emp 
    group by job;

     

12. 显示受雇时间在1981年2月20日到1981年5月1日之间的并且工资大于1500美元的雇员的姓名、受雇时间、转正时间（受雇后的6个月即转正时间）和工资，列标题分别显示为Employee、Hire Date、Correct Time和Monthly Salary。（注意有些列名中间有空格）

    select ename "Employee",sal "Monthly Salary",hiredate "Hire Date",
    ADD_MONTHS(hiredate,6) "Correct Time" 
    From emp 
    where sal>1500 and hiredate between '20-2月-1981' and '1-5月-1981';
    --标准答案： to_date('20-2月-1981') and to_date('1-5月-1981');

     

13. 查询每个经理管理的员工人数。要求只显示管理人数超过2个人（含2人）的经理编号和管理的员工人数（列名为EmpNumber），并按照员工人数的升序排列。

    select mgr as "经理编号", count(mgr) as "EmpNumber"
    from emp 
    group by mgr
    having count(mgr)>=2
    order by count(mgr);
    
    --标准答案
    select mgr,count(*) "EmpNumber" 
    from emp 
    where mgr is not null 
    group by mgr 
    having count(*)>=2 
    order by 2 asc

     

14. 查询一个列，其中包含雇员姓名和以*为指示符代表的工资数额（该列标题为EMPLOYEE_AND_THEIR_SALARIES）。具体要求如下：1)工资数额的一个指示符*代表一百美元（若超过50可以显示一个*，没有超过50就不显示一个*）。2)查询结果以“雇员名***”的格式显示，并以工资数额降序排列。如果SMITH工资为360美元，FORD工资为340美元，则显示如下：

    EMPLOYEE_AND_THEIR_SALARIES

    SMITH****

    FORD***

select ename||rpad('*',trunc(sal/100),'*') as "EMPLOYEE_AND_THEIR_SALARIES"
from emp
order by sal desc;