restricting and sorting data. ◦ limiting rows with: the where clause the comparison conditions...
TRANSCRIPT
![Page 1: Restricting and Sorting Data. ◦ Limiting rows with: The WHERE clause The comparison conditions using =,](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649ec45503460f94bced70/html5/thumbnails/1.jpg)
Restricting and Sorting Restricting and Sorting DataData
![Page 2: Restricting and Sorting Data. ◦ Limiting rows with: The WHERE clause The comparison conditions using =,](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649ec45503460f94bced70/html5/thumbnails/2.jpg)
◦Limiting rows with: The WHERE clause The comparison conditions using =, <=, BETWEEN, IN, LIKE, and NULL conditions
Logical conditions using AND, OR, and NOT operators
◦Rules of precedence for operators in an expression
◦Sorting rows using the ORDER BY clause
![Page 3: Restricting and Sorting Data. ◦ Limiting rows with: The WHERE clause The comparison conditions using =,](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649ec45503460f94bced70/html5/thumbnails/3.jpg)
Selection Vs projectionSelection Vs projection
![Page 4: Restricting and Sorting Data. ◦ Limiting rows with: The WHERE clause The comparison conditions using =,](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649ec45503460f94bced70/html5/thumbnails/4.jpg)
Limiting Rows Using a Limiting Rows Using a SelectionSelection
“retrieve allemployees in department 90”
EMPLOYEES
…
![Page 5: Restricting and Sorting Data. ◦ Limiting rows with: The WHERE clause The comparison conditions using =,](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649ec45503460f94bced70/html5/thumbnails/5.jpg)
Limiting the Rows that Are Limiting the Rows that Are SelectedSelectedRestrict the rows that are
returned by using the WHERE clause:
◦The WHERE clause follows the FROM clause.
SELECT *|{[DISTINCT] column|expression [alias],...}FROM table[WHERE condition(s)];
![Page 6: Restricting and Sorting Data. ◦ Limiting rows with: The WHERE clause The comparison conditions using =,](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649ec45503460f94bced70/html5/thumbnails/6.jpg)
Using the Using the WHEREWHERE Clause Clause
SELECT employee_id, last_name, job_id, department_idFROM employeesWHERE department_id = 90 ;
![Page 7: Restricting and Sorting Data. ◦ Limiting rows with: The WHERE clause The comparison conditions using =,](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649ec45503460f94bced70/html5/thumbnails/7.jpg)
Character Strings and Character Strings and DatesDates
◦ Character strings and date values are enclosed with single quotation marks.
◦ Character values are case-sensitive and date values are format-sensitive.
◦ The default date display format is DD-MON-RR.
SELECT last_name, job_id, department_idFROM employeesWHERE last_name = 'Whalen' ;
SELECT last_name FROM employeesWHERE hire_date = '17-FEB-96' ;
![Page 8: Restricting and Sorting Data. ◦ Limiting rows with: The WHERE clause The comparison conditions using =,](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649ec45503460f94bced70/html5/thumbnails/8.jpg)
Comparison OperatorsComparison Operators
•Not equal to<>
•Between two values (inclusive)
•BETWEEN...AND...
•Match any of a list of values •IN(set)
•Match a character pattern •LIKE
•Less than<
•Less than or equal to<=
•Greater than or equal to•>=
•Greater than>
•Equal to=•Meaning•Operator
![Page 9: Restricting and Sorting Data. ◦ Limiting rows with: The WHERE clause The comparison conditions using =,](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649ec45503460f94bced70/html5/thumbnails/9.jpg)
Using Comparison Using Comparison OperatorsOperators
SELECT last_name, salaryFROM employeesWHERE salary <= 3000 ;
![Page 10: Restricting and Sorting Data. ◦ Limiting rows with: The WHERE clause The comparison conditions using =,](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649ec45503460f94bced70/html5/thumbnails/10.jpg)
Range Conditions Using the Range Conditions Using the BETWEENBETWEEN Operator Operator
Use the BETWEEN operator to display rows based on a range of values:SELECT last_name, salaryFROM employeesWHERE salary BETWEEN 2500 AND 3500 ;
Lower limit Upper limit
![Page 11: Restricting and Sorting Data. ◦ Limiting rows with: The WHERE clause The comparison conditions using =,](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649ec45503460f94bced70/html5/thumbnails/11.jpg)
Membership Condition Using Membership Condition Using the the ININ Operator Operator
Use the IN operator to test for values in a list:SELECT employee_id, last_name, salary, manager_idFROM employeesWHERE manager_id IN (100, 101, 201) ;
![Page 12: Restricting and Sorting Data. ◦ Limiting rows with: The WHERE clause The comparison conditions using =,](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649ec45503460f94bced70/html5/thumbnails/12.jpg)
Pattern Matching Using the Pattern Matching Using the LIKELIKE Operator Operator
◦Use the LIKE operator to perform wildcard searches of valid search string values.
◦Search conditions can contain either literal characters or numbers: % denotes zero or many characters. _ denotes one character.
SELECT first_nameFROM employeesWHERE first_name LIKE 'S%' ;
![Page 13: Restricting and Sorting Data. ◦ Limiting rows with: The WHERE clause The comparison conditions using =,](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649ec45503460f94bced70/html5/thumbnails/13.jpg)
Combining Wildcard Combining Wildcard CharactersCharacters
◦ You can combine the two wildcard characters (%, _) with literal characters for pattern matching:
◦ You can use the ESCAPE identifier to search for the actual % and _ symbols.
SELECT last_nameFROM employeesWHERE last_name LIKE '_o%' ;
![Page 14: Restricting and Sorting Data. ◦ Limiting rows with: The WHERE clause The comparison conditions using =,](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649ec45503460f94bced70/html5/thumbnails/14.jpg)
Defining Conditions Using the Defining Conditions Using the Logical OperatorsLogical Operators
•Returns TRUE if the condition is false
• NOT
•Returns TRUE if either component condition is true
OR
•Returns TRUE if both component conditions are true
AND•Meaning•Operator
![Page 15: Restricting and Sorting Data. ◦ Limiting rows with: The WHERE clause The comparison conditions using =,](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649ec45503460f94bced70/html5/thumbnails/15.jpg)
Using the Using the ANDAND Operator Operator
AND requires both the component conditions to be true:
SELECT employee_id, last_name, job_id, salaryFROM employeesWHERE salary >= 10000AND job_id LIKE '%MAN%' ;
![Page 16: Restricting and Sorting Data. ◦ Limiting rows with: The WHERE clause The comparison conditions using =,](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649ec45503460f94bced70/html5/thumbnails/16.jpg)
SELECT employee_id, last_name, job_id, salaryFROM employeesWHERE salary >= 10000OR job_id LIKE '%MAN%' ;
Using the Using the OROR Operator Operator
OR requires either component condition to be true:
![Page 17: Restricting and Sorting Data. ◦ Limiting rows with: The WHERE clause The comparison conditions using =,](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649ec45503460f94bced70/html5/thumbnails/17.jpg)
SELECT last_name, job_idFROM employeesWHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP') ;
Using the Using the NOTNOT Operator Operator
![Page 18: Restricting and Sorting Data. ◦ Limiting rows with: The WHERE clause The comparison conditions using =,](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649ec45503460f94bced70/html5/thumbnails/18.jpg)
Lesson AgendaLesson Agenda◦Limiting rows with:
The WHERE clause The comparison conditions using =, <=, BETWEEN, IN, LIKE, and NULL operators
Logical conditions using AND, OR, and NOT operators
◦Rules of precedence for operators in an expression
◦Sorting rows using the ORDER BY clause
![Page 19: Restricting and Sorting Data. ◦ Limiting rows with: The WHERE clause The comparison conditions using =,](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649ec45503460f94bced70/html5/thumbnails/19.jpg)
Rules of PrecedenceRules of Precedence
You can use parentheses to override rules of precedence.
• Not equal to6
• NOT logical condition7
• AND logical condition8
• OR logical condition9
• IS [NOT] NULL, LIKE, [NOT] IN4
• [NOT] BETWEEN5
• Comparison conditions3
• Concatenation operator2
• Arithmetic operators1• Meaning•Operator
![Page 20: Restricting and Sorting Data. ◦ Limiting rows with: The WHERE clause The comparison conditions using =,](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649ec45503460f94bced70/html5/thumbnails/20.jpg)
SELECT last_name, job_id, salaryFROM employeesWHERE job_id = 'SA_REP'OR job_id = 'AD_PRES'AND salary > 15000;
Rules of PrecedenceRules of Precedence
SELECT last_name, job_id, salaryFROM employeesWHERE (job_id = 'SA_REP'OR job_id = 'AD_PRES')AND salary > 15000;
1
2
![Page 21: Restricting and Sorting Data. ◦ Limiting rows with: The WHERE clause The comparison conditions using =,](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649ec45503460f94bced70/html5/thumbnails/21.jpg)
Lesson AgendaLesson Agenda
◦Limiting rows with: The WHERE clause The comparison conditions using =, <=, BETWEEN, IN, LIKE, and NULL operators
Logical conditions using AND, OR, and NOT operators
◦Rules of precedence for operators in an expression
◦Sorting rows using the ORDER BY clause
![Page 22: Restricting and Sorting Data. ◦ Limiting rows with: The WHERE clause The comparison conditions using =,](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649ec45503460f94bced70/html5/thumbnails/22.jpg)
Using the Using the ORDERORDER BYBY Clause Clause
◦ Sort retrieved rows with the ORDER BY clause: ASC: Ascending order, default DESC: Descending order
◦ The ORDER BY clause comes last in the SELECT statement:
SELECT last_name, job_id, department_id, hire_dateFROM employeesORDER BY hire_date ;
…
![Page 23: Restricting and Sorting Data. ◦ Limiting rows with: The WHERE clause The comparison conditions using =,](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649ec45503460f94bced70/html5/thumbnails/23.jpg)
SortingSorting◦Sorting in descending order:
◦Sorting by column alias:
SELECT last_name, job_id, department_id, hire_dateFROM employeesORDER BY hire_date DESC ;
1
SELECT employee_id, last_name, salary*12 annsalFROM employeesORDER BY annsal ;
2
![Page 24: Restricting and Sorting Data. ◦ Limiting rows with: The WHERE clause The comparison conditions using =,](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649ec45503460f94bced70/html5/thumbnails/24.jpg)
SortingSorting◦ Sorting by using the column’s numeric position:
◦ Sorting by multiple columns:
SELECT last_name, job_id, department_id, hire_dateFROM employeesORDER BY 3;
3
SELECT last_name, department_id, salaryFROM employeesORDER BY department_id, salary DESC;
4