Study Guide for 1Z0-071: Oracle Database 12c SQL: Oracle Certification Prep

Description

This Study Guide is targeted at IT professionals who are working towards becoming an Oracle Database 12c SQL Certified Associate. The book provides information covering all of the exam topics for the Oracle certification exam: "1Z0-071: Oracle Database 12c SQL". The books in the Oracle Certification Prep series are built in lockstep with the test topics provided by Oracle Education's certification program. Each book is intended to provide the information that will be tested in a clean and concise format. The guides introduce the subject you'll be tested on, follow that with the information you'll need to know for it, and then move on to the next topic. They contain no drills or unrealistic self-tests to bump the page count without adding value. The series is intended to provide a concentrated source of exam information that is compact enough to be read through multiple times. This series is ideal for experienced Oracle professionals that are familiar with the topic being tested, but want a means to rapidly reinforce their existing skills and bridge any gaps in their knowledge. It is also an excellent option as a second source of information for candidates pursuing certification. Contents......Page 4 What to expect from the test......Page 7 What to Expect from this Study Guide......Page 9 Additional Study Resources......Page 11 Practice Questions......Page 12 Oracle and Structured Query Language (SQL)......Page 13 Identify the connection between an ERD and a Relational Database......Page 14 Explain the relationship between a database and SQL......Page 21 Describe the purpose of DDL......Page 23 Describe the purpose of DML......Page 25 Build a SELECT statement to retrieve data from an Oracle Database table......Page 27 Use the ORDER BY clause to sort SQL query results......Page 39 Limit the rows that are retrieved by a query......Page 45 Use ampersand substitution to restrict and sort output at runtime......Page 55 Use SQL row limiting clause......Page 60 Use various types of functions available in SQL......Page 63 Use character, number, date and analytical functions in SELECT statements......Page 72 Describe various types of conversion functions that are available in SQL......Page 85 Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions......Page 88 Apply general functions and conditional expressions in a SELECT statement......Page 93 Reporting Aggregated Data Using the Group Functions......Page 96 Describe the use of group functions......Page 97 Group data by using the GROUP BY clause......Page 103 Include or exclude grouped rows by using the HAVING clause......Page 106 Describe the different types of joins and their features......Page 109 Use SELECT statements to access data from more than one table using equijoins and nonequijoins......Page 112 Join a table to itself by using a self-join......Page 118 View data that generally does not meet a join condition by using outer joins......Page 120 Define subqueries......Page 123 Describe the types of problems subqueries can solve......Page 126 Describe the types of subqueries......Page 129 Query data using correlated subqueries......Page 131 Update and delete rows using correlated subqueries......Page 132 Use the EXISTS and NOT EXISTS operators......Page 133 Use the WITH clause......Page 135 Use single-row and multiple-row subqueries......Page 136 Describe set operators......Page 138 Use a set operator to combine multiple queries into a single query......Page 140 Control the order of rows returned......Page 143 Truncate data......Page 145 Insert rows into a table......Page 147 Update rows in a table......Page 153 Delete rows from a table......Page 154 Control transactions......Page 155 Describe data types that are available for columns......Page 159 Create a simple table......Page 161 Create constraints for tables......Page 166 Drop columns and set column UNUSED......Page 169 Create and use external tables......Page 171 Query various data dictionary views......Page 173 Differentiate system privileges from object privileges......Page 177 Grant privileges on tables and on a user......Page 180 Distinguish between privileges and roles......Page 182 Describe how schema objects work......Page 183 Create simple and complex views with visible/invisible columns......Page 186 Create, maintain and use sequences......Page 191 Create and maintain indexes including invisible indexes and multiple indexes on the same columns......Page 193 Perform flashback operations......Page 199 Describe the features of multitable INSERTs......Page 204 Merge rows in a table......Page 210