Oracle 10g SQL Fundamentals I(学习笔记二第4-6章)
第四节:分组函数: select [column,]group_function(column),... from table where condition group by column order by column; select avg(salary) ,max(salary),min(salary),sum(salary) from employees where job_id where '%REP%';select min(hire_date),max(hire_date) from employees;select count(*) from employees where department_id=50select count(commission_pct) from employees where department_id=80;select count( distinct department_id) from employees;select avg(nvl(commission_pct,0)) from employees; select department_id dept_id,job_id,sum(salary) from employees group by department_id,job_id; select department_id dept_id,max(salary) from employees group by department_id having max(salary)>1000 order by sum(salary); 第五章: 关联查询 交叉查询: 使用Using字句: select empno,ename,dname ,loc from dept join emp using (deptno); 备注两个表中均有deptno列. select e.empno,e.ename,d.dname ,d.loc from dept d join emp e using (deptno); 使用自然连接: select empno,ename,dname ,loc from dept natural join emp;使用On字句连接: select e.empno,e.ename,d.dname ,d.loc from dept d join emp e on (e.deptno=d.deptno) ;使用on进行自关联 select e.last_name emp,m.last_name mgr from employees e join employees m on (e.manager_id=m.employee_id) ;带条件的关联: select e.empno,e.ename,d.dname ,d.loc from dept d join emp e on (e.deptno=d.deptno) and e.deptno=24;使用on多个表关联 select employee_id,city,department_name from employees ejoin departments don d.department_id =e.department_idjoin locations l on d.location_id=l.location_id;使用on作为条件的非连接用法 select last_name,e.salary ,j.grade_level from employees e join job_grades j on e.salary between j.lowest_sal and j.highest_sal;左外连接: select e.last_name,e.department_id,d.department_name from employees e left outer join departments d on( e.department_id=d.department_id);右外连接 select e.last_name,e.department_id,d.department_name from employees e right outer join departments d on( e.department_id=d.department_id);全外连接: select e.last_name,e.department_id,d.department_name from employees e full outer join departments d on( e.department_id=d.department_id); 笛卡儿积形成原因: 1.连接的条件的无效.2.连接的条件遗漏3.两表记录交叉连接.第六章子查询 select last_name,job_id,salary from employees where job_id =(select job_id from employees where employee_id=141) and salary > ( select salary from employees where employee_id=143)带函数的子查询select last_name,job_id,salary from employeeswhere salary=(select min(salary) from employees); 带having条件的子查询 select deparment_id,min(salary) from employees group by department_id having min(salary)> ( select min(salary) from employees where department_id=50 );使用any操作 select employee_id,last_name,job_id,salary from employees where salary< any( select salary from employeeswhere job_id ='IT_PROG' ) and job_id<> 'IT_PROG';使用all操作 select employee_id,last_name,job_id,salary from employees where salary< all( select salary from employeeswhere job_id ='IT_PROG' ) and job_id<> 'IT_PROG';
?
