Exam 1Z0-071: IT Certification Guaranteed, The Easy Way!

IT Certification Guaranteed, The Easy Way!
Exam : 1Z0-071
Title : Oracle Database SQL
Vendor : Oracle
Version : V15.75
1
NO.1 Which three statements are true about inner and outer joins?
A. A full outer join returns matched and unmatched rows.
B. A full outer join must use Oracle syntax.
C. Outer joins can be used when there are multiple join conditions on two tables.
D. Outer joins can only be used between two tables per query.
E. An inner join returns matched rows.
F. A left or right outer join returns only unmatched rows.
Answer: A,C,E
NO.2 Which two statements are true about outer Joins?

A. The outer join operator (+) can be used on both sides of the join condition in an outer join.
B. An outer join is used to retrieve only the rows that do not meet the join condition.
C. The IN operator cannot be used in a condition that Involves an outer join.
D. A condition representing an outer join cannot be linked to another condition using the or logical
operator.
E. The outer join operator (+) is used next to the column of the table without the matching rows.
Answer: C,D
NO.3 Examine this statement:

SELECT1 AS id,' John' AS first_name, NULL AS commission FROM dual
INTERSECT
SELECT 1,'John' null FROM dual ORDER BY 3;
What is returned upon execution?[
A. 2 rows
B. 0 rows
C. An error
D. 1 ROW
Answer: D
NO.4 Which two are true about rollbacks?

A. The ROLLBACK statement does not release locks resulting from table updates.
B. Data Control L anguage (DCL) statements, such as GRANT and REVOKE, can be rolled back.
C. A transaction interrupted by a system failure is automatically rolled back.
D. If the ROLLBACK statement is used without TO SAVEPOINT, then all savepoints in the transaction
are deleted .
E. Data consistency is not guaranteed after a rollback.
Answer: C,D
NO.5 Which two statements are true about Entity Relationships?

A. A Relationship can be mandatory for both entities
B. A one-to-one relationship is always a self-referencing relationship
C. A many-to-many relationship can be implemented only by using foreign keys
D. A table name can be specified just once when selecting data from a table having a selfreferencing
2
relationship
E. A one-to-many relatonship in one direction is a one-to-one relationship in the other direction
Answer: A,C
NO.6 Examine this statement which executes successfully:

Which three are true?
A. Regardless of salary,only if the employee id is less than 125,insert
EMPLOYEE_ID,NANAGER_ID,SALARY into the MGR_HISTORY table.
B. If the salary is more than 20000 and the employee is less than 125,insert EMPLOYEE_ID and
SALARY into the SPECIAL_SAL table.
C. Only if the salary is 20000 or less and the employee id is less than 125,insert
EMPLOYEE_ID,MANAGER_ID,and SALARY into the MGR_HISTORY table.
D. Regardless of salary and employee id,insert EMPLOYEE_ID,MANAGER_ID,and SALARY into the
MGR_HISTORY table.
E. If the salary is 20000 or less and the employee id is less than 125,insert
EMPLOYEE_ID,HIRE_DATE,and SALARY into the SAL_HISTORY table.
F. Only if the salary is 20000 or less and the employee id is 125 or higher,insert
EMPLOYEE_ID,MANAGER_ID,and SALARY into the MDR_HISTORY table.
Answer: A,B,E
NO.7 Table EMPLOYEES contains columns including EMPLOYEE_ID, JOB_ID and SALARY.
Only the EMPLOYEES_ID column is indexed.
Rows exist for employees 100 and 200.
Examine this statement:
UPDATE employees
SET (job_id, salary) =
(SELECT job_id, salary
FROM employees
WHERE employee_id = 200)
WHERE employee id=100;
Which two statements are true?
A. Employees 100 and 200 will have the same SALARY as before the update command.
B. Employee 100 will have SALARY set to the same value as the SALARY of employee 200.
C. Employee 100 will have JOB_ID set to the same value as the JOB_ID of employee 200.
D. Employees 100 and 200 will have the same JOB ID as before the update command.
E. Employee 200 will have SALARY set to the same value as the SALARY of employee 100.
F. Employee 200 will have JOB_ID set to the same value as the JOB_ID of employee 100
Answer: B,C
NO.8 Examine thee statements which execute successfully:

CREATE USER finance IDENTIFIED BY pwfin;
CREATE USER fin manager IDENTIETED BY pwmgr;
CREATE USER fin. Clerk IDENTIFIED BY pwclerk;
GRANT CREATE SESSON 20 finance, fin clerk;
3
GRANT SELECT ON scott. Emp To finance WITH GRANT OPTION;

CONNECT finance/pwfin
GRANT SELECT ON scott. emp To fin_ _clerk;
Which two are true?
A. Dropping user FINANCE will automatically revoke SELECT on SCOTT. EMP from user FIN _ CLERK
B. Revoking SELECT on SCOTT. EMP from user FINANCE will also revoke the privilege from user FIN_
CLERK.
C. User FINANCE can grant CREATE SESSION to user FIN MANAGER.
D. User FIN CLERK can grant SELECT on SCORT, ENP to user FIN MANAGER.
E. User FINANCE is unable to grant ALL on SCOTT.ENP to FIN MANAGER.
Answer: B,E
NO.9 Examine the description of the PRODUCT_ DETAILS table:

A. PRODUCT_ PRICE can be used in an arithmetic expression even if it has no value stored in it.
B. PRODUCT_ ID can be assigned the PRIMARY KEY constraint.
C. EXPIRY_ DATE cannot be used in arithmetic expressions.
D. EXPIRY_ DATE contains the SYSDATE by default if no date is assigned to it.
E. PRODUCT_ PRICE contains the value zero by default if no value is assigned to it.
F. PRODUCT_ NAME cannot contain duplicate values.
Answer: A,B
NO.10 Which two statements are true about a self join?

A. The join key column must have an index.
B. It can be a left outer join.
C. It must be a full outer join.
D. It can be an inner join.
E. It must be an equijoin.
Answer: B,D
NO.11 Examine the description of the MEMBERS table;

SELECT city,last_name LNAME FROM members ...
You want to display all cities that contain the string AN. The cities must be returned in ascending
order, with the last names further sorted in descending order.
Which two clauses must you add to the query?
A. ORDER BY 1,2.
B. ORDER BY last_name DESC,city ASC
4
C. CORADER BY 1, LNAME DESC

D. WHERE city='%AN%;
E. WHERE city LIKE '%AN%;
F. WHERE city IN ('%AN%')
Answer: C,E
NO.12 Examine the description or the BOOKS_TRANSACTIONS table:
FOR customers whose income level has a value, you want to display the first name and due amount
as 5% of their credit limit. Customers whose due amount is null should not be displayed.
Which query should be used?
A. SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT
FROM customers
WHERE cust income_level !=NULL
AND cust credit_level !=NULL;
B. SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT
FROM customers
WHERE cust income_level IS NOT NULL
AND due_amount IS NOT NULL;
C. SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT
FROM customers
WHERE cust income_level <> NULL
AND due_amount <> NULL;
D. SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT
FROM customers
WHERE cust_income_level IS NOT NULL
AND cust_credit_limit IS NOT NULL;
E. SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT
FROM customers
WHERE cust income_level !=NULL
AND due_amount !=NULL;
Answer: D
NO.13 Which two statements are true about external tables?

A. Indexes can be created on them.
B. You can populate them from existing data in the database by using the CREATE TABLE AS SELECT
command.
C. DML statements cannot be used on them.
D. Their data can be retrieved by using only SQL or PL/SQL.
5
E. Their metadata and actual data are both stored outside the database.
Answer: B,C
NO.14 Examine the description of the sales table.

The sales table has 55,000 rows.
Examine this statements:
A. SALES1 has PRIMARY KEY and UNIQUE constraints on any selected columns which had those
constraints in the SALES table.
B. SALES1 created with 55, 000 rows
C. SALES1 created with no rows.
D. SALES1 created with 1 row.
E. SALES1 has NOT NULL constraints on any I selected columns which had those constraints I in the
SALES table.
Answer: B,E
NO.15 Which three are true about privileges?

A. Schema owners can grant object privileges on objects in their schema to any other user or role.
B. A combination of object and system privileges can be granted to a role.
C. All types of schema objects have associated object privileges .
D. Only users with the DBA role can create roles .
E. Object privileges granted on a table automatically apply to all synonyms for that table.
F. Only users with the GRANT ANY PRIVILEGE privilege can grant and revoke system privileges from
other users.
Answer: A,B,C
NO.16 Which three statements are true about GLOBAL TEMPORARY TABLES?
A. A GLOBAL TEMPORARY TABLE cannot have PUBLIC SYNONYM.
B. A GLOBAL TEMPORARY TABLE can have multiple indexes
C. A GLOBAL TEMPORARY TABLE can be referenced in the defining query of a view.
D. Data Manipulation Language (DML) on GLOBAL TEMPORARY TABLES generates no REDO.
E. A GLOBAL TEMPORARY TABLE can have only one index.
F. A trigger can be created on a GLOBAL TEMPORARY TABLE
Answer: B,C,F
NO.17 Which two statements execute successfully?

A. SELECT TO_ DATE('2019-DEC-25 15:30', 'YYYY-MON-DD HH24:MI', 'NLS_ DATE_ LANGUAGE
=AMERICAN' ) FROM DUAL;
B. SELECT TO_CHAR('2019-DEC-25 15:30", YY-MON-D HH24:M2', 'NLS_DATE LANGUAGE =
AMERICAN')
FROM DUAL;
C. SELECT TO _DATE (TO_ CHAR ('2019-DEC-25 03:30', 'YYYY-MON-DD HH12:MI'))
FROM DUAL;
6
D. SELECT TO _ CHAR (TO_ DATE ('2019-DEC-25 03:30','YYYY-MON-DD HH12:MI'))

FROM DUAL
E. SELECT TO _ CHAR ('2019-DEC-25 15:30'.'YYYY-MON-DD HH24:MI')
FROM DUAL
Answer: A,D
NO.18 Which three statements are true about Data Manipulation Language (DML)?
A. delete statements can remove multiple rows based on multiple conditions.
B. insert statements can insert nulls explicitly into a column.
C. insert into. . .select. . .from statements automatically commit.
D. DML statements require a primary key be defined on a table.
E. update statements can have different subqueries to specify the values for each updated column.
Answer: A,B,E
NO.19 Which two are true about global temporary tables?

A. They can be created only by a user with the DBA role,but can be accessed by all users who can
create a session.
B. Backup and recovery operations are available for these tables.
C. If the ON COMMIT clause is session-specific,the table is dropped when the session is terminated.
D. Their data is always stored in the default temporary tablespace of the user who created them.
E. Indexes can be created on them.
F. If the ON COMMIT clause Is transaction-specific, all rows in the table are deleted alter each
COMMIT or ROLLBACK.
Answer: C,F
NO.20 Which two statements will do an implicit conversion?

A. SELECT * FROM customers WHERE customer_ id = 0001 ;
B. SELECT * FROM customers WHERE customer id = '0001';
C. SELECT * FROM customers WHERE insert_ date = DATE '2019-01-01';
D. SELECT * FROM customers WHERE insert date ='01-JAN-19'
E. SELECT * FROM customers WHERE TO_ CHAR (customer_ id) ='0001';
Answer: B,D

Which statement will violate the CHECK constraint?
A. UPDATE emp80
SET department_id=90
WHERE department_id=80;
B. DELETE FROM emp80
C. SELECT *
FROM emp80
7
D. SELECT *
FROM emp80
Answer: A
NO.22 Which two queries return the string Hello! we're ready?
A. SELECT q'! Hello! We're ready! 'FROM DUAL;
B. SELECT "Hello! We're ready "FROM |DUAL;
C. SELECT q'[Hello! We're ready]'FROM DUAL;
D. SELECT 'Hello! we\ re ready' ESCAPE'N'FROMDUAL:
E. SELECT 'Hello! We're ready' FROM DUAL;
Answer: A,C
NO.23 which three statements are true about indexes and their administration in an Oracle
database?
A. The same table column can be part of a unique and non-unique index
B. A DESCENDING INDEX IS A type of function-based index
C. A DROP INDEX statement always prevents updates to the table during the drop operation
D. AN INVISIBLE INDEX is not maintained when DML is performed on its underlying table.
E. AN INDEX CAN BE CREATED AS part of a CREATE TABLE statement
F. IF a query filters on an indexed column then it will always be used during execution of query
Answer: B,C,E
NO.24 Which three statements are true about views in an Oracle database?
A. The WITH CHECK clause prevents certain rows from being displayed when querying the view.
B. The WITH CHECK clause prevents certain rows from being updated or inserted.
C. Tables in the defining query of a view must always exist in order to create the view.
D. Date Manipulation Language (DML) can always be used on views.
E. Deleting one or more rows using a view whose defining query contains a GROUP BY clause will
cause an error.
F. Views can be updated without the need to re-grant privileges on the view.
G. Inserting one or more rows using a view whose defining query contains a GROUP BY clause will
cause an error.
Answer: B,E,F
NO.25 Examine these statements:

CREATE TABLE dept (
deptno NUMBER PRIMARY KEY,
diname VARCHAR2(10) ,
mgr NUMBER ,
CONSTRAINT dept_fkey FOREIGN KEY(mgr) REFERENCES emp (empno));
CREATE TABLE emp (
Empno NUMBER PRIMARY KEY,
Ename VARCHAR2 (10) ,
8
deptno NUMBER,
CONSTRAINT emp_fkey FOREIGN KEY (deptno) REFERENCES dept (deptno) DISABLE);
ALTER TABLE emp MODIFY CONSTRAINT emp_fkey ENABLE;
Which two are true?
A. The MGR column in the DEPT table will not be able to contain NULL values.
B. The CREATE TABLE EMP statement must precede the CREATE TABLE DEPT statement for all
threestatements to execute successfully.
C. Both foreign key constraint definitions must be removed from the CREATE TABLE statements,
andbe added with ALTER TABLE statements once both tables are created, for the two CREATE
TABLEstatements to
execute successfully in the order shown.
D. The DEFT FKEY constraint definition must be removed from the CREATE TABLE DEF statement.and
be added with an AITER TABLE statement once both tables are created, for the two CREATE TABLE
statements
to execute successfully in the order shown.
E. The Deptno column in the emp table will be able to contain nulls values.
F. All three statements execute successfully in the order shown
Answer: D,E
NO.26 Examine this query which executes successfully:

SELECT job, deptno FROM emp
UNION ALL
SELECT job, deptno FROM jobs_ history;
What will be the result?
A. It will return rows common to both SELECT statements.
B. It will return rows from both SELECT statements after eliminating duplicate rows.
C. It will return rows that are not common to both SELECT statements.
D. It will return rows from both SELECT statements including duplicate rows.
Answer: D
NO.27 An Oracle database server session has an uncommitted transaction in progress which
updated 5000 rows
in a table.
In which three situations does the transact ion complete thereby committing the updates?
A. When the session logs out is successfully
B. When a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a
COMMIT
C. When a CREATE INDEX statement is executed successfully in same session
D. When a COMMIT statement is issued by the same user from another session in the same database
instance
E. When a CREATE TABLE AS SELECT statement is executed unsuccessfully in the same session
F. When a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user, then issues
a COMMIT
Answer: A,C,F
9
NO.28 Which three statements are true about dropping and unused columns in an Oracle database?
A. A primary key column referenced by another column as a foreign key can be dropped if using the
CASCADE option.
B. A DROP COLUMN command can be rolled back.
C. An UNUSED column's space is remained automatically when the block containing that column is
next queried.
D. An UNUSED column's space is remained automatically when the row containing that column is
next queried.
E. Partition key columns cannot be dropped.
F. A column that is set to NNUSED still counts towards the limit of 1000 columns per table.
Answer: A,E,F
NO.29 You create a table named 123.

Which statement runs successfully?
A. SELECT * FROM TABLE (123) ;
B. SELECT * FROM '123';
C. SELECT * FROM "123";
D. SELECT * FROM V'123V';
Answer: C
NO.30 Which statement falls to execute successfully?

A. SELECT *
FROM employees e
JOIN department d
WHERE e.department_id=d.department_id
AND d.department_id=90;
B. SELECT *
FROM employees e
JOIN departments d
ON e.department_id=d.department_id
WHERE d.department_id=90;
C. SELECT *
FROM employees e
JOIN departments d
ON e.department_id=d.department_id
AND d.department_id=90;
D. SELECT *
FROM employees e
JOIN departments d
ON d.departments_id=90
WHERE e.department_id=d.department_id;
Answer: D
10
NO.31 Which two are true about the data dictionary?

A. Base tables in the data dictionary have the prefix DBA_.
B. All user actions are recorded in the data dictionary.
C. The data dictionary is constantly updated to reflect changes to database objects, permissions, and
data.
D. All users have permissions to access all information in the data dictionary by default
E. The SYS user owns all base tables and user-accessible views in the data dictionary.
Answer: C,E
NO.32 You execute this command:

TRUNCATE TABIE depts;
Which two are true?
A. A ROLLBACK statement can be used to retrieve the deleted data.
B. It drops any triggers defined on the table.
C. It retains the indexes defined on the table.
D. It retains the integrity constraints defined on the table,
E. It always retains the space used by the removed rows.
F. A FLASHBACK TABLE statement can be used to retrieve the deleted data.
Answer: C,D
NO.33 Which three statements are true regarding indexes?

A. A SELECT statement can access one or more indices without accessing any tables.
B. A table belonging to one user can have an index that belongs to a different user,
C. When a table is dropped and is moved to the RECYCLE BIN, all Indexes built on that table are
permanently dropped.
D. A UNIQUE index can be altered to be non-unique.
E. An update to a table can result in no updates to any of the table's indexes.
F. An update to a table can result in updates to any or all of the table's indexes.
Answer: B,C,E
NO.34 Which two statements are true about * _TABLES views?

A. You must have ANY TABLE system privileges, or be granted object privilges on the table, to viewa
tabl e in DBA TABLES.
B. USER TABLES displays all tables owned by the current user.
C. You must have ANY TABLE system privileges, or be granted object privileges on the table, to view a
table in USER_TABLES.
D. ALL TABLES displays all tables owned by the current user.
E. You must have ANY TABLE system privileges, or be granted object privileges on the table, to view a
table in ALL_TABLES.
F. All users can query DBA_TABLES successfully.
Answer: A,B
NO.35 Examine the command to create the BOOKS table.
11
SQL> create table books(book id CHAR(6) PRIMARY KEY,

title VARCHAR2(100) NOT NULL,
publisher_id VARCHAR2(4),
author_id VARCHAR2 (50));
The BOOK ID value 101 does not exist in the table.
Examine the SQL statement.
insert into books (book id title, author_id values
('101','LEARNING SQL','Tim Jones')
A. It executes successfully and the row is inserted with a null PLBLISHER_ID.
B. It executes successfully only if NULL is explicitly specified in the INSERT statement.
C. It executes successfully only NULL PUBLISHER_ID column name is added to the columns list in the
INSERT statement.
D. It executes successfully onlyif NULL PUBLISHER ID column name is added to the columns list and
NULL is explicitly specified In the INSERT statement.
Answer: A
NO.36 Which three statements are true about GLOBAL TEMPORARY TABLES?
A. GLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose
user has been granted select on the table.
B. A TRUNCATE command issued in a session causes all rows In a GLOBAL TEMPORARY TABLE for the
issuing session to be deleted.
C. A DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back.
D. A GLOBAL TEMPORARY TABLE's definition is available to multiple sessions.
E. Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted.
F. GLOBAL TEMPORARY TABLE space allocation occurs at session start.
Answer: B,D,F
NO.37 Which three statements are true regarding single row subqueries?
A. They must be placed on the left side of the comparison operator or condition.
B. They must return a row to prevent errors in the SQL statement.
C. A SQL statement may have multiple single row subquery blocks.
D. They can be used in the HAVING clause.
E. They must be placed on the right side of the comparison operator or condition.
F. They can be used in the clause.
Answer: C,D,F
NO.38 Which two statements are true about the DUAL table?
A. It can display multiple rows and columns.
B. It can be accessed only by the SYS user.
C. It can be accessed by any user who has the SELECT privilege in any schema
D. It can display multiple rows but only a single column.
E. It consists of a single row and single column of VARCHAR2 data type.
F. It can be used to display only constants or pseudo columns.
12
Answer: A,C
NO.39 You own table DEPARTMENTS, referenced by views, indexes, and synonyms.
Examine this command which executes successfully:
DROP TABLE departments PURGE;
Which three statements are true?
A. Neither can it be rolled back nor can the DEPARTMENTS table be recovered.
B. It will remove all views that are based on the DEPARTMENTS table.
C. It will delete all rows from the DEPARTMENTS table, but retain the empty table.
D. It will remove the DE PARTMENTS table from the database.
E. It will remove all synonyms for the DEPARTMENTS table.
F. It will drop all indexes on the DEPARTMENTS table.
Answer: A,D,F
NO.40 Examine the description of the EMPLOYEES table:
Which two queries return rows for employees whose manager works in a different department?
A. SELECT emp. *
FROM employees emp
WHERE manager_ id NOT IN (
SELECT mgr.employee_ id
FROM employees mgr
WHERE emp. department_ id < > mgr.department_ id
);
B. SELECT emp.*
FROM employees emp
WHERE NOT EXISTS (
SELECT NULL
FROM employees mgr
WHERE emp.manager id = mgr.employee_ id
AND emp.department_id<>mgr.department_id
);
C. SELECT emp.*
FROM employees emp
LEFT JOIN employees mgr
ON emp.manager_ id = mgr.employee_ id
AND emp. department id < > mgr. department_ id;
D. SELECT emp. *
FROM employees emp
RIGHT JOIN employees mgr
13
ON emp.manager_ id = mgr. employee id

AND emp. department id <> mgr.department_ id
WHERE emp. employee_ id IS NOT NULL;
E. SELECT emp. *
FROM employees emp
JOIN employees mgr
ON emp. manager_ id = mgr. employee_ id
AND emp. department_ id<> mgr.department_ id;
Answer: D,E
NO.41 Examine the BRICKS table:
You write this query:

SELECT
FROM bricks b1 CROSS JOIN bricks b2
WHERE b1. Weight < b2. Weight:
How many rows will the query return?
A. 1
B. 16
C. 10
D. 6
E. 4
F. 0
Answer: D
NO.42 The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2.
The table has two rows whose COST_LAST_MANE values are Anderson and Ausson.
Which query produces output for CUST_LAST_SAME containing Oder for the first row and Aus for the
second?
A. SELECT REPLACE (REPLACE(cust_last_name,'son',''),'An','O') FROM customers;
B. SELECT REPLACE (TRIM(TRALING'son' FROM cust_last_name),'An','O') FROM customers;
C. SELECT INITCAP (REPLACE(TRIM('son' FROM cust_last_name),'An','O')) FROM customers;
D. SELECT REPLACE (SUBSTR(cust_last_name,-3),'An','O') FROM customers;
Answer: A
NO.43 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.
14
Which two are true about data type conversions involving these columns in query expressions?
A. invoice_date> '01-02-2019': uses implicit conversion
B. qty_sold ='05549821 ': requires explicit conversion
C. CONCAT(qty_sold, invoice_date): requires explicit conversion
D. qty_sold BETWEEN '101' AND '110': uses implicit conversion
E. invoice_date = '15-march-2019': uses implicit conversion
Answer: D,E
NO.44 Examine the description of the CUSTOMERS table:
You need to display last names and credit limits of all customers whose last name starts with A or B In
lower or upper case, and whose credit limit is below 1000.
Examine this partial query:
SELECT cust_last_nare, cust_credit_limit FROM customers
Which two WHERE conditions give the required result?
A. WHERE UPPER(cust_last_name) IN ('A%', 'B%') AND cust_credit_limit < 1000:
B. WHERE (INITCAP(cust_last_name) LIKE 'A%' OR ITITCAP(cust_last_name) LIKE 'B%') AND
cust_credit_limit < 1000
C. WHERE UPPER(cust_last_name) BETWEEN UPPER('A%' AND 'B%') AND ROUND(cust_credit_limit) <
1000;
D. WHERE (UPPER(cust_last_name) LIKE 'A%' OR UPPER(cust_last_name) LIKE 'B%') AND
ROUND(cust_credit_limit) < 1000;
E. WHERE (UPPER(cust_last_name) like INITCAP ('A') OR UPPER(cust_last_name) like INITCAP('B'))
AND ROUND(cust_credit_limit) < ROUND(1000) ;
Answer: B,D
NO.45 Examine the description of the SALES table:
15
The SALES table has 5,000 rows.

Examine this statement:
CREATE TABLE sales1 (prod id, cust_id, quantity_sold, price)
AS
SELECT product_id, customer_id, quantity_sold, price
FROM sales
WHERE 1=1
A. SALES1 is created with 1 row.
B. SALES1 has PRIMARY KEY and UNIQUE constraints on any selected columns which had those
constraints in the SALES table.
C. SALES1 Is created with 5,000 rows.
D. SALES1 has NOT NULL constraints on any selected columns which had those constraints in the
SALES table.
Answer: C,D
NO.46 Examine these statements:

CREATE TABLE alter_test (c1 VARCHAR2(10), c2 NUMBER(10));
INSERT INTO alter_test VALUES ('123', 123);
COMMIT;
Which is true ahout modifyIng the columns in AITER_TEST?
A. c1 can be changed to NUMBER(10) and c2 can be changed to VARCHAN2 (10).
B. c2 can be changed to NUMBER(5) but c1 cannot be changed to VARCHAN2 (5).
C. c2 can be changed to VARCHAR2(10) but c1 cannot be changed to NUMBER (10).
D. c1 can be changed to NUMBER(10) but c2 cannot be changed to VARCHAN2 (10).
E. c1 can be changed to VARCHAR2(5) and c2 can be changed to NUMBER (12,2).
Answer: E
NO.47 Examine this description of the EMP table:
16
You execute this query:

SELECT deptno AS "departments", SUM (sal) AS "salary"
FROM emp
GROUP | BY 1
HAVING SUM (sal)> 3 000;
What is the result?
A. only departments where the total salary is greater than 3000, returned in no particular order
B. all departments and a sum of the salaries of employees with a salary greater than 3000
C. an error
D. only departments where the total salary is greater than 3000, ordered by department
Answer: C
NO.48 Which two queries execute successfully?

A. SELECT NULLIF(100, 100) FROM DUAL;
B. SELECT COALESCE(100, NULL, 200) FROM DUAL;
C. SELECT NULLIF(100, 'A') FROM DUAL;
D. SELECT NULLIF(NULL, 100) FROM DUAL;
E. SELECT COALESCE(100, 'A' ) FROM DUAL;
Answer: A,B
NO.49 Examine the description of the ENPLYEES table:
Which two queries return all rows for employees whose salary is greater than the average salary in
their department?
A. SELECT "
FROM employees
WHERE salary > ANY
SELECT AVG (salary)
EROM employees
GROUP BY department_ id);
17
B. SELECT
FROM employees
WHERE salary > AVG (salary) OVER (PARTITION BY department _ id);
C. SELECT"
FROM employees e1
WHERE salary >!
SELECT AVG (salary)
FROM employees e2
WHERE e1. Department _id = e2, department_ id
D. SELECT.
FROM
SELECT e.", AVG (salary) OVER (PARTITION BY department id) avg_ sal
FROM employees e
WHERE salary > avg_ sal;
E. SELECT"
FROM employees
WHERE salary >
( SELECT AVG
(salary) FROM
employees
GROUP BY department _ id
Answer: C,D
NO.50 Which three statements are true about the DESCRIBE command?
A. It can be used from SQL Developer.
B. It can be used to display the structure of an existing view.
C. It can be used only from SQL*Plus.
D. It displays the NOT NULL constraint for any columns that have that constraint.
E. It displays all constraints that are defined for each column.
F. It displays the PRIMARY KEY constraint for any column or columns that have that constraint.
Answer: A,B,D
NO.51 Examine this business rule:

Each student can work on multiple projects and each project can have multiple students.
You must design an Entity Relationship(ER) model for optimal data storage and allow for generating
reports in this format:

A. An associative table must be created with a composite key of STUDENT_ID and PROJRCT_ID, which
is the foreign key linked to the STUDENTS and PROJECTS entities.
B. PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS
entity.
C. The ER must have a 1-to-many relationship between the STUDENTS and PROJECTS entities.
D. The ER must have a many to-many relationship between the STUDENTS and PROJECTS entities
18
that must be resolved into 1-to-many relationships.

E. STUDENT ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS
entity.
Answer: A,D

TRUNCATE TABLE depts;
Which two are true?
A. It retains the indexes defined on the table.
B. It drops any triggers defined on the table.
C. A Flashback TABLE statement can be used to retrieve the deleted data.
D. It retains the integrity constraints defined on the table.
E. A ROLLBACK statement can be used to retrieve the deleted data.
F. It always retains the space used by the removed rows
Answer: A,D
NO.53 Examine the description of EMPLOYEES table:

Which three queries return all rows for which SALARY+COMMISSION is greate than 20000?
A. SELECT * FROM employees WHERE salary+NULLF(commission,0)>=20000;
B. SELECT * FROM employees WHERE salary+NVL2(commission,commission,0)>=20000;
C. SELECT * FROM employees WHERE NVL2(salary)+commission,salary+commission,
D. SELECT * FROM employees WHERE salary+NVL(commission,0)>=20000;
E. SELECT * FROM employees WHERE NVL(salary+commission,0)>=20000;
F. SELECT * FROM employees WHERE NVL(salary+commission,0)>==20000;
Answer: B,C,D
NO.54 Which three statements are true about time zones, date data types, and timestamp data
types in an Oracle database?
A. The DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC)
B. A TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the time
zone of the session that inserted the row
C. A TIMESTAMP data type column contains information about year, month, and day
D. The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC)
E. The CURRENT_TIMESTAMP function returns data without time zone information
Answer: A,B,D
NO.55 Examine the data in the EMPLOYEES table:
Which statement will compute the total annual compensation for each employee?
19
A. SELECT last _ NAME (monthly_ salary + monthly _commission _ pct) * 12 AS annual_ comp FROM
employees;
B. select last _ name, (monthly_ salary * 12) + (monthly_ salary * 12 *monthly_ commission_ pct) AS
annual_ camp FROM employees
C. SELECT last _ name, (monthly_ salary * 12) + (monthly_ salary * 12 * NVL (monthly_ commission
_pct, 0)) AS annual _comp
D. SELECT last _ name, (monthly _ salary * 12) + (monthly_ commission _ pct * 12) AS FROM
employees;
Answer: C
NO.56 In which three situations does a new transaction always start?

A. When issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was
issued in the same session
B. When issuing a CREATE INDEX statement after a CREATE TABLE statement completed
unsuccessfully in the same session
C. When issuing a TRUNCATE statement after a SELECT statement was issued in the same session
D. When issuing a CREATE TABLE statement after a SELECT statement was issued in the same session
E. When issuing the first Data Manipulation Language (OML) statement after a COMMIT or
ROLLBACK statement was issued in the same session
F. When issuing a DML statement after a DML statement filed in the same session.
Answer: A,B,E
NO.57 Which three actions can you perform by using the ORACLE DATAPUMP access driver?
A. Create a directory object for an external table.
B. Read data from an external table and load it into a table in the database.
C. Query data from an external table.
D. Create a directory object for a flat file.
E. Execute DML statements on an external table.
F. Read data from a table in the database and insert it into an external table.
Answer: A,C,F
NO.58 In your session NLS_ DATE_ FORMAT is set to DD-MON_RR.

Which two queries display the year as four digits?
A. SELECT TO_DATE(TO_CHAR(SYSDATE,'MM/DD/YYYY'),'MM/DD/YYYY') FROM DUAL;
B. SELECT TO_CHAR (ADD_MONTHS (SYSDATE,6)) FROM DUAL;
C. SELECT TO_DATE (SYSDATE, 'RRRR-MM-DD') FROM DUAL;
D. SELECT TO_DATE (ADD_MONTHS(SYSDATE,6), 'dd-mon-yyyy') FROM DUAL;
E. SELECT TO_CHAR (SYSDATE, 'MM/DD/YYYY') FROM DUAL;
F. SELECT TO_CHAR (ADD_MONTHS (SYSDATE, 6), 'dd-mon-yyyy') FROM DUAL;
Answer: E,F
NO.59 Examine the description of the PRODCTS table which contains data:
20
Which two are true?

A. The PROD ID column can be renamed.
B. The PROD_ ID column data type can be changed to VARCHAR2 (2).
C. The EXPIRY DATE column data type can be changed to TIME STAMP.
D. The EXPIRY DATE column cannot be dropped.
E. The PROD NAME column cannot have a DEFAULT clause added to it.
Answer: A,C
Which statement will execute successfully, returning distinct employees with non-null first names?
A. SELECT DISTINCT * FROM employees WHERE first_ name IS NOT NULL;
B. SELECT first_ name, DISTNCT last_ name FROM employees WHERE first_ name IS NOT NULL;
C. SELECT Distinct * FROM employees WHERE first_ name < > NULL;
D. SELECT first_ name, DISTINCT last_ name FROM employees WHERE first_ name < > NULL;
Answer: A
NO.61 Examine the description of the transactions table:

Which two SQL statements execute successfully?
A. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS DATE, amount+100 "DUES" from
transactions;
B. SELECT customer_id AS 'CUSTOMER-ID',transaction_date AS DATE, amount+100 'DUES' from
transactions;
C. SELECT customer_id CUSTID, transaction_date TRANS_DATE,amount+100 DUES FROM
transactions;
D. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS "DATE", amount+100 DUES FROM
transactions;
E. SELECT customer id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount+100 "DUES
AMOUNT" FROM transactions;
Answer: C,D
NO.62 Which two statements are true about the ORDER BY clause?
21
A. Numeric values are displayed in descending order if they have decimal positions.
B. Only columns that are specified in the SELECT list can be used in the ORDER BY cause.
C. NULLS are not included in the sort operation.
D. Column aliases can be used In the ORDER BY cause.
E. Ina character sort, the values are case-sensitive.
Answer: D,E
NO.63 The ORDERS table has a primary key constraint on the ORDER_ID column.
The ORDER_ITEMS table has a foreign key constraint on the ORDER_ID column, referencing the
primary key of the ORDERS table.
The constraint is defined with on DELETE CASCADE.
There are rows in the ORDERS table with an ORDER_TOTAL less than 1000.
Which three DELETE statements execute successfully?
A. DELETE FROM orders WHERE order_total<1000;
B. DELETE * FROM orders WHERE order_total<1000;
C. DELETE orders WHERE order_total<1000;
D. DELETE FROM orders;
E. DELETE order_id FROM orders WHERE order_total<1000;
Answer: A,C,D
NO.64 You must find the number of employees whose salary is lower than employee 110.
Which statement fails to do this?
A. SELECT COUNT (*)
FROM employees
JOIN employees a
ON e. salary< a. salary
WHERE a. employee_ id= 110;
B. SELECT COUNT (* )
FROM employees
WHERE salary < (SELECT salary FROM employees WHERE employee.id =
110) ;
C. SELECT COUNT (*)
FROM employees e
JOIN (SELECT salary FROM employees WHERE employee_ id= 110) a
ON e. salary< a. salary;
D. SELECT COUNT (* )
FROM employees e
WHERE e. salary < (SELECT a. salary FROM employees a WHERE e. employee_ id = 110);
Answer: D
NO.65 The SYSDATE function displays the current Oracle Server date as:
21 -MAY-19
You wish to display the date as:
MONDAY, 21 MAY, 201 9
22
Which statement will do this?

A. SELECT TO _ CHAR (SYSDATE, ' FMDAY, DD MONTH, YYYY') FROM DUAL;
B. SELECT TO _ DATE (SYSDATE, ' FMDAY, DD MONTH, YYYY') FROM DUAL;
C. SELECT TO_ CHAR (SYSDATE, ' FMDD, DAY MONTH, YYYY') FROM DUAL;
D. SELECT TO_ CHAR (SYSDATE, ' FMDAY, DDTH MONTH, YYYY') FROM DUAL;
Answer: A

SELECT 1 AS id, ' John' AS first name
FROM DUAL
UNION
SELECT 1 , ' John' AS name
FROM DUAL
ORDER BY 1;
What is returned upon execution?
A. 0 rows
B. an error
C. 1 row
D. 2 rows
Answer: C
NO.67 Which two are true about virtual columns?

A. They can be referenced In the where clause of an update or debete statement.
B. They can be referenced in the set clause of an update statement as the name of the column To be
updated.
C. They can be indexed.
D. They cannot have a data type explicitly specified.
E. They can be referenced in the column expression of another virtxial column.
Answer: A,C
NO.68 Which two statements are true about the COUNT function?
A. It can only be used for NUMBER data types.
B. COUNT (DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and
NULLs in the INV_AMT column
C. COUNT(*) returns the number of rows in a table including duplicate rows and rows containing
NULLs in any column.
D. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE
clause.
E. COUNT(inv_amt) returns the number of rows in a table including rows with NULL in the INV_AMT
column.
Answer: B,C
NO.69 Which three statements are true about performing Data Manipulation Language (DML)
operations on a view In an Oracle Database?
23
A. Insert statements can always be done on a table through a view.

B. The WITH CHECK clause has no effect when deleting rows from the underlying table through the
view.
C. Views cannot be used to query rows from an underlying table if the table has a PRIPOARY KEY and
the PRIMARY KEY columns are not referenced in the defining query of the view.
D. Views cannot be used to add or modify rows in an underlying table if the defining query of the
view contains the DISTINCT keyword.
E. Views cannot be used to add on modify rows in an underlying table if the defining query of the
view contains aggregating functions.
F. Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL
constraints lacking default values which are not referenced in the defining query of the view.
Answer: D,E,F
NO.70 Which two statements cause changes to the data dictionary?

A. DELETE FROM scott. emp;
B. GRANT UPDATE ON scott. emp TO fin manager;
C. AITER SESSION set NLs. _DATE FORMAT = 'DD/MM/YYYY';
D. TRUNCATE TABLE emp:
E. SELECT * FROM user_ tab._ privs;
Answer: B,D
NO.71 Which two are true about the MERGE statement?

A. The WHEN NOT MATCHED clause can be used to specify the deletions to be performed.
B. The WHEN NOT MATCHED clause can be used to specify the inserts to be performed.
C. The WHEN MATCHED clause can be used to specify the inserts to be performed.
D. The WHEN NOT MATCHED clause can be used to specify the updates to be performed.
E. The WHEN MATCHED clause can be used to specify the updates to be performed.
Answer: B,E
Which two queries return the highest salary in the table?

A. SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id;
B. SELECT MAX (salary)
24
FROM employees;
C. SELECT MAX (salary)
FROM employees
D. SELECT MAX (salary)
FROM employees
GROUP BY department_id
HAVING MAX (salary) = MAX (MAX (salary));
E. SELECT MAX (MAX (salary))
FROM employees
Answer: B,E
NO.73 Examine the description or the CUSTOMERS table:
For Customers whose income level has a value, you want to display the first name and due amount as
5% of their credit limit. Customers whose due amount is null should not be displayed.
Which query should be used?
A. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers
WHERE cust_income_level != NULL AND cust_credit_level != NULL;
B. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMONT FROM customers
WHERE cust_income_level <> NULL AND due_amount <> NULL;
C. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMONT FROM customers
WHERE cust_income_level IS NOT NULL AND cust_credit_limit IS NOT NULL;
D. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMONT FROM customers
WHERE cust_income_level IS NOT NULL AND due_amount IS NOT NULL;
E.
SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMONT FROM customers
WHERE cust_income_level != NULL AND due_amount != NULL;
Answer: C
NO.74 Which three statements are true about the Oracle join and ANSI Join syntax?
A. The Oracle join syntax only supports right outer joins,
B. The Oracle join syntax supports creation of a Cartesian product of two tables.
C. The SQL:1999 compliant ANSI join syntax supports natural joins.
D. The Oracle join syntax supports natural joins.
25
E. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
F. The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables.
G. The Oracle join syntax performs less well than the SQL:1999 compliant ANSI Join Answer.
Answer: B,C,F
NO.75 You want to return the current date and time from the user session, with a data type of
TIMESTAMP WITH TIME ZONE.
Which function will do this?
A. CURRENT DATE
B. CURRENT_ TIMESTAMP
C. SYSDATE
D. LOCALTIMESTAMP
Answer: B
NO.76 Examine the description of the ENPLOYES table:
Which query requires explicit data type conversion?

A. SELECT SUBSTR(join date, 1, 2) - 10 FROM employees;
B. SELECT join_ date + '20' EROM employees;
C. SELECT join_ date||''|| salary FROM employees;
D. SELECT join _ date FROM employees WHERE join date > *10-02-2018';
E. SELECT salary + '120.50' FROM employees;
Answer: D
NO.77 Which three statements are true about single-row functions?

A. The data type returned can be different from the data type of the argument.
B. They can be nested to any level.
C. They return a single result row per table.
D. They can accept only one argument.
E. The argument can be a column name, variable, literal or an expression.
F. They can be used only in the WHERE clause of a SELECT statement.
Answer: A,B
26
NO.78 Which three are true about system and object privileges
A. WITH GRANT OPTION can be used when granting an object privilege to both users and roles
B. WITH GRANT OPTION cannot be used when granting an object privilege to PUBLIC
C. Revoking a system privilege that was granted with the WITH ADMIN OPTION has a cascading
effect.
D. Revoking an object privilege that was granted with the WITH GRANT OPTION clause has a
cascading effect
E. Adding a primary key constraint to an existing table in another schema requires a system privilege
F. Adding a foreign key constraint pointing to a table in another schema requires the REFERENCEs
object privilege
Answer: D,E,F
NO.79 Which three statements are true?

A. A customer can exist in many countries.
B. The statement will fail if a row already exists in the SALES table for product 23.
C. The statement will fail because subquery may not be I contained in a values clause.
D. The SALES table has five foreign keys.
E. The statement will execute successfully and a new row will be inserted into the SALES table.
F. A product can have a different unit price at different times.
Answer: D,E,F
NLS_DATE FORMAT is DD-MON-RR.

Which two queries will execute successfully?
A. SELECT dept_ id, AVG (MAX(salary)) FROM employees GROUP By dept_id HAVING hire_date> ' O1-
JAN-19';
B. SELECT dept_ id, AVG(MAX(salary)) FROM employees GROUP BY dept_id, salary;
C. SELECT dept id, MAX (SUM(salary)) FROM employees GROUP BY dept_id;
D. SELECT dept_ iD, sum(salary) FROM employees WHERE hire_date > '01-JAN-9' GROUP BY dept_id;
E. SELECT AVG(MAX(salary)) FROM employees GROUP BY salary;
Answer: D,E
27
NO.81 Examine the description of the ORDERS table:
Which three statements execute successfully?

A. (SELECT * FROM orders
UNION ALL
SELECT* FROM invoices) ORDER BY order _id;
B. SELECE order _id, order _ date FRON orders
LNTERSECT
SELECT invoice_ id, invoice_ id, order_ date FROM orders
C. SELECT order_ id, invoice_ data order_ date FROM orders
MINUS
SELECT invoice_ id, invoice_ data FROM invoices ORDER BY invoice_ id;
D. SELECT * FROM orders ORDER BY order_ id
INTERSEOT
SELECT * FROM invoices ORDER BY invoice_ id;
E. SELECT order_ id, order_ data FROM orders
UNION ALL
SELECT invoice_ id, invoice_ data FROM invoices ORDER BY order_ id;
F. SELECT * FROM orders
MINUS
SELECT * FROM INVOICES ORDER BY 1
G. SELECT * FROM orders ORDER BY order_ id
UNION
SELECT * FROM invoices;
Answer: A,E,F
NO.82 Which three actions can you perform on an existing table containing date?
A. Add a new column as the table's first column.
B. Define a default value that is automatically inserted into a column containing nulls.
C. Add a new NOT NULL Column with a DEFAULT value.
D. Change a DATE Column containing data to a NUMBER data type.
E. Increase the width of a numeric column.
F. Change the default value of a column.
28
Answer: C,E,F
NO.83 Which two statements are true about the SET VERIFY ON command?
A. It displays values for variables created by the DEFINE command.
B. It can be used in SQL Developer and SQL*Plus.
C. It can be used only in SQL*plus.
D. It displays values for variables prefixed with &&.
E. It displays values for variables used only in the WHERE clause of a query.
Answer: C,D
NO.84 Examine this incomplete query:

SELECT DATA'2019-01-01'+<INTERVAL CLAUSE>
FROM DUAL;
Which three clauses can replace<INTERVAL CLAUSE>ti add 22 hours to the date?
A. INTERVAL '12:00'
B. INTERVAL'0,5'DAY
C. INTERVAL'12' HOUR
D. INTERVAL'720'MINUTE
E. INTERVAL'0 12'DAY TO HOUR
F. INTERVAL'11:60'HOUR TO MINUTE
Answer: C,D,E
NO.85 Which two are SQL features?

A. providing graphical capabilities
B. providing variable definition capabilities.
C. providing database transaction control
D. processing sets of data
E. providing update capabilities for data in external files
Answer: C,D
NO.86 Which two statements are true about transactions in the Oracle Database server?
A. An uncommitted transaction commits automatically if the user exits SQL*Plus
B. Data Manipulation Language (DML) statements always start a new transaction.
C. A user can always see uncommitted updates made by the same user in a different session.
D. A Data Definition Language (DDL) statement does a commit automatically only for the data
dictionary updates caused by the DDL
E. A session can always see uncommitted updates made by itself.
F. If a session has an uncommitted transaction, then a DDL statement issue a COMMIT before
starting a new transaction.
Answer: A,E
NO.87 Which two statements are true about * _ TABLES views?

A. You must have SELECT privileges on a table to view it in ALL _TABLES.
29
B. You must have SELECT privileges on a table to view it in DBA TABLES.

C. USER_ TABLES displays all tables owned by the current user.
D. ALL TABLES displays all tables owned by the current user.
E. You must have SELECT privileges on a table to view it in USER TABLES.
F. All users can query DBA TABLES successfully.
Answer: A,C
NO.88 Which two are true about transactions in the Oracle Database?
A. A session can see uncommitted updates made by the same user in a different session.
B. A DDL statement issued by a session with an uncommitted transaction automatically Commits that
transaction.
C. DML statements always start new transactions.
D. DDL statements automatically commit only data dictionary updates caused by executing the DDL.
E. An uncommitted transaction is automatically committed when the user exits SQL*Plus.
Answer: B,E
NO.89 Examine these requirements:

1. Display book titles for books purchased before January 17, 2007 costing less than 500 or more than
1000.
2. Sort the titles by date of purchase, starting with the most recently purchased book.
Which two queries can be used?
A. SELECT book_title FROM books WHERE (price< 500 OR >1000) AND (purchase date< '17-JAN-
2007') ORDER BY purchase date DESC;
B. SELECT book_title FROM books WHERE (price IN (500, 1000)) AND (purchase date < '17-JAN-2007')
ORDER BY purchase_date ASC;
C. SELECT book_title FROM books WHERE (price NOT BETWEEN 500 AND 1000) AND
(purchase_date< '17-JAN-2007') ORDER BY purchase_date DESC;
D. SELECT book_title FROM books WHERE (price BETWEEN 500 AND 1000) AND (purchase_date<'17-
JAN-2007') ORDER BY purchase_date;
Answer: A,C
NO.90 Which three statements are true about Oracle synonyms?

A. A synonym cannot be created for a PL /SQL package.
B. A SEQUENCE can have a synonym.
C. A synonym can be available to all users .
D. A synonym created by one user can refer to an object belonging to another user.
E. Any user can drop a PUBLIC synonym.
Answer: A,C,D
NO.91 Which statement will return the last sequence number generated by the EMP_ SEQ
sequence?
A. SELECT NEXTVAL FROM emp_ seq;
B. SELECT CURRVAL FROM emp_ seq;
30
C. SELECT emp_ seq. CURRVAL FROM DUAL;

D. SELECT emp_ seq . NEXTVAL FROM DUAL;
Answer: D
NO.92 Examine this SQL statement

DELETE FROM employees e
WHERE EXISTS
(SELECT' dummy'
FROM emp history
WHERE employee_ id= e. employee id);
Which two are true?
A. The subquery is not a correlated subquery.
B. The subquery is executed before the DELETE statement is executed.
C. All existing rows in the EMPLOYEES table are deleted,
D. The DELETE statement executes successfully even if the subquery selects multiple rows.
E. The subquery is executed for every row in the EMPLOYEES table.
Answer: D,E
NO.93 Which three are true about subqueries?

A. A subquery can be used in a WHERE clause.
B. A subquery can be used in a HAVING clause.
C. =ANY can only evaluate the argument against a subcjuery if it returns two or more values.
D. <ANY returns true if the argument is less than the highest value returned by the subquery.
E. A subquery cannot be used in a FROM clause.
F. < any returns true if the argument is less than the lowest value returned by the subquery.
G. A subquery cannot be used in the select list.
Answer: A,B,D
NO.94 Examine this partial query:

SELECT ch.channel_type, t.month, co.country_code, SUM(s.amount_sold) SALES
FROM sales s, times t, channels ch, countries co
WHERE s.time_ id = t.time id
AND s.country_ id = co. country id
AND s. channel id = ch.channel id
AND ch.channel type IN ('Direct Sales', 'Internet')
AND t.month IN ('2000-09', '2000-10')
AND co.country code IN ('GB', 'US')
Examine this output:
31
Which GROUP BY clause must be added so the query returns the results shown?
A. GROUP BY ch.channel_type, t.month, co.country code;
B. GROUP BY ch.channel_type,ROLLUP (t month, co. country_ code) ;
C. GROUP BY CUBE (ch. channel_ type, t .month, co. country code);
D. GROUP BYch. channel_ type, t.month,ROLIUP (co. country_ code) ;
Answer: B
NO.95 Which statement will execute successfully?

A. SELECT 1, 2 FROM DUAL
UNION
SELECT 3, 4 FROM DUAL
ORDER BY 1, 2;
B. SELECT 3 FROM DUAL
UNION
SELECT 4 FROM DUAL
ORDER BY 3 ;
C. SELECT 1, 2 FROM DUAL
UNION
SELECT 3, 4 FROM DUAL
ORDER BY 3, 4;
D. SELECT 1 FROM DUAL
UNION
SELECT 2 FROM DUAL
32
ORDER BY 1, 2;
Answer: A
Which three statements will do an implicit conversion?

A. SELECT * FROM customers WHERE TO_ CHAR (customer_ id) = '0001';
B. SELECT * FROM customers WHERE customer id = '0001';
C. SELECT * FROM customers WHERE customer_ id = 0001;
D. SELECT FROM customers WHERE insert date = '01-JAN-19';
E. SELECT. FROM customers WHERE insert_ date = DATE *2019-01-01';
F. SELECT. FRON customers WE TO DATE (Insert _ date) = DATE '2019-01-01';
Answer: B,D,F
NO.97 Examine the description of the transactions table:
Which two SQL statements execute successfully?

A. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS DATE, amount+100 "DUES" from
transactions;
B. SELECT customer_id AS 'CUSTOMER-ID',transaction_date AS DATE, amount+100 'DUES' from
transactions;
C. SELECT customer_id CUSTID, transaction_date TRANS_DATE,amount+100 DUES FROM
transactions;
D. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS "DATE", amount+100 DUES FROM
transactions;
E. SELECT customer id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount+100 "DUES
AMOUNT" FROM transactions;
Answer: C,D
NO.98 The STORES table has a column START_DATE of data type DATE, containing the datethe row
was inserted.
You only want to display details of rows where START_DATEis within the last 25 months.which
WHERE clause can be used?
A. WHERE TO_NUMBER(start_date - SYSDATE)<=25
33
B. WHERE ADD_MONTHS (start date , 25)<= SYSDATE

C. WHERE MONTHS_BETWEEN(SYSDATE, start_date)<=25
D. WHERE MONTHS_BETWEEN (start_date, SYSDATE)<=25
Answer: C
NO.99 Which two statements are true?

A. AIl conditions evaluated using DECODE can also be evaluated using CASE.
B. All conditions evaluated using CASE can also be evaluated using DECODE.
C. CASE is a function and DECODE is not.
D. DECODE is a function and CASE is not.
E. Neither CASE nor DECODE is a function.
F. Both CASE and DECODE are functions.
Answer: A,D
NO.100 Which two statements will convert the string Hello world to ello wozid?
A. SELECT LOWER (SUBSTR('Hello World, 2, 1)) FROM DUAL;
B. SELECT LOWER (SUBSTR('Hello World', 2)) FROM DUAL;
C. SELECT LOWER(TRIM('H' FROM 'Hello World')) FROM DUAL;
D. SELECT SUBSTR('Hello world', 2) FROM DUAL;
E. SELECT INITCAP(TRIM('H' FROM 'Hello World')) FROM DUAL;
Answer: B,C
NO.101 The CUSTOMERS table has a CUST_CREDT_LIMIT column of data type number.
Which two queries execute successfully?
A. SELECT TO_CHAR(NVL(cust_credit_limit * .15,'Not Available')) FROM customers;
B. SELECT NVL2(cust_credit_limit * .15,'Not Available') FROM customers;
C. SELECT NVL(cust_credit_limit * .15,'Not Available') FROM customers;
D. SLECT NVL(TO_CHAR(cust_credit_limit * .15),'Not available') from customers;
E. SELECT NVL2(cust_credit_limit,TO_CHAR(cust_credit_limit * .15),'NOT Available') FROM
customers;
Answer: D,E

SELECT last name
FROM employees
ORDER BY CASE WHEN salary = (SELECT MAX(salary) FROM employees)
THEN 'A'
ELSE last_ name
END ,last_name DESC;
A. The names of employees earning the maximum salary will appear first in descending order.
B. The names of employees earning the maximum salary will appear first In ascending order.
C. All remaining employee names will appear in ascending order.
34
D. All remaining employee names will appear in an unspecified order.

E. All remaining employee names will appear in descending order.
F. The names of employees earning the maximum salary will appear first in an unspecified order.
Answer: E,F
NO.103 Which two statements are true about the rules of precedence for operators?
A. Arithmetic operators with equal precedence are evaluated from left to right within an expression.
B. Multiple parentheses can be used to override the default precedence of operators in an
expression.
C. The + binary operator has the highest precedence in an expression in a SQL statements.
D. NULLS influence the precedence of operators in an expression.
E. The concatenation operator || is always evaluated before addition and subtraction in an
expression.
Answer: A,B
NO.104 Which two statements are true about truncate and delete?
A. the result of a delete can be undone by issuing a rollback
B. delete can use a where clause to determine which row(s) should be removed.
C. TRUNCATE can use a where clause to determine which row(s) should be removed.
D. truncate leavers any indexes on the table in an UNUSABLE STATE.
E. the result of a truncate can be undone by issuing a ROLLBACK.
Answer: A,B
NO.105 You create a table by using this command:

CREATE TABLE rate_list (rate NUMBER(6,2));
Which two are true about executing statements?
A. INSERT INTO rate_list VALUES (-.9) inserts the value as -.9.
B. INSERT INTO rate_list VALUES (0.999) produces an error.
C. INSERT INTO rate_list VALUES (-10) produces an error.
D. INSERT INTO rate_list VALUES (87654. 556) inserts the value as 87654.6.
E. INSERT INTO rate_list VALUES (0.551) inserts the value as .55.
F. INSERT INTO rate_list VALUES (-99.99) inserts the value as 99.99.
Answer: A,E
35
Which two statements will run successfully?

A. SELECT 'The first_name is '' || first_name || '' FROM employees ;
B. SELECT 'The first_name is '''||first_name ||'''' FROM employees ;
C. SELECT 'The first_name is ''' ||first_name||''' FROM employees ;
D. SELECT 'The first_name is '|| first_name|| '' FROM employees;
E. SELECT 'The first_name is \'' || first_name || '\'' FROM employees;
Answer: B,D
NO.107 Which two are true about unused columns?

A. The DESCRIBE command displays unused columns
B. A primary key column cannot be set to unused.
C. A query can return data from unused columns, but no DML is possible on those columns.
D. Once a column has been set to unused, a new column with the same name can be added to the
table.
E. A foreign key column cannot be set to unused.
F. Unused columns retain their data until they are dropped
Answer: D,F
NO.108 Examine this query:

SELECT SUBSTR (SYSDATE,1,5) 'Result' FROM DUAL
Which statement is true?
A. It fails unless the expression is modified to TO-CHAR(SUNBSTR(SYSDATE,1,5)
B. It fails unless the expression is modified to SUBSTR (TO_ CHAR(SYSDATE),1,5)
C. It fails unless the expression is modified to SUBSTR (TO_ CHAR(TRUNC(SYSDATE)),1,5)
D. It executes successfully with an implicit data type conversion
Answer: D
NO.109 Examine these SQL statements which execute successfully:
36
Which two statements are true after execution?

A. The primary key constraint will be enabled and DEFERRED.
B. The primary key constraint will be enabled and IMMEDIATE.
C. The foreign key constraint will be disabled.
D. The foreign key constraint will be enabled and DEFERRED.
E. The foreign key constraint will be enabled and IMMEDIATE.
Answer: B,C
NO.110 Examine the description of the BOOKS_TRANSACTIONS table:
Examine this partial SQL statement:

SELECT * FROM books_transactions
Which two WHERE conditions give the same result?
A. WHERE (borrowed_date = SYSDATE AND transaction_type = 'RM') OR member_id IN
('A101','A102');
B. WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' OR member_id IN('A101','A102');
C. WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' OR member_id IN('A101','A102');
D. WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' AND (member_id = 'A101' OR
member_id = 'A102'));
37
E. WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' AND member_id = 'A101' OR

member_id = 'A102');
Answer: A,B
NO.111 Which two are true about the USING clause when joining tables?
A. All column names in a USING clause must be qualified with a table name or table alias.
B. It can never be used with onatural join.
C. It is used to specify an equijoin of columns that have the same name in both tables.
D. It can never be used with a full outer join.
E. It is used to specify an explicit join condition involving operators.
Answer: B,E
NO.112 You have been asked to create a table for a banking application.
One of the columns must meet three requirements:
1: Be stored in a format supporting date arithmetic without using conversion functions
2: Store a loan period of up to 10 years
3: Be used for calculating interest for the number of days the loan remains unpaid Which data type
should you use?
A. TIMESTAMP WITH TIMEZONE
B. TIMESTAMP
C. TIMESTAMP WITH LOCAL TIMEZONE
D. INTERVAL YEAR TO MONTH
E. INTERVAL DAY TO SECOND
Answer: E
NO.113 Which two are true?

A. CONCAT joins two or more character strings together.
B. FLOOR returns the largest integer less than or equal to a specified number.
C. CONCAT joins two character strings together.
D. INSTR finds the offset within a string of a single character only.
E. INSTR finds the offset within a character string, starting from position 0.
F. FLOOR returns the largest positive integer less than or equal to a specified number.
Answer: B,C
NO.114 Which three are true about dropping columns from a table?
A. A column can be removed only if it contains no data.
B. A column drop is implicitly committed
C. A column that is referenced by another column in any other table cannot be dropped.
D. A column must be set as unused before it is dropped from a table.
E. A primary key column cannot be dropped.
F. Multiple columns can be dropped simultaneously using the ALTER TABLE command.
Answer: B,C,F
38
NO.115 Examine this SQL statement:
Which two are true?

A. The subquery is executed before the UPDATE statement is executed.
B. All existing rows in the ORDERS table are updated.
C. The subquery is executed for every updated row in the ORDERS table.
D. The UPDATE statement executes successfully even if the subquery selects multiple rows.
E. The subquery is not a correlated subquery.
Answer: B,C
NO.116 Which three statements are true about Structured Query Language (SQL)?
A. It guarantees atomicity, consistency, isolation, and durability (ACID) features
B. It best supports relational databases
C. It is used to define encapsulation and polymorphism for a relational table
D. It requires that data be contained in hierarchical data storage
E. It is the only language that can be used for both relational and object-oriented databases
F. It provides independence for logical data structures being manipulated from the underlying
physical data storage
Answer: B,C,F
NO.117 Which two are true about multiple table INSERT statements?
A. They always use subqueries.
B. They can transform a row from a source table into multiple rows in a target table.
C. The conditional INSERT FIRST statement always inserts a row into a single table.
D. The conditional INSERT ALL statement inserts rows into a single table by aggregating source rows.
E. The unconditional INSERT ALL statement must have the same number of columns in both the
source and target tables.
Answer: A,B
NO.118 Which two statements are true about single row functions?
A. CONCAT: can be used to combine any number of values
B. MOD: returns the quotient of a division operation
C. CEIL: can be used for positive and negative numbers
D. FLOOR: returns the smallest integer greater than or equal to a specified number
E. TRUNC: can be used with NUMBER and DATE values
Answer: C,E
39
NO.119 Examine the data in the EMPLOYEES table:
Which statement will compute the total annual compensation for each employee?
A. SELECT last name,
(monthly salary*12) + (monthly_commission_pct * 12) AS
annual comp
FROM employees
;
B. SELECT last_ name (monthly_ salary+ monthly_ commission _ pct) *12 AS annual_
FROM employees ;
C. SELECT last name, (monthly_ salary *12) + (monthly_ salary * 12 * NVL
(monthly commission pct,0) ) As annual _ comp
FROM employees;
D. SELECT last_ name, monthly_ salary*12) + (monthly_ salary * 12 * Monthly commission _Pct) AS
annual_ comp
FROM employees;
Answer: C
NO.120 Examine this data in the EMPLOYERS table:
Which statement will execute successfully?

A. SELECT dept_id, MAX (Last_name), SUM (salary) FROM employees GROUP BY dept_id
B. SELECT dept_id, LENGTH (last_name), SUM (salary) FROM employees GROUP BY dept_id
C. SELECT dept_id, STDDEV (last_name), SUM (salary) FROM employees GROUP BY dept_id
D. SELECT dept_id, INSTR (last_name,'A'), SUM (salary) FROM employees GROUP BY dept_id
Answer: A
NO.121 You need to allow user ANDREW to:

1. Modify the TITLE and ADDRESS columns of your CUSTOMERS table.
2. GRANT tha permission to other users.
40
Which statement will do this?

A. GRANT UPDATE (title, address) ON customers TO andrew WITH ADMIN OPTION;
B. GRANT UPDATE ON customers. title, customers.address TO andrew WITH GRANT OPTION;
C. GRANT UPDATE ON customers.title, customers.address TO andrew WITH ADMIN OPTION;
D. GRANT UPDATE (title, address) ON customers TO andrew;
E. GRANT UPDATE ON customers. title, customers.address TO andrew;
F. GRANT UPDATE (title, address) ON customers TO andrew WITH GRANT OPTION;
Answer: F
NO.122 Which two are true about transactions in the Oracle Database?
A. DDL statements automatically commit only data dictionary updates caused by executing the DDL.
B. A DDL statement issued by a session with an uncommitted transation automaticall commits that
transaction.
C. An uncommitted transaction is automatically committed when the user exits SQL*PLUS
D. DML statements always start new transactions.
E. A session can see uncommitted updates made by the same user in a different session
Answer: B,C
NO.123 In your session, the NLS._DAE_FORMAT is DD- MM- YYYY.There are 86400 seconds in
a day.Examine
this result:
DATE
02-JAN-2020
Which statement returns this?
A. SELECT TO_ CHAR(TO_ DATE('29-10-2019') +INTERVAL '2'; MONTH + INTERVAL '5'; DAY -
INTERVAL '86410' SECOND, ' DD-MON-YYYY') AS "date"
FROM DUAL;
B. SELECT TO_ CHAR(TO_ DATE('29-10-2019') + INTERVAL '3' MONTH + INTERVAL '7' DAY -
INTERVAL '360' SECOND, ' DD-MON-YYYY') AS "date"
FROM DUAL;
C. SELECT To CHAR(TO _DATE('29-10-2019') + INTERVAL '2' NONTH + INTERVAL '5' DAY
INEERVAL '120' SECOND, ' DD-MON-YYY) AS "date"
FROM DUAL;
D. SELECT-TO_CHAR(TO _DATE('29-10-2019'+ INTERVAL '2' MONTH+INTERVAL '6' DAYINTERVAL
'120' SECOND, 'DD-MON-YY') AS "daTe"
FROM DUAL;
E. SELECT-TO_CHAR(TO _DATE('29-10-2019'+ INTERVAL '2' MONTH+INTERVAL '4' DAYINTERVAL
'120' SECOND, 'DD-MON-YY') AS "daTe"
FROM DUAL;
Answer: C

CREATE TABTE orders
(sarial_no NUMBER UNIQUE,
41
order_id NUMBER PRIMARY KEY ,

order_date DATE NOT NULL,
status VARCHAR2 (10) CHECK (status IN ('CREDIT', 'CASH')),
product_id NUMBER REFERENCES products (product_id),
order_ total NUMBER);
On which two columns of the table will an index be created automatically?
A. SERIAL_NO
B. ORDER_DATE
C. PRODUCT_ ID
D. ORDER TOTAL
E. ORDER_ ID
F. STATUS
Answer: A,E
NO.125 Which three are true about privileges and roles?

A. A role is owned by the user who created it.
B. System privileges always set privileges for an entire database.
C. All roles are owned by the SYS schema.
D. A role can contain a combination of several privileges and roles.
E. A user has all object privileges for every object in their schema by default.
F. PUBLIC can be revoked from a user.
G. PUBLIC acts as a default role granted to every user in a database
Answer: D,E,G
NO.126 Examine the description of the PRODUCTS table:
Which two statements execute without errors?

A. MERGE INTO new_prices n
USING (SELECT * FROM products) p
WHEN MATCHED THEN
UPDATE SET n.price= p.cost* 01
WHEN NOT MATCHED THEN
INSERT(n.prod_id, n.price) VALUES(p.prod_id, cost*.01)
WHERE(p.cost<200);
B. MERGE INTO new_prices n
USING (SELECT * FROM products WHERE cost>150) p
ON (n.prod_id= p.prod_id)
WHEN MATCHED THEN
UPDATE SET n.price= p.cost*.01
42
DELETE WHERE (p.cost<200);

C. MERGE INTO new_prices n
USING products p
ON (p.prod_id =n.prod_id)
INSERT (n.prod _id, n.price) VALUES (p.prod_id, cost*.01)
WHERE (p.cost<200);
D. MERGE INTO new_prices n
ON (n.prod_id= p.prod_id)
WHEN MATCHED THEN
DELETE WHERE (p.cost<200)
Answer: B,C
NO.127 Which two statements are true regarding the UNION ALL operators?
A. NULLS are not ignored during duplicate checking.
B. Duplicates are eliminated automatically by the UNION ALL operator
C. The names of columns selected in each SELECT statement must be identical.
D. The number of columns selected in each SELECT statement must be identical
E. The output is sorted by the UNION ALL operator.
Answer: A,D
NO.128 Examine this query which executes successfully;

Select job,deptno from emp
Union all
Select job,deptno from jobs_history;
A. It will return rows from both select statements after eliminating duplicate rows.
B. It will return rows common to both select statements.
C. It will return rows both select statements including duplicate rows.
D. It will return rows that are not common to both select statements.
Answer: C
NO.129 Which three statements are true about built-in data types?
A. A VARCHAR2 blank-pads column values only if the data stored is non-numeric and contains no
special characters.
B. The default length for a CHAR column is always one character.
C. A VARCHAR2 column definition does not require the length to be specified.
D. A BLOB stores unstructured binary data within the database.
E. A CHAR column definition does not require the length to be specified.
F. A BFILE stores unstructured binary data in operating system files.
Answer: B,D,F
43
SELECT cust_id, cus_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, any one of which can complete the query successfully.
A. ORDERBY 2, 1
B. ORDER BY "CUST_NO"
C. ORDER BY 2,cust_id
D. ORDER BY CUST_NO
E. ORDER BY "Last Name"
Answer: A,C,E
NO.131 Which statements is true about using functions in WHERE and HAVING?
A. using single-row functions in the WHERE clause requires a subquery
B. using single-row functions in the HAVING clause requires a subquery
C. using aggregate functions in the WHERE clause requires a subquery
D. using aggregate functions in the HAVING clause requires a subquery
Answer: A,D
NO.132 Examine this business rule:

Each student can work on multiple projects and earth project can have multiple students.
You must decide an Entity Relationship (ER) model for optional data storage and allow generating
reports in this format:
STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK Which two
statements are true?
A. An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which
is the foreign key linked to the STUDENTS and PROJECTS entities.
B. The ER must have a many-to-many relationship between the STUDENTS and PROJECTS entities
that must be resolved into 1-to-many relationships.
C. PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS
entity.
D. The ER must have a 1-to-many relationship between the STUDENTS and PROJECTS entities.
E. STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS
entity.
Answer: A,B
NO.133 Which two statements are true about Oracle databases and SQL?
A. Updates performed by a database user can be rolled back by another user by using the ROLLBACK
command.
B. The database guarantees read consistency at select level on user-created tablers.
C. When you execute an UPDATE statement, the database instance locks each updated row.
44
D. A query can access only tables within the same schema.

E. A user can be the owner of multiple schemas In the same database.
Answer: B,C
NO.134 Which two queries only return CUBE?

A. SELECT shape FROM bricks JOIN boxes ON weight >= min_weight AND weight < max_weight;
B. SELECT shape FROM bricks JOIN boxes ON weight > min_weight;
C. SELECT shape FROM bricks JOIN boxes ON weight BETWEEN min_weight AND max_weight;
D. SELECT shape FROM bricks JOIN boxes ON weight < max_weight;
E. SELECT shape FROM bricks JOIN boxes ON NOT (weight > max_weight);
Answer: A,C
NO.135 Which three statements are true about an ORDER BY clause?

A. An ORDER BY clause always sorts NULL values last.
B. An ORDER BY clause can perform a binary sort
C. An ORDER BY clause can perform a linguistic sort
D. By default an ORDERBY clause sorts rows in ascending order
E. An ORDR BY clause will always precede a HAVI NG clause if both are used in the same top-level
Answer: B,C,D
NO.136 Table ORDER_ITEMS contains columns ORDER_ID, UNIT_PRICE and QUANTITY, of data type
NUMBER
Statement 1:
SELECT MAX (unit price*quantity) "Maximum Order FROM order items;
Statement 2:
SELECT MAX (unit price*quantity "Maximum order" FROM order items GROUP BY order id;
A. Statement 2 returns only one row of output.
B. Both the statement given the same output.
C. Both statements will return NULL if either UNIT PRICE or QUANTITY contains NULL,
D. Statement 2 may return multiple rows of output.
E. Statement 1 returns only one row of output.
Answer: D,E
NO.137 Which two statements will return the names of the three employees with the lowest
salaries?
A. SELECT last_name, salary
FROM employees
WHERE ROWNUM<=3
B. SELECT last_name,salary
FROM employees
ORDER BY salary
FETCH FIRST 3 ROWS ONLY;
C. SELECT last_name,salary
45
FROM employees
WHERE ROWNUM<=3
ORDER BY (SELECT salary FROM employees);
D. SELECT last_name,salary
FROM (SELECT * FROM employees ORDER BY salary)
E. SELECT last_name,salary
FROM employees
FETCH FIRST 3 ROWS ONLY
ORDER BY salary;
Answer: B,D
Which two SELECT statements will return these results:

CUSTOMER_ NAME
--------------------
Mandy
Mary
A. SELECT customer_ name FROM customers WHERE customer_ name LIKE ' % a % ' ;
B. SELECT customer_ name FROM customers WHERE customer name LIKE 'Ma%' ;
C. SELECT customer_ name FROM customers WHERE customer_ name='*Ma*';
D. SELECT customer_ name FROM customers WHERE UPPER (customer_ name ) LIKE 'MA*. ;
E. SELECT customer_ name FROM customers WHERE customer name LIKE 'Ma*';
F. SELECT customer_ name FROM customers WHERE UPPER (customer name) LIKE 'MA&';
G. SELECT customer_ name FROM customers WHERE customer_ name KIKE .*Ma*';
Answer: A,B
NO.139 Examine the description of the PRODUCT_INFORMATION table:
A. SELECT (COUNT(list_price) FROM Product_intormation WHERE list_price=NULL;

B. SELECT count(nvl( list_price,0)) FROM product_information WHERE list_price is null;
46
C. SELECT COUNT(DISTINCT list_price) FROM product_information WHERE list_price is null.

D. BELECT COUNT(list_price) FROM product_information where list_price is NULL;
Answer: B
NO.140 Examine the description of the PROMOTIONS TABLE:

You want to display the unique is promotion costs in each promotion category.
A. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
B. SELECT promo_cost, promo_category FROM promotions ORDER BY 1
C. SELECT promo_category, DISTINCT promo_cost FROM promotiong ORDER BY 2:
D. select DISTINCT promo_categoryIl 'has'||promol_cost as COSTS FROM promotions ORDER BY 1:
E. SELECT DISTINCT promo_cost ||'in'IIDISTINCT promo_category promotions ORDER BY1;
Answer: A,D
NO.141 Which four statements are true regarding primary and foreign key constraints and the effect
they can have on table data?
A. Only the primary key can be defined at the column and table level.
B. The foreign key columns and parent table primary key columns must have the same names.
C. It is possible for child rows that have a foreign key to remain in the child table at the time the
parent row is deleted.
D. A table can have only one primary key but multiple foreign keys.
E. Primary key and foreign key constraints can be defined at both the column and table level.
F. A table can have only one primary key and one foreign key.
G. It is possible for child rows that have a foreign key to be deleted automatically from the child table
at the time the parent row is deleted
Answer: C,D,E,G
NO.142 Which two are true about granting object privileges on tables, views, and sequences?
A. DELETE can be granted on tables, views, and sequences.
B. REFERENCES can be grantrd only on tables.
C. INSERT can be granted only on tables and sequences.
D. SELECT can be granted on tables, views, and sequences.
E. ALTER can be granted only on tables and sequences.
Answer: D,E
NO.143 Examine this statement,which executes successfully:

In which order are the rows displayed?
A. sorted by DEPARTMENT_NAME
B. sorted by DEPARTMENT_NAME and AVGSAL
C. sorted by DEPARTMENT_NAME and MAXSAL
D. sorted by AVGSAL
E. Sorted by MAXSAL
Answer: D
47
NO.144 Whith three statements are true about built in data types?
A. A VARCHAR2 blank pads column values only if the data stored is non numeric and contains no
special characlers
B. A BFILE stores unstructured binary data in operating systerm files
C. A CHAR column definition does not require the length to be specified
D. The default length for a CHAR column is always one character
E. A VARCHAR2 column definition does not require the length to be specified
F. A BLOB stores unstructured binary data within the database
Answer: B,D,F
NO.145 Which two are true about using constraints?

A. A FOREIGN KEY column in a child table and the referenced PRIMARY KEY column in the
parenttable must have the same names.
B. A table can have multiple PRIMARY KEY and multiple FOREIGN KEY constraints.
C. A table can have only one PRIMARY KEY and one FOREIGN KEY constraint.
D. PRIMARY KEY and FOREIGNY constraints can be specified at the column and at the table level
E. A table can have only one PRIMARY KEY but may have multiple FOREIGN KEY constraints.
F. NOT NULL can be specified at the column and at the table level.
Answer: D,E
NO.146 Examine the data in the CUST_NAME column of the CUSTOMERS table:
CUST_NAME
---------------------
Renske Ladwig
Jason Mallin
Samuel McCain
Allan MCEwen
Irene Mikkilineni
Julia Nayer
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?
A. WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name,' ') + 1)) LIKE UPPER('MC%')
B. WHERE SUBSTR(cust_name, INSTR(cust_name,' ') + 1) LIKE 'Mc%' OR 'MC%'
C. WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ') + 1)) IN ('MC%','Mc%')
D. WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ') + 1)) LIKE 'Mc%'
E. WHERE SUBSTR(cust_name, INSTR(cust_name,' ') + 1) LIKE 'Mc%'
Answer: A,D
NO.147 Which two statements will return the names of the three employees with the lowest
salaries?
A. SELECT last_ name, salary
FROM employees
FETCH FIRST 3 ROWS ONLY
48
ORDER BY salary;
B. SELECT last name, salary
FROM employees
ORDER BY salary
FETCE FIRST 3 RONS ONLY;
C. SELECT last_ name, salary
FBOM employees
WEERE
ORDER BY SELECT
ROINUM <= 3
salary FROM
employees);
D. SELECT last_ name, salary
FROM
(SELECT" FROM employees ORDER BY salary)
WHERE ROWNUM <=3
E. SELECT last_ name, salary
FROM employees
WHERE ROWNUM <=3
ORDER BY salary
Answer: B,D
NO.148 Examine the data in the EMP table:
You execute this query:

SELECT deptno AS "Department", AVG(sal) AS AverageSalary, MAX(sal) AS "Max Salary"
FROM emp
WHERE sal >= 12000
GROUP BY "Department "
ORDER BY AverageSalary;
Why does an error occur?
A. An alias name must not be used in an ORDER BY clause.
B. An allas name must not contain space characters.
C. An alias name must not be used in a GROUP BY clause.
D. An alias name must always be specified in quotes.
Answer: C
NO.149 Examine this partial command:
49
Which two clauses are required for this command to execute successfully?
A. the DEFAULT DIRECTORY clause
B. the REJECT LIMIT clause
C. the LOCATION clause
D. the ACCESS PARAMETERS clause
E. the access driver TYPE clause
Answer: A,C
NO.150 The EMPLOYEES table contains columns EMP_ID of data type NUMBER and HIRE_DATE of
data type DATE
You want to display the date of the first Monday after the completion of six months since hiring.
The NLS_TERRITORY parameter is set to AMERICA in the session and, therefore, Sunday is the first
day of the week Which query can be used?
A. SELECT emp_id,NEXT_DAY(ADD_MONTHS(hite_date,6),'MONDAY') FROM employees;
B. SELECT emp_id,ADD_MONTHS(hire_date,6), NEXT_DAY('MONDAY') FROM employees;
C. SELECT emp_id,NEXT_DAY(MONTHS_BETWEEN(hire_date,SYSDATE),6) FROM employees;
D. SELECT emp_id,NEXT_DAY(ADD_MONTHS(hire_date,6),1) FROM employees;
Answer: A
NO.151 Which two statements are true about selecting related rows from two tables based on
entity relationship diagram (ERD)?
A. Relating data from a table with data from the same table is implemented with a self join.
B. An inner join relates rows within the same table.
C. Rows from unrelated tables cannot be joined.
D. Implementing a relationship between two tables might require joining additional tables.
E. Every relationship between the two tables must be implemented in a Join condition.
Answer: A,D

SELECT cust_id, cust_last_name "Last Name
FROM customers
WHERE countryid=10
UNION
SELECT custid CUSTNO, cust_last_name
FROM customers
WHERE countryid=30
Identify three ORDER BY clauses, any one of which can complete the query successfully.
50
A. ORDER BY"CUST NO"

B. ORDER BY 2, cust_id
C. ORDERBY2, 1
D. ORDER BY "Last Name"
E. ORDER BY CUSTNO
Answer: B,C,D
The session time zone is the same as the database server

Which two statements will list only the employees who have been working with the company for
more than five years?
A. SELECT employee_ name FROM employees WHERE (SYSDATE - hire_ data) / 365>5
B. SELECT employee_ name FROM employees WHERE (SYSTIMESTAMP - hire_ data) / 365>
C. SELECT employee_ name FROM employees WHERE (CUARENT_ DATE - hire_ data / 365>5
D. SELECT employee_ name FROM employees WHERE (SYSNAYW - hire_ data / 12> 3
E. SELECT employee_ name FROM employees WHERE (SYSNAYW - hire_ data / 12> 3
F. SELECT employee_ name FROM employees WHERE (CUNACV_ DATE - hire_ data / 12> 3
Answer: A,C
NO.154 Choose the best answer.

Examine the description of the EMPLOYEES table:
Which query is valid?

A. SELECT dept_id, join_date,SUM(salary) FROM employees GROUP BY dept_id, join_date;
B. SELECT depe_id,join_date,SUM(salary) FROM employees GROUP BY dept_id:
C. SELECT dept_id,MAX(AVG(salary)) FROM employees GROUP BY dept_id;
D. SELECT dept_id,AVG(MAX(salary)) FROM employees GROUP BY dapt_id;
Answer: A
51
NO.155 You execute these commands:

CREATE TABLE customers (customer id INTEGER, customer name VARCHAR2 (20));
INSERT INTO customers VALUES (1,'Custmoer1 ');
SAVEPOINT post insert;
INSERT INTO customers VALUES (2, 'Customer2 ');
<TODO>
SELECTCOUNT (*) FROM customers;
Which two, used independently, can replace <TODO> so the query retums 1?
A. ROLLBACK;
B. COMMIT;
C. ROLIBACK TO SAVEPOINT post_ insert;
D. CONOIT TO SAVEPOINT post_ insert;
E. ROLLEBACK TO post_ insert;
Answer: C,E
NO.156 Which three statements about roles are true?

A. Roles are assigned to roles using the ALTER ROLE statement.
B. A single user can be assigned multiple roles.
C. Roles are assigned to users using the ALTER USER statement.
D. A single role can be assigned to multiple users.
E. Privileges are assigned to a role using the ALTER ROLE statement.
F. A role is a named group of related privileges that can only be assigned to a user.
G. Privileges are assigned to a role using the GRANT statement.
Answer: B,D,G

SELECT employee_id, first_name, salary
FROM employees
WHERE hiredate > 61*
Which two methods should yours to prevent prompting for hire date value when this queries
executed?
A. Execute the SET VERIFY ON command before executing the query.
B. Execute the SET VERIFY OFF command before executing the query.
C. Store the query in a script and pass the substitution value to the script when executing it.
D. Replace 's1' with &1'in the query:
E. Use the UNDEFINE command before executing the query.
F. Use the DEFINE command before executing the query
Answer: C,F
NO.158 Which two statements are true regarding a SAVEPOINT?

A. Rolling back to a SAVEPOINT can undo a CREATE INDEX statement.
B. Only one SAVEPOINT may be issued in a transaction.
C. A SAVEPOINT does not issue a COMMIT
52
D. Rolling back to a SAVEPOINT can undo a TRUNCATE statement.

E. Rolling back to a SAVEPOINT can undo a DELETE statement
Answer: C,E
NO.159 Which three are true about scalar subquery expressions?

A. A scalar subquery expression that returns zero rows evaluates to zoro
B. They cannot be used in the values clause of an insert statement*
C. They can be nested.
D. A scalar subquery expression that returns zero rows evaluates to null.
E. They cannot be used in group by clauses.
F. They can be used as default values for columns in a create table statement.
Answer: C,D,E
NO.160 Which two tasks require subqueries?

A. Display the total number of products supplied by supplier 102 which have a product status of
obsolete.
B. Display suppliers whose PROD_LIST_PRICE is less than 1000.
C. Display the number of products whose PROD_LIST_PRICE is more than the average
PROD_LIST_PRICE.
D. Display the minimum PROD_LIST_PRICE for each product status.
E. Display products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all
products, and whose status is orderable.
Answer: C,E
NO.161 Examine the data in the INVOICES table:
Examine the data in the CURRENCIES table:

CURRENCY_CODE
-------------
JPY
GPB
CAD
EUR
USD
Which query returns the currencies in CURRENCIES that are not present in INVOICES?
A. SELECT currency_ code FROM currencies
MINUS
SELECT currency_ code FROM invoices;
53
B. SELECT * FROM currencies

WHERE NOT EXISTS (
SELECT NULL FROM invoices WHERE currency_ code = currency_ code);
C. SELECT currency_ code FROM currencies
INTERSECT
SELECT currency_ code FROM invoices;
D. SELECT * FROM currencies
MINUS
SELECT * FROM invoices;
Answer: A
NO.162 Which three statements are true about sequences in a single instance Oracle database?
A. A sequence's unallocated cached values are lost if the instance shuts down.
B. Two or more tables cannot have keys generated from the same sequence.
C. A sequence number that was allocated can be rolled back if a transaction fails.
D. A sequence can issue duplicate values.
E. Sequences can always have gaps.
F. A sequence can only be dropped by a DBA.
Answer: A,D,E
NO.163 Which three statements are true about defining relations between tables in a relational
database?
A. Foreign key columns allow null values.
B. Unique key columns allow null values
C. Primary key columns allow null values.
D. Every primary or unique key value must refer to a matching foreign key value.
E. Every foreign key value must refer to a matching primary or unique key value.
Answer: A,B,E
NO.164 SELECT *
FROM bricks,colors;
A. You can add an ON clause with a join condition.
B. You can add a WHERE clause with filtering criteria.
C. It returns the number of rows in BRICKS plus the number of rows in COLORS.
D. You can add a USING clause with a join condition.
E. It returnsthe same rows as SELECT * FROM bricks CROSS JOIN colors.
Answer: B,E

Which three statements will do an implicit conversion?
A. SELECT * FROM customers WHERE insert_date=DATE'2019-01-01';
B. SELECT * FROM customers WHERE customer_id='0001';
54
C. SELECT * FROM customers WHERE TO_DATE(insert_date)=DATE'2019-01-01';

D. SELECT * FROM customers WHERE insert_date'01-JAN-19';
E. SELECT * FROM customers WHERE customer_id=0001;
F. SELECT * FROM customers WHERE TO_CHAR(customer_id)='0001';
Answer: B,C,D
NO.166 The ORDERS table has a column ORDER_DATE of date type DATE The default display format
for a date is DD-MON-RR
Which two WHERE conditions demonstrate the correct usage of conversion functions?
A. WHERE ordet_date> TO_CHAR(ADD_MONTHS(SYSDATE, 6),'MON DD YYYY')
B. WHERE TO_CHAR(order_date,'MON DD YYYY') ='JAN 20 2019';
C. WHERE order_date> TO_DATE('JUL 10 2018','MON DD YYYY');
D. WHERE order_date IN (TO_DATE ('Oct 21 2018','MON DD YYYY'), TO_CHAR('Nov 21 2018','MON
DD YYYY'));
E. WHERE order_date> TO_DATE(ADD_MONTHS(SYSDATE,6),'MON DD YYYY');
Answer: B,C
NO.167 Examine the description of the SALES1 table:
SALES2 is a table with the same description as SALES1,

Some sales data is duplicated In both tables.
You want to display the rows from the SALES1 table which are not present in the SALIES2 table.
Which set operator generates the required output?
A. SUBTRACT
B. INTERSECT
C. UNION ALL
D. MINUS
E. UNION
Answer: D
NO.168 Which two statements are true about the WHERE and HAVING clauses in a SELECT
statement?
A. The WHERE clause can be used to exclude rows after dividing them into groups
B. WHERE and HAVING clauses can be used in the same statement only if applied to different table
columns.
C. The HAVING clause can be used with aggregating functions in subqueries.
55
D. Aggregating functions and columns used in HAVING clauses must be specified in these SELECT list
of a query.
E. The WHERE clause can be used to exclude rows before dividing them into groups.
Answer: C,E
NO.169 Which three statements are true?

A. The COMMISSION column can contain negative values .
B. The MANAGER column is a foreign key referencing the EMPNO column.
C. The SALARY column must have a value .
D. An index is created automatically in the MANAGER column.
E. The DEPTNO column in the EMP table can contain the value 1.
F. The DEPTNO column in the EMP table can contain NULLS .
G. The DNAME column has a unique constraint.
Answer: A,C,F
NO.170 Which two are true about queries using set operators (UNION, UNION ALL, INTERSECT and
MINUS)?
A. There must be an equal number of columns in each SELECT list.
B. The name of each column in the first SELECT list must match the name of the corresponding
column in each subsequent SELECT list.
C. Each SELECT statement in the query can have an ORDER BY clause.
D. None of the set operators can be used when selecting CLOB columns.
E. The FOR UPDATE clause cannot be specified.
Answer: A,E

SELECT TRUNC (ROUND(156.00,-2),-1) FROM DUAL; What is the result?
A. 16
B. 160
C. 150
D. 200
E. 100
Answer: D
NO.172 In the PROMOTIONS table, the PROMO_ BEGIN_DATE column is of data type and the default
date format is DD-MON-RR
Which two statements are true about expressions using PROMO_ BEGIN_DATE in a query?
A. TONUMBER (PROMO BEGIN_DATE) - 5 will return a number
B. PROMO_ BEGIN_DATE - 5 will return a date
C. PROMO_ BEGIN_DATE - SYSDATE will return a number
D. PROMO_ BEGIN_DATE - SYSDATE will return an error
E. TODATE(PROMO BEGIN_DATE *5) will return a date
Answer: B,C
56
NO.173 Which two statements about INVISIBLE indexes are true?

A. an INVISIBLE Index consumes no storage
B. You can only create one INVISIBLE index on the same column list
C. The query optimIzer never considers INVISIBLE Indexes when determining execution plans
D. You use AlTER INDEX to make an INVISIBLE Index VISIBLE
E. All INSERT, UPDATE, and DELETE statements maintain entries in the index
Answer: D,E
NO.174 Examine these statements executed in a single Oracle session:

CREATE TABLE product (pcode NUMBER(2),pname VARCHAR2(20));
INSERT INTO product VALUES(1,'pen');
INSERT INTO product VALUES (2,'pencil');
INSERT INTO product VALUES(3,'fountain pen');
SAVEPOINT a;
UPDATE product SET pcode=10 WHERE pcode =1;
COMMIT;
DELETE FROM product WHERE pcode =2;
SAVEPOINT b;
UPDATE product SET pcode=30 WHERE pcode =3;
SAVEPOINT c;
DELETE FROM product WHERE pcode =10;
ROLLBACK TO SAVEPOINT b;
COMMIT;
A. The code for pen is 10.
B. There is no row containing fountain pen.
C. There is no row containing pencil.
D. The code for pen is 1.
E. The code for fountain pen is 3
F. There is no row containing pen
Answer: A,C,E
NO.175 Examine the description of the ORDER_ITEMS table:
Examine this incomplete query:

SELECT DISTINCT quantity * unit_price total_paid FROM order_items ORDER BY <clause>;
Which two can replace <clause> so the query completes successfully?
57
A. quantity
B. quantity, unit_price
C. total_paid
D. product_id
E. quantity * unit_price
Answer: C,E
NO.176 Which statement is true regarding the SESSION_PRIVS dictionary view?

A. It contains the object privileges granted to other users by the current user session.
B. It contains the system privileges granted to other users by the current User session.
C. It contains the current system privileges available in the user session.
D. It contains the current object privileges available in the user session.
Answer: C
NO.177 Which three are true?

A. LAST_DAY returns the date of the last day of the current ,month onlyu.
B. CEIL requires an argument which is a numeric data type.
C. ADD_MONTHS adds a number of calendar months to a date.
D. ADD_MONTHS works with a character string that can be implicitlyt converted to a DATE data type.
E. LAST_DAY return the date of the last day the previous month only.
F. CEIL returns the largest integer less than or equal to a specified number.
G. LAST_DAY returns the date of the last day of the month for the date argument passed to the
function.
Answer: B,C,G
NO.178 Which two statements are true about Oracle synonyms?

A. A synonym can have a synonym.
B. All private synonym names must be unique in the database.
C. Any user can create a PUBLIC synonym.
D. A synonym can be created on an object in a package.
E. A synonym has an object number.
Answer: A,E
NO.179 What is true about non-equijoin statement performance?

A. The between condition always performs less well than using the >= and <= conditions.
B. The Oracle join syntax performs better than the SQL: 1999 compliant ANSI join syntax.
C. The join syntax used makes no difference to performance.
D. The between condition always performs better than using the >= and <= conditions.
E. Table aliases can improve performance.
Answer: C,E

SELECT 2 FROM dual d1 CROSS JOIN dual d2 CROSS JOIN dual d3;
58
What is returned upon execution?

A. 0 rows
B. an error
C. 8 rows
D. 6 rows
E. 1 row
F. 3 rows
Answer: E
NO.181 Which is true about the & and && prefixes with substitution variables?
A. & can prefix a substitution variable name only in queries. DML
B. An & prefix to an undefined substitution variable, which is referenced twice in the same query, will
prompt for a value twice .
C. The && prefix will not prompt for a value even if the substitution variable is not previously defined
in the session.
D. An && prefix to an undefined substitution variable, which is referenced multiple times in multiple
queries, will prompt for a value once per query.
E. Both & and && can prefix a substitution variable name in queries and DML statements.
Answer: B,E
NO.182 Which three statements are true about performing DML operations on a view with no
INSTEAD OF triggers defined?
A. Insert statements can always be done on a table through a view.
B. The WITH CHECK clause has no effect when deleting rows from the underlying table through the
view.
C. Delete statements can always be done on a table through a view.
D. Views cannot be used to add rows to an underlying table If the table has columns with NOT NULL
E. Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and
the primary key columns are not referenced in the defining query of the view.
F. Views cannot be used to add or modify rows in an underlying table If the defining query of the
Answer: D,E,F
NO.183 For each employee in department 90 you want to display:

1. their last name
2. the number of complete weeks they have been employed
The output must be sorted by the number of weeks, starting with the longest serving employee
first.Which statement will accomplish this?
A. SELECT last_name, TRUNC( (SYSDATE - hire_ date) 1 7) AS tenure
FROM employees
WHERE department_ id = 90
ORDER BY tenure ;
B. SELECT last_name, ROUND( (SYSDATE - hire_ date) 1 7) AS tenure
59
FROM employees
ORDER BY tenure ;
C. SELECT last_name, ROUND( (SYSDATE - hire_ date) 17) AS tenure
FROM employees
ORDER BY tenure DESC;
D. SELECT last_name, TRUNC ( (SYSDATE - - hire_ date) 1 7) AS tenure
FROM employees
WHERE department_id = 90
ORDER BY tenure DESC;
Answer: D
NO.184 Examine this description of the PRODUCTS table:
Rows exist in this table with data in all the columns. You put the PRODUCTS table in read-only mode.
Which three commands execute successfully on PRODUCTS?
A. ALTER TAELE products DROP COLUMN expiry_date;
B. CREATE INDEX price_idx on products (price);
C. ALTER TABLE products SET UNUSED(expiry_date);
D. TRUNCATE TABLE products;
E. ALTER TABLE products DROP UNUSED COLUMNS
F. DROP TABLE products
Answer: B,E,F
NO.185 Which two statements are true about date/time functions in a session where
NLS_DATE_PORMAT is set to DD-MON-YYYY SH24:MI:SS
A. SYSDATE can be used in expressions only if the default date format is DD-MON-RR.
B. CURRENT_TIMESTAMP returns the same date as CURRENT_DATE.
C. CURRENT_DATE returns the current date and time as per the session time zone
D. SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the
database server.
E. CURRENT_TIMESTAMP returns the same date and time as SYSDATE with additional details of
functional seconds.
F. SYSDATE can be queried only from the DUAL table.
60
Answer: C,E
NO.186 Which two statements are true about INTERVAL data types
A. INTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years.
B. The value in an INTERVAL DAY TO SECOND column can be copied into an INTERVAL YEAR TO
MONTH column.
C. INTERVAL YEAR TO MONTH columns only support monthly intervals within a single year.
D. The YEAR field in an INTERVAL YEAR TO MONTH column must be a positive value.
E. INTERVAL DAY TO SECOND columns support fractions of seconds.
F. INTERVAL YEAR TO MONTH columns support yearly intervals.
Answer: E,F
NO.187 You have the privileges to create any type of synonym.

Which stalement will create a synonym called EMP for the HCM.EMPLOYEE_RECORDS table that is
accesible to all users?
A. CREATE GLOBAL SYNONYM emp FOR hcm.employee_records;
B. CREATE SYNONYM emp FOR hcm.employee_records;
C. CREATE SYNONYM PUBLIC.emp FOR hcm.employee_records;
D. CREATE SYNONYM SYS.emp FOR hcm.employee_records;
E. CREATE PUBLIC SYNONYM emp FOR hcm. employee_records;
Answer: E
NO.188 Which two statements are true about CURRENT_TIMEITAMP?

A. The date is in the time zone of DBTIMEZONE.
B. The value varies depending on the setting of SESSIONTIMEZONE.
C. It returns the same date as CURRENT_DATE.
D. The time is in the time zone of DBTIMEZONE.
E. It returns a value of data type TIMESTAMP
F. It always returns the same value as SYSTIMESTAMP
Answer: D,F
NO.189 Which two statements are true about a full outer join?
A. It includes rows that are returned by an inner join.
B. The Oracle join operator (+) must be used on both sides of the join condition in the WHERE clause.
C. It includes rows that are returned by a Cartesian product.
D. It returns matched and unmatched rows from both tables being joined.
E. It returns only unmatched rows from both tables being joined.
Answer: A,D
61
You want to display details of all customers who reside in cities starting with the letter D followed by
at least two character.
Which query can be used?
A. SELECT * FROM customers WHERE city ='D_%';
B. SELECT * FROM customers WHERE city ='%D_';
C. SELECT * FROM customers WHERE city LIKE'D %';
D. SELECT * FROM customers WHERE city LIKE'D_';
Answer: C
NO.191 In the PROMOTIONS table, the PROMO_BEGTN_DATE column is of data type DATE and the
default date format is DD-MON-RR.
Which two statements are true about expressions using PROMO_BEGIN_DATE contained in a query?
A. TO_NUMBER(PROMO_BEGIN_DATE)-5 will return number
B. TO_DATE(PROMO_BEGIN_DATE * 5) will return a date
C. PROMO_BEGIN_DATE-SYSDATE will return a number.
D. PROMO_BEGIN_DATE-5 will return a date.
E. PROMO_BEGIN_DATE-SYSDATE will return an error.
Answer: C,D
NO.192 The PROD_ID column is the foreign key in the SALES table.Which references the PRODUCTS
table.
Similarly,the CUST_ID and TIME_ID columns are Also foreign keys in the SALES table referencing the
CUSTOMERS and TIMES tables, respectively.
Evaluate the following CREATE TABLE command:
CREATE TABLE new_sales(prod_id, I cust_id, order_date DEFAULT SYSDATE)
AS SELECT I prod_id,cust_id,time_id FROM sales.
Which statement is true regarding the above command?
A. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in
the column definition.
B. The NEW_SALES table would get created and all the NOT NULL constraints defined on the
specified columns would be passed to the new table.
C. The NEW_SALES table would not get created because the column names in the CREATE TABLE
command and the SELECT clause I do not match.
D. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the
62
specified columns would be passed to the new table

Answer: B
NO.193 Viev the Exhibit and examine the structure of the PRODUCT INFORMATION and
INVENTORIEStables.
You have a requirement from the supplies department to give a list containing PRODUCT
_ID,SUPPLIER ID, and QUANTITY_ON HAND for all the products where in QUANTITY ON HAND is
lessthan five.
Which two SQL statements can accomplish the task? (Choose two)
A. SELECT product id, quantity on hand, supplier id
FROM product information
NATURAL JOIN inventories AND quantity .on hand < 5;
B. SELECT i. product id, i. quantity .on hand, pi. supplier_id
FROM product_information pi JOIN inventories i
ON (pi. product. id=i. product id) AND quantity on hand < 5;
C. SELECT i. product_id, i. quantity_on hand, pi. supplier id
FROM product information pi JOIN inventories i USING (product id) AND quantity .on hand < 5;
D. SELECT i.product id, i. quantity on hand, pi. supplier id
FROM product information pi JOIN inventories i
ON (pi.product id=i. product id)WHERE quantity on hand < 5;
Answer: B,D
NO.194 Which two statements are true about * TABLES views?

A. You must have SELECT privileges on a table to view it in ALL TABLES.
B. You must have SELECT privileges on a table to view it in DBA TABLES.
C. USER TABLES displays all tables owned by the current user.
D. All TABLES displays all tables owned by the current user.
E. You must have SELECT privileges on a table to view it in USER TABLES.
F. All users can query DBA TABLES successfully.
Answer: A,C
Which statement increases each employee's SALARY by the minimum SALARY for their
DEPARTM
ENT_ID?
A. UPDATE employees e1
SET salary =(SELECT e2. salary + MIN(e2.salary)
FROM employees e2
63
WHERE e1.department_ id = e2. department_id GROUP BY e2. department_id) ;

B. UPDATE employees e1
SET salary = salary +
(SELECT MIN(e1. salary)
FROM employees e2
WHERE e1.department_id = e2 .department_id);
C. UPDATE employees e1
SET salary = salary+(SELECT MIN (salary)
FROM employees e2) ;
D. UPDATE employees e1
SET salary=
(SELECT e1.salary + MIN(e2.salary)
FROM employees e2
WHERE e1. department_ id = e2.department_id);
Answer: D
NO.196 Which two are true about external tables that use the ORACLE _DATAPUMP access driver?
A. Creating an external table creates a directory object.
B. When creating an external table, data can be selected only from a table whose rows are stored in
database blocks.
C. When creating an external table, data can be selected from another external table or from a table
whose rows are stored in database blocks.
D. Creating an external table creates a dump file that can be used by an external table in the same or
a different database.
E. Creating an external table creates a dump file that can be used only by an external table in the
same database.
Answer: B,D
NO.197 Examine this partial command:

CREATE TABLE cust(
cust_id NUMBER(2),
credit_limit NUMBER(10)
ORGANIZATION EXTERNAL
Which two clauses are required for this command to execute successfully?
A. the ACCESS PARAMETERS clause
B. the DEFAULT DIRECTORY clause
C. the access driver TYPE clause
D. the LOCATION clause
E. the REJECT LIMIT clause
Answer: B,D
NO.198 Which two are true about scalar subquery expressions?

A. You cannot correlate them with a table in the parent statement
B. You can use them as a default value for a column.
64
C. .You must enclose them in parentheses.

D. They can return at most one row.
E. They can return two columns.
Answer: A,C
NO.199 Which two queries will result in an error?

A. SELECT FIRST_NAME LAST_NAME FROM EMPLOYEES;
B. SELECT FIRST_NAME,LAST_NAME FROM EMPLOYEES;
C. SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY
FROM EMPLOYEES
WHERE ANNUAL_SALARY > 100000
ORDER BY 12 * SALARY ;
D. SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY
FROM EMPLOYEES
WHERE 12 * SALARY > 100000
ORDER BY ANNUAL_SALARY;
E. SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY
FROM EMPLOYEES
WHERE 12 * SALARY > 100000
ORDER BY 12 * SALARY;
F. SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY
FROM EMPLOYEES
WHERE ANNUAL_SALARY > 100000
ORDER BY ANNUAL_SALARY;
Answer: C,F
NO.200 Which two statements are true about an Oracle database?

A. A table can have multiple primary keys.
B. A table can have multiple foreign keys.
C. A NUMBER column without data has a zero value.
D. A column definition can specify multiple data types.
E. A VARCHAR2 column without data has a NULL value.
Answer: B,E

A. SELECT prod_id, exp_date FROM products
UNION ALL
SELECT prod_id, NULL FROM new_products;
B. SELECT prod_id, prod_name FROM products
INTERSECT
SELECT 100, prod_name FROM newproducts;
C. SELECT * FROM products
UNION
SELECT * FROM new_products;
65
D. SELECT k FROM products

MINUS
SELECT prod_id FROM new_products;
E. SELECT prod_id FROM products
UNION ALL
SELECT prod_id, prod_name FROM new_products;
Answer: A,C
NO.202 which three statements are true regarding single row subqueries?
A. THEY CAN BE USED in the where clause.
B. A SQL STATEMENT MAY HAVE MULTIPLE SINGLE ROW SUBQUERY BLOCKS.
C. THEY MUST BE PLACED ON THE RIGHT SIDE OF THE COMPARISON OPERATOR OR CONDITION.
D. they must be placed on the left side of the comparison operator or condition.
E. THEY CAN BE USED IN THE HAVING CLAUSE
F. they must return a row to prevent errors in the SQL statement.
Answer: A,B,E
NO.203 Which two are true about queries using set operators such as UNION?
A. An expression in the first SELECT list must have a column alias for the expression
B. CHAR columns of different lengths used with a set operator retum a vAacsua mhtoe e equals the
longest CHAR value.
C. Queries using set operators do not perform implicit conversion across data type groups (e.g.
character, numeric)
D. In a query containing multiple set operators INTERSECT always takes precedence over UNION and
UNION ALL
E. All set operators are valid on columns all data types.
Answer: C,E
NO.204 Choose two

Examine the description of the PRODUCT DETALS table:
A. PRODUCT_ID can be assigned the PEIMARY KEY constraint.

B. EXPIRY_DATE cannot be used in arithmetic expressions.
C. EXPIRY_DATE contains the SYSDATE by default if no date is assigned to it
D. PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it
66
E. PRODUCT_PRICE contains the value zero by default if no value is assigned to it.

F. PRODUCT_NAME cannot contain duplicate values.
Answer: A,D
NO.205 Which statement is true about the INTERSECT operator used in compound queries?
A. It processes NULLS in the selected columns.
B. INTERSECT is of lower precedence than UNION or UNION ALL.
C. It ignores NULLS.
D. Multiple INTERSECT operators are not possible in the same SQL statement.
Answer: A
NO.206 Which two are true about granting privilege on objects?

A. The owner of an object acquires all object privilege on that object by default.
B. The WITH GRANT OPTION clause can be used only by DBA users.
C. A table owner must grant the references privilege to allow other users to create FOREIGN KEY
constraints using that table.
D. An object privilege can be granted to a role only by the owner of that object.
E. An object privilege can be granted to other users only by the owner of object.
Answer: A,C
NO.207 Examine these two queries and their output:

SELECT deptno, dname FROM dept;
SELECT ename, job, deptno FROM emp ORDER BY deptno;
Now examine this query:

SELECT ename, dname
FROM emp CROSS JOIN dept WHERE job = 'MANAGER'
AND dept.deptno IN (10, 20) ;
A. 64
B. 6
C. 3
67
D. 12
Answer: B
NO.208 Which three statements are true about views in an Oracle database?
A. A SELECT statement cannot contain a where clause when querying a view containing a WHERE
clause in its defining query
B. Rows inserted into a table using a view are retained in the table if the view is dropped
C. Views can join tables only if they belong to the same schema.
D. Views have no segment.
E. Views have no object number.
F. A view can be created that refers to a non-existent table in its defining query.
Answer: B,D,F
NO.209 Examine the data in the ORDERS table:
Examine the data in the INVOICES table:
Examine this query:

SELECT order_ id, order_ date FROM orders
INTERSECT
SELECT order_ 1d, order_ date FROM invoices;
Which two rows will it return?
A. 3 <null>
B. 2 <null>
C. 1 <null>
D. 5 01-MAR-2019
E. 4 01-FEB-2019
68
F. 3 01-JAN-2019
Answer: C,E

A. SELECT INTERVAL '1' DAY - SYSDATE FROM DUAL;
B. SELECT SYSTIMESTAMP + INTERVAL '1' DAY FROM DUAL;
C. SELECT INTERVAL '1' DAY - INTERVAL '1' MINUTE FROM DUAL;
D. select INTERVAL '1' DAY +INTERVAL '1' MONTH FROM DUAL;
E. SELECT SYSDATE "INTERRVAL '1' DAY FROM DUAL;
Answer: B,C
NO.211 You execute these commands:

SQL> DEFINE hiredate = '01-APR -2011';
SQL> SELECT employee_id, first_name, salary FROM employees WHERE hire date > &hiredate AND
manager_id >&mgr_id;
For which substitution variables will you be prompted?
A. none
B. &hiredate and &mgr_id
C. only &hiredate
D. only &mgr_id
Answer: D
NO.212 Evaluate these commands which execute successfully CREATE SEQUENCE ord_seq
INCREMENT BY 1
START WITH 1
MAXVALUE 100000
CYCLE
CACHE 5000;
Create table ord_items(
ord_no number(4) default ord_seq.nextval not null,
Item_no number(3),
Qty number(3),
Expiry_date date,
Constraint it_pk primary key(ord_no,item_no),
Constraint ord_fk foreign key (ord_no) references orders(ord_no));
Which two statements are true about the ORD_ITEMS table and the ORD_SEQ sequence?
A. Any user inserting rows into table ORD_ITEMS must have been granted access to sequence
ORD_SEQ.
B. Column ORD_NO gets the next number from sequence ORD_SEQ whenever a row is inserted into
ORD_ITEMS and no explicit value is given for ORD_NO.
C. Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times
D. IF sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for
rows inserted into ORD_ITEMS.
E. Sequence ORD_SEQ is guaranteed not to generate duplicate numbers.
69
Answer: A,B
NO.213 Which is the default column or columns for sorting output from compound queries using
SET operators such as INTERSECT in a SQL statement?
A. The first column in the last SELECT of the compound query
B. The first NUMBER column in the first SELECT of the compound query
C. The first VARCHAR2 column in the first SELECT of the compound query
D. The first column in the first SELECT of the compound query
E. The first NUMBER or VARCHAR2 column in the last SELECTof the compound query
Answer: D
NO.214 You execute this query:

SELECT TO CHAR (NEXT_DAY(LAST_DAY(SYSDATE),'MON' ),' dd"Monday for" fmMonth rrr') FROM
DUAL;
What is the result?
A. It executes successfully but does not return any result.
B. It returns the date for the first Monday of the next month.
C. It generates an error.
D. It returns the date for the last Monday of the current month.
Answer: B
NO.215 Examine the data in the COLORS table:
Examine the data in the BRICKS table:
Which two queries return all the rows from COLORS?

A. AIl conditions evaluated using DECODE can also be evaluated using CASE.
B. All conditions evaluated using CASE can also be evaluated using DECODE.
C. CASE is a function and DECODE is not.
D. DECODE is a function and CASE is not.
E. Neither CASE nor DECODE is a function.
F. Both CASE and DECODE are functions.
70
Answer: B,D
NLS_DATE_FORMAT is set to DD-MON-YY.

Which query requires explicit data type conversion?
A. SELECT salary + 120.50 FROM employees;
B. SELECT SUBSTR(join date, 1, 2)- 10 FROM employees;
C. SELECT join date 11.'11 salary FROM employees;
D. SELECT join date FROM employees where join date > *10-02-2018*;
E. SELECT join date + 20 FROM employees;
Answer: D
NO.217 Which two statements are true about the order by clause when used with a sql statement
containing a set operator such as union?
A. column positions must be used in the order by clause.
B. The first column in the first select of the compound query with the union operator is used by
default to sort output in the absence of an order by clause.
C. Each select statement in the compound query must have its own order by clause.
D. only column names from the first select statement in the compound query are recognized.
E. Each select statement in the compound query can have its own order by clause.
Answer: B,D

TRUNCATE TABLE dept;
Which two are true?
A. It drops any triggers defined on the table.
B. It retains the indexes defined on the table.
C. It retains the integrity constraints defined on the table.
D. A ROLLBACK statement can be used to retrieve the deleted data.
E. It always retains the space used by the removed rows.
F. A FLASHBACK TABLE statement can be used to retrieve the deleted data.
Answer: B,C
NO.219 Which two are true about creating tables in an Oracle database?
A. A create table statement can specify the maximum number of rows the table will contain.
71
B. The same table name can be used for tables in different schemas.
C. A system privilege is required.
D. Creating an external table will automatically create a file using the specified directory and file
name.
E. A primary key constraint is manadatory.
Answer: A,B
NO.220 You execute these commands successfully:

CREATE GLOBAL TEMPORARY TABLE invoices _ gtt
( customer id INTEGER,
invoice_ total NUMBER (10, 2)
) ON COMMIT PRESERVE ROWS;
INSERT INTO invoices_ gtt VALUES (1, 100);
COMMIT;
Which two are true?
A. You can add a foreign key to the table.
B. When you terminate your session, the row will be deleted.
C. To drop the table in this session, you must first truncate it.
D. You can add a column to the table in this session.
E. Other sessions can view the committed row.
Answer: B,C
NO.221 Examine this list of queries:

A. 1 and 4 give the same result.
B. 2 returns the value 20.
C. 2 and 3 give the same result.
D. 3 returns an error.
E. 1 and 4 give different results.
Answer: A,B
NO.222 Which three are true about granting object privileges on tables, views, and sequences?
A. UPDATE can be granted only on tables and views.
B. DELETE can be granted on tables, views, and sequences.
C. REFERENCES can be granted only on tables and views.
D. INSERT can be granted on tables, views, and sequences.
E. SELECT can be granted only on tables and views.
F. ALTER can be granted only on tables and sequences.
Answer: A,C,F
NO.223 Evaluate the following SQL statement

SQL>SELECT promo_id, prom _category FROM promotions
WHERE promo_category='Internet' ORDER BY promo_id
UNION
72
SELECT promo_id, promo_category FROM Pomotions

WHERE promo_category = 'TV'
UNION
SELECT promoid, promocategory FROM promotions WHERE promo category='Radio'
Which statement is true regarding the outcome of the above query?
A. It executes successfully and displays rows in the descend ignore of PROMO CATEGORY.
B. It produces an error because positional, notation cannot be used in the ORDER BY clause with SBT
operators.
C. It executes successfully but ignores the ORDER BY clause because it is not located at the end of the
compound statement.
D. It produces an error because the ORDER BY clause should appear only at the end of a compound
query-that is, with the last SELECT statement.
Answer: D
NO.224 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. SELECT &col1, &col2
FROM &&table
WHERE &condition;
B. SELECT &col1, &col2
FROM "&table"
WHERE &condition;
C. SELECT &&col1,&&col2
FROM &table
WHERE &&condition= &&cond;
D. SELECT'&co11','&&co12'
FROM &table
WHERE'&&condition' ='&cond';
E.
SELECT&&col1, &&col2
FROM &table
WHERE &&condition;
Answer: A
Which statement will fail?
73
A. SELECT department_id, COUNT (*)

FROM employees
HAVING department_ id <> 90 AND COUNT(*) >= 3
B. SELECT department_id, COUNT (*)
FROM employees
WHERE department_ id <> 90 AND COUNT(*) >= 3
C. SELECT department_id, COUNT(*)
FROM employees
WHERE department_id <> 90 HAVING COUNT(*) >= 3
D. SELECT department_id, COUNT(*)
FROM employees
WHERE department_id <> 90 GROUP BY department_id
HAVING COUNT(*) >= 3;
Answer: B

CREATE view emp80 AS
SELECT
FROM employees
WITH CHECK OPTION;
Which statement will violate the CHECK constraint?
A. DELETE FROM emp80
WHERE department_ id = 90;
B. SELECT
FROM emp80
WHERE department_ id = 90;
C. SELECT
FROM emp80
WHERE department. id = 80;
D. UPDATE emp80
SET department. 1d =80;
WHERE department_ id =90;
Answer: D
NO.227 Examine the data in the COLORS table:
74
Examine the data in the BRICKS table:
Which two queries return all the rows from COLORS?

A. SELECT.
FROM bricks b
RIGHT JOIN colors c
ON b. color _rgb_ hex_ value = c. rgb hex_ value;
B. SELECT
EROM colors C
LEFT JOIN bricks b
USING (rgb _ hex_ value) ;
C. SELECT
FROM bricks b
FULL JOIN colors C
ON b. color rgb _ hex_ value = c. rgb _hex_ value;
D. SELECT *
EROM bricks | b
JOIN colors C
ON b. color_ rgb_ hex_ value =c. rgb _hex value;
E. SELECT
EROM colors C
LEET JOIN bricks b
ON b. color_ rgb_ hex value = c. rgb. hex.
value
WHERE b. brick_ id > 0;
Answer: A,C
NO.228 Which two are true about granting privilege on objects?

A. An object privilege can be granted to a role only by the owner of that object
B. An object privilege can be granted to other users only by the owner of that object
C. The owner of an object acquires all object privilege on that object by default
D. A table owner must grant the REFERENCES privilege to allow other users to create FOREIGN KEY
75
constraints using that table

E. The WITH GRANT OPTION clause can be used only by DBA users
Answer: C,D
NO.229 Examine the description of the PROMTIONS table:
You want to display the unique promotion costs in each promotion category.
A. SELECT promo_cost, | pxomo_category FROM promotions ORDER BY 1;
B. SELECT promo_category, DISTINCT promo_cost PROM promotions ORDER BY 2:
C. SELECT DISTINCT promo_category ||'has'|| promo_cost AS COSTS FROM promotions ORDER BY 1;
D. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
E. SELECT DISTINCT promo_cost ||' in' II DISTINCT promo_category FROM promotions ORDER BY 1;
Answer: C,D
NO.230 Which three privileges can be restricted to a subset of columns in a table?

A. ALTER
B. REFERENCES
C. UPDATE
D. SELECT
E. INDEX
F. INSERT
G. DELETE
Answer: B,C,F
NO.231 Which three statements are true about performing DML operations on a view with no
Instead of triggers defined?
A. WITH CHECK clause has no effect when deleting rows from the underlying table through the view.
B. Insert statements can always be done on a table through a view.
C. Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL
D. Views cannot be used to add or modify rows in an underlying table if the defining query of the
E. Delete statements can always be done on a table tough a view.
F. Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and
the PRIMARY KEY columns are not referenced in the defining query of the view.
Answer: C,D,F
76
NO.232 Examine this Statement which returns the name of each employee and their manager,
SELECT e.last name AS emp,,m.last_name AS mgr
FROM employees e JOIN managers m
ON e.manager_ id = m. employee_ id ORDER BY emp;
You want to extend the query to include employees with no manager. What must you add before
JOIN to do this?
A. CROSS
B. FULL OUTER
C. LEFT OUTER
D. RIGHT OUTER
Answer: C
NO.233 The PRODUCT_INFORMATION table has a UNIT_PRICE column of data type NUMBER(8, 2).
Evaluate this SQL statement:
SELECT TO_CHAR(unit_price,'$9,999') FROM PRODUCT_INFORMATION;
Which two statements are true about the output?
A. A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,024.
B. A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,023.
C. A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as $1,0236.
D. A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as $1,023.
E. A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as #####
Answer: A,E
NO.234 Which statement will return a comma-separated list of employee names in alphabetical
order for each department in the EMP table?
A. SELECT deptno,LISTAGG(ename, ' , ') WITHIN GROUP AS employee_list FROM emp GROUP BY
deptno;
B. SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP AS employee_list FROM emp GROUP BY
deptno ORDER BY ename;
C. SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (GROUP BY deptno) AS employee_list FROM
emp ORDER BY ename;
D. SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) AS employee_list FROM
emp GROUP BY deptno;
Answer: D

A. All remaining employee names will appear in an ascending order
B. The names of employees remaining the maximum salary will appear first in an ascending order
C. All remaining employee names will appear in ascending order
77
D. All remaining employee names will appear in descending order

E. The names of employees maximum salary will appear fist to descending order
F. The names of employees maximum salary will appear fist to ascending order
Answer: C,E
NO.236 Which statements are true regarding primary and foreign key constraints and the effect
they can have on table data?
A. A table can have only one primary key but multiple foreign keys.
B. It is possible for child rows that have a foreign key to remain in the child table at the time the
parent row is deleted.
C. Primary key and foreign key constraints can be defined at both the column and table level.
D. Only the primary key can be defined the column and table level.
E. It is possible for child rows that have a foreign key to be deleted automatically from the child table
at the time the parent row is deleted.
F. The foreign key columns and parent table primary key columns must have the same names.
G. A table can have only one primary key and one foreign key.
Answer: A,B,C,E
NO.237 Which two statements are true regarding non equijoins?

A. The ON clause can be used.
B. The USING clause can be used.
C. The SQL:1999 compliant ANSI join syntax must be used.
D. Table aliases must be used.
E. The Oracle join syntax can be used.
Answer: A,E
NO.238 Which two statements are true about views?

A. Views can be indexed.
B. Theethi CHEcK clause prevents certalin rows from being updated or inserted in the underying table
through the view.
C. Tables in the defining query of a view must always exist in order to create the view.
D. Views can be updated without the need to re-grant privileges on the view.
E. The wITH CHECK clause prevents certain rows from being displayed when querying the view.
Answer: B,D
NO.239 Which two statements are true about substitution variables?

A. A substitution variable used to prompt for a column name must be endorsed in single quotation
marks.
B. A substitution variable used to prompt for a column name must be endorsed in double quotation
marks.
C. A substitution variable prefixed with & always prompts only once for a value in a session.
D. A substitution variable can be used with any clause in a SELECT statement.
E. A substitution variable can be used only in a SELECT statement.
78
F. A substitution variable prefixed with 6 prompts only once for a value in a session unless is set to
undefined in the session.
Answer: D,F
NO.240 Which statement is true about aggregate functions?

A. The AVG function implicitly converts NULLS to zero
B. The MAX and MIN functions can be used on columns with character data types
C. Aggregate functions can be used in any clause of a SELECT statement
D. Aggregate functions can be nested to any number of levels
Answer: B
NO.241 Examine the description of the BOOKS_TRANSACTIONS table:
Which two WHERE conditions give the same result?

A. WHERE borrowed_date = SYSDATE AND (transaction_type ='RM' OR member_id IN
('A101','A102'));
B. WHERE borrowed_date = SYSDATE AND transaction_type ='RM' OR member_id IN ('A101','A102');
C. WHERE borrowed_date = SYSDATE AND (transaction_type ='RM' AND member_id='A101' OR
member_id ='A102'));
D. WHERE (borrowed_date = SYSDATE AND transaction_type ='RM') OR member_id IN
('A101','A102');
E. WHERE borrowed_date = SYSDATE AND (transaction_type ='RM' AND (member_id ='A101' OR
member_id ='A102') );
Answer: B,D

ALTER TABLE employees SET UNUSED (department_id);
Which two are true?
A. A query can display data from the DEPARTMENT_ID column.
B. The storage space occupied by the DEPARTMENT_ID column is released only after a COMMIT is
issued.
C. The DEPARTMENT_ID column is set to null for all tows in the table
79
D. A new column with the name DEPARTMENT_ID can be added to the EMPLOYEES table.
E. No updates can be made to the data in the DEPARTMENT_ID column.
F. The DEPARTMENT_ID column can be recovered from the recycle bin
Answer: D,E
NO.243 Examine the data in the ENPLOYEES table:
Which statement will compute the total annual compensation tor each employee?
A. SECECT last_namo, (menthy_salary + monthly_commission_pct) * 12 AS annual_comp
FROM employees;
B. SELCECT last_namo, (monthly_salary * 12) + (monthly_commission_pct * 12) AS annual_comp
FROM employees
C. SELCECT last_namo, (monthly_salary * 12) + (menthy_salary * 12 * NVL
(monthly_commission_pct, 0)) AS annual_comp FROM employees
D. SELCECT last_namo, (monthly_salary * 12) + (menthy_salary * 12 * monthly_commission_pct)
AS annual_comp FROM employees
Answer: C
NO.244 Which two are true about the precedence of opertors and condtions
A. + (addition) has a higher order of precedence than * (mliplpition)
B. NOT has a higher order of precedence than AND and OR in a condition.
C. AND and OR have the same order of precedence in a condition
D. Operators are evaluated before conditions.
E. || has a higher order of precedence than +(addition)
Answer: B,D
NO.245 You need to calculate the number of days from 1st January 2019 until today.
Dates are stored in the default format of DD-MON-RR.
Which two queries give the required output?
A. SELECT SYSDATE-TO_DATE ('01-JANUARY-2019') FROM DUAL;
B. SELECT TO_DATE (SYSDATE, 'DD/MONTH/YYYY')-'01/JANUARY/2019' FROM DUAL;
C. SELECT ROUND (SYSDATE-TO_DATE ('01/JANUARY/2019')) FROM DUAL;
D. SELECT TO_CHAR (SYSDATE, 'DD-MON-YYYY')-'01-JAN-2019' FROM DUAL;
E. SELECT ROUND (SYSDATE- '01-JAN-2019') FROM DUAL;
Answer: A,C
NO.246 Examine the data in the NEW_EMPLOYEES table:
80
Examine the data in the EMPLOYEES table:
You want to:

1. Update existing employee details in the EMPLOYEES table with data from the NEW EMPLOYEES
table.
2. Add new employee detail from the NEW_ EMPLOYEES able to the EMPLOYEES table.
Which statement will do this:
A. MERGE INTO employees e
USING new employees ne
WHERE e.employee_id = ne.employee_ id
WHEN MATCHED THEN
UPDATE SET e.name = ne.name, e.job_id = ne.job_id,e.salary =ne. salary
INSERT VALUES (ne. employee_id,ne.name, ne.job_id,ne.salary) ;
B. MERGE INTO employees e
USING new_employees n
ON (e.employee_id = ne.employee_id)
WHEN MATCHED THEN
UPDATE SET e.name = ne.name, e.job id = ne.job_id,e.salary =ne. salary
INSERT VALUES (ne. employee_id,ne.name,ne.job_id,ne.salary);
C. MERGE INTO employees e
USING new employees ne
ON (e.employee_id = ne.employee_id)
WHEN FOUND THEN
81
UPDATE SET e.name =ne.name, e.job_id=ne.job_id, e.salary =ne.salary

WHEN NOT FOUND THEN
INSERT VALUES (ne.employee_id,ne.name,ne.job_id,ne.salary) ;
D. MERGE INTO employees e
USING new_employees n
WHERE e.employee_id = ne.employee_id
WHEN FOUND THEN
UPDATE SET e.name=ne.name,e.job_id =ne.job_id, e.salary=ne.salary
WHEN NOT FOUND THEN
INSERT VALUES (ne.employee_ id,ne.name,ne.job id,ne.salary) ;
Answer: B
NO.247 Which two are true about self joins?

A. They are always equijoins.
B. They require the NOT EXISTS operator in the join condition.
C. They have no join condition.
D. They can use INNER JOIN and LEFT JOIN.
E. They require table aliases.
F. They require the EXISTS opnrator in the join condition.
Answer: D,E
Which query is valid?

A. SELECT dept_id, join date, SUM(salary) FROM employees GROUP BY dept_id,join_date;
B. SELECT dept_id, MAX (AVG(salary)) FROM employees GROUP BY dept_id;
C. SELECT dept_id, AVG(NAX(salary)) FROM employees GROUP BY dept_id;
D. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id;
Answer: A
NO.249 Examine this list of requirements for a sequence:

1. Name:EMP_SEQ
2. First value returned:1
3. Duplicates are never permitted.
4. Provide values to be inserted into the EMPLOYEES.EMPLOYEE_ID COLUMN.
5. Reduce the chances of gaps in the values.
Which two statements will satisfy these requirements?
82
A. CREATE SEQUENCE emp_seq START WITH 1 INCRENENT BY 1 NOCACHE;

B. CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CYCLE;
C. CREATE SEQUENCE emp_seq NOCACHE;
D. CREATE SEQUENCE emp_seq START WITH 1 CACHE;
E. CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CACHE;
F. CREATE SEQUENCE emp_seq;
Answer: A,C
NO.250 Which two statements are true? (Choose two.)

A. The USER SYNONYMS view can provide information about private synonyms.
B. The user SYSTEM owns all the base tables and user-accessible views of the data dictionary.
C. All the dynamic performance views prefixed with V$ are accessible to all the database users.
D. The USER OBJECTS view can provide information about the tables and views created by the user
only.
E. DICTIONARY is a view that contains the names of all the data dictionary views that the user can
access.
Answer: A,E
NO.251 BOOK_SEQ is an existing sequence in your schema.

Which two CREATE TABLE commands are valid?
A. CREATE TABLE bookings (
bk_id NUMBER(4) NOT NULL PRIMARY KEY,
start_date DATE NOT NULL,
end_date DATE DEFAULT SYSDATE);
B. CREATE TABLE bookings (
bk_id NUMBER(4) NOT NULL DEFAULT book_seq.CURRVAL,
start_date DATE NOT NULL,
end_date DATE DEFAULT SYSDATE);
C. CREATE TABLE bookings (
bk_id NUMBER(4) DEFAULT book_seq.CURRVAL,
start_date DATE DEFAULT SYSDATE,
end_date DATE DEFAULT start date);
D. CREATE TABLE bookings ( bk_id NUMBER(4),
end_date DATE DEFAULT (end_date >= start_date));
E. CREATE TABLE bookings (
bk_id NUMBER(4) DEFAULT book_seq.NEXTVAL PRIMARY KEY,
end_date DATE DEFAULT SYSDATE NOT NULL);
Answer: A,E
NO.252 View the Exhibit and examine the structure of the ORDERS table.
The columns ORDER_MODE and ORDER TOTAL have the default values'direct "and
respectively.
83
Which two INSERT statements are valid? (Choose two.)

A. INSERT INTO (SELECT order_id, order date, customer_id FROM orders) VALUES (1, '09-mar-
2007",101);
B. INSERT INTO orders (order_id, order_date, order mode,customer_id, order_total) VALUES (1,
TO_DATE (NULL),'online',101, NULL) ;
C. INSERT INTO orders VALUES (1, '09-mar-2007', 'online',' ',1000);
D. INSERT INTO orders (order id, order_date, order mode, order_total)VALUES (1,'10-mar-
2007','online', 1000)
E. INSERT INTO orders VALUES('09-mar-2007',DEFAULT,101, DEFALLT);
Answer: A,E
NO.253 Examine the ORDER _ITEms table:
Which two queries return rows where QUANTITY is a multiple of ten?

A. SELECT * FROM order_ items WHERE quantity = TRUNC (quantity, -1);
B. SELECT * FROM order_ items WHERE MOD (quantity, 10) = 0;
C. SELECT" FROM order_ items WHERE FLOOR (quantity / 10) = TRUNC (quantity / 10);
D. SELECT FROM order_ items WHERE quantity / 10 = TRUNC (quantity);
E. SELECT" FROM order_ _items WHERE quantity = ROUND (quantity, 1);
Answer: A,B
NO.254 Which two will execute successfully?

A. SELECT COALESCR('DATE', SYSDATE) FROM DUAL;
B. SELECT NVL('DATE',SYSDATE) FROM DUAL;
C. SELECT COALESCE(O,SYSDATE) TRCH DUAL;
D. SELECT NVL('DATE',200) FROM (SELECT NULL AS "DATE" FROM DUAL);
E. SELECT COALESCE('DATE',SYSDATE) FROM (SELECT NULL AS "DATE" FROM DUAL) ;
Answer: B,D
84
You write this failing statement:

SELECT dept_no AS department_id, MAX (salary) As max_sal
FROM employees
WHERE salary >10000
GROUP BY department_id
ORDER BY max_sal;
Which clause causes the error?
A. ORDER BY
B. WHERE
C. GROUP BY
D. SELECT
Answer: C
NO.256 Which two true about a sql statement using SET operations such as UNION?
A. The data type of each column returned by the second query must be implicitly convertible to the
data type of the corresponding column returned by the first query
B. The data type of each column retuned by the second query must exactly match the data type of
the corresponding column returned by the first query
C. The number, but not names, of columns must be identical for all SELECT statements in the query
D. The data type group of each column returned by the second query must match the data type
group of the corresponding column returned by the first query
E. The names and number of columns must be identical for all SELECT statements in the query.
Answer: A,C
NO.257 Examine the description of the PRODUCT_ STATUS table:
The STATUS column contains the values IN STOCK or OUT OF STocK for each row.
A. SELECT prod_id ||q'(' s not available)' 'CURRENT AVAILABILITY' FROM
product_ status WHERE status = 'OUT OF STOCK'
B. SELECT prod_id ||q"' s not available" FROM
85
product_ status WHERE status = 'OUT OF STOCK';

C. SELECT PROD_ID||q'('s not available)' FROM
product_ status WHERE status = 'OUT OF STOCK';
D. SELECT PROD_ID||q'('s not available)' "CURRENT AVAILABILITY"
FROM product_ status WHERE status = 'OUT OF STOCK';
E. SELECT prod_id q's not available" from product_ status WHERE status = 'OUT OF STOCK';
F. SELECT prod_id "CURRENT AVAILABILITY"||q' ('s not available)' from product_ status WHERE
status
= 'OUT OF STOCK';
Answer: C,D
NO.258 Examine this description of the PRODUCTS table:

You successfully execute this command:
CREATE TALE new_prices(prod_id NUBER(2),price NUMBER(8,2));
Which two statements execute without errors?
A. MERGE INTO new_prices n
USING(SELECT*FROM products)p
WHEN MATECHED THEN
UPDATE SET n.price=p.cost*.01
INSERT(n.prod_id,n.price)VALUES (p.prod_id,cost*01)
WHERE(p.cost<200);
B. MERGE INTO new_prices n
USING(SELECT*FROM product WHERE cost>150) p
ON (n.prod_id=p.prod_id)
WHEN NATCHED THEN
DELETE WHERE(p.cost<200)
INSERT (n.prod_id,n.price)VALUES (p.prod_id,p.cost*.01);
C. MERGE INTO new_prices n
ON (n.prod_id=p.prod_id)
WHEN NATCHED THEN
UPDATE SET n.price=p.cost*.01
DELETE WHERE (p.cost<200);
D. MERGE INTO new_prices n
USING products p
WHEN NOT NATCHED THEN
INSERT (n.prod_id, n.price)VALUES (p.prod_id,cost*.01)
WHERE (p.cost <200);
Answer: B,C
NO.259 Examine this partial statement:

SELECT ename, sal,comm FROM emp
Now examine this output:
86
WHICH ORDER BY clause will generate the displayed output?

A. ORDER BY NVL(enam,0) DESC, ename
B. ORDER BY NVL(comm,0) ASC NULLS FIRST, ename
C. ORDER BY NVL(comm,0) ASC NULLS LAST, ename
D. ORDER BY comm DESC NULLS LAST, ename
Answer: A,D
NO.260 Which three queries execute successfully?

A. SELECT (SYSDATE-DATE '2019-01-01') / 1 FROM DUAL;
B. SELECT 1 / SYSDATE - DATE '2019-01-01' FROM DUAL;
C. SELECT SYSDATE / DATE '2019-01-01' - 1 FROM DUAL
D. SELECT SYSDATE - DATE '2019-01-01' - 1 FROM DUAL;
E. SELECT 1 - SYSDATE- DATE '2019-01-01' FROM DUAL;
F. SELECT SYSDATE - 1 - DATE'2019-01-01' EROM DUAL;
Answer: A,D,F
NO.261 MANAGER is an existing role with no privileges or roles.

EMP is an existing role containing the CREATE TABLE privilege.
EMPLOYEES is an existing table in the HR schema.
Which two commands execute successfully?
A. GRANT CREATE SEQUENCE TO manager, emp;
B. GRANT SELECT, INSERT ON hr.employees TO manager WITH GRANT OPTION:
C. GRANT CREATE TABLE, emp TO manager;
87
D. GRANT CREATE TABLE, SELECT ON hr. employees TO manager;

E. GRANT CREATE ANY SESSION, CREATE ANY TABLE TO manager;
Answer: A,C
NO.262 .No user-defined locks are used in your database.

Which three are true about Transaction Control Language (TCL)?
A. COMMIT erases all the transaction's savepoints and releases its locks.
B. COMMIT ends the transaction and makes all its changes permanent.
C. ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes but does not
release its locks.
D. ROLLBACK to SAVEPOTNT undoes the transaction's changes made since the named savepoint and
then ends the transaction.
E. ROLLBACK without the TO SAVEPOINT clause undoes alt the transaction's changes, releases its
locks, and erases all its savepoints.
F. ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes but does not
erase its savepoints.
Answer: A,B,E
NO.263 Which three queries use valid expressions?

A. SELECT product_id,(unit_price * 0.15 / (4.75 + 552.25)) FROM products;
B. SELECT product_id,(expiry_date - delivery_date) * 2 FROM products;
C. SELECT product_id,unit_price || 5 "Discount" , unit_price + surcharge - discount FROM products;
D. SELECT product_id, expiry_date * 2 from products;
E. SELECT product_id,unit_price,5 "Discount", unit_price + surcharge-discount FROM products;
F. SELECT product_id, unit_price, unit_price + surcharge FROM products;
Answer: A,B,F
NO.264 Examine these statements and results:

SQL> SELECT COUNT(*) FROM emp
COUNT(*)
---------------------
14
sQL> CREATE GLOBAL TEMPORARY TABLE t emp As SELECT * FROM emp;
Table created
SQL> INSERT INTo temp SELECT * FROM emp;
14 rows created
SQL> COMMIT:
Commit complete*
SQL> INSERT INTo temp SELECT * EROM emp;
14. rows created
SQL> SELECT COUNT(*) FROM t emp
How many rows are retrieved by the last query?
A. 28
B. 0
88
C. 14
D. 42
Answer: C
NO.265 Which three are true aboutprivileges and roles?

A. System prilgese always set prilge for an entire database.
B. PUBLIC acts as a default role granted to every user in a database.
C. A user has all object privileges for every object in their schema by default.
D. A role can contain a combination of several privileges and roles.
E. A role is owned by the user who created it.
F. All roles are owned by the sYs schema.
G. PUBIIC can be revoked from a user.
Answer: B,C,D
NO.266 Examine the data in the PRODUCTS table:
Examine these queries:

1. SELECT prod name, prod list
FROM products
WHERE prod 1ist NOT IN(10,20) AND category _id=1;
2. SELECT prod name, | prod _ list
FROM products
WHERE prod list < > ANY (10,20) AND category _id= 1;
SELECT prod name, prod _ list
FROM products
WHERE prod_ list <> ALL (10, 20) AND category _ id= 1;
Which queries generate the same output?
A. 1 and 3
B. 1, 2 and 3
C. 2 and 3
D. 1 and 2
Answer: A
NO.267 The SALES table has columns PROD_ID and QUANTITY_SOLD of data type NUMBER. Which
two queries execute successfully?
A. SELECT COUNT(prod_id) FROM sales WHERE quantity_sold>55000 GROUP BY prod_id;
89
B. SELECT prod_id FROM sales WHERE quantity_sold> 55000 GROUP BY prod_id HAVING COUNT(*)>
10;
C. SELECT COUNT(prod_id) FROM sales GROUP BY prod_id WHERE quantity_sold> 55000;
D. SELECT prod_id FROM sales WHERE quantity_sold> 55000 AND COUNT(*)> 10 GROUP BY
COUNT(*)> 10;
E. SELECT prod_id FROM sales WHERE quantity_sold> 55000 AND COUNT(*)> 10 GROUP BY prod_id
HAVING COUNT(*)> 10;
Answer: A,B
NO.268 Which two are true about the WITH GRANT OPTION clause?
A. The grantee can grant the object privilege to any user in the database, with of without including
this option.
B. The grantee must have the GRANT ANY OBJECT PRIVILEGE system prvilege to use this option.
C. It can be used when granting privileges to roles.
D. It can be used for system and object privileges.
E. It cannot be used to pass on privileges to PUBLIC by the grantee.
F. It can be used to pass on privileges to other users by the grantee.
Answer: A,F
NO.269 Which three are true about the CREATE TABLE command?
A. It can include the CREATE...INDEX statement for creating an index to enforce the primary key
constraint.
B. The owner of the table should have space quota available on the tablespace where the table is
defined.
C. It implicitly executes a commit.
D. It implicitly rolls back any pending transactions.
E. A user must have the CREATE ANY TABLE privilege to create tables.
F. The owner of the table must have the UNLIMITED TABLESPACE system privilege.
Answer: A,B,C
NO.270 Which three actions can you perform only with system privileges?
A. Truncate a table in another schema.
B. Access flat files via a database, which are stored in an operating system directory.
C. Log in to a database.
D. Query any table in a database.
E. Use the WITH GRANT OPTION clause.
F. Execute a procedure in another schema.
Answer: C,D,F

SELECT employee_id,first_name,salary
FROM employees
WHERE hire_date>'&1';
90
Which two methods should you use to prevent prompting for a hire date value when this query is
executed?
A. Use the DEFINE command before executing the query.
B. Store the query in a script and pass the substitution value to the script when executing it.
C. Replace'&1' with'&&1' in the query.
D. Execute the SET VERIFY OFF command before executing the query.
E. Use the UNDEFINE command before executing the query.
F. Execute the SET VERIFY ON command before executing the query.
Answer: A,B
NO.272 Examine the data in the CUST NAME column of the CUSTOMERS table:
CUST_NAME
------------------------------
Renske Ladwig
Jason Mallin
Samuel McCain
Allan MCEwen
Irene Mikkilineni
Julia Nayer
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?
A. WHERE INITCAP (SUBSTR(cust_name, INSTR(cust_name,'') +1)) IN ('MC%','Mc%)
B. WHERE UPPER (SUBSTR(cust_name, INSTR(cust_name, '') +1)) LIKE UPPER('MC%')
C. WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,'') +1)) LIKE'Mc%'
D. WHERE SUBSTR(cust_name,INSTR(cust_name,'') +1) LIKE'Mc%' OR'MC%'
E. WHERE SUBSTR(cust_name, INSTR(cust_name,'') +1) LIKE'Mc%'
Answer: B,C
NO.273 Which three items does a direction of a relationship contain?

A. an attribute
B. a cardinality
C. label
D. an optionality
E. a unique identifier
F. an entity
Answer: A,B,F
NO.274 Which three statements are true about a self join?

A. It must be an inner join.
B. It must be an equijoin.
C. The query must use two different aliases for the table.
D. The on clause can be used.
E. The on clause must be used.
91
F. It can be an outer join.

Answer: C,D,F
NO.275 Which two statements are true about the results of using the intersect operator in
compound queries?
A. intersect ignores nulls.
B. Reversing the order of the intersected tables can sometimes affect the output.
C. Column names in each select in the compound query can be different.
D. intersect returns rows common to both sides of the compound query.
E. The number of columns in each select in the compound query can be different.
Answer: C,D
NO.276 Which three statements are true about indexes and their administration in an Oracle
database?
A. An INVISIBLE index is not maintained when Data Manipulation Language (DML) is performed on its
underlying table.
B. An index can be created as part of a CREATE TABLE statement.
C. A DROP INDEX statement always prevents updates to the table during the drop operation
D. A UNIQUE and non-unique index can be created on the same table column
E. A descending index is a type of function-based index
F. If a query filters on an indexed column then it will always be used during execution of the query
Answer: B,C,E
NO.277 You and your colleague Andrew have these privileges on the EMPLOYEE_RECORDS table:
1. SELECT
2. INSERT
3. UPDATE
4. DELETE
You connect to the database instance an perform an update to some of the rows in
EMPLOYEE_RECORDS, but don't commit yet.
Andrew connects to the database instance and queries the table
No othet user are accessing the table
Which two statements ate true at this point?
A. Andrew will be able to modify any rows in the table that have not been modified by your
transaction
B. Andrew will be unable to see the changes you have made
C. Andrew will be able to see the changes you habe made
D. Andrew will be unable to perform any INSERT, UPDATE of DELETE on the teble
E. Andrew will be able to SELECT from the table, but be unable to modify any existing rows.
Answer: A,B
NO.278 Which three are true about multiple INSERT statements?

A. They can be performed only by using a subquery.
B. They can be performed on relational tables.
92
C. They can be performed on views.

D. They can be performed on remote tables.
E. They can be performed on external tables using SQL*Loader.
F. They can insert each computed row into more than one table.
Answer: A,B,D
NO.279 Which two statements are true about conditional INSERT ALL?
A. Each row returned by the subquery can be inserted into only a single target table.
B. It cannot have an ELSE clause.
C. The total number of rows inserted is always equal to the number of rows returned by the
subquery
D. A single WHEN condition can be used for multiple INTO clauses.
E. Each WHEN condition is tested for each row returned by the subquery.
Answer: C,E
NO.280 Which two actions can you perform with object privileges?
A. Create roles.
B. Delete rows from tables in any schema except sys.
C. Set default and temporary tablespaces for a user.
D. Create FOREIGN KEY constraints that reference tables in other schemas.
E. Execute a procedure or function in another schema.
Answer: B,D
NO.281 Which three are true about the MERGE statement?

A. It can merge rows only from tables.
B. It can use views to produce source rows.
C. It can combine rows from multiple tables conditionally to insert into a single table.
D. It can use subqueries to produce source rows.
E. It can update the same row of the target table multiple times.
F. It can update, insert, or delete rows conditionally in multiple tables.
Answer: B,C,D
NO.282 Examine the description of the PRODUCT_STATUS table:
The STATUS column contains the values 'IN STOCK' or 'OUT OF STOCK' for each row
A. SELECT prod_id "CURRENT AVAILABILITY" || q'('s not available)' FROM product_status WHERE
status = 'OUT OF STOCK';
B. SELECT prod_id || q's not available'' FROM product_status WHERE status='OUT OF STOCK';
93
C. SELECT prod_id || q'('s not available)' "CURRENT AVAILABILITY" FROM product_status WHERE
D. SELECT prod_id || q'('s not available)' FROM product_status WHERE status = 'OUT OF STOCK';
E. SELECT prod_id || q'('s not available)' 'CURRENT AVAILABILITY' FROM product_status WHERE
F. SELECT prod_id || q"'s not available" FROM product_status WHERE status = 'OUT OF STOCK';
Answer: C,D
NO.283 Which three statements are true about multiple row subqueries?
A. They can contain HAVING clauses.
B. Two or more values are always returned from the subquery.
C. They cannot contain subquery.
D. They can return multiple columns.
E. They can contain GROUP BY clauses.
Answer: A,B,E
NO.284 A session's NLS_DATE_FORMAT is set to DD Mon YYYY .

Which two queries return the value 1 Jan 2019?
A. SELECT to_date(' 2019-01-01 ', 'YYYY -MM-DD' ) FROM DUAL;
B. SELECT DATE '2019-01-01' FROM DUAL ;
C. SELECT TO_CHAR('2019-01-01') FROM DUAL; 2019-01-01
D. SELECT '2019-01-01' FROM DUAL ; 2019-01-01
E. SELECT TO_ DATE('2019-01-01') FROM DUAL;
Answer: A,B
NO.285 Which two are true about the NVL, NVL2, and COALESCE functions?
A. The first expression in NVL2 is never returned.
B. NVL2 can have any number of expressions in the list.
C. COALESCE stops evaluating the list of expressions when it finds the first null value.
D. COALESCE stops evaluating the list of expressions when it finds the first non-null value.
E. NVL must have expressions of the same data type.
F. NVL can have any number of expressions in the list.
Answer: A,D

DELETE FROM employees e
WHERE EXISTS
(SELECT'dummy'
FROM emp_history
WHERE employee_id = e.employee_id)
Which two are true?
A. The subquery is executed for every row in the EMPLOYEES table.
B. The subquery is not a correlated subquery.
94
C. The subquery is executed before the DELETE statement is executed.

D. All existing rows in the EMPLOYEE table are deleted.
E. The DELETE statement executes successfully even if the subquery selects multiple rows.
Answer: A,E
NO.287 Which statement executes successfully?

A. SELECT TO_DATE(TO_NUMBER(INTERVATL '800' SECOND)) FROM DUAL;
B. SELECT TO_NUMBER(INTERVAL'800' SECOND, 'HH24:MM') FROM DUAL;
C. SELECT TO_DATE(INTERVAL '800' SECOND,'HH24:MM') FROM DUAL;
D. SELECT TO_NUWBER(TO_DATE(INTERVAL '800' SECOND)) FROM DUAL;
E. SELECT TO_CHAR(INTERVAL '800' SECOND, 'HH24:MM') FROM DUAL;
Answer: E
NO.288 Which three are key components of an Entity Relationship Model?

A. a table
B. an attribute
C. a unique identifier
D. an activity
E. a relationship
F. an entity
Answer: B,E,F
NO.289 Examine the description of the CUSTONERS table
CUSTON is the PRIMARY KEY.

You must derermine if any customers'derails have entered more than once using a different
costno,by listing duplicate name
Which two methode can you use to get the requlred resuit?
A. RIGHT OUTER JOIN with seif join
B. FULL OUTER JOIN with seif join
C. SUBQUERY
D. seif join
E. LEFT OUTER JOIN with seif join
Answer: C,D
95
NO.290 You issued this command: DROP TABLE hr. employees;

A. ALL constraints defined on HR, EMPLOYEES are dropped.
B. The HR. EMPLOYEES table may be moved to the recycle bin.
C. Synonyms for HR EMPLOYEES are dropped.
D. Sequences used to populate columns in the HR. EMPLOYEES table are dropped.
E. All indexes defined on HR, EMPLOYEES are dropped.
F. Views referencing HR, EMPLOYEES are dropped.
Answer: A,B,E
NO.291 Which three statements are true about single row functions?
A. They can be used only in the where clause of a select statement.
B. They can accept only one argument.
C. They return a single result row per table.
D. The argument can be a column name, variable, literal or an expression.
E. They can be nested to any level.
F. The date type returned can be different from the data type of the argument.
Answer: D,E,F
Examine this query:
Which line produces an error?

A. Line 7
B. Line 8
C. Line 3
96
D. Line 5
Answer: C
NO.293 Examine the description of the BRICKS table;
Examine the description of the BRICKS_STAGE table;
Which two queries execute successfully?

A. SELECT shape,color,weight from bricks
MINUS
SELECT * FROM bricks_stage;
B. SELECT shape,color FROM bricks
MINUS
SELECT WEIGHT,color FROM bricks_stage;
C. select * from bricks
MINUS
select * from bricks_stage;
D. SELECT shape,color FROM bricks
MINUS
SELECT color,shape FROM bricks_stage;
E. SELECT brick_id,shape FROM bricks
MINUS
SELECT WEIGHT,COLOR from bricks_stage;
Answer: D,E
NO.294 Which three actions can you perform by using the ALTER TABLE command?
A. Drop pseudo columns from a table.
B. Restrict all DML statements on a table.
C. Drop all columns simultaneously from a table.
D. Lock a set of rows in a table CE Rename a table.
97
E. Rename a table
F. Enable or disable constraints on a table.
Answer: D,E,F
NO.295 which is true about the round,truncate and mod functions>?

A. ROUND(MOD(25,3),-1) IS INVALID
B. ROUND(MOD(25,3),-1) AND TRUNC(MOD(25,3),-1) ARE BOTH VALID AND GIVE THE SAME RESULT.
C. ROUND(MOD(25,3),-1) AND TRUNC(MOD(25,3),-1) ARE BOTH VALID AND GIVE THE DIFFERENT
RESULTS.
D. TRUNC(MOD(25,3),-1) IS INVALID.
Answer: B
NO.296 Which four statements are true about constraints on Oracle tables?
A. A Column can have only one CHECK Constraint.
B. A NOT NULL Constraint can be defined at the table level.
C. A UNIQUE constraint permits NULLS.
D. A PRIMARY KEY Constraint can be added after a table has been created and populated.
E. A CHECK Constraint can refer to values in other rows.
F. A UNIQUE Constraint can use a pre-existing index on the constrained column or columns.
G. A FOREIGN KEY Column can contain NULLS.
Answer: C,D,F,G
NO.297 Which statement is true about TRUNCATE and DELETE?

A. For large tables TRUNCATE is faster than DELETE.
B. For tables with multiple indexes and triggers is faster than TRUNCATE.
C. You can never TRUNCATE a table if foreign key constraints will be violated.
D. You can never tows from a table if foreign key constraints will be violated.
Answer: A
NO.298 The STORES table has a column START_ DATE of data type DATE, containing the date the
row was inserted.
You only want to display details of rows where START_ DATE is within the last 25 months.
Which WHERE clause can be used?
A. WHERE MONTHS_ BETWEEN (SYSDATE, start_ date) <= 25
B. WHERE MONTHS_ BETWEEN (start_ date, SYSDATE) <= 25
C. WHERE TO_ NUMBER (start_ date - SYSDATE) <= 25
D. WHERE ADD_ MONTHS (start_ date, 25) <= SYSDATE
Answer: A
NO.299 Examine the description of the BOOKS table:
98
The table has 100 rows.

Examine this sequence of statements issued in a new session;
INSERT INTO BOOKS VALUES ('ADV112' , 'Adventures of Tom Sawyer', NULL, NULL);
SAVEPOINT a;
DELETE from books;
ROLLBACK TO SAVEPOINT a;
ROLLBACK;
A. The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row
still to be committed.
B. The second ROLLBACK command does nothing.
C. The first ROLLBACK command restores the 101 rows that were deleted and commits the inserted
row.
D. The second ROLLBACK command replays the delete.
E. The second ROLLBACK command undoes the insert.
Answer: A,E

SELECT INTERVAL '100' MONTH DURATION FROM DUAL;
What will be the output?
A. DURATION
+08-04
B. DUFATION
+100
C. DURATION
+08
D. an error
Answer: A
NO.301 Examine these statements which execute successfully:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24 MI: SS'
ALTER SESSION SET TIME_ ZONE = '-5:00';
SELECT DBTIMEZONE, SYSDATE FROM DUAL
Examine the result:
99
If LOCALTIMESTAMP was selected at the same time what would it return?

A. 11-JUL-2019 6,00,00,00000000 AM - 05:00
B. 11-JUL-2019 11,00,00,00000000 AM
C. 11-JUL-2019 6,00,00,000000 AM
D. 11-JUL-2019 11,00,00,000000AM -05:00
Answer: B
NO.302 Examine the description of the PRODUCTS table:
Which three queries use valid expressions?

A. SELECT produet_id, unit_pricer, 5 "Discount",unit_price+surcharge-discount FROM products;
B. SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;
C. SELECT ptoduct_id, (expiry_date-delivery_date) * 2 FROM products;
D. SPLECT product_id, expiry_date * 2 FROM products;
E. SELEGT product_id, unit_price, unit_price + surcharge FROM products;
F. SELECT product_id,unit_price || "Discount", unit_price + surcharge-discount FROM products;
Answer: B,C,E
NO.303 Which two statements are true about the ORDER BY clause?
A. Numeric values are displayed in descending order if they have decimal positions.
B. Only columns that are specified in the SELECT list can be used in the ORDER BY clause.
C. In a character sort, the values are case-sensitive.
D. Column aliases can be used in the ORDER BY clause.
E. NULLS are not included in the sort operation.
Answer: C,D
NO.304 View the Exhibits and examine the structure of the COSTS and PROMOTIONS tables.
100
You want to display PROD IDS whose promotion cost is less than the highest cost PROD ID in a pro
motion time interval.
Examine this SQL statement:
SELECT prod id
FROM costs
WHERE promo id IN
(SELECT promo id
FROM promotions
WHERE promo_cost < ALL
(SELECT MAX (promo cost)
FROM promotions
GROUP BY (promo_end date-promo_begin_date)) );
A. It executes successfully but does not give the required result.
B. It gives an error because the ALL keyword is not valid.
C. It gives an error because the GROUP BY clause is not valid
D. It executes successfully and gives the required result.
Answer: A
NO.305 Examine the description of the CUSTONERS table:
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. LEFT OUTER JOIN with self join
B. PULL OUTER JOIN with self join
C. subquery
D. RIGHT OUTER JOIN with self join
E. self Join
Answer: C,E
101

Exam 1Z0-071: IT Certification Guaranteed, The Easy Way!

Uploaded by

Copyright:

Available Formats

Exam 1Z0-071: IT Certification Guaranteed, The Easy Way!

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Exam 1Z0-071: IT Certification Guaranteed, The Easy Way!

Uploaded by

Copyright:

Available Formats

IT Certification Guaranteed, The Easy Way!

Title : Oracle Database SQL

NO.2 Which two statements are true about outer Joins?

NO.3 Examine this statement:

NO.4 Which two are true about rollbacks?

NO.5 Which two statements are true about Entity Relationships?

NO.6 Examine this statement which executes successfully:

NO.8 Examine thee statements which execute successfully:

GRANT SELECT ON scott. Emp To finance WITH GRANT OPTION;

NO.9 Examine the description of the PRODUCT_ DETAILS table:

Which two statements are true?

NO.10 Which two statements are true about a self join?

NO.11 Examine the description of the MEMBERS table;

C. CORADER BY 1, LNAME DESC

NO.12 Examine the description or the BOOKS_TRANSACTIONS table:

NO.13 Which two statements are true about external tables?

NO.14 Examine the description of the sales table.

NO.15 Which three are true about privileges?

NO.17 Which two statements execute successfully?

D. SELECT TO _ CHAR (TO_ DATE ('2019-DEC-25 03:30','YYYY-MON-DD HH12:MI'))

NO.19 Which two are true about global temporary tables?

NO.20 Which two statements will do an implicit conversion?

NO.21 Examine this statement which executes successfully:

NO.25 Examine these statements:

NO.26 Examine this query which executes successfully:

NO.29 You create a table named 123.

NO.30 Which statement falls to execute successfully?

NO.31 Which two are true about the data dictionary?

NO.32 You execute this command:

NO.33 Which three statements are true regarding indexes?

NO.34 Which two statements are true about * _TABLES views?

NO.35 Examine the command to create the BOOKS table.

SQL> create table books(book id CHAR(6) PRIMARY KEY,

NO.40 Examine the description of the EMPLOYEES table:

ON emp.manager_ id = mgr. employee id

NO.41 Examine the BRICKS table:

You write this query:

NO.44 Examine the description of the CUSTOMERS table:

NO.45 Examine the description of the SALES table:

The SALES table has 5,000 rows.

NO.46 Examine these statements:

NO.47 Examine this description of the EMP table:

You execute this query:

NO.48 Which two queries execute successfully?

NO.49 Examine the description of the ENPLYEES table:

NO.51 Examine this business rule:

Which two statements are true?

that must be resolved into 1-to-many relationships.

NO.52 You execute this command:

NO.53 Examine the description of EMPLOYEES table:

NO.55 Examine the data in the EMPLOYEES table:

NO.56 In which three situations does a new transaction always start?

NO.58 In your session NLS_ DATE_ FORMAT is set to DD-MON_RR.

Which two are true?

NO.60 Examine the description of the EMPLOYEES table:

NO.61 Examine the description of the transactions table:

Which statement will do this?

NO.66 Examine this statement: