book

Oracle® 9i SQL Exam Cram™ 2 (Exam 1Z0-007)

by Gavin Powell

September 2004

Intermediate to advanced

448 pages

8h 49m

English

Pearson IT Certification

Read now

Unlock full access

The Iterative System Development CycleBasic TerminologyThe SELECT StatementThe WHERE ClauseThe ORDER BY ClauseThe GROUP BY ClauseJoining TablesSpecialized QueriesOperatorsConditionsPseudocolumnsExpressionsFunctionsSingle Row FunctionsAggregate FunctionsChanging Data (DML)
Software Used During the Writing of This BookOther Sources of InformationOther Titles by the Same AuthorAbout the Technical Reviewers
How to Prepare for an ExamPractice TestsTaking a Certification ExamScheduling a Certification ExamArriving at the Exam LocationIn the Exam RoomAfter the ExamRetaking a TestAbout This BookChapter ElementsOther Book ElementsChapter ContentsContacting the Author
Oracle9i SQL in the Real WorldThe Ideal Oracle9i SQL CandidatePut Yourself to the TestYour Educational BackgroundYour Hands-on ExperienceTesting Your Exam ReadinessAssessing Your Readiness for the 1Z0-007 Exam
What Is an RDBMS?Basic Relational Data ModelingNormalizationReferential IntegrityDenormalizationThe Object-Relational Data ModelDatabase Design and Modeling the Oracle WayWhat Is Oracle SQL?ToolsSQL*PlusSQL*Plus WorksheetiSQL*PlusOracle Enterprise ManagerOracle SQL New Features for Oracle9iGeneralSQL StatementsDML CommandsDDL CommandsDatatypesExpressionsConditionsBuilt-In FunctionsThe MOVIES SchemaSyntax Conventions Used in This BookExam Prep Questions
What Is a Query?SELECT Statement Syntax and Query CapabilitiesSelecting Columns from TablesUsing AliasesSelecting from Other Database ObjectsTypes of QueriesSome Things We Can Do with SELECTThe DUAL TableNULL ValuesThe DISTINCT ClauseExam Prep Questions
Filtering with the WHERE ClauseWHERE Clause SyntaxComparison ConditionsLogical ConditionsTop-N QueriesSorting with the ORDER BY ClauseORDER BY Clause SyntaxSorting Using the ORDER BY ClauseSorting MethodsSorting ModifiersExam Prep Questions

PrecedenceOperatorsConditionsPseudocolumnsExpressionsExam Prep Questions
What Is a Function?Types of FunctionsWhere Can Functions Be Used?Single Row FunctionsString FunctionsString Functions Returning NumbersString Functions Returning StringsNumber FunctionsSimple CalculatorsTrigonometryLogarithmsDate FunctionsSimple Date Retrieval FunctionsParts of DatesTime ZonesDatatype Conversion FunctionsString ConversionsNumber ConversionsDatetime ConversionsROWIDs and HexadecimalsCharacter Set Conversion FunctionsObscure Conversion FunctionsMiscellaneous FunctionsXML and Other Obscure FunctionsUser-Defined FunctionsExam Prep Questions
What Is a Join?ANSI and Oracle Join SyntaxANSI Join SyntaxOracle Join SyntaxTypes of JoinsCross JoinInner JoinOuter JoinLeft Outer JoinRight Outer JoinFull Outer JoinSelf JoinNatural Joins Using ANSI Join SyntaxThe NATURAL KeywordThe USING ClauseThe ON ClauseEqui Joins (Equality), Anti Joins (Inequality), Range Joins, and Semi JoinsMutable and Complex JoinsExam Prep Questions
What Is >GROUP BY?Basic GROUP BY Clause SyntaxGROUP BY Clause RulesExpanding the MOVIES SchemaGrouping FunctionsAggregate FunctionsSimple SummariesSimple StatisticsStatistical DistributionsRankingsGroupingsAnalyticsAnalytical-Only FunctionsFiltering with the HAVING ClauseExtending GROUP BY: ROLLUP and CUBEExam Prep Questions
What Is a Subquery?Where Can Subqueries Be Located?Comparison Condition Subquery SyntaxTypes of SubqueryScalar SubquerySingle Row SubqueryMultiple Row SubqueryMultiple Column SubqueryCorrelated SubqueryInline ViewNested SubqueryThe WITH ClauseSpecialized QueriesComposite QueryHierarchical QueryParallel QueryFlashback QueryExam Prep Questions
Environmental SettingsThe SHOW CommandThe SET CommandThe Syntax of Environmental SettingsScripts and Variable SubstitutionScripting in SQL*PlusVariable Substitution in SQL*PlusThe DEFINE CommandThe UNDEFINE CommandFormatting for ReadabilityColumn Formatting and HeadingsHeaders and FootersReport Headers and FootersPage Headers and FootersPage Breaks and Grouping ComputationsiSQL*PlusDifferences Between SQL*Plus and iSQL*PlusHTML Embedded ScriptsCustomizing DisplayExam Prep Questions
What Is the Difference Between DML and DDL?The INSERT CommandSingle Table INSERTMultiple Table INSERTConditional Multiple Table INSERT CommandNon-Conditional Multiple Table INSERT CommandThe UPDATE CommandThe DELETE CommandThe TRUNCATE CommandThe MERGE CommandTransactional ControlCOMMIT, ROLLBACK, and SAVEPOINTSET TRANSACTIONExam Prep Questions
Database ObjectsCreating TablesDatatypesOracle Built-In DatatypesUser-Defined DatatypesConstraintsAltering TablesColumnsConstraintsDropping and Truncating TablesMetadata ViewsTablesDatatypesConstraintsExam Prep Questions
ViewsCreating ViewsChanging Data from ViewsUpdatable Table Columns Through ViewsAdding Constraints to ViewsAltering and Dropping ViewsInline Views and Top-N AnalysisIndexesCreating IndexesAltering and Dropping IndexesSequencesSynonymsMetadata ViewsViewsIndexesSequences and SynonymsFinding the Right Metadata ViewsExam Prep Questions
Schemas and UsersTypes of UsersCreating UsersGrouping Privileges Using RolesPrivilegesGranting PrivilegesRevoking PrivilegesMetadata ViewsExam Prep Questions
Multiple Test ModesStudy ModeCertification ModeCustom ModeAdaptive ModeMissed Question ModeNon-Duplicate ModeQuestion TypesRandom Questions and Order of AnswersDetailed Explanations of Correct and Incorrect AnswersAttention to Exam ObjectivesInstalling the CDCreating a Shortcut to the MeasureUp Practice TestsTechnical Support
Chapter 1Chapter 2Chapter 3Chapter 4Chapter 5Chapter 6Chapter 7Chapter 8Chapter 9Chapter 10Chapter 11Chapter 12Chapter 13Chapters 14–17

Content preview from Oracle® 9i SQL Exam Cram™ 2 (Exam 1Z0-007)

Chapter 2. Retrieving Data (SELECT)

Terms You Need to Understand

✓ The SELECT statement
✓ The FROM clause
✓ Query
✓ Column and column list
✓ Schema
✓ Expression
✓ Alias
✓ DISTINCT versus ALL
✓ NULL

Concepts You Need to Master

✓ What a query is
✓ What an expression is
✓ The difference between a column and an expression
✓ Simple queries
✓ The DUAL table
✓ The effect of NULL values on the result of expressions
✓ The DISTINCT clause
✓ Repeating groups

This chapter jumps straight into the basic syntax of data retrieval using the Oracle SQL SELECT statement, additionally briefly introducing some specifics.

What Is a Query?

A query is a statement interrogating the database and returning information. Most often tables are interrogated and rows from those tables are returned. ...