Oracle 1Z0-071 Quiz 45 Topic 11 Questions 221-225

Question: 1

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?

ASELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdthsp ''of'' Month, Year')FROM DUAL;

BSELECT TO_CHAR('11-oct-2007', 'fmDdspth ''of'' Month, Year')FROM DUAL;

CSELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdspth of month, year')FROM DUAL;

DSELECT TO_DATE(TO_CHAR('11-oct-2007'), 'fmDdspth ''of'' Month, Year'))FROM DUAL;

Show Answer

Question: 2

Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS:

q2_1Z0-071

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?

ASELECT course_id, department_id, FROM department_details d RIGHT OUTER JOIN course_details c USING (department_id)

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

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

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

Show Answer

Question: 3

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

q3_1Z0-071

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?

AThe 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.

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

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

DThe 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.

Show Answer

Question: 4

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

q4_1Z0-071

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?

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

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

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

DThe 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.

Show Answer

Question: 5

Evaluate the following statement.

q5_1Z0-071

Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?

AEach row is evaluated by the first WHEN clause and if the condition is false then the row would be evaluated by the subsequent WHEN clauses.

BAll rows are evaluated by all the three WHEN clauses.

CEach row is evaluated by the first WHEN clause and if the condition is true, then the row would be evaluated by the subsequent WHEN clauses.

DThe INSERT statement will return an error because the ELSE clause is missing.

Show Answer

Oracle 1Z0-071 Quiz:45 Topic:11 Questions:221-225