第五讲 Orale语句基础.pptVIP

* * * * * * * * * * * 1 教材介绍： 比较经典的数据库教材 第一版中国最早的数据库教材，为众多高校计算机专业采用 2. 萨老师介绍 创建信息系，最早系主任 中国的C J, Date 3 王老师介绍 4 数据库所介绍 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * Grouping technique 2.　求出平均工资大于2000的部门编号和平均工资 SELECT deptno,AVG(sal) sal_avg FROM emp WHERE AVG(sal)2000 GROUP BY deptno HAVING AVG(sal)2000; 注： WHERE只能对单条记录限制， HAVING 是对分组进行过滤。 Grouping technique 3.　显示非销售人员工作名称以及从事同一工作雇员的月工资总和，并且要满足从事同一工作的雇员的月工资合计大于5000，输出结果按月工资合计升序排列。　 SELECT　job, SUM(sal) sal_sum FROM emp WHERE job’SALESMAN’ GROUP BY job HAVING SUM(sal)5000 ORDER BY sal_sum; Execution procedure of SQL statement The order of execution of the SQL statement is not consistent with the syntax order of the statement. The syntax order of the SQL statement is:： SELECT [DISTINCT] FROM WHERE GROUP BY HAVING UNION ORDER BY The order of execution of the SQL statement is ： FROM WHERE GROUP BY HAVING SELECT DISTINCT UNION ORDER BY 4、Multi table retrieval Table alias SELECT emp.empno,emp.ename,dept.deptno,dept.loc FROM emp,dept WHERE emp.deptno=dept.deptno; SELECT e.empno,e.ename,d.deptno,d.loc FROM emp e,dept d WHERE e.deptno=d.deptno; DML---- SELECT 4、 Multi table retrieval (2) Internal connection select column_list from table_name1 [inner] join table_name2 on join_condition; select e.ename,e.job,e.sal,d.deptno,d.dname from emp e jion dept d on e.deptno=d.deptno; select e.ename,e.job,e.sal,d.deptno,d.dname from emp e , dept d where e.deptno=d.deptno; 4、 Multi table retrieval (3) External connection select column_list from table_name1 left [outer] join table_name2 on join_condition; left [outer] join right [outer] join full [outer] join 4、 Multi table retrieval (3) External connection---Practice SQLinsert into emp(empno,ename,job,sal) values(8000,’ATG’,’CLERK’,950); SQLselect e.ename,e.job,e.sal,d.deptno,d.dname from emp e left join dept d on e.deptno=d.deptno; right?full?Tell me about your results. SQLselect