Oracle 1Z0-071 Quiz:23 Topic:6 Questions:111-115

Free Practice Mock Questions Set 111-115 () for Oracle 1Z0-071 Exam, according to official Oracle Database SQL exam syllabus topic # 6

Question: 1

You executed the following CREATE TABLE statement that resulted in an error:

SQL> CREATE TABLE employees(emp_id NUMBER(10) PRIMARY KEY, ename VARCHAR2(20), email NUMBER(3) UNIQUE, address VARCHAR2(500), phone VARCHAR2(20), resume LONG, hire_date DATE, remarks LONG, dept_id NUMBER(3) CONSTRAINT emp_dept_id_fk REFERENCES departments (dept_id), CONSTRAINT ename_nn NOY NULL(ename));

Identify two reasons for the error.

AThe NOT NULL constraint on the ENAME column must be defined as the column level

BFOREIGN KEY defined on the DEPT_ID column must be at the table level only

COnly one LONG column can be used per table

DThe FOREIGN KEY keyword is missing in the constraint definition

EThe PRIMARY KEY constraint in the EMP_ID column must have a name and must be defined at the table level only

Answer : A, C

Show Answer

Hide Answer

Question: 2

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

q2_1Z0-071

You want to generate a report showing the last names and credit limits of all customers whose last names start with A, B, or C, and credit limit is below 10,000.

Evaluate the following two queries:

SQL> SELECT cust_last_name, cust_credit_limit FROM customers

WHERE (UPPER(cust_last_name) LIKE 'A%' OR

UPPER (cust_last_name) LIKE 'B%' OR UPPER (cust_last_name) LIKE 'C%')

AND cust_credit_limit < 10000;

SQL>SELECT cust_last_name, cust_credit_limit FROM customers

WHERE UPPER (cust_last_name) BETWEEN 'A' AND 'C'

AND cust_credit_limit < 10000;

Which statement is true regarding the execution of the above queries?

AOnly the second query gives the correct result

BBoth execute successfully but do not give the required result

COnly the first query gives the correct result

DBoth execute successfully and give the same result

Answer : C

Show Answer

Hide Answer

Question: 3

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) );

Why would the ORD_ITEMS table not get created?

ASYSDATE cannot be used with the CHECK constraint.

BThe BETWEEN clause cannot be used twice for the same table.

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.

Answer : A

Show Answer

Hide Answer

Question: 4

Examine the structure of the EMPLOYEES table:

q4_1Z0-071

There is a parent/child relationship between EMPLOYEE_ID and MANAGER_ID.

You want to display the name, joining date, and manager for all employees. Newly hired employees are yet to be assigned a department or a manager. For them, 'No Manager' should be displayed in the MANAGER column.

Which SQL query gets the required output?

ASELECT e.last_name, e.hire_date, NVL(m.last_name, 'No Manager') ManagerFROM employees e JOIN employees mON (e.manager_id = m.employee_id);

BSELECT e.last_name, e.hire_date, NVL(m.last_name, 'No Manager') ManagerFROM employees e LEFT OUTER JOIN employees mON (e.manager_id = m.employee_id);

CSELECT e.last_name, e.hire_date, NVL(m.last_name, 'No Manager') ManagerFROM employees e RIGHT OUTER JOIN employees mON (e.manager_id = m.employee_id);

DSELECT e.last_name, e.hire_date, NVL(m.last_name, 'No Manager') ManagerFROM employees e NATURAL JOIN employees mON (e.manager_id = m.employee_id).

Answer : B

Show Answer

Hide Answer

Question: 5

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.

q5_1Z0-071

There is only one customer with the cust_last_name column having value Roberts. Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600?

AINSERT INTO ordersVALUES(1, '10-mar-2007', 'direct',(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), 1000);

BINSERT INTO orders (order_id, order_data, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), order_total)VALUES(1, '10-mar-2007', 'direct', &&customer_id, 1000);

CINSERT INTO(SELECT o.order_id, o.order_date, o.order_mode, c.customer_id, o.order_totalFROM orders o, customers cWHERE o.customer_id = c.customer_idAND c.cust_last_name='Roberts' AND c.credit_limit=600 )VALUES (1, '10-mar-2007', 'direct', (SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), 1000);

DINSERT INTO orders (order_id, order_data, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), order_total)VALUES (1, '10-mar-2007', 'direct', &customer_id, 1000).

Answer : A

Show Answer

Hide Answer

Quiz 19

Quiz 20

Quiz 21

Quiz 22

Quiz 23

Quiz 24

Quiz 25

Quiz 26

Quiz 27