book

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

Name: OCA Oracle Database SQL Exam Guide (Exam 1Z0-071)
Author: Steve O'Hearn
ISBN: 9781259584619

by Steve O'Hearn

August 2017

Intermediate to advanced

832 pages

15h

English

McGraw Hill Computing

Read now

Unlock full access

Cover Page
Title Page
Copyright Page
Dedication
Contents
Acknowledgments
Preface
Introduction
Exam Readiness Checklist
1 Oracle and Structured Query Language (SQL)
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

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 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
3 Manipulating Data
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
4 Restricting and Sorting Data
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
5 Using Single-Row Functions to Customize Output
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
6 Using Conversion Functions and Conditional Expressions
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
7 Reporting Aggregated Data Using the Group Functions
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
8 Displaying Data from Multiple Tables
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
9 Using Subqueries to Solve Queries
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
10 Managing Schema Objects
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
11 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
12 Managing Objects with Data Dictionary Views
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
13 Manipulating Large Data Sets
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
14 Controlling User Access
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
A About the Download
System RequirementsInstalling and Running Total TesterAbout Total TesterTechnical Support
Glossary
Index

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

Images

6 Using Conversion Functions and Conditional Expressions

CERTIFICATION OBJECTIVES

6.01 Describe Various Types of Conversion Functions

6.02 Use the TO_CHAR, TO_NUMBER, and TO_DATE Conversion Functions

6.03 Apply General Functions and Conditional Expressions in a SELECT Statement

✓ Two-Minute Drill

Q&A Self Test

This chapter looks at the topics of conversion functions and conditional expressions. Conversion functions perform data type transformation from one general type such as numeric to another such as text. Conditional expressions perform evaluations and return data using criteria you specify in the context of SQL ...

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

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

Publisher Resources

ISBN: 9781259584619

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

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

by Steve O'Hearn

6

Using Conversion Functions and Conditional Expressions

CERTIFICATION OBJECTIVES

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.