OCP Exam 3: Introduction to SQL

1.	You issue the following select statement in Oracle: SQL> select e.empno, e.ename, d.loc from emp e, dept d 3 where e.deptno = d.deptno 4 and substr(e.ename,1,1) = 'S'; Which of the following statements identifies an ANSI-compliant equivalent statement usable on the Oracle database? select empno, ename, loc from emp join dept on emp.deptno = dept.deptno where substr(emp.ename,1,1) = 'S'; select empno, ename, loc from emp, dept on emp.deptno = dept.deptno where substr(emp.ename,1,1) = 'S'; select empno, ename, loc from emp join dept where emp.deptno = dept.deptno and substr(emp.ename,1,1) = 'S'; select empno, ename, loc from emp join dept on emp.deptno = dept.deptno and substr(emp.ename,1,1) = 'S';
2.	You are trying to manipulate data on the Oracle database. Which of the following choices identifies a capacity of select statements in Oracle and does not require the use of a subquery? You can change data in Oracle using select statements. You can remove data from Oracle using select statements. You can create a table with the contents of another using select statements. You can truncate tables using select statements.
3.	You issue a query in the Oracle database. Which of the following choices does not identify a component of your query if you want the query to execute a mathematical operation on user-defined static expressions? Column clause Table clause The DUAL table The where clause
4.	You are manipulating data in Oracle. Which of the following is not a SQL command? select * from dual; set define ? update emp set empno = 6543 where ename = 'SMITHERS'; create table employees (empid varchar2(10) primary key);
5.	You are defining SQL queries in Oracle. Which of the following database objects cannot be referenced directly from a select statement? Tables Sequences Indexes Views
6.	You need to filter return data from your query on the PROFITS table according to the PRODUCT_NAME column. Which of the following clauses in your SQL query will contain reference to the appropriate filter criteria? select from where having
7.	A partial listing of output from the PROFITS table is shown in the following code block: PRODUCT_NAME PRODUCT_TYPE QTR_END_DATE PROFIT ------------ ------------ ------------ ------------- BARNEY DOLL TOY 31-MAR-2001 6575430.30 GAS GRILL APPLIANCE 31-MAR-2001 1234023.88 PENCIL OFFICE 30-JUN-2001 34039.99 Which of the following choices identifies the proper setup of a where clause for a query that calculates the total profits for all appliances sold in the six-month period from January 1 to June 30, 2001? where product_name = 'GAS GRILL' and qtr_end_date between '01-JAN-2001' and '01-JUL-2001'; where product_type = 'APPLIANCE' and product_name = 'GAS GRILL' and qtr_end_date = '31-JAN-2001' or '30-JUN-2001'; where product_type = 'APPLIANCE' and qtr_end_date between '01-JAN-2001' and '01-JUL-2001'; where product_name = 'GAS GRILL' and qtr_end_date = '01-JAN-2001' or '30-JUN-2001'; Use the contents of the EMP table shown in the following code block to answer the next eight questions: EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --------- -------- --------- ----- --------- ---- ---- ------ 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10
8.	Which of the following choices identifies the value that would be returned from the following query: select sum(sal) + sum(comm) from emp where job = 'ANALYST' or ename like 'J%'? 6000 9925 9975 NULL
9.	Which of the following choices identifies the value that would be returned from the following query: select count(mgr) from emp where deptno = 10? One Two Three NULL
10.	Which of the following choices identifies the value returned if you issued the following query: select count(*) from emp where mgr = 7700-2? Five Six Seven NULL
11.	Which of the following choices identifies the third employee listed from the top of the output from the following SQL command: select ename, sal from emp where job = 'SALESMAN' order by empno desc? ALLEN MARTIN TURNER WARD
12.	Which of the following choices identifies the third employee listed from the top in the output generated from the following SQL command: select ename, job from emp where job = 'SALESMAN' order by 1 desc? ALLEN MARTIN TURNER WARD
13.	Which of the following choices identifies the value returned by Oracle when you issue the following query: select substr(job, 1,3) from emp where ename like upper('_ _ar%')? ANA CLE MAN SAL
14.	Which of the following choices identifies the value returned by Oracle when you issue: select trunc(months_between(min(hiredate), max(hiredate))) from emp? 24 25 -24 -25
15.	Which of the following choices identify the value returned by Oracle when you issue the following query: select * from emp where hiredate > '23-JAN-82'? (Choose two.) ADAMS MILLER SCOTT SMITH
16.	A table called TEST contains two columns: TESTCOL, defined as a NUMBER(10) datatype; and TESTCOL_2, defined as a VARCHAR2(10) datatype. You issue the following statement on Oracle: insert into test (testcol, testcol_2) values (null, 'FRANCIS'). You then issue the following query against that table: select nvl(testcol,'EMPTY') as testcol from test where testcol_2 = 'FRANCIS'. Which of the following choices correctly identifies the result? Oracle returns zero as the result. Oracle returns EMPTY as the result. Oracle returns NULL as the result. Oracle returns an error as the result.
17.	You want to obtain data from the ORDERS table, which contains three columns: CUSTOMER, ORDER_DATE, and ORDER_AMT. Which of the following choices identifies how you would formulate the where clause in a query against the ORDERS table when you want to see orders for customer LESLIE that exceed 2700? where customer = 'LESLIE'; where customer = 'LESLIE' and order_amt < 2700; where customer = 'LESLIE' or order_amt > 2700; where customer = 'LESLIE' and order_amt > 2700;
18.	Use the following output to answer the question (assume that the information shown comes from the EMP table we've been using in the chapter): ENAME ---------- SMITH-dog- ALLEN-dog- WARD-dog-d JONES-dog- MARTIN-dog BLAKE-dog- CLARK-dog- SCOTT-dog- KING-dog-d TURNER-dog ADAMS-dog- JAMES-dog- FORD-dog-d MILLER-dog Which of the following choices identifies the SQL statement that produced this output? select trim(trailing '-dog' from ename) as ename from emp; select rpad(ename, 10, '-dog') as ename from emp; select substr(ename, 1, 10) as ename from emp; select lpad(ename, 10, '-dog') as ename from emp;
19.	Use the following code block to answer the question: SQL> select _____(-45) as output from dual; OUTPUT ------ -45 Which of the following choices identifies a single-row function that could not have produced this output? abs( ) ceil( ) floor( ) round( )
20.	For a certain row in a table, a VARCHAR2 column contains the value SMITHY, padded to the right with seven spaces by the application. When the length( ) function processes that column value, what will be the value returned? 6 13 30 60
21.	You issue the following statement in SQL*Plus: SQL> select ceil(-97.342), 2 floor(-97.342), 3 round(-97.342,0), 4 trunc(-97.342) 5 from dual; Which of the following choices identifies the function that will not return -97 as the result? ceil( ) floor( ) round( ) trunc( )
22.	You issue the following statement in SQL*Plus: SQL> select ceil(256.342), 2 floor(256.342), 3 round(256.342,0), 4 trunc(256.342) 5 from dual; Which of the following choices identifies the function that will not return 256 as the result? ceil( ) floor( ) round( ) trunc( )
23.	You issue the following query in Oracle: SQL> select months_between('15-MAR-83', '15-MAR-97') from dual; What will Oracle return? 14 -14 168 -168
24.	You want to use a format mask for date information in Oracle. In which of the following situations is this format mask not appropriate? to_date( ) to_char( ) alter session set nls_date_format to_number( )
25.	Two tables, PRODUCT and STORAGE_BOX, exist in a database. Individual products are listed in the table by unique ID number, product name, and the box a particular product is stored in. Individual storage boxes (identified by number) listed in the other table can contain many products, but each box can be found in only one location. Which of the following statements will correctly display the product ID, name, and box location of all widgets in this database? select p.prod_id, p.prod_name, b.box_loc from product p, storage_box b where p.prod_id = b.prod_id and prod_name = 'WIDGET'; select p.prod_id, p.prod_name, b.box_loc fromproduct p, storage_box b where prod_name = 'WIDGET'; select p.prod_id, p.prod_name, b.box_loc from product p, storage_box b where p.stor_box_num = b.stor_box_num and p.prod_name = 'WIDGET'; select prod_id, prod_name, box_loc from product, storage_box where stor_box_num = stor_box_num and prod_name = 'WIDGET';
26.	You want to join information from three tables as part of developing a report. The tables are EMP, DEPT, and SALGRADE. Only records corresponding to employee, department location, and salary range are required for employees in grades ten and higher for the organization. How many comparison operations are required for this query? Two Three Four Five
27.	You want to join the contents of two tables, PRODUCT and STORAGE, to list the location of all boxes containing widgets. PRODUCT has three columns: ID, NAME, and BOX#. STORAGE has two columns: BOX# and LOC. Which of the following choices will not give the desired result? select product.id, product.name, storage.loc from product, storage where product.box# = storage.box#; select product.id, product.name, storage.loc from product join storage on product.box# = storage.box#; select product.id, product.name, storage.loc from product natural join storage on product.box# = storage.box#; select product.id, product.name, storage.loc from product natural join storage;
28.	You are defining an outer join statement. Which of the following choices is true concerning outer join statements? Because outer join operations permit NULL values from one of the tables, you do not have to specify equality comparisons to join those tables. In outer join statements on tables A and B, you specify the right outer join when you want all of table A's rows, even when no corresponding record exists in table B. In outer join statements on tables A and B, you specify the left outer join when you want all of table B's rows, even when no corresponding record exists in table A. Even though outer join operations permit NULL values from one of the tables, you still need to specify equality comparisons to join those tables.
29.	Two tables, PRODUCT and STORAGE_BOX, exist in a database. Individual products are listed in the table by unique ID number, product name, and the box a particular product is stored in. Individual storage boxes (identified by number) listed in the other table can contain many products, but the box can be found in only one location. Which of the following statements will correctly display the product ID, name, and box location of all widgets in this database that have or have not been assigned to a storage box? select p.prod_id, p.prod_name, b.box_loc from product p left outer join storage_box b on p.stor_box_num = b.stor_box_num where p.prod_name = 'WIDGET'(+); select p.prod_id, p.prod_name, b.box_loc from product p left outer join storage_box b on p.stor_box_num = b.stor_box_num where p.prod_name = 'WIDGET'; select p.prod_id, p.prod_name, b.box_loc from product p right outer join storage_box b where b.stor_box_num = p.stor_box_num (+) and p.prod_name = 'WIDGET'; select p.prod_id, p.prod_name, b.box_loc from product p full outer join storage_box b on p.stor_box_num = b.stor_box_num where b.stor_box_num is NULL;
30.	You issue the following command in Oracle: SQL> select e.ename, a.street_address, a.city, a.state, a.post_code from emp e, addr a 3 where e.empno = a.empno (+) 4 and a.state = 'TEXAS'; Which of the following choices shows the ANSI/ISO equivalent statement? select e.ename, a.street_address, a.city, a.state, a.post_code from emp e outer join addr a on e.empno = a.empno where a.state = 'TEXAS'; select e.ename, a.street_address, a.city, a.state, a.post_code from emp e left outer join addr a on e.empno = a.empno where a.state = 'TEXAS'; select e.ename, a.street_address, a.city, a.state, a.post_code from emp e right outer join addr a on e.empno = a.empno where a.state = 'TEXAS'; select e.ename, a.street_address, a.city, a.state, a.post_code from emp e right outer join addr a where e.empno = a.empno (+) and a.state = 'TEXAS';
31.	Examine the following output from SQL*Plus: PRODUCT.ID PRODUCT.NAME BOX.LOCATION ---------- ------------ ------------ 578-X WIDGET IDAHO TENNESSEE 456-Y WIDGET Which of the following choices identifies the type of query that likely produced this result? Full outer join Left outer join Right outer join Equijoin
32.	You are developing a query on the PROFITS table, which stores profit information by company region, product type, and quarterly time period. Which of the following SQL statements will display a cross-tabulation of output showing profits by region, product type, and time period? select region, prod_type, time, sum(profit) from profits group by region, prod_type, time; select region, prod_type, time from profits group by rollup (region, prod_type, time); select region, prod_type, time from profits group by cube (region, prod_type, time); select region, prod_type, time, sum(profit) from profits group by cube (region, prod_type, time);
33.	Which of the following choices identifies a group by query that will not result in an error from Oracle when run against the database? select deptno, job, sum(sal) from emp group by job, deptno; select sum(sal), deptno, job from emp group by job, deptno; select deptno, job, sum(sal) from emp; select deptno, sum(sal), job from emp group by job, deptno;
34.	Review the following SQL statement: SQL> select a.deptno, a.job, b.loc, sum(a.sal) 2 from emp a, dept b 3 where a.deptno = b.deptno 4 group by a.deptno, a.job, b.loc 5 order by sum(a.sal); Which of the following choices identifies the column upon which the order of output from this query will be returned? A.DEPTNO A.JOB B.LOC sum(A.SAL)
35.	You are developing a query on the PROFITS table, which stores profit information by company region, product type, and quarterly time period. Which of the following choices identifies a query that will obtain the average profits greater than $100,000 by product type, region, and time period? select region, prod_type, period, avg(profit) from profits where avg(profit) > 100000 group by region, prod_type, period; select region, prod_type, period, avg(profit) from profits where avg(profit) > 100000 order by region, prod_type, period; select region, prod_type, period, avg(profit) from profits group by region, prod_type, period having avg(profit) > 100000; select region, prod_type, period, avg(profit) from profits group by region, prod_type, period having avg(profit) < 100000;
36.	The company has an employee expense application with two tables. One table, called EMP, contains all employee data. The other, called EXPENSE, contains expense vouchers submitted by every employee in the company. Which of the following queries will obtain the employee ID and name for those employees who have submitted expenses whose total value exceeds their salary? select e.empno, e.ename from emp e where e.sal < (select sum(x.vouch_amt) from expense x) and x.empno = e.empno; select e.empno, e.ename from emp e where e.sal < (select x.vouch_amt from expense x where x.empno = e.empno); select e.empno, e.ename from emp e where sal < (select sum(x.vouch_amt) from expense x where x.empno = e.empno); select e.empno, e.ename from emp e where exists (select sum(x.vouch_amt) from expense x where x.empno = e.empno);
37.	Take a look at the following statement: SQL> select ename 2 from emp 3 where empno in 4 ( select empno 5 from expense 6 where vouch_amt > 10000); Which of the following choices identifies a SQL statement that will produce the same output as the preceding statement, rewritten to use the exists operator? select e.ename from emp e where exists (select x.empno from expense x where x.vouch_amt > 10000) and x.empno = e.empno; select e.ename from emp e where exists (select x.empno from expense x where x.vouch_amt > 10000 and x.empno = e.empno); select e.ename from emp e where x.empno = e.empno and exists (select x.empno from expense x where x.vouch_amt > 10000); select e.ename from emp e, expense x where x.empno = e.empno and x.vouch_amt > 10000 and exists (select x.empno from expense x where x.vouch_amt > 10000);
38.	Use the following code block to answer the question: SQL> select deptno, job, avg(sal) 2 from emp 3 group by deptno, job 4 having avg(sal) >   5 ( select sal 6 from emp 7 where ename = 'MARTIN'); Which of the following choices identifies the type of subquery used in the preceding statement? A single-row subquery A multirow subquery A from clause subquery A multicolumn subquery
39.	The company's sales database has two tables. The first, PROFITS, stores the amount of profit made on products sold by the different corporate regions in different quarters. The second, REGIONS, stores the name of each departmental region, the headquarter location for that region, and the name of the region's vice president. Which of the following queries will obtain total profits on toys for regions headed by SMITHERS, FUJIMORI, and LAKKARAJU? select sum(profit) from profits where region in ( select region from regions where reg_head in ('SMITHERS', 'FUJIMORI', 'LAKKARAJU')) and product = 'TOYS'; select sum(profit) from profits where region in ( select region from regions where reg_head in ('SMITHERS', 'FUJIMORI', 'LAKKARAJU') and product = 'TOYS'); select sum(profit) from profits where region = ( select region from regions where reg_head in ('SMITHERS', 'FUJIMORI', 'LAKKARAJU')) and product = 'TOYS'; select sum(profit) from profits where region in ( select region from regions where reg_head in ('SMITHERS', 'FUJIMORI', 'LAKKARAJU') and product = 'TOYS';
40.	The following code block shows a query containing a subquery: SQL> select dname, avg(sal) as dept_avg 2 from emp, dept 3 where emp.deptno = dept.deptno 4 group by dname having avg(sal) >   5 (select avg(sal) * 1/4 6 from emp, dept 7 where emp.deptno = dept.deptno) 8 order by avg(sal); Which of the following choices identifies a clause you might use to redefine this query to remove redundancy of group function execution in the subquery and in the main query? group by order by with having
41.	Use the output in the code block to answer the following question: SQL> select e.deptno, e.ename, e.job, e.sal 2 from emp e 3 where e.sal = 4 (select max(e2.sal) 5 from emp e2 6* where nvl(e.deptno,99) = nvl(e2.deptno, 99)); DEPTNO ENAME JOB SAL --------- ---------- --------- --------- 30 BLAKE MANAGER 2850 10 CLARK MANAGER 2450 20 SCOTT ANALYST 3000 KING PRESIDENT 5000 20 FORD ANALYST 3000 In order to display a value of 99 in the DEPTNO column in the preceding return set, which of the following SQL statements might be appropriate? select nvl(e.deptno, 99), e.ename, e.job, e.sal from emp e where (e.deptno, e.sal) =(select max(e2.sal) from emp e2 where nvl(e.deptno, 99) = nvl(e2.deptno, 99)); select nvl(e.deptno, 99), 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)); select nvl(e.deptno,99), e.ename, e.job, e.sal from emp e where (e.deptno, e.sal) =(select e2.deptno, max(e2.sal) from emp e2 where nvl(e.deptno, 99) = nvl(e2.deptno, 99)); select nvl(e.deptno, 99), e.ename, e.job, e.sal from emp e where (e.deptno, e.sal) =(select e2.deptno, max(e2.sal) from emp e2 where nvl(e.deptno, 99) = nvl(e2.deptno, 99) group by e2.deptno);
42.	Your company's sales database contains one table, PROFITS, which stores profits listed by product name, sales region, and quarterly time period. If you wanted to obtain a listing of the five best-selling products in company history, which of the following SQL statements would you use? select p.prod_name, p.profit from (select prod_name, profit from profits order by profit desc) where rownum <= 5; select p.prod_name, p.profit from (select prod_name, sum(profit) from profits group by prod_name order by sum(profit) desc) subq where p.prod_name = subq.prod_name; select prod_name, profit from (select prod_name, sum(profit) from profits group by prod_name order by sum(profit) desc) where rownum <=5; select prod_name, profit from (select prod_name, sum(profit) from profits order by sum(profit) desc) where rownum <=5;
43.	Your sales database consists of one table, PROFITS, which lists profits for every product type the company sells listed by quarter and by sales region. You need to develop a report that users can run interactively to show them the profits on toys for a given quarter. You have concerns about the users of this report because they have frequently complained about the readability and usability of your reports. Which of the following choices shows the contents of the script you should use for your report? select profit from profits where prod_type = 'TOYS' and time_period = '&v_period'; define v_periodselect profit from profits where prod_type = 'TOYS' and time_period = '&v_period'; accept v_period prompt 'Enter the time period => 'select profit from profits where prod_type = 'TOYS' and time_period = '&v_period'; accept v_periodselect profit from profits where prod_type = 'TOYS' and time_period = '&v_period';
44.	Review the following code block containing the contents of a script called dates.sql: accept v_hiredate prompt 'enter hire date => ' select empno, ename, job from emp where trunc(hiredate) = trunc('&v_hiredate'); Which of the following aspects of the script must be changed in order for the script to function properly? Variable v_hiredate must be changed to accept DATE information. The trunc( ) function in the query should be eliminated. The prompt clause in the accept command is unnecessary. Nothing; the script will work fine as is.
45.	You are creating tables in the Oracle database. Which of the following statements identifies a table-creation statement that is not valid? create table cats (c_name varchar2(10), c_weight number, c_owner varchar2(10)); create table my_cats as select * from cats where owner = 'ME'; create global temporary table temp_cats (c_name varchar2(10), c_weight number, c_owner varchar2(10)); create table cats-over-5-lbs as select c_name, c_weight from cats where c_weight > 5;
46.	Your attempt to create a table in Oracle results in the following error: ORA-00955 - name is already used by existing object. Which of the following choices does not identify an appropriate correction for this situation? Create the object as a different user. Drop the existing object with the same name. Change the column names in the object being created. Rename the existing object.
47.	The PROFITS column inside the SALES table is declared as NUMBER(10,2). Which of the following values cannot be stored in that column? 5392845.324 871039453.1 75439289.34 60079829.25
48.	Employee KING was hired on November 17, 1981. You issue the following query on your Oracle database: select vsize(hiredate) from emp where ename = 'KING;. Which of the following choices identifies the value returned? 4 7 9 17
49.	You define the PRODUCT_NAME column in your SALES table to be CHAR(40). Later, you add one row to this table with the value CAT_TOYS for PRODUCT_NAME. You then issue the following command: select vsize(product_name) from sales. Which of the following choices best identifies the value returned? 8 12 40 4000
50.	The JOB table contains three columns: JOB_NAME, JOB_DESC, and JOB_WAGE. You insert a new row into the JOB_DESC table using the following command: SQL> insert into job (job_name, job_desc) 2 values ('LACKEY','MAKES COFFEE'); Later, you query the table, and receive the following result: SQL> select * from job where job_name = 'LACKEY'; JOB_NAME JOB_DESC JOB_WAGE --------- ------------ -------- LACKEY MAKES COFFEE 35 Which of the following choices identifies how JOB_WAGE was populated with data? The row for LACKEY in the JOB table already existed with JOB_WAGE set to 35. A default clause on the JOB_WAGE column defined when the table was created specified the value when the row was inserted. The values clause in the insert statement contained a hidden value that was added when the row was added. The only possible explanation is that a later update statement issued against the JOB table added the JOB_WAGE value.
51.	You want to reduce the size of a non-NULL NUMBER(10) column to NUMBER(6). Which of the following steps must be completed after the appropriate alter table command is issued? Copy column records to a temporary storage location. Set the NUMBER column to NULL for all rows. Create a temporary location for NUMBER data. Copy column records from the temporary location back to the main table.
52.	You just issued the following statement: alter table sales drop column profit;. Which of the following choices identifies when the column will actually be removed from Oracle? Immediately following statement execution After the alter table drop unused columns command is issued After the alter table set unused column command is issued After the alter table modify command is issued
53.	You want to increase the size of a non-NULL VARCHAR2(5) column to VARCHAR2(10). Which of the following steps must be accomplished after executing the appropriate alter table command? Set the VARCHAR2 column to NULL for all rows. Create a temporary location for VARCHAR2 data. Copy the column records from the temporary location back to the main table. Nothing; the statement is executed automatically.
54.	You want to increase the size of the PRODUCT_TYPE column, declared as a VARCHAR(5) column, to VARCHAR2(10) in the SALES table. Which of the following commands is useful for this purpose? alter table sales add (product_type varchar2(10)); alter table sales modify product_type varchar2(10)); alter table sales set unused column product_type varchar2(10)); alter table sales drop column product_type;
55.	You drop a table in an Oracle database that is the parent table in a parent-child data relationship. Which of the following objects will not be dropped when you drop the parent table? Associated constraints The child column Associated triggers Associated indexes
56.	The PROFITS table in your database has a primary key on the PRODUCT_NAME and SALE_PERIOD columns. Which of the following statements could not have been used to define this primary key? create table profits (product_name varchar2(10), sale_period varchar2(10), profit number, constraint pk_profits_01 primary key (product_name, sale_period)); alter table profits add constraint pk_profits_01 primary key (product_name, sale_period) deferrable initially immediate; alter table profits add (constraint pk_profits_01 primary key (product_name, sale_period)); create table profits (product_name varchar2(10) primary key, sale_period varchar2(10) primary key, profit number);
57.	You are defining check constraints on your SALES table, which contains two columns: PRODUCT_TYPE and UNIT_SALES. Which of the following choices identify a properly defined check constraint? (Choose two.) alter table sales add constraint ck_sales_01 check (product_type in ('TOYS', 'HOT DOGS', 'PALM PILOTS')); alter table sales add constraint ck_sales_01 check (product_type in (select product_type from valid_products)); alter table sales modify (product_type varchar2(30) check (product_type in ('TOYS', 'HOT DOGS', 'PALM PILOTS'))); alter table sales add (product_name varchar2(30) check (product_name <> 'AK-47'));
58.	Use the following code block to answer the question: SQL> create table prices 2 ( product_name varchar2(30), 3 price number(10,4)); Table created. SQL> alter table prices add constraint pk_prices_01 2 primary key (product_name); Table altered. SQL> insert into prices values ('DOGGY', 499.99); 1 row created. SQL> alter table prices disable constraint pk_prices_01; Table altered. SQL> insert into prices values ('DOGGY', 449.99); 1 row created. SQL> alter table prices enable novalidate constraint pk_prices_01; What happens next? Existing entries are checked for violations, PK_PRICES_01 is enabled, and Oracle checks subsequent entries for violations immediately. Existing entries are checked for violations, PK_PRICES_01 is not enabled, and Oracle does not check subsequent entries for violations immediately. Existing entries are not checked for violations, PK_PRICES_01 is enabled, and Oracle checks subsequent entries for violations immediately. Existing entries are checked for violations, PK_PRICES_01 is not enabled, Oracle checks subsequent entries for violations immediately.
59.	Your attempt to disable a constraint yields the following error: ORA-02297: cannot disable constraint-dependencies exist. Which of the following types of constraints is likely causing interference with your disablement of this one? Check constraint Not NULL constraint Foreign key constraint Unique constraint
60.	You are disabling a not NULL constraint on the UNIT_PRICE column in the SALES table. Which of the following choices identifies the correct statement for performing this action? alter table sales modify (unit_prices null); alter table sales modify (unit_prices not null); alter table sales add (unit_prices null); alter table sales add (unit_prices not null);

Answers

1.	A. select empno, ename, loc from emp join dept on emp.deptno = dept.deptno where substr(emp.ename,1,1) = 'S'; Explanation Choice A identifies the correct ANSI-compliant syntax for setup of table joins in Oracle9i. Choice B is incorrect because the on keyword is used only in the presence of the join keyword, which is not present in the syntax of that choice. Choice C is incorrect because the join condition must be identified as part of the on clause when the join keyword is present. Finally, choice D is incorrect because determining whether the first character in the ENAME column is S is not a join condition—it is a filtering condition. Thus, that condition should not be included in the on clause, but rather in a where clause. (Topic 4.1)
2.	C. You can create a table with the CONTENTS of another using select statements. Explanation Choice C is correct because the create table as select command involves a select statement that is not used as a subquery. Choices A and B are incorrect because the update and delete commands must include a subquery in order for you to change or remove data with the use of a select command. Finally, it is not possible to include a subquery in a truncate command. (Topic 6.1)
3.	D. The where clause Explanation If you are using a select statement to perform a mathematical operation on static expressions, you do not need to use the where clause. Choice A is incorrect because your query must include the static expressions and the math operation in the column clause. Choice B is incorrect because all SQL statements need to include a from clause. Finally, choice C is incorrect because your from clause in this scenario will reference the DUAL table. (Topic 2.1)
4.	B. set define ? Explanation The set command is not a SQL command in Oracle. Rather, it is an Oracle tool command that operates in a tool-specific way. For SQL*Plus, the set command enables you to define aspects of your SQL operating environment. Choices A and C are incorrect because the select and update commands are part of SQL data manipulation language. Choice D is incorrect because the create table command is part of SQL data definition language. (Topic 1.3)
5.	C. Indexes Explanation You cannot reference an index directly from a SQL command, but Oracle may use one behind the scenes to improve performance under certain conditions. Choice A is incorrect because the select command enables you to query Oracle for the contents of a table. You can also reference sequences directly in select commands, making choice B incorrect as well. Finally, choice D is incorrect because you can also reference views directly using SQL select commands. (Topic 12.2)
6.	C. where Explanation Filter conditions are always properly placed in the where clause of your SQL query. Choice A is incorrect because the columns you want to see in the output are listed in the select clause. Choice B is incorrect because the tables you want to query are listed in the from clause. Finally, choice D is incorrect because the having clause acts as an additional filter usually when you want to see only the output that conforms to filter conditions when a group by clause is used. (Topic 2.1)
7.	C. where product_type = 'APPLIANCE' and qtr_end_date between '01-JAN-2001' and '01-JUL-2001'; Explanation Because you want to get the total profits for all appliances for the first six months of 2001, choice C is your best answer because it filters based on that criteria in the columns PRODUCT_TYPE and QTR_END_DATE. Choice A is incorrect because that where clause filters on the PRODUCT_NAME column, and although gas grills are certainly appliances, other appliances could be listed in the PROFITS table that are not gas grills that we would want to include in the total profits calculation. Choice B is incorrect because again, not all appliances are gas grills. Furthermore, the filter condition on the QTR_END_DATE column is malformed. Finally, choice D is incorrect because no filter condition appears on the PRODUCT_TYPE column and the filter condition on the QTR_END_DATE column is malformed. (Topic 2.1)
8.	D. NULL Explanation This is a difficult question that tests your knowledge of how group functions react when NULL information is given. Take another look at the contents of the COMM column in the EMP table. Many rows in that table contain NULL values for the COMM column. When NULL data is fed into group functions, the result is always NULL. Thus, although the sum of salaries and commissions for analysts or employees whose names begin with the letter J is 9925, Oracle returns NULL as the answer. (Topic 5.2)
9.	B. Two Explanation Again, this difficult question tests your knowledge on how group functions react when NULL information is given. Although three employees have a value of ten in the DEPTNO column, Oracle does not count KING's record because the MGR column for KING's record contains a NULL value. Thus, choice C is incorrect. (Topic 5.2)
10.	A. Five Explanation 7700 minus 2 equals 7698. Five employees have a value in the MGR column of 7698, so the answer is choice A. All other choices are incorrect. Be sure you understand that you can include math operations on static expressions in the filter comparisons of your SQL queries. (Topic 1.3)
11.	D. WARD Explanation Take a moment to reread the question text, and attempt to rephrase what the question is asking for in your own words. First, this question asks you to identify the employees who are salesmen. Second, it asks you to list the output in descending order based on EMPNO, meaning that the employee with the highest value for EMPNO gets listed first, followed by the second highest, and so on. The listed output will be TURNER, MARTIN, WARD, and ALLEN, in that order. Finally, the question asks you to identify the employee listed third from the top of that list. Thus, choice D is the correct answer. (Topic 2.2)
12.	B. MARTIN Explanation Take a moment to reread the question text, and try to rephrase the question in your own words. First, this question asks you to figure out which employees are salesmen. Second, the question asks you to list the output in descending order based on the values in the first column in the output: the ENAME column. The listed output will be WARD, TURNER, MARTIN, and ALLEN, in that order. Finally, the question asks you to identify the employee listed third from the top of that list. Thus, choice B is the correct answer. (Topic 2.2)
13.	C. MAN Explanation Recall that when the like keyword is used, Oracle will use the wildcard characters _ and % to identify data via pattern matching. The _ character indicates that this single letter in the text string can be anything, whereas the % character indicates that the prior or subsequent text in the string can be anything. In this case, we are looking for the row whose value for ENAME can contain any letter for the first two characters, followed by AR, followed by anything. Only one name in the list matches this criteria—CLARK. The value in the JOB column for CLARK is MANAGER, and the first three characters of that text string are MAN. Thus, choice C is correct. (Topic 3.2)
14.	C. -24 Explanation The result Oracle produces when the query in this question is issued is -24, so choice C is correct. The result will be negative because Oracle subtracts the greater, or more recent hire date from the lesser, more distant hire date. Thus, choices A and B are incorrect. Choice D is incorrect because the trunc() function truncates the value to the right of the decimal point and returns the value -24, not -25. (Topic 3.2)
15.	A and C. ADAMS and SCOTT Explanation Oracle will only return rows where the value in the HIREDATE column is greater or more recent than January 23, 1982. Only two rows contain values meeting this criterion. They are shown in choices A and C. Choices B and D are both incorrect because the hire dates shown in the HIREDATE column for those two rows are both less recent than January 23, 1982. (Topic 1.2)
16.	D. Oracle returns an error as the result. Explanation Any substitution value specified using the nvl() function must be the same datatype as the column specified in the call to that function. Thus, because the TESTCOL column is of NUMBER datatype and 'EMPTY' is a text string, Oracle returns an error. Had we specified the TESTCOL_2 column instead of TESTCOL in our call to nvl(), then choice B would have been the correct answer. (Topic 3.2)
17.	D. where customer = 'LESLIE' and order_amt > 2700; Explanation To see order information for LESLIE where the total order exceeds $2,700, you must use the where clause identified in choice D. Choice A is incorrect because Oracle will indiscriminately return every order LESLIE has placed. Choice B is incorrect because Oracle will return orders LESLIE placed whose total is less than $2,700. Finally, choice C is incorrect because Oracle will return all orders that LESLIE placed and all orders for more than $2,700, even if LESLIE didn't place the order—this is too much information. (Topic 2.1)
18.	B. select rpad(ename, 10, '-dog') as ename from emp; Explanation Each employee name is padded to the right with the text -dog repeated over and over to fill up to ten places. Thus, we have to use the rpad() function. Choice A is incorrect because the trim() function removes text specified from the string you pass to the function. Choice C is incorrect because the substr() function returns a subset of text from a string you pass to the function. Finally, choice D is incorrect because the lpad() function pads to the left with the text you specify in order to fill up to a specific number of spaces. (Topic 3.2)
19.	A. abs() Explanation The abs() function returns the absolute value of a number, defined as the distance from zero of that number. Absolute values are always positive, so this function could not have produced the output identified in the question. Choices B, C, and D are all incorrect because the ceil(), floor(), and round() functions all could have produced the amount given. (Topic 3.2)
20.	B. 13 Explanation Blank spaces are still counted as part of the overall length of a text string. Because SMITHY has six characters and seven blank spaces, the length() function returns 13 for the length of this string. Choice A is incorrect because the value given doesn't take into account the blank spaces padded into the value stored in the column. Choice D would have been correct if the column had been declared as a CHAR(60) column rather than a VARCHAR2(60) column. Finally, you have no logical basis for arriving at 30 as the answer, so choice C can be eliminated immediately. (Topic 3.2)
21.	B. floor() Explanation The floor() function always rounds to the lower integer value when given a decimal to work with, and because -98 is smaller than -97.342, that is the result floor() returns. Choices A, C, and D are all incorrect because ceil(), round(), and trunc() all return -97 as the result of this query. (Topic 3.2)
22.	A. ceil() Explanation The ceil() function always rounds up to the next highest integer value when presented with a decimal, and because 257 is the next highest integer, that is what ceil() returns. Choices B, C, and D are all incorrect because those functions round down to the next lowest integer when given the decimal value in this question. (Topic 3.2)
23.	D. -168 Explanation Oracle returns -168 as the result of this query because 168 months are between March 15, 1983, and March 15, 1997. The result is negative because 1983 is a lesser value than 1997, and because Oracle subtracts the second value from the first, the result is negative. Had the dates passed into months_between () been reversed, choice C would have been the correct answer. Choices A and B are obviously incorrect. (Topic 3.2)
24.	D. to_number() Explanation Although date information is stored internally within Oracle as a number, you cannot use the to_number() function to convert the date to an actual number, thus making it unnecessary and wrong to use a date format mask with the to_number() function. Choices A, B, and C all indicate functions or situations where it is appropriate to use a date format mask. (Topic 3.3)
25.	C. select p.prod_d, p.prod_name, b.box_loc from product p, storage_box b where p.stor_box_num = b.stor_box_num and p.prod_name = 'WIDGET'; Explanation The join and filter conditions required for this query are represented correctly in choice C because the common column in both tables is joined and the filtering condition on PROD_NAME for all widgets is represented correctly. Choice A is incorrect because the PRODUCT and STORAGE_BOX tables do not share the PROD_ID column in common—they share the STOR_BOX_NUM column in common. Choice B is incorrect because no joining condition exists between the two tables, which will cause the output to form a Cartesian product. Finally, choice D is incorrect because the join condition refers to the STOR_BOX_NUM columns ambiguously in both tables. (Topic 4.1)
26.	B. Three Explanation You will need n - 1 join conditions, where n is the number of tables, plus a filter comparison, to obtain the result requested in this question. Because three tables are present, you will need two join conditions, plus the filter condition, for a total of three comparison operations. (Topic 4.1)
27.	C. select product.id, product.name, storage.loc from product natural join storage on product.box# = storage.box#; Explanation Natural joins do not require you to identify a join condition because Oracle assumes the join will be executed on the common columns in both tables. Thus, the on clause in choice C is unnecessary, and will result in an error. Choice A identifies the traditional way to define a join in Oracle, whereas choice B identifies the ANSI/ISO-compliant way to define a join in Oracle. Finally, choice D is incorrect because it properly defines how to specify a natural join in Oracle. (Topic 4.1)
28.	D. Even though outer join operations permit NULL values from one of the tables, you still need to specify equality comparisons to join those tables. Explanation Choice D is the logical opposite of choice A, and therefore is the correct answer. For this reason, choice A is also incorrect. Choice B is incorrect because you specify a left join in order to see all of table A's rows, even when no corresponding record exists in table B. Finally, choice C is incorrect because you specify a right join in order to see all of table B's rows, even when no corresponding record exists in table A. (Topic 4.2)
29.	B. select p.prod_id, p.prod_name, b.box_loc from produ ct p left outer jon storage_box b on p.stor_box_num = b.stor_box_num where p.prod_name = 'WIDGET'; Explanation Choice B is the only statement that properly defines an outer join meeting the criteria specified by the question. Choices A and C are easy targets to eliminate because they combine the ANSI/ISO outer join syntax with the traditional Oracle syntax—this is not permitted in the Oracle database, and Oracle will return an error if you attempt to do so. Choice D is incorrect because it specifies a full outer join of all rows in both tables that would otherwise not have been returned if an equijoin was executed. (Topic 4.2)
30.	C. select e.ename, a.street_address, a.city, a.state, a.post_code from emp e right outer join addr a on e.empno = a.empno where a.state = 'TEXAS'; Explanation Choice C is correct because the Oracle outer join operator is on the right side of the join comparison operation. This signifies that you must execute a right outer join using the ANSI/ISO syntax. Choice A is incorrect because the left or right keyword is missing from the statement. Choice B is incorrect because a left outer join will return records from EMP even where no corresponding record exists in ADDR. Choice D is incorrect because the traditional Oracle syntax for outer joins is incorrectly used in the same statement as the ANSI/ISO syntax. (Topic 4.2)
31.	A. Full outer join Explanation The output in this question clearly shows a situation where records from both PRODUCT and BOX are returned even when no corresponding record exists in either table. This is precisely the functionality provided by full outer joins. Choices B and C are incorrect because full outer joins return both the records from left and right outer joins. Finally, choice D is incorrect because an equijoin operation would not have returned the second or the third records listed in the output of the query. (Topic 4.2)
32.	D. select region, prod_type, time, sum(profit) from profits group by cube (region, prod_type, time); Explanation When you see a question indicating that the output of a query should produce a cross-tabulation of information, you should automatically know that the result requested is produced by the cube keyword. Choice A identifies a properly formed column clause containing a group function needed by the group by clause, but does not contain the needed cube keyword, so it is therefore incorrect. Choice B is incorrect because the column clause contains no group function. Choice C is incorrect for the same reason. (Topic 5.2)
33.	A. select deptno, job, sum(sal) from emp group by job, deptno; Explanation The statement in choice A is correct because it contains reference to a group function. The group by clause is correct even though the columns referenced are in a different order than they appear in the column clause of the query, which is acceptable so long as all non-group columns are listed before the group expression. Choice B is incorrect because the group expression is listed before the non-group expressions, which results in an error. Choice C is incorrect because no group by clause appears in the query, resulting in an error. Finally, choice D is incorrect for the same reason that choice B is incorrect—the group expression is listed to the left of a non-group expression, resulting in an error. (Topic 5.2)
34.	D. sum(A.SAL) Explanation When an order by clause is present, then Oracle lists output of the query according to the expression included in the order by clause. Otherwise, Oracle sorts the output by columns listed in the group by clause. Thus, choices A, B, and C are all incorrect. (Topic 5.1)
35.	C. select region, prod_type, period, avg(profit) from profits group by region, prod_type, period having avg(profit) > 100000; Explanation Choice C contains a well-formed column clause and group by clause, and uses the having clause to filter the results according to the criteria identified in the question. A careful read of choice D enables you to discard it, because the having clause in that choice filters results where average profits are less than $100,000, not greater than that amount. Choices A and B inappropriately attempt to use the where clause in order to filter unwanted data. (Topic 5.4)
36.	C. select e.empno, e.ename from emp e where sal < (select sum(x.vouch_amt) from expense x where x.empno = e.empno); Explanation To obtain the correct result, you will need to have Oracle calculate the total amount for all vouchers submitted by each employee listed in the EMP table, and only choice C correctly shows the correlated subquery required for the task. Choice A shows the sum of all vouchers in the EXPENSE table, an amount that will surely be greater than everyone's salary. Choice B is incorrect because it likely results in an error where a single-row subquery expected by the parent query returns more than one row. Choice D is incorrect because the exists operator is an inappropriate comparison between parent and subquery, given the need for a listing of employees whose expense vouchers total more than the employee's salary. (Topic 6.4)
37.	B. select e.ename from emp e where exists (select x.empno from expense x where x.vouch_amt > 10000 and x.empno = e.empno); Explanation The statement in choice B is correct because it properly uses the exists operator and forms the correlated subquery required to obtain the same result as the query shown in the question. Choice A is incorrect because the reference to the EXPENSE table in the parent query is out of scope. Choice C is incorrect for the same reason. Finally, the where clause forming a join and using the exists operation in choice D is redundant and therefore incorrect. (Topic 6.2)
38.	A. A single-row subquery Explanation The way to determine what kind of subquery is shown in this question is to determine how much output is produced by the query. Because only one record in the EMP table contains the value 'MARTIN' for ENAME, this subquery returns only one row and is therefore a single-row subquery. Choice B is incorrect for this reason. Choice C is incorrect because a from clause subquery is the same as an inline view, and no subquery is shown in the from clause of the query in this question. Finally, choice D is incorrect because the subquery does not contain reference to multiple columns. (Topic 6.3)
39.	A. select sum(profit) from profits where region in (select region from regions where reg_head in ('SMITHERS', 'FUJIMORI', 'LAKKARAJU')) and product = 'TOYS'; Explanation To obtain the correct result, you need a multiple-row subquery listing the regions headed by the region heads identified in the question. You also want a filtering condition on the PRODUCT column so only toys are returned. Choice A gives you all these things. Choice B is incorrect because the subquery incorrectly includes filter conditions operating on the REGIONS table that are meant to operate on the PROFITS table. Choice C is incorrect because the equality comparison between the REGION column and the values returned from the subquery will result in an error because the single-row subquery the parent query expects will in fact return more than one row. Finally, choice D is incorrect for the same reason as choice B, and because of a missing parenthesis at the end of the query. (Topic 6.1)
40.	C. with Explanation The with clause can be used to define a summary dataset to avoid redundancy in both the parent query and the subquery in Oracle. You would not use the group by clause for this purpose, eliminating choice A, nor would you use the order by clause for this purpose, eliminating choice B. Finally, you would not use the having clause for this purpose, eliminating choice D. (Topic 6.1)
41.	B. select nvl(e.deptno,99), 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)); Explanation Even SQL experts should find this question challenging. You are asked to determine which of these statements will return 99 in the DEPTNO column where the DEPTNO column is currently NULL in the dataset shown in the question. The most obvious answer is to find the query where the column clause contains a nvl() function on the DEPTNO column in the column clause. The problem is that all the choices contain references to the nvl() function in the column clause. Thus, you have to look beyond that factor for other clues, and your best bet is to try to find reasons to eliminate wrong answers rather than clues to find the right answer. Your first clue comes in choice A, where the parent query where clause tips you off that the multiple-column subquery is expected by the parent query but not provided, making that choice incorrect. Second, you should notice that in choice C, the group expression in the subquery necessitates a group by clause that is not provided in the subquery, making that choice incorrect as well. If you noticed these important clues, you've narrowed down your odds of guessing the right answer to 50/50. If you recall that 99 is not a value actually stored in the DEPTNO column and that group by clauses ignore NULL values by default, you should be able to eliminate choice D therefore leaving you with the correct answer, choice B. (Topic 6.1)
42.	C. select prod_name, profit from (select prod_name, sum(profit) from profits group by prod_name order by sum(profit) desc) where rownum <=5; Explanation Choice C correctly indicates the query containing an inline view that will produce a listing of the five best-selling products in the company's history. Choice A is incorrect because profits are not grouped by product name over the entire company's history in the inline view, but rather broken out by quarter, which will produce the wrong result. Choice B is incorrect because you do not need to join the contents of the PROFITS table with the inline view. Moreover, choice B does nothing to filter out the five top-selling products in company history. Finally, the inline view in choice D does not contain a group by clause, which will result in an error due to the presence of the group function in the inline view's column clause, making that choice incorrect. (Topic 6.3)
43.	C. accept v_period prompt 'Enter the time period => 'select profit from profits where prod_type = 'TOYS' and time_period = '&v_period'; Explanation Because the users have complained about readability and interaction according to the text of the question, you should base your answer on the choice that provides interactive definition of the time period and does so with a readable prompt. Choice C provides for these conditions. Choice D is close but does not provide for a readable prompt, so that choice is incorrect. Choice A is incorrect because the prompt Oracle will use does not address the readability requirement. Choice B is incorrect because the use of the define command limits interaction between SQL*Plus and the user. (Topic 7.1)
44.	A. Variable v_hiredate must be changed to accept DATE information. Explanation When variables are defined using the accept command in SQL*Plus, Oracle implicitly defines the datatype as CHAR. You must therefore specify the datatype when the variable is meant to accept non-CHAR information. Thus, choice A is the answer. Choice B is incorrect because the trunc() function is acceptable in this query. Choice C is incorrect because you needn't eliminate the prompt clause. Finally, the script does not work fine as is, so choice D is incorrect. (Topic 7.1)
45.	D. create table cats-over-5-lbs as select c_name, c_weight from cats where c_weight > 5; Explanation You cannot include hyphens or dashes as part of the name of any table in Oracle, thus making choice D the correct answer. Choice A is incorrect because that choice identifies a proper create table command. Choice B is incorrect because that choice identifies a proper create table as select command. Choice C is incorrect because that choice identifies a proper create global temporary table command. (Topic 9.2)
46.	C. Change the column names in the object being created. Explanation Changing the column names in a table when an object with that table's name already exists in Oracle will not prevent the same error from happening again. Instead, you should create the table as a different user, drop the existing object with the same name, or rename the existing object. Thus, choice C is correct, and all other choices are incorrect. (Topic 9.4)
47.	B. 871039453.1 Explanation Because the column was defined as datatype NUMBER(10,2), Oracle only permits numbers with up to eight places to the left of the decimal point to be placed into the column on this table. Thus, choice B is correct because that number has nine digits to the left of the decimal point. Choices C and D both identify proper numbers for this column. The number in choice A can also be stored in this column, but Oracle implicitly rounds off to the hundredths place to the right of the decimal point. (Topic 9.3)
48.	B. 7 Explanation The vsize() function returns a value of 7 for all columns of DATE datatype, no matter what the actual date appears to be. The information about KING's specific value for HIREDATE is only there to distract you. (Topic 3.3)
49.	C. 40 Explanation The vsize() function always returns a value equivalent to the size of the CHAR column regardless of the actual value stored in that column. This is because Oracle pads whatever value you specify for storage in a CHAR column with blanks up to the width of the CHAR column. Had the column been defined as a VARCHAR2 column, choice A would have been correct. All other choices should be easy to eliminate once you understand Oracle datatypes. (Topic 3.3)
50.	B. A default clause on the JOB_WAGE column defined when the table was created specified the value when the row was inserted. Explanation The purpose of a default clause is to specify a default value for a column whenever users don't explicitly identify the value to populate in the column for a row being added to the table. Choice A is incorrect because an insert statement will not populate column values for existing rows in the table. Choice C is incorrect because you cannot hide values in a values clause of an insert statement. Finally, choice D is incorrect because changing the JOB_WAGE column via a later update statement isn't the only way this value could have been added to the table. (Topic 9.3)
51.	D. Copy column records from the temporary location back to the main table. Explanation If you review all choices carefully before selecting an answer, you should notice that several steps of the process required to perform the task identified by the question are listed as choices. Its up to you to put those steps in order. The correct order is: C, A, B, D. The question then states that you have just completed the step identified in choice B. Thus, the only step left to perform is the one identified in choice D, the correct answer. (Topic 9.4)
52.	A. Immediately following statement execution Explanation Oracle drops the PROFIT column as soon as the statement identified in the answer choice is executed. Choices B and C identify the other way to remove columns from Oracle using the set unused column and drop unused columns clauses. However, we didn't see that option being used in the question, so those choices are incorrect. Finally, you cannot remove a column using the alter table modify command. (Topic 9.4)
53.	D. Nothing; the statement is executed automatically. Explanation Once you've issued the appropriate alter table statement to increase the size of a column, you are done with this task. Oracle will then automatically increase the size of the column. Choices A, B, and C identify steps appropriate for either decreasing the size of a column or altering that column's datatype, and therefore are incorrect. (Topic 9.4)
54.	B. alter table sales modify product_type varchar2(10); Explanation To increase the size of a column, you use the alter table modify command shown in choice B. Choice A is incorrect because that choice adds an extra column to the table unnecessarily. Choices C and D are incorrect because they identify steps in a process for removing a column from the table, which is not required by the question. (Topic 9.4)
55.	B. The child column Explanation Dropping a parent table will not remove the common column from the child table, making choice B the correct answer. Choice A is incorrect because associated constraints on the parent table are most certainly removed when you drop the parent table. Triggers and indexes on the parent table are dropped when the parent table is dropped as well, making choices C and D incorrect. (Topic 9.5)
56.	D. create table profits (product_name varchar2(10) primary key, sale_period varchar2(10) primary key, profit number); Explanation The primary key clause used for defining a primary key as a column constraint for both the PRODUCT_NAME and SALE_PERIOD columns as shown in choice D is not permitted in the Oracle database. Other choices show composite primary keys defined properly. (Topic 10.2)
57.	B. alter table sales add constraint ck_sales_01 check (product_type in (select product_type from valid_products)); Explanation You cannot use subqueries when defining valid values for check constraints—all values must be static. Choices A, C, and D all identify check constraints defined with static valid values and are permitted within the Oracle database. (Topic 10.2)
58.	B. Existing entries are checked for violations, PK_PRICES_01 is not enabled, and Oracle does not check subsequent entries for violations immediately. Explanation Oracle ignores the novalidate keyword because the constraint wasn't defined as deferrable in the question. Oracle thus checks existing entries for violations, and finds violations. PK_PRICES_01 will not be enabled, and Oracle does not check for future violations because the constraint isn't enabled. Choices A, C, and D are all incorrect for this reason. (Topic 10.2)
59.	C. Foreign key constraint Explanation Foreign key constraints in child tables create dependencies that later make it difficult to disable primary key constraints on parent tables. Choice A is incorrect because check constraints cannot create relationships between tables. Choice B is incorrect for the same reason. Finally, unique constraints also cannot create relationships between tables and is incorrect as well. (Topic 10.1)
60.	A. alter table sales modify (unit_prices null); Explanation The syntax for removing a not NULL constraint from a table is shown correctly in choice A. Choice B is incorrect because it defines a not NULL constraint. Choices C and D identify improper syntax for defining nullabililty in Oracle columns. (Topic 10.2)