Oracle 1Z0-071 Online Practice Questions and Exam Preparation

Oracle 1Z0-071 Online Questions & Answers

• Question 341:

  The ORDERS table has a column ORDER_DATE of data type DATE.

  The default display format for a date is DD-MON-RR.

  Which two WHERE conditions demonstrate the correct usage of conversion functions? (Choose two.)

  A. WHERE order_date > TO_DATE(`JUL 10 2018', ` MON DD YYYY')
  B. WHERE order_date > TO_DATE(ADD_MONTH(SYSDATE, 6), `MON DD YYYY')
  C. WHERE order_date IN (TO_DATE(`Oct 21 2018', `Mon DD YYYY'), TO_CHAR(`Nov 21 2018', `Mon DD YYYY'))
  D. WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE, 6), `MON DD YYYY')
  E. WHERE TO_CHAR(order_date. `MON DD YYYY') = `JAN 20 2019'
  A. WHERE order_date > TO_DATE(`JUL 10 2018', ` MON DD YYYY')
  E. WHERE TO_CHAR(order_date. `MON DD YYYY') = `JAN 20 2019'

• Question 342:

  You execute the following commands:

  SQL > DEFINE hiredate = '01-APR-2011'

  SQL >SELECT employee_id, first_name, salary

  FROM employees

  WHERE hire_date > 'andhiredate'

  AND manager_id > andmgr_id;

  For Which substitution variables are you prompted for the input?

  A. none, because no input required
  B. both the substitution variables ''hiredate' and 'mgr_id'.
  C. only hiredate'
  D. only 'mgr_id'
  D. only 'mgr_id'

• Question 343:

  You execute this command: TRUNCATE TABLE depts; Which two are true?

  A. It retains the indexes defined on the table.
  B. It drops any triggers defined on the table.
  C. A Flashback TABLE statement can be used to retrieve the deleted data.
  D. It retains the integrity constraints defined on the table.
  E. A ROLLBACK statement can be used to retrieve the deleted data.
  F. It always retains the space used by the removed rows
  A. It retains the indexes defined on the table.
  D. It retains the integrity constraints defined on the table.

• Question 344:

  Which two statements are true about the data dictionary?

  A. The data dictionary is accessible when the database is closed.
  B. The data dictionary does not store metadata in tables.
  C. Views with the prefix ALL_, DBA_ and USER_ are not all available for every type of metadata.
  D. Views with the prefix DBA_ display only metadata for objects in the SYS schema.
  E. Views with the prefix ALL_ display metadata for objects to which the current user has access.
  C. Views with the prefix ALL_, DBA_ and USER_ are not all available for every type of metadata.
  E. Views with the prefix ALL_ display metadata for objects to which the current user has access.

• Question 345:

  The STORES table has a column START_DATE of data type DATE, containing the datethe row was inserted.

  You only want to display details of rows where START_DATEis within the last 25 months.Which WHERE clause can be used?

  A. WHERE TO_NUMBER(start_date - SYSDATE)
  B. WHERE ADD_MONTHS (start date , 25)
  C. WHERE MONTHS_BETWEEN(SYSDATE, start_date)
  D. WHERE MONTHS_BETWEEN (start_date, SYSDATE)
  C. WHERE MONTHS_BETWEEN(SYSDATE, start_date)

• Question 346:

  Examine this SQL statement:

  

  Which two are true? (Choose two.)

  A. The DELETE statement executes successfully even if the subquery selects multiple rows.
  B. The subquery is executed before the DELETE statement is executed.
  C. The subquery is not a correlated subquery.
  D. All existing rows in the EMPLOYEES table are deleted.
  E. The subquery is executed for every row in the EMPLOYEES table.
  A. The DELETE statement executes successfully even if the subquery selects multiple rows.
  E. The subquery is executed for every row in the EMPLOYEES table.

• Question 347:

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

  

  You must display the NAME of stores along with the ADDRESS, START_DATE, PROPERTY_PRICE, and the projected property price, Which is 115% of property price.

  The stores displayed must have START_DATE in the range of 36 months starting from 01-Jan-2000 and above.

  Which SQL statement would get the desired output?

  A. SELECT name, concat (address| | ','| |city| |', ', country) AS full_address, start_date, property_price, property_price*115/100 FROM stores WHERE MONTHS_BETWEEN (start_date, '01-JAN-2000')
  B. SELECT name, concat (address| | ','| |city| |', ', country) AS full_address, start_date, property_price, property_price*115/100 FROM stores WHERE TO_NUMBER(start_date-TO_DATE('01-JAN-2000','DD-MON-RRRR'))
  C. SELECT name, address||', '||city||', '||country AS full_address, start_date, property_price, property_price*115/100 FROM stores WHERE MONTHS_BETWEEN(start_date,TO_DATE('01-JAN-2000','DD-MON-RRRR'))
  D. SELECT name, concat (address||','| |city| |', ', country) AS full_address, start_date, property_price, property_price*115/100 FROM stores WHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR'))
  D. SELECT name, concat (address||','| |city| |', ', country) AS full_address, start_date, property_price, property_price*115/100 FROM stores WHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR'))

• Question 348:

  Examine the description of the PRODUCTS table Which contains data:

  

  Which two are true?

  A. The PROD_NAME column cannot have a DEFAULT clause added to it.
  B. The EXPIRY_DATE column cannot be dropped.
  C. The EXPIRY_DATE column data type can be changed to TIMESTAMP.
  D. The PROD_ID column can be renamed.
  E. The PROD_ID column data type can be changed to VARCHAR2(2).
  C. The EXPIRY_DATE column data type can be changed to TIMESTAMP.
  D. The PROD_ID column can be renamed.

• Question 349:

  Examine the description of the CUSTOMERS table:

  

  You need to display last names and credit limits of all customers whose last name starts with A or B In lower or upper case, and whose credit limit is below 1000.

  Examine this partial query:

  SELECT cust_last_nare, cust_credit_limit FROM customers

  Which two WHERE conditions give the required result?

  A. WHERE UPPER(cust_last_name) IN (`AX', `B%') AND cust_credit_limit < 1000;
  B. WHERE (UPPER(cust_last_name) LIKE `A%' OR UPPER(cust_last_name) LIKE `B%') AND ROUND(cust_credit_limit) < 1000;
  C. WHERE UPPER(cust_last_name) BETWEEN UPPER(`A%' AND `B%') AND ROUND(cust_credit_limit) < 1000;
  D. WHERE (INITCAP(cust_last_name) LIKE `A%' OR INITCAP(cust_last_name) LIKE `B%') AND cust_credit_limit < 1000;
  E. WHERE (UPPER(cust_last_name) LIKE INITCAP(`A') OR UPPER(cust_last_name) LIKE INITCAP(`B')) AND ROUND(cust_credit_limit) < ROUND(1000);
  B. WHERE (UPPER(cust_last_name) LIKE `A%' OR UPPER(cust_last_name) LIKE `B%') AND ROUND(cust_credit_limit) < 1000;
  D. WHERE (INITCAP(cust_last_name) LIKE `A%' OR INITCAP(cust_last_name) LIKE `B%') AND cust_credit_limit < 1000;

• Question 350:

  View the exhibit and examine the structure and data in the INVOICE table.

  

  Which two SQL statements would execute successfully? (Choose two.)

  A. SELECT MAX(AVG(SYSDATE -inv_date)) FROM invoice
  B. SELECT AVG(inv_date) FROM invoice
  C. SELECT MAX(inv_date), MIN(cust_id) FROM invoice
  D. SELECT AVG( inv_date -SYSDATE), AVG(inv_amt) FROM invoice
  C. SELECT MAX(inv_date), MIN(cust_id) FROM invoice
  D. SELECT AVG( inv_date -SYSDATE), AVG(inv_amt) FROM invoice