Oracle SQL introduction

1. Oracle SQL introduction

2. 使用聲明 • 請尊重智慧財產權 • 本資料僅限高雄應用科技大學資管系教學使用 • 未經允許請勿擅自複製、散佈、使用

3. Agenda • Database Structure Overview • Oracle SQL* Net (Networking) • SQL • Data Retrieval • Data Manipulation Language (DML) • Data Definition Language (DDL) • Transaction Control • Database Objects • Table, Constraint , Sequence , View , Index

4. DAY 1

5. Terminology • Instance (SGA + background process) • Database (control file + data file + redo files) • Tablespace • Schema • Object • Table,Index,Sequence,View,Package,Procedure,Function,Cluster…etc

6. Database Structure Overview Parameter File Instance Database SGA SMON Control File User Process Shared Pool DBW0 Database Buffer Cache Redo log Files PMON Server Process Library Cache CKPT Data Files LGWR Redo log Buffer Data Dict. Cache ARC0 Archived Log File

7. STARTUP OPEN MOUNT SHUTDOWN NOMOUNT SHUTDOWN Stages in Startup and Shutdown

8. How to determine Database up or down ? (Windows)

9. How to determine Database up or down ? (UNIX)

10. Database & Tablespace

11. Tablespace & Database object

12. FNDX GLX OEX FNDD GLD OED Oracle E-Business Suite Tablespace Design SYSTEM RBS TEMP

13. Establishing Connections to Oracle Servers

14. How to determine Listener up or down?(Windows)

15. How to determine Listener up or down?(Unix)

16. Starting and using the Net Configuration Assistant

17. tnsnames.ora SEMPROD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.64.32.53 )(PORT = 1520))) (CONNECT_DATA = (SERVICE_NAME = PROD)) ) SEMUAT2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.64.32.54 )(PORT = 1529))) (CONNECT_DATA = (SERVICE_NAME = UAT2)) )

18. SQL*Plus

19. Logging into Database 開始 程式集  Oracle 9i  Application Development SQL Plus

20. Logging into Database

21. SQL*Plus Command Summary (I) • @ • & • get • edit • execute • host • list • /

22. SQL*Plus Command Summary (II) • spool • connect • describe • column • set • show all • exit • help

23. 3rd Party Tools • TOAD (Quest Software) • SQL Navigator (Quest Software)

24. Exercise 1 • Installation Oracle 9i Database • Create Database • Connect to Database by SQL* Net (using SQL* Plus) • show user

25. Sample Schema Diagrams

26. SQL Statement • Data Retrieval • Select • Data Manipulation Language (DML) • Insert,Update,Delete • Data Definition Language (DDL) • Create,alter,drop,rename,truncate • Transaction Control • Commit,rollback,savepoint • Data Control Language (DCL) • Grant,revoke

27. SQL

28. Displaying Table Structure

29. Basic SELECT Statement SELECT column [alias] FROM table [table alias] WHERE column = ‘xxx’ • Not Case Sensitive • Can be one or more lines • Keywords can not be abbreviated or split across lines

30. Selecting All Columns, All Rows

31. Selecting Specific Columns

32. Arithmetic Expressions

33. Concatenation Operator Concatenation Operator

34. Managing Null Values • NULL is a value that is unavailable,unassigned,unknown,or inapplicable • NULL is not the same as zero or space • Arithmetic expressions containing a null value evaluate to NULL

35. Conditions containing NULL

36. Conditions containing NULL (example)

37. NVL function • Converts null to an actual value • Datatype that can be used are date,character,and number • Datatype must match • NVL(comm,0) • NVL(hiredate,’01-JAN-97’) • NVL(job,’Not Job Yet’)

38. NVL Function (example)

39. DECODE Function • Facilitates conditional inqueries by doing the work of a CASE or IF-THEN-ELSE statement DECODE (col/express, search1, result1 [ ,search2,result2,……] [, default ])

40. Using the DECODE Function (example)

41. Built-in Function Quick Ref.

42. Preventing the Selection of Duplicate Rows • The default display of queries is all rows including duplicate rows SQL>SELECT JOB_ID 2 FROM EMPLOYEES; • Eliminate duplicate rows by using DISTINCT in the SELECT clause SQL>SELECT DISTINCT JOB_ID 2 FROM EMPLOYEES;

43. Display All Rows Display Unique Rows • SQL>SELECT DISTINCT JOB_ID • FROM EMPLOYEES; • JOB_ID • -------------------- • AC_ACCOUNT • AC_MGR • AD_ASST • AD_PRES • AD_VP • FI_ACCOUNT • FI_MGR • HR_REP • SQL>SELECT JOB_ID • FROM EMPLOYEES; • JOB_ID • -------------------- • SA_MAN • SA_REP • SA_REP • SA_REP • SH_CLERK • SH_CLERK Duplicate Rows Preventing the Selection of Duplicate Rows(example)

44. Limiting Selected Rows

45. Comparison and Logical Operations • Logical comparison operators = > >= < <= • SQL comparison operators • BETWEEN ... AND • IN (list) • LIKE • IS NULL • Logical operators • AND • OR • NOT

46. The WHERE Clause SELECT first_name,last_name,job_id FROM employees WHERE first_name = 'John‘; FIRST_NAME LAST_NAME JOB_ID --------------- ------------------------------ ---------------- John Chen FI_ACCOUNT John Seo ST_CLERK John Russell SA_MAN

47. Negating Expressions • Logical Operators • != <> ^= • SQL Operator • NOT BETWEEN ... AND ... • NOT IN • NOT LIKE • IS NOT NULL

48. The ORDER BY Clause

49. Exercise 2 • Installing the Human Resources (HR) Schema • 列出薪資超過10000之員工姓名,受雇日期,部門及薪資 • 列出所有銷售部門的員工編號,姓名及薪資, ,並依受雇日期做sorting (descending)

50. Oracle Date Format • Oracle stores dates in a internal numeric format(Valid date range from January 1, 4712 BC to December 31, 9999 AD) • Century,year,month,day,hours,minutes,seconds • Default date display is DD-MON-RR • SYSDATE is a function returning date and time • DUAL id a dummy table used to view SYSDATE