- 浏览: 124586 次
- 性别:
- 来自: 吉林
文章分类
- 全部博客 (118)
- oracle数据库 (5)
- Web前端 (3)
- java后台 (6)
- Java虚拟机 (3)
- 大数据 (1)
- Java (27)
- 微信公众号 (1)
- ssh框架 (2)
- Java,eclipse (1)
- Hibernate (4)
- SSH (4)
- asp.net (1)
- XML,JSON (1)
- junit (1)
- SpringMVC (2)
- XML (2)
- JSON (2)
- HTTP AJAX POST请求 (2)
- request payload (1)
- form data (1)
- Content Type (1)
- Spring RESTful (1)
- RESTful (2)
- Git (2)
- 企业软件版本管理工具 (1)
- HIVE,HADOOP (1)
- Hadoop (1)
- Dobbo (1)
- 缓存(cache) (1)
- 服务器端,客户端 (1)
- Cookie (1)
- Session (1)
- MySQL索引 (1)
- 乐观锁,悲观锁 (1)
- 电子商务 (1)
- 面试部分 (1)
- 分布式事务处理 (1)
- java详解 (1)
- commons (1)
- NoClassDefFoundErrorLogFactory (1)
- Spring (1)
- tomcat (3)
- Ubuntu (1)
- 虚拟机 (1)
- Intel Virtual Technology (1)
- SVN (4)
- Taglib (5)
- java unsigned类型 (1)
- fmt (1)
- HMAC (1)
- linux (13)
- mysql (2)
- FTP (4)
- Maven (5)
- spring boot (6)
- myeclipse (1)
- web (1)
- Https (2)
- ssl (3)
- crt (1)
- cer (1)
- byte (1)
- 字符占字节 (1)
- 限制IP远程登录 (1)
- 禁止某些 IP 访问 (1)
- iptables (1)
- linux私钥登陆 (1)
- windows (1)
- openSSH (2)
- 大牛博客地址 (1)
- Maven常见异常 (1)
- shell命令 (1)
- WebWork2,Spring MVC (1)
- LinuxSS (0)
- Bitvise SSH Client (1)
- -bash-4.1$ (1)
- Centos7 (1)
- MQTT (5)
- Mosquitto (3)
- Mosquittos (0)
- HAProxy (1)
- 集群 (0)
- 负载均衡 (1)
- moqutte (1)
- 多线程 (1)
- Queue (1)
- BlockingQueue (1)
- ConcurrentLinkedQueue (1)
- erlang (1)
- jersey (1)
最新评论
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;
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;
相关推荐
ORACLE经典语句汇总 -- 字符串左填充和右填充,默认填充空格 -- 产生1~99行数据,少于一位则补0 -- 刪除相同行 -- 随机数 -- 产生业务流水号 -- 查询某张表中有哪些字段 -- 自循环表中 由叶子节点查父节点 -- 查子...
ORACLE经典常用语句总结 ORACLE经典常用语句总结
经典的ORACLE查询语句教学资料 ORACLE SQL MYSQL JAVASE SWING
Oracle 的经典查询语句,有46 道题目,和相应的sql 语句 ,包含建表语句,在MySQL 里面也可以使用,需要将建表语句里面的 number(4,2) 修改为int(4) ,varchar2修改为varchar
很经典的oracle操作语句
oracle常用经典sql语句,查询经典语句集锦
oracle常用经典数据库管理sql语句,涵盖数据库日常维护和管理的SQL语句。
很经典哦,对大家做报表都很有帮助!
这是我对Oracle建表语句中的经典部分进行的总结,希望对大家有帮助。
oracle经典sql语句
Oracle经典语句,是一些大家比较少用的ORACLE语句,一辈子受益
oracle的经典SQL语句收集,是用word文档整理出来的。
Oracle经典的SQL语句训练(100例)
oracle常用经典语句,用到oracle常用的语句总结,以及oracle的备份与恢复技术。
ORACLE经典语句ORACLE经典语句ORACLE经典语句ORACLE经典语句ORACLE经典语句ORACLE经典语句ORACLE经典语句ORACLE经典语句ORACLE经典语句ORACLE经典语句ORACLE经典语句
保证你面试绝对会用到的SQL查询语句,快准备下吧
经典语句 oracle经典语句 oracle经典语句 oracle经典语句 oracle经典语句 oracle经典语句
ORACLE经典常用语句,增加主键,外键,索引管理!