USER_TAB_COMMENTS Stores comment information on tables.

USER_COL_COMMENTS Stores comment information for columns.

USER_CONSTRAINTS CONTRAINT_TYPE column will list the first character of the first word that best describes each type of constraint.

ALL_CONSTRAINTS CONTRAINT_TYPE column will list the first character of the first word that best describes each type of constraint.

USER_OBJECTS Can be used to verify the validity status of all user database objects.

ALL_OBJECTS Can be used to verify the validity status of all database objects.

USER_UPDATABLE_COLUMNS COLUMN_NAME and UPDATABLE will tell you whether the columns in a complex view can be modified.

USER_INDEXES Contains the name of the index and it uniqueness.

USER_IND_COLUMNS Contains the index name, the table name, and the column name.

USER_SYS_PRIVS Shows all privileges associated with this user.

SESSION_PRIVS Shows all privileges available in this session.

USER_ROLE_PRIVS Identifies the roles granted to you.

ROLE_ROLE_PRIVS Identifies the roles granted to other roles in the database.

ROLE_TAB_PRIVS Identifies object privileges granted to roles.

ROLE_SYS_PRIVS Identifies system privileges granted to roles.

SESSION_ROLES Identifies roles available to the current session.

Grant Privileges..System Privileges and Object Privileges

Granting system and object privileges is accomplished with the grant command. Example: GRANT create session TO turner or GRANT select, update, insert ON emp TO turner (NOTE, this object privilege syntax does not work when granting privileges to a single column - correct syntax is GRANT privilege (column_name) ON table TO user;).

System Privileges:

1. Database Access These privileges control who accesses the database, when he or she can access it, and what he or she can do regarding management of his or her restricted session. Privileges include create session, alter session, and restricted session.
2. Users These privileges are used to manage users in the Oracle database. Typically, these privileges are reserved for DBAs or security administrators. Privileges include create user, become user, alter user, and drop user.
3. Tables These privileges govern which users can create and maintain tables. The privileges include create table, create any table, alter any table, backup any table, drop any table, lock any table, comment any table, select any table, insert any table, update any table, and delete any table. The create table or create any table privilege also enables you to drop the table. The create table privileges also bestows the ability to create indexes on the table and to run the analyze command on the table. To be able to truncate a table, you must have the drop any table privilege granted to you.
4. Indexes The privileges include create any index, alter any index, and drop any index. You should note that no create index system privilege exists. the create table privilege also enables you to alter and drop indexes that you won and that are associated with the table.
5. Synonyms These privileges include create synonym, create any synonym, drop any synonym, create public synonym, and drop public synonym. The create synonym privilege also enables you to alter and drop synonyms that you own.
6. Views Privileges include create view, create any view, and drop any view. The create view privilege also enables you to alter and drop views that you own.
7. Sequences Privileges include create sequence, create any sequence, alter any sequence, drop any sequence, and select any sequence. The create sequence privilege also enables you to drop sequences that you own.
8. Roles Roles are objects that can be used for simplified privilege management. You create a role, grant privileges to it, and then grant the role to users. Privileges include create role, drop any role, grant any role, and alter any role.
9. Transactions These privileges are for resolving in-doubt distributed transactions being processed on the Oracle database. Privileges include force transaction and force any transaction.
10. PL/SQL These privileges enable you to create, run, and manage those different types of blocks. Privileges include create procedure, create any procedure, alter any procedure, drop any procedure, and execute any procedure. The create procedure privilege also enables you to alter and drop PL/SQL blocks that you own.
11. Triggers A trigger is a PL/SQL block in Oracle that executes when a specified DML activity occurs on the table to which the trigger is associated. Privileges include create trigger, create any trigger, alter any trigger, and drop any trigger. The create trigger privilege also enables you to alter and drop triggers that you own.

Object Privileges:

1. select Permits the grantee of this object privilege to access the date in a table, sequence, view, or snapshot.
2. insert Permits the grantee of this object privilege to insert data into a table or, in some cases, a view. You can also restrict this privilege to specified columns of a table.
3. update Permits the granted to update data in a table or view. You can also restrict this privilege to specified columns of a table.
4. delete Permits the granted to delete data in a table or view. You can also restrict this privilege to specified columns of a table.
5. alter Permits the grantee of this object privilege to alter the definition of a table or sequence only; the alter privileges on all other database objects are considered system privileges.
6. index Permits the grantee of this object privilege to create an index on a table already defined.
7. references Permits the grantee of this object privilege to create or alter a table in order to crate a foreign key constraint against data in the reference table.
8. execute Permits the grantee of this object privilege to run a stored procedure or function.

Oracle Certified Professional Exam Questions -Part I

Oracle Certified Professional

The Oracle Certified Professional recognizes achievement in mastering intermediate and advanced Oracle skills. Demonstrate proficiency and receive recognition in managing the implementation of Oracle technology while putting your career on the fast track.

Gain additional job opportunities - fill the Oracle skills gap in the marketplace Help your employers accelerate adoption of technology solutions and maximize ROI Increased opportunity to earn 21% more on average than your non certified counterparts

1. You are creating some tables in your database as part of the logical data model Which of the following constraints have an index associated with them that is generated automatically by Oracle?
   1. Unique
   2. Foreign-key
   3. Check
   4. NOT NULL
2. You have a table with three associated indexes, two triggers, two references to that table from other tables, and a view You issue the DROP TABLE CASCADE CONSTRAINTS statement Which of the following objects will still remain after the statement is issued?
   A The triggers B The indexes C The foreign keys in the other tables D The view
3. In order to set your SQL*Plus session so that your NLS_DATE_FORMAT information is altered in a specific way every time you log into Oracle, what method would be used?
   A Setting preferences in the appropriate menu option B Creating an appropriate LOGINSQL file C Issuing the ALTER USER statement D Issuing the ALTER TABLE statement

4. The EMP_SALARY table has two columns, EMP_USER and SALARY EMP_USER is set to be the same as the Oracle username To support user MARTHA, the salary administrator, you create a view with the following statement: CREATE VIEW EMP_SAL_VW AS SELECT EMP_USER, SALARY FROM EMP_SALARY WHERE EMP_USER <> 'MARTHA'; MARTHA is supposed to be able to view and update anyone in the company's salary except her own through this view Which of the following clauses do you need to add to your view creation statement in order to implement this functionality?

   1. WITH ADMIN OPTION
   2. WITH GRANT OPTION
   3. WITH SECURITY OPTION
   4. WITH CHECK OPTION
5. You are developing PL/SQL code to manipulate and store data in an Oracle table All of the following numeric datatypes in PL/SQL can be stored in an Oracle database, except one Which is it?
   1. CHAR
   2. RAW
   3. DATE
   4. INTEGER
6. You are performing some conversion operations in your PL/SQL programs To convert a date value into a text string, you would use which of the following conversion functions?
   1. CONVERT
   2. TO_CHAR
   3. TO_NUMBER
   4. TO_DATE
7. You have a table called TEST_SCORE that stores test results by student personal ID number, test location, and date the test was taken Tests given in various locations throughout the country are stored in this table A student is not allowed to take a test for 30 days after failing it the first time, and there is a check in the application preventing the student from taking a test twice in 30 days at the same location Recently, it has come to everyone's attention that students are able to circumvent the 30-day rule by taking a test in a different location Which of the following SQL statements would be useful for identifying the students who have done so?
   1. SELECT ASTUDENT_ID, ALOCATION, BLOCATION FROM TEST_SCORE A, TEST_SCORE B WHERE ASTUDENT_ID = BSTUDENT_ID AND ALOCATION = BLOCATION AND TRUNC(ATEST_DATE)+30 <= TRUNC(BTEST_DATE) AND TRUNC(ATEST_DATE)-30 >= TRUNC(BTEST_DATE);
   2. SELECT ASTUDENT_ID, ALOCATION, BLOCATION FROM TEST_SCORE A, TEST_SCORE B WHERE ASTUDENT_ID = BSTUDENT_ID AND ALOCATION <> BLOCATION AND TRUNC(ATEST_DATE)+30 >= TRUNC(BTEST_DATE) AND TRUNC(ATEST_DATE)-30 <= TRUNC(BTEST_DATE);
   3. SELECT ASTUDENT_ID, ALOCATION, BLOCATION FROM TEST_SCORE A, TEST_SCORE B WHERE ASTUDENT_ID = BSTUDENT_ID AND ALOCATION = BLOCATION AND TRUNC(ATEST_DATE)+30 >= TRUNC(BTEST_DATE) AND TRUNC(ATEST_DATE)-30 <= TRUNC(BTEST_DATE);
   4. SELECT ASTUDENT_ID, ALOCATION, BLOCATION FROM TEST_SCORE A, TEST_SCORE B WHERE ASTUDENT_ID = BSTUDENT_ID AND ALOCATION <> BLOCATION AND TRUNC(ATEST_DATE)+30 <= TRUNC(BTEST_DATE) AND TRUNC(ATEST_DATE)-30 >= TRUNC(BTEST_DATE);
8. You create a view with the following statement: CREATE VIEW BASEBALL_TEAM_VW AS SELECT BJERSEY_NUM, BPOSITION, BNAME FROM BASEBALL_TEAM B WHERE BNAME = USER; What will happen when user JONES attempts to SELECT a listing for user SMITH?
   1. The SELECT will receive an error
   2. The SELECT will succeed
   3. The SELECT will receive NO ROWS SELECTED
   4. The SELECT will add data only to BASEBALL_TEAM
9. You query the database with this command: SELECT atomic_weight FROM chart_n WHERE (atomic_weight BETWEEN 1 AND 50 OR atomic_weight IN (25, 70, 95)) AND atomic_weight BETWEEN (25 AND 75) Which of the following values could the statement retrieve?
   1. 51
   2. 95
   3. 30
   4. 75
10. What will the following operation return? [Choose two] SELECT TO_DATE('01-jan-00') - TO_DATE('01-dec-99') FROM dual;
    1. 365 if the NLS_DATE_FORMAT is set to 'DD-mon-RR'
    2. A VARCHAR2 value
    3. An error; you can't do this with dates
    4. -36493 if the NLS_DATE_FORMAT is set to the default value
11. What is the purpose of the SUBSTR string function?
    1. To insert a capital letter for each new word in the string
    2. To return a specified substring from the string
    3. To return the number of characters in the string
    4. To substitute a non-null string for any null values returned
12. Evaluate this command: SELECT iisotope, gcalibration FROM chart_n i, gamma_calibrations g WHERE ienergy = genergy; What type of join is the command?
    1. Equijoin
    2. Nonequijoin
    3. Self-join
    4. The statement is not a join query
13. In a SELECT statement, which character is used to pass in a value at runtime?
    1. \
    2. %
    3. &
    4. _ (underscore)
14. Which single-row function could you use to return a specific portion of a character string?
    1. INSTR
    2. SUBSTR
    3. LPAD
    4. LEAST
15. What will the following statement return? SELECT LAST_NAME, FIRST_NAME, START_DATE FROM EMPLOYEES WHERE HIRE_date< Trunc(sysdate) 5;
    1. Employees hired in the past 5 years
    2. Employess hired in the past 5 days
    3. Employees hired more thatn 5 years ago
    4. Employees hired more than 5 days ago
16. Which function(s) accept arguments of any datatype? Select all that apply
    1. SUBSTR
    2. NVL
    3. ROUND
    4. DECODE
    5. SIGN
17. What will be returned from SIGN(ABS(NVL(-32,0)))?
    1. 1
    2. 32
    3. 1
    4. 0
    5. NULL
18. Which functions could you use to strip leading characters from a character string Select two
    1. LTRIM
    2. SUBSTR
    3. RTRIM
    4. INSTR
    5. MOD
19. what will the following query return? SELECT REPLACE(RTRIM('Anticipation','on'), 'ti','shun') from DUAL;
    1. Anticipashun
    2. Anshuncipashun
    3. Anshuncipashunon
    4. Anticipashunon
20. In oracle, what do trigonometric functions operate on?
    1. Degrees
    2. Radians
    3. Gradients
    4. The default is radians, but degrees or gradients can be specified
21. If it is 5 minutes past noon on 15 jan 2000, what will the following statement return? SELECT ROUND(SYSDATE) ROUND(SYSDATE,'Y') FROM DUAL;
    1. 155
    2. 15
    3. 0
    4. 16
22. Which statement about nested functions is most correct?
    1. Single-row nested functions can be nested in either single-row or group functions
    2. Group functions can be nested in other group functions
    3. Group functions cab be nested in single-row functions
    4. A, B and C
    5. A and B only
23. Why will the following query raise an exception? SELECT DEPT_NO, AVG(DISTINCT SALARY), COUNT(JO
24. JOB_COUNT FRIM EMP WHERE MGR LIKE 'J%' OR ABS(SALARY)>10 HAVING COUNT (JO
25. >5 ORDER BY 2 DESC;
    1. A HAVING clause cannot contain a group function
    2. The GROUP BY clause is missing
    3. Abs() is not an oracle function
    4. The query will not raise an exception
26. Why does the following SELECT statement fail? SELECT colorname Colour, MAX(cost) From itemdetail Where upper(colorname) like '%WHITE%' Group by colour Having count(*) > 20;
    1. A GROUP BY clause cannot contain a coloumn alias
    2. The condition COUNT (*) > 20 should be in the WHERE clause
    3. The GROUP BY clause must contain the group functions used in the SELECT list
    4. The HAVING Clause can only contain the group functions used in the SELECT list
27. What will the following query report? SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;
    1. The number of employees in each department, including those without a deptnno
    2. The number of employees in each department, ecept those without a deptno
    3. The total number of employees, including those without a deptno
    4. The total number of employees, except those without a deptno
28. Which assertion about the following quires is true> SELECT COUNT(DISTICT mgr), MAX(DISTINCT salary) from emp; SELECT COUNT (ALL mgr), MAX(ALL salary) FROM emp;
    1. They will always return the same numbers in columns 1 and 2
    2. They may return different numbers in column 1 but will always return the same number in column 2
    3. They may return different numbers in column 1 and may return different numbers in column 2
    4. They will always return the same number in column 1 but may return different numbers in column 2
29. What is the limit on the number of values a subquery using the IN operator can return to the parent query?
    1. 1
    2. 32,764
    3. unlimited
    4. 0
30. When using multiple tables to query information, in which clause do you specify the table names?
    1. HAVING
    2. GROUP BY
    3. WHERE
    4. FROM
31. The contents of the CONTESTANTS table are listed as follows: NAME AGE COUNTRY ---------------- -------------- --------------- BERTRAND 24 FRANCE GONZALEZ 29 SPAIN HEINRICH 22 GERMANY TAN 39 CHINA SVENSKY
32. RUSSIA SOO 21 You issue the following query against this table: SELECT NAME FROM CONTESTANT WHERE (COUNTRY, AGE) IN ( SELECT COUNTRY, MIN(AGE) FROM CONTESTANT GROUP BY COUNTRY); What is the result?
    1. SOO
    2. HEINRICH
    3. BERTRAND
    4. GONZALEZ
33. To delete any constraint from the table we have to use command
    1. Drop Constraint
    2. Delet
    3. Alter *
    4. Truncate
34. All the operators are used in single row subquery except one
    1. Between and
    2. <>
    3. =
    4. in
35. All the commands executes in iSQLplus except one
    1. Column
    2. Compute
    3. define
    4. Accept
36. Ascript file which will be executed automatically in iSQLPlus is
    1. afiedtbuf
    2. loginsql
    3. both a and b
    4. none of the above
37. A command in iSQL plus is used to give the status of old and new value of variable
    1. set feedback
    2. set verify
    3. set confirm
    4. none of the above
38. All commands are used to save the changes of the transaction except one
    1. Commit
    2. exitting from sqlplus
    3. DDL command
    4. savepoint
    5. none of the above
39. A Clause which is used in joining two tables other than equality operator is
    1. join
    2. on
    3. in
    4. using
40. A Clause which is the pseudocolumn used to know the current value of the sequence
    1. nextval
    2. current_val
    3. currval
    4. none of the above
41. A Query which is used in top-N analysis is
    1. subquery
    2. correlated subquery
    3. inline query
    4. outer query
42. An operator is used to get and display the redundant records
    1. Union all
    2. Distinct
    3. Union
    4. Intersect
43. All the datatypes with respect to Oracle 9i is true except one
    1. DATE
    2. TIMESTAMP
    3. TIMSTAMP with TIME ZONE
    4. TIMESTAMP WITH LOCAL TIME ZONE
    5. None of the above
    
    

Useful Oracle Guidelines or Tips

* Data is retrieved from Oracle using SELECT statements. *The syntax for a SELECT statement consists of SELECT.FROM.;. *Expressions appearing after the keyword SELECT are part of the column clause, and are usually the names of columns from the table storing the data you wish to retrieve.. *Expressions appearing after the FROM keyword are part of the table clause, and are usually the names of tables you want to retrieve data from. *When you’re entering a SELECT statement from the prompt using SQL*Plus, a semicolon (;) at the end of the statement or a slash (/) at the beginning of the first empty line appearing after the statement in your operating buffer must be used to terminate the statement. *Arithmetic operations can be used to perform math operations on data selected from a table or on numbers using the DUAL table. *The DUAL table is a table with one column and one row used to fulfill the syntactic requirements of SQL SELECT statements. *Values in columns for particular rows may be empty (NULL). *If a column contains a NULL value, you can use the NVL() function to return meaningful information instead of an empty field. *Aliases can be used in place of the actual column name or to replace the appearance of the function name in the header. *Output from two columns can be concatenated together using a double pipe (||). Alternately, the CONCAT() function can be used for this purpose. *SQL commands can be entered directly into SQL*Plus on the command line. *You can edit mistakes in SQL*Plus with the CHANGE command. If a mistake is made, the CHANGE(c/old/new) command is used. *Alternatively, the EDIT (ed) command can be used to make changes in your favorite text editor. *You can specify you favorite text editor by issuing the DEFINE_EDITOR command at the prompt. *Use the acronym PEMDAS to remember the correct order for operator precedence. *There are a host of commands available in SQL*Plus that are not part of Structured Query Language to be aware of. A few to pay close attention to include: o GET for retrieving SQL scripts into SQL*Plus o RUN for executing retrieved SQL scripts o @ for getting and running a script in one operation o DESCRIBE for listing the columns in a particular table, along with their datatypes o SPOOL for telling SQL*Plus to write the contents of your session to a file *The ORDER BY clause in a SELECT statement is a useful clause for incorporating a sort order into the output of a file. *The sort orders that can be used are ascending and descending, abbreviated as ASC and DESC, respectively. The order is determined by the column identified in the ORDER BY clause. The default is ASC. *The WHERE clause is used in SQL queries to limit the data returned by a query. *The WHERE clauses contain comparison operations that determine whether a row will be returned by a query. *The logical comparison operations include =, >, >=, <, <=, <>, !=, and ^=. *In addition to the logical operations, a comparison operation, called LIKE, can be used to pattern matching. The % and _ characters are used to designate wildcards. *The rage operation is called BETWEEN. *The fuzzy logic operation is called SOUNDEX. *The WHERE clause can contain one or more comparison operations linked together by using AND or OR and preceded by NOT. *SQL functions are broken down into character functions, number functions, and date functions. Be sure you know how to use these functions for the OCP exam: Text Functions o lpad(x,y[,z]) and rpad(x,y[,z]) Return data in string or column x padded on the left or right side, respectively, to width y. The optional value z indicates the character(s) that lpad() or rpad() use to pad the column data. If no character z is specified, a space is used. o lower(x), upper(x), and initcap(x) Return data in string or column x in lowercase or uppercase characters, respectively, or change the initial letter in the data from column x to a capital letter. o length(x) Returns the number of characters in string or column x. o substr(x,y[,z]) Returns a substring of string or column x, starting at the character in position number y to the end, which is optionally defined by the character appearing in the position z of the string. o instr(x,y) Determines whether a substring y given can be found in string x. o trim() A single-row function that behaves like a combination of ltrim() and rtrim(). Trim() accepts a string describing the data you would like to trim from a column value using the following syntax: trim([[keyword] ‘x’ from] column). Here keyword is replaced by leading, trailing, or both, or it’s omitted. Also, x is replaced with the character to be trimmed, or it’s omitted. If x is omitted, Oracle assumes it must trim whitespace. Finally, column is the name of the column in the table to be trimmed. Arithmetic Functions o abs(x) Obtains the absolute value for a number. For example, the absolute value of –1 is 1, whereas the absolute value of 6 is 6. o round(x,y) Rounds x to the decimal precision of y. if y is negative, it rounds to the precision of y places to the left of the decimal point. This can also be used on DATE columns. o ceil(x) Similar to executing round on an integer, except ceil always rounds up. o floor(x) Similar to ceil, except floor always rounds down. o mod(x,y) The modulus of x, defined in long division as the integer remainder when x is divided by y until no further whole number can be produced. o sign(x) Displays an integer value corresponding to the sign of x: 1 if x is positive, -1 if x is negative. o sqrt(x) The square root of x. o trunc(x,y) Truncates x to the decimal precision of y. If y is negative, it truncates to y number of places to the left of the decimal point. o vsize(x) The storage size in bytes for x. List Functions o greatest(x,y,…) Returns the highest value from the list of text, strings, numbers or dates. o least(x,y,…) Returns the lowest value from the list of text strings, numbers, or dates. o decode(column name, val1, sub1, val2, sub2,…) Works on the same principle as the if-then-else statement does in many common programming languages. Date Functions o add_months(x,y) Returns a date corresponding to date x plus y months. o last_day(x) Returns the date of the last day of the month that contains date x. o months_between(x,y) Returns a number of months between dates x and y. If date x is earlier than y, the result is negative; otherwise, the result is positive. If dates x and y contain the same day of different months, the result is an integer; otherwise, the result is a decimal. o new_time(x,y,z) Returns the current date and time for date x in time zone y as it would be in time zone z. o next_day(x) Identifies the name of the next day from given date, x. *Several conversion functions are available for transforming data from text to numeric datatypes and back, numbers to dates and back, text to ROWID and back, and so on. Conversion Functions o to_char(x) Converts the value x to a character or converts a date to a character string using formatting conventions. o to_number(x) Converts nonnumeric value x to a number. o to_date(x[,y]) Converts the nondate value x to a date using the format specified by y. o to_multi_byte(x) Converts the single-byte character string x to multibyte characters according to national language standards. o to_single_byte(x) Converts the multibyte character string x to single-byte characters according to national language standards. o chartorowid(x) Converts the string of characters x into an Oracle ROWID. o rowidtochar(x) Converts the ROWID value into the string of characters x of VARCHAR2 datatype. *SELECT statements that obtain data from more than one table and merge the data together are called joins. *In order to join data from two tables, a common column must exist. *A common column between two tables can create a foreign key, or link, from one table to another. This condition is especially true if the data in one of the tables is part of the primary key – the column that defines uniqueness for rows on a table. *A foreign key can create a parent/child relationship between two tables. *One type of join is the inner join, or equijoin. An equijoin operation is based on an equality operation linking the data in common columns of two tables. *When joining tables using Oracle syntax, a join comparison on the common columns in the tables must be included in the WHERE clause of the query (i.e., WHERE a.empno=b.empno). *When joining tables using ANSI/ISO syntax, the join tablename on join_comparison clause must be used (i.e., FROM a JOIN b ON a.empno=b.empno). the join comparison operation is thus kept separate from other filter comparisons that might otherwise appear in the WHERE clause. *ANSI/ISO and Oracle syntax for joins is logically equivalent. There is no performance advantage for using one over the other. However, ANSI/ISO and Oracle syntax for joins cannot be used together in the same SQL query. *Another type of join is the outer join. An outer join returns data in one table even when there is no data in the other table. The “other” table in the outer join operation is called the outer table. *The common column that appears in the outer table of the join must have a special marker next to it in the comparison operation of the SELECT statement that creates the table. *The Oracle-syntax outer join symbol is “(+)”, which is equivalent to the ANSI/ISO [left|right] outer join clause. Whether the outer join is a left join or right join depends on where the outer join symbol is placed (i.e., left join: all values from the table on the left of the statement are shown, even if the value in the right table is null; right join: all values from the table on the right side of the statement are shown, even if the value in the left table is null). Example: SELECT e.ename, e.deptno, d.dname FROM dept d, emp e WHERE d.deptno (+) = e.deptno is the same as SELECT e.ename, e.deptno, d.dname FROM emp e left outer join dept d on d.deptno=e.deptno. *If the column name is the same in both tables, the common column in both tables used in Oracle-syntax join operations must be preceded either with a table alias that denotes the table in which the column appears or the entire table name. *The data from a table can be joined to itself. This technique is useful in determining whether there are rows in the table that have slightly different values but are otherwise duplicate rows. This is called a self-join operation. *Table aliases must be used in self-join SELECT statements. Example: SELECT e.empno, e.ename, e.job FROM emp e, emp e2 WHERE e.empno <> e2.empno AND e.name = e2.name; *A Cartesian product is formed when you omit a join comparison from your Oracle-syntax join statement, or when you use the cross join keywords in ANSI/ISO syntax. Example: SELECT col1, col2 FROM example1 cross join example2; *A natural join can be used to simplify join operations. Natural joins are possible in ANSI/ISO syntax when all common columns between joined tables have the same name. Use the natural keyword to specify to Oracle to execute a natural join. You do not need to specify a join comparison in order to execute a natural join. Example: SELECT ename, deptno, dname FROM emp natural join dept; *Data output from table SELECT statements can be grouped together according to criteria set by the query. *The group by clause assists you in grouping data together. Example: SELECT job, count(job) FROM emp GROUP BY job; *Several grouping functions are available that allow you to perform operations on data in a column as though the data were logically one variable. *The grouping functions are max(), min(), sum(), avg(), stddev(), variance(), and count(). o avg(x) Averages all x column values returned by the SELECT statement. o count(x) Counts the number of non-NULL values returned by the SELECT statement for column x. o max(x) Determines the maximum value in column x for all rows returned by the SELECT statement. o min(x) Determines the minimum value in column x for all rows returned by the SELECT statement. o stddev(x) Calculates the standard deviation for all values in column x in all rows returned by the SELECT statement. o sum(x) Calculates the sum of all values in column x in all rows returned by the SELECT statement. o variance(x) Calculates the variance for all values in column x in all rows returned by the SELECT statement. *These grouping functions can be applied to the column values for a table as a whole or for subsets of column data for rows returned in group by statements. *Data in a group by statement can be excluded or included based on a special set of where criteria defined specifically for the group in a having clause. *The data used to determine the having clause can be specified at runtime by the query. *NULL values are ignored by group functions. To force group functions not to ignore NULL values, use the nvl() function. *The data used to determine the having clause can either be specified at runtime by the query or by a special embedded query, called a subquery which obtains unknown search criteria based on known search methods. *Subqueries can be used in other parts of the SELECT statement to determine unknown search criteria, as well. Subqueries are generally included in this fashion in the WHERE clause. *Subqueries can use columns in comparison operations that are local to the table specified in the subquery, or they can use columns that are specified in tables named in any parent query to the subquery. This use is based on the principles of variable scope. All variables or columns named in comparison operations in the outermost SELECT statement operation are local to that operation and global to all the nested subqueries. *Various types of subqueries you might encounter when using Oracle include the following: o Single-row subqueries The main query expects the subquery to return only one value. Example: SELECT empno FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = ‘John Smith’); o Multiple-row subqueries The main query can handle situations where the subquery returns more than one value. Example: SELECT ename, job, sal FROM emp WHERE deptno in (SELECT deptno FROM dept WHERE dname in (‘Accounting’,’Sales’); o Inline views A subquery in a FROM clause used for defining an intermediate result to query from. Example: SELECT ename, job, sal, rownum FROM (SELECT ename, job, sal FROM emp ORDER BY sal) where rownumb <=3; Notice that rownum is a virtual column identifying the row number in the table to deterime the top number of rows to return as output. o Multiple-column subqueries A subquery that contains more than one column of return data in addition to however many rows are given in the output. Example: SELECT deptno, ename, job, sal FROM emp WHERE (deptno,sal) in (SELECT deptno, max(sal) FROM emp GROUP BY deptno); *Be sure you understand how to set up and use a correlated subquery in Oracle to retrieve data. Oracle performs a correlated subquery when the subquery references a column from a table referred to in the parent statement. Example: SELECT e.ename, e.job e.sal FROM emp e WHERE exists (SELECT d. deptno FROM dept d WHERE d.loc = ‘New York’ AND d. deptno = e.deptno); *Recall that most subqueries (even those returning multiple rows) generally only return one column of output per row. However, you can construct subqueries that return multiple colums. *Subqueries that contain group by expressions will ignore rows if the group by column contains NULL values for those rows. Be sure you understand how to rewrite such queries, if necessary, to obtain those NULL values. Example: SELECT e.deptno, e.ename, e.job, e.sal FROM emp e WHERE e.sal = (SELECT max(e2.sal) from emp e2 where nvl(e.deptno,99) = nvl(e2.deptno,99)); *A subquery found in a FROM clause of the parent SQL query is called an inline view. Be sure you recall the syntax involved in using inline views, especially if you want to refer directly to columns in an inline view. Recall the use of inline views for top-N queries as well. *Review the SQL*Plus environment characteristics that can be configured using the set command. o ARRAYSIZE [ARRAY] {15|n} this command sets the number of rows that SQL*Plus fetches from the database at one time. Valid values are 1 to 5,000. A large value increases the efficiency of queries and subqueries that fetch many rows, but it requires more memory. o AUTOTRACE [AUTO] {OFF|ON|TRACEONLY|EXPLAIN|STATISTICS} This command displays a report on the execution of successful SQL statements. The report can include execution statistics and the query execution path. OFF does not display a trace report. ON displays a trace report. TRACEONLY displays a trace report, but does not print query data, if any. Before using autotrace, you must run the plustrce.sql script fond in the sqlplus/admin directory under your Oracle software home directory. The EXPLAIN and STATISTICS options can be used for performance tuning as well by displaying SQL statement execution plans and statistics from the cost-based optimizer, respectively. o COLSEP [COLSEP] {|text} This command sets the text to be printed between selected columns. If the colsep variable contains blanks or punctuation characters, you must enclose it with single quotes. The default value for text is a single space. In multilane rows, the column separator does not print between columns that begin on different lines. o FEEDBACK [FEED] {6|n|OFF|ON} This displays the number of records returned by a query when a query selects at least n records. ON or OFF turns this display on or off. Turning feedback ON sets n to 1. Setting feedback to 0 is equivalent to turning it OFF. o HEADING [HEA] {OFF|ON} This command controls the printing of column headings in reports. ON prints column headings in reports; OFF suppresses column headings. The set heading OFF command will not affect the column width displayed; it only suppresses the printing of the column header itself. o LINESIZE [LIN] {80|n} This sets the total nmber of characters that SQL*Plus displays on one line before beginning a new line. You can define linesize as a value from 1 to a system-dependent maximum. o LONG [LONG] {80|n} This sets the maximum width (in bytes) for displaying LONG, CLOB, and NCLOB values as well as for copying LONG values. The maximum valule of n is 2 GB. o PAGESIZE [PAGES] {24|n} This sets the number of lines in each page. You can set pagesize to 0 to suppress all headings, page breaks, titles, the initial blank line, and other formatting information. o PAUSE [PAU] {OFF|ON|text} This command enables you to control scrolling of your terminal when running reports. ON causes SQL*Plus to pause at the beginning of each page of report output. You must press ENTER after each pause. The text you enter specifies the text to be displayed each time SQL*Plus pauses. If you enter multiple words, you must enclose the text in single quotes. You can embed terminal-dependent escape sequences in the pause command. These sequences enable you to create inverse video messages or other effects on terminals that support such characteristics. o SUFFIX [SUF] {SQL|text} This sets the default file extension that SQL*Plus uses in commands that refer to command files. The value for suffix does not control extensions for spool files. o TERMOUT [TERM] {OFF|ON} This controls the display of output generated by commands executed from the command file. OFF suppresses the display so that you can spool output from a command file without seeing the output on the screen. ON displays the output. Setting termout OFF does not affect output from commands you enter interactively. *In addition, be sure you understand completely how to use the following SQL*Plus commands for enhancing output readability: o format COLUMN {col} FORMAT {fmt} HEADING {string}. Example: COLUMN empno FORMAT 999999 or COLUMN ename FORMAT a12 o underline UNDERLINE {-|c|ON|OFF} This command sets the character used to underline column headings in SQL*Plus reports to c. Note, c cannot be an alphanumeric character or a whitespace character. ON or OFF turns underlining on or off. ON changes the value of c back to the default (-). Example: set underline * o wrap WRAP {OFF|ON} The wrap variable controls whether SQL*Plus truncates the display of a selected row if it is too long for the current line width. OFF truncates the selected row; ON emables the selected row to wrap to the next line. Example: set recsep wrapped o break Sometimes when the information returned by your SQL query is ordered on a column, you may have multiple rows of data, each with the same value in the ordered column. The output can be changed so that only the first in a series of rows, where the ordered column values is the same, will show the column value. Example: break on deptno o compute The computer command performs one of several grouping functions on the column you are breaking on, including sum, minimum, maximum, avg (average), std (standard deviation), variance, count, and number (number of rows in the column). Example: compute sum of sal on deptno o ttitle and btitle If you want a top or bottom title to appear on each page of a report, you can place one through the use of the ttitle and btitle commands, respectively. The syntax is [btitle|ttitle] position ‘title_text’, where position can be LEFT, CENTER, RIGHT, or COLn to indicate a fixed number of characters from the left to start the title line. *Variables can be set in a SELECT statement at runtime with the use of runtime variables. A runtime variable is designated with the ampersand character (&) preceding the variable name. Example: SELECT empno, deptno, sal FROM emp WHRE ename =’&name’; *The special character that designates a runtime variable an be changed using the set define command. *The define command can identify a runtime variable value to be picked up by the SELECT statement automatically. Example: define var_empno = 7844 *Once defined, the variable remains defined for the rest of the session or until it is undefined by the user or process with the undefined command. Example: undefined var_empno *You can modify the message that prompts the user to input a variable value. This activity is performed with the accept command. Example: accept var_empno prompt ‘Enter EMPNO now =>’ *Every time you execute a SQL statement in SQL*Plus, that statement gets saved to a buffer used by SQL*Plus for repeat execution. This SQL statement can be saved using the save command. Example: save employee.sql *The basic types of data relationships in Oracle include primary keys and functional dependency within a table as well as foreign key constraints from one table to another. *A relational database is composed of objects that store data, objects that manage access to data, and objects that improve performance when accessing data. *A table can be created using a create table statement. Example: CREATE TABLE emp (empno number primary key, name varchar2(50), ssn number(9)); *Tables can be added using data from another table. Example: CREATE TABLE emp AS SELECT * FROM scott.emp WHERE deptno = 10; *A temporary table is created using the create global temporary table command. Data in a temporary table is private to the session, meaning that each session can see and modify only its own data in the temporary table. *Oracle database object names must begin with a letter and can usually be between 1 and 30 characters long, except for databases (which have a maximum of eight characters) and database links (with a maximum of 128 characters). Names are not case-sensitive. *A user cannot own or refer to two objects with the same name, so if both you and SCOTT own a table called EMPLOYEE, you must prefix references to EMPLOYEE with the schema owner. *Don’t name a table DUAL, because Oracle already has table called DUAL that is accessible by everyone. *Don’t use table names beginning with SYS. *You can only use the following three special characters in table and column names: #, $, and _. *Don’t use special characters from European or Asian character sets in a database name, global database name, or database link name. *An object name cannot be an Oracle reserved word, such as SELECT or FROM; a datatype, such as NUMBER; or a built-in function, such as DECODE(). Oracle may not complain when you create the object, but it may give you an unpleasant surprise when you refer to the object in your SQL statement. *A table can be dropped using the drop table statement. Example: DROP TABLE emp; *A table can be renamed using the alter table rename statement. Example: RENAME emp to employee or ALTER TABLE emp RENAME TO employee; *A table can be truncated using the truncate table statement. Truncating a table removes all row data from a table quickly, while leaving the definition of the table intact, including the definition of constraints and any associated database objects such as indexes, constraints, and triggers on the table. When you truncate the table, Oracle resets the high-water mark to zero. Once this operation has been completed, the data cannot be recovered unless you have a backed-up copy of the data. *Comments can be added to a table or column using the comment command. Example: COMMENT ON TABLE emp IS ‘This is a test table’ or COMMENT ON column emp.empno IS ‘unique employee number’; *A table can be created with five different types of integrity constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, not NULL, and CHECK. *Referential integrity often creates a parent/child relationship between two tables – the parent being the referenced table and the child being the referring table. Often a naming convention that requires child objects to adopt and extend the name of the parent table is useful in identifying these relationships. *The datatypes available for creating columns in tables are CHAR, VARCHAR2, NUMBER, DATE, RAW, LONG, LONG RAW, ROWID, BLOB, CLOB, NCLOB, and BFILE. o CHAR(n) Contains fixed text strings of n bytes, where n can be up to 2,000 bytes in Oracle. o VARCHAR2(n) Contains variable-length text strings of length n bytes, where n can be of up to 4,000 bytes. o NUMBER(n[,m]) Contains numeric data of up to n digits in length, where n can be up to 38 digits in Oracle. A NUMBER can also have an optimal m number of digits to the right of the decimal point. This collection of digits is called a mantissa. The mantissa can have up to 38 digits as well. If no value is specified for n, Oracle defaults to 38. o DATE Contains date information. DATE columns are seven bytes in length. o RAW Contains binary data of up to 2, 000 bytes in Oracle. This is a variable length datatype like VARCHAR2 in Oracle. o LONG Contains text data of up to 2GB. o LONG RAW Contains binary data of up to 2GB. o ROWID Contains the address for rows in your table. These could be physical ROWIDs or logical ROWIDs. o BLOB Stores large unstructured binary object data of up to 4GB. o CLOB Stores large database character set data of up to 4GB. o NCLOB Stores large single-byte or multibyte character-based Unicode character set data of up to 4 GB. o BFILE Stores pointers to large unstructured operating system files outside the Oracle database. *A table column can be added or modified with the alter table statement. Example: ALTER TABLE emp add (ename varchar2(25)) or ALTER TABLE emp modify (ename varchar2(30)) *Columns can be added with little difficulty if they are nullable, using the alter table add (column_name datatype) statement. If a not NULL constraint is desired, add the column, populate the column with data, and then add the not NULL constraint separately. *Column datatype size can be increased with no difficulty using the alter table modify (column_name datatype) statement. Column size can be decreased, or the datatype can be changed, only if the column contains NULL for all rows. *Constraints can be added to a column only if the column already contains values that will not violate the added constraint. *PRIMARY KEY constraints can be added with the table constraint definition by using the alter table add (constraint constraint_name primary key (column_name)) statement or with a column constraint definition using the alter table modify (column_name constraint constraint_name primary key) statement. Example: CREATE TABLE emp (empno number primary key, name varchar2(50)) or CREATE TABLE emp (empno number, name varchar2(50) CONSTRAINT pk_emp_01 primary key (empno)) or ALTER TABLE add CONSTRAINT pk_emp_01 primary key (empno) or ALTER TABLE modify (empno CONSTRAINT pk_emp_01 primary key) *UNIQUE constraints can be added with the table constraint definition by using the alter table add (constraint constraint_name unique (column_name)) statement or with a column constraint definition by using the alter table modify (column_name constraint constraint name unique) statement. Example: CREATE TABLE emp (empno number unique, name varchar2(50)) or CREATE TABLE emp (empno number, name varchar2(50) CONSTRAINT uk_emp_01 unique (empno)) or ALTER TABLE add CONSTRAINT uk_emp_01 unique (empno) or ALTER TABLE modify (empno CONSTRAINT uk_emp_01 unique); *FOREIGN KEY constraints can be added with a table constraint definition by using the alter table add (constraint constraint_name foreign key (column_name) references OWNER.TABLE (column_name) [on delete cascade]) statement or with a column constraint definition by using the alter table modify (column_name constraint constraint_name references OWNER.TABLE (column_name) [on delete cascade]) statement. Example: CREATE TABLE emp (empno number, name varchar2(50) references scott.employee (ename) on delete set null) or ALTER TABLE add CONSTRAINT fk_emp_01 foreign key (name) references scott.employee (ename) or ALTER TABLE modify (empno CONSTRAINT fk_emp_01 foreign key (name) references scott.employee (ename); *CHECK constraints can be added with a table constraint definition by using the alter table add (constraint constraint_name check (check_condition)) statement or with a column constraint definition by using the alter table modify (column_name constraint constraint_name check (check_condition)) statement. Example: CREATE TABLE emp (empno number primary key, name varchar2(50), salary (8,2) check (salary <= 100000) or ALTER TABLE add CONSTRAINT ck_emp_01 check (salary <= 100000) or ALTER TABLE modify (salary CONSTRAINT ck_emp_01 check (salary <= 100000); *The check condition cannot contain subqueries, references to certain keywords (such as user, sysdate, and rowed) or any pseudocolumns. *Not NULL constraints can be added with a column constraint definition by using the alter table modify (column_name NOT NULL) statement. Example: CREATE TABLE emp (empno number primary key, name varchar2(50) NOT NULL) or ALTER TABLE modify (name NOT NULL); *A named PRIMARY KEY, UNIQUE, CHECK, or FOREIGN KEY constraint can be dropped with the alter table drop constraint constraint_name statement. A NOT NULL constraint is dropped using the alter table modify (column_name NULL) statement. Example: ALTER TABLE DROP CONSTRAINT pk_emp_01 or ALTER TABLE MODIFY (name NULL); *If a constraint that created an index automatically (such as a primary key or UNIQUE constraint) is dropped, the corresponding index is also dropped. *A constraint can be disabled using the alter table disable constraint statement. Example: ALTER TABLE employee DISABLE primary key or ALTER TABLE employee DISABLE CONSTRAINT uk_employee_01; *You may experience a problem if you attempt to disable a primary key when existing foreign keys depend on that primary key. *A constraint can be re-enabled using the alter table enable statement. Example: ALTER TABLE department ENABLE primary key or ALTER TABLE employee ENABLE uk_employee_01; *If you disable a constraint and then load data into the table column that violates the integrity constraint while the constraint is disabled, your attemp to enable the constraint later with the alter table enable constraint statement will fail. *You will need to use a special table called EXCEPTIONS (created by running the utlexcpt.sql script from rdbms/admin under the Oracle software home directory) to identify and correct offending records. *When there are constraints on other tables that reference the tables to be dropped, you can use the cascade constraints clause in your drop table statement. The constraints in other tables that refer to the table being dropped are also dropped with cascade constraints. Example: DROP TABLE department CASCADE CONSTRAINTS; *If the table is dropped, all constraints, triggers, and indexes created for the table are also dropped. *Removing all data from a table is best accomplished with the truncate command rather than the delete from table_name statement because truncate resets the table’s high-water mark and deallocates all the table’s storage quickly, thus improving performance on SELECT count() statements issued after the truncation. *An object name can be changed with the rename statement or with the use of synonyms. *Indexes are created automatically in conjunction with primary key and UNIQUE constraints. The indexes are named after the constraint name given to the constraint in the definition of the table. *Tables are created without any data in them, except for tables created with the create table as select statement. These tables are crated and prepopulated with data from another table. *New rows are put into a table with the insert statement. The user issuing the insert statement can insert one row at a time with one statement or can perform a mas insert operation with insert into table_name (select…..). Example: INSERT INTO emp (empno, name) values (12345,’John Smith’) or INSERT INTO emp (SELECT * FROM scott.emp); *Existing rows in a database table can be modified using the update statement. The update statement contains a where clause similer in function to the where clause of select statements. Example: UPDATE emp set name = null or UPDATE emp set name = ‘Jane Smith’ where empno = 12345; *Existing rows in a table can be deleted using the delete statement. The delete statement also contains a where clause similar in function to the where clause in update and select statements. Example: DELETE FROM emp or DELETE FROM emp WHERE name = ‘Jane Smith’; *Transaction processing controls the change of data in an Oracle database. *Transaction controls include commands that identify the beginning, breakpoint, and end of a transaction as well as the locking mechanism that prevent more than one user at a time from making changes in the database. o set transaction Initiates the beginning of a transaction and sets key features. This command is optional. A transaction will be started automatically when you start SQL*Plus, commit the previous transaction, or roll back to the previous transaction. the set transaction isolation level serializable command specifies serializable transaction isolation mode as defined in SQL92. o commit Ends the current transaction by saving database changes and starts a new transaction. o rollback Ends the current transaction by discarding database changes and starts a new transaction. o savepoint Defines breakpoints for the transaction to enable partial rollbacks. *A table-level lock enables only the user holding the lock to change any piece of row data in the table, during which time no other users can make changes anywhere on the table. *A row-level lock gives the user the excusive ability to change data in one or more rows of the table. *An update statement requires a special rwo-level lock called a row-exclusive lock, which means that for the period of time the update statement is executing, no other user in the database ccan view or change the data in the row. The same goes for delete or insert operations. Another update statement – the select for update statement – acquires a more lenient lock called the share row lock. This lock means that for the period of time the update statement is changing the data in the rows of the table, no other user may change that row, but users may look at the data in the row as it changes. *A vew is a virtual table defined by a select statement. *Views can distill data from tables that may be inappropriate for some users, and they can hide the complexity of data joined from several tables. You can also mask the complexity that arises when you perform many single-row or group operations on the data returned by the view’s query. *The two types of views are simple and complex. *Simple views are those that only have only one underlying table. Example: CREATE OR REPLACE VIEW emp_view AS (SELECT * FROM emp WHERE job = ‘ANALYST’); *Complex views are those with two or more underlying tables that have been joined together. Example: CREATE OR REPLACE VIEW emp_dept_view AS (SELECT empno, ename, job, dname, loc FROM emp e, dept d WHERE e.deptno = d.deptno and job in (‘ANALYST’,’CLERK’,’MANAGER’)); *Data may be inserted into simple views, except in the following cases: o If the with check option clause is used, the user may not insert, delete, or update data on the table underlying the simple view if the view itself is not able to select that data for the user. o The user may not insert, delete, or update data on the table underlying the simple view if the SELECT statement creating the view contains group by, order by, or a single-row operation. o No data may be inserted in simple views that contain references to any virtual columns, such as ROWID, CURRVAL, NEXTVAL, and ROWNUM. o Not data may be inserted into simple views that are created with the read only option. *Data may be inserted into complex views when all the following conditions are true: o The statement affects only one of the tables in the join. o For update statements, all columns changed are extracted from a key-preserved table. In addition, if the view is created with the with check option clause, join columns and columns taken from the tables that are referenced more than once in the view are not part of the update. o Fore delete statements, the join has only one key preserved table. This table may be present more than once in the join, unless the view has been created with the check option clause. o For insert statements, all columns where values are inserted must come from a key-preserved table, and the view must not have been created with the with check option. *The with check option clause, upon creating a view, enables this simple view to limit the data that can be inserted or otherwise changed on the underlying table by requiring that the data change can be selectable by the view. *A join view is simply another name for a complex view. *A key-preserved table is a table in a complex view whose primary key column is present in the view and whose values are all unique and not NULL in the view. *Views containing outer joins generally won’t contain key preserved tables unless the outer join generates no NULL values. *Modifying the data selected by a view requires re-creating the view with the create or replace view statement or dropping the view first and issuing the create view statement. *An existing view can be recompiled by executing the alter view statement if for some reason it becomes invalid due to object dependency. Example: ALTER VIEW emp_dept_view COMPILE; *A view is dropped with a drop view statement. Example: DROP VIEW profits_view; *Oracle doesn’t remove views from the database if a base table is destroyed. Oracle simply marks the view as invalid. *A sequence generates integers based on rules that are defined by sequence creation. *Options that can be defined for sequences include the first number generated, how the sequence increments, the maximum value, the minimum value, whether the sequence can recycle numbers, and whether numbers will be cached for improved performance. Example: CREATE SEQUENCE countdown_20 START WITH 20 INCREMENT BY –1 MAXVALUE 20 MINVALUE 0 CYCLE ORDER CACHE2; o start with n Enables the creator of the sequence to specify the first value generated by the sequence. If no start with value is specified, Oracle defaults to a start value of 1. o increment by n Defines the number by which to increment the sequence every time the NEXTVAL virtual column is referenced. The default for this clause is 1 if it is not explicitly specified. o minvalue n Defines the minimum value that can be produced by the sequence. If no minimum value is specified, Oracle will assume the default, nominvalue. o maxvalue n Defines the maximum value that can be produced by the sequence. If no maximum value is desired or specified, Oracle will assume the default, nomaxvalue. o cycle Enables the sequence to recycle values produced when maxvalue or minvalue is reached. If cycling is not desired or not explicitly specified, Oracle will assume the default, nocycle. You cannot specify cycle in conjunction with nomaxvalue or nominvalue. if you want your sequence to cycle, you must specify maxvalue for incrementing sequences or minvalue for decrementing or countdown sequences. o cache n Enables the sequence to cache a specified number of values to improve performance. If caching is not desired or not explicitly specified, Oracle will assume the default, which is to cache 20 values. o order Enables the sequence to assign values in the order in which requests are received by the sequence. If order is not desired or not explicitly specified, Oracle will assume the default, noorder. *Sequences are used by selecting from the CURRVAL and NEXTVAL virtual columns. *The CURRVAL column contains the current value of the sequence. *Selecting from NEXTVAL increments the sequence and changes the value of CURRVAL to whatever is produced by NEXTVAL. *The rules that a sequence uses to generate values can be modified using the alter sequence statement. The effect is immediate. Example: ALTER SEQUENCE countdown_20 INCREMENT BY –4; *A sequence can be deleted with the drop sequence statement. Example: DROP SEQUENCE countdown_20; *References to sequences cannot be used in subqueries of SELECT statements (including those with having), views, and SELECT statements using set operations (such as union and minus), or any SELECT statement that requires a sort to be performed. *Some indexes in a database are created automatically, such as those supporting the primary key and the unique constraints on a table. *Other indexes are created manually to support database performance improvements. o unique indexes CREATE UNIQUE INDEX emp_empno_01 ON emp (empno); o nonunique indexes CREATE INDEX emp_sal_01 ON emp (sal); o composite indexes CREATE UNIQUE INDEX employee_empno_ename_indx_01 ON emp (empno, ename); o reverse key indexes CREATE INDEX emp_ename_reverse_indx ON emp (ename) REVERSE; o bitmap indexes CREATE BITMAP INDEX emp_deptno_indx_01 ON emp (deptno); o function-based indexes CREATE INDEX idx_emp_01 ON emp (sal*1.08); *Indexes created manually are often on non-unique columns. *B-tree indexes work best on columns that have high cardinality – that is, columns that contain a large number of distinct values and few duplicates. *B-tree indexes improve performance by storing data in a binary search tree and then searching for values in a tree using a divide-and-conquer methodology, as outlined in this chapter. *Bitmap indexes improve performance on columns with low cardinality – that is, columns that contain few distinct values and many duplicates. *Columns stored in an index can be changed only by dropping and recreating the index. *Indexes can be deleted by issuing the drop index statement. Example: DROP INDEX employee_last_first_indx_01; *A function-based index is a new type of index in Oracle that is designed to improve query performance by making it possible to define an index that works when your WHERE clause contains operation on columns. *To create a function-based index in your own schema on your own table you must have the CREATE INDEX and QUERY REWRITE system privileges. *To create a function-based index in another schema or on another schema’s table, you musht have the CREATE ANY INDEX and GLOBAL QUERY REWRITE privileges. The table owner must also have the EXECUTE object privilege on the functions used in the function-based index. *To enable the use of function-based indexes, you must issue two alter session statements: ALTER SESSION SET query_rewrite_enabled = true and ALTER SESSION SET query_rewrite_integrity = trusted; *Synonyms are alternative names that can be crated as database objects in Oracle to refer to a table or view. *Two types of synonyms exist in Oracle: private synonyms and public synonyms. *You can use a private synonym within your own schema to refer to a table or view by an alternative name. *Public synonyms are publicly available to all users of Oracle; however you need special privileges to create public synonyms. *Creating a synonym is accomplished with the create synonym or create public synonym command. Example: CREATE SYNONYM all_my_emps FOR emp or CREATE PUBLIC SYNONYM emp FOR scott.emp; *Synonyms do not give you access to data in a table that you do not already have access to. Only privileges can do that. Synonyms simply enable you to refer to a table without prefixing the schema name to the table reference. *The Oracle database security model consists of two parts: limiting user access with password authentication and controlling object use with privileges. *Available privileges in Oracle include system privileges, for maintaining database objects, and object privileges, for accessing and manipulating data in database objects. *Users can be created using the create user identified by statement. Example: CREATE USER turner IDENTIFIED BY ike; *Users that are created can be authenticated by a host. The username identifying the user in Oracle must match that used on the host system, prefixed by OPS$. Example: CREATE USER OPS$harvey IDENTIFIED BY externally; *Once a user is created, it does not have any privileges. The DBA can then grant privileges to the user. *Usernames can be up to 30 characters in length and can contain alphanumeric characters as well as the $, #, and _ characters. *Changing a password can be performed by a user with the alter user identified by statement. Example: ALTER USER SCOTT IDENTIFIED BY 123456; *Granting system and object privileges is accomplished with the grant command. Example: GRANT create session TO turner or GRANT select, update, insert ON emp TO turner (NOTE, this object privilege syntax does not work when granting privileges to a single column – correct syntax is GRANT privilege (column_name) ON table TO user;). System Privileges o Database Access These privileges control who accesses the database, when he or she can access it, and what he or she can do regarding management of his or her restricted session. Privileges include create session, alter session, and restricted session. o Users These privileges are used to manage users in the Oracle database. Typically, these privileges are reserved for DBAs or security administrators. Privileges include create user, become user, alter user, and drop user. o Tables These privileges govern which users can create and maintain tables. The privileges include create table, create any table, alter any table, backup any table, drop any table, lock any table, comment any table, select any table, insert any table, update any table, and delete any table. The create table or create any table privilege also enables you to deop the table. The create table privileges also bestows the ability to create indexes on the table and to run the analyze command on the table. To be able to truncate a table, you must have the drop any table privilege granted to you. o Indexes The privileges include create any index, alter any index, and drop any index. You should note that no create index system privilege exists. the create table privilege also enables you to alter and drop indexes that you won and that are associated with the table. o Synonyms These privileges include create synonym, create any synonym, drop any synonym, create public synonym, and drop public synonym. The create synonym privilege also enables you to alter and drop synonyms that you own. o Views Privileges include create view, create any view, and drop any view. The create view privilege also enables you to alter and drop views that you own. o Sequences Privileges include create sequence, create any sequence, alter any sequence, drop any sequence, and select any sequence. The create sequence privilege also enables you to drop sequences that you own. o Roles Roles are objects that can be used for simplified privilege management. You create a role, grant privileges to it, and then grant the role to users. Privileges include create role, drop any role, grant any role, and alter any role. o Transactions These privileges are for resolving in-doubt distributed transactions being processed on the Oracle database. Privileges include force transaction and force any transaction. o PL/SQL These privileges enable you to create, run, and manage those different types of blocks. Privileges include create procedure, create any procedure, alter any procedure, drop any procedure, and execute any procedure. The create procedure privilege also enables you to alter and drop PL/SQL blocks that you own. o Triggers A trigger is a PL/SQL block in Oracle that executes when a specified DML activity occurs on the table to which the trigger is associated. Privileges include create trigger, create any trigger, alter any trigger, and drop any trigger. The create trigger privilege also enables you to alter and drop triggers that you own. Object Privileges o select Permits the grantee of this object privilege to access the date in a table, sequence, view, or snapshot. o insert Permits the grantee of this object privilege to insert data into a table or, in some cases, a view. You can also restrict this privilege to specified columns of a table. o update Permits the granted to update data in a table or view. You can also restrict this privilege to specified columns of a table. o delete Permits the granted to delete data in a table or view. You can also restrict this privilege to specified columns of a table. o alter Permits the grantee of this object privilege to alter the definition of a table or sequence only; the alter privileges on all other database objects are considered system privileges. o index Permits the grantee of this object privilege to create an index on a table already defined. o references Permits the grantee of this object privilege to create or alter a table in order to crate a foreign key constraint against data in the reference table. o execute Permits the grantee of this object privilege to run a stored procedure or function. *In order to grant an object privilege, the grantor must either have been granted the privilege with the with grant option privilege or must own the object. Example: GRANT insert ON emp TO turner WITH GRANT OPTION; *A system privilege or a role can be granted with the ADMIN OPTION. A grantee with this option can grant or revoke the system privilege or role to or from any user or other role in the database. However, a user cannot revoke a role from themselves. *Taking away system and object privileges is accomplished with the revoke command. Example: REVOKE create session FROM turner or REVOKE select, update, insert ON emp FROM turner (NOTE, this object privilege syntax does not work when granting privileges to a single column – correct syntax is REVOKE privilege (column_name) ON table FROM user;). *When the CASCADE CONSTRAINTS option is specified, any foreign key constraints currently defined that use the revoked REFERENCES privilege are dropped. *No cascading effects of revoking system privileges from users occur. If you want to take away a system privilege from a user, you have to explicitly revoke that privilege directly from the user as well as drop whatever objects that user has created while having the privilege. *When an object privilege is revoked from a grantor of that privilege, all grantees receiving the privilege from the grantor also lose the privilege. The changes made by those users will remain. *If a privileges has been granted on two individual columns, the privilege cannot be revoked on only one column – the privilege must be revoked entirely and then regranted on the individual column. *If the user has been given the references privilege and has used it to create a foreign key constraint to another table, you must use the cascade constraints clause to revoke the references privilege (otherwise it will fail), as follows: REVOKE REFERENCES ON emp FROM spanky CASCADE CONSTRAINTS; *The insert, update, and references privileges can be granted on columns within the database object. However, if a user has the insert privilege on several columns in the table but not all columns, the privilege administrator must ensure that no columns in the table that do not have the insert privilege granted are not NULL columns. *If a user has the ability to execute a stored procedure owned by another user, and the procedure accesses some tables, the object privileges required to access those tables must be granted to the owner of the procedure, not the user whom execute privileges were granted. What’s more, the privileges must be granted directly to the user, not through a role. *Depending upon what is granted or revoked, a grant or revoke takes effect at different times. All grants/revokes of privileges (system and schema object) to users, roles, or PUBLIC are immediately observed. All grants/revokes of roles to users, other roles, or public are observed only when a current user session issues a SET ROLE statement to reenable the role after the grant/revoke, or when a new user session is created after the grant/revoke. *A role acts in two capacities in the database. First, the role can act as a focal point for grouping the privileges to execute certain tasks. Second, the role can act as a “virtual user” of a database, to which all the objects privileges required to execute a certain job function can be granted. * A role can be created using the create role statement. You must have the create role system privilege to create a role. Example: CREATE ROLE rpt_writer. * A role can be modified using the alter role statement. It can also be altered to require a password by using the alter role identified by statement. Example: ALTER ROLE data_changer IDENTIFIED BY hightly#secure; * You grant and revoke object privileges to a role in the same manner you would a user. Example: GRANT select ON emp TO rpt_writer; * Once a role is created and privileges are granted to it, the role can then be granted to users. Example: GRANT rpt_writer TO turner; * If a role already granted to a user is later granted another privilege, that additional privilege is available to the user immediately. The same statement can be made for privileges revoked from roles already granted to users. * You can use the following keywords in the alter user default role command to define default roles for users: all, all except rolename, and none. Note that users usually cannot issue alter user default role themselves to change their default roles – only a privileged user such as the DBA can do it for them. Example: ALTER USER scott DEFAULT ROLE none; * You can enable a role by using the set role command. Example: set role data_changer identified by highly#secure; * Some special roles are available to the users of a database. * CONNECT Enables the user extensive development ability within his or her own user schema, including the ability to perform create table, create cluster, create session, create view, create sequence, and more. The privileges associated with this roll are platform specific; therefore, the role can contain a different number of privileges, but typically the role never enables the creation of stored procedures. * RESOURCE Enables the user moderate development ability within his or her own schema, such as the ability to execute create table, create cluster, create trigger, and create procedure. The privileges associated with this role are platform specific; therefore, the role can contain a different number of privileges. * DBA Enables the user to administer and use all system privileges. USER_TAB_COMMENTS Stores comment information on tables. USER_COL_COMMENTS Stores comment information for columns. USER_CONSTRAINTS CONTRAINT_TYPE column will list the first character of the first word that best describes each type of constraint. ALL_CONSTRAINTS CONTRAINT_TYPE column will list the first character of the first word that best describes each type of constraint. USER_OBJECTS Can be used to verify the validity status of all user database objects. ALL_OBJECTS Can be used to verify the validity status of all database objects. USER_UPDATABLE_COLUMNS COLUMN_NAME and UPDATABLE will tell you whether the columns in a complex view can be modified. USER_INDEXES Contains the name of the index and it uniqueness. USER_IND_COLUMNS Contains the index name, the table name, and the column name. USER_SYS_PRIVS Shows all privileges associated with this user. SESSION_PRIVS Shows all privileges available in this session. USER_ROLE_PRIVS Identifies the roles granted to you. ROLE_ROLE_PRIVS Identifies the roles granted to other roles in the database. ROLE_TAB_PRIVS Identifies object privileges granted to roles. ROLE_SYS_PRIVS Identifies system privileges granted to roles. SESSION_ROLES Identifies roles available to the current session.

Oracle Explain Plan

The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations Oracle performs to execute the statement. An execution plan defines how Oracle finds or writes the data. The components of execution plans include:

• An ordering of the tables referenced by the statement
• An access method for each table mentioned in the statement
• A join method for tables affected by join operations in the statement

The general syntax of EXPLAIN PLAN is: explain plan for your- sql-statement;

EXPLAIN PLAN output shows how Oracle executes SQL statements. To determine the execution plan Oracle follows to execute a specified SQL statement. This statement inserts a row describing each step of the execution plan into a specified table. EXPLAIN PLAN results alone, however, cannot differentiate between well-tuned statements and those that perform poorly. For example, if EXPLAIN PLAN output shows that a statement uses an index, this does not mean the statement runs efficiently. Sometimes using indexes can be extremely inefficient. It is thus best to use EXPLAIN PLAN to determine an access plan and later prove it is the optimal plan through testing. If you execute an EXPLAIN PLAN, Oracle will analyze the statement and fill a special table with the Execution plan for that statement. Before issuing an EXPLAIN PLAN statement, verify whether PLAN_TABLE exits or create a table to hold its output . Use one of the following approaches:

1)Run the SQL script UTLXPLAN.SQL to create a sample output table called PLAN_TABLE in your schema. The exact name and location of this script depends on your operating system. PLAN_TABLE is the default table into which the EXPLAIN PLAN statement inserts rows describing execution plans.

2)Issue a CREATE TABLE statement to create an output table with any name you choose. When you issue an EXPLAIN PLAN statement you can direct its output to this table.

Any table used to store the output of the EXPLAIN PLAN statement must have the same column names and datatypes as the PLAN_TABLE: CREATE TABLE plan_table (statement_id VARCHAR2(30), timestamp DATE, remarks VARCHAR2(80), operation VARCHAR2(30), options VARCHAR2(30), object_node VARCHAR2(128), object_owner VARCHAR2(30), object_name VARCHAR2(30), object_instance NUMERIC, object_type VARCHAR2(30), optimizer VARCHAR2(255), search_columns NUMERIC, id NUMERIC, parent_id NUMERIC, position NUMERIC, cost NUMERIC, cardinality NUMERIC, bytes NUMERIC, other_tag VARCHAR2(255) other LONG); To issue an EXPLAIN PLAN statement, you must have the privileges necessary to insert rows into an existing output table that you specify to hold the execution plan. To examine the execution plan produced by an EXPLAIN PLAN statement, you must have the privileges necessary to query the output table. EXPLAIN PLAN SET STATEMENT_ID (optional) INTO TABLE_NAME FOR YOUR- SQL-STATEMENT; If you omit the INTO TABLE_NAME clause, Oracle fills a table named PLAN_TABLE by default.

Keywords and Parameters :

SET STATEMENT_ID : Specifies the value of the STATEMENT_ID column for the rows of the execution plan in the output table. You can then use this value to identify these rows among others in the output table. Be sure to specify a STATEMENT_ID value if your output table contains rows from many execution plans. If you omit this clause, the STATEMENT_ID value defaults to null.

INTO : Specifies name of the output table, and optionally its schema and database. This table must exist before you use the EXPLAIN PLAN statement.

FOR statement : Specifies a SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, or CREATE INDEX statement for which the execution plan is generated. Note: If statement includes the parallel_clause, the resulting execution plan will indicate parallel execution. However, EXPLAIN PLAN actually inserts the statement into the plan table, so that the parallel DML statement you submit is no longer the first DML statement in the transaction. This violates the Oracle restriction of one parallel DML statement per transaction, and the statement will be executed serially. To maintain parallel execution of the statements, you must commit or roll back the EXPLAIN PLAN statement, and then submit the parallel DML statement.

The EXPLAIN PLAN statement is a data manipulation language (DML) statement, rather than a data definition language (DDL) statement. Therefore, Oracle does not implicitly commit the changes made by an EXPLAIN PLAN statement. If you want to keep the rows generated by an EXPLAIN PLAN statement in the output table, you must commit the transaction containing the statement. The Plan Table: The plan table is the table that Oracle fills when you issue an execution plan for an SQL statement. You must make sure such a plan table exists. Oracle ships with the script UTLXPLAN.SQL which creates this table, named PLAN_TABLE (which is the default name used by EXPLAIN PLAN.You can also choose any other name for the plan table, as long as you have been granted insert on it and its table structure should be similar to Plan Table.

The PLAN_TABLE used by the EXPLAIN PLAN statement contains the following columns: PLAN_TABLE Columns :

STATEMENT_ID : The value of the optional STATEMENT_ID parameter specified in the EXPLAIN PLAN statement.

TIMESTAMP : The date and time when the EXPLAIN PLAN statement was issued.

REMARKS : Any comment (of up to 80 bytes) you wish to associate with each step of the explained plan. If you need to add or change a remark on any row of the PLAN_TABLE, use the UPDATE statement to modify the rows of the PLAN_TABLE.

OPERATION : The name of the internal operation performed in this step. In the first row generated for a statement, the column contains one of the following values: DELETE STATEMENT ,INSERT STATEMENT ,SELECT STATEMENT ,UPDATE STATEMENT .

OPTIONS : A variation on the operation described in the OPERATION column.

OBJECT_NODE : The name of the database link used to reference the object (a table name or view name). For local queries using parallel execution, this column describes the order in which output from operations is consumed.

OBJECT_OWNER : The name of the user who owns the schema containing the table or index.

OBJECT_NAME : The name of the table or index.

OBJECT_INSTANCE : A number corresponding to the ordinal position of the object as it appears in the original statement. The numbering proceeds from left to right, outer to inner with respect to the original statement text. View expansion will result in unpredictable numbers.

OBJECT_TYPE : A modifier that provides descriptive information about the object; for example, NON-UNIQUE for indexes.

OPTIMIZER : The current mode of the optimizer.

SEARCH_COLUMNS : Not currently used.

ID : A number assigned to each step in the execution plan.

PARENT_ID : The ID of the next execution step that operates on the output of the ID step.

POSITION : The order of processing for steps that all have the same PARENT_ID.

OTHER : Other information that is specific to the execution step that a user may find useful.

OTHER_TAG : Describes the contents of the OTHER column

DISTRIBUTION : Stores the method used to distribute rows from "producer" query servers to "consumer" query servers.

Pstart : The start partition of a range of accessed partitions. It can take one of the following values: n indicates that the start partition has been identified by the SQL compiler and its partition number is given by n. KEY indicates that the start partition will be identified at execution time from partitioning key values. ROW LOCATION indicates that the start partition (same as the stop partition) will be computed at execution time from the location of each record being retrieved. The record location is obtained by a user or from a global index. INVALID indicates that the range of accessed partitions is empty.

Pstop : The stop partition of a range of accessed partitions. It can take one of the following values: n indicates that the stop partition has been identified by the SQL compiler and its partition number is given by n. KEY indicates that the stop partition will be identified at execution time from partitioning key values. ROW LOCATION indicates that the stop partition (same as the start partition) will be computed at execution time from the location of each record being retrieved. The record location is obtained by a user or from a global index. INVALID indicates that the range of accessed partitions is empty.

PID : The step that has computed the pair of values of the Pstart and Pstop columns.

COST : The cost of the operation as estimated by the optimizer's cost-based approach. For statements that use the rule-based approach, this column is null. Cost is not determined for table access operations. The value of this column does not have any particular unit of measurement, it is merely a weighted value used to compare costs of execution plans.

CARDINALITY : The estimate by the cost-based approach of the number of rows accessed by the operation.

BYTES : The estimate by the cost-based approach of the number of bytes accessed by the operation.

Examples

Using the EXPLAIN PLAN Statement:

The following example shows a SQL statement and its corresponding execution plan generated by EXPLAIN PLAN. The sample query retrieves names and related information for employees whose salary is not within any range of the SALGRADE table:

SELECT ename, job, sal, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND NOT EXISTS (SELECT * FROM salgrade WHERE emp.sal BETWEEN lowsal AND highsal);

This EXPLAIN PLAN statement generates an execution plan and places the output in PLAN_TABLE:

EXPLAIN PLAN SET STATEMENT_ID = 'Emp_Sal' FOR SELECT ename, job, sal, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND NOT EXISTS (SELECT * FROM salgrade WHERE emp.sal BETWEEN losal AND hisal);

Selecting PLAN_TABLE Output in Table Format

This SELECT statement:

SELECT operation, options, object_name, id, parent_id, position, cost, cardinality, other_tag, optimizer FROM plan_table WHERE statement_id = 'Emp_Sal' ORDER BY id;

Generates this output:

OPERATION OPTIONS OBJECT_NAME ID PARENT_ID POSITION COST CARDINALITY BYTES OTHER_TAG OPTIMIZER

----------------------------------------------------------------------------------------------- SELECT STATEMENT 0 2 2 1 62

CHOOSE

FILTER 1 0 1

NESTED LOOPS 2 1 1 2 1 62

TABLE ACCESS FULL EMP 3 2 1 1 1 40

ANALYZED TABLE ACCESS FULL DEPT 4 2 2 4 88 ANALYZED TABLE ACCESS FULL SALGRADE 5 1 2 1 1 13 ANALYZED

Oracle certification Questions and Answers

QUESTION NO: 1 You need to create a report to display the ship date and order totals of your ordid table. If the order has not been shipped your report must display not shipped. If the total is not available your report must say not available. In the ordid table the ship date column has a data type of date the total column has a data type of number. Which statement do you use to create this report? A. Select ordid, shipdate “Not shipped”, total “Not available” FROM order; B. Select ordid, NVL (shipdate ‘Not shipped’), NVL (total, “Not available”) FROM order; C. Select ordid, NVL (TO_CHAR (shipdate), ‘Not shipped’), NVL (TO_CHAR (total), ‘Not available’) FROM order; D. Select ordid, TO_CHAR (shipdate, ‘Not shipped’) TO_CHAR (total, ‘Not available’) FROM order; Guaranteed stuff for all IT certification exams: mail at certking@certificationking.net Answer: C Explanation: Answer C shows correct syntax of command NVL Incorrect Answers: A: This command will show ALL data with name substitution of columns shipdate and total. B: Incorrect usage for NVL command, because shipdate and total are needed to be converted into VARCHAR2 type with TO_CHAR function. Both parameters of NVL command have to have the same data type. D: Incorrect syntax. TO_CHAR command is used just to convert data type into VARCHAR2 data type, it have nothing to do with NULL values in columns. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 10-11 Chapter 1: Selecting Data from Oracle QUESTION NO: 2 You want of display the details or all employees whose last names is Smith. But you are not sure in which case last names are stored. Which statement will list all the employees whose last name is Smith? A. Select last name, first name. FROM emp 4 WHERE last name= ‘smith’; B. Select last name, first name. FROM emp WHERE UPPER (last name)= ‘smith’; C. Select last name, first name. FROM emp WHERE last name=UPPER (‘smith’); D. Select last name, first name. FROM emp WHERE LOWER (last name)= ‘smith’; Answer: D Explanation: Select last name, first name. FROM emp WHERE LOWER (last name)= ‘smith’ Answer D shows all records with last name Smith because function LOWER returns the column value passed as x into all lowercase Incorrect Answers: A: This command will show only records with last name ‘smith’. B: Command UPPER converts all data in last_name column into uppercase. C: This command will show only records with last name ‘SMITH’. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 22 Chapter 1: Selecting Data from Oracle QUESTION NO: 3 You need to analyze how long your orders to be shipped from the date that the order is placed. To do this you must create a report that displays the customer number, date order, date shipped and the number of months in whole numbers from the time the order is placed to the time the order is shipped. Which statement produces the required results? A. SELECT custid, orderate, shipdate, ROUND(MONTHS_BETWEEN(shipdate,orderate)) “Time Taken” FROM ord; B. SELECT custid, orderate, shipdate, ROUND(DAYS_BETWEEN(shipdate,orderate))/30. FROM ord; C. SELECT custid, orderate, shipdate, 5 ROUND OFF (shipdate-orderate) “Time Taken” FROM ord; D. SELECT custid, orderate, shipdate, MONTHS_BETWEEN (shipdate,orderate) “Time Taken”. FROM ord; Answer: A Explanation: Answer A shows the number of months (rounded to integer) between the date of order and the date of shipment. Incorrect Answers: B: Function, function DAYS_BETWEEN shows number of days between shipping date and order date. C: Incorrect function ROUND OFF. D: This command will show not rounded to integer value, like 8.6451613. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 30 Chapter 1: Selecting Data from Oracle QUESTION NO: 4 The employee table contains these columns: Last_name Varchar2 (25) First_name Varchar2 (25) Salary Number7, 2 You need to display the names of employees on more than an average salary of all employees. Evaluate the SQL statement. SELECT, LAST_NAME, FIRST_NAME from employee where salary< avg(salary); Which change should you make to achieve the desired results? A. Change the function in the Where clause. B. Move the function to the select clause and add a group clause. C. Use a sub query in the where clause to compare the average salary value. D. Move the function to the select clause and add a group by clause and a having clause. Answer: C Explanation: Answer C shows the correct way to change query, because function AVG can not be used in WHERE clause. 6 Incorrect Answers: A: Usage of function AVG is correct B: This query does not require grouping to extract correct information from the table. D: This query does not require to use GROUP BY and HAVING clauses to extract correct information from table Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 57 Chapter 2: Advanced Data Selection in Oracle QUESTION NO: 5 The employee table contains these columns: FIRST-NAME VARCHER2(25) COMISSION NUMBER(3,2) Evaluate this SQL statement SELECT first-name,commission FROM employee WHERE commission= (SELECTcomission FROM employee WHERE UPPER(first-name)= ‘scott’) Which statement will cause this statement to fail? A. Scott has a null commission resolution. B. Scott has a zero commission resolution. C. There is no employee with the first name Scott. D. The first name values in the data base are in the lower case. Answer: A Explanation: Answer A is correct because if Scott has a null commission expression in WHERE clause will cause error. Incorrect Answers: B: Query will work correctly. C: Query will work even without employee with the first name Scott. D: Name values will be converted to upper case by function UPPER, query will work, but for correct result you need to change UPPER to LOWER function. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 64 Chapter 2: Advanced Data Selection in Oracle 7 QUESTION NO: 6 You create the sales table with this command CREATE TABLE sale. (purchase-no NUMBER(9) CONSTRAINT sale-purchase-no-pk PRIMARY KEY, costumer-id NUMBER(9) CONSTRAINT sale-customer-id-nk NOT NULL); Which index or indexes are created for this table? A. No indexes are created for this table. B. An index is created for purchase_no column. C. An index is created for the customer_no column. D. An index is created for each column. Answer: B Explanation: Answer B is correct because index will be created for PRIMARY KEY column automatically during table creation. Also index is created for UNIQUE constraint, but this table creation statement does not include any UNIQUE constraint. Incorrect Answers: A: A system index will be created for PRIMARY KEY column. C: An index will not be created for customer_no column. D: Indexes will not be created for EACH column. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 110 Chapter 3: Creating Oracle Database Objects QUESTION NO: 7 How would you add a foreign key constraint on the dept_no column in the EMP table. Referring to the ID column in the DEPT table? A. Use the ALTER TABLE command with the ADD clause in the DEPT table. B. Use the ALTER TABLE command with the ADD clause on the EMP table. C. Use the ALTER TABLE command with the MODIFY clause on the DEPT table. D. Use the ALTER TABLE command with the MODIFY clause on the EMP table. E. This task cannot be accomplished. Answer: B 8 Explanation: Answer B is correct because constraint will be created for EMP table using ALTER TABLE command. Incorrect Answers: A: Foreign key constraint will not be created for DEPT table, only for EMP table. DEPT table needs to have PRIMARY KEY for successful creation foreign key for EMP table. C: Foreign key constraint will not be created for DEPT table and MODIFY clause is used for different purposes. D: MODIFY clause of ALTER TABLE command is used for different purposes. E: It’s possible to add foreign constraint after table creation. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 143 Chapter 4: Creating Other Database Objects in Oracle QUESTION NO: 8 Examine the structure of student table: Name Null Type STU ID NOT NULL NUMBER(3) NAME VARCHER2(25) ADDRESS VARCHER2(50) GRADUATION DATE Currently the table is empty. You have decided that null values should not be allowed for the NAME column. Which statement restricts NULL values from being entered into column? A. ALTER TABLE student ADD CONSTRAINT name(NOT NULL); B. ALTER TABLE student ADD CONSTRAINT NOT NULL (name); C. ALTER TABLE student MODIFY CONSTRAINT name(NOT NULL); D. ALTER TABLE student MODIFY(name varcher2(25) NOT NULL); Answer: D Explanation: Answer D is correct because this query add NOT NULL constraint to Student table Incorrect Answers: A: Incorrect ADD CONSTRAINT clause in ALTER TABLE command. B: Incorrect ADD CONSTRAINT clause in ALTER TABLE command. C: Incorrect syntax using MODIFY clause of ALTER TABLE command. There is no MODIFY CONSTRAINT clause in ALTER TABLE command. 9 Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 143 Chapter 4: Creating Other Database Objects in Oracle QUESTION NO: 9 You have decided to permanently remove all the data from the STUDENT table and you need the table structure in the future. Which single command performs this? A. DROP TABLE student; B. TRUNCATE TABLE student; C. DELETE* FROM student; D. TRUNCATE TABLE student KEEP STRUCTURE; E. DELETE* FROM student KEEP STRUCTURE. Answer: B Explanation: Answer B is correct because after truncating table you delete all data and keep table and its structure for future use. Also command TRUNCATE reset highwatermark level to zero for table. Incorrect Answers: A: This DDL operation will drop table and its structure without possibility to rollback this operation. C: This operation can be used to perform question task, but it works slowly and usually used when you need to delete NOT ALL table rows. It does not reset high water mark for table. D: Incorrect clause KEEP STRUCTURE inside TRUNCATE TABLE command. E: Incorrect clause KEEP STRUCTURE inside DELETE command. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 151 Chapter 4: Creating Other Database Objects in Oracle QUESTION NO: 10 Examine this block of code: SET OUTPUT ON Declare X NUMBER; V_SAL NUMBER; V_found VARCHAR2(10):=’TRUE’; Begin X:=1; V_sal := 1000; 10 Declare V_found VARCHAR2(10); Y NUMBER Begin IF (V_Sal>500) THEN V_found := ’YES’; END IF; DBMS_OUTPUT.PUT_LINE(‘Value of V_found is ‘|| V_Sal); DBMS_OUTPUT.PUT_LINE(‘Value of V_Sal is ‘|| TO_CHAR (V_Sal)); Y:=20; END; DBMS_OUTPUT.PUT_LINE(‘Value of V_found is’ || V_found); DBMS_OUTPUT.PUT_LINE(‘Value of Y is’ || TO_CHAR(Y)); END; SET server OUTPUT if What is the result of executing this block of code? A. PLS-00201: identifier ‘Y’ must be declared. B. Value of V_found is YES Value of V_sal is 1000 Value of V_found is TRUE C. Value of V_found is YES Value of V_found is 1000 Value of V_found is TRUE Value of Y is 20 D. PLS-00201: identifier ‘V_sal’ must be declared PLS-00201: identifier ‘Y’ must be declared E. Value of V_found is YES Value of V_sal is 1000 Value of V_found is TRUE Value of Y is 20 Answer: A Explanation: Answer A is correct because PL/SQL variable ‘Y’ is not declared in DECLARE section of PL/SQL block, but only inside BEGIN … END block and executing of this block will return error. Incorrect Answers: B: This code does work because of V_found and V_sal are identified. C: This code does work because of V_found and V_sal are identified. D: Identifier V_sal is declared, so code will fail because of identifier ‘Y’, not V_sal, must be declared. E: This code does work because of all variables are declared and populated with values. 11 Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 209 Chapter 5: Introducing PL/SQL QUESTION NO: 11 You need to store currency data and you know that data will always have two digits to the right of the decimal points. However the number of digits to the left of the decimal place will vary greatly. Which data type would be most appropriate to store the data? A. NUMBER B. NUMBER(T) C. LANG D. LANGRA Answer: A Explanation: Answer A is correct because by default NUMBER is NUMBER(L, P) type, which is always stored as variable-length data, where 1 byte is used to store the exponent, 1 byte is used to store for every two significant digits of the number’s mantissa, and 1 byte is used for negative numbers if the number of significant digits is less than 38 bytes. Incorrect Answers: B: NUMBER(T) type will not allow to keep more than T numbers of digits to the left of the decimal place, but question says that it will vary greatly. C: There is no type LANG in Oracle 8i. D: Type LANGRA does not exists too. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 390 Chapter 8: Managing Database objects I QUESTION NO: 12 Examine the structure of STUDENT table. NAME NULL TYPE STUDENT ID NOT NULL NUMBER(3) NAME NOT NULL VARCHAR2(25) PHONE NOT NULL VARCHAR2(9) ADDRESS VARCHAR2(50) GRADUATION DATE 12 There are hundred records in the student table. You need to modify the Phone column to hold only numeric value. Which statement will modify the data type of the Phone column? A. ALTER TABLE student MODIFY phone NUMBER(9) B. ALTER STUDENT table MODIFY COLUMN phone NUMBER(9); C. You can not modify a VARCHAR2 data type to a NUMBER data type for a D. Column. E. You cannot modify the data type of a column if there is data in the column. Answer: E Explanation: Answer E is correct because Phone column in STUDENT table has NOT NULL constraint, which does not allows to modify data type of a column if there is data in the column. Incorrect Answers: A: The statement will fail because it is incorrect way to change data type for column with NOT NULL constraint on it. B: There is a wrong syntax ‘ALTER STUDENT table’ and ‘MODIFY COLUMN’ in the statement. C: It is possible to modify VARCHAR2 data type to NUMBER data type (with some restrictions). D: Does not exists in question Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 141-142 Chapter 4: Creating Other Database Objects in Oracle QUESTION NO: 13 You need to update employee salaries if the salary of an employee is less than 1000. The salary needs to be incremented by 10%. Use SQL*Plus substitution variable to accept the employee number. Which PL/SQL block successfully updates the salaries? A. Declare V_sal emp.sal % TYPE; Begin SELECT Sal INTO V_sal FROM emp WHERE empno = and P_empno; IF (V_Sal<1000) THEN UPDATE emp INTO Sal := Sal*1.1 WHERE empno = and p_empno; 13 END IF; END; B. Declare V_sal emp.sal % TYPE; Begin SELECT Sal INTO V_sal FROM emp WHERE empno = and P_empno; IF (V_Sal<1000) THEN SAL := SAL * 1.1; END IF; END; C. Declare V_sal emp.sal % TYPE; Begin SELECT Sal INTO V_sal FROM emp WHERE empno = and P_empno; IF (V_Sal<1000) THEN UPDATE emp Sal := Sal*1.1 WHERE empno = and p_empno; END IF; END; D. Declare V_sal emp.sal % TYPE; Begin SELECT Sal INTO V_sal FROM emp WHERE empno = and P_empno; IF (V_Sal<1000) THEN UPDATE emp Set Sal := Sal*1.1 WHERE empno = and p_empno; END IF; END; Answer: D Explanation: Answer D is correct because it’s uses cursor and IF-THEN structure correctly to increase salary for all employees with current salary less than 1000. 14 Incorrect Answers: A: UPDATE INTO is wrong construction for UPDATE command B: There is no SAL variable defined in PL/SQL block, so SAL:=SAL*1.1 will fail and it’s wrong way to change value of column Sal in table EMP. C: Sal:=Sal*1.1 can not be inside UPDATE … WHERE command. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 215-217 Chapter 8: Introducing PL/SQL QUESTION NO: 14 The employee table contains these columns. LAST_NAME VARCHAR2(25) FIRST_NAME VARCHAR2(25) DEPT_ID NUMBER(9) You need to display the names of the employees that are not assigned to the department. Evaluate this SQL statement. SELECT last_name, first_name FROM employee WHERE dept_id is NULL Which change should you make to achieve the desired result? A. Create an outer join. B. Change the column in the where condition. C. Change the operator in the where condition D. Add a second condition to the where condition Answer: C Explanation: Answer C is correct because the NULL operator need to be changed in the WHERE condition to display correct result. Incorrect Answers: A: One table is used for query, so there is no reason to use outer join operation. B: The column in the WHERE condition is correct and don’t need to be changed. D: Query does not require additional condition to extract correct data. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 10-11 Chapter 1: Selecting data from Oracle QUESTION NO: 15 Which statement about SQL is true? 15 A. Null values are displayed last in the ascending sequences. B. Data values are displayed in descending order by default. C. You cannot specify a column alias in an ORDER BY clause. D. You cannot sort query results by a column that is not included in the SELECT list. E. The results are sorted by the first column in the SELECT list, if the ORDER BY clause is not provided. Answer: A Explanation: Answer A is correct because of null values are displayed last in the ascending sequences. Incorrect Answers: B: Data values are displayed in ASCENDING order by default. C: It is possible to specify a column alias in an ORDER BY clause (but not in a GROUP BY clause). D: You can sort query results by a column that is not included in the SELECT list. E: The results will not be sorted at all, if the ORDER BY clause was not used in query. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 18-19 Chapter 8: Selecting Data from Oracle QUESTION NO: 16 Written a PL/SQL loop, you need to test if the current FETCH was successful. Which SQL cursor attribute would you use to accomplish this task? A. SQL % ISOPEN B. SQL % ROWCOUNT C. SQL % FOUND D. This task cannot be accomplished with a SQL cursor attribute. E. A SQL cursor attribute cannot be used within a PL/SQL loop. Answer: C Explanation: Answer C is correct because of SQL%FOUND attribute returns TRUE if current FETCH have been successful. Incorrect Answers: A: SQL%ISOPEN attribute returns TRUE if cursor is OPEN and ready for use. B: SQL%ROWCOUNT returns the number of rows that were processed by the statement. D: This task can be successfully accomplished with a SQL%FOUND cursor attribute. 16 E: PL/SQL loop can contain a SQL cursor attribute. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 222-224 Chapter 5: Introducing PL/SQL QUESTION NO: 17 The structure of the DEPT table is as follows: NAME NULL TYPE Deptno Not Null NUMBER(2) Dname VARCHAR2(14) Loc VARCHAR2(13) Examine the code Declare Type dept_record_type is record (dno NUMBER, name VARCHAR2(20)); dept_rec dept_record; Begin Select deptno, dname INTO dept_rec FROM dept WHERE deptno=10; END Which statement displays the name of selected department? A. DBMS_OUTPUT.PUT_LINE (name); B. DBMS_OUTPUT.PUT_LINE (dname); C. DBMS_OUTPUT.PUT_LINE (dept_rec.name); D. DBMS_OUTPUT.PUT_LINE (dept_rec.dname); E. DBMS_OUTPUT.PUT_LINE (dept_rec (name)); Answer: C Explanation: Answer C is correct because it shows field NAME for record DEPT_REC of DEPT_RECORD type. Incorrect Answers: A: Statement does not specify that NAME is field of DEPT_REC record. B: DNAME is name of column in DEPT table and cannot be used this way to show data. D: There is not field DNAME in record DEPT_REC. E: This statement just has wrong syntax of function PUT_LINE in DBMS_OUTPUT package to represent data. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 211-213 17 Chapter 5: Introducing PL/SQL QUESTION NO: 18 Which privilege concerns with system level security? A. Drop any table. B. DELETE C. ALTER D. INDEX E. UPDATE Answer: A Explanation: Answer A is correct because of only DROP ANY TABLE privilege from all choices belongs to SYSTEM level security, all others – not. Incorrect Answers: B: DELETE is object privilege. Permits the grantee of this object privilege to delete data from a table or view. C: ALTER is object privilege. Permits the grantee of this object privilege to alter the definition of a table or sequence only. The ALTER privileges on all other database objects are considered system privileges. D: INDEX is object privilege. Permits the grantee of this object privilege to create an index on a table already defined. E: UPDATE is object privilege. Permits the grantee of this object privilege to update data into a table or view. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 485 Chapter 10: Managing Database Use QUESTION NO: 19 Evaluate the SQL statement. CREATE ROLE manager; CREATE ROLE clerk; CREATE ROLE inventory; CREATE USER scott IDENTIFIED BY tiger; GRANT inventory TO clerk; GRANT clerk TO manager; GRANT inventory TO scott; / 18 How many roles will user scott have access to? A. 0 B. 1 C. 2 D. 3 Answer: B Explanation: Answer B is correct because of only INVENTORY role will be granted to user SCOTT. Incorrect Answers: A: One role (INVENTORY) is granted to user SCOTT, not zero. C: This choice would be correct if role CLERK would be granted to SCOTT because this role includes INVENTORY role. D: This choice would be correct if role MANAGER would be granted to SCOTT because this role includes INVENTORY and CLERK roles. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 490-494 Chapter 10: Managing Database Use QUESTION NO: 20 Scott forgot his password while on location. Which command must be executed to set a password for scott? A. Scott must execute the command. ALTER USER scott PASSOWRD BY lion B. The DBA must execute the command. ALTER USER scott IDENTIFIED BY lion C. Scott must execute the command ALTER USER scott IDENTIFIED BY lion D. The scott must execute the command CHANGE password to lion WHERE “user=scott”; E. The DBA must execute the command CHANGE password to lion WHERE “user=scott”; Answer: B Explanation: Answer B is correct because of only DBA (not user himself) can change password for account if user forgot his password. Incorrect Answers: A: Clause ‘PASSWORD BY’ is wrong for ‘ALTER USER’ command. 19 C: User needs to be connected to change his password, but he cannot connect if he forgot password. D: Command CHANGE is incorrect to change password and user cannot connect himself to change password because he forgot it. E: Command CHANGE is incorrect to change password. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 182 Chapter 4: Creating Other Database Objects in Oracle QUESTION NO: 21 You are updating the employee table. Jane has been granted the same privileges as you on the employee table. You ask Jane to logon to the database to check your world before you issue the commit command. What can Jane do to the employee table? A. Jane can access the table and verify your changes. B. Jane cannot access the table. C. Jane can access the table but she cannot see your changes, she can make the changes for you. D. Jane can access the table but she cannot see your changes and cannot make the changes to the roles that you are changing. Answer: D Explanation: Answer D is correct because before you committed changes in the employee table nobody can see changed data. Incorrect Answers: A: Jane can access table but she cannot verify changes because she does not see them before you commit data changes. B: Jane can access table because she has been granted the same privileges as you on the employee table. C: Jane cannot commit herself changes you did to the data. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 484-485 Chapter 10: Managing Database Use QUESTION NO: 22 Examine the structure of STUDENT table. Name Null? Type. 20 STUD-ID NOT NULL NUMBER(3) NAME NOT NULL VARCHER2(25) ADDRESS VARCHER2(50) GRADUATION DATE. Which statement inserts a new row into the STUDENT table? A. INSERT INTO student. VALUES(101, ‘Smith’); B. INSERT INTO student. VALUES(101, ‘100 Main Street’, ‘17-JUN-99’, ‘Smith’); C. INSERT INTO test. VALUES(101, ‘Smith’, ‘100 Main Street’, ‘17-JUN-99’); D. INSERT INTO student.(stud-id,address,gradulation) VALUES(101, ‘Smith’, ‘100 Main Street’, ‘17-JUN-99’); E. INSERT INTO student.(stud-id,address,name,gradulation) VALUES(101, ‘100 Main Street’,‘Smith’, ‘17-JUN-99’); Answer: E Explanation: Answer E is correct because you need to show ALL columns and in correct order for values which you try to insert into table STUDENT. Incorrect Answers: A: You need to show ALL columns for values, which you try to insert into table STUDENT. B: You need to show columns in correct order for values, which you try to insert into table STUDENT. In this statement Name is located in wrong place according table definition. C: This statement contains wrong table name TEST, not STUDENT. D: This statement shows list of only 3 columns when there are 4 values need to be inserted into table row. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 120-122 Chapter 4: Creating Oracle Database Objects QUESTION NO: 23 Examine the structure of the STUDENT table. NAME NULL TYPE STUDENT_ID NOT NULL NUMBER(3) NAME NOT NULL VARCHAR2(25) 21 ADDRESS VARCHAR2(50) GRADUATION DATE Graduation column is a foreign key column to the graduate table. Examine the data in the GRADE DATE table. Graduation 20-jan-1999 12-may-1999 19-jan-2000 25-may-2000 13-jan-2001 29-may-2001 Which update statement produces the following error: ORA-02291 integrity constraint(sys_c23) violated parent key not found? A. UPDATE student SET stud-id=999, graduation= ’29-MAY-2001’ WHERE stud-id=101;’ B. UPDATE student SET name= ‘Smith’, graduation= ’29-MAY-2001’ WHERE stud-id=101; C. UPDATE student SET name= ‘Smith’, graduation= ‘15-AUG-2000’ WHERE stud-id=101 D. UPDATE student SET stud-id=NULL, address= ‘100 Main Street’ WHERE graduation= ‘20-JAN-1999’ Answer: C Explanation: Answer C is correct because there is no ’15-AUG-2000’ value in the graduate table. Foreign constraint for this statement will be violated because parent key not found. Incorrect Answers: A: There is ’29-MAY-2001’ in the graduate table and foreign constraint will not be violated. B: There is ’29-MAY-2001’ in the graduate table and foreign constraint will not be violated. D: There is ’20-JUN-1999’ in the graduate table and foreign constraint will not be violated. 22 Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 428-433 Chapter 9: Managing Database Objects II QUESTION NO: 24 In SQL Plus You issued this command: Delete from dept where dept_id=901 You received an integrated constraint error because the child record was found. What could you do to make the statement execute? A. Delete the child record first. B. You cannot make the command execute. C. Add a fourth keyword to the command. D. Add the constraints cascade option to the command. Answer: A Explanation: Answer A is correct because you need first delete the child record in dependable table for avoid foreign constraint violation. Incorrect Answers: B: You can execute the command if you don’t have child records in other tables for record you need to delete in the parent table DEPT. C: Adding an additional keyword will not help because foreign constraint will be violated. D: CASCADE CONSTRAINTS option works only for DROP TABLE command, not for DELETE. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 398-399 Chapter 8: Managing Database Objects I QUESTION NO: 25 The view EMP-VIEW is created based on the EMP table as follows. CREATE OF REPLACE VIEW emp-view AS SELECT deptno,SUM(sal)TOT_SAL,COUNT(*)NOT-EMP FROM emp GROUP BY deptno; What happens when the command is used? 23 UPDATE emp-view SET tot-sal=20000 WHERE deptno=10; A. The base table cannot be updated through this view. B. The TOT_SAL column in the EMP table is updated to 20,000 for department 10. C. The TOT_SAL column in the EMP view is updated to 20,000 for department10. D. The SAL column in the EMP table is updated to 20,000 for employees in department 10. Answer: A Explanation: Answer A is correct because the user may not INSERT, DELETE, or UPDATE data on the table underlying the sample view if the SELECT statement creating the view contains GROUP BY, or a single-row operation. Incorrect Answers: B: TOT_SAL column in the EMP table will not be updated. C: TOT_SAL column in the EMP table will not be updated for any rows. D: UPDATE command does not contain SAL column for update. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 164 Chapter 4: Creating Other Database Objects in Oracle QUESTION NO: 26 You have a view card ANN_SAL that is based on the employee table. The structure of the ANN_SAL view is: NAME NULL TYPE EMPNO NOT NULL NUMBER(4) YEARLY_SAL NUMBER(9,2) MONTHLY_SAL NUMBER(9,2) Which statement retrieves the data from the ANN_SAL view? A. SELECT * FROM ANN_SAL B. SELECT * FROM EMPLOYEE C. SELECT * FROM VIEW ANN_SAL D. SELECT * FROM VIEW ANN_SAL IS DON EMPLOYEE Answer: A 24 Explanation: Answer A is correct because correct syntax for SELECT command for view is SELECT * FROM Incorrect Answers: B: This statement will show data from table EMPLOYEE, not view ANN_SAL. C: It’s an incorrect statement because of usage word ‘VIEW’. D: It’s an incorrect statement because of usage words ‘VIEW’ and ‘IS DON EMPLOYEE’. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 160-169 Chapter 4: Creating Other Database Objects in Oracle QUESTION NO: 27 Evaluate this IF statement. IF v_value>100 THEN v_new-value:=2*v-value; ELSIF v-value>200 THEN v-new-value:=3*v-value; ELSIF v-value>300 THEN v-new-value:=4*v-value; ELSE v-new-value:=5*v-value; END IF What would be assigned to v_new_value if v_value=250? A. 250 B. 500 C. 750 D. 1000 Answer: B Explanation: Answer B is correct because first IF condition v_value > 100 will be TRUE if v_value have been assigned with new value equal 250. Result of 2*v_value is 500. Incorrect Answers: A: All IF conditions multiple 250 on 2, 3, 4 or 5, so result cannot be 250. C: First IF condition will work not second one, so result will be 500, not 750. 25 D: First IF condition will work not second or third one, so result will be 500, not 750 or 1000. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 215-217 Chapter 5: Introducing PL/SQL QUESTION NO: 28 The PLAYER table contains these columns id number(9) name varchar(2) manager_id number(9) In this instance, managers are players with you need to display a list of players. Evaluate these TWO SQL statements: SELECT p.name,m.name FROM player p,player m WHERE m.id= m.manager_id; SELECT p.name,m.name FROM player p,player m WHERE m.manager_id=p.id; How would the results differ? A. Statement1 will not execute, statement2 will. B. Statement1 will execute, statement2 will not. C. Statement1 is self join, statement2 is not. D. The results will be same but the display will be different. Answer: D Explanation: Answer D is correct because the results of these queries will be same, just will look different. In first statement driving column is ID, in second – MANAGER_ID. Incorrect Answers: A: Both statements will be executed successfully. B: Both statements will be executed successfully. C: Both statements are self join, not only Statement1. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 55-56 Chapter 4: Advanced Data Selection in Oracle 26 QUESTION NO: 29 How would you declare a PL/SQL table of records to hold the rows selected from the EMP table? A. DECLARE emp-table is TABLE of emp%ROWTYPE. B. BEGIN TYPE emp-table is TABLE of emp%ROWTYPE emp-table emp-table-type; C. DECLARE TYPE emp-table is TABLE of emp%ROWTYPE INDEX BY WHOLE NUMBER: emp-table emp-table-type; D. DECLARE TYPE emp-table is TABLE of emp%ROWTYPE INDEX BY BINARY INTEGRATDE. emp-table emp-table-type; Answer: D Explanation: Answer D is correct because INDEX BY BINARY INTEGRATED clause need to be set for TABLE type. Incorrect Answers: A: INDEX BY clause is not used in definition statement of PL/SQL block. B: INDEX BY clause is not used in definition statement of PL/SQL block. C: INDEX BY clause uses WHOLE NUMBER option, which is incorrect for TABLE type definition. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 392 Chapter 8: Managing Database Objects I QUESTION NO: 30 You want to create a cursor that can be used several times in a block. Selecting a different active set each time that it is opened. Which type of cursor do you create? A. A cursor for loop. B. A multiple selection cursor. C. A cursor for each active set. 27 D. A cursor that uses parameters. Answer: D Explanation: Answer D is correct because a cursor with parameters can be used several times in a block, selecting active set each time that it was opened depending on parameters’ values. Incorrect Answers: A: A cursor for loop used for different purpose. B: A multiple selection cursor does not exist. C: A cursor for each active set does not allow to use the same cursor several times in a block. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 231-233 Chapter 5: Introducing PL/SQL QUESTION NO: 31 Which statement is true when writing a cursor for loop? A. You must explicitly fetch the rows within a cursor for loop. B. You must explicitly open the cursor prior to the cursor for loop. C. You must explicitly close the cursor prior to the end of program. D. You do not explicitly open, fetch or close a cursor within a cursor for loop. E. You must explicitly declare the record variable that holds the row returned from the cursor. Answer: D Explanation: Answer B is correct because when using a cursor for loop you don’t need explicitly open, fetch or close a cursor (by definition of a cursor for loop). Incorrect Answers: A: You don’t need explicitly fetch the cursor within a cursor for loop. B: You don’t need explicitly open the cursor within a cursor for loop. C: You don’t need explicitly close the cursor within a cursor for loop. E: You don’t need to declare the record variable to hold the row from the cursor if you use a cursor for loop. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 233-234 Chapter 5: Introducing PL/SQL 28 QUESTION NO: 32 The structure of the DEPT table as: Name Null? Type DEPT NO Not NULL Number(25) DNAME VARCHER2(14) LOC VARCHER2(13) Examine the code: DECLARE Dept_rec dept%ROWTYPE: BEGIN SELECT* INTO dept_rec FROM dept. WHERE deptno=10; END; Which PL/SQL statement displays the location of selected department? A. DBMS_OUTPUT.PUT-LINE(dept_rec); B. DBMS_OUTPUT.PUT-LINE(dept_rec.loc); C. DBMS_OUTPUT.PUT-LINE(dept_rec(1).loc); D. You can’t display a single field in the record because they are not specially identified in declarative section. Answer: B Explanation: Answer B is correct because PUT_LINE function from DBMS_OUTPUT package will show LOC field from DEPT_REC record. Incorrect Answers: A: You cannot show ALL record fields using DEPT_REC. C: Syntax ‘dept_rec(1).loc’ is wrong to display the location of selected department. D: You can display a single field of record. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 211-213 Chapter 5: Introducing PL/SQL 29 QUESTION NO: 33 Which statement about implicit cursors is true? A. Implicit cursors are declared implicitly only for DML statements. B. Implicit cursors are declared implicitly for all the DML and SELECT statements. C. Programmers need to close all the implicit cursors before the end of the PL/SQL program. D. Programmers can declare implicit cursors by using the cursor type in the declaration section. Answer: B Explanation: Answer B is correct because implicit cursors are declared implicitly for all DML and SELECT statements. Incorrect Answers: A: Implicit cursors are declared implicitly not only for all DML, but for SELECT statements too. C: Programmers don’t need to close the implicit cursor, because it will be closed automatically at the end of the PL/SQL block. D: Programmers cannot declare implicit cursors, only explicit cursors. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 222-224 Chapter 5: Introducing PL/SQL QUESTION NO: 34 Evaluate this PL/SQL block: DECLARE v-result NUMBER(2); BEGIN DELETE FROM employee WHERE dep_id IN (10,20,30); v-result:= SQL%ROWCOUNT; COMMIT; END; What will be the value of v_result if no rows are deleted? A. 0 B. 1 30 C. True D. Null Answer: A Explanation: Answer A is correct because if no rows are deleted SQL%ROWCOUNT attribute of cursor will return 0 – number of deleted rows. Incorrect Answers: B: No rows are deleted so SQL%ROWCOUNT will return 0, not 1. C: SQL%ROWCOUNT attribute of cursor returns always a numeric value, not BOOLEAN. D: SQL%ROWCOUNT attribute of cursor returns always a numeric value, which cannot be a NULL. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 224 Chapter 5: Introducing PL/SQL QUESTION NO: 35 Which two conditions in a PL/SQL block cause an exception error to occur? (Choose two) A. Select statement does not return a row. B. Select statement returns more than one row. C. Select statement contains a group by clause. D. Select statement does not have where clause. E. The data type in the select list are inconsistent with the data types in the into clause. Answer: A, B Explanation: Answers A and B is correct because there are two common exceptions can cause an error : NO_DATA_FOUND, if no rows were selected or changed by the SQL operation or TOO_MANY_ROWS, if more than one row was obtained by a single-row subquery, or in another SQL statement operation where Oracle was expecting one row. Incorrect Answers: C: It will not be an exception error if SELECT statement contains a GROUP BY clause. D: It will not be an exception error if SELECT statement contains a WHERE clause. E: It will ROWTYPE_MISMATCH an exception error if the datatypes of the record to which data from the cursor is assigned are INCOMPATIBLE, but not INCONSISTENT as answer E says. 31 Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 239 Chapter 5: Introducing PL/SQL QUESTION NO: 36 You need to create a PL/SQL program to insert records into employee table. Which block of code successfully uses the insert command? A. DECLARE v_hiredate DATE:=SYSDATE: BEGIN INSERT INTO emp(empnp, ename, hiredate, deptno) VALUES(empno_sequence.nextval, ‘and name’,v_hirerdate and deptno) B. DECLARE v-hiredate DATE:=SYSDATE: BEGIN INSERT INTO emp(empnp,ename,hiredate,deptno) C. DECLARE v-hiredate DATE:=SYSDATE: BEGIN INSERT INTO emp(empnp,ename,hiredate) VALUES(empno_sequence.nextval, name, v_hirerdate) END: D. DECLARE v-hiredate DATE:=SYSDATE: BEGIN INSERT INTO emp(empnp,ename,heridate,deptno) VALUES(empno_sequence.nextval, ‘and name’,v_herdate and deptno) Job=Clerk END: Answer: C Explanation: Answer C is correct because expression EMPNO_SEQUENCE.NEXTVAL will calculate next value for EMPNP column automatically after each insert. Incorrect Answers: A: Syntax ‘v_iredate and deptno’ is incorrect in INSERT VALUES statement. B: Statement does not display VALUES which need to be inserted into table. 32 D: Using ‘Job=Clerk’ is incorrect syntax in the INSERT statement and in the PL/SQL block. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 153-160 Chapter 4: Creating Other Database Objects in Oracle QUESTION NO: 37 Evaluate this PL/SQL block. BEGIN FOR i IN 1..10 LOOP IF I=4 OR I=6 THEN null; ELSE INSERT INTO test(result) VALUES (I) ; END IF; COMMIT; END LOOP; ROLL BACK; END. How many values will be inserted into the TEST table? A. 0 B. 4 C. 6 D. 8 E. 10 Answer: D Explanation: Answer D is correct because loop will be executed 10 times, but 2 times IF-THEN condition will not allow to insert 2 values into TEST table, so result is 8. Incorrect Answers: A: Loop will be executed exactly 10 times, because I will change value from 1 to 10 by LOOP-FOR definition. B: Loop will be executed exactly 10 times, not 4. C: Loop will be executed exactly 10 times, not 6. E: This answer would be correct if the condition IF-THEN have been absent inside this PL/SQL block. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 219-220 33 Chapter 5: Introducing PL/SQL QUESTION NO: 38 You issue this command: CREATE public synonym EMP for ed.employee; Which task has been accomplished? A. The object can now be accessed by all the users. B. All users were given object privileges to the table. C. The need to qualify the object name with its schema is eliminated only for you. D. The need to qualify the object name with its schema is eliminated for all users. Answer: D Explanation: Answer D is correct because the public synonym will be created for table EMPLOYEE of ED owner. After that other users will not need to use object owner prefix to access data inside this table. Incorrect Answers: A: Creation public synonym will not open access for ALL users, but will make more easy access for user, which have access to EMPLOYEE table of user ED. B: Creation public synonym for table does not provide objects privileges for ALL users to the table. C: Because of creation PUBLIC synonym for ED.EMPLOYEE need to qualify the object name with its owner name is eliminated for ALL users who have already access to this table, not only for you. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 183-185 Chapter 4: Creating Other Database Objects in Oracle QUESTION NO: 39 Which statement about multiple sub-queries is True? A. A pair wise comparison produces a cross product. B. A non-pair wise comparison produces a cross product. C. In a pair wise subquery, the values returned from the subquery are compared individually to the values in the outer query. D. In a non-pair wise subquery, the values returned from the subquery are compared as a group to the values in the outer query. 34 Answer: B Explanation: Answer B is correct because a non-pair wise comparison really produces a cross product. Incorrect Answers: A: A pair comparison does not produces a cross product because of a non-pair wise comparison does. C: In a pair wise subquery, the values returned from the subquery are compared as a GROUP, not INDIVIDUALLY to the values in the outer query. D: In a non-pair wise subquery, the values returned from the subquery are compared INDIVIDUALLY to the values in the outer query, not as a GROUP. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 64-72 Chapter 2: Advanced Data Selection QUESTION NO: 40 You attempt to query to the database with this command: SELECT dept_no,AVG(MONTHS_BETWEEN(SYSDATE,hire-data)) FROM employee WHERE AVG(MONTHS_BETWEEN(SYSDATE,hire_date))>60 GROUP BY by dept_no ORDER BY AVG(MONTHS_BETWEEN(SYSDATE,hire_date)); Why does this statement cause an error? A. A select clause cannot contain a group function. B. A where clause cannot be used to restrict groups. C. An order by clause cannot contain a group function. D. A group function cannot contain a single row function. Answer: B Explanation: Answer B is correct because function AVG cannot be used in a WHERE clause of SELECT statement. Incorrect Answers: A: SELECT clause can contain a group function. C: ORDER BY clause can contain a group function. D: A GROUP function can contain a single row function. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 57 35 Chapter 5: Advanced Data Selection in Oracle QUESTION NO: 41 The path table contains these columns: ID NUMBER(7) PK COST NUMBER(7,2) PRODUCT_ID NUMBER(7) Evaluate these SQL statements: SELECT ROUND(max(cost),2), ROUND(min(cost),2), round(sum(cost),2), ROUND(AVG(cost),2) FROM part; SELECT product_id, ROUND(max(cost),2), ROUND(min(cost),2), ROUND(sum(cost),2), ROUND(AVG(cost),2) FROM part GROUP BY product_id; How will the results differ? A. The results will be same but the display will differ. B. The statement1 will only display one row of results, statement2 can display more than one. C. Statement1 will display a result for each part, statement2 will display a result for each product. D. One of the statements will generate an error. Answer: B Explanation: Answer B is correct because Statement2 uses GROUP BY function to calculate aggregated functions for each PRODUCT_ID while Statement1 returns one row result. Incorrect Answers: A: The results will not be the same. C: Statement1 will not display a result for each part, but only one row. D: Both statements will be successfully completed without errors. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 58-64 Chapter 5: Advanced Data Selection in Oracle 36 QUESTION NO: 42 In which section of a PL/SQL block is a user defined exception waste? A. Heading B. Executable C. Declarative D. Exception handling Answer: B Explanation: Answer B is correct because in the executable section of PL/SQL block a user-defined exception waste. Incorrect Answers: A: Heading section of PL/SQL block is used for function, procedure, package naming, not for exceptions. C: Declarative section of PL/SQL block, which is optional, identifies all variables constructs that will be used in the code block. D: Exception handling section of PL/SQL block defines all errors that may occur in the block and specifies how they should be handled. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 209 Chapter 5: Introducing PL/SQL QUESTION NO: 43 Examine the code: SET SERVER OUTPUT ON DECLARE v_char_val varchar2(100); BEGIN v_char_val:= ‘Hello World’, DBMS_OUTPUT.PUT_LINE(v_char_val); END SET SERVER OUTPUT OFF This code is stored in a script file name “myproc,sql”. Which statement executes the code in the script file? A. Myproc.sql B. RUN myproc,sql 37 C. START myproc.sql D. EXECUTE myproc.sql E. BEGIN myproc.sql END; Answer: C Explanation: Answer C is correct because command START is used to execute the code in the script file. Incorrect Answers: A: This statement will not start execution of the script. C: Command RUN is not used to start the stored script. D: Command EXECUTE is used to execute function or procedure, but not a stored script E: Structure BEGIN … END represent executable section of PL/SQL block, it will not start to execute a stored script. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 16 Chapter 1: Selecting data from Oracle QUESTION NO: 44 Examine this block F code Set server output ON Declare X NUMBER; V_SAL NUMBER; V_found VARCHAR2(10) := ‘TRUE’ Begin X:=1; V_SAL :=1000; Declare V_found VARCHAR2(10); Y NUMBER; Begin IF (V_sal>500) THEN V_found := ‘YES’; END IF; DBMS_OUTPUT.PUT_LINE(‘value f V_found is’ || V_found); DBMS_OUTPUT.PUT_LINE (‘value f V_found is’ || V_found); Y:20; END DBMS_OUTPUT.PUT_LINE (‘value f V_found is’ || V_found); DBMS_OUTPUT.PUT_LINE (‘value f Y is’ || TO_CHAR (Y); END Why does this code produce an error when executed? 38 A. The value f V_found cannot be YES. B. Variable V_found is declared at more than one location. C. Variable Y is declared in the inner block and referenced in the outer block. D. Variable V_sal is declared in the outer block and referenced in the inner block. Answer: C Explanation: Answer C is correct because the usage of variable Y, which have been defined in the inner block, in the outer block (second line of code from the bottom) will cause the error. Incorrect Answers: A: V_found variable is VARCHAR2 type, so it can have value ‘YES’. B: Variable V_found is declared at more that one location, in the inner and the outer blocks, but it is correct usage of the variables in PL/SQL block. Each of it has its own scope. There is no error in this situation. D: All variables defined in the outer block can be referenced in the inner block, but not vice versa. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 213 Chapter 5: Introducing PL/SQL QUESTION NO: 45 Which statement is valid within the executable section of Pl/SQL block? A. BEGIN emp_rec emp%ROWTYPE END; B. WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT.LINE(‘No records found’); C. Select ename,sal into v_ename,v_sal from emp where empno=101; D. Procedure cal_max(n1 NUBER n2 NUMBER, p_max OUT NUMBER) IS BEGIN If n1>n2 then p_max:=n1; Else p_max=n2; END. 39 Answer: C Explanation: Answer C is correct because this statement populates variables v_ename and v_sal with data from EMP table. Syntax is correct. Incorrect Answers: A: Record cannot be defined inside the executable section of PL/SQL block, only inside the declarative section. B: Exception NO_DATA_FOUND can be used only inside the exception handler block, not inside the executable block. D: It represents header section of the PL/SQL block, not the executable block. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 209 Chapter 5: Introducing PL/SQL QUESTION NO: 46 How do you send the output of your SQL* Plus session to a text operating system file called MYOUTPUT.LST? A. SAVE MYOUTPUT.LST B. SPOOL MYOUTPUT.LST C. PRINT MYOUTPUT.LST D. SEND MYOUTPUT.LST Answer: B Explanation: Answer B is correct because command SPOOL is used in SQL * Plus to send output of the session to a text operation system file. Incorrect Answers: A: Command SAVE is not used for output generation in SQL * Plus. C: Command PRINT is used for printing from SQL * Plus. D: Command SEND does not exist. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 7 Chapter 1: Selecting data from Oracle QUESTION NO: 47 The product table contains these columns. 40 ID NUMBER(9) PK COST NUMBER(7,2) SALE_PRICE NUMBER(7,2) Management has asked you to calculate the net revenue per unit for each product, if the cost of each product is increased by 10% and the sale price of each product is increased by 25%. You issue this SQL statement. SELECT id, sale_price * 1.25 – cost * 1.10 FROM product; Which conclusion can you draw from the results? A. Only the required results are displayed. B. The results provide more information than management requested. C. A function needs to be included in the SELECT statement to achieve the desired result. D. The order on the operations in the calculation needs to be changed to achieve the required results. Answer: A Explanation: Answer A is correct because only the requested results will be displayed. Incorrect Answers: B: This query returns only information requested by management. C: There is no need to use additional function to display desired results. D: The order of operations is correct and does not need to be changed. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 6-8 Chapter 1: Selecting data from Oracle QUESTION NO: 48 You want to create report to show different jobs in each department. You do not want to display any duplicate roles in the report. Which SELECT statement do you use to create the report? A. SELECT deptno, job FROM emp; B. SELECT no duplicate deptno, job FROM emp; C. SELECT distinct deptno, job FROM emp; 41 D. CREATE report DISPLAY deptno, job FROM emp; E. SELECT distinct deptno, distinct job FROM emp; Answer: C Explanation: Answer C is correct because this query uses keyword DISTINCT which allows to avoid duplications in displayed results. Incorrect Answers: A: This statement returns results with duplications. B: NO DUPLICATE is wrong syntax in SELECT statement. D: CREATE REPORT is wrong syntax in SELECT statement. E: Keyword DISTINCT does not need to be used for each column in the list of SELECT statement. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 70-71 Chapter 2: Advanced Data Selection in Oracle QUESTION NO: 49 Which SELECT statement displays employee names, salary, department numbers and average salaries for all employees who earn more than the average salary in their department? A. SELECT ename, sal, deptno, AVG(sal) FROM emp GROUP BY ename, sal, deptno Answer: A QUESTION NO: 50 Mr. King is the president of a company. Five managers report to him. All other employees report to these managers. Examine the code. SELECT employee.ename FROM emp employee WHERE employee. empno not in SELECT manager.mgr FROM emp manager; 42 The above statement returns no rows selected as the result why? A. All employees have a manager. B. None of the employees have a manager. C. A null value is returned from the sub query. D. Operator is not allowed in sub queries. Answer: C Explanation: Answer C is correct because a null value is returned from the sub query (president of a company does not have manager for himself). Incorrect Answers: A: Not ALL employees have a manager. B: Some of the employees have a manager. D: Statement is correct, all operators is used correctly. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 64-72 Chapter 5: Advanced Data Selection in Oracle