book

OCA Oracle Database SQL Certified Expert Exam Guide (Exam 1Z0-047)

Name: OCA Oracle Database SQL Certified Expert Exam Guide (Exam 1Z0-047)
Author: Steve O'Hearn
ISBN: 9780071614221

by Steve O'Hearn

November 2009

Intermediate to advanced

796 pages

16h 57m

English

McGraw Hill Computing

Read now

Unlock full access

Cover Page
OCA Oracle Database SQL Certified Expert Exam Guide
Copyright Page
Contents
Acknowledgments
Preface
Introduction
1 Introduction to SQL
The Exam: An Overview“SQL Fundamentals I” Versus “SQL Expert”What to ExpectDefine and Understand the Basics of the RDBMSRelational Databases and Dr. E.F. CoddDatabase NormalizationDatabase Design ConsiderationsDefine and Understand the Basics of SQLUnderstand the Oracle RDBMS and Oracle SQLOracle Is the Market LeaderCertification: Oracle SQL Versus ANSI SQLCertification: Oracle SQL Versus Oracle SQL*PlusOracle’s Tools for Working with SQLOracle’s Documentation for SQLUnderstand the Unique Role of SQL in Modern Software SystemsSQL Is a 4GLSQL: Gateway to the RDBMS for All Other LanguagesSyntax Isn’t EnoughConfirm Appropriate Materials for StudySoftwareDocumentationCertification Summary Two-Minute DrillQ&A Self Test Self Test Answers
2 Using DDL statements to Create and Manage tables
Categorize the Main Database ObjectsWhat Are Database Objects?SchemasCreate a Simple TableNaming a Table or Other ObjectThe SQL Statement Create TableReview the Table StructureList the Data Types That Are Available for ColumnsCharacterNumericDateLarge Objects (LOBs)Explain How Constraints Are Created at the Time of Table CreationCreating Constraints in the Create Table StatementThe Types of ConstraintsCertification Summary Two-Minute DrillQ&A Self TestSelf Test Answers
3 Manipulating Data
Describe Each Data Manipulation Language (DML) StatementSQL Statement OverviewDML Statement DescriptionsInsert Rows into a TableDefault Column ListEnumerated Column ListUpdate Rows in a TableExpressionsConstraintsThe Where ClauseDelete Rows from a TableControl TransactionsCommitRollbackSavepointRollback RevisitedCertification Summary Two-Minute DrillQ&A Self TestSelf Test Answers

4 Retrieving Data using the SQL Select statement
Execute a Basic Select StatementThe Select Statement—An ExampleSelect: Minimum RequirementsList the Capabilities of SQL Select StatementsThe Select Statement—An OverviewExpressionsAdditional Select Statement ClausesThe Capabilities of SelectDescribe How Schema Objects WorkTablesConstraintsViewsIndexesSequencesSynonymsCertification Summary Two-Minute DrillQ&A Self TestSelf Test Answers
5 Restricting and sorting Data
Limit the Rows That Are Retrieved by a QueryThe Where ClauseBoolean LogicAdditional Where Clause FeaturesAdditional ConceptsSort the Rows That Are Retrieved by a QueryReference by NameExpressionsReference by PositionCombinationsOrder By And NullCertification Summary Two-Minute DrillQ&A Self TestSelf Test Answers
6 Using single-row Functions to Customize output
Describe Various Types of Functions That Are Available in SQLCharacter FunctionsNumber FunctionsDate FunctionsConversion FunctionsOther FunctionsUse Character, Number, and Date Functions in Select StatementsThe Dual TableCharacter ManipulationMathematical ProcessingWorking with DatesOther FunctionsNesting FunctionsDescribe the Use of Conversion FunctionsConversion FunctionsAutomatic Datatype ConversionsManage Data in Different Time Zones—Use Various Datetime FunctionsDatabase Time Versus Session TimeCoordinated Universal Time (UTC)Time Zone DatatypesTime Zone FunctionsTime Zone Conversion FunctionsAT TIME ZONE, AT LOCALCertification Summary Two-Minute DrillQ&A Self TestSelf Test Answers
7 Reporting Aggregated Data using the Group Functions
Identify the Available Group FunctionsDescribe the Use of Group FunctionsCountSumMin, MaxAvgMedianRankFirst, LastGroupingOthersGroup Data by Using the Group By ClauseMultiple ColumnsOrder By RevisitedNesting FunctionsInclude or Exclude Grouped Rows by Using the Having ClauseCertification Summary Two-Minute DrillQ&A Self TestSelf Test Answers
8 Displaying Data from Multiple tables
Write Select Statements to Access Data from More Than One Table Using Equijoins and Non-Equijoins/View Data That Generally Does Not Meet a Join Condition by Using Outer JoinsKey RelationshipsTypes of JoinsInner JoinsOuter JoinsUsing Table AliasesNatural JoinsUsingMultitable JoinsNon-EquijoinsJoin a Table to Itself by Using a Self-JoinSelf-Referencing Foreign KeysSelf-Join SyntaxGenerate a Cartesian Product of All Rows from Two or More TablesCertification SummaryTwo-Minute DrillQ&A Self TestSelf Test Answers
9 Retrieving Data using subqueries
Define SubqueriesDescribe the Types of Problems That Subqueries Can SolveList the Types of SubqueriesWrite Single-Row and Multiple-Row SubqueriesSingle-Row SubqueriesMultiple-Row SubqueriesWrite a Multiple-Column SubqueryUse Scalar Subqueries in SQLSolve Problems with Correlated SubqueriesUpdate and Delete Rows Using Correlated SubqueriesUpdate with a Correlated SubqueryDelete with a Correlated SubqueryUse the Exists and Not Exists OperatorsUse the With ClauseCertification Summary Two-Minute DrillQ&A Self TestSelf Test Answers
10 Creating other schema objects
Create and Use Simple and Complex ViewsCreating ViewsUpdatable ViewsInline ViewsRetrieving DataAlter ViewCreate, Maintain, and Use SequencesCreating SequencesUsing SequencesCreate and Maintain IndexesImplicit Index CreationSingle ColumnCompositeUniqueDroppingCreate Private and Public SynonymsPrivatePublicObject PrivilegesName PriorityReplacingDroppingCertification Summary Two-Minute DrillQ&A Self TestSelf Test Answers
11 Managing schema objects
Add and Modify ColumnsAdding ColumnsModifying ColumnsRenaming ColumnsDrop Columns and Set Column UNUSEDDropping ColumnsUnusedAdd ConstraintsUsing Alter Table to Add ConstraintsModifying ConstraintsRemoving ConstraintsDisabling and Enabling ConstraintsDrop Table and Cascade ConstraintsDelete and on DeleteDeferrable and DeferredRenaming ConstraintsCreate Indexes Using the Create Table StatementAutomatic Index CreationUsing IndexCreate Function-Based IndexesPerform Flashback OperationsOverviewRecover Dropped TablesRecovering Tables in TimeMarking TimeCreate and Use External TablesBenefitsCreating External TablesCertification Summary Two-Minute DrillQ&A Self TestSelf Test Answers
12 Using the set operators
Describe Set OperatorsUse a Set Operator to Combine Multiple Queries into a Single QueryUnionUnion AllIntersectMinusCombinationsControl the Order of Rows ReturnedOrder By—By PositionOrder By—By ReferenceCertification Summary Two-Minute DrillQ&A Self TestSelf Test Answers
13 Generating reports by Grouping related Data
Use the Rollup Operation to Produce Subtotal ValuesUse the Cube Operation to Produce Crosstabulation ValuesUse the Grouping Function to Identify the Row Values Created by Rollup or CubeUse Grouping Sets to Produce a Single Result SetCertification SummaryTwo-Minute DrillQ&A Self TestSelf Test Answers
14 Managing objects with Data Dictionary Views
Use the Data Dictionary Views to Research Data on Your ObjectsStructureDynamic Performance ViewsReading CommentsAdding CommentsQuery Various Data Dictionary ViewsDictionaryIdentifying a User’s Owned ObjectsInspecting Tables and ColumnsCompiling ViewsChecking PrivilegesInspecting ConstraintsFinding ColumnsCertification Summary Two-Minute DrillQ&A Self TestSelf Test Answers
15 Manipulating Large Data sets
Manipulate Data Using SubqueriesCreate Table and SubqueriesInsert and SubqueriesUpdate and Correlated SubqueriesDescribe the Features of Multitable INSERTsUse the Following Types of Multitable Inserts: Unconditional, Conditional, and PivotUnconditionalConditionalPivotMerge Rows in a TableTrack the Changes to Data over a Period of TimeFlashback QueryFlashback Version QueryFlashback Transaction QueryCertification SummaryTwo-Minute DrillQ&A Self TestSelf Test Answers
16 Hierarchical retrieval
Interpret the Concept of a Hierarchical QueryCreate and Format Hierarchical DataCreate a Tree-Structured ReportChoosing DirectionOrder Siblings BySys_Connect_By_PathConnect_By_RootExclude Branches from the Tree StructureCertification Summary Two-Minute DrillQ&A Self TestSelf Test Answers
17 Regular Expression Support
Using MetacharactersRegular Expression FunctionsReplacing PatternsRegular Expressions and CHECK ConstraintsCertification SummaryTwo-Minute DrillQ&A Self TestSelf Test Answers
18 Controlling user Access
Differentiate System Privileges from Object PrivilegesSystem PrivilegesPrerequisitesGrant and RevokeAnyAdmin OptionAll PrivilegesPublicGrant Privileges on TablesSchema PrefixesWith Grant OptionRevokeAll PrivilegesDependent PrivilegesView Privileges in the Data DictionaryGrant RolesDistinguish Between Privileges and RolesCertification Summary Two-Minute DrillQ&A Self TestSelf Test Answers
A About the CD
System RequirementsInstalling and Running MasterExamMasterExamElectronic BookHelpRemoving Installation(s)Technical SupportLearnKey Technical Support
Glossary
Index

Content preview from OCA Oracle Database SQL Certified Expert Exam Guide (Exam 1Z0-047)

12Using the Set Operators

CERTIFICATION OBJECTIVES

12.01 Describe Set Operators

12.02 Use a Set Operator to Combine Multiple Queries into a Single Query

12.03 Control the Order of Rows Returned

Two-Minute Drill

Q&A Self Test

This chapter describes the set operators in SQL. Set operators work with sets of output data from two or more SELECT statements. They combine standalone SELECT statements in ways that cannot be done with joins or other conventional methods in SQL.

Set operators are ideal for a variety of situations where a SELECT statement’s output can be combined with other data that isn’t necessarily related through a structured key relationship ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

OCA/OCP Oracle Database 12c All-in-One Exam Guide (Exams 1Z0-061, 1Z0-062, & 1Z0-063), 2nd Edition

Publisher Resources

ISBN: 9780071614214

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

OCA Oracle Database SQL Certified Expert Exam Guide (Exam 1Z0-047)

by Steve O'Hearn