Oracle 1Z0-071 Quiz 44 Topic 8 Questions 216-220

Question: 1

Which two statements are true regarding the SQL GROUP BY clause? (Choose two.)

AYou can use a column alias in the GROUP BY clause.

BUsing the WHERE clause after the GROUP BY clause excludes rows after creating groups.

CThe GROUP BY clause is mandatory if you are using an aggregating function in the SELECT clause.

DUsing the WHERE clause before the GROUP BY clause excludes rows before creating groups.

EIf the SELECT clause has an aggregating function, then columns without an aggregating function in the SELECT clause should be included in the GROUP BY clause.

Show Answer

Question: 2

You issue this command which succeeds:

SQL> DROP TABLE products;

Which three statements are true? (Choose three.)

AAll existing views and synonyms that refer to the table are invalidated but retained.

BAny uncommitted transaction in the session is committed.

CTable data and the table structure are deleted.

DAll the table's indexes if any exist, are invalidated but retained.

ETable data is deleted but the table structure is retained.

Show Answer

Question: 3

You execute the SQL statement:

q3_1Z0-071

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 CNAMES constraint is not valid.

DIt succeeds and an index is created for CITIZEN_ID.

Show Answer

Question: 4

Evaluate the following CREATE 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) );

The above command fails when executed. What could be the reason?

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: 5

Examine the structure of the PROGRAMS table:

q5_1Z0-071

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

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

Oracle 1Z0-071 Quiz:44 Topic:8 Questions:216-220