Oracle 1Z0-071 Quiz 34 Topic 1 Questions 166-170

Question: 1

View the Exhibit and examine the structure in the DEPARTMENTS tables. (Choose two.)

q1_1Z0-071

Examine this SQL statement:

SELECT department_id "DEPT_ID", department_name, 'b'

FROM departments

WHERE departments_id=90

UNION

SELECT department_id, department_name DEPT_NAME, 'a'

FROM departments

WHERE department_id=10

Which two ORDER BY clauses can be used to sort the output? (Choose two.)

AORDER BY DEPT_NAME;

BORDER BY DEPT_ID;

CORDER BY 'b';

DORDER BY 3;

Show Answer

Question: 2

Which two statements are true regarding the WHERE and HAVING clauses in a SELECT statement? (Choose two.)

AThe WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.

BThe aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query.

CThe WHERE clause can be used to exclude rows after dividing them into groups.

DThe HAVING clause can be used with aggregate functions in subqueries.

EThe WHERE clause can be used to exclude rows before dividing them into groups.

Show Answer

Question: 3

You must create a table EMPLOYEES in which the values in the columns EMPLOYEES_ID and LOGIN_ID must be unique and not null.

Which two SQL statements would create the required table? (Choose two.)

ACREATE TABLE employees(employee_id NUMBER,login_id NUMBER,employee_name VARCHAR2(100),hire_date DATE,CONSTRAINT emp_id_uk UNIQUE (employee_id, login_id));

BCREATE TABLE employees(employee_id NUMBER,login_id NUMBER,employee_name VARCHAR2(25),hire_date DATE,CONSTRAINT emp_id_pk PRIMARY KEY (employee_id, login_id));

CCREATE TABLE employees(employee_id NUMBER CONSTRAINT emp_id_pk PRIMARY KEY,login_id NUMBER UNIQUE,employee_name VARCHAR2(25),hire_date DATE);

DCREATE TABLE employees(employee_id NUMBER,login_id NUMBER,employee_name VARCHAR2(100),hire_date DATE,CONSTRAINT emp_id_uk UNIQUE (employee_id, login_id);CONSTRAINT emp_id_nn NOT NULL (employee_id, login_id));

ECREATE TABLE employees(employee_id NUMBER CONSTRAINT emp_id_nn NOT NULL,login_id NUMBER CONSTRAINT login_id_nn NOT NULL,employee_name VARCHAR2(100),hire_date DATE,CONSTRAINT emp_num_id_uk UNIQUE (employee_id, login_id));

Show Answer

Question: 4

View the Exhibit and examine the structure of the PRODUCT_INFORMATION table.

q4_1Z0-071

PRODUCT_ID column is the primary key.

You create an index using this command:

SQL > CREATE INDEX upper_name_idx

ON product_information(UPPER(product_name));

No other indexes exist on the PRODUCT_INFORMATION table.

Which query would use the UPPER_NAME_IDX index? (Choose the best answer.)

ASELECT product_id, UPPER(product_name)FROM product_informationWHERE UPPER(product_name)='LASERPRO' OR list_price > 1000;

BSELECT UPPER(product_name)FROM product_information;

CSELECT UPPER(product_name)FROM product_informationWHERE product_id = 2254;

DSELECT product_idFROM product_informationWHERE UPPER(product_name) IN ('LASERPRO', 'CABLE');

Show Answer

Question: 5

Examine the types and examples of relationships that follow:

q5_1Z0-071

Which option indicates correctly matched relationships? (Choose the best answer.)

A1-d, 2-b, 3-a, and 4-c

B1-c, 2-d, 3-a, and 4-b

C1-a, 2-b, 3-c, and 4-d

D1-c, 2-a, 3-b, and 4-d

Show Answer

Oracle 1Z0-071 Quiz:34 Topic:1 Questions:166-170