1.已知Oracle的Scott用户中提供了三个测试数据库表,名称分别为dept,emp和salgrade。使用SQL语言完成以下操作
1)试用SQL语言完成下列查询(单表查询):
a)查询20号部门的所有员工信息:
select * from emp e where e.deptno=20;
b)查询奖金(COMM)高于工资(SAL)的员工信息:
select * from emp where comm>sal;
c)查询奖金高于工资的20%的员工信息:
select * from emp where comm>sal*0.2;
d)查询10号部门中工种为MANAGER和20号部门中工种为CLERK的员工的信息:
select * from emp e
where (e.deptno=10 and e.job='MANAGER')
or (e.deptno=20 and e.job='CLERK') ;
e)查询所有工种不是MANAGER和CLERK,且工资大于或等于2000的员工的详细信息:
select * from emp
where job not in('MANAGER','CLERK') and sal>=2000;
f)查询没有奖金或奖金低于100的员工信息:
select * from emp where comm is null or comm<100;
g)查询员工工龄大于或等于10年的员工信息:
select * from emp where (sysdate-hiredate)/365>=10;
h)查询员工信息,要求以首字母大写的方式显示所有员工的姓名:
第一种写法:
select initcap(ename) from emp;
第二种写法:
select upper(substr(ename,1,1))||lower(substr(ename,2)) from emp;
i)查询在2月份入职的所有员工信息:
select * from emp where to_char(hiredate,'MM')='02';
j)显示所有员工的姓名、入职的年份和月份,按入职日期所在的月份排序,若月份相同则按入职的年份排序:
select ename,to_char(hiredate,'yyyy') year,to_char(hiredate,'MM')
month
from emp
order by month,year;
k)查询'JONES'员工及所有其直接、间接下属员工的信息:
select e.* from emp e
start with ename='JONES'
connect by prior empno=mgr;
l)查询SCOTT员工及其直接、间接上级员工的信息:
select e.* from emp e
start with ename='SCOTT'
connect by prior mgr=empno;
2)试用SQL语言完成下列查询(多表查询):
a)查询从事同一种工作但不属于同一部门的员工信息:
select a.ename,a.job,a.deptno,b.ename,b.job,b.deptno
from emp a,emp b
where a.job=b.job and a.deptno<>b.deptno;
b)查询各个部门的详细信息以及部门人数、部门平均工资:
select d.deptno,count(e.empno),avg(e.sal),d.dname,d.loc
from emp e ,dept