Oracle 1z0-071 Exam Dumps

Which three statements are true about sequences in a single instance Oracle database? (Choose three.)

A. A sequence can issue duplicate values
B. A sequence’s unallocated cached value are lost if the instance shuts down
C. Sequences can always have gaps
D. Two or more tables cannot have keys generated from the same sequence
E. A sequence can only be dropped by a DBA
F. A sequence number that was allocated can be rolled back if a transaction fails

Answer: B,E,F

The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2.The table has two rows whose CUST_LAST_NAME values are Anderson and Ausson.Which query produces output for CUST_LAST_NAME containing Oder for the first row and Aus for the second?

A. SELECT REPLACE (TRIM(TRAILING ‘son’ FROM cust_last_name), ‘An’, ‘o’) FROM customers;
B. SELECT INITCAP (REPLACE(TRIM(‘son’ FROM cust_last_name), ‘An’, ‘o’)) FROM customers;
C. SELECT REPLACE (SUBSTR(cust_last_name, -3), ‘An’, ‘o’) FROM customers;
D. SELECT REPLACE (REPLACE(cust_last_name, ‘son’, ‘’), ‘An’, ‘o’) FROM customers;

Answer: D

Which three actions can you perform by using the ALTER TABLE command?

A. Lock a set of rows in a table.
B. Drop pseudocolumns from a table.
C. Rename a table.
D. Drop all columns simultaneously from a table.
E. Enable or disable constraints on a table.
F. Restrict all DML statements on a table.

Answer: C,D,E

Which two statements are true regarding savepoints? (Choose two.)

A. Savepoints may be used to ROLLBACK.
B. Savepoints can be used for only DML statements.
C. Savepoints are effective only for COMMIT.
D. Savepoints are effective for both COMMIT and ROLLBACK.
E. Savepoints can be used for both DML and DDL statements.

Answer: A,B

Which three statements are true about inner and outer joins?

A. Outer joins can be used when there are multiple join conditions on two tables.
B. Outer joins can only be used between two tables per query.
C. A full outer join returns matched and unmatched rows.
D. An inner join returns matched rows.
E. A full outer join must use Oracle syntax.
F. A left or right outer join returns only unmatched rows.

Answer: B,C,D

Which three statements are true about performing Data Manipulation Language (DML) operations on a view with no INSTEAD OF triggers defined? (Choose three.)

A. Insert statements can always be done on a table through a view.
B. 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.
C. 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.
D. Delete statements can always be done on a table through a view.
E. The WITH CHECK clause has no effect when deleting rows from the underlying table through the view.
F. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword.

Answer: A,C,D

Which three statements are true about dropping and unused columns in an Oracle database?

A. Partition key columns cannot be dropped.
B. A column that is set to unused still counts towards the limit of 1000 columns per table.
C. An unused column's space is reclaimed automatically when the row containing that column is next queried.
D. A drop column command can be rolled back.
E. A primary key column referenced by another column as a foreign key can be dropped if using the cascade option.
F. An unused column's space is reclaimed automatically when the block containing that column is next queried.

Answer: A,B,F

Which three statements are true about GLOBAL TEMPORARY TABLES?

A. A GLOBAL TEMPORARY TABLE can have multiple indexes.
B. A GLOBAL TEMPORARY TABLE cannot have a PUBLIC SYNONYM.
C. A trigger can be created on a GLOBAL TEMPORARY TABLE.
D. A GLOBAL TEMPORARY TABLE can be referenced in the defining query of a view.
E. A GLOBAL TEMPORARY TABLE can have only one index..
F. Data Manipulation Language (DML) on GLOBAL TEMPORARY TABLES generates no REDO

Answer: C,D,F

Which two statements are true about the SET VERIFY ON command? (Choose two.)

A. It can be used in SQL Developer and SQL*Plus
B. It displays values for variables used only in the WHERE clause of a query
C. It can be used only in SQL*Plus
D. It displays values for variables prefixed with &&
E. It displays values for variables created by the DEFINE command

Answer: C,E

Which three actions can you perform on an existing table containing data? (Choose three.)

A. Add a new NOT NULL column with a DEFAULT value
B. Change the default value of a column
C. Change a DATE column containing data to a NUMBER data type
D. Add a new column as the table’s first column
E. Define a default value that is automatically inserted into a column containing nulls
F. Increase the width of a numeric column

Answer: B,D

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 TO_CHAR(SYSDATE, ‘DD-MON-YYYY’) – ’01-JAN-2019’ FROM DUAL;
B. SELECT SYSDATE – TO_DATE(’01-JANUARY-2019’) FROM DUAL;
C. SELECT ROUND(SYSDATE – ’01-JAN-2019’) FROM DUAL;
D. SELECT ROUND(SYSDATE – TO_DATE(‘01/JANUARY/2019’)) FROM DUAL;
E. SELECT TO_DATE(SYSDATE, ‘DD/MONTH/YYYY’) – ‘01/JANUARY/2019’ FROM DUAL;

Answer: A

Which two statements are true about substitution variables? (Choose two.)

A. A substitution variable can be used with any clause in a SELECT statement
B. A substitution variable used to prompt for a column name must be enclosed in a single 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 only in a SELECT statement
E. A substitution variable used to prompt for a column name must be enclosed in double quotation marks
F. A substitution variable prefixed with && prompts only once for a value in a session unless it is set to undefined in the session

Answer: B,C

Which three statements are true regarding single row subqueries?

A. They must return a row to prevent errors in the SQL statement.
B. They must be placed on the left side of the comparison operator or condition.
C. They can be used in the where clause.
D. A SQL statement may have multiple single row subquery blocks.
E. They must be placed on the right side of the comparison operator or condition.
F. They can be used in the having clause.

Answer: C,D,F

Which two statements are true about the rules of precedence for operators? (Choose two.)

A. The concatenation operator | | is always evaluated before addition and subtraction in an expression
B. Multiple parentheses can be used to override the default precedence of operators in an expression
C. Arithmetic operators with equal precedence area evaluated from left to right within an expression
D. NULLS influence the precedence of operators in an expression
E. The + binary operator has the highest precedence in an expression in a SQL statement

Answer: B,E

Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data? (Choose four.)

A. 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
B. Only the primary key can be defined at the column and table level
C. The foreign key columns and parent table primary key columns must have the same names
D. A table can have only one primary key and one foreign key
E. A table can have only one primary key but multiple foreign keys
F. Primary key and foreign key constraints can be defined at both the column and table level
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,E,F,G

Which three privileges can be restricted to a subset of columns in a table? (Choose three.)

A. ALTER
B. DELETE
C. UPDATE
D. SELECT
E. INDEX
F. REFERENCES
G. INSERT

Answer: B,C,D

Which three statements are true about the DESCRIBE command? (Choose three.)

A. It can be used to display the structure of an existing view
B. It can be used only from SQL*Plus
C. It displays the PRIMARY KEY constraint for any column or columns that have that constraint
D. It can be used from SQL Developer
E. It displays all constraints that are defined for each column
F. It displays the NOT NULL constraint for any columns that have that constraint

Answer: A,B,F

Which two statements are true about single-row functions? (Choose two.)

A. CEIL: can be used for positive and negative numbers
B. FLOOR: returns the smallest integer greater than or equal to a specified number
C. TRUNC: can be used with NUMBER and DATE values
D. CONCAT: can be used to combine any number of values
E. MOD: returns the quotient of a division operation

Answer: C,E

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. CONCAT(qty_sold, invoice_date) : requires explicit conversion
B. invoice_date > ’01-02-2019’ : uses implicit conversion
C. invoice_date = ’15-march-2019’ : uses implicit conversion
D. qty_sold BETWEEN ‘101’ AND ‘110’ : uses implicit conversion
E. qty_sold = ‘0554982’ : requires explicit conversion

Answer: C,D

Which three are true about system and object privileges? (Choose three.)

A. WITH GRANT OPTION can be used when granting an object privilege to both users and roles
B. Adding a primary key constraint to an existing table in another schema requires a system privilege
C. Adding a foreign key constraint pointing to a table in another schema requires the REFERENCES object privilege
D. Revoking a system privilege that was granted with WITH ADMIN OPTION has a cascading effect
E. Revoking an object privilege that was granted with the WITH GRANT OPTION clause has a cascading effect.
F. WITH GRANT OPTION cannot be used when granting an object privilege to PUBLIC

Answer: A,C,E

An Oracle database server session has an uncommitted transaction in progress which updated 5000 rows in a table.In which three situations does the transactions complete thereby committing the updates? (Choose three.)

A. when a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user then issues a COMMIT
B. when a CREATE INDEX statement is executed successfully in the same session
C. when a COMMIT statement is issued by the same user from another session in the same database instance
D. when the session logs out successfully
E. when a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a COMMIT
F. when a CREATE TABLE AS SELECT statement is executed unsuccessfully in the same session

Answer: B,D,E

Which three actions can you perform by using the ORACLE_DATAPUMP access driver? (Choose three.)

A. Read data from an external table and load it into a table in the database
B. Create a directory object for an external table
C. Execute DML statements on an external table
D. Query data from an external table
E. Read data from a table in the database and insert it into an external table
F. Create a directory object for a flat file

Answer: B,D,E