Oracle 1Z0-071 Quiz 71 Topic 15 Questions 351-355

Question: 1

You execute the SQL statement:

SQL> CREATE TABLE citizens

(citizen_id CHAR (10) PRIMARY KEY,

last_name VARCHAR2 (50) NOT NULL,

first_name VARCHAR2 (50),

address VARCHAR2 (100),

city VARCHAR2 (30) DEFAULT 'SEATTLE' NOT NULL,

CONSTRAINT cnames CHECK (first_name<>last_name) );

What is the outcome?

AIt fails because the NOT NULL and DEFAULT options cannot be combined for the same column.

BIt succeeds and CITY can contain only 'SEATTLE' or null for all rows.

CIt fails because the condition for the CANAMES constraint is not valid.

DIt succeeds and an index is crated for CITIZEN_ID.

Show Answer

Question: 2

Evaluate the following CRTEATE TABLE commands:

CREATE_TABLE orders

(ord_no NUMBER (2) CONSTRAINT ord_pk PRIMARY KEY,

ord_date DATE,

cust_id NUMBER (4) );

CREATE TABLE ord_items

(ord _no NUMBER (2),

item_no NUMBER(3),

qty NUMBER (3) CHECK (qty BETWEEEN 100 AND 200),

expiry_date date CHECK (expiry_date> SYSDATE),

CONSTRAINT it_pk PRIMARY KEY (ord_no, item_no),

CONSTARAINT ord_fk FOREIGN KEY (ord_no) REFERENCES orders (ord_no) );

Why would the ORD_ITEMS table not get created?

ASYSDATE cannot be used with the CHECK constraint.

BThe BETWEEN clause cannot be used for the CHECK constraint.

CThe CHECK constraint cannot be placed on columns having the DATE data type.

DORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.

Show Answer

Question: 3

Examine the structure of the PROGRAMS table:

q3_1Z0-071

Which two SQL statements would execute successfully?

ASELECT NVL (ADD_MONTHS (END_DATE,1) SYSDATE) FROM programs;

BSELECT TO_DATE (NVL (SYSDATE-END_DATE, SYSDATE)) FROM programs;

CSELECT NVL (MONTHS_BETWEEN (start_date, end_date), 'Ongoing') FROM programs;

DSELECT NVL (TO_CHAR (MONTHS_BETWEEN (start-date, end_date)), 'Ongoing') FROM programs

Show Answer

Question: 4

Examine these SQL statements that are executed in the given order:

CREATE TABLE emp

(emp_no NUMBER (2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,

ename VARCHAR 2 (15),

salary NUMBER (8, 2),

mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp (emp_no));

ALTER TABLE emp

DISABLE CONSTRAINT emp_emp_no_pk CASCADE;

ALTER TABLE emp

ENABLE CONSTRAINT emp_emp_no_pk;

What will be the status of the foreign key EMP_MGR_FK?

AIt will be enabled and immediate.

BIt will be enabled and deferred.

CIt will remain disabled and can be re-enabled manually.

DIt will remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it.

Show Answer

Question: 5

View the Exhibit and examine, the description for the SALES and CHANNELS tables. (Choose the best answer.)

q5_1Z0-071

You issued this SQL statement:

INSERT INTO SALES VALUES (23, 2300, SYSDATE,

(SELECT CAHNNEL_ID

FROM CHANNELS

WHERE CHANNEL_DESC='DIRECT SALES'), 12, 1, 500);

Which statement is true regarding the result?

AThe statement will fail because the sub-query in the VALUES clause is not enclosed within single quotation marks.

BThe statement will fail because a subquery cannot be used in a VALUES clause.

CThe statement will execute and a new row will be inserted in the SALES table.

DThe statement will fail because the VALUES clause is not required with the subquery.

Show Answer

Oracle 1Z0-071 Quiz:71 Topic:15 Questions:351-355