Transcription
Introduction to Oracle9i: SQLInstructor Guide Volume 140049GC11Production 1.1October 2001D33993
AuthorsCopyright Oracle Corporation, 2000, 2001. All rights reserved.Nancy GreenbergPriya NathanThis documentation contains proprietary information of Oracle Corporation. It isprovided under a license agreement containing restrictions on use and disclosure andis also protected by copyright law. Reverse engineering of the software is prohibited.If this documentation is delivered to a U.S. Government Agency of the Department ofDefense, then it is delivered with Restricted Rights and the following legend isapplicable:Technical Contributorsand ReviewersJosephine TurnerMartin AlvarezAnna AtkinsonDon BatesMarco BerbeekAndrew BranniganRestricted Rights LegendUse, duplication or disclosure by the Government is subject to restrictions forcommercial computer software and shall be deemed to be Restricted Rights softwareunder Federal law, as set forth in subparagraph (c)(1)(ii) of DFARS 252.227-7013,Rights in Technical Data and Computer Software (October 1988).This material or any portion of it may not be copied in any form or by any meanswithout the express prior written permission of Oracle Corporation. Any other copyingis a violation of copyright law and may result in civil and/or criminal penalties.Laszlo CzinkoczkiMichael GerlachSharon GrayRosita HanomanMozhe JalaliSarah JonesCharbel KhouriChristopher LawlessDiana LorentzNina MinchenCuong NguyenDaphne NougierPatrick OdellLaura PezziniStacey ProcterMaribel RenauBryan RobertsHelen RobertsonSunshine SalmonCasa SharifBernard SoleillantCraig SpoonemoreRuediger SteffanKarla VillasenorAndree WheeleyLachlan WilliamsPublisherNita BrozowskiIf this documentation is delivered to a U.S. Government Agency not within theDepartment of Defense, then it is delivered with “Restricted Rights,” as defined inFAR 52.227-14, Rights in Data-General, including Alternate III (June 1987).The information in this document is subject to change without notice. If you find anyproblems in the documentation, please report them in writing to Education Products,Oracle Corporation, 500 Oracle Parkway, Box SB-6, Redwood Shores, CA 94065.Oracle Corporation does not warrant that this document is error-free.Oracle and all references to Oracle products are trademarks or registered trademarksof Oracle Corporation.All other products or company names are used for identification purposes only, andmay be trademarks of their respective owners.
ContentsPrefaceCurriculum MapIntroductionObjectives I-2Oracle9i I-3Oracle9i Application Server I-5Oracle9i Database I-6Relational and Object Relational Database Management System I-7Oracle Internet Platform I-8System Development Life Cycle I-9Data Storage on Different Media I-11Relational Database Concept I-12Definition of a Relational Database I-13Data Models I-14Entity Relationship Model I-15Entity Relationship Modeling Conventions I-16Relating Multiple Tables I-18Relational Database Terminology I-19Relational Database Properties I-20Communicating with a RDBMS Using SQL I-21Relational Database Management System I-22SQL Statements I-23Tables Used in the Course I-241Writing Basic SQL SELECT StatementsObjectives 1-2Capabilities of SQL SELECT Statements 1-3Basic SELECT Statement 1-4Selecting All Columns 1-5Selecting Specific Columns 1-6Writing SQL Statements 1-7Column Heading Defaults 1-8Arithmetic Expressions 1-9Using Arithmetic Operators 1-10Operator Precedence 1-11Using Parentheses 1-13Defining a Null Value 1-14Null Values in Arithmetic Expressions 1-15Defining a Column Alias 1-16Using Column Aliases 1-17Concatenation Operator 1-18Using the Concatenation Operator 1-19Literal Character Strings 1-20Using Literal Character Strings 1-21Duplicate Rows 1-22Eliminating Duplicate Rows 1-23iii
SQL and iSQL*Plus Interaction 1-24SQL Statements Versus iSQL*Plus CommandsOverview of iSQL*Plus 1-26Logging In to iSQL*Plus 1-27The iSQL*Plus Environment 1-28Displaying Table Structure 1-29Interacting with Script Files 1-31Summary 1-34Practice Overview 1-352Restricting and Sorting DataObjectives 2-2Limiting Rows Using a Selection 2-3Limiting the Rows Selected 2-4Using the WHERE Clause 2-5Character Strings and Dates 2-6Comparison Conditions 2-7Using Comparison Conditions 2-8Other Comparison Conditions 2-9Using the BETWEEN Condition 2-10Using the IN Condition 2-11Using the LIKE Condition 2-12Using the NULL Conditions 2-14Logical Conditions 2-15Using the AND Operator 2-16Using the OR Operator 2-17Using the NOT Operator 2-18Rules of Precedence 2-19ORDER BY Clause 2-22Sorting in Descending Order 2-23Sorting by Column Alias 2-24Sorting by Multiple Columns 2-25Summary 2-26Practice 2 Overview 2-273Single-Row FunctionsObjectives 3-2SQL Functions 3-3Two Types of SQL Functions 3-4Single-Row Functions 3-5Single-Row Functions 3-6Character Functions 3-7Character Functions 3-8Case Manipulation Functions 3-9Using Case Manipulation Functions 3-10iv1-25
Character-Manipulation Functions 3-11Using the Character-Manipulation Functions 3-12Number Functions 3-13Using the ROUND Function 3-14Using the TRUNC Function 3-15Using the MOD Function 3-16Working with Dates 3-17Arithmetic with Dates 3-19Using Arithmetic Operators with Dates 3-20Date Functions 3-21Using Date Functions 3-22Practice 3, Part One: Overview 3-24Conversion Functions 3-25Implicit Data Type Conversion 3-26Explicit Data Type Conversion 3-28Using the TO CHAR Function with Dates 3-31Elements of the Date Format Model 3-32Using the TO CHAR Function with Dates 3-36Using the TO CHAR Function with Numbers 3-37Using the TO NUMBER and TO DATE Functions 3-39RR Date Format 3-40Example of RR Date Format 3-41Nesting Functions 3-42General Functions 3-44NVL Function 3-45Using the NVL Function 3-46Using the NVL2 Function 3-47Using the NULLIF Function 3-48Using the COALESCE Function 3-49Conditional Expressions 3-51The CASE Expression 3-52Using the CASE Expression 3-53The DECODE Function 3-54Using the DECODE Function 3-55Summary 3-57Practice 3, Part Two: Overview 3-584Displaying Data from Multiple TablesObjectives 4-2Obtaining Data from Multiple Tables 4-3Cartesian Products 4-4Generating a Cartesian Product 4-5Types of Joins 4-6Joining Tables Using Oracle Syntax 4-7What is an Equijoin? 4-8v
Retrieving Records with Equijoins 4-9Additional Search Conditions Using the AND Operator 4-10Qualifying Ambiguous Column Names 4-11Using Table Aliases 4-12Joining More than Two Tables 4-13Non-Equijoins 4-14Retrieving Records with Non-Equijoins 4-15Outer Joins 4-16Outer Joins Syntax 4-17Using Outer Joins 4-18Self Joins 4-19Joining a Table to Itself 4-20Practice 4, Part One: Overview 4-21Joining Tables Using SQL: 1999 Syntax 4-22Creating Cross Joins 4-23Creating Natural Joins 4-24Retrieving Records with Natural Joins 4-25Creating Joins with the USING Clause 4-26Retrieving Records with the USING Clause 4-27Creating Joins with the ON Clause 4-28Retrieving Records with the ON Clause 4-29Creating Three-Way Joins with the ON Clause 4-30INNER Versus OUTER Joins 4-31LEFT OUTER JOIN4-32RIGHT OUTER JOIN4-33FULL OUTER JOIN4-34Additional Conditions 4-35Summary 4-36Practice 4, Part Two: Overview 4-375Aggregating Data Using Group FunctionsObjectives 5-2What Are Group Functions? 5-3Types of Group Functions 5-4Group Functions Syntax 5-5Using the AVG and SUM Functions 5-6Using the MIN and MAX Functions 5-7Using the COUNT Function 5-8Using the DISTINCT Keyword 5-10Group Functions and Null Values 5-11Using the NVL Function with Group Functions 5-12Creating Groups of Data 5-13Creating Groups of Data: The GROUP BY Clause Syntax 5-14Using the GROUP BY Clause 5-15Grouping by More Than One Column 5-17vi
Using the GROUP BY Clause on Multiple Columns 5-18Illegal Queries Using Group Functions 5-19Excluding Group Results 5-21Excluding Group Results: The HAVING Clause 5-22Using the HAVING Clause 5-23Nesting Group Functions 5-25Summary 5-26Practice 5 Overview 5-276SubqueriesObjectives 6-2Using a Subquery to Solve a Problem 6-3Subquery Syntax 6-4Using a Subquery 6-5Guidelines for Using Subqueries 6-6Types of Subqueries 6-7Single-Row Subqueries 6-8Executing Single-Row Subqueries 6-9Using Group Functions in a Subquery 6-10The HAVING Clause with Subqueries 6-11What is Wrong with this Statement? 6-12Will this Statement Return Rows? 6-13Multiple-Row Subqueries 6-14Using the ANY Operator in Multiple-Row Subqueries 6-15Using the ALL Operator in Multiple-Row Subqueries 6-16Null Values in a Subquery 6-17Summary 6-18Practice 6 Overview 6-197Producing Readable Output with iSQL*PlusObjectives 7-2Substitution Variables 7-3Using the & Substitution Variable 7-5Character and Date Values with Substitution Variables 7-7Specifying Column Names, Expressions, and Text 7-8Defining Substitution Variables 7-10DEFINE and UNDEFINE Commands 7-11Using the DEFINE Command with & Substitution Variable 7-12Using the && Substitution Variable 7-13Using the VERIFY Command 7-14Customizing the iSQL*Plus Environment 7-15SET Command Variables 7-16iSQL*Plus Format Commands 7-17The COLUMN Command 7-18Using the COLUMN Command 7-19vii
COLUMN Format Models 7-20Using the BREAK Command 7-21Using the TTITLE and BTITLE Commands 7-22Creating a Script File to Run a Report 7-24Sample Report 7-26Summary 7-28Practice 7 Overview 7-298Manipulating DataObjectives 8-2Data Manipulation Language 8-3Adding a New Row to a Table 8-4The INSERT Statement Syntax 8-5Inserting New Rows 8-6Inserting Rows with Null Values 8-7Inserting Special Values 8-8Inserting Specific Date Values 8-9Creating a Script 8-10Copying Rows from Another Table 8-11Changing Data in a Table 8-12The UPDATE Statement Syntax 8-13Updating Rows in a Table 8-14Updating Two Columns with a Subquery 8-15Updating Rows Based on Another Table 8-16Updating Rows: Integrity Constraint Error 8-17Removing a Row from a Table 8-18The DELETE Statement 8-19Deleting Rows from a Table 8-20Deleting Rows Based on Another Table 8-21Deleting Rows: Integrity Constraint Error 8-22Using a Subquery in an INSERT Statement 8-23Using the WITH CHECK OPTION Keyword on DML Statements 8-25Overview of the Explicit Default Feature 8-26Using Explicit Default Values 8-27The MERGE Statement 8-28The MERGE Statement Syntax 8-29Merging Rows 8-30Database Transactions 8-32Advantages of COMMIT and ROLLBACK Statements 8-34Controlling Transactions 8-35Rolling Back Changes to a Marker 8-36Implicit Transaction Processing 8-37State of the Data Before COMMIT or ROLLBACK 8-38State of the Data after COMMIT 8-39Committing Data 8-40viii
State of the Data After ROLLBACK 8-41Statement-Level Rollback 8-42Read Consistency 8-43Implementation of Read Consistency 8-44Locking 8-45Implicit Locking 8-46Summary 8-47Practice 8 Overview 8-48Read Consistency Example 8-539Creating and Managing TablesObjectives 9-2Database Objects 9-3Naming Rules 9-4The CREATE TABLE Statement 9-5Referencing Another User’s Tables 9-6The DEFAULT Option 9-7Creating Tables 9-8Tables in the Oracle Database 9-9Querying the Data Dictionary 9-10Data Types 9-11DateTime Data Types 9-13TIMESTAMP WITH TIME ZONE Data Type 9-15TIMESTAMP WITH LOCAL TIME Data Type 9-16INTERVAL YEAR TO MONTH Data Type 9-17INTERVAL DAY TO SECOND Data Type 9-18Creating a Table by Using a Subquery Syntax 9-20Creating a Table by Using a Subquery 9-21The ALTER TABLE Statement 9-22Adding a Column 9-24Modifying a Column 9-26Dropping a Column 9-27The SET UNUSED Option 9-28Dropping a Table 9-29Changing the Name of an Object 9-30Truncating a Table 9-31Adding Comments to a Table 9-32Summary 9-33Practice 9 Overview 9-34ix
10 Including ConstraintsObjectives 10-2What are Constraints? 10-3Constraint Guidelines 10-4Defining Constraints 10-5The NOT NULL Constraint 10-7The UNIQUE Constraint 10-9The PRIMARY KEY Constraint 10-11The FOREIGN KEY Constraint 10-13FOREIGN KEY Constraint Keywords 10-15The CHECK Constraint 10-16Adding a Constraint Syntax 10-17Adding a Constraint 10-18Dropping a Constraint 10-19Disabling Constraints 10-20Enabling Constraints 10-21Cascading Constraints 10-22Viewing Constraints 10-24Viewing the Columns Associated with Constraints 10-25Summary 10-26Practice 10 Overview 10-2711 Creating ViewsObjectives 11-2Database Objects 11-3What is a View? 11-4Why use Views? 11-5Simple Views and Complex Views 11-6Creating a View 11-7Retrieving Data from a View 11-10Querying a View 11-11Modifying a View 11-12Creating a Complex View 11-13Rules for Performing DML Operations on a View 11-14Using the WITH CHECK OPTION Clause 11-17Denying DML Operations 11-18Removing a View 11-20Inline Views 11-21Top-N Analysis 11-22Performing Top-N Analysis 11-23Example of Top-N Analysis 11-24Summary 11-25Practice 11 Overview 11-26x
12 Other Database ObjectsObjectives 12-2Database Objects 12-3What is a Sequence? 12-4The CREATE SEQUENCE Statement Syntax 12-5Creating a Sequence 12-6Confirming Sequences 12-7NEXTVAL and CURRVAL Pseudocolumns 12-8Using a Sequence 12-10Modifying a Sequence 12-12Guidelines for Modifying a Sequence 12-13Removing a Sequence 12-14What is an Index? 12-15How Are Indexes Created? 12-16Creating an Index 12-17When to Create an Index 12-18When Not to Create an Index 12-19Confirming Indexes 12-20Function-Based Indexes 12-21Removing an Index 12-23Synonyms 12-24Creating and Removing Synonyms 12-25Summary 12-26Practice 12 Overview 12-2713 Controlling User AccessObjectives 13-2Controlling User Access 13-3Privileges 13-4System Privileges 13-5Creating Users 13-6User System Privileges 13-7Granting System Privileges 13-8What is a Role? 13-9Creating and Granting Privileges to a Role 13-10Changing Your Password 13-11Object Privileges 13-12Granting Object Privileges 13-14Using the WITH GRANT OPTION and PUBLIC Keywords 13-15Confirming Privileges Granted 13-16How to Revoke Object Privileges 13-17Revoking Object Privileges 13-18Database Links 13-19Summary 13-21Practice 13 Overview 13-22xi
14 SQL WorkshopWorkshop Overview 14-215 Using SET OperatorsObjectives 15-2The SET Operators 15-3Tables Used in This Lesson 15-4The UNION Operator 15-7Using the UNION Operator 15-8The UNION ALL Operator 15-10Using the UNION ALL Operator 15-11The INTERSECT Operator 15-12Using the INTERSECT Operator 15-13The MINUS Operator 15-14SET Operator Guidelines 15-16The Oracle Server and SET Operators 15-17Matching the SELECT Statements 15-18Controlling the Order of Rows 15-20Summary 15-21Practice 15 Overview 15-2216 Oracle9i Datetime FunctionsObjectives 16-2TIME ZONES 16-3Oracle9i Datetime Support 16-4TZ OFFSET 16-6CURRENT DATE 16-8CURRENT TIMESTAMP 16-9LOCALTIMESTAMP 16-10DBTIMEZONE and SESSIONTIMEZONE 16-11EXTRACT 16-12TIMESTAMP Conversion Using FROM TZ 16-13STRING To TIMESTAMP Conversion Using TO TIMESTAMP andTO TIMESTAMP TZ 16-14Time Interval Conversion with TO YMINTERVAL 16-15Summary 16-16Practice 16 Overview 16-1717 Enhancements to the GROUP BY ClauseObjectives 17-2Review of Group Functions 17-3Review of the GROUP BY Clause 17-4Review of the HAVING Clause 17-5GROUP BY with ROLLUP and CUBE Operators 17-6ROLLUP Operator 17-7ROLLUP Operator Example 17-8xii
CUBE Operator 17-9CUBE Operator: Example 17-10GROUPING Function 17-11GROUPING Function: Example 17-12GROUPING SETS 17-13GROUPING SETS: Example 17-15Composite Columns 17-17Composite Columns: Example 17-19Concatenated Groupings 17-21Concatenated Groupings Example 17-22Summary 17-23Practice 17 Overview 17-2418 Advanced SubqueriesObjectives 18-2What Is a Subquery? 18-3Subqueries 18-4Using a Subquery 18-5Multiple-Column Subqueries 18-6Column Comparisons 18-7Pairwise Comparison Subquery 18-8Nonpairwise Comparison Subquery 18-9Using a Subquery in the FROM Clause 18-10Scalar Subquery Expressions 18-11Scalar Subqueries: Examples 18-12Correlated Subqueries 18-14Using Correlated Subqueries 18-16Using the EXISTS Operator 18-18Using the NOT EXISTS Operator 18-20Correlated UPDATE 18-21Correlated DELETE 18-24The WITH Clause 18-26WITH Clause: Example 18-27Summary 18-29Practice 18 Overview 18-3119 Hierarchical RetrievalObjectives 19-2Sample Data from the EMPLOYEES Table 19-3Natural Tree Structure 19-4Hierarchical Queries 19-5Walking the Tree 19-6Walking the Tree: From the Bottom Up 19-8Walking the Tree: From the Top Down 19-9Ranking Rows with the LEVEL Pseudocolumn 19-10xiii
Formatting Hierarchical Reports Using LEVEL and LPAD 19-11Pruning Branches 19-13Summary 19-14Practice 19 Overview 19-1520 Oracle9i Extensions to DML and DDL StatementsObjectives 20-2Review of the INSERT Statement 20-3Review of the UPDATE Statement 20-4Overview of Multitable INSERT Statements 20-5Overview of Multitable INSERT Statements 20-6Types of Multitable INSERT Statements 20-7Multitable INSERT Statements 20-8Unconditional INSERT ALL 20-10Conditional INSERT ALL 20-11Conditional FIRST INSERT 20-13Pivoting INSERT 20-15External Tables 20-18Creating an External Table 20-19Example of Creating an External Table 20-20Querying External Tables 20-23CREATE INDEX with CREATE TABLE Statement 20-24Summary 20-25Practice 20 Overview 20-26A Practice solutionsB Table Descriptions and DataC Using SQL* PlusD Writing Advanced ScriptsE Oracle Architectural ComponentsIndexAdditional PracticesAdditional Practice SolutionsAdditional Practices Table and Descriptionsxiv
Preface
ProfileBefore You Begin This CourseBefore you begin this course, you should be able to use a graphical user interface (GUI).Required prerequisites are familiarity with data processing concepts and techniques.How This Course Is OrganizedIntroduction to Oracle9i: SQL is an instructor-led course featuring lectures and hands-onexercises. Online demonstrations and written practice sessions reinforce the concepts andskills introduced.Preface-3
Related PublicationsOracle PublicationsTitlePart NumberOracle9i Reference, Release 1 (9.0.1)A90190-02Oracle9i SQL Reference, Release 1 (9.0.1)A90125-01Oracle9i Concepts, Release 1 (9.0.0)A88856-02Oracle9i Server Application Developer’s Guide FundamentalsRelease 1 (9.0.1)A88876-02iSQL*Plus User’s Guide and Reference, Release 9.0.0A88826-01SQL*Plus User’s Guide and Reference, Release 9.0.1A88827-02Additional Publications System release bulletins Installation and user’s guides read.me files International Oracle User’s Group (IOUG) articles Oracle MagazinePreface-4
Typographic ConventionsWhat follows are two lists of typographical conventions used specifically within text or withincode.Typographic Conventions Within TextConventionObject or TermExampleUppercaseCommands,functions,column names,table names,PL/SQL objects,schemasUse the SELECT command to viewinformation stored in the LAST NAMEcolumn of the EMPLOYEES table.Lowercase,italicFilenames,syntax variables,usernames,passwordswhere: roleInitial capTrigger andbutton namesAssign a When-Validate-Item trigger tothe ORD block.is the name of the roleto be created.Choose Cancel.ItalicQuotation marksBooks, names ofcourses andmanuals, andemphasizedwords or phrasesFor more information on the subject seeOracle Server SQL Language ReferenceManualLesson moduletitles referencedwithin a courseThis subject is covered in Lesson 3,“Working with Objects.”Do not save changes to the database.Preface-5
Typographic Conventions (continued)Typographic Conventions Within CodeConventionObject or TermExampleUppercaseCommands,functionsSELECT employee idFROM employees;Lowercase,italicSyntax variablesCREATE ROLE role;Initial capForms triggersForm module: ORDTrigger level: S ITEM.QUANTITYitemTrigger name: When-Validate-Item. . .LowercaseColumn names,table names,filenames,PL/SQL objects. . .OG ACTIVATE LAYER(OG GET LAYER (’prod pie layer’)). . .SELECT last nameFROM employees;BoldText that mustbe entered by auserCREATE USER scottIDENTIFIED BY tiger;Preface-6
CurriculumMap
Introduction to Oracle9i: SQL Curriculum Map - 2
Languages Curriculum for Oracle9iIntroduction toOracle9i: SQLIntroductionto Oracle9i:SQL BasicsOracle9i:AdvancedSQLorIntroduction to Oracle9iforExperienced SQL UsersOracle9i: SQL forEnd UsersinClassinClassinClassOracle9i: Program with PL/SQLOracle9i: PL/SQLFundamentalsOracle9i: Develop PL/SQLProgram UnitsinClassOracle9i: Advanced PL/SQLinClassCopyright Oracle Corporation, 2001. All rights reserved.Integrated Languages CurriculumIntroduction to Oracle9i: SQL consists of two modules, Introduction to Oracle9i: SQL Basics andOracle9i: Advanced SQL. Introduction to Oracle9i: SQL Basics covers creating database structuresand storing, retrieving, and manipulating data in a relational database. Oracle9i: Advanced SQLcovers advanced SELECT statements, Oracle SQL and iSQL*Plus Reporting.For people who have worked with other relational databases and have knowledge of SQL, anothercourse, called Introduction to Oracle9i for Experienced SQL Users is offered. This course covers theSQL statements that are not part of ANSI SQL but are specific to Oracle.Oracle9i: Program with PL/SQL consists of two modules, Oracle9i: PL/SQL Fundamentals andOracle9i: Develop PL/SQL Program Units. Oracle9i: PL/SQL Fundamentals covers PL/SQL basicsincluding the PL/SQL language structure, flow of execution and interface with SQL. Oracle9i:Develop PL/SQL Program Units covers creating stored procedures, functions, packages, and triggersas well as maintaining and debugging PL/SQL program code.Oracle9i: SQL for End Users is directed towards individuals with little programming backgroundand covers basic SQL statements. This course is for end users who need to know some basic SQLprogramming.Oracle9i: Advanced PL/SQL is appropriate for individuals who have experience in PL/SQLprogramming and covers coding efficiency topics, object-oriented programming, working withexternal code, and the advanced features of the Oracle supplied packages.Introduction to Oracle9i: SQL Curriculum Map - 3
Languages Curriculum for Oracle9iIntroduction toOracle9i: SQLIntroductionto Oracle9i:SQL BasicsorOracle9i:AdvancedSQLIntroduction to Oracle9iforExperienced SQL UsersOracle9i: SQL forEnd UsersinClassinClassinClassOracle9i: Program with PL/SQLOracle9i: PL/SQLFundamentalsOracle9i: Develop PL/SQLProgram UnitsinClassOracle9i: Advanced PL/SQLinClassCopyright Oracle Corporation, 2001. All rights reserved.Integrated Languages CurriculumThe slide lists various modules and courses that are available in the languages curriculum. Thefollowing table lists the modules and courses with their equivalent TBTs.Course or ModuleEquivalent TBTIntroduction to Oracle9i: SQLBasicsOracle SQL: Basic SELECT StatementsOracle SQL: Data Retrieval TechniquesOracle SQL: DML and DDLOracle SQL and SQL*Plus: Advanced SELECT StatementsOracle SQL and SQL*Plus: SQL*Plus and ReportingOracle SQL Specifics: Retrieving and Formatting DataOracle SQL Specifics: Creating and Managing Database ObjectsPL/SQL: BasicsPL/SQL: Procedures, Functions, and PackagesPL/SQL: Database ProgrammingSQL for End Users: Part 1SQL for End Users: Part 2Advanced PL/SQL: Implementation and Advanced FeaturesAdvanced PL/SQL: Design Considerations and Object TypesOracle9i: Advanced SQLIntroduction to Oracle9i forExperienced SQL UsersOracle9i: PL/SQL FundamentalsOracle9i: Develop PL/SQLProgram UnitsOracle9i: SQL for End UsersOracle9i: Advanced PL/SQLInstructor NoteInform participants what their next course should be and give a brief description of the course.Introduction to Oracle9i: SQL Curriculum Map - 4
IntroductionCopyright Oracle Corporation, 2001. All rights reserved.Schedule:TimingTopic60 minutesLecture60 minutesTotal
ObjectivesAfter completing this lesson, you should be ableto do the following: List the features of Oracle9i Discuss the theoretical and physical aspects ofa relational database Describe the Oracle implementation of theRDBMS and ORDBMSCopyright Oracle Corporation, 2001. All rights reserved.I-2Lesson AimIn this lesson, you gain an understanding of the relational database management system (RDBMS) andthe object relational database management system (ORDBMS). You are also introduced to thefollowing: SQL statements that are specific to Oracle iSQL*Plus, which is used for executing SQL and for formatting and reporting purposesIntroduction to Oracle9i: SQL I-2
ReliabilitySingledevelopmentmodelCommonskill setsCopyright Oracle Corporation, 2001. All rights reserved.I-3Oracle9i FeaturesOracle offers a comprehensive high-performance infrastructure for e-business. It is called Oracle9i.Oracle9i includes everything needed to develop, deploy, and manage Internet applications.Benefits include: Scalability from departments to enterprise e-business sites Robust, reliable, available, secure architecture One development model, easy deployment options Leverage an organization’s current skillset throughout the Oracle platform (including SQL,PL/SQL, Java, and XML) One management interface for all applications Industry standard technologies, no proprietary lock-inIntroduction to Oracle9i: SQL I-3
Oracle9iI-4Copyright Oracle Corporation, 2001. All rights reserved.Oracle9iThere are two products, Oracle9i Application Server and Oracle9i Database, that provide a completeand simple infrastructure for Internet applications.Introduction to Oracle9i: SQL I-4
Oracle9i Application ServerPortalsAPACHETransactional AppsBusiness intelligenceIntelligenceIntegrationCopyright Oracle Corporation, 2001. All rights reserved.I-5Oracle9i Application ServerThe Oracle9i Application Server (Oracle9iAS) runs all your applications. The Oracle9i Databasestores all your data.Oracle9i Application Server is the only application server to include services for all the differentserver applications you will want to run. Oracle9iAS can run your: Portals or Web sites Java transactional applications Business intelligence applicationsIt also provides integration between users, applications, and data throughout your organization.Instructor NoteApache is used to maintain an open-source HTTP server software product for various modern desktopand server operating systems. It provides a secure, efficient and extensible server with HTTP servicesin synchronization with the current HTTP standards.Oracle9iAS is powered by Apache, which is the de facto industry standard.Introduction to Oracle9i: SQL I-5
Oracle9i DatabaseObject Relational DataDocumentsXMLMultimediaMessagesCopyright Oracle Corporation, 2001. All rights reserved.I-6Oracle9i DatabaseThe roles of the two products are very straightforward. Oracle9i Database manages all your data. Thisis not just the object relational data that you expect an enterprise database to manage. It can also beunstructured data like: Spreadsheets Word documents PowerPoint presentations XML Multimedia data types like MP3, graphics, video, and moreThe data does not even have to be in the database. Oracle9i Database has services through which youcan store metadata about information stored in file systems. You can use the database server tomanage and serve information wherever it is located.Instructor NoteXML (the Extensible Markup Language) was first ratified by the W3C (World Wide WebConsortium) as the standard for information exchange on the Internet in February 1998. Since then ithas been rapidly gaining momentum as the development community has begun to appreciate itspotential and as vendors have started to deliver tools to support it. XML specifies a rigorous, textbased way to represent the structure inherent in data so that it can be authored and interpretedunambiguously.Introduction to Oracle9i: SQL I-6
Relational and Object Relational DatabaseManagement System Relational model and object relational modelUser-defined data types and objectsFully compatible with relational databaseSupport of multimedia and large objectsHigh-quality database server featuresI-7Copyright Oracle Corporation, 2001. All rights reserved.About the Oracle ServerThe Oracle9i server supports both the relational and object relation models.The Oracle server extends the data modeling capabilities to support an object relational databasemodel that brings object-oriented programming, complex data types, complex business objects, andfull compatibility with the relational world.It includes several features for improved performance and functionality of online transactionprocessing (OLTP) applications, such as better sharing of run-time data structures, larger buffercaches, and deferrable constraints. Data warehouse applications will benefit from enhancements suchas parallel execution of insert, update, and delete operations; partitioning; and parallel-aware queryoptimization. Operating within the Network Computing Architecture (NCA) framework, Oracle9isupports client-server and Web-based applications that are distributed and multitiered.Oracle9i can scale tens of thousands of concurrent users, support up to 512 petabytes of data (apetabyte is 1,000 terabytes), and can handle any type of data, including text, spatial, image, sound,video, and time series as well as traditional structured data.For more information, see Oracle9i Concepts.Instructor NoteThe Object Relation Database Management System features are available with release 8 and higher.Oracle7 is a relational database management system and Oracle8, 8i, and 9i are object relationaldatabase management systems.Introduction to Oracle9i: SQL I-7
Oracle Internet PlatformClientsAny mailclientAny FTP clientInternet applicationsBusiness logicand dataPresentation andbusiness logicDatabasesApplicationserversDevelopment toolsSystem managementAny browserSQLPL/SQLJavaNetwork servicesI-8Copyright Oracle Corporation, 2001. All rights reserved.Oracle Internet PlatformOracle offers a comprehensive high-performance Internet platform for e-commerce and datawarehousing. This integrated platform includes everything needed to develop, deploy, and manageInternet applications. The Oracle Internet Platform is built on three core pieces: Browser-based clients to process presentation Application servers to execute business logic and serve presentation logic to browser-basedclients Databases to execute data
Introduction to Oracle9i: SQL Instructor Guide
