Free 1Z0-071 Exam Dumps

Question 26

View the exhibit and examine the description of the DEPARTMENTS and EMPLOYEES tables.
1Z0-071 dumps exhibit
The retrieve data for all the employees for their EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT NAME, the following SQL statement was written:
SELECT employee_id, first_name, department_name FROM employees
NATURAL JOIN departments;
The desired output is not obtained after executing the above SQL statement. What could be the reason for this?

A. The table prefix is missing for the column names in the SELECT clause.
B. The NATURAL JOIN clause is missing the USING clause.
C. The DEPARTMENTS table is not used before the EMPLOYEES table in the FROM clause.
D. The EMPLOYEES and DEPARTMENTS tables have more than one column with the same column name and data type.

Correct Answer:D
Natural join needs only one column to be the same in each table. The EMPLOYEES and DEPARTMENTS tables have two columns that are the same (Department_ID and Manager_ID)

Question 27

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?

A. SYSDATE cannot be used with the CHECK constraint.
B. The BETWEEN clause cannot be used for the CHECK constraint.
C. The CHECK constraint cannot be placed on columns having the DATE data type.
D. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.

Correct Answer:A

Question 28

Which three arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database? (Choose three.)

A. Finding the lowest value
B. Finding the quotient
C. Raising to a power
D. Subtraction
E. Addition

Correct Answer:ACE

Question 29

Which two statements are true regarding constraints?

A. A table can have only one primary key and one foreign key.
B. A table can have only one primary key but multiple foreign keys.
C. Only the primary key can be defined at the column and table levels.
D. The foreign key and parent table primary key must have the same name.
E. Both primary key and foreign key constraints can be defined at both column and table levels.

Correct Answer:BE

Question 30

Examine the structure of the PROMOTIONS table: (Choose the best answer.)
1Z0-071 dumps exhibit
Management requires a report of unique promotion costs in each promotion category. Which query would satisfy this requirement?

A. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1
B. SELECT promo_category, DISTINCT promo_cost FROM promotions
C. SELECT DISTINCT promo_cost, promo_category FROM promotions
D. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;

Correct Answer:A