Oracle常用查询语句

 

/*--------------------------------'s_emp表、s_dept表、s_region表'-------------------------------*/

--1、找出所有员工的职称(title),不允许出现重复项;
select title 职称 from s_emp group by title;
-------------2种方法
select distinct title 职称 from s_emp;
--2、找出每位员工的年薪[含基本工资和奖金,每年发12个月工资]
select first_name 员工,salary*(1+nvl(commission_pct/100,0))*12 年薪 from s_emp;
--3、显示每位员工的全名、职称、提成,没有提成的员工要显示"没有提成"
select last_name||'  '||first_name 员工,title 职称,nvl(to_char(commission_pct*salary/100),'没有提成') 提成 from s_emp;
--4、找出工资超过1500元的员工,并按工资降序排序
select first_name 员工,salary 工资 from s_emp where salary>1500 order by salary desc;
--5、找出年薪超过25000的员工,并按年薪降序排列
select first_name 员工,salary*(1+nvl(commission_pct,0)/100)*12 年薪 from s_emp
where salary*(1+nvl(commission_pct,0)/100)*12>25000
order by salary*(1+nvl(commission_pct,0)/100)*12 desc;
--6、找出在41部门并且工资超过1200元的员工
select first_name 员工,dept_id 部门,salary 工资 from s_emp
where dept_id=41 and salary>1200;
--7、找出在10、31、50部门或者职称中以'VP'开头的员工
select first_name 员工,dept_id 部门,title 职称 from s_emp
where dept_id in (10,31,50) or title like 'VP%';
--------------2种方法
select first_name 员工,dept_id 部门,title 职称 from s_emp
where dept_id=10 or dept_id=31 or dept_id=50 or title like 'VP%';
--8、找出first_name中,第二个字母是'e'的员工
select first_name 员工 from s_emp where first_name like '_e%';
--9、找出所有91年入职的员工,并按入职时间升序排列
select first_name 员工,start_date 入职时间 from s_emp where start_date like '%91'
order by start_date asc;
-------------2种方法
select first_name 员工,start_date 入职时间 from s_emp
where to_char(start_date,'fmrr')='91' order by start_date asc;
--10、找出2月份入职的员工
select first_name 员工,start_date 入职时间 from s_emp
where to_char(start_date,'fmmm')='2';
--11、找出当月月份入职的员工
select first_name 员工,start_date 入职时间,sysdate 系统时间 from s_emp
where to_char(start_date,'fmmm')=to_char(sysdate,'fmmm');
--12、找出每位员工入职起至现在,总共各工作了多少个月
select first_name 员工,months_between(sysdate,start_date) 工作了多少个月 from s_emp;
--13、找出所有员工的first_name,并把'a'全部替换成'A'
select first_name 员工,replace(first_name,'a','A') 替换后的员工名 from s_emp;
--14、找出所有员工的first_name,但只显示前三个字母
select first_name 员工,substr(first_name,1,3) 截取后的员工名 from s_emp;
--15、找出last_name长度刚好为5的员工
select last_name 员工 from s_emp where length(last_name)=5;
--16、找出Sales部门的员工,并按员工姓名进行排序输出
select e.first_name 员工,d.name 部门名 from s_emp e join s_dept d on e.dept_id=d.id
where upper(d.name)='SALES' order by e.first_name;
--17、找出设立在Asia区域的部门
select d.id 部门编号,d.name 部门名,r.name 区域名 from s_dept d join s_region r
on d.region_id=r.id where lower(r.name)='asia';
--18、找出在Asia区域工作的员工
select e.first_name 员工,r.name 区域名 from s_dept d join s_region r on d.region_id=r.id
                      join s_emp e on d.id=e.dept_id
                      where lower(r.name)='asia';
--19、找出每位员工及其上司的名字,没有上司的员工请显示出'没有上司'
select w.first_name 员工,nvl(m.first_name,'没有上司') 员工的上司 from s_emp w left join s_emp m
                on w.manager_id=m.id;
--20、找出各区域所设定的部门数,显示出区域名称和部门数量
select r.name 区域,count(*) 部门数 from s_region r join s_dept d on r.id=d.region_id group by r.name;
--21、找出超出3个员工的部门编号
select dept_id 部门编号,count(*) 员工数 from s_emp group by dept_id having count(*)>3;
--22、统计出各部门的人员,并按人数的降序排序
select s_dept.name 部门名,count(*) 员工数 from s_emp join s_dept on s_emp.dept_id=s_dept.id
group by s_dept.name order by count(*) desc;
--23、统计各职称的人数
select title 职称,count(*) 人数 from s_emp group by title;
--24、显示所有薪金高于各自部门平均薪金的人
select first_name 员工,dept_id 部门,salary 薪金 from s_emp e1 where salary>(
select avg(salary) from s_emp e2 group by dept_id having e1.dept_id=e2.dept_id)
order by dept_id desc;
--25、显示薪金最高的3位员工
select * from(
select first_name,salary from s_emp order by salary desc)inner_ where rownum<=3;
--26、显示10部门薪金前两名的员工
select * from(
select dept_id,salary from s_emp where dept_id=41 order by salary desc)inner_ where rownum<=2;
--27、显示各部门工资前两位的员工
select first_name 员工,dept_id 部门,salary 工资 from s_emp e1 where
(select count(*) from s_emp e2 where e1.dept_id=e2.dept_id
and e1.salary<e2.salary)<2 order by dept_id;
--28、查询出41部门的第二至第三条记录
select * from(
select id 人员编号,dept_id 部门编号,rownum row_ from s_emp where dept_id=41) where row_<4 and row_>1;
--29、删除没有员工的部门
--30、删除入职年限低于20年的员工
--31、找出所有员工全名,所有部门名的组合
select e.last_name||''||e.first_name 员工,d.name 部门名 from s_emp e join s_dept d on e.dept_id=d.id;
--32、找出所有部门名、及它所在的区域名
select d.name 部门名,r.name 区域名 from s_dept d join s_region r on d.region_id=r.id;
--33、列出区域名及在区域的所有客户的名字,电话号码
select s_region.name 区域,s_customer.name 客户名,s_customer.phone 客户电话 from s_region,s_customer
where s_region.id=s_customer.region_id;
--34、找出在Sales部门工作的员工全名,入职时间
select e.first_name||''||e.last_name 员工名,e.start_date 入职时间,d.name 部门名
from s_dept d join s_emp e on d.id=e.dept_id;
--35、找出5月份入职的员工
select last_name 员工,start_date 入职时间 from s_emp where to_char(start_date,'fmmm')='5';
--36、找出当前月份入职的员工
select last_name 员工,start_date 入职时间 from s_emp where to_char(start_date,'fmmm')=to_char(sysdate,'fmmm');
--37、找出所有90年入职的员工
select last_name 员工,start_date 入职时间 from s_emp where start_date like '%90';
--38、找出92年上半年或91年下半年入职的员工
select last_name 员工,start_date 入职时间 from s_emp
where start_date between to_date('01-06-91','dd-mm-rr') and to_date('30-06-92','dd-mm-rr');
--39、找出平均工资超过2000元的部门编号
select dept_id 部门编号,avg(salary) 平均工资 from s_emp group by dept_id having avg(salary)>2000;
--40、找出43部门中最高、最低、平均工资
select dept_id 部门,max(salary) 最高工资,min(salary) 最低工资,avg(salary) 平均工资
from s_emp where dept_id=43 group by dept_id;
--41、找出平均工资在1200至1450之间的部门
select dept_id 部门编号,avg(salary) 平均工资 from s_emp group by dept_id having avg(salary) between 1200 and 1450;
--42、找出拥有订单数量超过1的客户
select s_customer.name 客户,count(*) 订单数 from s_ord join s_customer on s_ord.customer_id=s_customer.id
group by s_customer.name having count(*)>1;
--43、找出没有任何订单的客户 姓名
select name 客户 from s_customer where id not in (select customer_id from s_ord group by customer_id);
--44、找出设在亚洲的部门信息
select d.* from s_dept d join s_region r on d.region_id=r.id
where lower(r.name)='asia';
--45、得到每个部门工资最高的员工信息
select last_name 员工,dept_id 部门,salary 工资 from s_emp e1 where
(select count(*) from s_emp e2 where e1.dept_id=e2.dept_id and e1.salary<e2.salary)<1;
--46、找出各职称(title)中工资最高的员工
select last_name 员工,title 职称,salary 工资 from s_emp e1 where
(select count(*) from s_emp e2 where e1.title=e2.title and e1.salary<e2.salary)<1;

/*--------------------'prefix_product表、prefix_customer表、prefix_purcase表'------------------*/

--（1）求购买了供应商"宝洁"产品的所有顾客；
select distinct cus.name 顾客,pro.provider 产品 from prefix_purcase pur join prefix_customer cus
                on pur.customerid=cus.customerid        join  prefix_product pro
                on pur.productid=pro.productid     where pro.provider='宝洁';
--（2）求购买的商品包含了顾客"Dennis"所购买的所有商品的顾客（姓名）
select name from prefix_customer where customerid in (
select customerid from prefix_purcase
where productid in
(select pc.productid from prefix_customer c, prefix_purcase pc where c.customerid = pc.customerid and c.name='Dennis')
group by customerid
having count(productid)>=
(select count(pc.productid) from prefix_customer c, prefix_purcase pc where c.customerid = pc.customerid and c.name='Dennis')
) and name !='Dennis';             
--（3）求牙膏卖出数量最多的供应商。
select * from (
select inner_.provider,avg(quantity)*count(inner_.provider) from(
select prefix_product.provider,prefix_purcase.quantity from prefix_product,prefix_purcase
where prefix_product.productid=prefix_purcase.productid and prefix_product.category='牙膏'
group by prefix_product.provider,prefix_purcase.quantity)inner_
group by inner_.provider order by avg(quantity)*count(inner_.provider) desc)out_
where rownum<2;
--（4）将所有的牙膏商品单价增加10%。
--查看:select category,unitprice from prefix_product where category='牙膏'
update prefix_product set unitprice=unitprice*(1+10/100) where category='牙膏';
--（5）删除从未被购买的商品记录。
delete from prefix_product where productid not in(
select productid from prefix_purcase group by productid);