Oracle 1Z0-071 Quiz:75 Topic:9 Questions:371-375

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

Question: 1

Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS:

SQL>CREATE TABLE DEPARTMENT_DETAILS

(DEPARTMENT_ID NUMBER PRIMARY KEY,

DEPARTMENT_NAMEVARCHAR2(50),

HODVARCHAR2(50));

SQL>CREATE TABLE COURSE_DETAILS

(COURSE_IDNUMBER PRIMARY KEY,

COURSE_NAMEVARCHAR2(50),

DEPARTMENT_IDVARCHAR2(50));

You want to generate a list of all department IDs along with any course IDs that may have been assigned to them.

Which SQL statement must you use?

ASELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (d.department_id=c. department_id);

BSELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN course_details c ON (d.department_id=c. department_id);

CSELECT d.department_id, c.course_id FROM course_details c LEFT OUTER JOIN department_details d ON (c.department_id=d. department_id);

DSELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (c.department_id=d. department_id);

Answer : B

Show Answer

Hide Answer

Question: 2

Which two tasks can be performed by using Oracle SQL statements?

Achanging the password for an existing database user

Bconnecting to a database instance

Cquerying data from tables across databases

Dstarting up a database instance

Eexecuting operating system (OS) commands in a session

Answer : A, C

Show Answer

Hide Answer

Question: 3

View the exhibit for the structure of the STUDENT and FACULTY tables.

STUDENT

NameNull?Type

--------------------------------------------------

STUDENT_IDNOT NULLNUMBER(2)

STUDENT_NAMEVARCHAR2(20)

FACULTY_IDVARCHAR2(2)

LOCATION_IDNUMBER(2)

FACULTY

NameNull?Type

--------------------------------------------------

FACULTY_IDNOT NULLNUMBER(2)

FACULTY_NAMEVARCHAR2(20)

LOCATION_IDNUMBER(2)

You need to display the faculty name followed by the number of students handled by the faculty at the base location.

Examine the following two SQL statements:

Statement 1

SQL>SELECT faculty_name, COUNT(student_id)

FROM student JOIN faculty

USING (faculty_id, location_id)

GROUP BY faculty_name;

Statement 2

SQL>SELECT faculty_name, COUNT(student_id)

FROM student NATURAL JOIN faculty

GROUP BY faculty_name;

Which statement is true regarding the outcome?

AOnly statement 2 executes successfully and gives the required result.

BOnly statement 1 executes successfully and gives the required result.

CBoth statements 1 and 2 execute successfully and give different results.

DBoth statements 1 and 2 execute successfully and give the same required result.

Answer : B

Show Answer

Hide Answer

Question: 4

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

ORDERS

Name

Null?

Type

ORDER_ID

NOT NULL

NUMBER(4)

ORDER_DATE

NOT NULL

DATE

ORDER_MODE

VARCHAR2(8)

CUSTOMER_ID

NOT NULL

NUMBER(6)

ORDER_TOTAL

NUMBER(8, 2)

CUSTOMERS

Name

Null?

Type

CUSTOMER_ID

NOT NULL

NUMBER(6)

CUST_FIRST_NAME

NOT NULL

VARCHAR2(20)

CUST_LAST_NAME

NOT NULL

VARCHAR2(20)

CREDIT_LIMIT

NUMBER(9,2)

CUST_ADDRESS

VARCHAR2(40)

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? Assume there exists only one row with CUST_LAST_NAME as Roberts and CREDIT_LIMIT as 600.

AINSERT 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_id AND c.cust_last_name='Roberts' AND c.credit_limit=600)VALUES (1,'10-mar-2007', 'direct', (SELECT customer_idFROM customersWHERE cust_last_name='Roberts' AND credit_limit=600), 1000);

BINSERT INTO orders (order_id, order_date, order_mode,(SELECT customer idFROM customersWHERE cust_last_name='Roberts' AND credit_limit=600), order_total);VALUES (1,'10-mar-2007', 'direct', &customer_id, 1000);

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

DINSERT INTO orders (order_id, order_date, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' AND credit_limit=600), order_total);VALUES (1,'10-mar-2007', 'direct', &customer_id, 1000);

Answer : C

Show Answer

Hide Answer

Question: 5

Which statements are correct regarding indexes? (Choose all that apply.)

AA non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically attempts to creates a unique index.

BIndexes should be created on columns that are frequently referenced as part of any expression.

CWhen a table is dropped, the corresponding indexes are automatically dropped.

DFor each DML operation performed, the corresponding indexes are automatically updated.

Answer : A, C, D

Show Answer

Hide Answer

Quiz 71

Quiz 72

Quiz 73

Quiz 74

Quiz 75

Quiz 76

Quiz 77

Quiz 78

Quiz 79