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

Description

Title Page......Page 2 Copyright Page......Page 3 Dedication......Page 6 Contents......Page 11 Acknowledgments......Page 21 Preface......Page 27 Introduction......Page 33 Exam Readiness Checklist......Page 36 1 Oracle and Structured Query Language (SQL)......Page 41 The Exam: An Overview......Page 43 What to Do and What to Expect......Page 45 SQL Fundamentals I vs. SQL Certified Associate......Page 49 Confirm Appropriate Materials for Study......Page 56 Entity-Relationship Diagrams and Data Modeling......Page 62 Relational Databases......Page 66 Many-to-Many Relationships......Page 69 Database Normalization......Page 71 Explain the Relationship Between a Database and SQL......Page 75 Describe the Purpose of DDL......Page 79 Transaction Control Language......Page 82 Build a SELECT Statement to Retrieve Data from an Oracle Database Table......Page 84 Certification Summary......Page 85 ✓ Two-Minute Drill......Page 86 Q&A Self Test......Page 88 Self Test Answers......Page 92 2 Using DDL Statements to Create and Manage Tables......Page 96 What Are Database Objects?......Page 97 Schemas......Page 99 Create a Simple Table......Page 103 Naming a Table or Other Object......Page 105 The SQL Statement CREATE TABLE......Page 113 Review the Table Structure......Page 115 Character......Page 118 Numeric......Page 120 Date......Page 121 Large Objects......Page 125 Explain How Constraints Are Created at the Time of Table Creation......Page 127 Creating CONSTRAINTS in the CREATE TABLE Statement......Page 128 The Types of CONSTRAINTS......Page 133 Dropping Columns......Page 148 UNUSED......Page 153 Creating External Tables......Page 156 Certification Summary......Page 161 ✓ Two-Minute Drill......Page 163 Q&A Self Test......Page 166 Self Test Answers......Page 171 3 Manipulating Data......Page 176 Truncate Data......Page 177 Recursively Truncate Child Tables......Page 178 Default Column List......Page 181 Enumerated Column List......Page 186 Update Rows in a Table......Page 191 Constraints......Page 193 The WHERE Clause......Page 196 Delete Rows from a Table......Page 198 COMMIT......Page 200 ROLLBACK......Page 205 SAVEPOINT......Page 208 ROLLBACK Revisited......Page 210 Certification Summary......Page 211 ✓ Two-Minute Drill......Page 213 Q&A Self Test......Page 215 Self Test Answers......Page 220 4 Restricting and Sorting Data......Page 225 Reference by Name......Page 227 Expressions......Page 232 Reference by Position......Page 236 Combinations......Page 237 ORDER BY and NULL......Page 238 The WHERE Clause......Page 239 Boolean Logic......Page 247 Additional WHERE Clause Features......Page 252 Additional Concepts......Page 256 Use Ampersand Substitution to Restrict and Sort Output at Run Time......Page 257 &......Page 258 DEFINE and UNDEFINE Commands......Page 262 The SET and SHOW Commands......Page 263 ACCEPT and PROMPT......Page 265 FETCH......Page 271 WITH TIES......Page 272 OFFSET......Page 274 Certification Summary......Page 275 ✓ Two-Minute Drill......Page 277 Q&A Self Test......Page 280 Self Test Answers......Page 285 5 Using Single-Row Functions to Customize Output......Page 291 Use Various Types of Functions That Are Available in SQL......Page 292 Date Functions......Page 294 Other Functions......Page 295 The DUAL Table......Page 296 Character Functions......Page 297 Numerical Functions......Page 307 Date Functions......Page 310 Analytical Functions......Page 318 Nesting Functions......Page 331 Certification Summary......Page 333 ✓ Two-Minute Drill......Page 334 Q&A Self Test......Page 335 Self Test Answers......Page 339 6 Using Conversion Functions and Conditional Expressions......Page 345 Describe Various Types of Conversion Functions......Page 346 Explicit and Implicit Conversion......Page 347 Use the TO_CHAR, TO_NUMBER, and TO_DATE Conversion Functions......Page 351 Conversion Functions......Page 352 Additional Conversion Functions......Page 363 CASE......Page 369 DECODE......Page 370 NVL......Page 372 NULLIF......Page 373 Certification Summary......Page 374 ✓ Two-Minute Drill......Page 375 Q&A Self Test......Page 376 Self Test Answers......Page 380 7 Reporting Aggregated Data Using the Group Functions......Page 386 Describe the Use of Group Functions......Page 387 COUNT......Page 390 MIN, MAX......Page 394 AVG......Page 396 MEDIAN......Page 397 RANK......Page 398 DENSE_RANK......Page 400 FIRST, LAST......Page 402 Others......Page 403 Group Data by Using the GROUP BY Clause......Page 404 Multiple Columns......Page 410 Nesting Functions......Page 412 Include or Exclude Grouped Rows by Using the HAVING Clause......Page 417 Certification Summary......Page 418 ✓ Two-Minute Drill......Page 419 Q&A Self Test......Page 421 Self Test Answers......Page 426 8 Displaying Data from Multiple Tables......Page 432 Types of Joins......Page 434 Inner Joins......Page 436 Using Table Aliases......Page 440 Natural Joins......Page 442 USING......Page 444 Multitable Joins......Page 445 Non-Equijoins......Page 447 Join a Table to Itself by Using a Self-Join......Page 450 Self-Join Syntax......Page 451 LEFT OUTER JOIN......Page 453 FULL OUTER JOIN......Page 454 For the Record: Oracle Outer Join Syntax: (+)......Page 455 Certification Summary......Page 456 ✓ Two-Minute Drill......Page 457 Q&A Self Test......Page 458 Self Test Answers......Page 465 9 Using Subqueries to Solve Queries......Page 469 Define Subqueries......Page 471 Describe the Types of Problems Subqueries Can Solve......Page 474 Describe the Types of Subqueries......Page 477 Query Data Using Correlated Subqueries......Page 479 UPDATE with a Correlated Subquery......Page 482 DELETE with a Correlated Subquery......Page 485 Use the EXISTS and NOT EXISTS Operators......Page 487 Use the WITH Clause......Page 488 Single-Row Subqueries......Page 490 Multiple-Row Subqueries......Page 494 ✓ Two-Minute Drill......Page 498 Q&A Self Test......Page 500 Self Test Answers......Page 508 10 Managing Schema Objects......Page 514 Constraints......Page 515 Indexes......Page 516 Sequences......Page 517 Create Simple and Complex Views with Visible/Invisible Columns......Page 519 Creating Views......Page 520 Updatable Views......Page 524 Inline Views......Page 528 ALTER VIEW......Page 530 Visible/Invisible Columns......Page 532 Creating and Dropping Sequences......Page 538 Using Sequences......Page 540 Create and Maintain Indexes Including Invisible Indexes and Multiple Indexes on the Same Columns......Page 545 The Oracle Database Optimizer......Page 546 Implicit Index Creation......Page 547 Single Column......Page 548 Composite......Page 552 Unique......Page 553 Dropping......Page 554 Visible and Invisible Indexes......Page 555 Index Alternatives on the Same Column Set......Page 557 Overview......Page 560 Recover Dropped Tables......Page 561 Recovering Data Within Existing Tables over Time......Page 565 Marking Time......Page 567 Certification Summary......Page 571 ✓ Two-Minute Drill......Page 573 Q&A Self Test......Page 575 Self Test Answers......Page 581 11 Using the Set Operators......Page 585 Describe Set Operators......Page 587 UNION......Page 591 UNION ALL......Page 595 INTERSECT......Page 596 MINUS......Page 597 Combinations......Page 598 ORDER BY—By Position......Page 600 ORDER BY—By Reference......Page 601 ✓ Two-Minute Drill......Page 603 Q&A Self Test......Page 604 Self Test Answers......Page 611 12 Managing Objects with Data Dictionary Views......Page 614 Query Various Data Dictionary Views......Page 616 Structure......Page 617 Dynamic Performance Views......Page 621 Reading Comments......Page 623 Adding Comments......Page 624 DICTIONARY......Page 626 Identifying a User’s Owned Objects......Page 627 Inspecting Tables and Columns......Page 630 Compiling Views......Page 631 Checking Privileges......Page 632 Inspecting Constraints......Page 633 Finding Columns......Page 634 Certification Summary......Page 635 ✓ Two-Minute Drill......Page 636 Q&A Self Test......Page 638 Self Test Answers......Page 641 13 Manipulating Large Data Sets......Page 646 Describe the Features of Multitable INSERTs......Page 647 Use the Following Types of Multitable INSERTS: Unconditional and Conditional......Page 652 Merge Rows into a Table......Page 667 ✓ Two-Minute Drill......Page 671 Q&A Self Test......Page 673 Self Test Answers......Page 678 14 Controlling User Access......Page 682 System Privileges......Page 684 Prerequisites......Page 687 GRANT and REVOKE......Page 690 ANY......Page 693 ADMIN OPTION......Page 695 PUBLIC......Page 696 Grant Privileges on Tables and on a User......Page 699 Schema Prefixes......Page 701 WITH GRANT OPTION......Page 703 ALL PRIVILEGES......Page 704 Dependent Privileges......Page 705 View Privileges in the Data Dictionary......Page 706 Grant Roles......Page 708 Distinguish Between Privileges and Roles......Page 714 Certification Summary......Page 715 ✓ Two-Minute Drill......Page 716 Q&A Self Test......Page 717 Self Test Answers......Page 722 About Total Tester......Page 726 Technical Support......Page 727 Glossary......Page 729 Index......Page 749