Oracle 1Z0-071 Online Practice Questions and Exam Preparation

Oracle 1Z0-071 Online Questions & Answers

• Question 221:

  Examine the structure of the BOOKS_TRANSACTIONS table: You want to display the member IDs, due date, and late fee as $2 for all transactions. Which SQL statement must you execute?

  

  A. SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE FROM BOOKS_TRANSACTIONS;
  B. SELECT member_id 'MEMBER ID', due_date 'DUE DATE', '$2 AS LATE FEE' FROM BOOKS_TRANSACTIONS;
  C. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE FEE" FROM BOOKS_TRANSACTIONS;
  D. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", $2 AS "LATE FEE" FROM BOOKS_TRANSACTIONS;
  C. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE FEE" FROM BOOKS_TRANSACTIONS;

• Question 222:

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

  ---

  Explanation/Reference:

  .

• Question 223:

  Examine the structure of the MEMBERS table:

  

  You want to display details of all members who reside in states starting with the letter A followed by exactly one character.

  Which SQL statement must you execute?

  A. SELECT * FROM MEMBERS WHERE state LIKE '%A_';
  B. SELECT * FROM MEMBERS WHERE state LIKE 'A_';
  C. SELECT * FROM MEMBERS WHERE state LIKE 'A_%';
  D. SELECT * FROM MEMBERS WHERE state LIKE 'A%';
  B. SELECT * FROM MEMBERS WHERE state LIKE 'A_';

• Question 224:

  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.
  A. They can contain HAVING clauses.
  D. They can return multiple columns.
  E. They can contain GROUP BY clauses.

• Question 225:

  The BOOKS_TRANSACTIONS table exists in your database.

  SQL>SELECT * FROM books_transactions ORDER BY 3;

  What is the outcome on execution?

  A. The execution fails unless the numeral 3 in the ORDER BY clause is replaced by a column name.
  B. Rows are displayed in the order that they are stored in the table only for the three rows with the lowest values in the key column.
  C. Rows are displayed in the order that they are stored in the table only for the first three rows.
  D. Rows are displayed sorted in ascending order of the values in the third column in the table.
  D. Rows are displayed sorted in ascending order of the values in the third column in the table.

• Question 226:

  Which statement is true regarding external tables?

  A. The CREATE TABLE AS SELECT statement can be used to upload data into a normal table in the database from an external table.
  B. The data and metadata for an external table are stored outside the database.
  C. The default REJECT LIMIT for external tables is UNLIMITED.
  D. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.
  A. The CREATE TABLE AS SELECT statement can be used to upload data into a normal table in the database from an external table.

  ---

  Explanation/Reference:

  References: https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables013.htm

• Question 227:

  You want to display the date for the first Monday of the next month and issue the following command:

  

  What is the outcome?

  A. In generates an error because rrrr should be replaced by rr in the format string.
  B. It executes successfully but does not return the correct result.
  C. It executes successfully and returns the correct result.
  D. In generates an error because TO_CHAR should be replaced with TO_DATE.
  E. In generates an error because fm and double quotation marks should not be used in the format string.
  C. It executes successfully and returns the correct result.

• Question 228:

  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.
  A. It processes NULLS in the selected columns.

• Question 229:

  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:

  

  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.00000000 AM
  D. 11-JUL-2019 11,00,00,000000AM -05:00
  C. 11-JUL-2019 6.00.00.00000000 AM

• Question 230:

  View the exhibit and examine the structure of the CUSTOMERS table.

  

  Which two tasks would require subqueries or joins to be executed in a single statement?

  A. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
  B. finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'
  C. listing of customers who do not have a credit limit and were born before 1980
  D. finding the number of customers, in each city, whose marital status is 'married'.
  E. listing of those customers, whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'.
  A. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
  E. listing of those customers, whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'.