Oracle 1z0-071 Exam Dumps

Oracle Database 12c SQL

Oracle 1z0-071 Sample Questions

Question # 1

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

A. They can be nested to any level
B. The data type returned can be different from the data type of the argument
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. They return a single result row per table

Show Answer

Question # 2

The ORDERS table a primary key constrain 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 CASCADS.There are rows in the ORDERS table with an ORDER_TOTAL of less than 1000.Which three DELETE statements execute successfully?

A. DELETE * FROM orders WHERE order_total < 1000;
B. DELETE FROM orders;
C. DELETE order_id FROM orders WHERE order_total < 1000;
D. DELETE orders WHERE order_total < 1000;
E. DELETE FROM orders WHERE order_total < 1000;

Show Answer

Question # 3

Which two statements are true about views?

A. Views can be updated without the need to re-grant privileges on the view.
B. Views can be indexed.
C. The with check clause prevents certain rows from being displayed when querying the view.
D. The with check clause prevents certain rows from being updated or inserted in the underlying table through the view.
E. Tables in the defining query of a view must always exist in order to create the view.

Show Answer

Question # 4

Which three are true about the CREATE TABLE command? (Choose three.)

A. It can include the CREATE..INDEX statement for creating an index to enforce the primary key constraint
B. It implicitly executes a commit
C. A user must have the CREATE ANY TABLE privilege to create tables
D. It implicitly rolls back any pending transactions
E. The owner of the table should have space quota available on the tablespace where the table is defined
F. The owner of the table must have the UNLIMITED TABLESPACE system privilege

Show Answer

Question # 5

Which two statements are true about selecting related rows from two tables based on an Entity Relationship Diagram (ERD)? (Choose two.)

A. Implementing a relationship between two tables might require joining additional tables
B. Relating data from a table with data from the same table is implemented with a self join
C. Rows from unrelated tables cannot be joined
D. Every relationship between the two tables must be implemented in a join condition
E. An inner join relates rows within the same table

Show Answer

Question # 6

Which two statements are true about external tables?

A. Their data can be retrieved by using only SQL or PL/SQL.
B. Their metadata and actual data are both stoned outside the database.
C. Indexes can be created on them.
D. You can populate them from existing data in the database by using the CREATE TABLE AS SELECT command.
E. DML statements cannot be used on them.

Show Answer

Question # 7

Which three statements are true regarding indexes? (Choose three.)

A. A SELECT statement can access one or more indices without accessing any tables
B. An update to a table can result in no updates to any of the table’s indexes
C. A table belonging to one user can have an index that belongs to a different user
D. A UNIQUE index can be altered to be non-unique
E. An update to a table can result in updates to any or all of the table’s indexes
F. When a table is dropped and is moved to the RECYCLE BIN, all indexes built on that table are permanently dropped

Show Answer

Question # 8

Examine this query:SELECT TRUNC(ROUND(156.00, -2), -1) FROM DUAL;What is the result?

A. 16
B. 200
C. 100
D. 160
E. 150

Show Answer

Question # 9

Which two statements are true regarding the UNION and UNION ALL operators? (Choose two.)

A. The output is sorted by the UNION ALL operator
B. The names of columns selected in each SELECT statement must be identical
C. The number of columns selected in each SELECT statement must be identical
D. Duplicates are eliminated automatically by the UNION ALL operator
E. NULLS are not ignored during duplicate checking

Show Answer

Question # 10

Which two statements are true regarding a SAVEPOINT? (Choose two.)

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

Show Answer

Question # 11

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

Show Answer

Question # 12

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;

Show Answer

Question # 13

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.

Show Answer

Question # 14

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.

Show Answer

Question # 15

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.

Show Answer

Question # 16

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.

Show Answer

Question # 17

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.

Show Answer

Question # 18

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

Show Answer

Question # 19

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

Show Answer

Question # 20

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

Show Answer

Question # 21

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;

Show Answer

Question # 22

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

Show Answer