ist 318 – db administration data retrieval using select statements
TRANSCRIPT
![Page 1: IST 318 – DB Administration Data Retrieval Using SELECT statements](https://reader036.vdocuments.us/reader036/viewer/2022082820/5697bf761a28abf838c80c1c/html5/thumbnails/1.jpg)
IST 318 – DB Administration
Data Retrieval
Using SELECT statements
![Page 2: IST 318 – DB Administration Data Retrieval Using SELECT statements](https://reader036.vdocuments.us/reader036/viewer/2022082820/5697bf761a28abf838c80c1c/html5/thumbnails/2.jpg)
Data Modeling
Entity: something exists and can be describedAttribute: 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
![Page 3: IST 318 – DB Administration Data Retrieval Using SELECT statements](https://reader036.vdocuments.us/reader036/viewer/2022082820/5697bf761a28abf838c80c1c/html5/thumbnails/3.jpg)
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
![Page 4: IST 318 – DB Administration Data Retrieval Using SELECT statements](https://reader036.vdocuments.us/reader036/viewer/2022082820/5697bf761a28abf838c80c1c/html5/thumbnails/4.jpg)
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
![Page 5: IST 318 – DB Administration Data Retrieval Using SELECT statements](https://reader036.vdocuments.us/reader036/viewer/2022082820/5697bf761a28abf838c80c1c/html5/thumbnails/5.jpg)
SELECT Statement: basic structure
SELECT – specifying what to displayFROM – specifying where data come fromWHERE – specifying criteria for individual
recordsGROUP BY – specifying how to divide records
into groupsHaving – specifying criteria for groups ORDER BY – specifying the way to sort results
![Page 6: IST 318 – DB Administration Data Retrieval Using SELECT statements](https://reader036.vdocuments.us/reader036/viewer/2022082820/5697bf761a28abf838c80c1c/html5/thumbnails/6.jpg)
SELECT Clause
May include columns or expressionsExpressions 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
![Page 7: IST 318 – DB Administration Data Retrieval Using SELECT statements](https://reader036.vdocuments.us/reader036/viewer/2022082820/5697bf761a28abf838c80c1c/html5/thumbnails/7.jpg)
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)
![Page 8: IST 318 – DB Administration Data Retrieval Using SELECT statements](https://reader036.vdocuments.us/reader036/viewer/2022082820/5697bf761a28abf838c80c1c/html5/thumbnails/8.jpg)
WHERE Clause
Logical operators may be used to build composite conditions AND, OR, NOT
Relational operators: =, <>/!=/^=, >, <, ... IN (.., ..)BETWEEN ... AND ...IS NULL/IS NOT NULLLIKE
![Page 9: IST 318 – DB Administration Data Retrieval Using SELECT statements](https://reader036.vdocuments.us/reader036/viewer/2022082820/5697bf761a28abf838c80c1c/html5/thumbnails/9.jpg)
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 hereWill be carried out after criteria in the
WHERE clause are applied
![Page 10: IST 318 – DB Administration Data Retrieval Using SELECT statements](https://reader036.vdocuments.us/reader036/viewer/2022082820/5697bf761a28abf838c80c1c/html5/thumbnails/10.jpg)
HAVING Clause
May only use expressions with group functions
May appear before or after the GROUP BY clause
![Page 11: IST 318 – DB Administration Data Retrieval Using SELECT statements](https://reader036.vdocuments.us/reader036/viewer/2022082820/5697bf761a28abf838c80c1c/html5/thumbnails/11.jpg)
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
![Page 12: IST 318 – DB Administration Data Retrieval Using SELECT statements](https://reader036.vdocuments.us/reader036/viewer/2022082820/5697bf761a28abf838c80c1c/html5/thumbnails/12.jpg)
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%’
![Page 13: IST 318 – DB Administration Data Retrieval Using SELECT statements](https://reader036.vdocuments.us/reader036/viewer/2022082820/5697bf761a28abf838c80c1c/html5/thumbnails/13.jpg)
Single-Row Functions: for char
substr(col, start, length) substr(col, -start, length): starting from the end
trim()length()ltrim/rtrimlpad/rpad(col, length, c)
![Page 14: IST 318 – DB Administration Data Retrieval Using SELECT statements](https://reader036.vdocuments.us/reader036/viewer/2022082820/5697bf761a28abf838c80c1c/html5/thumbnails/14.jpg)
Single-Row Functions: for number
trunc(col, p) p > 0 p = 0 p < 0
round(col, p)
![Page 15: IST 318 – DB Administration Data Retrieval Using SELECT statements](https://reader036.vdocuments.us/reader036/viewer/2022082820/5697bf761a28abf838c80c1c/html5/thumbnails/15.jpg)
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
![Page 16: IST 318 – DB Administration Data Retrieval Using SELECT statements](https://reader036.vdocuments.us/reader036/viewer/2022082820/5697bf761a28abf838c80c1c/html5/thumbnails/16.jpg)
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;
![Page 17: IST 318 – DB Administration Data Retrieval Using SELECT statements](https://reader036.vdocuments.us/reader036/viewer/2022082820/5697bf761a28abf838c80c1c/html5/thumbnails/17.jpg)
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_SALARYFROM employees;
![Page 18: IST 318 – DB Administration Data Retrieval Using SELECT statements](https://reader036.vdocuments.us/reader036/viewer/2022082820/5697bf761a28abf838c80c1c/html5/thumbnails/18.jpg)
Nested Group Function
2-level topsGroup by clause has to be usedNo other expressions without nested
group functions allowed to show in the same SELECT clause SELECT max(avg(retail))
FROM booksGROUP BY category;
![Page 19: IST 318 – DB Administration Data Retrieval Using SELECT statements](https://reader036.vdocuments.us/reader036/viewer/2022082820/5697bf761a28abf838c80c1c/html5/thumbnails/19.jpg)
JOIN Types
CROSS JOINNATURAL JOININNER
Equality-joins Non-equality-joins Self-joins
OUTER: FULL/LEFT/RIGHTBoth the latter two need to use ON or
USING to specify joining conditions
![Page 20: IST 318 – DB Administration Data Retrieval Using SELECT statements](https://reader036.vdocuments.us/reader036/viewer/2022082820/5697bf761a28abf838c80c1c/html5/thumbnails/20.jpg)
Subqueries
Used in WHERE or HAVING clausesSingle-value subqueriesMultiple-value subqueries