OCP Exam 1: Introduction to SQL

OCP Exam 1 in the Oracle DBA track covers concepts and practices involving the use of SQL commands. To pass this exam, you need to demonstrate an understanding of the basic SQL constructs available in Oracle, including built-in functions. You should also understand the basic concepts of an Oracle relational database management system (RDBMS). In more recent editions of OCP Exam 1, the focus has shifted to a complete understanding of use of the SQL programming language. In addition, new features introduced in Oracle9i are tested, so you should also be sure you understand these new features.

Practice Exam 1

1.	You are formulating a SQL statement to retrieve data from Oracle. Which of the following SQL statements is invalid? select NAME, JERSEY_NO where Jersey_No = 6; select NAME, JERSEY_NO from PLAYERS; select * from PLAYERS where JERSEY_NO = 6; select JERSEY_NO from PLAYERS;
2.	You are processing some data changes in your SQL*Plus session as part of one transaction. Which of the following choices does not typically indicate the end of a transaction? Issuing an update statement Issuing a commit statement Issuing a rollback statement Ending your session
3.	You have just removed 1,700 rows from a table. In order to save the changes you've made to the database, which of the following statements is used? savepoint commit rollback set transaction
4.	To identify the columns that are indexed exclusively as the result of their inclusion in a constraint, which of the following dictionary views is appropriate? USER_INDEXES USER_TAB_COLUMNS USER_COLUMNS USER_CONS_COLUMNS
5.	You are creating some tables in your database as part of the logical data model. Which of the following constraints can only be created as a column constraint (that is, not as a table constraint) either when you create or alter the table? Unique Foreign key Check Not NULL
6.	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? The triggers The indexes The foreign keys in the other tables The view
7.	You are using SQL operations in Oracle. All of the following DATE functions return a DATE datatype except one. Which one is it? NEW_TIME LAST_DAY ADD_MONTHS MONTHS_BETWEEN
8.	You issue a select statement on the BANK_ACCT table containing the order by clause. Which of the following uses of the order by clause would produce an error? order by acctno DESC; order by 1; order by sqrt(1); order by acctno ASC;
9.	You execute the query select 5 + 4 from DUAL. You have never inserted data into the DUAL table before. Which of the following statements best describes the DUAL table? Dictionary view containing two schema names Table with one column and one row used in various operations Dictionary view containing two index names Table with two columns and no rows used in various operations
10.	You issue the following statement: SELECT DECODE(ACCTNO, 123456, 'CLOSED', 654321, 'SEIZED', 590395, 'TRANSFER','ACTIVE') FROM BANK_ACCT; If the value for ACCTNO is 503952, what information will this statement display? ACTIVE TRANSFER SEIZED CLOSED
11.	You are entering several dozen rows of data into the BANK_ACCT table. Which of the following statements enables you to execute the same statement again and again, entering different values for variables at statement runtime? insert into BANK_ACCT (ACCTNO, NAME) VALUES (123456,'SMITH'); insert into BANK_ACCT (ACCTNO, NAME) VALUES (VAR1, VAR2); insert into BANK_ACCT (ACCTNO, NAME) VALUES (&VAR1, '&VAR2'); insert into BANK_ACCT (select ACCTNO, NAME from EMP_BANK_ACCTS);
12.	You execute the following SQL statement: select ADD_MONTHS ('28-APR-97',120) from DUAL. What will Oracle return? 28-APR-03 28-APR-07 28-APR-13 28-APR-17
13.	On Monday, June 26, 2037, at 10:30 P.M., you issue the following statement against an Oracle database: ALTER SESSION SET NLS_DATE_FORMAT = 'DAY MONTH DD, YYYY: HH:MIAM'; Then you issue the following statement: SELECT SYSDATE FROM DUAL; What will Oracle return? 26-JUN-37 June 26, 2037, 22:30 26-JUN-2037 MONDAY JUNE 26, 2037: 10:30PM
14.	You want to join the data from two tables, A and B, into one result set and display that set in your session. Tables A and B have a common column, called C in both tables. Which of the following choices correctly displays the where clause you should use if you want to see the data in table A where the value in column C equals 5, even when no corresponding value appears in table B? where A.C = 5 AND A.C = B.C; where A.C = 5 AND A.C = B.C (+); where A.C = 5 AND A.C (+) = B.C(+); where A.C = 5;
15.	Each of the following statements is true about associated columns and datatypes except one. Which of the following statements is not true? A column designed to hold data in a table must be declared with a datatype large enough to hold values for that column. When creating composite primary keys, the datatypes in all columns within the primary key must be the same datatype. When creating referential integrity constraints between two tables, the datatype of the referenced column in the parent table must be identical to the referencing column in the child. When creating record variables designed to hold a row's worth of data, each element's datatype in the record must be large enough to hold the associated column from the table.
16.	You have a group of values from a column in a table, and you would like to perform a group operation on them. Each of the following functions operates on data from all rows as a group except for which of the following choices? avg( ) sqrt( ) count( ) stddev( )
17.	You have a situation where you need to use the nvl( ) function. All the following statements about the nvl( ) function are true except one. Which is it? nvl( ) returns the second value passed if the first value is NULL. nvl( ) handles values of many different datatypes. nvl( ) returns NULL if the first value is not equal to the second. Both the values passed for nvl( ) must be the same datatype.
18.	You create a sequence with the following statement: CREATE SEQUENCE MY_SEQ START WITH 394 INCREMENT BY 12 NOMINVALUE NOMAXVALUE NOCACHE NOCYCLE;   A user issues SQL statements to obtain NEXTVAL three times, and then issues SQL statements to obtain CURRVAL four times. What is the current value of the sequence? 406 418 430 442
19.	Table EMP has 17,394,430 rows in it. You issue a delete from EMP statement, followed by a commit. Then you issue a select count(*) to find out how many rows are in the table. Several minutes later, Oracle returns zero. Why did it take so long for Oracle to obtain this information? The table was not empty. The high-water mark was not reset. Oracle always performs slowly after a commit is issued. The table data did not exist to be counted anymore.
20.	After creating a view, you realize that several columns were left out. Which of the following statements should you issue in order to add some columns to your view? alter view create or replace view insert into view create view
21.	You are testing several SQL statements for accuracy and usefulness. A SQL statement will result in a Cartesian product as the result of which of the following items? A join statement without a where clause The result of the sum( ) operation select * from DUAL The result of the avg( ) operation
22.	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 should you use? Setting preferences in the appropriate menu option Creating an appropriate login.sql file Issuing the alter user statement Issuing the alter table statement
23.	The EMP_SALARY table has two columns: EMP_USER and SALARY. EMP_USER is set to be the same as the Oracle username. To allow user MARTHA, the salary administrator, to see her own salary only, 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'; Later, you decide to deploy this view to other users. Which of the following choices identifies a revision of this view that would prevent users from seeing any salary information other than their own? create or replace view emp_sal_vw as select emp_user,_salary from emp_salary where emp_user <> user; create or replace view emp_sal_vw as select emp_user,_salary from emp_salary where emp_user = user; create or replace view emp_sal_vw as select emp_user,_salary from emp_salary where emp_user <> 'MARTHA'; create or replace view emp_sal_vw as select emp_user,_salary from emp_salary where emp_user in (select emp_user from emp_salary where emp_user <> 'MARTHA');
24.	You are trying to store data in an Oracle table. All of the following scalar datatypes can be stored in an Oracle database except one. Which is it? CHAR RAW DATE INTEGER
25.	You are performing some conversion operations in your SQL*Plus session. To convert a date value into a text string, you should use which of the following conversion functions? CONVERT TO_CHAR TO_NUMBER TO_DATE
26.	Your attempt to read the trigger code stored in the Oracle data dictionary view ALL_TRIGGERS has encountered a problem. The contents of the TRIGGER_BODY column appear to be getting cut off at the end. In order to resolve this problem, which of the following measures is appropriate? Grant appropriate select privileges on ALL_TRIGGERS to yourself. Increase your memory allocation limit with the alter user statement. Use the set command to allow for larger LONG column values. Drop and recreate the ALL_TRIGGERS view.
27.	You issue the following update statement against the Oracle database: UPDATE BANK_ACCT SET NAME = 'SHAW'; Which records will be updated in that table? The first record only All records The last record only None of the records
28.	You create a table but then subsequently realize you need a few new columns. To add those columns later, you should issue which of the following statements? create or replace table alter table create table truncate table
29.	You are busy creating your tables based on a logical data model. Which of the following constraints requires the references privilege in order to be created? unique foreign key check not NULL
30.	The INVENTORY table has three columns: UPC_CODE, UNITS, and DELIV_DATE. The primary key is UPC_CODE. You want to add new records daily through a view. The view will be created using the following code: CREATE VIEW DAY_INVENTORY_VW AS SELECT UPC_CODE, UNITS, DELIV_DATE FROM INVENTORY WHERE DELIV_DATE = SYSDATE ORDER BY UPC_CODE; What happens when you try to create the previous view? Oracle returns an error stating that the order by clause is not permitted on views. Oracle returns an error stating that the with check option clause is required for creating this view. Oracle returns an error stating that the select statement must be enclosed in parentheses. Oracle creates the view successfully.
31.	You need to search for text data in a column, but you only remember part of the string. Which of the following SQL operations enables the use of wildcard comparisons? in exists between like
32.	You have a script you plan to run using SQL*Plus that contains one SQL statement that inserts data into one table. Which of the following options is the easiest way for this script to enable you to specify values for variables once in the script in a way where no user interaction is required at the SQL*Plus prompt? Use define to capture values. Use accept to capture values for each run. Use & to specify values at runtime for the statement. Use hard-coded values in the statement.
33.	You join data from two tables, EXPNS and EMP, into one result set and display that set in your session. The tables have a common column called EMPID. Which of the following choices correctly displays the where clause you would use if you wanted to see the data in table EMP where the value in column EMPID equals 39284, but only when a corresponding value appears in table EXPNS? where EMP.EMPID = 39284 AND EMP.EMPID = EXPNS.EMPID; where EMP.EMPID = 39284 (+) AND EMP.EMPID = EXPNS.EMPID; where EMP.EMPID = EXPNS.EMPID; where EMP.EMPID = 39284 AND EMP.EMPID = EXPNS.EMPID (+);
34.	Review the following transcript of a SQL*Plus session: INSERT INTO INVENTORY (UPC_CODE, PRODUCT) VALUES (503949353,'HAZELNUT COFFEE'); INSERT INTO INVENTORY (UPC_CODE, PRODUCT) VALUES (593923506,'SKIM MILK'); INSERT INTO INVENTORY (UPC_CODE, PRODUCT) VALUES (402392340,'CANDY BAR'); SAVEPOINT INV1; UPDATE INVENTORY SET UPC_CODE = 50393950 WHERE UPC_CODE = 402392340; UPDATE INVENTORY SET UPC_CODE = 4104930504 WHERE UPC_CODE = 402392340; COMMIT; UPDATE INVENTORY SET PRODUCT = ( SELECT PRODUCT FROM INVENTORY WHERE UPC_CODE = 50393950) WHERE UPC_CODE = 593923506; ROLLBACK;   Which of the following UPC codes will not have records in the INVENTORY table as a result of this series of operations? 593923506 503949353 4104930504 50393950
35.	You are removing a table from the Oracle database. When you issue the drop table command to remove the table, what happens to any of the views that may have an object dependency on that table? The views are dropped automatically along with the table. Views in the same schema as the table are dropped automatically, but views outside that schema are not dropped. Views in the same database as the table are dropped automatically, but views that access the table via database link are not dropped. Views with object dependencies on the table being dropped are rendered invalid automatically, but are not dropped.
36.	You want to join data from four tables into one result set and display that set in your session. Table A has a column in common with table B, table B with table C, and table C with table D. You want to further restrict data returned from the tables by only returning data where values in the common column shared by A and B equal 5. How many conditions should you have in the where clause of your select statement? Two Three Four Five
37.	You are attempting to explain the Oracle security model for an Oracle database to the new security administrator. What are two components of the Oracle database security model? Password authentication and granting privileges Password authentication and creating database objects Creating database objects and creating users Creating users and password authentication
38.	You have a script you plan to run using SQL*Plus that contains several SQL statements that manage milk inventory in several different tables based on various bits of information. You want the output to go into a file for review later. Which command should you use? prompt echo spool define
39.	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 a check in the application prevents 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? select A.STUDENT_ID, A.LOCATION, B.LOCATION from TEST_SCORE A, TEST_SCORE B where A.STUDENT_ID = B.STUDENT_ID AND A.LOCATION = B.LOCATION AND trunc(A.TEST_DATE)+30 <= trunc(B.TEST_DATE) AND trunc(A.TEST_DATE)-30 >= trunc(B.TEST_DATE); select A.STUDENT_ID, A.LOCATION, B.LOCATION from TEST_SCORE A, TEST_SCORE B where A.STUDENT_ID = B.STUDENT_ID AND A.LOCATION <> B.LOCATION AND trunc(A.TEST_DATE)+30 >= trunc(B.TEST_DATE) AND trunc(A.TEST_DATE)-30 <= trunc(B.TEST_DATE); select A.STUDENT_ID, A.LOCATION, B.LOCATION from TEST_SCORE A, TEST_SCORE B where A.STUDENT_ID = B.STUDENT_ID AND A.LOCATION = B.LOCATION AND trunc(A.TEST_DATE)+30 >= trunc(B.TEST_DATE) AND trunc(A.TEST_DATE)-30 <= trunc(B.TEST_DATE); select A.STUDENT_ID, A.LOCATION, B.LOCATION from TEST_SCORE A, TEST_SCORE B where A.STUDENT_ID = B.STUDENT_ID AND A.LOCATION <> B.LOCATION AND trunc(A.TEST_DATE)+30 <= trunc(B.TEST_DATE) AND trunc(A.TEST_DATE)-30 >= trunc(B.TEST_DATE);
40.	In an expense application, you are searching for employee information in the EMPLOYEE table corresponding to an invoice number you have. The INVOICE table contains EMPID, the primary key for EMPLOYEE. Which of the following options is appropriate for obtaining data from EMPLOYEE using your invoice number? select * from EMPLOYEE where empid = &empid; select * from EMPLOYEE where empid = 69494; select * from EMPLOYEE where empid = (select empid from invoice where invoice_no = 4399485); select * from EMPLOYEE;
41.	Which of the following uses does not describe an appropriate use of the having clause? To put returned data into sorted order To exclude certain data groups based on known criteria To include certain data groups based on unknown criteria To include certain data groups based on known criteria
42.	You are managing data access for an application with 163 tables and 10,000 users. Which of the following objects would assist in managing access in this application by grouping privileges into an object that can be granted to users at once? Sequences Tables Indexes Roles
43.	After logging onto Oracle the first time to access table EMP, user SNOW is told to change his password. Which of the following statements enables him to do so? alter user alter table alter role alter index
44.	User SNOW executes the following statement: select * from EMP.This statement executes successfully, and SNOW can see the output. User REED owns table EMP. What object is required in order for this scenario to happen? User SNOW needs the role to view table EMP. User SNOW needs the privileges to view table EMP. User SNOW needs a synonym for table EMP. User SNOW needs the password for table EMP.
45.	You issue the following statement in Oracle: SELECT * FROM EMP WHERE DEPT IN (SELECT DEPT FROM VALID_DEPTS WHERE DEPT_HEAD = 'SALLY' ORDER BY DEPT); Which of the following choices best indicates how Oracle will respond to this SQL statement? Oracle returns the data selected. Oracle returns data from EMP but not VALID_DEPTS. Oracle returns data from VALID_DEPTS but not EMP. Oracle returns an error.
46.	You are coding SQL statements in SQL*Plus. Which of the following is a valid SQL statement? select nvl(sqrt(59483)) from dual; select to_char(nvl(sqrt(59483), 0)) from dual; select to_char(nvl(sqrt(59483), 'VALID')) from dual; select (to_char(nvl(sqrt(59483), '0')) from dual;
47.	The following output is from a SQL*Plus session: select PLAY_NAME||', ' || AUTHOR play_table from PLAYS; My Plays and Authors ------------------------------------- Midsummer Night's Dream, SHAKESPEARE Waiting For Godot, BECKETT The Glass Menagerie, WILLIAMS   Which of the following SQL*Plus commands produced it? column PLAY_TABLE alias "My Plays and Authors" column PLAY_TABLE format a12 column PLAY_TABLE heading "My Plays and Authors" column PLAY_TABLE as "My Plays and Authors"
48.	You create a view with the following statement: CREATE VIEW BASEBALL_TEAM_VW AS SELECT B.JERSEY_NUM, B.POSITION, B.NAME FROM BASEBALL_TEAM B WHERE B.NAME = (SELECT UNAME FROM MY_USERS); The contents of the MY_USERS table are listed as follows: UNAME ----- JONES SMITH FRANK JENNY Which of the following players will not be listed when user JONES attempts to query the view? JONES SMITH BABS JENNY
49.	Your attempt to read the view-creation code stored in the Oracle data dictionary has encountered a problem. The view code appears to be getting cut off at the end. In order to resolve this problem, which of the following measures is appropriate? Increase the size of the dictionary view. Increase your user view allotment with the alter user statement. Use the set long statement. Use the set NLS_DATE_FORMAT statement.
50.	Inspect the following SQL statement: SELECT FARM_NAME, COW_NAME, COUNT(CARTON) AS NUMBER_OF_CARTONS FROM COW_MILK GROUP BY COW_NAME; Which of the following choices contains the line with the error? select FARM_NAME, COW_NAME, count(CARTON) as NUMBER_OF_CARTONS from COW_MILK group by COW_NAME; This statement has no errors.
51.	Inspect the following SQL statement: SELECT COW_NAME, MOD(CARTON, FILL_STATUS) FROM COW_MILK GROUP BY COW_NAME; Which of the following lines contains an error? select COW_NAME, mod(CARTON, FILL_STATUS) from COW_MILK group by COW_NAME; This statement has no errors.
52.	You are writing queries against an Oracle database. Which of the following queries takes advantage of an inline view? select * from EMP_VW where EMPID = (select EMPID from INVOICE where INV_NUM = 5506934); select A.LASTNAME, B.DEPT_NO from EMP A, (select EMPID, DEPT_NO from DEPT) B where A.EMPID = B.EMPID; select * from EMP where EMPID IN (select EMPID from INVOICE where INV_NUM > 23); select 'select * from EMP_VW where EMPID is not NULL;' from USER_TABLES;
53.	You have several indexes on a table that you want to remove. You want to avoid removing the indexes associated with constraints, however. Each of the following statements will remove the index associated with a constraint except one. Which choice will not remove the index associated with a constraint? drop index alter table drop primary key cascade alter table drop constraint drop table
54.	You are managing constraints on a table in Oracle. Which of the following choices correctly identifies the limitations on primary key constraints? Every primary key column value must be unique. No primary key column value can be NULL. Every primary key column value must be unique and none can be NULL. Every primary key column must be the same datatype as other columns in the table.
55.	Review the following statement: CREATE TABLE FOOBAR ( MOO VARCHAR2(3), BOO NUMBER); This table contains 60,000,000 rows. You issue the following statement: SELECT MOO, BOO FROM FOOBAR WHERE MOO = 'ABC' This value is unique in column MOO, yet the query takes several minutes to resolve. Which of the following explanations is the best reason why? Oracle didn't use the existing primary key index. select statements that do not use views take longer to resolve. Table FOOBAR has no primary key, and therefore has no index on MOO. The table had been dropped and recreated.
56.	You have created a table called EMP with a primary key called EMP_PK_01. In order to identify any objects that may be associated with that table and primary key, what dictionary views and characteristics would you look for? USER_SEQUENCES, sequences created at the same time USER_TABLES, tables with the same number of columns USER_IND_COLUMNS, constraints with the same name as the table USER_INDEXES, indexes with the same name as the constraint
57.	You are designing your database and attempting to determine the best method for indexing your tables. Which of the following is a main advantage of using bitmap indexes on a database? To improve performance on columns with many unique values To improve performance on columns with few unique values To improve performance on columns with all unique values To improve performance on sequences with all unique values
58.	User HARRIS would like to change a row into the EMPLOYEE table that has three columns: EMPID, LASTNAME, and SALARY. The user would like to update salary data for employee number 59694. Which statement would work best? update employee set salary = 5000 where empid = 59694; update employee set empid = 45939 where empid = 59694; update employee set lastname = 'HARRIS' where empid = 59694; update employee set salary = 5000 where lastname = 'HARRIS';
59.	You want to grant user TIMOTHY the ability to update data in the EMP table as well as the ability to administer that access for others. Which of the following commands would you issue? grant update to timothy; grant update on emp to timothy; grant update on emp to timothy with grant option; grant update on emp to timothy with admin option;
60.	User REED can administer the create session privilege. User REED grants the same create session privilege to MANN using the appropriate clause. MANN then grants the privilege to SNOW. REED discovers MANN issued the privilege to SNOW and revokes the privilege from MANN. Who can connect to Oracle? REED only SNOW and MANN only REED, MANN, and SNOW REED and SNOW only

Answers

1.	A. select NAME, JERSEY_NO where JERSEY_NO = 6; Explanation SQL statements in Oracle must have a from clause. A SQL statement can lack a where clause, in which case, all of the data in the table will be returned. However, if the statement does not have a from clause, Oracle will not know what table to retrieve data from. Recall that a special table called DUAL assists in situations where you don't want to retrieve data from a table, but instead only want to manipulate expressions. (Topic 2.1)
2.	A. Issuing an update statement Explanation The only choice that does not end a transaction is the one that continues the transaction, namely issuing another update statement. A commit tells Oracle to save your data changes and end the transaction. A rollback tells Oracle to discard your data changes and end the transaction. Closing SQL*Plus or otherwise ending the session is usually treated as an implicit commit and ends your transaction as well. (Topic 8.3)
3.	B. commit Explanation In order to save any change you make in Oracle, you use the commit command. The savepoint command merely identifies a logical breakpoint in your transaction that you can use to break up complex units of work. The rollback command discards every change you made since the last commit. Finally, the set transaction command sets up the transaction to be read-only against the Oracle database. (Topic 8.6)
4.	D. USER_CONS_COLUMNS Explanation The USER_CONS_COLUMNS dictionary view shows you all of the columns in tables belonging to that user that are part of indexes used to enforce constraints. USER_INDEXES is incorrect because that view only displays information about the index itself, not the columns in the index. USER_TAB_COLUMNS displays all the columns in all tables owned by the user. Finally, USER_COLUMNS is not an actual view in the Oracle database. (Topic 12.2)
5.	D. Not NULL Explanation Not NULL integrity constraints can only be declared as column constraints, meaning that the actual syntax for defining the constraint will appear next to the constrained column, as opposed to at the end of the column listing. Choices A, B, and C all identify constraints that can be defined as table constraints or as column constraints. (Topic 10.2)
6.	D. The view Explanation When you drop a table with the cascade constraints option, Oracle removes from other tables all associated indexes, triggers, and constraints that reference that table. Oracle does not remove the views that use that table, however. You must remove a view manually with the drop view statement. (Topic 11.2)
7.	D. MONTHS_BETWEEN Explanation Each of the choices accepts a DATE datatype as input and returns a DATE datatype, with one exception. The MONTHS_BETWEEN function returns a number indicating how many months there are between the two dates you give it. This number will be displayed with numbers to the right of the decimal point, which you can round off if you like. (Topic 3.2)
8.	C. order by sqrt(1); Explanation The order by clause in the select clause of the select statement enables you to refer to the column you want the table order determined by, either by the column name or by the number representing the column order. However, you cannot perform any sort of numeric function on that column-order number. Both the asc and desc keywords are valid for the order by clause, indicating ascending order (default) and descending order, respectively. (Topic 2.2)
9.	B. Table with one column and one row used in various operations Explanation The DUAL table is a special table in Oracle used to satisfy the requirement of a from clause in your SQL statements. It contains one column and one row of data. It is not a dictionary view; rather, it is an actual table. You could use the DUAL table in arithmetic expressions and not actually pull real data from the database. You should never insert data into the DUAL table under any circumstances. (Topic 3.2)
10.	A. ACTIVE Explanation The decode() function is used as a case statement, where Oracle will review the value in the column identified in the first parameter (in this case, ACCTNO). If that value equals the second parameter, the third parameter is returned. If that value equals the fourth parameter, the fifth parameter is returned, and so on. If the value equals no parameter, the default value provided in the last parameter (in this case, ACTIVE) is returned. TRANSFER would be returned if ACCTNO equaled 590395, SEIZED would be returned if ACCTNO equaled 654321, and CLOSED would be returned if ACCTNO equaled 123456. (Topic 3.2)
11.	C. insert into BANK_ACCT (ACCTNO, NAME) VALUES (&VAR1, '&VAR2'); Explanation In order to have statement reusability where you can enter a value on-the-fly, you must use lexical references as runtime variables. These references are preceded with an ampersand (&) character, as in the correct answer. Although you can use nested subqueries in your insert statements, this has the effect of inserting multiple rows at once without requiring input from the user. (Topic 7.1)
12.	B. 28-APR-07 Explanation For this question, you really need to put on your thinking cap. ADD_MONTHS adds a specified number of months, indicated by the second parameter to the value in the first parameter. The parameter 120 months is ten years, so if you add ten to the year in the date given, you should come up with 28-APR-07, which is the correct answer. When you are taking the exam, beware of having too much of your time sucked up by this sort of brainteaser question. (Topic 3.2)
13.	D. MONDAY JUNE 26, 2037: 10:30PM Explanation The first statement in this question alters the date format shown in your SQL*Plus session. The second statement returns the current date and time in that specific format. In this case, your format is the day of the week, followed by the month of the year, the date, the year, and the time in A.M./P.M. format. This being the case, the correct answer is MONDAY JUNE 26, 2037: 10:30PM. (Topic 3.2)
14.	B. where A.C = 5 AND A.C = B.C (+); Explanation The correct choice illustrates the use of Oracle's outer join function. The question indicates that you want to see data in table A, whether or not corresponding data exists in table B. Thus, you place the outer join operation (it looks like a (+)) next to the reference to the C column in table B. If the outer join operation is removed, Oracle will only return data from table A for which corresponding data exists in table B. If the outer join operator is used for both tables, you will get a syntax error. If you omit the join operator comparing values from table A to table B, Oracle will return a Cartesian product of the data you requested from A with all the data from table B. (Topic 4.2)
15.	B. When creating composite primary keys, the datatypes in all columns within the primary key must be the same datatype. Explanation No restriction exists on column datatypes for composite primary keys requiring that all columns in the primary key have the same datatype. Choice A is incorrect because you must ensure that the variables designed to hold data from table columns are large enough for the values in those columns. Choice D is incorrect for largely the same reason. Finally, choice C is incorrect because Oracle forces you to declare the column in a child table with the exact same datatype as it has in the parent table. (Topic 10.2)
16.	B. sqrt() Explanation All the choices indicate group by functions except for the sqrt() function. sqrt()is a single-row function acting on each value in each column row, one at a time or individually. avg() processes data from multiple rows in a column and produces one result: the average value for all of them. count() processes all values in a column or columns and counts the number of row values in that column or columns. The stddev() function takes all values in a column of rows and determines the standard deviation for that set of values. (Topic 5.1)
17.	C. nvl() returns NULL if the first value is not equal to the second. Explanation The only statement that is not true is nvl() returns NULL if the first value is not equal to the second. nvl() is specifically designed to avoid returning NULL for a column by substituting another value that you pass as the second parameter. nvl() handles many different datatypes, and both values passed must be the same datatype. (Topic 3.3)
18.	B. 418 Explanation Regardless of what you think you know about Oracle, the only true way to know what Oracle does is to experience it. Three requests for NEXTVAL from MY_SEQ does not mean that each request increments the sequence because the first request for NEXTVAL returns the initial value. Take a look: SQL> create sequence my_seq 2 start with 394 3 increment by 12 4 nominvalue 5 nomaxvalue 6 nocycle 7 nocache; Sequence created. SQL> select my_seq.nextval from dual; NEXTVAL --------- 394 SQL> / NEXTVAL --------- 406   SQL> / NEXTVAL --------- 418 SQL> select my_seq.currval from dual; CURRVAL --------- 418 SQL> / CURRVAL --------- 418 SQL> / CURRVAL --------- 418 SQL> / CURRVAL ————- 418 Thus, the sequence has only been incremented twice, so the answer is 418. (Topic 12.1)
19.	B. The high-water mark was not reset. Explanation The select count(*) statement takes a long time because Oracle needed to inspect the table in its entirety in order to derive the row count, even though the table was empty. To avoid this situation on large tables, use the truncate statement rather than delete. truncate resets the high-water mark on your table, thus reducing the time it takes Oracle to perform select count(*) operations. (Topic 9.5)
20.	B. create or replace view Explanation The column definitions for a view can be changed only by recreating the view with the create or replace view statement. The alter view command is used only to recompile a view. insert into view is not a valid SQL statement. Although create view will technically work, you must first drop the view you want to recreate, which requires two statements, not one. create or replace view is the most accurate choice offered. (Topic 11.2)
21.	A. A join statement without a where clause Explanation Cartesian products are the result of select statements that contain malformed where clauses. sum() and avg() operations are group functions and do not produce Cartesian products. Selecting data from the DUAL table will not produce a Cartesian product because only one table is involved—and a table with only one row at that! (Topic 4.1)
22.	B. Creating an appropriate login.sql file Explanation SQL*Plus shows its roots in UNIX systems through the login.sql file. This file is used to specify settings used in your session. login.sql runs automatically after you log into Oracle. SQL*Plus in Windows environments does not have a Preferences menu, eliminating that choice. You shouldn't attempt to use the alter table or alter user statements for this purpose either. (Topic 7.3)
23.	B. create or replace view emp_sal_vw as select emp_user, salary from emp_salary where emp_user = user; Explanation The command in choice B is correctly defined for creating a view that will only enable users to see their own salary information from the underlying table. Choice A is incorrect because the view defined will show all salary information except for salary data for the user issuing the query. Choices C and D are incorrect because the view will show only salary data for users other than MARTHA. (Topic 11.2)
24.	D. INTEGER Explanation Although you can declare variables in PL/SQL blocks using the INTEGER datatype, you cannot store INTEGER datatype data in Oracle tables. All other datatypes shown—CHAR, RAW, and DATE—can be stored in the Oracle database. (Topic 9.3)
25.	B. TO_CHAR Explanation TO_CHAR is used to convert DATE values, numbers, and other things into text strings. The CONVERT operation is used to convert a text string from one character set to another. The TO_NUMBER operation converts numeric text to true numbers. The TO_DATE function is used to convert a properly formatted text string into a DATE value. (Topic 3.3)
26.	C. Use the set command to allow for larger LONG column values. Explanation The TRIGGER_BODY column in the ALL_TRIGGERS view is declared as a LONG datatype column, and SQL*Plus is most likely cutting off data from the output. Choice A is incorrect because the question says you are able to see some of the data in the view, just not all the data. Choice B is incorrect because memory allocation has nothing to do with the problem identified in the question. Finally, choice D is incorrect because nothing is wrong with the ALL_TRIGGERS view. The problem lies instead with how SQL*Plus is currently configured to display LONG column data. (Topic 7.2)
27.	B. All records Explanation Because the update statement does not contain a where clause, the change will be made to every record in the table. It is not possible to accurately update only the first or last record in the table. None of the records will be updated only if something is wrong with the update statement, such as a column being referenced incorrectly. (Topic 8.3)
28.	B. alter table Explanation The alter table statement enables you to easily add columns after the table is created, with minimal impact to your system. Unlike when you want to change views, you do not use the or replace keyword for this effort, thus creating a powerful distraction for the user who is more familiar with views than with underlying tables. The create table statement could be used for the task, but you would first need to issue the drop table statement to get rid of the initial table. (Topic 9.4)
29.	B. foreign key Explanation Foreign key relationships require that you grant references privileges on a table to the user who is creating the foreign key relationship from his or her table to yours. No particular special privilege must be granted to create unique, check, or not NULL constraints other than create table. (Topic 10.2)
30.	D. Oracle creates the view successfully. Explanation When you issue the create view command shown in the question, Oracle creates the view successfully. A view can be created with the order by clause, making choice A incorrect. You do not need to enclose the select statement in your create view command in parentheses, as choice C suggests. Finally, Choice B is incorrect because you do not need to use the with check option clause for creating a view. (Topic 11.2)
31.	D. like Explanation In the situation where you want to use wildcards, Oracle offers the like comparison operator. This operator enables you to search for text strings like the one you're looking for. The in operator specifies a set of values to which the comparison value can be equal to one of. exists enables you to use a subquery as a lookup validity test for some piece of information. between specifies a range comparison, such as between 1 AND 5. (Topic 2.1)
32.	A. Use define to capture values. Explanation The define command can be used to identify a variable and assign it a value for use throughout a script running in SQL*Plus. This is useful when you are executing a number of SQL statements in batch. Although the accept command can perform the same function, the key factor that makes this the wrong answer is the mention of no user interaction in the question. Hard-coded values will work, but they make the script almost completely not reusable. Finally, although lexical references using an ampersand (&) followed by a label will provide statement reusability, your users will need to keep entering values every time a statement containing the lexical reference is processed. (Topic 7.1)
33.	A. where EMP.EMPID = 39284 AND EMP.EMPID = EXPNS.EMPID; Explanation Because you only want data from each table where a match appears in the other, you are performing a regular join or equijoin operation. In Oracle, you would not use the outer join (+) operator for this purpose. This eliminates both of the answer choices that contain an outer join operator. (Topic 4.1)
34.	C. 4104930504 Explanation The only record that will not be present from the choices given is 4104930504, because UPC code 402392340 does not exist at the time this statement is issued. It was already changed to 50393950, and thus the 4104930504 update statement fails when you issue it. In order to get the answer correct, you need to read the question for a long time, and that wastes time when you're taking the OCP exams. Be aware that this question can take up an enormous amount of time if you're not careful. (Topic 8.2)
35.	D. Views with object dependencies on the table being dropped will be rendered invalid automatically, but will not be dropped. Explanation Oracle does not remove views when you drop underlying tables. Instead, Oracle merely marks the view as invalid. Thus, because choices A, B, and C all indicate in various different ways that the view will be dropped, all those choices are incorrect. (Topic 11.3)
36.	C. Four Explanation The general rule of thumb here is that if you have n tables you want to join—four in this case—you will generally need n - 1 comparison operations in your where clause joined together by AND—three in this case. In addition, the question states that you want to further restrict return data based on values in the first table. Thus, your where clause would have four conditions, and may look something like the following block: WHERE A.COLUMN1 = 5 AND A.COLUMN1 = B.COLUMN1 AND B.COLUMN2 = C.COLUMN2 AND C.COLUMN3 = D.COLUMN3 (Topic 4.1)
37.	A. Password authentication and granting privileges Explanation Although in order to get database access you need to create user privileges, the two real components of the Oracle security model are password authentication and granting privileges. When users are created, they will still not be able to connect to Oracle unless they are granted a privilege (create session), and even when they connect, they still cannot see anything unless someone gives them permission via the grant command. (Topic 13.1)
38.	C. spool Explanation The spool command makes SQL*Plus write an output file containing all information transacted in the session, from the time you turn spooling on and identify the output file to the time you either turn spooling off or end the session. prompt causes SQL*Plus to prompt you to enter data using a custom request message. echo causes an error because it is not a valid command in SQL*Plus. Finally, the define command is used for variable definition and variable assignment in SQL*Plus scripts. (Topic 1.3)
39.	B. select A.STUDENT_ID, A.LOCATION, B.LOCATION from TEST_SCORE A, TEST_SCORE B where A.STUDENT_ID = B.STUDENT_ID AND A.LOCATION <> B.LOCATION AND trunc(A.TEST_DATE)+30 >= trunc(B.TEST_DATE) AND trunc(A.TEST_DATE)-30 <= trunc(B.TEST_DATE); Explanation Because it ensures that the student is the same, that the date the test was taken violated the 30-day rule, and that the test location is not the same, choice B is the correct answer. This question is probably the hardest on the exam. Even if you have a bit of SQL experience, this question will take you a while. When taking the OCP exam, the last thing you need is time-waster questions to throw you off. A good technique to avoid having questions like this one consume all your time is to skip it if you cannot answer it within 30 seconds. You'll most likely have some time at the end of the exam to review the questions you skipped. (Topic 4.3)
40.	C. select * from EMPLOYEE where empid = (select empid from invoice where invoice_no = 4399485); Explanation If you can use a subquery, you should do so. Only one choice displays a subquery, so that one must be the correct answer. All the other choices depend on the EMPID being provided, not using the invoice number. (Topic 6.2)
41.	A. To put returned data into sorted order Explanation The having clause is best used to include or exclude certain data groups, not to return data in sort order. The order by clause handles that task. (Topic 2.2)
42.	D. Roles Explanation Roles enable you to group privileges together into one object and grant the privileges to the user at one time. No privileges are related to indexes other than the privilege to access the associated table. Tables and sequences both require privileges to be granted to a user or role; they do not simplify the act of privilege management in any way. (Topic 13.2)
43.	A. alter user Explanation The alter user statement with the identified by clause is used to change a user's password. alter role is used for modifying the actual role object, and affects users insofar as the user has been granted the role. Of the remaining choices, although user SNOW may be able to execute those statements depending on what privileges he is granted, none of these privileges will handle what the question requires. (Topic 13.1)
44.	C. User SNOW needs a synonym for table EMP. Explanation User SNOW needs a synonym in order to refer to a table he doesn't own without prefixing that reference with a schema owner. Without privileges, SNOW would not see the data, but even with the appropriate privileges granted, SNOW still needs to prefix the table name with the schema information if no synonym exists for the table in that schema. If no synonym exists, SNOW still must prefix references to EMP with REED, as in REED.EMP. Tables don't have passwords like databases do, so that choice is patently incorrect. (Topic 12.3)
45.	D. Oracle returns an error. Explanation In this situation, you cannot use the order by clause in a subquery. Oracle will return an error. Thus, no data will be returned from any table, so all of the other choices are wrong. (Topic 6.4)
46.	B. select to_char(nvl(sqrt(59483), 0)) from dual; Explanation The select to_char(nvl(sqrt(59483), 0)) from dual; statement is a valid statement. The select nvl(sqrt(59483)) from dual; statement does not pass enough parameters to the nvl() function. The select TO_CHAR(nvl(sqrt(59483), 'VALID')) from dual; statement breaks the rule in nvl() that states that both parameters passed into the function must be the same datatype. The select (to_char(nvl(sqrt(59483), '0')) from dual; statement is missing a matching closing parenthesis after '0'. (Topic 1.2)
47.	C. column PLAY_TABLE heading "My Plays and Authors" Explanation The heading clause to the column command in SQL*Plus acts in the same way as a column alias does in SQL—it modifies the output of the query to use a heading of your design. Despite its similarity, however, the heading clause is not the same as an alias in SQL. Thus, both the choice identifying the alias clause and the choice using the as keyword are incorrect. The choice containing the format clause should be easy to eliminate. (Topic 1.3)
48.	C. BABS Explanation Because BABS is not listed in the contents of the MY_USERS table, JONES will not see BABS when he queries the view. Choices A, B, and D all identify users who are listed in the MY_USERS view, and thus will be seen by JONES when he queries BASEBALL_TEAM_VW. (Topic 11.3)
49.	C. Use the set long command Explanation The set long command is used for adjusting the SQL*Plus output produced from data dictionary views with long columns. The view containing view creation code contains a long column, so adjusting how SQL*Plus displays that information will solve the issue. Choice A is incorrect because you cannot change the size of a dictionary view. Choice D is incorrect because the alter user statement does not somehow adjust view alottments. Finally, the NLS_DATE_FORMAT controls date formatting, not long columns. (Topic 1.3)
50.	D. group by COW_NAME; Explanation When the column clause contains a mix of group and non-group expressions, all non-group expressions must be listed to the left of the group expression, and all non-group expressions listed in the column clause must also be listed in the group by clause. Otherwise, Oracle returns an error indicating a problem with the group by expression, making choice D the correct answer. (Topic 5.3)
51.	D. group by COW_NAME; Explanation The mod() function is not a group function, so no group by clause is necessary in this query. Thus, choice D is the correct answer. (Topic 5.3)
52.	B. select A.LASTNAME, B.DEPT_NO from EMP A, (select EMPID, DEPT_NO from DEPT) B where A.EMPID = B.EMPID; Explanation An inline view is a subquery appearing in the table clause of your SQL query. Thus, because choice B is the only query showing a subquery in the from clause, it is the only query that uses an inline view and therefore is the correct answer. Choices A and C are incorrect because the subqueries are used in the where clause. Choice D is incorrect because the subquery in the column clause is really a static text string that will be listed once for as many rows as there are in the USER_TABLES view. (Topic 11.5)
53.	A. drop index Explanation An index associated with a constraint cannot be dropped using the drop index command, making choice A the correct answer. The other statements all indicate methods that can be used for removing indexes associated with constraints. Constraints will be removed when the table is removed or whenever you drop the constraint. (Topic 12.2)
54.	C. Every primary key column value must be unique and none can be NULL. Explanation Choice C is the only answer that encapsulates all the restrictions on data in primary key columns, so it is therefore the correct answer. Although choices A and B each identify some of the restrictions on primary key constraints, neither choice alone is the correct answer. Choice D does not identify a restriction on data in primary key columns so you can discard that choice immediately. (Topic 10.1)
55.	C. Table FOOBAR has no primary key, and therefore no index on MOO. Explanation Because no primary key was defined when you created the FOOBAR table, the later query on that table cannot use any unique index on the MOO column to speed access to data in your FOOBAR table. Thus, choice C is the correct answer. Choice A is incorrect because you have no information indicating a primary key index is present, whereas choice B is incorrect because nothing states that a query takes longer to resolve when operating on a table rather than a view—if anything, the opposite is true. Finally, choice D is incorrect because again, you have no information indicating that the table was dropped and recreated. (Topic 10.1)
56.	D. USER_INDEXES, indexes with the same name as the constraint Explanation The object associated with the table and the primary key is an index, and the USER_INDEXES dictionary view contains a listing of all indexes owned by the current user. You can query this dictionary view to find indexes with the same name as the constraint identified in the question text, making choice D the correct answer. Choice A is incorrect because USER_SEQUENCES contains information about sequences, which wasn't a topic for the question. USER_TABLES contains information about tables, which also wasn't a topic for the question, making choice B incorrect as well. Finally, USER_IND_COLUMNS contains information about indexed columns, which aren't objects separate from the table or constraint, making choice C incorrect as well. Oracle has been de-emphasizing dictionary view questions on this exam of late, so consider it a bonus if you knew the correct answer. (Topic 12.2)
57.	B. To improve performance on columns with few unique values Explanation Bitmap indexes are designed to improve performance on columns with few unique values. Traditional or B-tree indexes are designed for indexes with many or all unique values, making choices A and C incorrect. Sequences do not need an index, making choice D incorrect. A question about bitmap indexes may or may not be on the OCP exam, so consider it a bonus if you knew the correct answer. (Topic 12.2)
58.	A. update employee set salary = 5000 where empid = 59694; Explanation Choice A correctly identifies a statement that will correspond to the requirements of the statement, whereby the salary information for employee 59694 is changed. Choice B is incorrect because the update statement changes EMPID column information, not SALARY. Choice C is incorrect because the statement changes LASTNAME column information, not SALARY. Finally, choice D is incorrect because although SALARY information is being changed, it is being done based on LASTNAME information, not EMPID information. (Topic 8.3)
59.	C. grant update on emp to timothy with grant option; Explanation The statement offered by choice C is used for giving object privileges to other users along with the ability to grant that privilege to others. Choice D is incorrect because with admin option is used for giving administrative ability over system privileges with respect to other users. Choice A is not a properly formulated grant command for object privileges, making it an incorrect answer. Choice B is incorrect because although the privilege itself was given to TIMOTHY, administrative ability was not. (Topic 13.3)
60.	D. REED and SNOW only Explanation SNOW does not lose the ability to create sessions with Oracle just because REED revoked the privilege from MANN because Oracle does not cascade revocation of system privileges. Thus, REED still has the privilege because no one revoked it from her, and SNOW still has the privilege because Oracle didn't cascade the revocation. (Topic 13.1)