Skip to main content

Oracle8iTM DBA: SQL and PL/SQL Certification Bible

Oracle8iTM DBA: SQL and PL/SQL Certification Bible

Damir Bersinic, Stephen Giles, Susan Ibach, Myles Brown

ISBN: 978-0-764-54832-1

Oct 2001

740 pages

Select type: Hardcover

Product not available for purchase

Description

Oracle8i DBA: SQL and PL/SQL Certification Bible brings you comprehensive coverage of the exam objectives, presented according to the Oracle certification guide. Because Oracle's exam scoring provides section scores according to the exact objectives, each exam objective is covered in its own chapter complete with self-assessment opportunities. Four appendices include a listing and description of the Oracle initialization parameters and other elements that will be tested on the exam. These appendices will provide information useful post-exam in real-work implementation of the Oracle systems.

Covers: Exam 1Z0-001
Preface.

Acknowledgments.

PART I: The Oracle SQL Language.

Chapter 1: The Oracle Database.

Chapter 2: Retrieving Data Using Basic SQL Statements.

Chapter 3: Using Single- and Multi-Row Functions.

Chapter 4: Advanced SELECT Statements.

Chapter 5: Adding, Updating, and Deleting Data.

PART II: Managing Database Objects.

Chapter 6: The SQL*Plus Environment.

Chapter 7: Creating and Managing Oracle Database Objects.

Chapter 8: Configuring Security in Oracle Databases.

PART III: Using PL/SQL.

Chapter 9: Introduction to PL/SQL.

Chapter 10: Controlling Program Execution in PL/SQL.

Chapter 11: Interacting with the Database Using PL/SQL.

Chapter 12: Handling Errors and Exceptions in PL/SQL.

Chapter 13: Introduction to Stored Programs.

Appendix A: What's on the CD-ROM?

Appendix B: Practice Exam.

Appendix C: Objective Mapping.

Appendix D: Exam Tips.

Appendix E: Database Schema for Labs.

Appendix F: Data Dictionary Views.

Appendix G: Suggested Readings, Web Sites, and Other Resources.

Index.

End-User License Agreement.

CD-ROM Installation Instructions.
ChapterPageDetailsDatePrint Run
CD ID#19CD Question ID#19
Question: Which of the following queries returns the names of all of the projects started in the year 2000? You may assume that the current year is in the twenty-first century and that you are using the Oracle default date format "DD-MON-YY". (Choose the best answer.)

Answer 1: SELECT name FROM project WHERE TRUNC(start_date) BETWEEN '01-JAN-00' AND '31-DEC-00';
Answer 2: SELECT name FROM project WHERE start_date LIKE '%00';
Answer 3: SELECT name FROM project WHERE start_date IN ('2000');
Answer 4: Both A and B.

Explanation: A is incorrect because the TRUNC of the start date gives only the date portion, not time. For the project to have started in 2000, the start date must be on one of the days between the first and last days, inclusive of the endpoints. B is incorrect because the LIKE operator makes the percent sign (%)a wildcard. The LIKE operator acts on character strings so the START_DATE must be implicitly converted to VARCHAR2, and it will be of the form "DD-MON-YY" so the last two characters represent the two-digit year. C is incorrect because the IN operator is for exact checking against a set of choices, but they must be of the same datatype. The start_date is converted to a string of the form "DD-MON-YY", not "YYYY".

Errata: All the answers are incorrect as written. Answer D should have been "None of the above" and would have been correct.
9/22/02

CD ID#69CD Question ID#69
Question: Assume that the PLAYERS table has columns. "Which of these is not allowed in a check constraint?" "

ID NUMBER(4)
FIRST_NAME VARCHAR2(20)
LAST_NAME VARCHAR2(20)
POSITION VARCHAR2(20)
SALARY NUMBER(11,2)
CAPTAIN VARCHAR2(1)
BIRTH_DATE DATE
TEAM_ID NUMBER(4)

Answer 1: CHECK (captain IN ('Y', 'N'))
Answer 2: CHECK (first_name||last_name IS NOT NULL)
Answer 3: CHECK (salary > (SELECT MIN(salary) FROM players))
Answer 4: CHECK (team_id IN (SELECT id FROM teams))

Explanation: Subqueries are not allowed in the CHECK constraint so C and D are correct. Further, calls to SQL functions such as SYSDATE and USER are not allowed so E is correct. The Boolean condition that makes up the constraint can use any of the operators such as IN and LIKE, and it can use more than one column from the same row.

Errata: The question in bold above was left out of the question on the test.
7/25/02