Which task can be performed by using a single Data Manipulation Language (DML)
statement?

A.

adding a column constraint when inserting a row into a table

B.

adding a column with a default value when inserting a row into a table

C.

removing all data only from one single column on which a unique constraint is defined

D.

removing all data only from one single column on which a primary key constraint is
defined

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

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?
http://selfexamtraining.com/uploadimages/1z0-071-Q-51.jpg

A.

SELECT course_id, department_id, FROM department_details d RIGHT OUTER JOIN
course_details c USING (department_id)

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)

You must create a table for a banking application. (Choose the best answer.)
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

Which two partitioned table maintenance operations support asynchronous Global Index
Maintenance in Oracle database 12c?

A.

ALTER TABLE SPLIT PARTITION

B.

ALTER TABLE MERGE PARTITION

C.

ALTER TABLE TRUNCATE PARTITION

D.

ALTER TABLE ADD PARTITION

E.

ALTER TABLE DROP PARTITION

F.

ALTER TABLE MOVE PARTITION

View the Exhibit and examine the structure of CUSTOMERS table.
Using the CUSTOMERS table, you need to generate a report that shows an increase in the
credit limit by 15% for all customers. Customers whose credit limit has not been entered
should have the message "Not Available" displayed.
Which SQL statement would produce the required result?

A.

SELECT NVL (TO CHAR(cust_credit_limit * .15), 'Not Available') "NEW CREDIT"FROM
customers;

B.

SELECT TO_CHAR (NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT"FROM
customers;

C.

SELECT NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT"FROM customers;

D.

SELECT NVL(cust_credit_limit), 'Not Available') "NEW CREDIT"FROM customers;

View the exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS
tables.
EMPLOYEES
NameNull?Type
----------- -------
EMPLOYEE_IDNOT NULLNUMBER(6)
FIRST_NAMEVARCHAR2(20)
LAST_NAMENOT NULLVARCHAR2(25)
HIRE_DATENOT NULLDATE
JOB_IDNOT NULLVARCHAR2(10)
SALARYNUMBER(10,2)
COMMISSIONNUMBER(6,2)
MANAGER_IDNUMBER(6)
DEPARTMENT_IDNUMBER(4)
DEPARTMENTS
NameNull?Type
----------- -------
DEPARTMENT_IDNOT NULLNUMBER(4)
DEPARTMENT_NAMENOT NULLVARCHAR2(30)
MANAGER_IDNUMBER(6)
LOCATION_IDNUMBER(4)
You want to update EMPLOYEES table as follows:
You issue the following command:
SQL> UPDATE employees
SET department_id =
(SELECT department_id
FROM departments
WHERE location_id = 2100),
(salary, commission) =
(SELECT 1.1*AVG(salary), 1.5*AVG(commission)
FROM employees, departments
WHERE departments.location_id IN(2900, 2700, 2100))
WHERE department_id IN
(SELECT department_id
FROM departments
WHERE location_id = 2900
OR location_id = 2700;
What is outcome?

A.

It generates an error because multiple columns (SALARY, COMMISSION) cannot be
specified together in an UPDATE statement.

B.

It generates an error because a subquery cannot have a join condition in a UPDATE
statement.

C.

It executes successfully and gives the desired update

D.

It executes successfully but does not give the desired update

Which three statements are true about multiple-row subqueries?

A.

They can contain a subquery within a subquery.

B.

They can return multiple columns as well as rows.

C.

 They cannot contain a subquery within a subquery.

D.

They can return only one column but multiple rows.

E.

They can contain group functions and GROUP BY and HAVING clauses.

F.

They can contain group functions and the GROUP BY clause, but not the HAVING
clause.

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.

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.

E.

The WHERE clause can be used to exclude rows before dividing them into groups.

Evaluate the following SQL statement
SQL> select cust_id, cust_last_name "Last name"
FROM customers
WHERE country_id = 10
UNION
SELECT cust_id CUST_NO, cust_last_name
FROM customers
WHERE country_id = 30
Identify three ORDER BY clauses either one of which can complete the query.

A.

ORDER BY "Last name"

B.

ORDER BY 2, cust_id

C.

ORDER BY CUST_NO

D.

ORDER BY 2, 1

E.

ORDER BY "CUST_NO"

You execute the SQL statement:
SQL> CREATE TABLE citizens
(citizen_id CHAR (10) PRIMARY KEY,
last_name VARCHAR2 (50) NOT NULL,
first_name VARCHAR2 (50),
address VARCHAR2 (100),
city VARCHAR2 (30) DEFAULT ‘SEATTLE’ NOT NULL,
CONSTRAINT cnames CHECK (first_name<>last_name) );
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 CANAMES constraint is not valid.

D.

It succeeds and an index is crated for CITIZEN_ID.