130 likes | 147 Views
Restricting and Sorting Data. Kroenke, Chapter Two. Topics. Limiting rows selected (Where) Sorting selected rows (Order By). Limiting Rows Selected. SELECT [ DISTINCT ] { * | column [ alias ], … } FROM table [ WHERE condition (s) ] ;. Where Clause Can Compare. Values in columns
E N D
Restricting and Sorting Data Kroenke, Chapter Two From Introduction to Oracle:SQL and PL/SQL, Oracle
Topics • Limiting rows selected (Where) • Sorting selected rows (Order By) From Introduction to Oracle:SQL and PL/SQL, Oracle
Limiting Rows Selected SELECT [ DISTINCT ] { * | column [alias], … } FROM table [ WHERE condition (s) ]; From Introduction to Oracle:SQL and PL/SQL, Oracle
Where Clause Can Compare • Values in columns • Literal values • Arithmetic expressions, or • Functions From Introduction to Oracle:SQL and PL/SQL, Oracle
Character Strings & Dates • Enclosed in single quotation marks • Character valuescase sensitive • Date values • format sensitive • Default – DD-MON-YY From Introduction to Oracle:SQL and PL/SQL, Oracle
Comparison Operators • = • > • >= • < • <= • <> Syntax -- WHERE expr operator value From Introduction to Oracle:SQL and PL/SQL, Oracle
Examples From Introduction to Oracle:SQL and PL/SQL, Oracle
Other Comparison Operators • BETWEEN … AND … Between two values (inclusive) • IN(list) Matches any value in list of values • LIKE Match a character pattern • IS NULL Is a null value From Introduction to Oracle:SQL and PL/SQL, Oracle
LIKE Wildcards • % any sequence of zero or more characters • _ (underscore) any single character • ESCAPE Option to match wildcard characters From Introduction to Oracle:SQL and PL/SQL, Oracle
Logical Operators • AND True if both conditions True • OR True if either condition True • NOT • True if condition False • False if condition True • Null if condition Null From Introduction to Oracle:SQL and PL/SQL, Oracle
Operator Precedence Rules • All comparison operators • NOT • AND • OR Override rules with parenthesis. From Introduction to Oracle:SQL and PL/SQL, Oracle
ORDER BY Clause • Sorts rows • ASC ascending order (default) • DESC descending order • Must be last clause in Select statement From Introduction to Oracle:SQL and PL/SQL, Oracle
Order By Syntax SELECT expression FROM table [WHERE condition(s)] [ORDER BY {column, expr}|ASC|DESC||; From Introduction to Oracle:SQL and PL/SQL, Oracle