FIT2094 Lecture Notes - Lecture 7: Pattern Matching, Database
31 May 2018
School
Department
Course
Professor
W7: SQL!
ā¢Set Membership"
ā Totestwhetherthevalueofexpressionequalsoneofasetofvalues. ā Operator: "
ā¢ IN"
ā Example:cityIN(āMelbourneā,āSydneyā) "
ā¢Pattern Match"
ā Totestwhetherastring(text)matchesaspeciļ¬edpattern. ā Operator: "
ā¢ LIKE ā Patterns: !
ā¢% character represents any sequence of zero or more character. !
ā¢_ character represents any single character. ā Example: !
ā¢WHERE city LIKE āM%ā !
ā¢WHERE unitcode LIKE āFIT91__ā "
NULL"
ā To test whether a column has a NULL (unknown) !
value."
ā Example: WHERE grade IS NULL. !
ā¢ Use in subquery (to be discussed in the future) ā ANY, ALL !
ā EXISTS !
ā¢Predicate evaluation is done using three-valued logic. TRUE, FALSE and UNKNOWN !
ā¢DBMS will evaluate the predicate against each row. !
ā¢Row that is evaluated to be TRUE will be retrieved. !
ā¢NULL is considered to be UNKNOWN. "
Logical operators ā AND, OR, NOT !
ā¢ Rules:"
ā An expression is evaluated LEFT to RIGHT."
ā Sub-expression in brackets are evaluated ļ¬rst. ā NOTs are evaluated before AND and OR"
ā ANDs are evaluated before OR. !
ā¢āORDER BYā clause. !
ā¢Order can be ASCending or DESCending. The default is "
ASCending. !
ā¢NULL values can be placed ļ¬rst/last using āNULLS LASTā "
or āNULLS FIRSTā command !
ā¢Sorting can be done for multiple columns. "
ā order of the sorting is speciļ¬ed for each column. !
find more resources at oneclass.com
find more resources at oneclass.com
