Oracle Database 10g SQL Fundamentals I Part 1

11.6.06

Oracle Database 10g: SQL Fundamentals I Part 1


Database Management in Oracle 10g 2hr
Preasses1
Database Manage: Intro
Oracle 10g
Components: Oracle 10g
isql*plus overiew
isqp*plus: interaction with a script file
sql statements
Post Assess1
Basic SQL Statements 2hr
preasses
retrieving data
select statement
select statement: arith
select statement: formatting
restricting data
where clause
where clause: comparison conditions
where clause: logical
where clause: rules of prec
order by clause
substitution variables
postassess
SQL Functions 2hr
single row functions
single row functions: features
char func
num func
date func
conv func
nested func
general func
conditional func
group functions
group functions: type
group by
Advanced SQL Statements 2hr
Joins
Natural Joins
Equijoin
Self-join
Outer Join
Non-equijoins
cartesian products
Subqueries
subqueries: introduction
single-row subqueries
multiple-row subqueries
Set Operators
set operators: types



sdlc - system development lifecycle

4 types of databases
- hierarchical
- network
- relationship
- object relational

5 phases of sdlc
- strategy and analysis: requirements, interviews
- design: model created, requirements depicted in graphical
- build and documentation: database built as a prototype, commands to make & populate tables
- transition: refined prototype, tested for user acceptance
- production: system delivered to the user, analysze performance



11.29.06 7:30pm
ER model pg. 1-17

Uses of Data models
- comm database design considerations
- categorizing info
- describing working databases
- analyzing design implementations

an ER model separates data structures from business activiities, ensuring minimal changes

three elements used in ER model
- entity
- attribute
- relationship

an entity represents areas which info is required. For example, courses, teachers, students are entities for the school

an attribute defines features of an entity, such as student name

an element relationship, such as course and teacher, many to one, etc.

attribute naming conventions
* : mandatory
o : optional

symbols to depict relationships
-- dashed line: maybe
-- solid line: mandaotry
gt- crows foot: one or more
-- single line: one and only one

Products
Priced Products
Item
Order
Customer






7:35pm
RDBMS, pg. 1-13
modeling and storing data in 2d tables, contain columns. Data stored in rows
columns contain attributes of the relation
rows contain values of the relation
select * from ct_employee
empno,name,salary represents attributes about employees
rows contain values about single employee

RDBMS
- collection of objects or relations
- contains operators that process relations
- maintain data integrity
- enables data access and mods

normalization process - reduce multiple copies of the data
minimize repetition
reduce inconsistencies
very strict normalization can make some queries run slower

referential integrity
values in one column correspond to values in another table

primary key: attribute that uniquely identies a value (or row
foreign key: same attribute refereinced in another table





Oracle 10g Architecture, pg. 1-21
sql, pl/sql, java, xlm: avoid proprietary lock-ins
Grid Computing: resources are pooled together

three components
- oracle database 10g
- oracle application server 10g
- oracle enterprise manager 10g grid control

infrastructure platform: includes J2EE
server applications: portals, web sites, java transactional apps, business intelligence apps
grid environment: central management console, create single logical entity of many databases
grouping of target: execute jobs, enforce standard policies, monitor perf, automate jobs

core components of 10g platform
- browser based clients
- application servers
- databases

brower-gtapp logic-gtbusiness logic-gtdata
store data in relational structure, and support Java & XML. Has Pl/sql engine




isql*plus overiew, pg. 1-21
browser based interface
local & remote databases
create script files for future use

command categors
- environment
- format
- file manipulation
- execution
- edit
- interaction
- misc: commands, such as connect, and displaying structure

unlike SQL, where you can continue lines, isql requires a " - "
sql uses functions to format results, whereas isql uses environment command

to access isql*plus
http://host:5560/isqlplus
enter sql, pl/sql, and sql*plus
execute : load script: save script

Describe tablename - fix inconsistences? not avail in earlier oracle




isql*plus: interaction with scritp file, pg. 1-18
run recently used stuff with History tab
set preferences: history size (of scripts), input area size, ouptut location
script configuration: script formatting, script execution, database admin


post assessment, got 71%

SQL Statements, pg. 1-15
sql standard accepted by ansi & iso
sql english like, accessible by inexperienced programmers

4 functions:
- data manipulation DML: 5 : select, insert, update, delete, merge
- data definition DDL: 6: create, alter, rename, drop, ate, comment
- transaction control: 3 : commit, rollback, savepoint
- data control : 2: grant, revoke



11.30.06
Select statement, pg 1-14
three features:
- projection: use selective columns from tables
- selection: use selective rows from tables
- joining: combine, specifying links b/w tables

you can split statements over multiple lines, using indent to make more readable

Select : Arithmetic Expressions, pg. 1-17
can be used in any clause, except FROM clause
with DATE & TIMESTAMP, can only use additon and subtraction operators
select name, salary, salary+500
constraints: not null & primary keys
blank space as a separator to define an alias
select name "Employee Name", job
also define alias with AS keyword, not mandatory
select ename AS name


Select Statement: Formatting Results, pg. 1-14
concatenation of strings, using " || " operator
select ename || job as "Employee and job"
a literal can be a date, char, or number
you must enclose date & char literals in single quotes
select 'SENIOR' emptype, ename # SENIOR becomes value in emptype column
if you want to include a single quote in a literal, you have option
- use alternative quote operator, selecting your own quote mark delimiter
can be single or multi-byte
select ename || q'['s department number is ]'||



Where Clause, pg 1-9
select just a few rows from the table
row filtering


Where Clause: Comparison Conditions, pg 1-15
gt lt = gt=
WHERE sal BETWEEN 3000 and 5000
WHERE mgr IN (1,2,3,4)
where expr LIKE 'value%
where expr NOT LIKE value
escape option
where job like '%\_MAN' escape \'
where mgr is NULL

WHERE Clause: Logical Conditions, pg 1-12
and, or, not
NOT BETWEEN 1 and 3
not like '%A%'
is not null


Where Clause: Rules of Precedence, pg, 1-7
order of precedence
1 arthithmetic
2 concat
3 comparison
4 is null, like, in
5 between
6 not equal to
7 not logical condition
8 and
9 or

where job like 'M%' or job like 'A%' and sal gt 8000
hmm
1st condition is job like A% and sal gt 8000
2nd condition is job like M%


ORDER BY clause, pg 1-10
asc, desc
sort by alias, expression, or columns
sort in asc, lowest value first, nulls grouped at bottom


Substituion Variables, pg 1-20
isql*plus enables user interaction to provide values for vars at run time
preceded by a & or &&
supplement where, order by, column expressions, table names, or entire select statements
enabling you to provide where clause at runitme
can also extract values from a file
or pass values from one sql statement to another
where deptno=&deptno
# finally learning something new
Prompt given to user:
Input Required
Enter value for deptno: 30
no validation checks performed
include quote if variable refers to date or char values
ie. where job = '&job'
select &col1, &col2
can use anywhere except as first word in statement
&& when you want to reuse
use prompted only once in same user session
where job='%%job'
you can also use the define command
DEFINE var_empno = 123
select ...
use not prompted at runtime
you can delete them with UNDEFINE
isql*plus commands
SHOW ALL - list of all system settings
VERIFY ON - confirm changes in SQL statements
isql*plus shows old & new variables if VERIFY ON


12.01.06

SQL Functions 2hr
Single Row Functions, pg-1-9
- formatting dates & umbers
- modifying data values
an argument can be a column name, expressions, variable value
single row funcitions
- return a single result for each row
- operate on each row returned in the query
- accept one or more arguments
can be nested
can be used in select, where, or order-by
multiple-row functions operate on many rows, return one value
usually used for calculating summary & statistics

character functions, pg 1-28
3 case manipulation fn: lower, upper, initcap
example:
select lower(ename) from ct_emp where ename like 'D%'
select ename from ct_emp where lower(ename) = "allen"

character manipulation functions:
- concat
- substr(val, start_i, num_i) # extract
- length
- inst - is one string present in another, returns either a position, or a zero
- lpad - ensure strings of minimum length
- rpad
- replace - replaces part of one string with another # A1=str_to_srch, A2=str_srch_for A3=repl_str
- trim - trim padding, converse to lpad/rpad

select ename,instr(ename,'a',1) as "" return pos if a is contained in ename
select lpad(col_name,ltfinal length), pad_char)
select lpad(ename,20,'*') from
select ename, TRIM('D' from ename) from # if name starts with D, trim, otherwise don't
select ename, REPLACE(ename,'D','J') from


Number Functions, pg 1-13
ROUND,TRUNC,MOD

select round(47.937,2) from dual
select round(47.937,0) from dual = 48
select round(47.937,-1) from dual = 50

select trunc(47.937,2) from dual
select trunc(47.937,0) from dual = 47
select trunc(47.937,-1) from dual = 40

select mod(300,70) from dual = 20 # remainder


Date Functions, pg 1-22
internal format: 20,05,11,17,19,20,01
century,year,month,day,hours,minu,sec
default for input and display is DD-MM-RR
RR format = May 16,04
YY format = May 16,04

if two digit year is b/w 0-49, the year of the current century is returned. in RR format
select ename from ct_emp where hiredate gt '10-mar-85'

Date Functions
SYSDATE
MONTHS_BETWEEN # RETURNS A NO
ADD_MONTHS
NEXT_DAY
LAST_DAY - returns date of the last day of month
ROUND - according to format model
TRUNC

select sysdate from dual # returns current date & time
select SYSDATE, NEXT_DAY(SYSDATE,'SUNDAY') from 27-JUN-04

ROUND- format models: month,day,year,cc (century)
if nothing specified, use the day
select sysdate, ROUND(sysdate,'MONTH')

TRUNC - similar, with format model

arthrithmetic operators
- add or subtract a number to/ from date
- subtract two dates
- add hours to a date


Conversion Functions
implicit data type conversions
explicit data type conversions

implicit conversions for assignment
char -gt num
char -gt date
num -gt varchar2
date-gtvarchar2

implicit type conversions for expression eval
varchar-gtnum
varchar-gtdate

explicit conversions
to_char
to_number
to_date
cast

to_char(number|date, [fmt], [nlsparams])

elements of the date format model
yyyy - 1979
year - nineteen seventy nice
month - june
mon - jun
mm - 06
date - sunday
dd- 23
dy - sun
sp - spelled out number six

sample format elements of valid date formats
scc - returns the centry
y,yyy
iyyy,iyy,iy,i
bc or ad
b.c. or a.d.
q - returns the quart of year (1-4) JAN-MAR=1
rm - returns roman number month JAN=1 DEC=x11

guidelines for using the date format model
- enclose the format model in a single quotation marks
- specifiy a valid date format element in the appropriate case
- separate the date value from the format model by a comma
- remove padded blansk or leading zeroes using the fill mode fm element

select ename,to_char(hiredate,'fmDD Month YYYY') as hiredate # 17 December 1980

elements of the Time FOrmat Elements
HH or HH12 or HH24 - hour of the day, or hour from 1-12, or hour from 0-23
MI - minutes 0-59
SS - seconds from 0-59
SSSSS - seconds past midnight 0-86399
AM or PM
A.M or P.M.

select ename,to_char(hiredate, 'fmMonth DDth YYYY,fmHH:MI AM')


elements of athe number format model
9 represents a number
0 forces a zero to be display
$ places a floating dollar sign
L uses the floating local currency symbol
. displays a decimal point
, displays a comma as the thousands indicator
D returns the decimal char at the specified position

0 : displays leading zeros: 09999 = 05678
MI: returns minus sign to right: 9999MI = 5678-
PR: returns neg in angle brackets: 9999PR lt5678gt
V : multiply by 10 for n number of times, 9999V99 567800
S: returns the negat or positiv sign: S9999 -5678, or +5678

to_char(sal,'999,999') = 1,600



to_number(char,[fmt],[nlsparams])
select to_number('289.45',999D99') as numeric

to_date(char,[fmt],[nlsparams])'
where hiredate gt to_date('July 30,1981),'fxMonth DD,YYYY')

RR date format

CAST(
conversions between compatible & incompatible data types such as CHAR & BINARY, and VARCHAR & INT

select deptno, CAST(deptno AS VARCHAR2(4)) AS DEPARTMENT


Nested Functions, pg 1-9
you can nest single-row functions to any level
select ename, UPPER(SUBSTR(ename,1,6)) as ""
select INITCAP(CONCAT(RPAD(ename,10),job))
select ename, to_char(add_months(hiredate,6) 'Ddspth Month, YYYY) as ""


General Functions, pg 1-14
can work on any datatype
NVL - replace null value with actual value
NVL2,- accepts 3 args
NULLIF - accepts 2 args, compared, if =, return null
COALESCE - accepts multiple arguments, returns first-null expression in list

select ename,sal,nvl(comm,0)
select ename,nvl2(comm,'salary with commission','salary') as
sleect ename, nullif(nlv(comm,0),sal)
select ename, coalesce(mg,comm,-2)


conditional expressions, pg 1-12
used to invoke if-the-else
use either CASE or DECODE

select ename
case when sal lt=2450 then 'sal not taxable'
when sal gt 2450 then 'sal taxable'
else 'none
end "Grade"

select ename,job,sal
Decode(job, 'clerk', 1.20*sal,
'salesperson', 1.3 * sal,
'manager', 1.4 * sal,
sal # default ) as "New Salary
# selecting 3 columns, but result as 4 columns


Group Functions: Types, pg 1-21
calculation on collection of records to achieve a single result, ie. SUM(*)
avg, count, min, max, stddev, sum, variance
group functions allow
- consider only distinct values # default is all, including dups
- substitue null values # default is to exclude NULL, you can use NVL, NVL2, or COALESCE
- use columns with char, varchar2, number, or date
select avg(nvl(comm,0) ) as average
select count(distinct job) as

Group BY clause, pg 1-19
divides rows of a table into groups
you use group functions to obtain summary information
guidelines:
- ?
- where clause is used to specify rows that are not to be devided
select deptno,avg(sal)
from ct_employee
group by deptno
group by deptno, then calculate average salary by that group.
missing where clause, means considere all rows in table

you can create groups within groups
select deptno as "dept", job as "design", avg(sal) as "avg salary"
from ct_employee
group by deptno, job

WHERE clause to exclude rows before grouping
HAVING to exclude rows after grouping
Steps to be performed using the HAVING Clause
- group the rows
- apply group function on the group
- display groups that match the condition in the HAVING
select sum(sal) as "total salary"
from ct_employee
group by deptno
having sum(sal) gt= 5000
order by sum(sal)

can nest 2 group functions
select sum(avg(sal)) sas "total dept avg sal"
from ct_employee
group by deptno


select round() from dual;





Advanced SQL Statements
Joins
Natural Joins
Equijoins
self-join
outer join
non-equijoins
cartesian products
Subqueries
subqueries: intro
single row subqueries
multi-row subqueries
Set Operators
set operators: types


12.7.06
Natural Join, pg. 1-13
select from multiple tables requires logical links
a natural join connects two tables with identical column names
selects rows that have equal values in all matched columns
if columns having same names but different data types, an error is returned
to join data without any redundancy, tables must be normalized
4 types of joins:
- natural
- equijoins
- self joins
- outer joins


for natural join, use "natural join", columsn with same nmae & data type
A natural join is a type of equi-join and oin column does not display data redundantly

select table1.column, table2.column from table1
natural join table2

select table1.column, table2.column from table1
join table2 using (column_name) # performs an equijoin

select table1.column, table2.column from table1
join table2 ON(table1.column_name = table2.column_name)


equijoins, pg 1-19
AKA simple join, or inner join
uses equality operators
if the tables are having multiple common columns, then specify columns with USING clause
when using USING clause, no columns in WHERE clause can have qualifyer
such as a table name or alias
ie: join ct_department USING(deptno)
where ct_department.deptno = 10 # invalid
to avoid confusion about qualifying column names, use table prefixes
ie: select ct_employee.ename from ct_employee
join ct_department on (ct_employee.depno = ct_department.deptno)
use of prefix helps enhance performance of the server
while using USING clause, you can't quality column with table prefix or table prefix alias
? getting fuzzy
while table prefixes may help distinguishing the column belonging to different tables, writing prefixes can be tedious. A possible solution is a table alias

if you have many columns with the same name in the two tables,
you can use the ON clause to specify which particular columsn are to be joined
# where as for natural join, maybe can only have one redundant column?


the ON clause can also be used in two tables to be join with different column names
table1: A, B
table2: C, D
table3: A, B, C, C



Self-Join
return date twice, for same query, pg 1-8
get data from employee data considering as managers, and then as subordinates
use ON clause to perform self-join

select ...
from ct_employee e join ct_employee m
on (e.mgr = m.empno)
Subordinate, Manager ID, Emp#, Manager, Emp#
most useful where hierachical structure exists in the table
for example, if you want to find out a particular employee's maanager, you first locate that employee
you have to look at employee table twice
find employees's last name
then name under the manager code id


Outer Join, pg 1-9
get all records from one table, and only certain records from another
inner joins, only those that match the condition specified
- left outer join
- right outer join
- full outer join
ltoutergt is optional
left: returns all rows from first table, even if no matches with 2nd table
full outer joins returns all rows of both tables, even if no match


non-equijoin, pg 1-10
joined by something other than the equality (=) operator
different values in related columns
arithmetic comparison or logical comparison

ct_salgrade: grade, lowsal, highsal
ct_employee: ename, sal

select e.ename, e.sal, g.grade
from ct_employee e join ct_salgrade g
on e.sal between g.lowsal and g.highsal

considerations
- conditions: !=, gt gt= lt lt= and BETWEEN
- specify low and high values when using BETWEEN
- use table aliases




Subqueries: Introduction, pg 1-15
an advanced feature of SELECT statement
values based on a condition, obtained by writing subqueries in WHERE clause
sometimes more than one query is required to solve a problem
nest one query in another
subquery is a SELECT statement inside another Select,Insert,Update,Delete
and can be nested with the WHERE, Having, and From,
and specified in column list of a Select statement

in a simple subquery, the inner query is executed only once.
the value returned is passed to outer statement
subquery doesn't depend on values that would be returned if outer query executed by itself

with correlated subquery
inner query is executed more than once
and what it returns depends on stuff returned by outer query
it is executed once for each return by the outer query

for both simple & correlated subquery
comparisons: = gt lt IN ANY ALL
Exists and Not Exists used in Simple, not in correlated


select ename from ct_employee where sal gt
( select sal from ct_employee where ename = 'JAMES' )
# outer query is displaying the value of ename, for employees whose salary greatre than James

top-N analysis
query can be writen to find top 5 salaries of a companies employees
\top 20 cities by population in order
or the top 13 batting averages of major league baseball players in order

inline views are used for Top-N analysis
normally queries are performed on tables & rows
but we can query from another select statement, the subquery, as a data source
written as part of the from clause is known as inline view

# inline view
select ename,sal
from (select ename, sal from ct_employee order by sal desc)
where rownum lt= 5
# rownum is a temporary pseudocolumn


Single Row Subqueries, pg 1-13
# list of employees who belong to certain manager
select empno, mgr from ct_employee

# this is considered 3 blocks, an inner query block & outer query block
select ename,mgr,sal
from ct_employee
where mgr =
( select mgr
from ct_employee
where empno = 7499)
and sal gt
( select sal
from ct_employee
where empno = 7369)
where mgr = (select mgr from ct_employee where empno = 7788)

# retrieve details of employees with minimum salary
select ename, mgr, sal
from ct_employee
where sal = ( select min(sal) from ct_employee)

# can also be placed in the HAVING clause
select deptno, min(sal)
from ct_employee
group by deptno
having main(sal) gt ( select min(sal) from ct_employee where deptno = 30)

# generates an eroror, subquery returns multiple rows
select empno, ename
from ct_employee
where sal = (select min(sal) from ct_employee group by deptno)

# single row subqueries that return null, outer query evaluates to false
select ename, mgr
from ct_employee
where job =
( select job from ct_employee where ename = 'John')


Multiple-Row Subqueries, pg 1-12
mostly used in where clause
the operators IN, ANY, ALL are used

# the code to retrieve data about employees wh are managers
select e.ename
from ct_employee e
where e.empno in (select m.mgr from ct_employee m)


# employees who are not analysts and whose salary is less than any analyst
select ename,job,sal,deptno
from ct_employee
where sal lt any (select sal from ct_employee where job = 'ANALYST')
and job ltgt 'ANALYST'

# employees who are not analysts and whose salary is less than all analyst
select ename,job,sal,deptno
from ct_employee
where sal lt all (select sal from ct_employee where job = 'ANALYST')
and job ltgt 'ANALYST'


select
from
where e.empno not in (select ... where mgr is not null

NOT IN condition is same as ltgt ALL

if there is NULLs
you can use the IN condition
select
from
where e.empno IN (select m.mgr from ct_employee m)



Set Operators: Types, pg. 1-23s
merge the output of two or more queries into single output
4 set operators
- UNION
- UNION ALL
- INTERSECT
- MINUS
compound queries

Guidelines
- number of columns should be same # actual names can be different
- data type same
- order by should contain column name from 1st query
- parenthesis, to change order of evaluation # all set operators equal precedence

ct_employee: empno, ename, job, mgr, hiredate, sal, comm, deptno
ct_job_history: empno, enmae, job, startdate, enddate, deptno

UNION - combines all rows, only distinct rows, in ascending order
UNION ALL - dups and unsorted. Cannot use distinct
INTERSECT - returns only common rows, uses ORDER BY, includes NULLs
MINUS - distinct rows from first, not in second

sometimes number & data type may not match
you can include dummy columns & conversions
such as to_char, to_number, to_date

select deptno, to_char(null) place, hiredate
from ct_employee
UNION
select deptno, loc place, to_date(null)
from ct_department

if column in one table, but not in other, you can substitute a literal

select empno, deptno
from ct_employee
where (empno, deptno) IN (select empno,deptno FROM ct_empoloyee UNION ...)

Assessment