1 / 20

IST 318 – DB Administration

IST 318 – DB Administration. Data Retrieval Using SELECT statements. 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

evania
Télécharger la présentation

IST 318 – DB Administration

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related