Oracle lesson preparation for Nov 1st
1st Part : Introduction
———————————————————-
Oracle 9i offers comprehensive high-performance infrastructure including everything needed to develop,depoly and manage the internet applications:
Benifits:
1.Scalable from department to enterprise
2.Robust reliable available secure archetecture.
3.One development model, easy deployment options.
4.Leverage an organzation’s current skillset throught oracle applications(SQL,PL/SQL,XML,and Java)
5.One management interface
6.Industry standard technology.
Two productes:
1.Oracle9i Application Server
2.Oracle Database
Oracle Application Server runs all your applications:
1.Portal and website
2.Java transaction progress
3.Bussiness transaction process
Oracle database manage and store all the data:
1.object relational data
2.spreadsheet
3.work document
4.powerpoint presentations
5.XML
6.Multimedia file like Mp3,video and graphics.
Oracle 9i is object relational database management system.
1.Use defined types and objects
2.Full compatible with RDBMS
3.support multimedia and large objects
4.improve the performance and function in OLTP(online transaction process)
System development cycle life
1.strategy and analysis
2.design
3.build and document
4.transition
5.production
Database: an organized collection of information.
DBMS: is a progrom that could store,retrieve and modify the data in the database on request.
Four database: Flat-system,network,RDBMS,ORDBMS
RDBMS concepts:
1.1970,Dr E.F.Codd
2.RDBMS models consists the following:
collection of objects and relations
sets of operators act on relations
integraty of data for accuracy and consistency
RDBMS properties:
1.Can be accessed by executing SQL statement
2.Contains collection of tables with no physical points.
3.User operators.
ANSI(American National Standard Institute)
By useing SQL,
1.efficient
2.easy to learn and use
3.functional complete
—————————————————–
2nd Part : Write the basic SELECT statement
Syntax:
SELECT *|{[DISTINCT] column,expression [alias]}
FROM table
* : select all columns
DISTINCT: must following SELECT key word,it is used to encluding duplications.
Alias: 1.following the column
2.use AS key work
3.use double quatotion to include long strings.
DESC table
——————————————————-
3rd Part : Restricting and softing
Restricting by using WHERE clause:
Syntax:
SELECT *|{[DISTINCT] column,express[alias]}
FROM table
WHERE conditions
Conditions include:
1.=,>,>=,<,<=,<>
2.BETWEEN AND,IN(set),LIKE,NULL
3.NOT,AND,OR
In the WHERE conditions,the data and characters must be enclosed by singe quotations.
Softing:
Syntax:
ORDER BY column|expression ASC|DESC
We could also manipulate multiple columns.
——————————————–
4th Part : Sing row Functions
Character functions:
1.Case-sence: UPPER(column|expression)
LOWER(column|expression)
INITCAP(column|expression)
2.Manipulation functions:
CONCAT(column1|expression1,column2|expression2)
LENGTH(column|expression)
SUBSTR(column,m,n)
INSTR(column,m,n)
LPAD|RPAD(column,n,’strings’)
TRIM(leading|tailing|both,trim_string FROM trim_resouce)
REPLACE(test,seaching_String,replace_string)
Date functions:
MONTHS_BETWEEN(date1,date2)
ADD_MONTHS(date1,n)
NEXT_DAY(date,’strings’)
LAST_DAY(date)
ROUND(date,’string’)
TRUNC(date,’string’)
Numeric functions: Round(m,demical n)
TRUNC(m,n)
MOD(m,n)
Conversation functions:
TO_CHAR(date|number,’format’)
TO_NUMBER(character,’format’)
TO_DATE(character,’format’)
General functions:
NVL(expr1,expr2)
NVL2(expr1,expr2,expr3)
IFNULL(expr1,expr2)
COALESCE(expr1,expr2,expr3),return first non-null expression
Two expression: CASE, DECODE
CASE syntax:
CASE expr WHEN comparasion THEN returun_expr1
[WHEN comparasion THEN returun_expr2
WHEN comparasion THEN returun_expr3
]
ELSE exprn
END
DECODE syntax:
DECODE(expr1,search1,result1
search2,result2
…
default)