Oracle 1z0-071 Exam Dumps

Oracle Database 12c SQL

Recent 1z0-071 Exam Results

Our Oracle 1z0-071 dumps are key to get success. More than 80000+ success stories.

40

Clients Passed Oracle 1z0-071 Exam Today

91%

Passing score in Real Oracle 1z0-071 Exam

91%

Questions were from our given 1z0-071 dumps

Oracle 1z0-071 Sample Questions

Question # 1

Which two statements are true regarding operators used with subqueries (Choose two.) 

A. The NOT IN operator is equivalent to IS NULL.
B. The <ANY operator means less than the maximum.
C. =ANY and =ALL operators have the same functionality.
D. The IN operator cannot be used in single-row subqueries. 
E. The NOT operator can be used with IN, ANY and ALL operators.

Answer: B,E

Show Answer

Question # 2

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 (-10) produces an error.
B. INSERT INTO rate_list VALUES (87654.556) inserts the value as 87654.6.
C. INSERT INTO rate_list VALUES (0.551) inserts the value as .55.
D. INSERT INTO rate_list VALUES (-99.99) inserts the value as 99.99.
E. INSERT INTO rate_list VALUES (0.999) produces an error.
F. INSERT INTO rate_list VALUES (-.9) inserts the value as -.9.

Answer: C,D

Show Answer

Question # 3

Which two statements are true regarding working with dates? (Choose two.) 

A. The RR date format automatically calculates the century from the SYSDATE function but allows the session user to enter the century.
B. The RR date format automatically calculates the century from the SYSDATE function and does not allow a session user to enter the century.
C. The default internal storage of dates is in character format.
D. The default internal storage of dates is in numeric format.

Answer: A,D

Show Answer

Question # 4

You issued this command: SQL > DROP TABLE employees; Which three statements are true? (Choose three.)

A. Sequences used in the EMPLOYEES table become invalid.
B. If there is an uncommitted transaction in the session, it is committed.
C. All indexes and constraints defined on the table being dropped are also dropped.
D. The space used by the EMPLOYEES table is always reclaimed immediately.
E. The EMPLOYEES table can be recovered using the ROLLBACK command.
F. The EMPLOYEES table may be moved to the recycle bin.

Answer: B,C,F

Show Answer

Question # 5

Which three statements are true about multiple row subqueries? 

A. They can contain GROUP BY clauses.
B. They can return multiple columns.
C. Two or more values are always returned from the subquery.
D. They can contain HAVING clauses.
E. They cannot contain a subquery.

Answer: A,B,C

Show Answer

Question # 6

Which three statements are true about indexes and their administration in an Oracle database? 

A. An index can be created as part of a CREATE TABLE statement.
B. A DROP INDEX statement always prevents updates to the table during the drop operation. 
C. A UNIQUE and non-unique index can be created on the same table column.
D. A descending index is a type of function-based index.
E. If a query filters on an indexed column then it will always be used during execution of the query.
F. An INVISIBLE index is not maintained when Data Manipulation Language (DML) is performed on its underlying table.

Answer: A,D,F

Show Answer

Question # 7

Which statement is true about an inner join specified in a query’s WHERE clause? 

A. It only applies for equijoin conditions.
B. It applies for equijoin and nonequijoin conditions.
C. It requires column names to be the same in all tables being joined.
D. It must have primary-key and foreign-key constraints defined on the join columns.

Answer: B

Show Answer

Question # 8

Which two statements are true about conditional INSERT ALL? 

A. Each WHEN condition is tested for each row returned by the subquery.
B. The total number of rows inserted is always equal to the number of rows returned by the subquery.
C. A single WHEN condition can be used for multiple INTO clauses.
D. It cannot have an ELSE clause.
E. Each row returned by the subquery can be inserted into only a single target table.

Answer: C,D

Show Answer

Question # 9

Which three statements are true about the ALTER TABLE....DROP COLUMN.... command? (Choose three.)  

A. A column can be dropped only if it does not contain any data.
B. A column can be dropped only if another column exists in the table.
C. A dropped column can be rolled back.
D. The column in a composite PRIMARY KEY with the CASCADE option can be dropped.
E. A parent key column in the table cannot be dropped.

Answer: B,D,E

Show Answer

Question # 10

Which two statements best describe the benefits of using the WITH clause? (Choose two.) 

A. It can improve the performance of a large query by storing the result of a query block having the WITH clause in the session's temporary tablespace.
B. It enables sessions to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.
C. It enables sessions to store a query block permanently in memory and use it to create complex queries.
D. It enables sessions to store the results of a query permanently.

Answer: A,B

Show Answer

Question # 11

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

A. Only two subqueries can be placed at one level.
B. A subquery in the WHERE clause of a SELECT statement can be nested up to three levels only.
C. A subquery can be used to access data from one or more tables or views.
D. The columns in a subquery must always be qualified with the name or alias of the table used.
E. If the subquery returns 0 rows, then the value returned by the subquery expression is NULL.

Answer: C,E

Show Answer

Question # 12

Examine the structure of the MEMBERS table: NameNull?Type ------------------ --------------- ------------------------------ MEMBER_IDNOT NULLVARCHAR2 (6) FIRST_NAMEVARCHAR2 (50) LAST_NAMENOT NULLVARCHAR2 (50) ADDRESSVARCHAR2 (50) You execute the SQL statement: SQL > SELECT member_id, ' ' , first_name, ' ' , last_name "ID FIRSTNAME LASTNAME " FROM members; What is the outcome?

A. It fails because the alias name specified after the column names is invalid.
B. It fails because the space specified in single quotation marks after the first two column names is invalid.
C. It executes successfully and displays the column details in a single column with only the alias column heading.
D. It executes successfully and displays the column details in three separate columns and replaces only the last column heading with the alias.

Answer: D

Show Answer

Question # 13

The SALES table has columns PROD_ID and QUANTITY_SOLD of data type NUMBER. Which two queries execute successfully?

A. SELECT prod_id FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id HAVING COUNT(*) >10;
B. SELECT prod_id FROM sales WHERE quantity_sold > 55000 AND COUNT(*) > 10 GROUP BY prod_id HAVING COUNT(*) >10;
C. SELECT COUNT (prod_id) FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id;
D. SELECT prod_id FROM sales WHERE quantity_sold > 55000 AND COUNT(*) > 10 GROUP BY COUNT(*) >10;
E. SELECT COUNT(prod_id) FROM sales GROUP BY prod_id WHERE quantity_sold > 55000;

Answer: A,C

Show Answer

Question # 14

You must display details of all users whose username contains the string 'ch_'. Which query generates the required output? (Choose the best answer.) 

A. SELECT *FROM usersWHERE user_name LIKE '%ch_';
B. SELECT *FROM usersWHERE user_name LIKE '%ch_%'ESCAPE'%';
C. SELECT *FROM usersWHERE user_name LIKE 'ch\_%' ESCAPE '_';
D. SELECT *FROM usersWHERE user_name LIKE '%ch\_%' ESCAPE '\';

Answer: B

Show Answer

Question # 15

Which two tasks can be performed by using Oracle SQL statements? (Choose two.) 

A. changing the password for an existing database user
B. connecting to a database instance
C. querying data from tables in different databases
D. starting up a database instance
E. executing operating system (OS) commands in a session

Answer: A,C

Show Answer

Question # 16

Which two statements are true about Data Manipulation Language (DML) statements? (Choose two.) 

A. An INSERT INTO...VALUES.. statement can add multiple rows per execution to a table.
B. An UPDATE... SET... statement can modify multiple rows based on multiple conditions on a table.
C. A DELETE FROM..... statement can remove rows based on only a single condition on a table.
D. An INSERT INTO... VALUES..... statement can add a single row based on multiple conditions on a table.
E. A DELETE FROM..... statement can remove multiple rows based on multiple conditions on a table.
F. An UPDATE....SET.... statement can modify multiple rows based on only a single condition on a table.

Answer: B,E

Show Answer