---扩充：查找S_EMP表中员工userid为**的入职时间**的信息---
select * from s_emp；
select userid ||'的入职时间为'|| start_date from S_EMP;
select userid ||'的入职时间为'|| to_char(e.start_date,'yyyy-mm-dd') from s_emp e;
--习题6：找出S_EMP表中，START_DATE是91年的日期，并且以2017.3.28此种格式输出
select * from s_emp e where e.start_date like '%91';
select to_char(e.start_date,'yyyy.mm.dd') "start_date" from s_emp e where e.start_date like '%91';
--习题8：请统计出表S_ORD中，11号销售人员（SALES_REP_ID=11）的顾客数量,以及他的TOTAL平均值
select * from s_ord e where e.sales_rep_id like '11';
select count(*) "SALES_REP_ID=11的顾客数量",avg(e.total) "TOTAL平均值" from s_ord e where e.sales_rep_id like '11';
select substr(e.last_name,2,4),e.last_name from s_emp e;/*选取字符串，从第2位字母开始取4位*/
select length(e.last_name),e.last_name from s_emp e;/*字符长度*/
select round(123.4567,2)from dual;/*四舍五入，保留两位小数，得123.46*/
select round(183.4567,-2)from dual;/*四舍五入，整数两位取整，得200*/
select trunc(123.4567,2)from dual;/*截取，保留两位小数，得123.45*/
select trunc(123.4567,-2)from dual;/*截取，整数位第二位取整，得100*/
select trunc(123.4567,0)from dual;/*截取，得123*/
select round(123.9567,0)from dual;/*四舍五入，得124*/
--习题7：从表S_CUSTOMER,S_REGION中查找出顾客表的NAME、REGION_ID列和区域表的name列，REGION_ID为5号区域
select a.name,a.region_id,e.name from s_customer a,s_region e where region_id = 5;
select * from s_customer a,s_region e where region_id = 5;
select * from s_customer;
select * from s_customer,s_region;
select * from s_emp e ;
select * from s_emp e where e.salary>(select t.salary from s_emp t where t.first_name = 'Ben');
select * from s_emp e where e.salary>1100;
insert into s_dept values(51,'it',5);
commit;
--左、右、全连接
select * from s_emp e left outer join s_dept d on e.dept_id = d.id;
select * from s_emp e right outer join s_dept d on e.dept_id = d.id;
select * from s_emp e full outer join s_dept d on e.dept_id = d.id;
--习题10：请用左外连接方式重写以下事例
select e.last_name, e.id, c.name
from s_emp e
right outer join s_customer c
on e.id = c.sales_rep_id
order by e.id;/*右外连接方式*/
select c.name, e.last_name, e.id
from s_customer c
left outer join s_emp e
on e.id = c.sales_rep_id
order by e.id; /*左外连接方式*/
--查询平均薪资高于32号部门平均薪资的部门号和平均薪资
--1、子查询
select e.dept_id, avg(salary)
from s_emp e
group by e.dept_id
having avg(salary) > (select avg(e.salary) from s_emp e where e.dept_id = 32);/*分组查询，用having限定条件*/
select avg(e.salary) from s_emp e where e.dept_id = 32;/*取32号部门的平均工资*/
--2、多表查询
select e.dept_id,avg(e.salary)
from s_emp e,s_emp t
where t.dept_id = 32
group by e.dept_id
having (avg(e.salary) > avg(t.salary));
--3、内链接
select e.dept_id,avg(e.salary)
from s_emp e inner join s_emp t on t.dept_id = 32
group by e.dept_id
having avg(e.salary) > avg(t.salary);
--4、外连接
select e.dept_id,avg(e.salary)
from s_emp e left join s_emp t on t.dept_id = 32
group by e.dept_id
having (avg(e.salary) > avg(t.salary));
--习题11：在表s_emp中，查询出title列和非'VP'开头的title薪水总和列，此列用别名 PAYROLL显示，
--查询出该职位的所有员工薪水总和需要大于5000，PAYROLL列按升序排列
select e.title,sum(e.salary) payroll
from s_emp e
where e.title not like 'VP%'
group by e.title
having sum(e.salary)>5000
order by payroll;
--习题12：在表s_emp表中，查询出列last_name,title,salary，请仅使用where子查询方法，
--不能使用group by和having子句，查询出小于salary平均值的员工信息
select e.last_name,e.title,e.salary from s_emp e where e.salary < (select avg(e.salary) from s_emp e);
create table emp_41
(id number(7),
last_name varchar(25));
insert into emp_41;
select id,last_name from s_emp where dept_id = 41;
delete from emp_41;
select * from emp_41 t;
drop table emp_41;
truncate table emp_41;
insert into emp_41;
select id ,last_name from s_emp where dept_id =41;
savepoint A;
update emp_41 set id = 10 where id = 2;
savepiont B;
delete from emp_41;
savepiont C;
rollback to B;
rollback to A;
---扩充：查找S_EMP表中员工userid为**的入职时间**的信息---
select * from s_emp；
select userid ||'的入职时间为'|| start_date from S_EMP;
select userid ||'的入职时间为'|| to_char(e.start_date,'yyyy-mm-dd') from s_emp e;
--习题6：找出S_EMP表中，START_DATE是91年的日期，并且以2017.3.28此种格式输出
select * from s_emp e where e.start_date like '%91';
select to_char(e.start_date,'yyyy.mm.dd') "start_date" from s_emp e where e.start_date like '%91';
--习题8：请统计出表S_ORD中，11号销售人员（SALES_REP_ID=11）的顾客数量,以及他的TOTAL平均值
select * from s_ord e where e.sales_rep_id like '11';
select count(*) "SALES_REP_ID=11的顾客数量",avg(e.total) "TOTAL平均值" from s_ord e where e.sales_rep_id like '11';
select substr(e.last_name,2,4),e.last_name from s_emp e;/*选取字符串，从第2位字母开始取4位*/
select length(e.last_name),e.last_name from s_emp e;/*字符长度*/
select round(123.4567,2)from dual;/*四舍五入，保留两位小数，得123.46*/
select round(183.4567,-2)from dual;/*四舍五入，整数两位取整，得200*/
select trunc(123.4567,2)from dual;/*截取，保留两位小数，得123.45*/
select trunc(123.4567,-2)from dual;/*截取，整数位第二位取整，得100*/
select trunc(123.4567,0)from dual;/*截取，得123*/
select round(123.9567,0)from dual;/*四舍五入，得124*/
--习题7：从表S_CUSTOMER,S_REGION中查找出顾客表的NAME、REGION_ID列和区域表的name列，REGION_ID为5号区域
select a.name,a.region_id,e.name from s_customer a,s_region e where region_id = 5;
select * from s_customer a,s_region e where region_id = 5;
select * from s_customer;
select * from s_customer,s_region;
select * from s_emp e ;
select * from s_emp e where e.salary>(select t.salary from s_emp t where t.first_name = 'Ben');
select * from s_emp e where e.salary>1100;
insert into s_dept values(51,'it',5);
commit;
--左、右、全连接
select * from s_emp e left outer join s_dept d on e.dept_id = d.id;
select * from s_emp e right outer join s_dept d on e.dept_id = d.id;
select * from s_emp e full outer join s_dept d on e.dept_id = d.id;
--习题10：请用左外连接方式重写以下事例
select e.last_name, e.id, c.name
from s_emp e
right outer join s_customer c
on e.id = c.sales_rep_id
order by e.id;/*右外连接方式*/
select c.name, e.last_name, e.id
from s_customer c
left outer join s_emp e
on e.id = c.sales_rep_id
order by e.id; /*左外连接方式*/
--查询平均薪资高于32号部门平均薪资的部门号和平均薪资
--1、子查询
select e.dept_id, avg(salary)
from s_emp e
group by e.dept_id
having avg(salary) > (select avg(e.salary) from s_emp e where e.dept_id = 32);/*分组查询，用having限定条件*/
select avg(e.salary) from s_emp e where e.dept_id = 32;/*取32号部门的平均工资*/
--2、多表查询
select e.dept_id,avg(e.salary)
from s_emp e,s_emp t
where t.dept_id = 32
group by e.dept_id
having (avg(e.salary) > avg(t.salary));
--3、内链接
select e.dept_id,avg(e.salary)
from s_emp e inner join s_emp t on t.dept_id = 32
group by e.dept_id
having avg(e.salary) > avg(t.salary);
--4、外连接
select e.dept_id,avg(e.salary)
from s_emp e left join s_emp t on t.dept_id = 32
group by e.dept_id
having (avg(e.salary) > avg(t.salary));
--习题11：在表s_emp中，查询出title列和非'VP'开头的title薪水总和列，此列用别名 PAYROLL显示，
--查询出该职位的所有员工薪水总和需要大于5000，PAYROLL列按升序排列
select e.title,sum(e.salary) payroll
from s_emp e
where e.title not like 'VP%'
group by e.title
having sum(e.salary)>5000
order by payroll;
--习题12：在表s_emp表中，查询出列last_name,title,salary，请仅使用where子查询方法，
--不能使用group by和having子句，查询出小于salary平均值的员工信息
select e.last_name,e.title,e.salary from s_emp e where e.salary < (select avg(e.salary) from s_emp e);
create table emp_41
(id number(7),
last_name varchar(25));
insert into emp_41;
select id,last_name from s_emp where dept_id = 41;
delete from emp_41;
select * from emp_41 t;
drop table emp_41;
truncate table emp_41;
insert into emp_41;
select id ,last_name from s_emp where dept_id =41;
savepoint A;
update emp_41 set id = 10 where id = 2;
savepiont B;
delete from emp_41;
savepiont C;
rollback to B;
rollback to A;
存笔记