1 / 20

SELECT Statements

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.

vance-wynn
Download Presentation

SELECT Statements

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SELECT Statements Lecture Notes Sree Nilakanta Fall 2010 (rev)

  2. SELECT Statements • Retrieve data from one or more tables, object tables, views, object views, or snapshots • Must have SELECT privilege • Examples

  3. Examples • Selects rows from the employee table with the department number of 40. • SELECT * FROM emp WHERE deptno = 40

  4. 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)

  5. Examples • Selects from subqueries in the FROM clause and gives departments total employees and salaries as a percentage of all the departments:

  6. 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 ;

  7. 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;

  8. 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;

  9. 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.

  10. 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;

  11. ORDER BY Clause • order the rows selected by a query • can specify multiple expressions in the ORDER BY clause • sorts rows based on their values for the first expression

  12. Order by clause restrictions • compound queries (containing set operators UNION, INTERSECT, MINUS, or UNION ALL), the ORDER BY clause must use positions, rather than explicit expressions • cannot appear in subqueries within other statements

  13. Order by examples • Select all salesmen's records from EMP, and order the results by commission in descending order. • SELECT * FROM emp WHERE job = 'SALESMAN' ORDER BY comm DESC;

  14. Order by examples • Select the employees from EMP ordered first by ascending department number and then by descending salary. • SELECT ename, deptno, sal FROM emp ORDER BY deptno ASC, sal DESC;

  15. Order by examples • Use the positional ORDER BY notation • SELECT ename, deptno, sal FROM emp ORDER BY 2 ASC, 3 DESC;

  16. Joins • A query that combines rows from two or more tables, views, or snapshots • Performs a join whenever multiple tables appear in the query's FROM clause • List any columns from any of these tables • If any these tables have a common column name, these columns must be prefixed with table names to avoid ambiguity.

  17. Joins Examples • Returns the name and job of each employee and the number and name of the department in which the employee works. • SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno;

  18. Joins examples • Returns the name, job, department number, and department name of all clerks. • SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND job = 'CLERK';

  19. Joins: Self Joins • Returns the name of each employee along with the name of the employee's manager. • SELECT e1.ename||' works for '||e2.ename "Employees and their Managers" FROM emp e1, emp e2 WHERE e1.mgr = e2.empno;

  20. Joins: Cartesian Products • have no join condition • combines each row of one table with each row of the other • generates many rows and is rarely useful

More Related