Oracle查询作业集(上)
1.查询公司的人数,以及在80,81,82,87年,每年雇用的人数,结果类似下面的格式
TOTAL 1980 1981 1982 1987
---------- ---------- ---------- ---------- ----------
14 1 10 1 2
SQL> edit
已写入 file afiedt.buf
1 select count(*) total,sum(decode(to_char(hiredate,'YYYY'),1980,1,0))as "1980",
2 sum(decode(to_char(hiredate,'YYYY'),1981,1,0))as "1981",
3 sum(decode(to_char(hiredate,'YYYY'),1982,1,0))as "1982",
4 sum(decode(to_char(hiredate,'YYYY'),1987,1,0))as "1987"
5* from emp
SQL> /
TOTAL 1980 1981 1982 1987
---------- ---------- ---------- ---------- ----------
14 1 10 1 2
这个例子相当相当重要!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
其中select decode(to_char(hiredate,'YYYY'),1980,1,0) from emp这个语句是核心
如果年份是1980的将其置为1,否则为0,这样操作了所有的年份(相当于形成了一张临时表)
然后再利用sum计算其和(即计算这个临时表的总和就知道有多少个1980了)!!!
同理可以判断每个月份有多少人入职
select
sum(decode(to_char(hiredate,'MM'),02,1,0)) "2月入职",
sum(decode(to_char(hiredate,'MM'),03,1,0)) "3月入职",
sum(decode(to_char(hiredate,'MM'),04,1,0)) "4月入职",
sum(decode(to_char(hiredate,'MM'),05,1,0)) "5月入职"
from emp
2.查询员工号,姓名,工资(若为NULL则作为0处理),补助(若为NULL则作为0处理)以及工资提高百分之20%后四舍五入到整数的结果(取别名为new salary)。
总结:
这里用了两个很重要的函数:nvl和round()用于四舍五入——比如将原工资除以7,只保留三位小数
select round(sal/7,3) "new sal" from emp
![](https://images0.cnblogs.com/blog2015/798891/201508/182043427859790.png)
计算两个日期之间有多少个月?
select ename "名字",round(months_between(to_date('1990-01-01','YYYY-MM- DD'),to_date('1980-01-01','YYYY-MM-DD')),0) "worked_month" from emp
注意:months_between()这个函数里的两个参数是日期类型,所以需要转型!
4.查询员工的姓名和工资,按下面的形式显示结果(工资字段必须为15位,空位用$填充)
姓名 工资
KING $$$$$$$$$$24000
MIKE $$$$$$$$$$$4800
注意lpad的最后一个参数,因为它是字符型的,所以用单引号。
5.
SQL> edit
已写入 file afiedt.buf
1* select upper(ename)|| ' ' ||'earns'||to_char(sal,'$999999')||'monthly but wants'||to_char(sal*3,'$999999') "Dream salary" from emp
SQL> /
Dream salary
-------------------------------------------------
SMITH earns $800monthly but wants $2400
ALLEN earns $1600monthly but wants $4800
WARD earns $1250monthly but wants $3750
JONES earns $2975monthly but wants $8925
MARTIN earns $1250monthly but wants $3750
BLAKE earns $2850monthly but wants $8550
CLARK earns $2450monthly but wants $7350
SCOTT earns $3000monthly but wants $9000
KING earns $5000monthly but wants $15000
TURNER earns $1500monthly but wants $4500
ADAMS earns $1100monthly but wants $3300
JAMES earns $950monthly but wants $2850
FORD earns $3000monthly but wants $9000
MILLER earns $1300monthly but wants $3900
已选择14行。
总结(这个也很常用):
9,代表一位数字,如果当前位有数字,显示数字,否则不显示(小数部分仍然会强制显示)
0,强制显示该位,如果当前位有数字,显示数字,否则显示0
6.做一个查询,按下面的形式显示结果
Employees_and_their_salarys
King********
Zhang*****
Wang*****
其中每一个*代表一千元(四舍五入)。按工资从多到少排序。
SQL> edit
已写入 file afiedt.buf
1* select rpad(ename,(length(ename)+round(sal/1000)),'*') from emp
SQL> /
RPAD(ENAME,(LENGTH(ENAME)+ROUND(SAL/1000)),'*')
--------------------------------------------------------------------------------------------------------------------------------------------
SMITH*
ALLEN**
WARD*
JONES***
MARTIN*
BLAKE***
CLARK**
SCOTT***
KING*****
TURNER**
ADAMS*
JAMES*
FORD***
MILLER*
已选择14行。
7.查询所有部门的名称,loc,员工数量和工资平均值
select d.dname "部门名称",d.loc "部门所在地",count(*) "员工数量",avg(sal) "平均工资"
from emp e,dept d
where e.deptno=d.deptno
group by d.dname,d.loc
这样做也可以:
select e.deptno "部门名称",d.loc "部门所在地",count(*) "员工数量",avg(sal) "平均工资"
from emp e,dept d
where e.deptno=d.deptno
group by e.deptno,d.loc
这个例子也很有意思,很重要
再次注意和理解SQL语句的执行顺序
where(排除一些不必要的数据,提高效率)——>group up(按照一定的字段来分组)——>在分好组的基础上
再使用having(一些条件)——>然后再将其排序order by(某些字段,顺序)——>最后利用select输出
即:
在整个语句执行的过程中,最先执行的是Where子句,在对表数据进行过滤后,符合条件的数据通过Group by进行分组,
分组数据通过Having子句进行组函数过滤,最终的结果通过order by子句进行排序,排序的结果被返回给用户