 condition.

 E. An inner join relates rows within the same table.

Correct Answer: BC 
 
Question #59Topic 1
Which three statements about roles are true? (Choose three.)

 A. Roles are assigned to users using the ALTER USER statement.

 B. Privileges are assigned to a role using the GRANT statement.
 C. A role is a named group of related privileges that can only be assigned to a user.
 D. A single user can be assigned multiple roles.
 E. Privileges are assigned to a role using the ALTER ROLE statement.
 F. Roles are assigned to roles using the ALTER ROLE statement.
 G. A single role can be assigned to multiple users.
Correct Answer: BDG 
 
Question #60Topic 1
The INVOICE table has a QTY_SOLD column of data type NUMBER and an INVOICE_DATE
column of data type DATE.

NLS_DATE_FORMAT is set to DD-MON-RR.

Which two are true about data type conversions involving these columns in query
expressions? (Choose two.)

 A. invoice_date = '15-march-2019' : uses implicit conversion

 B. qty_sold BETWEEN '101' AND '110' : uses implicit conversion
 C. invoice_date > '01-02-2019' : uses implicit conversion
 D. qty_sold = '0554982' : requires explicit conversion
 E. CONCAT (qty_sold, invoice_date) : requires explicit conversion
Hide Solution    Discussion  
Correct Answer: AB 
 
Question #61Topic 1
Which three statements are true about inner and outer joins? (Choose three.)

 A. A full outer join returns matched and unmatched rows.

 B. Outer joins can be used when there are multiple join conditions on two tables.
 C. A full outer join must use Oracle syntax.
 D. Outer joins can only be used between two tables per query.
  E. A left or right outer join returns only unmatched rows.
 F. An inner join returns matched rows.
Correct Answer: ABF 
 
Question #62Topic 1
Which statement will execute successfully?

A.

B.

C.

D.

Correct Answer: A
 
Question #63Topic 1
Examine the description of the EMPLOYEES table:
Which two queries return all rows for employees whose salary is greater than the average
salary in their department? (Choose two.)

A.

B.

C.

D.

E.

Correct Answer: BE
 
Question #64Topic 1
Which three statements are true about the Oracle join and ANSI join syntax? (Choose
three.)

 A. The Oracle join syntax supports creation of a Cartesian product of two tables.
 B. The Oracle join syntax only supports right outer joins.
 C. The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian
product of two tables.
 D. The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join
syntax.
 E. The Oracle join syntax supports natural joins.
 F. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join
syntax.
 G. The SQL:1999 compliant ANSI join syntax supports natural joins.
Correct Answer: ACG 
 
Question #65Topic 1
Which two are true about the NVL, NVL2, and COALESCE functions? (Choose two.)

 A. NVL must have expressions of the same data type.

 B. NVL can have any number of expressions in the list.
 C. NVL2 can have any number of expressions in the list.
 D. COALESCE stops evaluating the list of expressions when it finds the first non-null
value.
 E. The first expression in NVL2 is never returned.
 F. COALESCE stops evaluating the list of expressions when it finds the first null
value.
Correct Answer: DE 
 
Question #66Topic 1
Examine this statement:

What is returned upon execution?

  A. an error
 B. 2 rows
 C. 0 rows
 D. 1 row
Correct Answer: D 
 
Question #67Topic 1
Examine this statement:

What is returned upon execution?

 A. an error
 B. 2 rows
 C. 0 rows
 D. 1 row
Correct Answer: D 
 
Question #68Topic 1
Which two statements execute successfully? (Choose two.)

A.

B.

C.

D.
E.

Correct Answer: AB
 
Question #69Topic 1
Which three statements are true regarding group functions? (Choose three.)

 A. They can be used on columns or expressions.

 B. They can be passed as an argument to another group function.
 C. They can be used only with a SQL statement that has the GROUP BY clause.
 D. They can be used on only one column in the SELECT clause of a SQL statement.
 E. They can be used along with the single-row function in the SELECT clause of a
SQL statement.
Suggested Answer: ABE 
 
 
Question #70Topic 1
Which three statements are true? (Choose three.)

 A. The data dictionary is created and maintained by the database administrator.

 B. The data dictionary views consist of joins of dictionary base tables and user-
defined tables.
 C. The usernames of all the users including database administrators are stored in
the data dictionary.
 D. The USER_CONS_COLUMNS view should be queried to find the names of
columns to which constraints apply.
 E. Both USER_OBJECTS and CAT views provide the same information about all
objects that are owned by the user.
 F. Views with the same name but different prefixes, such as DBA, ALL and USER,
reference the same base tables from the data dictionary.
Suggested Answer: CDF
 
 
Question #71Topic 1
View the exhibits and examine the structures of the COSTS and PROMOTIONS tables.
 

Evaluate the following SQL statement:

What would be the outcome of the above SQL statement?

 A. It displays prod IDs in the promo with the lowest cost.
 B. It displays prod IDs in the promos with the lowest cost in the same time interval.
 C. It displays prod IDs in the promos with the highest cost in the same time interval.
 D. It displays prod IDs in the promos which cost less than the highest cost in the
same time interval.
Suggested Answer: D 
 
Question #72Topic 1
View the exhibit and examine the descriptions of the DEPT and LOCATIONS tables.

You want to update the CITY column of the DEPT table for all the rows with the
corresponding value in the CITY column of the LOCATIONS table for each department.

Which SQL statement would you execute to accomplish the task?

 A. UPDATE dept d SET city = ALL (SELECT city FROM locations l WHERE
d.location_id = l.location_id);
 B. UPDATE dept d SET city = (SELECT city FROM locations l) WHERE d.location_id =
l.location_id;
 C. UPDATE dept d SET city = ANY (SELECT city FROM locations l)
 D. UPDATE dept d SET city = (SELECT city FROM locations l WHERE d.location_id =
l.location_id);
Suggested Answer: D 
 
 
Question #73Topic 1
Your database instance was shut down normally and then started in NOMOUNT state. You
then execute this command:

ALTER DATABASE MOUNT;

Which two actions are performed? (Choose two.)

 A. The online redo logs are opened

  B. The online data files are opened
 C. The alert log records the execution details
 D. The Oracle background processes are started
 E. The initialization parameter file is read
 F. The control file is read
Suggested Answer: CF
 
 
Question #74Topic 1
Oracle Managed Files (OMF) is enabled in a CDB and this command is successfully
executed:

Which three are true? (Choose three.)

 A. Application PDBs that are subsequently created in the APP1 application

container will be cloned from APP1$SEED.
 B. An application seed PDB is created for APP1.
 C. An application root PDB is created for APP1.
 D. A default service is created for the application root APP1.
 E. Application PDBs that are subsequently created in the APP1 application
container will be cloned from PDB$SEED.
 F. APP1 can never be unplugged.
Suggested Answer: CDE 
 
 
Question #75Topic 1
Identify situations in which the DBMS_SQL package is the only applicable method of
processing dynamic SQL. (Choose all that apply.)

 A. When a query returns multiple rows

 B. When a column name in a where clause is unknown at compile time
 C. When the number of columns selected in a query is not known until run time
 D. When a table needs to be created based on an existing table structure at run time
 E. When privileges need to be granted to a new user to access an existing schema
at run time
Suggested Answer: BC
 
 
Question #76Topic 1
You issue commands in SQL*Plus as the Oracle owner, to enable multithreading for your
UNIX-based Oracle 12c database:
CONNECT/AS SYSDBA -
ALTER SYSTEMSETTHREADED__EXECUTION= TRUESCOPE=SPFILE;
SHUTDOWNIMMEDIATE -
You then restart the instance and get an error:
STARTUP -
ORA-01031:insufficientprivileges
Why does the startup command return the error shown?

 A. because the threaded architecture requires exiting from sql*pIus and

reconnecting with sql*PIus / as sysdba before issuing a startup command
 B. because the threaded architecture requires issuing a new connect / as sysdba
from within sql*pIus before issuing a startup command
 C. because the threaded architecture requires authentication using a password file
before issuing a startup command
 D. because the threaded architecture requires connecting to the instance via a
listener before issuing a startup command
 E. because the threaded architecture requires restarting the listener before issuing
a startup command
Suggested Answer: C 
 
 
 
Question #77Topic 1
Evaluate the following CREATE TABLE command:

Which statement is true regarding the above SQL statement?

  A. It would execute successfully and only ORD_ITM_IDX index would be created.
 B. It would give an error because the USING INDEX clause cannot be used on a
composite primary.
 C. It would execute successfully and two indexes ORD_ITM_IDX and
ORD_ITM_ID_PK would be created.
 D. It would give an error because the USING INDEX clause is not permitted in the
CREATE TABLE command.
Suggested Answer: A 
 
 
 
Question #78Topic 1
Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit
amount in each income level. The report should NOT show any repeated credit amounts in
each income level.

Which query would give the required result?

 A. SELECT cust_income_level || "˜ ' || cust_credit_limit * 0.50 AS "50% Credit Limit"

FROM customers.
 B. SELECT DISTINCT cust_income_level || "˜ ' || cust_credit_limit * 0.50 AS "50%
Credit Limit" FROM customers.
 C. SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50%
Credit Limit" FROM customers.
 D. SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit
Limit" FROM customers
Suggested Answer: B
 
 
 
Question #79Topic 1
Which three statements are true regarding the SQL WHERE and HAVING clauses?

 A. The HAVING clause conditions can have aggregating functions.

 B. The HAVING clause conditions can use aliases for the columns.
 C. The WHERE and HAVING clauses cannot be used together in a SQL statement.
 D. The WHERE clause is used to exclude rows before grouping data.
 E. The HAVING clause is used to exclude one or more aggregated results after
grouping data.
Suggested Answer: ADE
 
 
Question #80Topic 1
You need to display the date 11-oct-2007 in words as "˜Eleventh of October, Two Thousand
Seven'.
Which SQL statement would give the required result?

 A. SELECT TO_CHAR (TO_DATE ('11-oct-2007'), "˜fmDdthsp "of" Month, Year')

FROM DUAL
 B. SELECT TO_CHAR ("˜11-oct-2007', "˜fmDdspth "of" Month, Year') FROM DUAL
 C. SELECT TO_CHAR (TO_DATE ("˜11-oct-2007'), "˜fmDdspth of month, year') FROM
DUAL
 D. SELECT TO_DATE (TO_CHAR ('11-oct-2007'), "˜fmDdspth "of" Month, Year'))
FROM DUAL
Suggested Answer: A
 
 
Question #81Topic 1
Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS:

You want to generate a report that shows all course IDs irrespective of whether they have
corresponding department IDs or not but no department IDs if they do not have any
courses.

Which SQL statement must you use?

 A. SELECT course_id, department_id, FROM department_details d RIGHT OUTER

JOIN course_details c USING (department_id) Most Voted
 B. SELECT c.course_id, d.department_id FROM course_details c RIGHT OUTER JOIN
.department_details d ON (c.depatrment_id=d.department_id)
 C. SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN
department_details d ON (c.department_id=d. department_id)
 D. SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN
department_details d ON (c.department_id<>d. department_id)
Suggested Answer: A
 
 
 
Question #82Topic 1
View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and
TIMES tables.

The PROD_ID column is the foreign key in the SALES table referencing the PRODUCTS
table.
The CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the
CUSTOMERS and TIMES tables, respectively.
Examine this command:
CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE)
AS -
SELECT prod_id, cust_id, time_id
FROM sales;
Which statement is true?

 A. The NEW_SALES table would get created and all the FOREIGN KEY constraints
defined on the selected columns from the SALES table would be created on the
corresponding columns in the NEW_SALES table.
 B. The NEW_SALES table would not get created because the column names in the
CREATE TABLE command and the SELECT clause do not match.
  C. The NEW_SALES table would not get created because the DEFAULT value cannot
be specified in the column definition.
 D. The NEW_SALES table would get created and all the NOT NULL constraints
defined on the selected columns from the SALES table would be created on the
corresponding columns in the NEW_SALES table.
Suggested Answer: D
 
 
Question #83Topic 1
Examine the data in the ORDERS table:

Examine the data in the INVOICES table:

Examine this query:

 A. 2
 B. 1
 C. 3
 D. 5 01-MAR-2019
 E. 3 01-JAN-2015
 F. 4 01-FEB-2015
Suggested Answer: BF
 
 
Question #84Topic 1
Evaluate the following statement.
Which statement is true regarding the evaluation of rows returned by the subquery in the
INSERT statement?

 A. Each row is evaluated by the first WHEN clause and if the condition is false then
the row would be evaluated by the subsequent when clauses.
 B. All rows are evaluated by all the three WHEN clauses.
 C. Each row is evaluated by the first WHEN clause and if the condition is true, then
the row would be evaluated by the subsequent when clauses.
 D. The INSERT statement will return an error because the ELSE clause is missing.
Suggested Answer: B
 
 
Question #85Topic 1
Which two statements are true regarding the SQL GROUP BY clause?

 A. You can use a column alias in the GROUP BY clause.

 B. Using the WHERE clause after the GROUP BY clause excludes rows after creating
groups.
 C. The GROUP BY clause is mandatory if you are using an aggregating function in
the SELECT clause.
 D. Using the WHERE clause before the GROUP BY clause excludes rows before
creating groups.
 E. If the SELECT clause has an aggregating function, then columns without an
aggregating function in the SELECT clause should be included in the GROUP BY
clause.
Suggested Answer: DE
 
 
Question #86Topic 1
You issue this command which succeeds:
SQL> DROP TABLE products;
Which three statements are true?

 A. All existing views and synonyms that refer to the table are invalidated but
retained. Most Voted
 B. Any uncommitted transaction in the session is committed. Most Voted
 C. Table data and the table structure are deleted. Most Voted
 D. All the table's indexes if any exist, are invalidated but retained.
 E. Table data is deleted but the table structure is retained.
Suggested Answer: ABC
 
 
Question #87Topic 1
You execute the SQL statement:

What is the outcome?

 A. It fails because the NOT NULL and DEFAULT options cannot be combined for the
same column.
 B. It succeeds and CITY can contain only "˜SEATTLE' or null for all rows.
 C. It fails because the condition for the CNAMES constraint is not valid.
 D. It succeeds and an index is created for CITIZEN_ID. Most Voted
Suggested Answer: D
 
 
Question #88Topic 1
Evaluate the following CREATE TABLE commands:

CREATE_TABLE orders -
(ord_no NUMBER (2) CONSTRAINT ord_pk PRIMARY KEY,
ord_date DATE,
cust_id NUMBER (4) );

CREATE TABLE ord_items -

(ord _no NUMBER (2),
item_no NUMBER(3),
qty NUMBER (3) CHECK (qty BETWEEEN 100 AND 200),
expiry_date date CHECK (expiry_date> SYSDATE),
CONSTRAINT it_pk PRIMARY KEY (ord_no, item_no),
CONSTARAINT ord_fk FOREIGN KEY (ord_no) REFERENCES orders (ord_no) );

The above command fails when executed. What could be the reason?

 A. SYSDATE cannot be used with the CHECK constraint.

 B. The BETWEEN clause cannot be used for the CHECK constraint.
 C. The CHECK constraint cannot be placed on columns having the DATE data type.
 D. ORD_NO and ITEM_NO cannot be used as a composite primary key because
ORD_NO is also the FOREIGN KEY.
Suggested Answer: A
 
 
Question #89Topic 1
Examine the structure of the PROGRAMS table:

Which two SQL statements would execute successfully?

 A. SELECT NVL (ADD_MONTHS (END_DATE,1) SYSDATE) FROM programs;

 B. SELECT TO_DATE (NVL (SYSDATE-END_DATE, SYSDATE)) FROM programs;
 C. SELECT NVL (MONTHS_BETWEEN (start_date, end_date), "˜Ongoing') FROM
programs;
 D. SELECT NVL (TO_CHAR (MONTHS_BETWEEN (start-date, end_date)), "˜Ongoing')
FROM programs
Suggested Answer: AD
 
 
Question #90Topic 1
Examine the description of the EMPLOYEES table:

Which two statements will insert a row into the EMPLOYEES table? (Choose two.)

 A. INSERT INTO employees VALUES (101, 'John', 'Smith', 12000, SYSDATE);

 B. INSERT INTO employees VALUES (101, 'John', 'Smith', 10, 12000, SYSDATE);
 C. INSERT INTO employees (employee_id, salary, first_name, hiredate, last_name)
VALUES (101, 12100, 'John', SYSDATE, 'Smith');
 D. INSERT INTO employees (employee_id, first_name, last_name, salary, hiresate)
VALUES ( (SELECT 101, 'John', 'Smith'. 12000, SYSDATE FROM dual) );
 E. INSERT INTO employees SELECT 101, 'John', 'Smith', 12000, (SELECT SYSDATE
FROM dual), 10 FROM dual;
 F. INSERT INTO employees VALUES (101, 'John', ' ', 12000, SYSDATE, 10);
Suggested Answer: CF
 
 
Question #91Topic 1
Examine this command:

TRUNCATE TABLE test;

Table truncated.
Which two are true? (Choose two.)

 A. The structure of the TEST table is removed.

 B. All the indexes on the TEST table are dropped.
 C. All the constraints on the TEST table are dropped.
 D. Removed rows can not be recovered using the ROLLBACK command.
 E. All the rows in the TEST table are removed.
Suggested Answer: DE
 
 
Question #92Topic 1
View the Exhibit and examine the structure in the EMPLOYEES tables.

Evaluate the following SQL statement:

SELECT employee_id, department_id
FROM employees -
WHERE department_id= 50 ORDER BY department_id
UNION -
SELECT employee_id, department_id
FROM employees -
WHERE department_id=90 -
UNION -
SELECT employee_id, department_id
FROM employees -
WHERE department_id=10;

What would be the outcome of the above SQL statement?

 A. The statement would not execute because the positional notation instead of the
column name should be used with the ORDER BY clause.
 B. The statement would execute successfully and display all the rows in the
ascending order of DEPARTMENT_ID.
 C. The statement would execute successfully but it will ignore the ORDER BY clause
and display the rows in random order.
 D. The statement would not execute because the ORDER BY clause should appear
only at the end of the SQL statement, that is, in the last SELECT statement.
Suggested Answer: D
 
 
 
Question #93Topic 1
View the Exhibit and examine the description for the SALES and CHANNELS tables.
(Choose the best answer.)

You issued this SQL statement:

Which statement is true regarding the result?

 A. The statement will fail because the subquery in the VALUES clause is not
enclosed within single quotation marks.
 B. The statement will fail because a subquery cannot be used in a VALUES clause.
 C. The statement will execute and a new row will be inserted in the SALES table.
 D. The statement will fail because the VALUES clause is not required with the
subquery.
Suggested Answer: C
 
 
Question #94Topic 1
View the Exhibit and examine the description of the ORDERS table.

Which two WHERE clause conditions demonstrate the correct usage of conversion
functions? (Choose two.)

 A. WHERE order_date_IN ( TO_DATE('OCT 21 2003','MON DD YYYY'), TO_CHAR('NOV

21 2003','MON DD YYYY') )
 B. WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE,6),'MON DD YYYY')
 C. WHERE TO_CHAR(order_date,'MON DD YYYY') = 'JAN 20 2003'
 D. WHERE order_date > TO_DATE('JUL 10 2006','MON DD YYYY')
Suggested Answer: CD
 
 
Question #95Topic 1
Which three arithmetic operations can be performed on a column by using a SQL function
that is built into Oracle database? (Choose three.)

 A. Finding the lowest value

 B. Finding the quotient
 C. Raising to a power
 D. Subtraction
 E. Addition
Suggested Answer: ACE
 
 
Question #96Topic 1
View the Exhibit and examine the structure of the EMPLOYEES and JOB_HISTORY tables.
Examine this query which must select the employee IDs of all the employees who have held
the job SA_MAN at any time during their employment.

SELECT EMPLOYEE_ID -
FROM EMPLOYEES -
WHERE JOB_ID = 'SA_MAN'
-------------------------------------
SELECT EMPLOYEE_ID -
FROM JOB_HISTORY -
WHERE JOB_ID = 'SA_MAN';

Choose two correct SET operators which would cause the query to return the desired result.

 A. UNION
 B. MINUS
 C. INTERSECT
 D. UNION ALL
Hide Answer
Suggested Answer: ACD 
 
 
Question #97Topic 1
Examine the data in the CUST_NAME column of the CUSTOMERS table:
You want to display the CUST_NAME values where the last name starts with Mc or MC.
Which two WHERE clauses give the required result? (Choose two.)

 A. WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name, ' ') + 1)) LIKE 'Mc%'

 B. WHERE SUBSTR(cust_name, INSTR(cust_name, ' ') + 1) LIKE 'Mc%'
 C. WHERE SUBSTR(cust_name, INSTR(cust_name, ' ') + 1 ) LIKE 'Mc%' OR 'MC%'
 D. WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name, ' ') + 1)) LIKE UPPER('MC
%')
 E. WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name, ' ’) + 1)) IN ('MC%', 'Mc
%)
Suggested Answer: AD
 
 
Question #98Topic 1
View the Exhibit and examine the details of the PRODUCT_INFORMATION table.

Evaluate this SQL statement:

SELECT TO_CHAR (list_price, '$9,999')
From product_information;
Which two statements are true regarding the output? (Choose two.)
  A. A row whose LIST_PRICE column contains value 11235.90 would be displayed as
#######.
 B. A row whose LIST_PRICE column contains value 1123.90 would be displayed as
$1,123.
 C. A row whose LIST_PRICE column contains value 1123.90 would be displayed as
$1,124.
 D. A row whose LIST_PRICE column contains value 11235.90 would be displayed as
$1,123.
Suggested Answer: AC 
 
 
Question #99Topic 1
Which statement is true about SQL query processing in an Oracle database instance?
(Choose the best answer.)

 A. During parsing, a SQL statement containing literals in the WHERE clause that has
been executed by any session and which is cached in memory, is always reused for
the current execution.
 B. During execution, the Oracle server may read data from storage if the required
data is not already in memory.
 C. During row source generation, rows that satisfy the query are retrieved from the
database and stored in memory.
 D. During optimization, execution plans are formulated based on the statistics
gathered by the database instance, and the lowest cost plan is selected for
execution.
Suggested Answer: B
 
 
Question #100Topic 1
Examine the structure of the ORDERS table: (Choose the best answer.)

You want to find the total value of all the orders for each year and issue this command:
SQL> SELECT TO_CHAR(order_date,'rr'), SUM(order_total) FROM orders
GROUP BY TO_CHAR(order_date, 'yyyy');
Which statement is true regarding the result?

 A. It executes successfully but does not give the correct output.
 B. It executes successfully and gives the correct output.
 C. It returns an error because the TO_CHAR function is not valid.
 D. It return an error because the datatype conversion in the SELECT list does not
match the data type conversion in the GROUP BY clause.
Suggested Answer: D 
 
Question #101Topic 1
View the Exhibit and examine the structure of the ORDER_ITEMS table.

You must select the ORDER_ID of the order that has the highest total value among all the
orders in the ORDER_ITEMS table.
Which query would produce the desired result?

 A. SELECT order_id FROM order_items GROUP BY order_id HAVING

SUM(unit_price*quantity) = (SELECT MAX(SUM(unit_price*quantity)) FROM
order_items GROUP BY order_id);
 B. SELECT order_id FROM order_items WHERE(unit_price*quantity) = (SELECT
MAX(unit_price*quantity) FROM order_items) GROUP BY order_id;
 C. SELECT order_id FROM order_items WHERE(unit_price*quantity) =
MAX(unit_price*quantity) GROUP BY order_id;
  D. SELECT order_id FROM order_items WHERE (unit_price*quantity) = (SELECT
MAX(unit_price*quantity) FROM order_items GROUP BY order_id)
Suggested Answer: A
 
 
Question #102Topic 1
View the Exhibit and examine the structure of the EMP table which is not partitioned and
not an index-organized table. (Choose two.)

Evaluate this SQL statement:

ALTER TABLE emp -
DROP COLUMN first_name;
Which two statements are true?

 A. The FIRST_NAME column can be dropped even if it is part of a composite

PRIMARY KEY provided the CASCADE option is added to the SQL statement.
 B. The FIRST_NAME column would be dropped provided at least one column
remains in the table.
 C. The FIRST_NAME column would be dropped provided it does not contain any
data.
 D. The drop of the FIRST_NAME column can be rolled back provided the SET
UNUSED option is added to the SQL statement.
Suggested Answer: AB
 
 
Question #103Topic 1
You want to write a query that prompts for two column names and the where condition
each time it is executed in a session but only prompts for the table name the first time it is
executed.
The variables used in your query are never undefined in your session.
Which query can be used?

 A. 
  B. 

 C. 

 D. 

 E. 

Hide Answer
Suggested Answer: D
 
Question #104Topic 1
Which two statements best describe the benefits of using the WITH clause? (Choose two.)

 A. It can improve the performance of a large query by storing the result of a query
block having the WITH clause in the session's temporary tablespace.
 B. It enables sessions to reuse the same query block in a SELECT statement, if it
occurs more than once in a complex query.
 C. It enables sessions to store a query block permanently in memory and use it to
create complex queries.
 D. It enables sessions to store the results of a query permanently.
Suggested Answer: AB 
 
 
Question #105Topic 1
Which three statements are true regarding subqueries? (Choose three.)

 A. The ORDER BY Clause can be used in a subquery.

 B. A subquery can be used in the FROM clause of a SELECT statement.
 C. If a subquery returns NULL, the main query may still return rows.
 D. A subquery can be placed in a WHERE clause, a GROUP BY clause, or a HAVING
clause.
 E. Logical operators, such as AND, OR and NOT, cannot be used in the WHERE
clause of a subquery.
Suggested Answer: ABC
 
Question #106Topic 1
Which two statements are true regarding single row functions? (Choose two.)

 A. MOD : returns the quotient of a division.

 B. TRUNC : can be used with NUMBER and DATE values.
 C. CONCAT : can be used to combine any number of values.
 D. SYSDATE : returns the database server current date and time.
 E. INSTR : can be used to find only the first occurrence of a character in a string.
 F. TRIM : can be used to remove all the occurrences of a character from a string.
Suggested Answer: BD 
 
 
Question #107Topic 1
View the Exhibit and examine the structure of the ORDERS table.
You must select ORDER_ID and ORDER_DATE for all orders that were placed after the last
order placed by CUSTOMER_ID 101.
Which query would give you the desired result?

 A. SELECT order_id, order_date FROM orders WHERE order_date > ANY (SELECT
order_date FROM orders WHERE customer_id = 101);
 B. SELECT order_id, order_date FROM orders WHERE order_date > ALL (SELECT
MAX(order_date) FROM orders ) AND customer_id = 101;
 C. SELECT order_id, order_date FROM orders WHERE order_date > ALL (SELECT
order_date FROM orders WHERE customer_id = 101);
 D. SELECT order_id, order_date FROM orders WHERE order_date > IN (SELECT
order_date FROM orders WHERE customer_id = 101);
Suggested Answer: C 
 
 
Question #108Topic 1
You must display details of all users whose username contains the string 'ch_'. (Choose the
best answer.)
Which query generates the required output?

 A. SELECT * FROM users Where user_name LIKE '%ch_';

 B. SELECT * FROM users Where user_name LIKE '%ch_%'ESCAPE'%';
 C. SELECT * FROM users Where user_name LIKE 'ch\_%' ESCAPE '_';
 D. SELECT * FROM users Where user_name LIKE '%ch\_%' ESCAPE '\'; Most Voted
Suggested Answer: D
 
 
Question #109Topic 1
Which three statements are true regarding the usage of the WITH clause in complex
correlated subqueries? (Choose three.)

 A. It can be used only with the SELECT clause.

 B. The WITH clause can hold more than one query.
 C. If the query block name and the table name are the same, then the table name
takes precedence.
 D. The query name in the WITH clause is visible to other query blocks in the WITH
clause as well as to the main query block
Suggested Answer: ABD 
 
Question #110Topic 1
View the Exhibit and examine the data in the PRODUCTS table.
You must display product names from the PRODUCTS table that belong to the
'Software/other' category with minimum prices as either $2000 or $4000 and with no unit
of measure.
You issue this query:

Which statement is true?

 A. It executes successfully but returns no result.

 B. It executes successfully and returns the required result.
 C. It generates an error because the condition specified for
PROD_UNIT_OF_MEASURE is not valid.
 D. It generates an error because the condition specified for the PROD_CATEGORY
column is not valid.
Suggested Answer: A 
 
 
Question #111Topic 1
Examine the structure of the EMPLOYEES table.
You must display the maximum and minimum salaries of employees hired 1 year ago.
Which two statements would provide the correct output? (Choose two.)

 A. SELECT MIN(Salary) minsal, MAX(salary) maxsal FROM employees WHERE

hire_date < SYSDATE-365 GROUP BY MIN(salary), MAX(salary);
 B. SELECT minsal, maxsal FROM (SELECT MIN(salary) minsal, MAX(salary) maxsal
FROM employees WHERE hire_date < SYSDATE-365) GROUP BY maxsal, minsal;
 C. SELECT minsal, maxsal FROM (SELECT MIN(salary) minsal, MAX(salary) maxsal
FROM employees WHERE hire_date < SYSDATE-365 GROUP BY MIN(salary),
MAX(salary));
 D. SELECT MIN(Salary), MAX(salary) FROM (SELECT salary FROM employees
WHERE hire_date < SYSDATE-365);
Suggested Answer: BD 
 
 
Question #112Topic 1
Which two statements are true regarding subqueries? (Choose two.)

 A. A subquery can appear on either side of a comparison operator.

 B. Only two subqueries can be placed at one level.
 C. A subquery can retrieve zero or more rows.
 D. A subquery can be used only in SQL query statements.
 E. There is no limit on the number of subquery levels in the WHERE clause of a
SELECT statement.
Suggested Answer: AC 
 
 
Question #113Topic 1
Which two statements are true regarding the execution of the correlated subqueries?
(Choose two.)

 A. The nested query executes after the outer query returns the row.
 B. The nested query executes first and then the outer query executes.
 C. The outer query executes only once for the result returned by the inner query.
 D. Each row returned by the outer query is evaluated for the results returned by the
inner query.
Suggested Answer: AD 
 
 
Question #114Topic 1
Which two statement are true regarding table joins available in the Oracle Database server?
(Choose two.)

 A. You can use the ON clause to specify multiple conditions while joining tables.
 B. You can explicitly provide the join condition with a NATURAL JOIN.
 C. You can use the JOIN clause to join only two tables.
 D. You can use the USING clause to join tables on more than one column.
Suggested Answer: AD 
 
Question #115Topic 1
You issued this command:

SQL > DROP TABLE employees;

Which three statements are true? (Choose three.)

 A. Sequences used in the EMPLOYEES table become invalid.

 B. If there is an uncommitted transaction in the session, it is committed.
 C. All indexes and constraints defined on the table being dropped are also dropped.
 D. The space used by the EMPLOYEES table is always reclaimed immediately.
 E. The EMPLOYEES table can be recovered using the ROLLBACK command.
 F. The EMPLOYEES table may be moved to the recycle bin.
Suggested Answer: BCF 
 
 
Question #116Topic 1
Which two statements are true? (Choose two.)

 A. CASE is a function and DECODE is not.

 B. Neither CASE nor DECODE is a function.
 C. All conditions evaluated using CASE can also be evaluated using DECODE.
 D. All conditions evaluated using DECODE can also be evaluated using CASE.
 E. DECODE is a function and CASE is not.
 F. Both CASE and DECODE are functions.
Suggested Answer: DE
 
 
Question #117Topic 1
View the Exhibit and examine the structure of the SALES and PRODUCTS tables. (Choose
two.)
In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS
table. You must list each product ID and the number of times it has been sold.

Examine this query which is missing a JOIN operator:

Which two JOIN operations can be used to obtain the required output?

 A. FULL OUTER JOIN

 B. JOIN
 C. LEFT OUTER JOIN
 D. RIGHT OUTER JOIN
Suggested Answer: AC
 
 
 
Question #118Topic 1
View the exhibit and examine the description of the EMPLOYEES table. (Choose two.)
You executed this SQL statement:

SELECT first_name, department_id, salary

FROM employees -
ORDER BY department_id, first_name, salary desc;
Which two statements are true regarding the result? (Choose two.)

 A. The values in the SALARY column would be returned in descending order for all
employees having the same value in the DEPARTMENT_ID and FIRST_NAME
column.
 B. The values in the FIRST_NAME column would be returned in ascending order for
all employees having the same value in the DEPARTMENT_ID column.
 C. The values in the SALARY column would be returned in descending order for all
employees having the same value in the DEPARTMENT_ID column.
 D. The values in all columns would be returned in descending order.
 E. The values in the FIRST_NAME column would be returned in descending order for
all employees having the same value in the DEPARTMENT_ID column.
Suggested Answer: AB 
 
 
Question #119Topic 1
Examine the structure of the SALES table.
Examine this statement:

Which two statements are true about the SALES1 table? (Choose two.)

 A. It will not be created because the column-specified names in the SELECT and
CREATE TABLE clauses do not match.
 B. It will have NOT NULL constraints on the selected columns which had those
constraints in the SALES table.
 C. It will not be created because of the invalid WHERE clause.
 D. It is created with no rows.
 E. It has PRIMARY KEY and UNIQUE constraints on the selected columns which had
those constraints in the SALES table.
Suggested Answer: BD 
 
 
Question #120Topic 1
Examine this SELECT statement and view the Exhibit to see its output:
SELECT constraints_name, constraints_type, search_condition, r_constraints_name,
delete_rule, status,
FROM user_constraints -
WHERE table_name = 'ORDERS';
Which two statements are true about the output? (Choose two.)

 A. The DELETE_RULE column indicates the desired state of related rows in the child
table when the corresponding row is deleted from the parent table.
 B. The R_CONSTRAINT_NAME column contains an alternative name for the
constraint.
 C. In the second column, 'c' indicates a check constraint.
 D. The STATUS column indicates whether the table is currently in use.
Suggested Answer: AC 
 
 
Question #121Topic 1
Which two statements are true regarding constraints? (Choose two.)

 A. All constraints can be defined at the column level and at the table level.
 B. A constraint can be disabled even if the constraint column contains data.
 C. A column with the UNIQUE constraint can contain NULLS.
 D. A foreign key column cannot contain NULLS.
 E. A constraint is enforced only for INSERT operations.
Suggested Answer: BC
 
 
Question #122Topic 1
Which two statements are true regarding working with dates? (Choose two.)

 A. The RR date format automatically calculates the century from the SYSDATE
function but allows the session user to enter the century.
  B. The RR date format automatically calculates the century from the SYSDATE
function and does not allow a session user to enter the century.
 C. The default internal storage of dates is in character format.
 D. The default internal storage of dates is in numeric format.
Suggested Answer: AD 
 
 
Question #123Topic 1
View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. (Choose
the best answer.)

You executed this UPDATE statement:

Which statement is true regarding the execution?

 A. It would not execute because a subquery cannot be used in the WHERE clause of
an UPDATE statement.
 B. It would not execute because two tables cannot be referenced in a single
UPDATE statement.
 C. It would execute and restrict modifications to the columns specified in the
SELECT statement.
  D. It would not execute because a SELECT statement cannot be used in place of a
table name.
Suggested Answer: C 
 
 
Question #124Topic 1
View the Exhibit and examine the structure of the PRODUCTS table.

You must display the category with the maximum number of items.
You issue this query:

What is the result?

 A. It generates an error because = is not valid and should be replaced by the IN
operator.
 B. It executes successfully but does not give the correct output.
 C. It executes successfully and gives the correct output.
 D. It generate an error because the subquery does not have a GROUP BY clause.
Suggested Answer: D
 
 
Question #125Topic 1
Examine the structure of the MEMBERS table: (Choose the best answer.)

Examine the SQL statement:

SQL > SELECT city, last_name LNAME FROM MEMBERS ORDER BY 1, LNAME DESC;
What would be the result execution?

 A. It displays all cities in descending order, within which the last names are further
sorted in descending order.
 B. It fails because a column alias cannot be used in the ORDER BY clause.
 C. It fails because a column number and a column alias cannot be used together in
the ORDER BY clause.
 D. It displays all cities in ascending order, within which the last names are further
sorted in descending order.
Suggested Answer: D 
 
 
Question #126Topic 1
View and Exhibit and examine the structure and data in the INVOICE table.
Which two statements are true regarding data type conversion in query expressions?
(Choose two.)

 A. inv_date = '15-february-2008' :uses implicit conversion

 B. inv_amt = '0255982' : requires explicit conversion
 C. inv_date > '01-02-2008' : uses implicit conversion
 D. CONCAT(inv_amt, inv_date) : requires explicit conversion
 E. inv_no BETWEEN '101' AND '110' : uses implicit conversion
Suggested Answer: AE
 
 
Question #127Topic 1
Examine the structure of the EMPLOYEES table.

You must display the details of employees who have manager with MANAGER_ID 100, who
were hired in the past 6 months and who have salaries greater than 10000.
Which query would retrieve the required result?
  A. SELECT last_name, hire_date, salary FROM employees WHERE salary > 10000
UNION ALL SELECT last_name, hire_date, salary FROM employees WHERE
manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100)
INTERSECT SELECT last_name, hire_date, salary FROM employees WHERE
hire_date > SYSDATE- 180;
 B. SELECT last_name, hire_date, salary FROM employees WHERE manager_id =
(SELECT employee_id FROM employees WHERE employee_id = 100) UNION ALL
(SELECT last_name, hire_date, salary FROM employees WHERE hire_date >
SYSDATE -180 INTERSECT SELECT last_name, hire_date, salary FROM employees
WHERE salary > 10000);
 C. SELECT last_name, hire_date, salary FROM employees WHERE manager_id =
(SELECT employee_id FROM employees WHERE employee_id = '100') UNION
SELECT last_name, hire_date, salary FROM employees WHERE hire_date > SYSDATE
-180 INTERSECT SELECT last_name, hire_date, salary FROM employees WHERE
salary > 10000;
 D. (SELECT last_name, hire_date, salary FROM employees WHERE salary > 10000
UNION ALL SELECT last_name, hire_date, salary FROM employees WHERE
manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100))
UNION SELECT last_name, hire_date, salary FROM employees WHERE hire_date >
SYSDATE -180;
Suggested Answer: C 
 
 
Question #128Topic 1
Examine the structure of the PROMOTIONS table: (Choose the best answer.)

Management requires a report of unique promotion costs in each promotion category.

Which query would satisfy this requirement?

 A. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1

 B. SELECT promo_category, DISTINCT promo_cost FROM promotions
 C. SELECT DISTINCT promo_cost, promo_category FROM promotions
 D. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;
Suggested Answer: A 
 
 
Question #129Topic 1
You must create a table for a banking application.
One of the columns in the table has these requirements:
1: A column to store the duration of a short team loan
2: The data should be stored in a format supporting DATE arithmetic with DATE datatypes
without using conversion functions.
3: The maximum loan period is 30 days.
4: Interest must be calculated based on the number of days for which the loan remains
unpaid.
Which data type would you use?

 A. DATE
 B. NUMBER
 C. TIMESTAMP
 D. INTERVAL DAY TO SECOND
 E. INTERVAL YEAR TO MONTH
Suggested Answer: D 
 
 
Question #130Topic 1
Examine the structure of the CUSTOMERS table: (Choose two.)

CUSTNO is the PRIMARY KEY.

You must determine if any customers' details have been entered more than once using a
different CUSTNO, by listing all duplicate names.
Which two methods can you use to get the required result?

 A. Subquery
 B. Self-join
 C. Full outer-join with self-join
 D. Left outer-join with self-join
 E. Right outer-join with self-join
Suggested Answer: AB 
 
 
Question #131Topic 1
Which two are the minimal requirements for a self-join? (Choose two.)

 A. Only equijoin conditions may be used in the query.

 B. Outer joins must not be used in the query.
 C. There must be a condition on which the self-join is performed.
 D. No other condition except the self-join may be specified.
 E. The table used for the self-join must have two different alias names in the query.
Suggested Answer: CE 
 
 
Question #132Topic 1
Examine the SQL statement used to create the TRANSACTION table.

SQL > CREATE TABLE transaction -

(trn_id char(2) primary key,
Start_date date DEFAULT SYSDATE,
End_date date NOT NULL);
The value 'A1' does not exist for trn_id in this table.
Which SQL statement successfully inserts a row into the table with the default value for
START_DATE?

 A. INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE(DEFAULT+10))

 B. INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE('SYSDATE+10'))
 C. INSERT INTO transaction (trn_id, end_date) VALUES ('A1', '10-DEC-2014')
 D. INSERT INTO transaction (trn_id, start_date, end_date) VALUES ('A1', , '10-DEC-
2014')
Suggested Answer: C 
 
 
Question #133Topic 1
Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose
three.)

 A. SELECT TO_CHAR (1890.55, '$99G999D00') FROM DUAL

 B. SELECT TO_CHAR (1890.55, '$9,999V99') FROM DUAL;
 C. SELECT TO_CHAR (1890.55, '$0G000D00') FROM DUAL;
 D. SELECT TO_CHAR (1890.55, '$99,999D99') FROM DUAL;
 E. SELECT TO_CHAR (1890.55, '$99G999D99') FROM DUAL
Suggested Answer: ACE
 
 
Question #134Topic 1
A subquery is called a single-row subquery when _______.

 A. There is only one subquery in the outer query and the inner query returns one or
more values
 B. The inner query returns a single value to the outer query.
 C. The inner query uses an aggregating function and returns one or more values.
 D. The inner query returns one or more values and the outer query returns a single
value.
Suggested Answer: B 
 
 
Question #135Topic 1
You must write a query that prompts users for column names and conditions every time it
is executed.
The user must be prompted only once for the table name.
Which statement achieves those objectives?

 A. SELECT &col1, '&col2' FROM &table WHERE &&condition = '&cond';

 B. SELECT &col1, &col2 FROM "&table" WHERE &condition = &cond;
 C. SELECT &col1, &col2 FROM &&table WHERE &condition = &cond;
 D. SELECT &col1, &col2 FROM &&table WHERE &condition = &&cond
Suggested Answer: C 
 
 
Question #136Topic 1
Which three statements are true regarding single-row functions? (Choose three.)

 A. The data type returned, can be different from the data type of the argument that
is referenced.
 B. They can return multiple values of more than one data type.
 C. They can accept only one argument.
 D. They can be nested up to only two levels.
 E. They can be used in SELECT, WHERE, and ORDER BY clauses.
 F. They can accept column names, expressions, variable names, or a user-supplied
constants as arguments.
Suggested Answer: AEF 
 
 
Question #137Topic 1
View the Exhibit and examine the structure in the DEPARTMENTS tables. (Choose two.)

Examine this SQL statement:

SELECT department_id "DEPT_ID", department_name, 'b' FROM

Departments
WHERE departments_id=90 -
UNION -
SELECT department_id, department_name DEPT_NAME, 'a' FROM
Departments
WHERE department_id=10 -

Which two ORDER BY clauses can be used to sort the output?

 A. ORDER BY DEPT_NAME;
 B. ORDER BY DEPT_ID;
 C. ORDER BY 'b';
 D. ORDER BY 3;
Suggested Answer: BD
 
 
 
Question #138Topic 1
Which two statements are true regarding the WHERE and HAVING clauses in a SELECT
statement? (Choose two.)

 A. The WHERE and HAVING clauses can be used in the same statement only if they
are applied to different columns in the table.
  B. The aggregate functions and columns used in the HAVING clause must be
specified in the SELECT list of the query.
 C. The WHERE clause can be used to exclude rows after dividing them into groups.
 D. The HAVING clause can be used with aggregate functions in subqueries. Most
Voted
 E. The WHERE clause can be used to exclude rows before dividing them into
groups. Most Voted
Suggested Answer: DE
 
 
 
Question #139Topic 1
You must create a table EMPLOYEES in which the values in the columns EMPLOYEES_ID
and LOGIN_ID must be unique and not null.
Which two SQL statements would create the required table? (Choose two.)

 A. CREATE TABLE employees (employee_id NUMBER, login_id NUMBER,

employee_name VARCHAR2(100), hire_date DATE, CONSTRAINT emp_id_uk
UNIQUE (employee_id, login_id));
 B. CREATE TABLE employees (employee_id NUMBER, login_id NUMBER,
employee_name VARCHAR2(25), hire_date DATE, CONSTRAINT emp_id_pk
PRIMARY KEY (employee_id, login_id));
 C. CREATE TABLE employees (employee_id NUMBER CONSTRAINT emp_id_pk
PRIMARY KEY, login_id NUMBER UNIQUE, employee_name VARCHAR2(25),
hire_date DATE);
 D. CREATE TABLE employees (employee_id NUMBER, login_id NUMBER,
employee_name VARCHAR2(100), hire_date DATE, CONSTRAINT emp_id_uk
UNIQUE (employee_id, login_id); CONSTRAINT emp_id_nn NOT NULL (employee_id,
login_id));
 E. CREATE TABLE employees (employee_id NUMBER CONSTRAINT emp_id_nn NOT
NULL, login_id NUMBER CONSTRAINT login_id_nn NOT NULL, employee_name
VARCHAR2(100), hire_date DATE, CONSTRAINT emp_num_id_uk UNIQUE
(employee_id, login_id));
Suggested Answer: BE 
 
 
Question #140Topic 1
View the Exhibit and examine the structure of the PRODUCT_INFORMATION table. (Choose
the best answer.)
PRODUCT_ID column is the primary key.
You create an index using this command:
SQL > CREATE INDEX upper_name_idx
ON product_information(UPPER(product_name));
No other indexes exist on the PRODUCT_INFORMATION table.
Which query would use the UPPER_NAME_IDX index?

 A. SELECT product_id, UPPER(product_name) FROM product_information WHERE

UPPER(product_name) = 'LASERPRO' OR list_price > 1000;
 B. SELECT UPPER(product_name) FROM product_information;
 C. SELECT UPPER(product_name) FROM product_information WHERE product_id =
2254;
 D. SELECT product_id FROM product_information WHERE UPPER(product_name) IN
('LASERPRO', 'CABLE');
Suggested Answer: D
 
 
Question #141Topic 1
Examine the types and examples of relationship that follow:
1 One-to-one a) teacher to Student
2 One-to-many b) Employees to Manager
3 Many-to-one c) Person to SSN
4 Many-to-many d) Customers to Products
Which option indicates correctly matched relationships?

 A. 1-d, 2-b, 3-a, and 4-c

 B. 1-c, 2-d, 3-a, and 4-b
  C. 1-a, 2-b, 3-c, and 4-d
 D. 1-c, 2-a, 3-b, and 4-d Most Voted
Suggested Answer: D
 
Question #142Topic 1
A non-correlated subquery can be defined as __________. (Choose the best answer.)

 A. A set of one or more sequential queries in which generally the result of the inner
query is used as the search value in the outer query.
 B. A set of sequential queries, all of which must return values from the same table.
 C. A set of sequential queries, all of which must always return a single value.
 D. A SELECT statement that can be embedded in a clause of another SELECT
statement only.
Suggested Answer: A
 
 
Question #143Topic 1
Which three statements are true reading subqueries? (Choose three.)

 A. A Main query can have many subqueries.

 B. A subquery can have more than one main query.
 C. The subquery and main query must retrieve date from the same table.
 D. The subquery and main query can retrieve data from different tables.
 E. Only one column or expression can be compared between the subquery and main
query.
 F. Multiple columns or expressions can be compared between the subquery and
main query.
Suggested Answer: ADF
 
Question #144Topic 1
See the Exhibit and examine the structure of the PROMOTIONS table:
Using the PROMOTIONS table,
you need to find out the average cost for all promos in the range $0-2000 and $2000-5000
in category A.
You issue the following SQL statements:

What would be the outcome?

 A. It generates an error because multiple conditions cannot be specified for the
WHEN clause.
 B. It executes successfully and gives the required result.
 C. It generates an error because CASE cannot be used with group functions.
 D. It generates an error because NULL cannot be specified as a return value.
Suggested Answer: B 
 
 
Question #145Topic 1
Which two statements are true regarding multiple-row subqueries? (Choose two.)

 A. They can contain group functions.

 B. They always contain a subquery within a subquery.
 C. They use the < ALL operator to imply less than the maximum.
 D. They can be used to retrieve multiple rows from a single table only.
 E. They should not be used with the NOT IN operator in the main query if NULL is
likely to be a part of the result of the subquery.
Suggested Answer: AE 
 
 
Question #146Topic 1
View the Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables.

The CUSTOMERS table contains the current location of all currently active customers.
The CUST_HISTORY table stores historical details relating to any changes in the location of
all current as well as previous customers who are no longer active with the company.
You need to find those customers who have never changed their address.
Which SET operator would you use to get the required output?

 A. INTERSECT
 B. UNION ALL
 C. MINUS
 D. UNION
Suggested Answer: C 
 
 
Question #147Topic 1
View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.
You executed the following query to display PRODUCT_NAME and the number of times the
product has been ordered:
SELECT p.product_name, i.item_cnt
FROM (SELECT product_id, COUNT (*) item_cnt
FROM order_items -
GROUP BY product_id) i RIGHT OUTER JOIN products p
ON i.product_id = p.product_id;
What would happen when the above statement is executed?

 A. The statement would execute successfully to produce the required output.

 B. The statement would not execute because inline views and outer joins cannot be
used together.
 C. The statement would not execute because the ITEM_CNT alias cannot be
displayed in the outer query.
 D. The statement would not execute because the GROUP BY clause cannot be used
in the inline.
Suggested Answer: A 
 
 
 
Question #148Topic 1
Which statement is true regarding the UNION operator?

 A. By default, the output is not sorted.

 B. Null values are not ignored during duplicate checking.
  C. Names of all columns must be identical across all select statements.
 D. The number of columns selected in all select statements need not be the same.
Suggested Answer: B
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
41
42
43
44
45
46
47
53
59
60
64
68
70
73
74
75
76
81
99
101
106
107
110
111
117
118
121
122
133
145
148