1Z0-071 Oracle Exam Questions and Free Practice Test

Question 7

Which three statements are true regarding the SQL WHERE and HAVING clauses?

A. The HAVING clause conditions can have aggregating functions.

B. The HAVING clause conditions can use aliases for the columns.

C. The WHERE and HAVING clauses cannot be used together in a SQL statement.

D. The WHERE clause is used to exclude rows before grouping data.

E. The HAVING clause is used to exclude one or more aggregated results after grouping data.

Correct Answer:ADE

Question 8

Examine the types and examples of relationship that follows: (Choose the best answer.)
1 One-to-one a) teacher to Student
2 One-to-many b) Employees to Manager
3 Many-to-one c) Person to SSN
4 Many-to-many d) Customers to Products
Which option indicates correctly matched relationships?

A. 1-d, 2-b, 3-a, and 4-c

B. 1-c, 2-d, 3-a, and 4-b

C. 1-a, 2-b, 3-c, and 4-d

D. 1-c, 2-a, 3-b, and 4-d

Correct Answer:C

Question 9

Sales data of a company is stored in two tables, SALES1 and SALES2, with some data being duplicated across the tables. You want to display the results from the SALES1 table, which are not present in the SALES2 table.
SALES1 table NameNullType
----------------------------------------------- SALES_IDNUMBER STORE_IDNUMBER ITEMS_IDNUMBER QUANTITYNUMBER SALES_DATEDATE
SALES2 table NameNullType
---------------------------------------------- SALES_IDNUMBER STORE_IDNUMBER
ITEMS_IDNUMBER QUANTITYNUMBER SALES_DATEDATE
Which set operator generates the required output?

A. INTERSECT

B. UNION

C. PLUS

D. MINUS

E. SUBTRACT

Correct Answer:D
References:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm

Question 10

Examine the business rule:
Each student can work on multiple projects and each project can have multiple students.
You need to design an Entity Relationship Model (ERD) for optimal data storage and allow for generating reports in this format:
STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK
Which two statements are true in this scenario?

A. The ERD must have a 1:M relationship between the STUDENTS and PROJECTS entities.

B. The ERD must have a M:M relationship between the STUDENTS and PROJECTS entities that must be resolved into 1:M relationships.

C. STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS entity.

D. PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity.

E. An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the STUDENTS and PROJECTS entities.

Correct Answer:BE
References:
http://www.oracle.com/technetwork/issue-archive/2011/11-nov/o61sql-512018.html

Question 11

Evaluate the following ALTER TABLE statement:
ALTER TABLE orders
SET UNUSED (order_date); Which statement is true?

A. After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table.

B. The ORDER_DATE column should be empty for the ALTER TABLE command to execute succsessfully.

C. ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.

D. The DESCRIBE command would still display the ORDER_DATE column.

Correct Answer:A

Question 12

View the Exhibit and examine the structure of the SALES and PRODUCTS tables. (Choose two.)
1Z0-071 dumps exhibit
In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table. You must list each product ID and the number of times it has been sold.
Examine this query which is missing a JOIN operator: SQL > SELECT p.prod_id, count(s.prod_id)
FROM products p sales s ON p.prod_id = s.prod_id
GROUP BY p.prod_id;
Which two JOIN operations can be used to obtain the required output?

A. FULL OUTER JOIN

B. JOIN

C. LEFT OUETR JOIN

D. RIGHT OUTER JOIN

Correct Answer:AC

START 1Z0-071 EXAM