Select statements
This presentation is the property of its rightful owner.
Sponsored Links
1 / 20

SELECT Statements PowerPoint PPT Presentation


  • 60 Views
  • Uploaded on
  • Presentation posted in: General

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.

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Select statements

SELECT Statements

Lecture Notes

Sree Nilakanta

Fall 2010 (rev)


Select statements1

SELECT Statements

  • Retrieve data from one or more tables, object tables, views, object views, or snapshots

  • Must have SELECT privilege

  • Examples


Examples

Examples

  • Selects rows from the employee table with the department number of 40.

  • SELECT * FROM emp WHERE deptno = 40


Examples1

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)


Examples2

Examples

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


Select statements

SELECT a.deptno "Department", a.num_emp/b.total_count "%Employees", a.sal_sum/b.total_sal "%SalaryFROM(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

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 examples1

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 = 'CLERKGROUP BY deptno;


Having clause

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 clause1

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


Order by clause restrictions

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


Order by examples

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;


Order by examples1

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;


Order by examples2

Order by examples

  • Use the positional ORDER BY notation

  • SELECT ename, deptno, sal FROM emp ORDER BY 2 ASC, 3 DESC;


Joins

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.


Joins examples

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;


Joins examples1

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


Joins self joins

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;


Joins cartesian products

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


  • Login