Oracle 1Z0-071 Online Practice Questions and Exam Preparation

Oracle 1Z0-071 Online Questions & Answers

• Question 281:

  Examine the data in the ENPLOYEES table:

  

  Which statement will compute the total annual compensation tor each employee

  A. SELECT last_name, (monthly_salary * 12) + (monthly_salary * 12 * NVL(monthly_commission_pct, 0)) AS annual_comp FROM employees;
  B. SELECT last_name, (monthly_salary * 12) + (monthly_salary * 12 * monthly_commission_pct) AS annual_comp FROM employees;
  C. SELECT last_name, (monthly_salary + monthly_commission_pct) * 12 AS annual_comp FROM employees;
  D. SELECT last_name, (monthly_salary * 12) + (monthly_commission_pct * 12) AS annual_comp FROM employees;
  A. SELECT last_name, (monthly_salary * 12) + (monthly_salary * 12 * NVL(monthly_commission_pct, 0)) AS annual_comp FROM employees;

• Question 282:

  Which two statements are true regarding non-equijoins? (Choose two.)

  A. Table aliases must be used.
  B. The SQL:1999 compliant ANSI join syntax must be used.
  C. The USING clause can be used.
  D. The Oracle join syntax can be used.
  E. The ON clause can be used.
  D. The Oracle join syntax can be used.
  E. The ON clause can be used.

• Question 283:

  Which three statements are true about time zones, date data types, and timestamp data types in an Oracle database? (Choose three.)

  A. A TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the time zone of the session that inserted the row.
  B. The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC).
  C. The DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC).
  D. The CURRENT_TIMESTAMP function returns data without time zone information.
  E. A TIMESTAMP data type column contains information about year, month, and day.
  B. The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC).
  C. The DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC).
  E. A TIMESTAMP data type column contains information about year, month, and day.

• Question 284:

  Examine the description of the EMPLOYEES table:

  

  Examine these requirements:

  1.

  Display the last name, date of hire and the number of years of service for each employee.

  2.

  If the employee has been employed 5 or more years but less than 10, display "5+ years of service".

  3.

  If the employee has been employed 10 or more years but less than 15, display "10+ years of service".

  4.

  If the employee has been employed 15 or more years, display "15+ years of service".

  5.

  If none of these conditions matches, display "<5 years of service".

  6.

  Sort the results by the HIRE_DATE column.

  Which statement satisfies all the requirements?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  D. Option D

  ---

  Explanation/Reference:

  SELECT e.last_name, hire_date, sysdate,

  (CASE WHEN (sysdate -TO_YMINTERVAL('15-0')) >= hire_date THEN '15 years of service'

  WHEN (sysdate -TO_YMINTERVAL('10-0')) >= hire_date THEN '10 years of service'

  WHEN (sysdate - TO_YMINTERVAL('5-0')) >= hire_date THEN '5 years of service'

  ELSE '<5 years of service' END) AS years

  FROM employees

  ORDER BY hire_date

• Question 285:

  You execute these commands successfully:

  

  Which two are true?

  A. You can add a foreign key to the table.
  B. When you terminate your session, the row will be deleted.
  C. To drop the table in this session, you must first truncate it.
  D. You can add a column to the table in this session.
  E. Other sessions can view the committed row.
  B. When you terminate your session, the row will be deleted.
  C. To drop the table in this session, you must first truncate it.

• Question 286:

  View the exhibit and examine the structure in ORDERS and ORDER_ITEMS tables.

  

  You need to create a view that displays the ORDER_ID, ORDER_DATE, and the total number of items in each order.

  Which CREATE VIEW statement would create the view successfully?

  A. CREATE OR REPLACE VIEW ord_vu AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id, o.order_date;
  B. CREATE OR REPLACE VIEW ord_vu (order_id, order_date) AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) "NO OF ITEMS" FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id, o.order_date;
  C. CREATE OR REPLACE VIEW ord_vu AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) "NO OF ITEMS" FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id, o.order_date;
  D. CREATE OR REPLACE VIEW ord_vu AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) || "NO OF ITEMS" FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id, o.order_date WHITH CHECK OPTION;
  C. CREATE OR REPLACE VIEW ord_vu AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) "NO OF ITEMS" FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id, o.order_date;

• Question 287:

  Which three statements are true about GLOBAL TEMPORARY TABLES?

  A. GLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose user has been granted select on the table.
  B. A TRUNCATE command issued in a session causes all rows In a GLOBAL TEMPORARY TABLE for the issuing session to be deleted.
  C. A DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back.
  D. A GLOBAL TEMPORARY TABLE's definition is available to multiple sessions.
  E. Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted.
  F. GLOBAL TEMPORARY TABLE space allocation occurs at session start.
  B. A TRUNCATE command issued in a session causes all rows In a GLOBAL TEMPORARY TABLE for the issuing session to be deleted.
  D. A GLOBAL TEMPORARY TABLE's definition is available to multiple sessions.
  E. Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted.

• Question 288:

  Examine this query:

  SELECT TRUNC (ROUND(156.00,-2),-1) FROM DUAL;

  What is the result?

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

• Question 289:

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

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

• Question 290:

  You create a table named 123. Which statement runs successfully?

  A. SELECT * FROM TABLE (123) ;
  B. SELECT * FROM andapos;123andapos;;
  C. SELECT * FROM "123";
  D. SELECT * FROM Vandapos;123Vandapos;;
  C. SELECT * FROM "123";