OCP Introduction to Oracle9i: SQL Exam Guide
Prepare to pass the OCP Introduction to Oracle9i SQL exam—a required exam for OCA/OCP DBA certification—using this Oracle Press study guide. You'll get complete coverage of all exam topics followed by practice questions and chapter summaries. The CD-ROM contains hundreds of practice exam questions in an adaptive format.
Search in google:
Get Certified with Official Exam Prep Material from Oracle PressPrepare to pass the Oracle Certified Professional Introduction to Oracle9i: SQL exam—a required exam for OCP DBA certification—using this Oracle Press study guide. Throughout each chapter, you'll find in-depth details on all the material covered on this challenging exam, followed by practice questions and chapter summaries to highlight what you've learned. Written for novices and experts alike, this book and CD-ROM package is the most comprehensive exam preparation tool available for this Oracle9i Certified Professional exam.Featuring a logical methodology designed to reinforce learning, this exam guide offers 100% complete coverage of all official exam objectives More than 300 practice questions—including scenario-based and short-answer question formats Exercises at the end of each chapter that reinforce your Oracle knowledge right away Tips, examples, and reviews in every chapter to help you remember key concepts Two-minute drills—quick and concise checklists that summarize each chapter's main points Chapter self-tests—exam-based questions complete with in-depth explanations Required Exam for OCP DBA CertificationBook and CD-ROM include more than 300 interactive exam-based questions. CD-ROM contains interactive exams that simulate the actual OCP exams and include hotlinked questions, answers, and explanations.
Chapter 2: Limiting, Sorting, and Manipulating Return Data\ In this chapter, you will learn about and demonstrate knowledge in the following areas: \ \ Restricting and sorting row data\ Using single-row functions\ \ This chapter will build on the concepts you learned in Chapter 1 with respect to selecting data from Oracle. In this chapter, we'll discuss how to limit the data selected from the Oracle database and how to tell Oracle to return the data in a specific order. We'll also discuss how to use a category of functions called single-row functions to manipulate the results of your queries in a variety of ways. This chapter covers important material comprising 16 percent of the test content of OCP Exam 1.\ Restricting and Sorting Row Data\ This section will cover the following areas related to restricting and sorting row data: \ \ Sorting return data with the order by clause\ Limiting return data with the where clause\ \ Obtaining all output from a table is great, but usually you must be more selective in choosing output. Most database applications contain a lot of data. How much data can a database contain? Some applications contain tables with a million rows or more, and the most recent release of Oracle9i will store over 512 petabytes (512 1,024 5 bytes) of data. Of course, this is only a theoretical limit. The real amount of data you can store with Oracle depends on how much disk space you give Oracle to use, but, needless to say, manipulating vast amounts of data like that requires you to be careful. Always ask for exactly what you want, and no more. This section tells you how.\ Sorting Return Data with the order by Clause\ Notice that Oracle does not return data requested in a particular order on any particular column, either numeric or alphabetical. According to the fundamentals of relational database theory, a table is by definition an unordered set of row data. That's fine for the ivory tower, but it's not always useful in real-world situations. Oracle enables you to order the output from select statements using the order by clause. This clause can impose a sort order on one or more columns in ascending or descending order in each of the columns specified. If more than one expression is specified for a sort order, then Oracle sorts in the order of data appearing in the first expression. If the first expression has duplicates, Oracle sorts in the order of data appearing in the second expression, and so on. The order by clause usually appears last in the Structured Query Language (SQL) statement, and the general syntax for the order by clause is to include both the clause and the column(s) or column alias(es) by which Oracle will order the results, each optionally followed by a special clause defining the direction of the order (asc for ascending and desc for descending). The default value is asc, and here is an example: \ \ \ SQL> select empno, ename, sal\ 2 from emp\ 3 order by ename asc;\ EMPNO ENAME SAL\ --------- ---------- ---------\ 7876 ADAMS 1100\ 7499 ALLEN 1600\ 7698 BLAKE 2850\ 7782 CLARK 2450\ 7902 FORD 3000\ 7900 JAMES 950\ 7566 JONES 2975\ 7839 KING 5000\ 7654 MARTIN 1250\ 7934 MILLER 1300\ 7788 SCOTT 3000\ 7369 SMITH 800\ 7844 TURNER 1500\ 7521 WARD 1250\ \ \ An example of sorting output in descending order using the desc keyword is shown here:\ \ \ SQL> select * from emp\ 2 order by ename desc;\ EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO\ ------ --------- --------- ----- --------- ----- ----- -------\ 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30\ 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30\ 7369 SMITH CLERK 7902 17-DEC-80 800 20\ 7788 SCOTT ANALYST 7566 19-APR-87 3000 20\ 7934 MILLER CLERK 7782 23-JAN-82 1300 10\ 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30\ 7839 KING PRESIDENT 17-NOV-81 5000 10\ 7566 JONES MANAGER 7839 02-APR-81 2975 20\ 7900 JAMES CLERK 7698 03-DEC-81 950 30\ 7902 FORD ANALYST 7566 03-DEC-81 3000 20\ 7782 CLARK MANAGER 7839 09-JUN-81 2450 10\ 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30\ 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30\ 7876 ADAMS CLERK 7788 23-MAY-87 1100 20\ \ \ TIP\ When NULL data appears in a column that Oracle is attempting to sort in ascending order, Oracle lists the NULL records at the end of the list. When sorting in descending order, Oracle places the NULL data at the top of the list.\ The order by clause can be useful in simple reporting. It can be applied to columns that are NUMBER, text (VARCHAR2 and CHAR), and DATE datatypes. You can even use numbers to positionally indicate the column where Oracle should order the output from a statement. For example, if you issue a statement similar to the one in the following code block, the order for the output will be as shown (the number 2 indicates that the second column specified in the statement should be used to define the order in the output):\ \ \ SQL> select empno, ename from emp\ 2 order by 2 desc;\ EMPNO ENAME\ ------ --------\ 7521 WARD\ 7844 TURNER\ 7369 SMITH\ 7788 SCOTT\ 7934 MILLER\ 7654 MARTIN\ 7839 KING\ 7566 JONES\ 7900 JAMES\ 7902 FORD\ 7782 CLARK\ 7698 BLAKE\ 7499 ALLEN\ 7876 ADAMS\ SQL> select ename, empno from emp\ 2 order by 2 desc;\ ENAME EMPNO\ --------- ------\ MILLER 7934\ FORD 7902\ JAMES 7900\ ADAMS 7876\ TURNER 7844\ KING 7839\ SCOTT 7788\ CLARK 7782\ BLAKE 7698\ MARTIN 7654\ JONES 7566\ WARD 7521\ ALLEN 7499\ SMITH 7369\ \ \ TIP\ You can also sort by column alias.\ Here's an even more complex example:\ \ \ SQL> select ename, deptno, sal\ 2 from emp\ 3 order by 2 asc, 3 desc;\ ENAME DEPTNO SAL\ ---------- --------- ---------\ KING 10 5000\ CLARK 10 2450\ MILLER 10 1300\ SCOTT 20 3000\ FORD 20 3000\ JONES 20 2975\ ADAMS 20 1100\ SMITH 20 800\ BLAKE 30 2850\ ALLEN 30 1600\ TURNER 30 1500\ WARD 30 1250\ MARTIN 30 1250\ JAMES 30 950\ \ \ For Review\ 1. Know how to put row data returned from a select statement in order and know the various sort orders (ascending and descending) that can be used with this option. Know also that Oracle can sort based on multiple columns.\ 2. Be sure you understand both the positional and named ways to specify the column on which the sort order should be defined.\ Exercises\ 1. Which of the choices below identifies the order by clause that produces the following output?\ \ \ EMPNO ENAME MGR\ --------- ---------- ---------\ 7369 SMITH 7902\ 7566 JONES 7839\ 7782 CLARK 7839\ 7698 BLAKE 7839\ 7876 ADAMS 7788\ 7934 MILLER 7782\ 7499 ALLEN 7698\ 7654 MARTIN 7698\ 7521 WARD 7698\ 7900 JAMES 7698\ 7844 TURNER 7698\ 7788 SCOTT 7566\ 7902 FORD 7566\ \ \ A. order by empno asc\ B. order by ename desc\ C. order by hiredate asc\ D. order by mgr desc\ 2. You are sorting data in a table in your select statement in descending order. The column you are sorting on contains NULL records. Where will the NULL records appear?\ A. At the beginning of the list\ B. At the end of the list\ C. In the middle of the list\ D. At the same location they are listed in the unordered table\ 3. Identify the default sort order used by Oracle when no sort order is specified:\ ______________________\ 4. The results from an SQL query are shown here:\ \ \ DEPTNO DNAME LOC\ --------- -------------- -------------\ 10 ACCOUNTING NEW YORK\ 40 OPERATIONS BOSTON\ 20 RESEARCH DALLAS\ 30 SALES CHICAGO\ \ \ Which of the following SQL statements could not have produced this output?\ A. select deptno, dname, loc from dept order by 2 asc, 1 asc, 3 desc;\ B. select deptno, dname, loc from dept order by 3 asc;\ C. select deptno, dname, loc from dept order by 2 asc;\ D. select deptno, dname, loc from dept order by 2 asc, 3 desc, 1 desc;...
PrefaceIntroductionPt. IPreparing for OCP DBA Exam I: Introduction to SQL1Overview of Oracle Databases52Limiting, Sorting, and Manipulating Return Data553Advanced Data Selection in Oracle974Subqueries1435Creating Oracle Database Objects2016Manipulating Oracle Data2597Creating Other Database Objects in Oracle2918User Access Control in Oracle347Pt. IIOCP Oracle9i DBA Practice Exams9OCP Exam I: Introduction to SQL381Index465
