Number: 1z0-071
Exam Name: Oracle Database SQL
Passing Score: 800
Time Limit: 120 min
File Version: 1.0
QUESTION 1:
Examine the description of the PROMOTIONS table:
You want to display the unique promotion costs in each promotion category. Which two queries can be used? (Choose two.)
A. SELECT DISTINCT promo_category || ‘ has ‘ || promo_cost AS COSTS FROM promotions ORDER BY 1;
B. SELECT DISTINCT promo_cost || ‘ in ‘ || DISTINCT promo_category FROM promotions ORDER BY 1;
C. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
D. SELECT promo_category DISTINCT promo_cost, FROM promotions ORDER BY 2;
E. SELECT promo_cost, promo_category FROM promotions ORDER BY 1;
Correct Answer: AC Section: (none) Explanation
Explanation/Reference:
QUESTION 2:
Examine the description of the PRODUCTS table:
Which three queries use valid expressions? (Choose three.)
A. SELECT product_id, unit_price, S “Discount”, unit_price + surcharge – discount FROM products;
B. SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;
C. SELECT product_id, (expiry_date – delivery_date) * 2 FROM products;
D. SELECT product_id, unit_price || 5 “Discount”, unit_price + surcharge – discount FROM products;
E. SELECT product_id, expiry_date * 2 FROM products;
F. SELECT product_id, unit_price, unit_price + surcharge FROM products;
Correct Answer: BCF
Section: (none) Explanation
Explanation/Reference:
QUESTION 3:
What is true about non-equijoin statement performance? (Choose two.)
A. The BETWEEN condition always performs less well than using the >= and <= conditions. B. The BETWEEN condition always performs better than using the >= and <= conditions.
C. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
D. Table aliases can improve performance.
E. The join syntax used makes no difference to performance.
Correct Answer: DE Section: (none) Explanation
Explanation/Reference:
QUESTION 4:
Which two are true? (Choose two.)
A. ADD_MONTHS adds a number of calendar months to a date.
B. CEIL requires an argument which is a numeric data type.
C. CEIL returns the largest integer less than or equal to a specified number.
D. LAST_DAY returns the date of the last day of the current month only.
E. LAST_DAY returns the date of the last day of the month for the date argument passed to the function.
F. LAST_DAY returns the date of the last day of the previous month only.
Correct Answer: AC Section: (none) Explanation
Explanation/Reference:
Reference: https://docs.oracle.com/database/121/SQLRF/functions025.htm https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions004.htm
QUESTION 5:
Which three statements are true about Oracle synonyms? (Choose three.)
A. A synonym cannot be created for a PL/SQL package.
B. A synonym can be available to all users.
C. A SEQUENCE can have a synonym.
D. Any user can drop a PUBLIC synonym.
E. A synonym created by one user can refer to an object belonging to another user.
Correct Answer: BCE Section: (none) Explanation
Explanation/Reference:
QUESTION 6:
Which two are true? (Choose two.)
A. CONCAT joins two character strings together.
B. CONCAT joins two or more character strings together.
C. FLOOR returns the largest positive integer less than or equal to a specified number.
D. INSTR finds the offset within a character string, starting from position 0.
E. INSTR finds the offset within a string of a single character only.
F. FLOOR returns the largest integer less than or equal to a specified number.
Correct Answer: AF Section: (none) Explanation
Explanation/Reference:
QUESTION 7:
Examine these SQL statements which execute successfully:
Which two statements are true after execution? (Choose two.)
A. The primary key constraint will be enabled and IMMEDIATE.
B. The foreign key constraint will be enabled and DEFERRED.
C. The primary key constraint will be enabled and DEFERRED.
D. The foreign key constraint will be disabled.
E. The foreign key constraint will be enabled and IMMEDIATE.
Correct Answer: AD Section: (none) Explanation
Explanation/Reference:
QUESTION 8:
Examine this SQL statement:
Which two are true? (Choose two.)
A. All existing rows in the ORDERS table are updated.
B. The subquery is executed before the UPDATE statement is executed.
C. The subquery is not a correlated subquery.
D. The subquery is executed for every updated row in the ORDERS table.
E. The UPDATE statement executes successfully even if the subquery selects multiple rows.
Correct Answer: AB Section: (none) Explanation
Explanation/Reference:
QUESTION 9:
Which two statements are true about TRUNCATE and DELETE? (Choose two.)
A. DELETE can use a WHERE clause to determine which row(s) should be removed.
B. TRUNCATE can use a WHERE clause to determine which row(s) should be removed.
C. TRUNCATE leaves any indexes on the table in an UNUSABLE state.
D. The result of a TRUNCATE can be undone by issuing a ROLLBACK.
E. The result of a DELETE can be undone by issuing a ROLLBACK.
Correct Answer: AE Section: (none) Explanation
Explanation/Reference:
QUESTION 10:
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 TO_NUMBER(start_date – SYSDATE) <= 25
B. WHERE MONTHS_BETWEEN(start_date, SYSDATE) <= 25
C. WHERE MONTHS_BETWEEN(SYSDATE, start_date) <= 25
D. WHERE ADD_MONTHS(start_date, 25) <= SYSDATE
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 11:
Which three are true about scalar subquery expressions? (Choose three.)
A. They can be nested.
B. They cannot be used in the VALUES clause of an INSERT statement.
C. A scalar subquery expression that returns zero rows evaluates to zero.
D. They can be used as default values for columns in a CREATE TABLE statement.
E. A scalar subquery expression that returns zero rows evaluates to NULL.
F. They cannot be used in GROUP BY clauses.
Correct Answer: ADE Section: (none) Explanation
Explanation/Reference:
Reference: https://docs.oracle.com/cd/B12037_01/server.101/b10759/expressions010.htm
QUESTION 12:
Examine this query:
Which two methods should you use to prevent prompting for a hire date value when this query is executed? (Choose two.)
A. Use the DEFINE command before executing the query.
B. Replace ‘&1’ with ‘&&1’ in the query.
C. Use the UNDEFINE command before executing the query.
D. Execute the SET VERIFY OFF command before executing the query.
E. Execute the SET VERIFY ON command before executing the query.
F. Store the query in a script and pass the substitution value to the script when executing it.
Correct Answer: AF Section: (none) Explanation
Explanation/Reference:
QUESTION 13:
You need to allow user ANDREW to:
1. Modify the TITLE and ADDRESS columns of your CUSTOMERS table.
2. GRANT that permission to other users. Which statement will do this?
A. GRANT UPDATE ON customers.title, customers.address TO andrew;
B. GRANT UPDATE (title, address) ON customers TO andrew;
C. GRANT UPDATE (title, address) ON customers TO andrew WITH GRANT OPTION;
D. GRANT UPDATE ON customers.title, customers.address TO andrew WITH ADMIN OPTION;
E. GRANT UPDATE ON customers.title, customers.address TO andrew WITH GRANT OPTION;
F. GRANT UPDATE (title, address) ON customers TO andrew WITH ADMIN OPTION;
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
QUESTION 14:
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? (Choose three.)
A. It will remove the DEPARTMENTS table from the database.
B. It will drop all indexes on the DEPARTMENTS table.
C. It will remove all views that are based on the DEPARTMENTS table.
D. It will remove all synonyms for the DEPARTMENTS table.
E. Neither can it be rolled back nor can the DEPARTMENTS table be recovered.
F. It will delete all rows from the DEPARTMENTS table, but retain the empty table.
Correct Answer: ABE Section: (none) Explanation
Explanation/Reference:
Reference: https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables010.htm#ADMIN01505
QUESTION 15:
Which three statements are true about Structured Query Language (SQL)? (Choose three.)
A. It requires that data be contained in hierarchical data storage.
B. It best supports relational databases.
C. It provides independence for logical data structures being manipulated from the underlying physical data storage.
D. It is the only language that can be used for both relational and object-oriented databases.
E. It guarantees atomicity, consistency, isolation, and durability (ACID) features.
F. It is used to define encapsulation and polymorphism for a relational table.
Correct Answer: BCE Section: (none) Explanation
Explanation/Reference:
QUESTION 16:
Which two statements are true about Oracle synonyms? (Choose two.)
A. Any user can create a PUBLIC synonym.
B. A synonym has an object number.
C. All private synonym names must be unique in the database.
D. A synonym can be created on an object in a package.
E. A synonym can have a synonym.
Correct Answer: BE Section: (none) Explanation
Explanation/Reference:
QUESTION 17:
Which is true about the ROUND, TRUNC and MOD functions?
A. TRUNC(MOD(25,3),-1) is invalid.
B. ROUND(MOD(25,3),-1) is invalid.
C. ROUND(MOD(25,3),-1) and TRUNC(MOD(25,3),-1) are both valid and give the same result.
D. ROUND(MOD(25,3),-1) and TRUNC(MOD(25,3),-1) are both valid but give different results.
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
QUESTION 18:
Which two are true about transactions in the Oracle Database? (Choose two.)
A. DML statements always start new transactions.
B. DDL statements automatically commit only data dictionary updates caused by executing the DDL.
C. A session can see uncommitted updates made by the same user in a different session.
D. A DDL statement issued by a session with an uncommitted transaction automatically commits that transaction.
E. An uncommitted transaction is automatically committed when the user exits SQL*Plus.
Correct Answer: CD Section: (none) Explanation
Explanation/Reference:
QUESTION 19:
Examine the description of the MEMBERS table:
Examine the partial query:
SELECT city, last_name AS 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? (Choose two.)
A. ORDER BY 1, 2
B. ORDER BY 1, lname DESC
C. WHERE city IN (‘%AN%’)
D. WHERE city = ‘%AN%’
E. WHERE city LIKE ‘%AN%’
F. ORDER BY last_name DESC, city ASC
Correct Answer: BE Section: (none) Explanation
Explanation/Reference:
QUESTION 20:
Examine this partial command:
Which two clauses are required for this command to execute successfully? (Choose two.)
A. the access driver TYPE clause
B. the DEFAULT DIRECTORY clause
C. the REJECT LIMIT clause
D. the LOCATION clause
E. the ACCESS PARAMETERS clause
Correct Answer: BD Section: (none) Explanation
Explanation/Reference:
QUESTION 21:
Which two are true about unused columns? (Choose two.)
A. A query can return data from unused columns, but no DML is possible on those columns.
B. Unused columns retain their data until they are dropped.
C. Once a column has been set to unused, a new column with the same name can be added to the table.
D. The DESCRIBE command displays unused columns.
E. A primary key column cannot be set to unused.
F. A foreign key column cannot be set to unused.
Correct Answer: BE Section: (none) Explanation
Explanation/Reference:
Reference: https://decipherinfosys.wordpress.com/2007/11/15/back-to-the-basics-dropping-unused- columns-in-oracle/
QUESTION 22:
Which two are true about the precedence of operators and conditions? (Choose two.)
A. || has a higher order of precedence than + (addition).
B. + (addition) has a higher order of precedence than * (multiplication).
C. NOT has a higher order of precedence than AND and OR in a condition.
D. AND and OR have the same order of precedence in a condition.
E. Operators are evaluated before conditions.
Correct Answer: DE Section: (none) Explanation
Explanation/Reference:
QUESTION 23:
In your session, the NLS_DATE_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 ‘4’ DAY
– INTERVAL ‘120’ 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’ MONTH + INTERVAL ‘5’ DAY
– INTERVAL ‘120’ SECOND, ‘DD-MON-YYYY’) AS “date” FROM DUAL;
D. SELECT TO_CHAR(TO_DATE(’29-10-2019′) + INTERVAL ‘2’ MONTH + INTERVAL ‘5’ DAY
– INTERVAL ‘86410’ SECOND, ‘DD-MON-YYYY’) AS “date” FROM DUAL;
E. SELECT TO_CHAR(TO_DATE(’29-10-2019′) + INTERVAL ‘2’ MONTH + INTERVAL ‘6’ DAY
– INTERVAL ‘120’ SECOND, ‘DD-MON-YYYY’) AS “date” FROM DUAL;
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 24:
Examine the data in the INVOICES table:
Examine the data in the CURRENCIES table:
Which query returns the currencies in CURRENCIES that are not present in INVOICES?
A.
B.
C.
D.
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
QUESTION 25:
The SALES table has columns PROD_ID and QUANTITY_SOLD of data type NUMBER. Which two queries execute successfully? (Choose two.)
A. SELECT prod_id FROM sales WHERE quantity_sold > 55000 AND COUNT(*) > 10 GROUP BY COUNT(*) > 10;
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 prod_id HAVING COUNT(*) > 10;
E. SELECT COUNT(prod_id) FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id;
Correct Answer: BE Section: (none) Explanation
Explanation/Reference:
QUESTION 26:
Which three statements are true about single-row functions? (Choose three.)
A. They return a single result row per table.
B. They can be nested to any level.
C. They can accept only one argument.
D. The argument can be a column name, variable, literal or an expression.
E. They can be used only in the WHERE clause of a SELECT statement.
F. The data type returned can be different from the data type of the argument.
Correct Answer: BDF Section: (none) Explanation
Explanation/Reference:
QUESTION 27:
Which two statements are true about *_TABLES views? (Choose two.)
A. USER_TABLES displays all tables owned by the current user.
B. You must have ANY TABLE system privileges, or be granted object privileges on the table, to view a table in USER_TABLES.
C. All users can query DBA_TABLES successfully.
D. You must have ANY TABLE system privileges, or be granted object privileges on the table, to view a table in DBA_TABLES.
E. ALL_TABLES displays all tables owned by the current user.
F. You must have ANY TABLE system privileges, or be granted object privileges on the table, to view a table in ALL_TABLES.
Correct Answer: CD Section: (none) Explanation
Explanation/Reference:
QUESTION 28:
Which two statements are true about conditional INSERT ALL? (Choose two.)
A. Each row returned by the subquery can be inserted into only a single target table.
B. A single WHEN condition can be used for multiple INTO clauses.
C. Each WHEN condition is tested for each row returned by the subquery.
D. It cannot have an ELSE clause.
E. The total number of rows inserted is always equal to the number of rows returned by the subquery.
Correct Answer: BC
Section: (none)
Explanation Explanation/Reference:
QUESTION 29:
Which two statements are true about the COUNT function? (Choose two.)
A. COUNT(*) returns the number of rows in a table including duplicate rows and rows containing NULLs in any column.
B. It can only be used for NUMBER data types.
C. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and
NULLs in the INV_AMT column.
D. COUNT(inv_amt) returns the number of rows in a table including rows with NULL in the INV_AMT
column
E. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.
Correct Answer: AC Section: (none) Explanation
Explanation/Reference:
QUESTION 30:
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, ADD_MONTHS(hire_date, 6), NEXT_DAY(‘MONDAY’) FROM employees;
B. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) 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), ‘MONDAY’) FROM employees;
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
QUESTION 31:
Which three statements are true about GLOBAL TEMPORARY TABLES? (Choose three.)
A. GLOBAL TEMPORARY TABLE space allocation occurs at session start.
B. GLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose user has been granted select on the table.
C. A TRUNCATE command issued in a session causes all rows in a GLOBAL TEMPORARY TABLE for the issuing session to be deleted.
D. Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted.
E. A DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back.
F. A GLOBAL TEMPORARY TABLE’S definition is available to multiple sessions.
Correct Answer: CDF Section: (none) Explanation
Explanation/Reference:
QUESTION 32:
Which two statements are true about the SET VERIFY ON command? (Choose two.)
A. It displays values for variables used only in the WHERE clause of a query.
B. It displays values for variables created by the DEFINE command.
C. It can be used only in SQL*Plus.
D. It displays values for variables prefixed with &&.
E. It can be used in SQL Developer and SQL*Plus.
Correct Answer: BE Section: (none) Explanation
Explanation/Reference:
QUESTION 33:
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? (Choose two.)
A. CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CYCLE;
B. CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CACHE;
C. CREATE SEQUENCE emp_seq;
D. CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 NOCACHE;
E. CREATE SEQUENCE emp_seq NOCACHE;
F. CREATE SEQUENCE emp_seq START WITH 1 CACHE;
Correct Answer: BD Section: (none) Explanation
Explanation/Reference:
QUESTION 34:
Which three queries execute successfully? (Choose three.)
A. SELECT 1 – SYSDATE – DATE ‘2019-01-01’ FROM DUAL;
B. SELECT SYSDATE – DATE ‘2019-01-01’ – 1 FROM DUAL;
C. SELECT SYSDATE / DATE ‘2019-01-01’ – 1 FROM DUAL;
D. SELECT SYSDATE – 1 – DATE ‘2019-01-01’ FROM DUAL;
E. SELECT (SYSDATE – DATE ‘2019-01-01’) / 1 FROM DUAL;
F. SELECT 1 / SYSDATE – DATE ‘2019-01-01’ FROM DUAL;
Correct Answer: BDE Section: (none) Explanation
Explanation/Reference:
QUESTION 35:
Which two are true about granting object privileges on tables, views, and sequences? (Choose two.)
A. INSERT can be granted only on tables and sequences.
B. DELETE can be granted on tables, views, and sequences.
C. SELECT can be granted on tables, views, and sequences.
D. ALTER can be granted only on tables and sequences.
E. REFERENCES can be granted only on tables.
Correct Answer: CD Section: (none) Explanation
Explanation/Reference:
QUESTION 36:
Examine the description of the BOOKS table:
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;
Which two statements are true? (Choose two.)
A. The second ROLLBACK command replays the delete.
B. The first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row.
C. The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed.
D. The second ROLLBACK command undoes the insert.
E. The second ROLLBACK command does nothing.
Correct Answer: CD Section: (none) Explanation
Explanation/Reference:
QUESTION 37:
Which two statements are true about an Oracle database? (Choose two.)
A. A table can have multiple primary keys.
B. A column definition can specify multiple data types.
C. A table can have multiple foreign keys.
D. A VARCHAR2 column without data has a NULL value.
E. A NUMBER column without data has a zero value.
Correct Answer: CD Section: (none) Explanation
Explanation/Reference:
QUESTION 38:
Examine the data in the EMP table:
You execute this query:
Why does an error occur?
A. An alias name must not contain space characters.
B. An alias name must always be specified in quotes.
C. An alias name must not be used in an ORDER BY clause.
D. An alias name must not be used in a GROUP BY clause.
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
QUESTION 39:
Which two actions can you perform with object privileges? (Choose two.)
A. Create roles.
B. Create FOREIGN KEY constraints that reference tables in other schemas.
C. Delete rows from tables in any schema except SYS.
D. Set default and temporary tablespaces for a user.
E. Execute a procedure or function in another schema.
Correct Answer: AE Section: (none) Explanation
Explanation/Reference:
Reference: http://www.cruzroja.es/help/wvtdbobp.htm
QUESTION 40:
No user-defined locks are used in your database.
Which three are true about Transaction Control Language (TCL)? (Choose three.)
A. ROLLBACK without the TO SAVEPOINT clause undoes all the transaction’s changes, releases its locks, and erases all its savepoints.
B. ROLLBACK without the TO SAVEPOINT clause undoes all the transaction’s changes but does not release its locks.
C. ROLLBACK without the TO SAVEPOINT clause undoes all the transaction’s changes but does not erase its savepoints.
D. ROLLBACK TO SAVEPOINT undoes the transaction’s changes made since the named savepoint and then ends the transaction.
E. COMMIT ends the transaction and makes all its changes permanent.
F. COMMIT erases all the transaction’s savepoints and releases its locks.
Correct Answer: AEF Section: (none) Explanation
Explanation/Reference:
QUESTION 41:
Examine the description of the EMPLOYEES table:
Which two queries return rows for employees whose manager works in a different department? (Choose two.)
A.
B.
C.
D.
E.
Correct Answer: BE Section: (none) Explanation
Explanation/Reference:
QUESTION 42:
Which three are true about dropping columns from a table? (Choose three.)
A. A column must be set as unused before it is dropped from a table.
B. A primary key column cannot be dropped.
C. Multiple columns can be dropped simultaneously using the ALTER TABLE command.
D. A column can be removed only if it contains no data.
E. A column that is referenced by another column in any other table cannot be dropped.
F. A column drop is implicitly committed.
Correct Answer: BDE Section: (none) Explanation
Explanation/Reference:
QUESTION 43:
Which three statements are true about views in an Oracle Database? (Choose three.)
A. A SELECT statement cannot contain a WHERE clause when querying a view containing a WHERE clause in its defining query.
B. Views have no segment.
C. Views have no object number.
D. Views can join tables only if they belong to the same schema.
E. A view can be created that refers to a non-existent table in its defining query.
F. Rows inserted into a table using a view are retained in the table if the view is dropped.
Correct Answer: BEF Section: (none) Explanation
Explanation/Reference:
QUESTION 44:
You start a session and execute these commands successfully:
Which two are true? (Choose two.)
A. To drop the table in this session, you must first truncate it.
B. Other sessions can view the committed row.
C. You can add a column to the table in this session.
D. You can add a foreign key to the table.
E. When you terminate your session, the row will be deleted.
Correct Answer: CE Section: (none) Explanation
Explanation/Reference:
QUESTION 45:
Examine this statement:
Which two statements are true? (Choose two.)
A. The names of employees earning the maximum salary will appear first in an unspecified order.
B. All remaining employee names will appear in descending order.
C. All remaining employee names will appear in an unspecified order.
D. All remaining employee names will appear in ascending order.
E. The names of employees earning the maximum salary will appear first in ascending order.
F. The names of employees earning the maximum salary will appear first in descending order.
Correct Answer: DF Section: (none) Explanation
Explanation/Reference:
QUESTION 46:
Which two are true about external tables that use the ORACLE_DATAPUMP access driver? (Choose two.)
A. When creating an external table, data can be selected only from a table whose rows are stored in database blocks.
B. Creating an external table creates a directory object.
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.
Correct Answer: CD Section: (none) Explanation
Explanation/Reference:
QUESTION 47:
Examine the description of the EMPLOYEES table:
Which statement will fail?
A.
B.
C.
D.
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 48:
Examine the data in the NEW_EMPLOYEES table:
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 details from the NEW_EMPLOYEES table to the EMPLOYEES table.
Which statement will do this?
A.
B.
C.
D.
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
QUESTION 49:
Examine the description of the EMPLOYEES table:
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?
Correct Answer: C Section: (none) Explanation
Explanation/Reference:
QUESTION 50:
Examine the description of the PRODUCT_DETAILS table:
Which two statements are true? (Choose two.)
A. EXPIRY_DATE contains the SYSDATE by default if no date is assigned to it.
B. PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it.
C. PRODUCT_NAME cannot contain duplicate values.
D. EXPIRY_DATE cannot be used in arithmetic expressions.
E. PRODUCT_PRICE contains the value zero by default if no value is assigned to it.
F. PRODUCT_ID can be assigned the PRIMARY KEY constraint.
Correct Answer: BF Section: (none) Explanation
Explanation/Reference:
QUESTION 51:
Examine the description of the EMPLOYEES table:
Which two queries will result in an error? (Choose two.) A.
Correct Answer: AE Section: (none) Explanation
Explanation/Reference:
QUESTION 52:
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 ‘123’;
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
QUESTION 53:
Which two statements are true regarding indexes? (Choose two.)
A. An update to a table can result in updates to any or all of the table’s indexes.
B. An update to a table can result in no updates to any of the table’s indexes.
C. A UNIQUE index can be altered to be non-unique.
D. When a table is dropped and is moved to the RECYCLE BIN, all indexes built on that table are permanently dropped.
E. A table belonging to one user cannot have an index that belongs to a different user.
Correct Answer: BE Section: (none) Explanation
Explanation/Reference:
QUESTION 54:
Which two are true about queries using set operators (UNION, UNION ALL, INTERSECT and MINUS)? (Choose two.)
A. The name of each column in the first SELECT list must match the name of the corresponding column in each subsequent SELECT list.
B. None of the set operators can be used when selecting CLOB columns.
C. There must be an equal number of columns in each SELECT list.
D. Each SELECT statement in the query can have an ORDER BY clause.
E. The FOR UPDATE clause cannot be specified.
Correct Answer: BE Section: (none) Explanation
Explanation/Reference:
Reference: https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm
QUESTION 55:
BOOK_SEQ is an existing sequence in your schema.
Which two CREATE TABLE commands are valid? (Choose two.)
Correct Answer: BD Section: (none) Explanation
Explanation/Reference:
QUESTION 56:
Which three statements are true about multiple row subqueries? (Choose three.)
A. Two or more values are always returned from the subquery.
B. They can contain HAVING clauses.
C. They can contain GROUP BY clauses.
D. They can return multiple columns.
E. They cannot contain a subquery.
Correct Answer: BCD Section: (none) Explanation
Explanation/Reference:
QUESTION 57:
Which three actions can you perform on an existing table containing data? (Choose three.)
A. Increase the width of a numeric column.
B. Add a new column as the table’s first column.
C. Define a default value that is automatically inserted into a column containing nulls.
D. Change a DATE column containing data to a NUMBER data type.
E. Change the default value of a column.
F. Add a new NOT NULL column with a DEFAULT value.
Correct Answer: AEF Section: (none) Explanation
Explanation/Reference:
QUESTION 58:
Which two statements are true about selecting related rows from two tables based on an Entity Relationship Diagram (ERD)? (Choose two.)
A. Rows from unrelated tables cannot be joined.
B. Relating data from a table with data from the same table is implemented with a self join.
C. Implementing a relationship between two tables might require joining additional tables.
D. Every relationship between the two tables must be implemented in a join condition.
E. An inner join relates rows within the same table.
Correct Answer: BC Section: (none) Explanation
Explanation/Reference:
QUESTION 59:
Which three statements about roles are true? (Choose three.)
A. Roles are assigned to users using the ALTER USER statement.
B. Privileges are assigned to a role using the GRANT statement.
C. A role is a named group of related privileges that can only be assigned to a user.
D. A single user can be assigned multiple roles.
E. Privileges are assigned to a role using the ALTER ROLE statement.
F. Roles are assigned to roles using the ALTER ROLE statement.
G. A single role can be assigned to multiple users.
Correct Answer: BDG Section: (none) Explanation
Explanation/Reference:
QUESTION 60:
The INVOICE table has a QTY_SOLD column of data type NUMBER and an INVOICE_DATE column of data type DATE.
NLS_DATE_FORMAT is set to DD-MON-RR.
Which two are true about data type conversions involving these columns in query expressions? (Choose two.)
A. invoice_date = ’15-march-2019′ : uses implicit conversion
B. qty_sold BETWEEN ‘101’ AND ‘110’ : uses implicit conversion
C. invoice_date > ’01-02-2019′ : uses implicit conversion
D. qty_sold = ‘0554982’ : requires explicit conversion
E. CONCAT (qty_sold, invoice_date) : requires explicit conversion
Correct Answer: BC Section: (none) Explanation
Explanation/Reference:
QUESTION 61:
Which three statements are true about inner and outer joins? (Choose three.)
A. A full outer join returns matched and unmatched rows.
B. Outer joins can be used when there are multiple join conditions on two tables.
C. A full outer join must use Oracle syntax.
D. Outer joins can only be used between two tables per query.
E. A left or right outer join returns only unmatched rows.
F. An inner join returns matched rows.
Correct Answer: ABF Section: (none) Explanation
Explanation/Reference:
QUESTION 62:
Which statement will execute successfully?
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
QUESTION 63:
Examine the description of the EMPLOYEES table:
Which two queries return all rows for employees whose salary is greater than the average salary in their department? (Choose two.)
Correct Answer: AD Section: (none) Explanation
Explanation/Reference:
QUESTION 64:
Which three statements are true about the Oracle join and ANSI join syntax? (Choose three.)
A. The Oracle join syntax supports creation of a Cartesian product of two tables.
B. The Oracle join syntax only supports right outer joins.
C. The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables.
D. The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax.
E. The Oracle join syntax supports natural joins.
F. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
G. The SQL:1999 compliant ANSI join syntax supports natural joins.
Correct Answer: ACG Section: (none) Explanation
Explanation/Reference:
QUESTION 65:
Which two are true about the NVL, NVL2, and COALESCE functions? (Choose two.)
A. NVL must have expressions of the same data type.
B. NVL can have any number of expressions in the list.
C. NVL2 can have any number of expressions in the list.
D. COALESCE stops evaluating the list of expressions when it finds the first non-null value.
E. The first expression in NVL2 is never returned.
F. COALESCE stops evaluating the list of expressions when it finds the first null value.
Correct Answer: DE Section: (none) Explanation
Explanation/Reference:
Reference: https://www.interviewsansar.com/difference-between-nvl-nvl2-nullif-and-coalesce-functions/
QUESTION 66:
Examine this statement:
What is returned upon execution?
A. an error
B. 2 rows
C. 0 rows
D. 1 row
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
QUESTION 67:
Examine this statement:
What is returned upon execution?
A. an error
B. 2 rows
C. 0 rows
D. 1 row
Correct Answer: D Section: (none) Explanation
Explanation/Reference:
QUESTION 68:
Which two statements execute successfully? (Choose two.)
Correct Answer: AB Section: (none) Explanation
Explanation/Reference:
QUESTION 69:
An Oracle Database session has an uncommitted transaction in progress which updated 5000 rows in a table.
In which three situations does the transaction complete thereby committing the updates? (Choose three.)
A. when a CREATE TABLE AS SELECT statement is issued in the same session but fails with a syntax error
B. when a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user then issues a
COMMIT
C. when the session logs out successfully
D. when a CREATE INDEX statement is executed successfully in the same session
E. when a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a
COMMIT
F. when a COMMIT statement is issued by the same user from another session in the same database instance
Correct Answer: ACD Section: (none) Explanation
Explanation/Reference:
QUESTION 70:
Which two are true about using constraints? (Choose two.)
A. NOT NULL can be specified at the column and at the table level.
B. A table can have only one PRIMARY KEY and one FOREIGN KEY constraint.
C. A FOREIGN KEY column in a child table and the referenced PRIMARY KEY column in the parent table must have the same names.
D. PRIMARY KEY and FOREIGN KEY constraints can be specified at the column and at the table level.
E. A table can have multiple PRIMARY KEY and multiple FOREIGN KEY constraints.
F. A table can have only one PRIMARY KEY but may have multiple FOREIGN KEY constraints.
Correct Answer: DF Section: (none) Explanation
Explanation/Reference:
QUESTION 71:
Examine this statement:
On which two columns of the table will an index be created automatically? (Choose two.)
A. ORDER_ID
B. ORDER_TOTAL
C. ORDER_DATE
D. PRODUCT_ID
E. STATUS
F. SERIAL_NO
Correct Answer: AE
Section: (none)
Explanation Explanation/Reference:
QUESTION 72:
Examine this partial query:
Examine this output:
Which GROUP BY clause must be added so the query returns the results shown?
A. GROUP BY ch.channel_type, ROLLUP(t.month, co.country_code);
B. GROUP BY ch.channel_type, t.month, ROLLUP(co.country_code);
C. GROUP BY CUBE(ch.channel_type, t.month, co.country_code);
D. GROUP BY ch.channel_type, t.month, co.country_code;
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
QUESTION 73:
Examine the description of the EMPLOYEES table:
Which statement will execute successfully, returning distinct employees with non-null first names?
A. SELECT first_name, DISTINCT last_name FROM employees WHERE first_name <> NULL;
B. SELECT first_name, DISTINCT last_name FROM employees WHERE first_name IS NOT NULL;
C. SELECT DISTINCT * FROM employees WHERE first_name IS NOT NULL;
D. SELECT DISTINCT * FROM employees WHERE first_name <> NULL;
Correct Answer: A Section: (none) Explanation
Explanation/Reference:
QUESTION 74:
Examine the description of the BRICKS table:
Examine the description of the BRICKS_STAGE table:
Which two queries execute successfully? (Choose two.)
A.
B.
C.
D.
E.
Correct Answer: BE Section: (none) Explanation
Explanation/Reference:
QUESTION 75:
Table EMPLOYEES contains columns including EMPLOYEE_ID, JOB_ID and SALARY. Only the EMPLOYEE_ID column is indexed.
Rows exist for employees 100 and 200.
Examine this statement:
Which two statements are true? (Choose two.)
A. Employees 100 and 200 will have the same SALARY as before the update command.
B. Employee 100 will have JOB_ID set to the same value as the JOB_ID of employee 200.
C. Employee 200 will have JOB_ID set to the same value as the JOB_ID of employee 100.
D. Employees 100 and 200 will have the same JOB_ID as before the update command.
E. Employee 100 will have SALARY set to the same value as the SALARY of employee 200.
F. Employee 200 will have SALARY set to the same value as the SALARY of employee 100.
Correct Answer: BE Section: (none) Explanation
Explanation/Reference:
QUESTION 76:
Examine these two queries and their output:
SELECT deptno, dname FROM dept;
SELECT emame, job, deptno FROM emp ORDER BY deptno;
Now examine this query:
How many rows will be displayed?
A. 64
B. 6
C. 3
D. 12
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
QUESTION 77:
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. SYSDATE
B. CURRENT_TIMESTAMP
C. LOCALTIMESTAMP
D. CURRENT_DATE
Correct Answer: B Section: (none) Explanation
Explanation/Reference:
Reference: https://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#NLSPG004
QUESTION 78:
You have been tasked 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. INTERVAL YEAR TO MONTH
B. TIMESTAMP WITH TIMEZONE
C. INTERVAL DAY TO SECOND
D. TIMESTAMP WITH LOCAL TIMEZONE
E. TIMESTAMP
Correct Answer: C Section: (none) Explanation
Explanation/Reference: