Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 31:

  You need to display the date 11-oct-2007 in words as `Eleventh of October, Two Thousand Seven'.

  Which SQL statement would give the required result?

  A. SELECT TO_CHAR (TO_DATE ('11-oct-2007'), `fmDdthsp "of" Month, Year') FROM DUAL

  B. SELECT TO_CHAR (`11-oct-2007', `fmDdspth "of" Month, Year') FROM DUAL

  C. SELECT TO_CHAR (TO_DATE (`11-oct-2007'), `fmDdspth of month, year') FROM DUAL

  D. SELECT TO_DATE (TO_CHAR ('11-oct-2007'), `fmDdspth "of" Month, Year')) FROM DUAL

  Correct Answer: A

• Question 32:

  Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS:

  

  You want to generate a report that shows all course IDs irrespective of whether they have corresponding department IDs or not but no department IDs if they do not have any courses. Which SQL statement must you use?

  A. SELECT course_id, department_id, FROM department_details d RIGHT OUTER JOIN course_details c USING (department_id)

  B. SELECT c.course_id, d.department_id FROM course_details c RIGHT OUTER JOIN .department_details d ON (c.depatrment_id=d.department_id)

  C. SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_id=d. department_id)

  D. SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_id<>d. department_id)

  Correct Answer: C

• Question 33:

  View the Exhibit and examine the structure of the ORDERS table. The ORDER_ID column is the PRIMARY KEY in the ORDERS table.

  

  Evaluate the following CREATE TABLE command:

  CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id)

  AS

  SELECT order_id.order_date,customer_id

  FROM orders;

  Which statement is true regarding the above command?

  A. The NEW_ODRDERS table would not get created because the DEFAULT value cannot be specified in the column definition.

  B. The NEW_ODRDERS table would get created and only the NOT NULL constraint defined on the specified columns would be passed to the new table.

  C. The NEW_ODRDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.

  D. The NEW_ODRDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table.

  Correct Answer: B

• Question 34:

  View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and TIMES tables.

  

  The PROD_ID column is the foreign key in the SALES table referencing the PRODUCTS table.

  The CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.

  Examine this command:

  CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE)

  AS SELECT prod_id, cust_id, time_id FROM sales;

  Which statement is true?

  A. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.

  B. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.

  C. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.

  D. The NEW_SALES table would get created and all the NOT NULL constraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.

  Correct Answer: D

• Question 35:

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

  

  You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the `INTERNET' category.

  Which query would give you the required output?

  A. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date> ALL (SELECT MAX (promo_begin_date) FROM promotions) AND promo_category= `INTERNET';

  B. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date IN (SELECT promo_begin_date FROM promotions WHERE promo_category= `INTERNET');

  C. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT promo_begin_date FROM promotions WHERE promo_category = `INTERNET');

  D. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date> ANY (SELECT promo_begin_date FROM promotions WHERE promo_category= `INTERNET');

  Correct Answer: C

• Question 36:

  Which statement is true about Data Manipulation Language (DML)?

  A. DML automatically disables foreign ley constraints when modifying primary key values in the parent table.

  B. Each DML statement forms a transaction by default.

  C. A transaction can consist of one or more DML statements.

  D. DML disables foreign key constraints when deleting primary key values in the parent table, only when the ON DELETE CASCADE option is set for the foreign key constraint.

  Correct Answer: C

• Question 37:

  View the exhibit and examine the descriptions of the DEPT and LOCATIONS tables.

  

  You want to update the CITY column of the DEPT table for all the rows with the corresponding value in the CITY column of the LOCATIONS table for each department.

  Which SQL statement would you execute to accomplish the task?

  A. UPDATE dept d SET city = ALL (SELECT city FROM locations l WHERE d.location_id = l.location_id);

  B. UPDATE dept d SET city = (SELECT city FROM locations l) WHERE d.location_id = l.location_id;

  C. UPDATE dept d SET city = ANY (SELECT city FROM locations l)

  D. UPDATE dept d SET city = (SELECT city FROM locations l WHERE d.location_id = l.location_id);

  Correct Answer: D

• Question 38:

  View the exhibits and examine the structures of the COSTS and PROMOTIONS tables.

  

  Evaluate the following SQL statement:

  

  What would be the outcome of the above SQL statement?

  A. It displays prod IDs in the promo with the lowest cost.

  B. It displays prod IDs in the promos with the lowest cost in the same time interval.

  C. It displays prod IDs in the promos with the highest cost in the same time interval.

  D. It displays prod IDs in the promos Which cost less than the highest cost in the same time interval.

  Correct Answer: D

• Question 39:

  Which three statements are true? (Choose three.)

  A. The data dictionary is created and maintained by the database administrator.

  B. The data dictionary views consist of joins of dictionary base tables and user-defined tables.

  C. The usernames of all the users including database administrators are stored in the data dictionary.

  D. The USER_CONS_COLUMNS view should be queried to find the names of columns to Which constraints apply.

  E. Both USER_OBJECTS and CAT views provide the same information about all objects that are owned by the user.

  F. Views with the same name but different prefixes, such as DBA, ALL and USER, reference the same base tables from the data dictionary.

  Correct Answer: CDF

  References: https://docs.oracle.com/cd/B10501_01/server.920/a96524/c05dicti.htm

• Question 40:

  Which three statements are true regarding group functions? (Choose three.)

  A. They can be used on columns or expressions.

  B. They can be passed as an argument to another group function.

  C. They can be used only with a SQL statement that has the GROUP BY clause.

  D. They can be used on only one column in the SELECT clause of a SQL statement.

  E. They can be used along with the single-row function in the SELECT clause of a SQL statement.

  Correct Answer: ABE

  References: https://www.safaribooksonline.com/library/view/mastering-oracle-sql/0596006322/ch04.html