第一部分
01.
查询员工表所有数据,
并说明使用*的缺点
select * from emp;
02.
查询职位(JOB)为'PRESIDENT'的员工的工资
select sal from emp where job='PRESIDENT';
03.
查询佣金(COMM)为0或为NULL的员工信息
SQL> select * from emp where nvl(comm,0)=0;
04.
查询入职日期在 1981-5-1到1981-12-31之间的所有员工信息
select * from emp where hiredate between to_date('1981-5-1','YYYY-MM-DD') and to_date('1981-12-31','YYYY-MM-DD');
select * from emp where hiredate between to_date('1/5月/1981')
and to_date('31/12月/1981');
05.
查询所有名字长度为4的员工的员工编号,姓名
select empno,ename from emp where length(ename)=4;
06.
显示10号部门的所有经理('MANAGER')和20号部门的所有职员('CLERK')的详细信息
SQL> select * from emp where (job='MANAGER' and deptno=10) or (deptno=20 and job='CLERK');
07.
显示姓名中没有'L'字的员工的详细信息或含有'SM'字的员工信息
select * from emp where ename not like '%L%' or ename like '%SM%';
08.
显示各个部门经理('MANAGER')的工资
select sal from emp where job='MANAGER';
09.
显示佣金(COMM)收入比工资(SAL)高的员工的详细信息
select * from emp where nvl(comm,0)>nvl(sal,0);
10.
把hiredate列看做是员工的生日,求本月过生日的员工(考察知识点:单行函数)
Select * from emp where to_char( hiredate,'mm')=to_char( sysdate,'mm');
11.
把hiredate列看做是员工的生日,求下月过生日的员工(考察知识点:单行函数)
SQL>
select * from emp where to_char( hiredate,'mm')=to_char( add_months(sysdate,1),'mm');
12.
求1982年入职的员工(考察知识点:单行函数)
select * from emp where to_char( hiredate,'YYYY')=1982;
13.
求1981年下半年入职的员工(考察知识点:单行函数)
SQL>
select * from emp where to_char( hiredate,'YYYY-MM-DD') between '1981-07-01' and '1981-12-31' ;
14.
求1981年各个月入职的的员工个数(考察知识点:组函数)
SQL>
select count(*),trunc(hiredate,'month') from emp where to_char( hiredate,'YYYY')='1981' group by trunc(hiredate,'month') ;
第二部分：
01.
查询各个部门的平均工资
select deptno,avg(nvl(sal,0)) from emp group by deptno ;
select deptno,sum(nvl(sal,0)+nvl(comm,0))/count(*) from emp group by deptno;
02.
显示各种职位的最低工资
select job,min(nvl(sal,0)) from emp
group by job;
03.
按照入职日期由新到旧排列员工信息
SQL> select * from emp order by hiredate desc;
04.
查询员工的基本信息，附加其上级的姓名
SQL> select t1.empno,t1.ename,t1.job,t2.ename,t1.hiredate,t1.sal,t1.comm,t1.deptno from emp t1 join emp t2 on t1.mgr=t2.empno;
05.
显示工资比'ALLEN'高的所有员工的姓名和工资
SQL> select t1.empno,t1.ename,t1.job,t1.mgr,t1.hiredate,t1.sal,t1.comm,t1.deptno from emp t1 join emp t2 on nvl(t1.sal,0)> nvl
(t2.sal,0) where t2.ename='ALLEN';
06.
显示与'SCOTT'从事相同工作的员工的详细信息
SQL> select t1.empno,t1.ename,t1.job,t1.mgr,t1.hiredate,t1.sal,t1.comm,t1.deptno from emp t1 join emp t2 on t1.job=t2.job where t2.ename='SCOTT';
07.
显示销售部('SALES')员工的姓名
select ename from emp e, dept d where e.deptno = d.deptno and d.dname='SALES';
08.
显示与30号部门'MARTIN'员工工资相同的员工的姓名和工资
SQL> select e.* from emp e join emp e1 on e.sal=e1.sal where e.deptno=30 and e1.ename='MARTIN';
09.
查询所有工资高于平均工资(平均工资包括所有员工)的销售人员('SALESMAN')
Select * from emp where sal>(select avg(nvl(sal,0)) from emp ) and job='SALESMAN';
10.
显示所有职员的姓名及其所在部门的名称和工资
SQL> select e.ename,e.sal,d.dname from emp e
join dept d on e.deptno=d.deptno ;
11.
查询在研发部('RESEARCH')工作员工的编号，姓名，工作部门，工作所在地
SQL> select e.*,
dname,loc from emp e join dept d on e.deptno=d.deptno where d.dname='RESEARCH';
12.
查询各个部门的名称和员工人数
Select deptno,count(*) from emp group by deptno;
13.
查询各个职位员工工资大于平均工资(平均工资包括所有员工)的人数和员工职位
Select count(*),job from emp where sal>(select avg(nvl(sal,0)) from emp ) group by job;
14.
查询工资相同的员工的工资和姓名
SQL> select t1.empno,t1.ename,t1.job,t1.mgr,t1.hiredate,t1.sal,t1.comm,t1.deptno from emp t1 join emp t2 on t1.sal=t2.sal where t1.empno!=t2.empno ;
15.
查询工资最高的 3名员工信息
select
* from (select
* from emp
order by nvl(sal,0)
desc) where
rownum<=3;
16.
按工资进行排名,排名从1开始,工资相同排名相同(如果两人并列第1则没有第2名,从第三名继续排)
SQL> select e.*, (select count(*) from emp where nvl(sal,0)> nvl(e.sal,0))+1 rank from emp e order by rank;
17.
求入职日期相同的(年月日相同)的员工
SQL> select t1.empno,t1.ename,t1.job,t1.mgr,t1.hiredate,t1.sal,t1.comm,t1.deptno from emp t1 join emp t2 on t1.hiredate=t2.hiredate where t1.empno!=t2.empno
;
18.
查询每个部门的最高工资
Select max(nvl(sal,0)),deptno from emp group by deptno;
19.
查询每个部门,每种职位的最高工资
select deptno, job, max(nvl(sal,0)) from emp group by deptno,job;
20.
查询每个员工的信息及工资级别(用到表 Salgrade)
SQL> select GRADE,e.* from salgrade , (Select * from
emp ) e where LOSAL <= nvl(e.sal,0) and nvl(e.sal,0)<=HISAL;
21.
查询工资最高的第 6-10
名员工
SQL> select * from (select e.*,rownum as rowno from (select
* from emp
order by nvl(sal,0)
desc) e) where rowno between 6 and 10;
22.
查询各部门工资最高的员工信息
Select * from emp e,(Select deptno,max(nvl(sal,0)) sa from emp group by deptno) s where e.sal=s.sa;
23.
查询每个部门工资最高的前 2名员工
SQL> select * from emp e where
(select count(*) from emp where nvl(sal,0) > nvl(e.sal,0) and e.deptno = deptno) < 2
order by deptno, nvl(sal,0) desc;
24.
查询出有3个以上下属的员工信息
SQL> select * from emp,(select mgr from (select mgr,count(*) from emp
group by mgr having count(*)>=3)) s where empno =s.mgr
;
select * from emp e where
(select count(*) from emp where e.empno = mgr) > 2;
25.
查询所有大于本部门平均工资的员工信息()
select * from emp e where sal > (select avg(sal) from emp where e.deptno=deptno) ;
26.
查询平均工资最高的部门信息
Select * from dept d,(Select deptno from (Select avg(nvl(sal,0)) sal ,deptno from emp group by deptno order by sal desc ) where
rownum<=1) e where d.deptno= e.deptno;
27.
查询大于各部门总工资的平均值的部门信息
select d.*,sumsal from dept d, (select sum(nvl(sal,0)) sumsal, deptno from emp group by deptno) se where se.sumsal >(select avg(sum(nvl(sal,0))) from emp
group by deptno) and se.deptno = d.deptno;
28.
查询大于各部门总工资的平均值的部门下的员工信息(考察知识点:子查询,组函数,连接查询)
select e.*,sumsal from emp e, (select sum(nvl(sal,0)) sumsal, deptno
from emp group by deptno) se where se.sumsal >(select avg(sum(nvl(sal,0))) from emp group by deptno) and se.deptno = e.deptno;
29.
查询没有员工的部门信息
SQL> select * from dept where deptno not in(select deptno from emp);
30.
查询用户（users表）huxz
所下所有订单编号，下单日期，总价格(orders表)，并包括订单中的商品数量（orderitem表），名称（product
表），价格（product表）
Select pr.PRODUCTNAME,qty,pr.PRODUCTID,orei,od,tp
from product pr, (Select QTY qty,PRODUCTID,ore.ORDERID orei,od,tp from orderitem
oi, (Select ORDERDATE od, ORDERID , TOTALPRICE tp from orders where username=(select username from users where username='huxz') ) ore where ore.ORDERID= oi.ORDERID) ori where ori.PRODUCTID=pr.PRODUCTID;
31.
查询100001号商品被哪些顾客（users表）购买过，下单日期(orders表)，每人购买的数量（orderitem表），购买时的价格（product表）
Select
ORDERDATE , TOTALPRICE, ords, qty
from orders ors,(Select
ORDERID, QTY qty, ORDERID
ords from
orderitem ord,( select
PRICE from product where
PRODUCTID='100001')
where ord.PRODUCTID='100001' )
where ors.ORDERID= ords;
32.
查询出哪些商品从未被订购过
select * from product pro left join (select distinct PRODUCTID from orderitem) ord on pro.PRODUCTID=ord.PRODUCTID where ord.PRODUCTID is
null;
33.
查询出被订购过 2次以上的商品信息
select p.* from product p where (select count(*) from orderitem where productid = p. productid) >= 2;
第三部分：
01.
tmp表中有如下记录（建表 SQL见emp.sql）
RQ
SHENGFU
---------- ----------
2005-05-09 WIN
2005-05-09 WIN
2005-05-09 LOSE
2005-05-09 LOSE
2005-05-10 WIN
2005-05-10 LOSE
2005-05-10 LOSE
要求格式为：
RQ
WIN LOSE
---------- ---------- ----------
2005-05-10
1 2
2005-05-09
2 2
答案：select rq,
count(case when shengfu='WIN' then 'WIN' else null end) WIN, count(case when shengfu='LOSE' then 'LOSE' else null end) LOSE from tmp group by rq ;
02.
查询当前月有多少天
答案：SQL> select trunc(add_months(sysdate,1),'month') - trunc(sysdate,'month') from dual;
03.
pages表有四个字段，id, url,title,body。如图：
ID
URL TITLE
BODY
--------- ----------- ------------------------- ------
1
http://www.baidu.com 新闻
党报评事业单位发绩效工资 砸铁饭碗再砸金饭碗
2
http://www.sina.com baidu新闻
假唱假演奏最高罚款3000元 10月1日起施行
3
http://www.yahoo.com 搜索结果
www.baidu.com/search/url_submit.html - 网页快4
http://www.baidu.com 新闻
垃圾焚烧产生致癌物 专家告诫中国勿重蹈日本覆辙
要求格式为：
ID
CUNT