IST 318 – DB Administration

1. IST 318 – DB Administration Data Retrieval Using SELECT statements

2. Data Modeling • Entity: something exists and can be described • Attribute: a characteristic of an entity • Describes: most attributes play this role • Identifies: PK (or any other attr that is unique) • Relates: FK • Relationship: shows how entities associated with each other • May also have descriptive attributes

3. ER Diagrams • Notations for Entities: boxes • Attributes and keys (PK, FK) • Notations for Relationships: diamonds/lines • Constraints • Cardinality: 1:1, 1:M, M:1, M:N • Optionality • Mandatory: 1, 1..M • Optional: 0..1, 0..M

4. Conversion from ERM to Tables • Entities and relationships will be converted into tables in design • One entity  one table • Attribute  column • Domain  data type • Value range  range constraints • M:N Relationship  one table

5. SELECT Statement: basic structure • SELECT – specifying what to display • FROM – specifying where data come from • WHERE – specifying criteria for individual records • GROUP BY – specifying how to divide records into groups • Having – specifying criteria for groups • ORDER BY – specifying the way to sort results

6. SELECT Clause • May include columns or expressions • Expressions may include • Arithmetic operations • Single-row functions (maybe nested) • Group functions (maybe nested up to 2 levels) • Columns/expressions may be displayed using (column) aliases • Alias may be quoted with “” (double quotes) • AS keyword optional

7. FROM Clause • Include tables or views (to be discussed later) • Table aliases may be used • Once used, original table names may not be used to qualify columns in WHERE clause • Two syntax styles • Traditional: list table names directly, separated with colons (,) • Standard: using JOIN syntax (to be specified on separate slides)

8. WHERE Clause • Logical operators may be used to build composite conditions • AND, OR, NOT • Relational operators: =, <>/!=/^=, >, <, ... • IN (.., ..) • BETWEEN ... AND ... • IS NULL/IS NOT NULL • LIKE

9. GROUP BY Clause • May include columns or expressions • Expressions may not include group functions • Columns/expressions appeared in SELECT clause together with expressions w/ group functions must also appear here. But not the other way round. • May not use column aliases here • Will be carried out after criteria in the WHERE clause are applied

10. HAVING Clause • May only use expressions with group functions • May appear before or after the GROUP BY clause

11. ORDER BY Clause • Columns/expressions used here don’t have to appear in the SELECT clause • May sort to ascending (default) or descending order • May use multiple col/expr to sort, each to a different order • May sort by column aliases or location in the SELECT clause as well

12. Matching String Patterns • Use the LIKE operator, instead of = • May use NOT LIKE • Wildcard matching characters • _ : representing exactly one character/digit • % : represent any (0 .. many) characters • It is CASE-SENSITIVE • Use case manipulation functions to match up • WHERE upper(course#) like ‘IST%’

13. Single-Row Functions: for char • substr(col, start, length) • substr(col, -start, length): starting from the end • trim() • length() • ltrim/rtrim • lpad/rpad(col, length, c)

14. Single-Row Functions: for number • trunc(col, p) • p > 0 • p = 0 • p < 0 • round(col, p)

15. Single-Row Functions: Type Conversion • to_char(number, ‘L9,999.99’) • L is for local currency • $ may be used • to_char(date, 'fmMM/DD/YYYY') • SELECT to_char(sysdate, 'fmMM/DD/YYYY‘)FROM dual

16. Single-Row Functions: Generic • nvl(col, vn) • nvl2(col, vnn, vn) • case SELECT last_name, job_id, salary, CASE job_id WHEN 'IT_PROG' THEN 1.10*salary WHEN 'ST_CLERK' THEN 1.15*salary WHEN 'SA_REP' THEN 1.20*salary ELSE salary END "REVISED_SALARY" FROM employees;

17. Single-Row Functions: DECOE SELECT last_name, job_id, salary, DECODE(job_id, 'IT_PROG', 1.10*salary, 'ST_CLERK', 1.15*salary, 'SA_REP', 1.20*salary, salary) REVISED_SALARY FROM employees;

18. Nested Group Function • 2-level tops • Group by clause has to be used • No other expressions without nested group functions allowed to show in the same SELECT clause • SELECT max(avg(retail))FROM booksGROUP BY category;

19. JOIN Types • CROSS JOIN • NATURAL JOIN • INNER • Equality-joins • Non-equality-joins • Self-joins • OUTER: FULL/LEFT/RIGHT • Both the latter two need to use ON or USING to specify joining conditions

20. Subqueries • Used in WHERE or HAVING clauses • Single-value subqueries • Multiple-value subqueries