SELECT StatementsSELECT语句.pptVIP

SELECT Statements Lecture Notes Sree Nilakanta Fall 2010 (rev) * * SELECT Statements Retrieve data from one or more tables, object tables, views, object views, or snapshots Must have SELECT privilege Examples * * Examples Selects rows from the employee table with the department number of 40. SELECT * FROM emp WHERE deptno = 40 * * Examples selects the name, job, salary and department number of all employees except salesmen from department number 30: SELECT ename, job, sal, deptno FROM emp WHERE NOT (job = SALESMAN AND deptno = 30) * * Examples Selects from subqueries in the FROM clause and gives departments total employees and salaries as a percentage of all the departments: * * SELECT a.deptno Department, a.num_emp/b.total_count %Employees, a.sal_sum/b.total_sal %Salary”FROM(SELECT deptno, COUNT(*) num_emp, SUM(SAL) sal_sumFROM scott.empGROUP BY deptno) a,(SELECT COUNT(*) total_count, SUM(sal) total_salFROM scott.emp) b ; * * Group by Examples Return the minimum and maximum salaries for each department in the employee table, issue the following statement. SELECT deptno, MIN(sal), MAX(sal)FROM emp GROUP BY deptno; * * Group by examples Return the minimum and maximum salaries for the clerks in each department, issue the following statement. SELECT deptno, MIN(sal), MAX(sal) FROM emp WHERE job = CLERK’GROUP BY deptno; * * HAVING Clause Restrict which groups of rows defined by the GROUP BY clause. Oracle removes all rows that do not satisfy WHERE clause, calculates and forms the groups as specified in the GROUP BY clause, removes all groups that do not satisfy the HAVING clause. * * Having clause Return the minimum and maximum salaries for the clerks in each department whose lowest salary is below $1,000. SELECT deptno, MIN(sal), MAX(sal) FROM emp WHERE job = CLERK GROUP BY deptno HAVING MIN(sal) 1000; * * ORDER BY Clause order the rows selected by a query can specify multiple expressions in the ORDER BY clause sorts rows based on