book

OCA Oracle Database SQL Exam Guide (Exam 1Z0-071)

by Steve O'Hearn

August 2017

Intermediate to advanced

832 pages

15h

English

McGraw Hill Computing

Read now

Unlock full access

The Exam: An OverviewWhat to Do and What to ExpectOracle SQL vs. ANSI SQLOracle SQL vs. Oracle SQL*PlusSQL Fundamentals I vs. SQL Certified AssociateConfirm Appropriate Materials for StudyIdentify the Connection Between an ERD and a Relational DatabaseEntity-Relationship Diagrams and Data ModelingRelational DatabasesMany-to-Many RelationshipsDatabase NormalizationExplain the Relationship Between a Database and SQLDescribe the Purpose of DDLDescribe the Purpose of DMLTransaction Control LanguageBuild a SELECT Statement to Retrieve Data from an Oracle Database TableCertification Summary✓ Two-Minute DrillQ&A Self TestSelf Test Answers

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 ObjectsExplain How Constraints Are Created at the Time of Table CreationCreating CONSTRAINTS in the CREATE TABLE StatementThe Types of CONSTRAINTSDrop Columns and Set Column UNUSEDDropping ColumnsUNUSEDCreate and Use External TablesBenefitsCreating External TablesCertification Summary✓ Two-Minute DrillQ&A Self TestSelf Test Answers
Truncate DataRecursively Truncate Child TablesInsert 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
Sort the Rows That Are Retrieved by a QueryReference by NameExpressionsReference by PositionCombinationsORDER BY and NULLLimit the Rows That Are Retrieved by a QueryThe WHERE ClauseBoolean LogicAdditional WHERE Clause FeaturesAdditional ConceptsUse Ampersand Substitution to Restrict and Sort Output at Run Time&DEFINE and UNDEFINE CommandsThe SET and SHOW CommandsACCEPT and PROMPTUse the SQL Row Limiting ClauseFETCHWITH TIESOFFSETCertification Summary✓ Two-Minute DrillQ&A Self TestSelf Test Answers
Use Various Types of Functions That Are Available in SQLCharacter FunctionsNumber FunctionsDate FunctionsOther FunctionsUse Character, Number, Date, and Analytical (PERCENTILE_CONT, STDDEV, LAG, LEAD) Functions in SELECT StatementsThe DUAL TableCharacter FunctionsNumerical FunctionsDate FunctionsAnalytical FunctionsNesting FunctionsCertification Summary✓ Two-Minute DrillQ&A Self TestSelf Test Answers
Describe Various Types of Conversion FunctionsExplicit and Implicit ConversionUse the TO_CHAR, TO_NUMBER, and TO_DATE Conversion FunctionsConversion FunctionsAdditional Conversion FunctionsApply General Functions and Conditional Expressions in a SELECT StatementCASEDECODENVLNULLIFCertification Summary✓ Two-Minute DrillQ&A Self TestSelf Test Answers
Describe the Use of Group FunctionsCOUNTSUMMIN, MAXAVGMEDIANRANKDENSE_RANKFIRST, LASTOthersGroup 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
Describe the Different Types of Joins and Their FeaturesTypes of JoinsUse SELECT Statements to Access Data from More Than One Table Using Equijoins and Non-EquijoinsInner JoinsUsing Table AliasesNatural JoinsUSINGMultitable JoinsNon-EquijoinsJoin a Table to Itself by Using a Self-JoinSelf-Referencing Foreign KeysSelf-Join SyntaxView Data That Generally Does Not Meet a Join Condition by Using Outer JoinsLEFT OUTER JOINRIGHT OUTER JOINFULL OUTER JOINFor the Record: Oracle Outer Join Syntax: (+)Certification Summary✓ Two-Minute DrillQ&A Self TestSelf Test Answers
Define SubqueriesDescribe the Types of Problems Subqueries Can SolveDescribe the Types of SubqueriesQuery Data Using Correlated SubqueriesUpdate and Delete Rows Using Correlated SubqueriesUPDATE with a Correlated SubqueryDELETE with a Correlated SubqueryUse the EXISTS and NOT EXISTS OperatorsUse the WITH ClauseWrite Single-Row and Multiple-Row SubqueriesSingle-Row SubqueriesMultiple-Row SubqueriesCertification Summary✓ Two-Minute DrillQ&A Self TestSelf Test Answers
Describe How Schema Objects WorkTablesConstraintsViewsIndexesSequencesCreate Simple and Complex Views with Visible/Invisible ColumnsCreating ViewsUpdatable ViewsInline ViewsALTER VIEWVisible/Invisible ColumnsCreate, Maintain, and Use SequencesCreating and Dropping SequencesUsing SequencesCreate and Maintain Indexes Including Invisible Indexes and Multiple Indexes on the Same ColumnsThe Oracle Database OptimizerImplicit Index CreationSingle ColumnCompositeUniqueDroppingVisible and Invisible IndexesIndex Alternatives on the Same Column SetPerform Flashback OperationsOverviewRecover Dropped TablesRecovering Data Within Existing Tables over TimeMarking TimeCertification Summary✓ Two-Minute DrillQ&A Self TestSelf Test Answers
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
Query Various Data Dictionary ViewsStructureDynamic Performance ViewsReading CommentsAdding CommentsDICTIONARYIdentifying a User’s Owned ObjectsInspecting Tables and ColumnsCompiling ViewsChecking PrivilegesInspecting ConstraintsFinding ColumnsCertification Summary✓ Two-Minute DrillQ&A Self TestSelf Test Answers
Describe the Features of Multitable INSERTsUse the Following Types of Multitable INSERTS: Unconditional and ConditionalMerge Rows into a TableCertification Summary✓ Two-Minute DrillQ&A Self TestSelf Test Answers
Differentiate System Privileges from Object PrivilegesSystem PrivilegesPrerequisitesGRANT and REVOKEANYADMIN OPTIONALL PRIVILEGESPUBLICGrant Privileges on Tables and on a UserSchema PrefixesWITH GRANT OPTIONREVOKEALL PRIVILEGESDependent PrivilegesView Privileges in the Data DictionaryGrant RolesDistinguish Between Privileges and RolesCertification Summary✓ Two-Minute DrillQ&A Self TestSelf Test Answers
System RequirementsInstalling and Running Total TesterAbout Total TesterTechnical Support

Overview

A fully updated, integrated self-study system for the Oracle Database SQL Exam

This thoroughly revised Oracle Press guide offers 100% coverage of all objectives on the latest version of the Oracle Database SQL Exam. Ideal both as a study guide and on-the-job reference, OCA Oracle Database SQL Exam Guide (Exam 1Z0-071) features detailed explanations, examples, practice questions, and chapter summaries. “Certification Objectives,” “Exam Watch,” and “On the Job” sections reinforce salient points throughout. You will gain access to two complete practice exams that match the tone, tenor, and format of the live test.

Get complete coverage every topic on Exam 1Z0-071, including:

• DDL and SQL SELECT statements
• Manipulating, restricting, and sorting data
• Single-row and group functions
• Displaying data from multiple tables
• Subqueries
• Schema objects
• Set operators
• Grouping related data
• Report creation
• Data dictionary views
• Large data sets
• Hierarchical retrieval
• Regular expression support
• User access control

The electronic includes:
• Two full practice exams
• Detailed answers and explanations

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.

Start your free trial

Publisher Resources

ISBN: 9781259584619

OCA Oracle Database SQL Exam Guide (Exam 1Z0-071)

by Steve O'Hearn

Overview

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.

You might also like

Learning SQL

SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition

Learning SQL, 3rd Edition

Oracle Database Administration from Zero to Hero

Publisher Resources

Overview

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.

You might also like

Learning SQL

SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition

Learning SQL, 3rd Edition

Oracle Database Administration from Zero to Hero

Publisher Resources

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.