Tips for 1z0 051 practice test

100% Correct of 1z0 051 practice test exam fees materials and pdf for Oracle certification for IT learners, Real Success Guaranteed with Updated 1z0 051 dumps pdf dumps vce Materials. 100% PASS Oracle Database: SQL Fundamentals I exam Today!

Q121. - (Topic 2) 

The STUDENT_GRADES table has these columns: 

STUDENT_ID NUMBER(12) 

SEMESTER_END DATE 

GPA NUMBER(4,3) 

Which statement finds the highest grade point average (GPA) per semester? 

A. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL; 

B. SELECT (gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL; 

C. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end; 

D. SELECT MAX(gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student_grades; 

E. SELECT MAX(gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL; 

Answer: C 

Explanation: Explanation: For highest gpa value MAX function is needed, for result with per semester GROUP BY clause is needed 

Incorrect Answer: Aper semester condition is not included Bresult would not display the highest gpa value Dinvalid syntax error Einvalid syntax error Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 5-7 

Q122. - (Topic 1) 

View the Exhibit and evaluate structures of the SALES, PRODUCTS, and COSTS tables. 

Evaluate the following SQL statements: 

Which statement is true regarding the above compound query? 

A. It shows products that have a cost recorded irrespective of sales 

B. It shows products that were sold and have a cost recorded 

C. It shows products that were sold but have no cost recorded 

D. It reduces an error 

Answer: C 

Q123. - (Topic 1) 

View the Exhibit and examine the data in the PRODUCTS table. You need to display product names from the PRODUCTS table that belong to the 'Software/Other1 category with minimum prices as either $2000 or $4000 and no unit of measure. You issue thej following query: 

Which statement is true regarding the above query? 

A. It executes successfully but returns no result. 

B. It executes successfully and returns the required result. 

C. It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid. 

D. It generates an error because the condition specified for the PROD_CATEGORY column is not valid. 

Answer: A 

Q124. - (Topic 2) 

View the Exhibit and examine the structure of the PRODUCTS, SALES, and SALE_SUMMARY tables. 

SALE_VW is a view created using the following command: 

SQL>CREATE VIEW sale_vw AS 

SELECT prod_id, SUM(quantity_sold) QTY_SOLD 

FROM sales GROUP BY prod_id; 

You issue the following command to add a row to the SALE_SUMMARY table: 

SQL>INSERT INTO sale_summary 

SELECT prod_id, prod_name, qty_sold FROM sale_vw JOIN products 

USING (prod_id) WHERE prod_id = 16; 

What is the outcome? 

A. It executes successfully. 

B. It gives an error because a complex view cannot be used to add data into the SALE_SUMMARY table. 

C. It gives an error because the column names in the subquery and the SALE_SUMMARY table do not match. 

D. It gives an error because the number of columns to be inserted does not match with the number of columns in the SALE_SUMMARY table. 

Answer: D 

Q125. - (Topic 2) 

Examine the structure of the PRODUCTS table: 

You want to display the names of the products that have the highest total value for UNIT_PRICE *QTY_IN_HAND. 

Which SQL statement gives the required output? 

A. 

SELECT prod_name FROM products WHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products); 

B. 

SELECT prod_name FROM products WHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products GROUP BY prod_name); 

C. 

SELECT prod_name FROM products GROUP BY prod_name HAVING MAX(unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products GROUP BY prod_name); 

D. 

SELECT prod_name 

FROM products 

WHERE (unit_price * qty_in_hand) = (SELECT MAX(SUM(unit_price * qty_in_hand)) 

FROM products) 

GROUP BY prod_name; 

Answer: A 

Q126. - (Topic 2) 

Which two statements are true regarding constraints? (Choose two.) 

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. 

Answer: B,E 

Q127. - (Topic 2) 

Which three statements are true regarding views? (Choose three.) 

A. Views can be created only from tables. 

B. Views can be created from tables or other views. 

C. Only simple views can use indexes existing on the underlying tables. 

D. Both simple and complex views can use indexes existing on the underlying tables. 

E. Complex views can be created only on multiple tables that exist in the same schema. 

F. Complex views can be created on multiple tables that exist in the same or different schemas. 

Answer: B,D,F 

Explanation: 

Creating a Sequence (continued) 

CYCLE | NOCYCLE Specifies whether the sequence continues to generate values after 

reaching its maximum or minimum value 

(NOCYCLE is the default option.) 

CACHE n | NOCACHE Specifies how many values the Oracle server preallocates and 

keeps in memory (By default, the Oracle server caches 20 values.) 

Q128. - (Topic 2) 

You need to produce a report for mailing labels for all customers. The mailing label must have only the customer name and address. The CUSTOMERS table has these columns: 

CUST_IDNUMBER(4)NOT NULL CUST_NAMEVARCHAR2(100)NOT NULL CUST_ADDRESSVARCHAR2(150) 

CUST_PHONEVARCHAR2(20) 

Which SELECT statement accomplishes this task? 

A. SELECT * FROM customers 

B. SELECT name, address FROM customers; 

C. SELECT id, name, address, phone FROM customers; 

D. SELECT cust_name, cust_address FROM customers; 

E. SELECT cust_id, cust_name, cust_address, cust_phone FROM customers; 

Answer: D 

Explanation: 

This answer provides correct list of columns for the output. 

Incorrect Answers 

A:This answer does not provide correct list of columns for the output. It is not required to 

show all columns of the table. Symbol “*” is used in the SELECT command to substitute a 

list of all columns of the table. 

B:This answer does not provide correct list of columns for the output. There are not NAME 

and ADDRESS columns in the CUSTOMERS table. 

C:This answer does not provide correct list of columns for the output. There are not ID, 

NAME, ADDRESS or PHONE columns in the CUSTOMERS table. 

E:This answer does not provide correct list of columns for the output. It is not required to 

show all columns of the table. 

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 20-24 

Chapter 1: Overview of Oracle Databases 

Q129. - (Topic 2) 

Examine the data in the ORD_ITEMS table: 

ORD_NO ITEM_NO QTY 

1 111 10 

1 222 20 

1 333 30 

2 333 30 

2 444 40 

3 111 40 

Evaluate the following query: 

SQL>SELECT item_no, AVG(qty) 

FROM ord_items 

HAVING AVG(qty) > MIN(qty) * 2 

GROUP BY item_no; 

Which statement is true regarding the outcome of the above query? 

A. It gives an error because the HAVING clause should be specified after the GROUP BY clause. 

B. It gives an error because all the aggregate functions used in the HAVING clause must be specified in the SELECT list. 

C. It displays the item nos with their average quantity where the average quantity is more than double the minimum quantity of that item in the table. 

D. It displays the item nos with their average quantity where the average quantity is more than double the overall minimum quantity of all the items in the table. 

Answer: C 

Q130. - (Topic 1) 

Examine the statement: 

Create synonym emp for hr.employees; 

What happens when you issue the statement? 

A. An error is generated. 

B. You will have two identical tables in the HR schema with different names. 

C. You create a table called employees in the HR schema based on you EMP table. 

D. You create an alternative name for the employees table in the HR schema in your own schema. 

Answer: D