OCA Oracle Database 11g: SQL Fundamentals I: A Real World Certification Guide ( 1ZO-051 )


OCA Oracle Database 11g: SQL Fundamentals I: A Real World Certification Guide ( 1ZO-051 )
eBook: $29.99
Formats: PDF, PacktLib, ePub and Mobi formats
$25.49
save 15%!
Print + free eBook + free PacktLib access to the book: $79.98    Print cover: $49.99
$49.99
save 37%!
Free Shipping!
UK, US, Europe and selected countries in Asia.
Also available on:
Overview
Table of Contents
Author
Support
Sample Chapters
  • Successfully clear the first stepping stone towards attaining the Oracle Certified Associate Certification on Oracle Database 11g
  • This book uses a real world example-driven approach that is easy to understand and makes engaging
  • Complete coverage of the prescribed syllabus
  • Learn from a range of self-test questions to fully equip you with the knowledge to pass this exam
  • After reading this book, you can enter the exam room with confidence, knowing that you have done all you can to prepare for the big day
  • Get Mock test exam paper and Mock test answers

Test your Knowledge

Book Details

Language : English
Paperback : 460 pages [ 235mm x 191mm ]
Release Date : December 2011
ISBN : 1849683646
ISBN 13 : 9781849683647
Author(s) : Steve Ries
Topics and Technologies : All Books, Enterprise Products and Platforms, Oracle Database, Enterprise, Oracle

Table of Contents

Preface
Chapter 1: SQL and Relational Databases
Chapter 2: SQL SELECT Statements
Chapter 3: Using Conditional Statements
Chapter 4: Data Manipulation with DML
Chapter 5: Combining Data from Multiple Tables
Chapter 6: Row Level Data Transformation
Chapter 7: Aggregate Data Transformation
Chapter 8: Combining Queries
Chapter 9: Creating Tables
Chapter 10: Creating Other Database Objects
Chapter 11: SQL in Application Development
Appendix A: Companylink Table Reference
Appendix B: Getting Started with APEX
Index
  • Chapter 1: SQL and Relational Databases
    • Relational Database Management Systems
      • Flat file databases
      • Limitations of the flat file paradigm
      • Normalization
      • The relational approach
      • Bringing it into the Oracle world
      • Tables and their structure
    • Structured Query Language
      • A language for relational databases
      • Commonly-used SQL tools
        • SQL*Plus
        • TOAD
        • DBArtisan
        • SQL Worksheet (Enterprise Manager)
      • PL/SQL Developer
      • Oracle SQL Developer
    • Working with SQL
      • Introducing the Companylink database
    • An introduction to Oracle SQL Developer
      • Setting up SQL Developer
      • Getting around in SQL Developer
    • Summary
    • Test your knowledge
    • Chapter 2: SQL SELECT Statements
      • The purpose and syntax of SQL
        • The syntax of SQL
          • Case sensitivity
          • The use of whitespace
          • Statement terminators
      • Retrieving data with SELECT statements
        • Projecting columns in a SELECT statement
          • Selecting a single column from a table
          • Selecting multiple columns from a table
          • Selecting all columns from a table
      • Displaying the structure of a table using DESCRIBE
      • Using aliases to format output of SELECT statements
      • Using arithmetic operators with SELECT
        • The DUAL table and the use of string literals
        • Mathematical operators with SELECT
        • The meaning of nothing
      • Using DISTINCT to display unique values
      • Concatenating values in SELECT statements
      • Summary
        • Certification objectives covered
      • Test your knowledge
      • Chapter 3: Using Conditional Statements
        • Implementing selectivity using the WHERE clause
          • Understanding the concept of selectivity
          • Understanding the syntax of the WHERE clause
        • Using conditions in WHERE clauses
          • Using equality conditions
          • Implementing non-equality conditions
        • Examining conditions with multiple values
          • Constructing range conditions using the BETWEEN clause
          • Using the IN clause to create set conditions
          • Pattern-matching conditions using the LIKE clause
          • Understanding Boolean conditions in the WHERE clause
          • Examining the Boolean OR operator
          • Understanding the Boolean AND operator
          • The Boolean NOT operator
          • Using ampersand substitution with runtime conditions
        • Sorting data
          • Understanding the concepts of sorting data
          • Sorting data using the ORDER BY clause
          • Changing sort order using DESC and ASC
          • Secondary sorts
        • Summary
        • Certification objectives covered
        • Test Your Knowledge
        • Chapter 4: Data Manipulation with DML
          • Persistent storage and the CRUD model
            • Understanding the principles of persistent storage
            • Understanding the CRUD model and DML
          • Creating data with INSERT
            • Examining the syntax of the INSERT statement
            • Using single table inserts
              • Inserts using positional notation
              • Inserts using named column notation
              • Inserts using NULL values
            • Multi-row inserts
            • Conditional Inserts—INSERT...WHEN
          • Modifying data with UPDATE
            • Understanding the purpose and syntax of the UPDATE statement
            • Writing single-column UPDATE statements
            • Multi-column UPDATE statements
          • Removing data with DELETE
            • The purpose and syntax of the DELETE statement
            • Deleting rows by condition
            • Deleting rows without a limiting condition
            • Removing data unconditionally with TRUNCATE
          • Transaction control
            • Transactions and the ACID test
            • Completing transactions with COMMIT
            • Undoing transactions with ROLLBACK
              • DELETE and TRUNCATE revisited
          • Recognizing errors
          • Summary
            • Certification objectives covered
          • Test your knowledge
          • Chapter 5: Combining Data from Multiple Tables
            • Understanding the principles of joining tables
              • Accessing data from multiple tables
              • The ANSI standard versus Oracle proprietary syntax
            • Using ANSI standard joins
              • Understanding the structure and syntax of ANSI join statements
              • Examining ambiguous Cartesian joins
              • Using equi joins—joins based on equivalence
                • Implementing two table joins with a table-dot notation
                • Using two table joins with alias notation
              • Understanding the row inclusiveness of outer joins
              • Retrieving data from multiple tables using n-1 join conditions
              • Working with less commonly-used joins—non-equi joins and self-joins
            • Understanding Oracle join syntax
              • Using Cartesian joins with Cross join
              • Joining columns ambiguously using NATURAL JOIN
              • Joining on explicit columns with JOIN USING
              • Constructing fully-specified joins using JOIN ON
              • Writing n-1 join conditions using Oracle syntax
                • Creating multi-table natural joins
                • Building multi-table joins with JOIN USING
            • Summary
              • Certification objectives covered
            • Test your knowledge
            • Chapter 6: Row Level Data Transformation
              • Understanding functions and their use
                • Comprehending the principles of functions
                • Using single-row functions for data transformation
              • Understanding String functions
                • Using case conversion functions
                  • UPPER()
                  • LOWER()
                  • INITCAP()
                • Writing SQL with String manipulation functions
                  • LENGTH()
                  • Padding characters with LPAD() and RPAD()
                  • RTRIM() and LTRIM()
                  • CONCAT()
                  • SUBSTR()
                  • INSTR()
                  • Exploring nested functions
              • Handling DATE functions
                • Distinguishing SYSDATE and CURRENT_TIMESTAMP
                • Utilizing datatype conversion functions
                  • Using date to character conversion with TO_CHAR
                  • Converting characters to dates with TO_DATE()
                  • Converting numbers using TO_NUMBER()
              • Using arithmetic functions
                • ROUND()
                • TRUNC()
                • Using ROUND() and TRUNC() with dates
                • MOD()
                • Understanding date arithmetic functions
                  • MONTHS_BETWEEN()
                  • ADD_MONTHS()
              • Examining functions that execute conditional retrieval
                • NVL()
                • NVL2()
                • DECODE()
              • Summary
                • Certification Objectives Covered
              • Test your knowledge
              • Chapter 7: Aggregate Data Transformation
                • Understanding multi-row functions
                  • Examining the principles of grouping data
                  • Using multi-row functions in SQL
                    • COUNT()
                    • MIN() and MAX()
                    • SUM()
                    • AVG()
                • Grouping data
                  • Grouping data with GROUP BY
                  • Avoiding pitfalls when using GROUP BY
                  • Extending the GROUP BY function
                  • Using statistical functions
                    • STDDEV()
                    • VARIANCE()
                  • Performing row group exclusion with the HAVING clause
                • Putting it all together
                • Certification objectives covered
                • Summary
                • Test your knowledge
                • Chapter 8: Combining Queries
                  • Understanding the principles of subqueries
                    • Accessing data from multiple tables
                    • Solving problems with subqueries
                  • Examining different types of subqueries
                    • Using scalar subqueries
                      • Using scalar subqueries with WHERE clauses
                      • Using scalar subqueries with HAVING clauses
                      • Using scalar subqueries with SELECT clauses
                    • Processing multiple rows with multi-row subqueries
                      • Using IN with multi-row subqueries
                      • Using ANY and ALL with multi-row subqueries
                      • Using multi-row subqueries with HAVING clauses
                      • Using correlated subqueries
                    • Using multi-column subqueries
                      • Using multi-column subqueries with WHERE clauses
                      • Multi-column subqueries with the FROM clause
                  • Investigating further rules for subqueries
                    • Nesting subqueries
                    • Using subqueries with NULL values
                  • Using set operators within SQL
                    • Principles of set theory
                    • Comparing set theory and relational theory
                    • Understanding set operators in SQL
                      • Using the INTERSECT set operator
                      • Using the MINUS set operator
                      • Using the UNION and UNION ALL set operators
                  • Summary
                  • Certification objectives covered
                  • Test your knowledge
                  • Chapter 9: Creating Tables
                    • Introducing Data Definition Language
                      • Understanding the purpose of DDL
                      • Examining Oracle's schema-based approach
                      • Understanding the structure of tables and datatypes
                        • CHAR
                        • VARCHAR2
                        • NUMBER
                        • DATE
                        • Other datatypes
                      • Using the CREATE TABLE Statement
                        • Understanding the rules of table and column naming
                      • Creating tables
                      • Avoiding datatype errors
                        • Avoiding character datatype errors
                        • Avoiding numeric datatype errors
                      • Copying tables using CTAS
                      • Modifying tables with ALTER TABLE
                        • Adding columns to a table
                        • Changing column characteristics using ALTER TABLE... MODIFY
                        • Removing columns using ALTER TABLE... DROP COLUMN
                      • Removing tables with DROP TABLE
                    • Using database constraints
                      • Understanding the principles of data integrity
                      • Enforcing data integrity using database constraints
                        • NOT NULL
                        • PRIMARY KEY
                        • Natural versus synthetic
                        • FOREIGN KEY
                        • Deleting values with referential integrity
                        • UNIQUE
                        • CHECK
                    • Extending the Companylink Data Model
                      • Adding constraints to Companylink tables
                        • Adding referential integrity
                        • Adding a NOT NULL constraint
                        • Adding a CHECK constraint
                      • Adding tables to the Companylink model
                    • Summary
                      • Certification objectives covered
                    • Test your knowledge
                    • Chapter 10: Creating Other Database Objects
                      • Using indexes to increase performance
                        • Scanning tables
                        • Understanding the Oracle ROWID
                        • Examining B-tree indexes
                        • Creating B-tree indexes
                          • Using composite B-tree indexes
                        • Working with bitmap indexes
                          • Understanding the concept of cardinality
                          • Examining the structure of bitmap indexes
                          • Creating a bitmap index
                        • Working with function-based indexes
                        • Modifying and dropping indexes
                      • Working with views
                        • Creating a view
                          • Creating selective views
                          • Distinguishing simple and complex views
                          • Configuring other view options
                        • Changing or removing a view
                      • Using sequences
                        • Using sequences to generate primary keys
                      • Object naming using synonyms
                        • Schema naming
                        • Using synonyms for alternative naming
                          • Creating private synonyms
                          • Creating public synonyms
                      • Summary
                        • Certification objectives covered
                      • Test your knowledge
                      • Chapter 11: SQL in Application Development
                        • Using SQL with other languages
                          • Why SQL is paired with other languages
                          • Using SQL with PL/SQL
                          • Using SQL with Perl
                          • Using SQL with Python
                          • Using SQL with Java
                        • Understanding the Oracle optimizer
                          • Rule-based versus cost-based optimization
                          • Gathering optimizer statistics
                          • Viewing an execution plan with EXPLAIN PLAN
                        • Advanced SQL statements
                        • Exam preparation
                          • Helpful exam hints
                          • A recommended strategy for preparation
                        • Summary

                            Steve Ries

                            Steve Ries has been an Oracle DBA for 16 years, specializing in all aspects of database administration, including security, performance tuning and backup and recovery. He is a specialist in Oracle Real Application Clusters (RAC) and has administered Oracle clustered environments in every version of Oracle since the creation of Oracle Parallel Server. Steve is the author of OCA Oracle Database 11g: SQL Fundamentals I: A Real-World Certification Guide, Packt Publishing. He holds five Oracle certifications as well as a Security+ certification. He currently consults for the Dept. of Defense, US Marine Corps, and holds a high-level security clearance. Additionally, Steve has been an adjunct instructor of Oracle technologies at Johnson County Community College for 9 years, where he teaches classes that prepare students for the Oracle certification exams. He was also a speaker at the 2011 and 2012 Oracle Open World conferences. Steve is a two-time, award-winning technical paper writer and the creator of the alt.oracle blog.
                            Sorry, we don't have any reviews for this title yet.

                            Code Downloads

                            Download the code and support files for this book.


                            Submit Errata

                            Please let us know if you have found any errors not listed on this list by completing our errata submission form. Our editors will check them and add them to this list. Thank you.


                            Errata

                            - 8 submitted: last submission 12 Nov 2013

                            Page : 359 Errata: Other

                            In Test your knowledge section, question number 11.
                            The answer is D and it should be:

                            d) ALTER TABLE award ADD ( award_number number(5) );

                            Errata type: Other

                            Chapter 3, Test your knowledge section, question number 8

                            The answer is D and not C

                            Errata Type: Other

                            Chapter 10, question number 10

                            The answer should be D instead of A

                            Errata type: Other

                            Mock Test Paper Answers
                            The answer to question 55 is given as C. but should be

                            B. ALTER INDEX mess_mon_idx REBUILD;

                            Errata type: Technical

                            Chapter 8, Combining Queries, Using ANY and ALL with multi-row subqueries, the paragraph proceeding the fourth screenshot.

                            It states:
                            The query returns one more value than the original - the lowest value in the subquery.
                            It should state:
                            The query returns one more value than the original - the highest value in the subquery.

                            Errata type: Technical

                            pg.58 Before you create a JDeveloper project,the OSB Cookbook Application needs to be created.

                             

                            Errata type: Typo

                            pg.108 The text says:
                            In order to start, we will use the setup from the recipe Consuming messages from a JMS queue . Import the base OSB project into Eclipse from \chapter-3\getting-ready\ sync-request-response-over-jms-queue.
                            The path needs to be updated to:
                            \chapter-3\solution\sync-request-response-over-jms-queue

                             

                            Errata type: Technical

                            pg.120 The property values for securityCredentials and securityPrincipal need to be swapped.

                             

                             

                            Errata type: Technical

                            pg.169 HttpPostToSoap.xq - the XSD reference included in this file should be location="../xsd/Customer.xsd";.

                             

                            Errata type Typo Pg No: 281 This is:In simpler terms, the query displays maximum login_count values that are greater than the average, grouped by project_id It should be: In simpler terms, the query displays maximum login_count values that are lower than the average, grouped by project_id

                            Sample chapters

                            You can view our sample chapters and prefaces of this title on PacktLib or download sample chapters in PDF format.

                            Frequently bought together

                            OCA Oracle Database 11g: SQL Fundamentals I: A Real World Certification Guide ( 1ZO-051 ) +    Oracle Modernization Solutions =
                            50% Off
                            the second eBook
                            Price for both: $38.40

                            Buy both these recommended eBooks together and get 50% off the cheapest eBook.

                            What you will learn from this book

                            • Get insights into the fundamentals of databases and how they work
                            • Create and manipulate databases
                            • Create complex reports by joining data from multiple tables
                            • Use functions to manipulate data for solving real world problems
                            • Create database objects from scratch to store the types of data used in businesses today
                            • Use SQL in application development
                            • Aggregate data transformation using group functions
                            • Combine SQL queries

                             

                            In Detail

                            The Oracle Database 11g: SQL Fundamentals I exam is the first stepping stone in getting the Oracle Certified Associate Certification for Oracle Database 11g. The SQL programming language is used in every major relational database today and understanding the real world application of it is the key to becoming a successful DBA.

                            This book gives you: the essential real world skills to master relational data manipulation with Oracle SQL and prepares you to become an Oracle Certified Associate. Beginners are introduced to concepts in a logical manner while practitioners can use it as a reference to jump to relevant concepts directly.

                            We begin with the essentials of why databases are important in today's information technology world and how they work.

                            We continue by explaining the concepts of querying and modifying data in Oracle using a range of techniques, including data projection, selection, creation, joins, sub-queries and functions. Finally, we learn to create and manipulate database objects and to use them in the same way as today's expert SQL programmers.

                            This book prepares you to master the fundamentals of the SQL programming language using an example-driven method that is easy to understand

                            This definitive certification guide provides a disciplined approach to be adopted for successfully clearing the 1Z0-051 SQL Fundamentals I exam, which is the first stepping stone towards attaining the OCA on Oracle Database 11g certification.

                            Each chapter contains ample practice questions at the end. A full-blown mock test is included for practice so you can test your knowledge and get a feel for the actual exam.

                            Approach

                            This book is packed with real word examples. Each major certification topic is covered in a separate chapter, which helps to make understanding of concepts easier. At the end of each chapter, you will find a variety of practice questions to strengthen and test your learning.

                            You will get a feel for the actual SQL Fundamentals I exam by solving practice papers modeled on it.

                            Who this book is for

                            This book is for anyone who needs the essential skills to pass the Oracle Database SQL Fundamentals I exam and use those skills in daily life as an SQL developer or database administrator.

                            Code Download and Errata
                            Packt Anytime, Anywhere
                            Register Books
                            Print Upgrades
                            eBook Downloads
                            Video Support
                            Contact Us
                            Awards Voting Nominations Previous Winners
                            Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
                            Resources
                            Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software