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)matchesaspecifiedpattern. – 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 first. – 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 first/last using “NULLS LAST” "
or “NULLS FIRST” command !
•Sorting can be done for multiple columns. "
– order of the sorting is specified for each column. !
find more resources at oneclass.com
find more resources at oneclass.com
