book

OCA Oracle Database 12c SQL Fundamentals I Exam Guide (Exam 1Z0-061), 2nd Edition

Name: OCA Oracle Database 12c SQL Fundamentals I Exam Guide (Exam 1Z0-061), 2nd Edition
Author: Roopesh Ramklass
ISBN: 9780071820295

by Roopesh Ramklass

April 2014

Intermediate to advanced

592 pages

13h 28m

English

McGraw-Hill

Read now

Unlock full access

Cover
Title Page
Copyright Page
Acknowledgments
About the Author
Contents at a Glance
Contents
Preface
Introduction
1. Relational Database Design Using Oracle
Position the Server TechnologiesThe Oracle Server ArchitectureThe Oracle WebLogic ServerOracle Enterprise ManagerCloud ComputingExercise 1-1: Investigate Your Database and Application EnvironmentDevelopment Tools and LanguagesUnderstand Relational StructuresReal-World ScenariosData ModelingEntities and RelationsExercise 1-2: Design an Entity-Relationship Diagram for the Geological Cores ScenarioRows and TablesSummarize the SQL LanguageSQL StandardsSQL CommandsA Set-Oriented LanguageUse the Client ToolsSQL*PlusSQL DeveloperCreate the Demonstration SchemasUsers and SchemasThe HR and OE SchemasDemonstration Schema CreationTwo-Minute DrillQ&A Self TestLab QuestionSelf Test AnswersLab Answer

2. Data Retrieval Using the SQL SELECT Statement
List the Capabilities of SQL SELECT StatementsIntroducing the SQL SELECT StatementThe DESCRIBE Table CommandExercise 2-1: Describing the Human Resources SchemaCapabilities of the SELECT StatementExecute a Basic SELECT StatementThe Primitive SELECT StatementSyntax RulesExercise 2-2: Answering Our First Questions with SQLSQL Expressions and OperatorsThe NULL ConceptExercise 2-3: Experimenting with Expressions and the DUAL TableTwo-Minute DrillQ&A Self TestLab QuestionSelf Test AnswersLab Answer
3. Restricting and Sorting Data
Limit the Rows Retrieved by a QueryThe WHERE clauseComparison OperatorsExercise 3-1: Using the LIKE OperatorBoolean OperatorsPrecedence RulesSort the Rows Retrieved by a QueryThe ORDER BY ClauseExercise 3-2: Sorting Data Using the ORDER BY ClauseAmpersand SubstitutionSubstitution VariablesDefine and VerifyExercise 3-3: Using Ampersand SubstitutionTwo-Minute DrillQ&A Self TestLab QuestionSelf Test AnswersLab Answer
4. Single-Row Functions
Describe Various Types of Functions Available in SQLDefining a FunctionTypes of FunctionsUse Character, Number, and Date Functions in SELECT StatementsUsing Character Case Conversion FunctionsExercise 4-1: Using the Case Conversion FunctionsUsing Character Manipulation FunctionsExercise 4-2: Using the Character Manipulation FunctionsUsing Numeric FunctionsWorking with DatesUsing Date FunctionsExercise 4-3: Using the Date FunctionsTwo-Minute DrillQ&A Self TestLab QuestionSelf Test AnswersLab Answer
5. Using Conversion Functions and Conditional Expressions
Describe Various Types of Conversion Functions Available in SQLConversion FunctionsUse the TO_CHAR, TO_NUMBER, and TO_DATE Conversion FunctionsUsing the Conversion FunctionsExercise 5-1: Converting Dates into Characters Using the TO_CHAR FunctionApply Conditional Expressions in a SELECT StatementNested FunctionsGeneral FunctionsExercise 5-2: Using NULLIF and NVL2 for Simple Conditional LogicConditional FunctionsExercise 5-3: Using the DECODE FunctionTwo-Minute DrillQ&A Self TestLab QuestionSelf Test AnswersLab Answer
6. Reporting Aggregated Data Using the Group Functions
Describe the Group FunctionsDefinition of Group FunctionsTypes and Syntax of Group FunctionsIdentify the Available Group FunctionsUsing the Group FunctionsExercise 6-1: Using the Group FunctionsNested Group FunctionsGroup Data Using the GROUP BY ClauseCreating Groups of DataThe GROUP BY ClauseGrouping by Multiple ColumnsExercise 6-2: Grouping Data Based on Multiple ColumnsInclude or Exclude Grouped Rows Using the HAVING ClauseRestricting Group ResultsThe HAVING ClauseExercise 6-3: Using the HAVING ClauseTwo-Minute DrillQ&A Self TestLab QuestionSelf Test AnswersLab Answer
7. Displaying Data from Multiple Tables
Write SELECT Statements to Access Data from More Than One Table Using Equijoins and NonequijoinsTypes of JoinsJoining Tables Using ANSI SQL SyntaxQualifying Ambiguous Column NamesThe NATURAL JOIN ClauseExercise 7-1: Using the NATURAL JOINThe JOIN USING ClauseThe JOIN ON ClauseExercise 7-2: Using the NATURAL JOIN…ON ClauseN-Way Joins and Additional Join ConditionsNonequijoinsJoin a Table to Itself Using a Self-JoinJoining a Table to Itself Using the JOIN…ON ClauseExercise 7-3: Performing a Self-JoinView Data That Does Not Meet a Join Condition by Using Outer JoinsInner versus Outer JoinsLeft Outer JoinsRight Outer JoinsFull Outer JoinsExercise 7-4: Performing an Outer-JoinGenerate a Cartesian Product of Two or More TablesCreating Cartesian Products Using Cross JoinsExercise 7-5: Performing a Cross-JoinTwo-Minute DrillQ&A Self TestLab QuestionSelf Test AnswersLab Answer
8. Using Subqueries to Solve Problems
Define SubqueriesExercise 8-1: Types of SubqueriesDescribe the Types of Problems That the Subqueries Can SolveUse of a Subquery Result Set for Comparison PurposesStar TransformationGenerate a Table from Which to SELECTGenerate Values for ProjectionGenerate Rows to be Passed to a DML StatementExercise 8-2: More Complex SubqueriesList the Types of SubqueriesSingle- and Multiple-Row SubqueriesCorrelated SubqueriesExercise 8-3: Investigate the Different Types of SubqueriesWrite Single-Row and Multiple-Row SubqueriesExercise 8-4: Write a Query That Is Reliable and User FriendlyTwo-Minute DrillQ&A Self TestLab QuestionSelf Test AnswersLab Answer
9. Using the Set Operators
Describe the Set OperatorsSets and Venn DiagramsSet Operator General PrinciplesExercise 9-1: Describe the Set OperatorsUse a Set Operator to Combine Multiple Queries into a Single QueryThe UNION ALL OperatorThe UNION OperatorThe INTERSECT OperatorThe MINUS OperatorMore Complex ExamplesExercise 9-2: Using the Set OperatorsControl the Order of Rows ReturnedExercise 9-3: Control the Order of Rows ReturnedTwo-Minute DrillQ&A Self TestLab QuestionSelf Test AnswersLab Answer
10. Manipulating Data
Describe Each Data Manipulation Language (DML) StatementINSERTUPDATEDELETEMERGETRUNCATEDML Statement FailuresInsert Rows into a TableExercise 10-1: Use the INSERT CommandUpdate Rows in a TableExercise 10-2: Use the UPDATE CommandDelete Rows from a TableRemoving Rows with DELETEExercise 10-3: Use the DELETE CommandRemoving Rows with TRUNCATEMERGEControl TransactionsDatabase TransactionsThe Transaction Control StatementsExercise 10-4: Use the COMMIT and ROLLBACK CommandsTwo-Minute DrillQ&A Self TestLab QuestionSelf Test AnswersLab Answer
11. Using DDL Statements to Create and Manage Tables
Categorize the Main Database ObjectsObject TypesUsers and SchemasNaming Schema ObjectsObject NamespacesExercise 11-1: Determine What Objects Are Accessible to Your SessionReview the Table StructureExercise 11-2: Investigate Table StructuresList the Data Types That Are Available for ColumnsExercise 11-3: Investigate the Data Types in the HR schemaCreate a Simple TableCreating Tables with Column SpecificationsCreating Tables from SubqueriesAltering Table Definitions After CreationDropping and Truncating TablesExercise 11-4: Create TablesExplain How Constraints Are Created at the Time of Table CreationThe Types of ConstraintsDefining ConstraintsExercise 11-5: Work with ConstraintsTwo-Minute DrillQ&A Self TestLab QuestionSelf Test AnswersLab Answer
A: About the Download
System RequirementsInstalling and Running Total TesterAbout Total TesterTechnical Support
Glossary
Index

Content preview from OCA Oracle Database 12c SQL Fundamentals I Exam Guide (Exam 1Z0-061), 2nd Edition

Acknowledgments

This book was written amidst emigrating from South Africa to Canada and completed during one of the most brutal winters in Canadian history. Concluding the task to a standard I am satisfied with during this tumultuous time would not have been possible without the love, support, encouragement, and friendship of my wife, Dr. Ameetha Garbharran.

I am grateful too for the meticulous attention to detail and wisdom of the technical editor, my esteemed colleague and friend Cecil Strydom, who kept me focused on the quality of this endeavor.

Finally, thank you to the team at McGraw-Hill Education, specifically Mary Demery and Stephanie ...

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 Oracle Database SQL Exam Guide (Exam 1Z0-071)

Publisher Resources

ISBN: 9780071820288

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

OCA Oracle Database 12c SQL Fundamentals I Exam Guide (Exam 1Z0-061), 2nd Edition

by Roopesh Ramklass

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.