1、列出薪金比“SMITH”多的所有员工。
select *from emp where sal+nvl(comm,0)>(select sal+nvl(comm,0) from emp where ename='ALLEN');
2、列出所有员工的姓名及其直接上级的姓名。
select ename as "员工姓名",(select ename as "上司姓名" from emp where empno in(a.mgr)) from emp a;
select m1.ename as "员工姓名",m2.ename as "上司姓名" from emp m1 left join emp m2 on m1.mgr=m2.empno;
3、列出受雇日期早于其直接上级的所有员工
Select m1.ename,m1.hiredate,m2.ename,m2.hiredate from emp m1,emp m2 where m1.hiredate<m2.hiredate and m1.mgr=m2.empno ;
4、列出薪资高于公司平均员工的所有信息
select *from emp where sal+nvl(comm,0)>(select avg(sal+nvl(comm,0)) from emp);
5、列出最低薪资大于1500的工作
select job from emp where sal+nvl(comm,0)>1500;
6、列出在部门'SALES'工作的所有员工,假设不知道部门的编号
select *from emp where deptno = (select deptno from dept where dname='SALES');
7、列出与"scott"从事相同工作的员工的信息,但是排除它本身
select *from emp where job = (select job from emp where ename='SCOTT') and ename!='SCOTT';
8、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金
select *from emp where sal+nvl(comm,0)>(select max(sal+nvl(comm,0)) from emp where deptno=30);
9、列出在每个部门工作的员工的数量、平均工资、平均服役期
select count(*) as "员工数量", avg(sal+nvl(comm,0)) as "平均工资",avg(sysdate-hiredate) as "平均服役期" from emp group by deptno;
10、列出所有员工的姓名、部门名称和工资
select ename,dept.dname,sal+nvl(comm,0) from emp,dept where emp.deptno=dept.deptno;
11、列出每种工作的最低工资
select job, min(sal+nvl(comm,0)) from emp group by job;
12、检索员工表中员工姓名、员工工资、部门编号
select ename as "员工姓名",sal+nvl(comm,0) as 员工工资,deptno as "部门编号" from emp;
13、检索员工表中的员工姓名和雇用时间
select ename as "员工姓名",to_char(hiredate,'YYYY-MM-DD') as "雇用时间" from emp;
14、检索员工表中的员工姓名和全年收入
select ename "员工姓名", (sal+nvl(comm,0))*12 "年薪" from emp;
15、检索员工表emp中的员工姓名和职位,显示成 某某员工的职位是什么
select ename || ' 的工作是 ' || job from emp;
16、检索月收入在1000到2000的员工姓名、职位以及雇用时间
select ename,job,sal,hiredate from emp where sal between 1000 and 2000;
17、检索员工姓名中低三个字符是A的员工的姓名、职位、工资和雇用时间
select ename ,job,sal+nvl(comm,0), hiredate from emp where ename like '__L%';
18、检索月收入在800或1250的员工信息
select *from emp where sal+nvl(comm,0) in (800,1250);
19、检索emp表中有提成的员工的信息
select *from emp where comm is not null;
20、检索emp表中部门编号是30的员工姓名、月收入及提成,并要求其结果按月收入升序、然后按提成降序显示。
select ename "员工姓名",comm "提成",sal "工资" from emp where deptno='30' order by sal asc,comm desc;
21、选择雇用时间在1981-02-01到1981-05-01之间的员工姓名
select ename "姓名",job "工作",to_char(hiredate,'YYYY-MM-DD') "雇用时间",sal "工资" from emp
where hiredate between to_date('1981-02-01','YYYY-MM-DD') and to_date('1981-05-01','YYYY-MM-DD');
Oracle练习:有关Oracle用户scott的sql练习
最新推荐文章于 2024-06-08 16:49:03 发布