Exam Code: 1z0 051 dumps pdf (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Oracle Database: SQL Fundamentals I
Certification Provider: Oracle
Free Today! Guaranteed Training- Pass oracle 1z0 051 Exam.


♥♥ 2017 NEW RECOMMEND ♥♥

Free VCE & PDF File for Oracle 1Z0-051 Real Exam (Full Version!)

★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions

Free Instant Download NEW 1Z0-051 Exam Dumps (PDF & VCE):
Available on: http://www.surepassexam.com/1Z0-051-exam-dumps.html

Q1. - (Topic 2) 

View the Exhibit and examine the structure and data in the INVOICE table. 

Which two SQL statements would execute successfully? (Choose two.) 

A. SELECT AVG(inv_date ) FROM invoice; 

B. SELECT MAX(inv_date),MIN(cust_id) FROM invoice; 

C. SELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice; 

D. SELECT AVG( inv_date - SYSDATE), AVG(inv_amt) FROM invoice; 

Answer: B,D 

Explanation: 

Using the AVG and SUM Functions You can use the AVG, SUM, MIN, and MAX functions against the columns that can store numeric data. The example in the slide displays the average, highest, lowest, and sum of monthly salaries for all sales representatives Using the MIN and MAX Functions You can use the MAX and MIN functions for numeric, character, and date data types. The example in the slide displays the most junior and most senior employees. 


Q2. - (Topic 2) 

The EMPLOYEES table contains these columns: 

EMPLOYEE_ID NUMBER(4) 

LAST_NAME VARCHAR2 (25) 

JOB_ID VARCHAR2(10) 

You want to search for strings that contain 'SA_' in the JOB_ID column. Which SQL statement do you use? 

A. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA\\_' ESCAPE '\\' 

B. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_' 

C. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_' ESCAPE "\\"; 

D. SELECT employee_id, last_name, job_id FROM employees WHERE job_id = '%SA_' 

Answer:

Explanation: ESCAPE identifier to search for the _ symbol 

Incorrect Answer: BESCAPE identifier must be use Cwrong syntax Dwrong syntax 

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-13 


Q3. - (Topic 1) 

A SELECT statement can be used to perform these three functions: 

Choose rows from a table. 

Choose columns from a table 

Bring together data that is stored in different tables by creating a link between 

them. 

Which set of keywords describes these capabilities? 

A. difference, projection, join 

B. selection, projection, join 

C. selection, intersection, join 

D. intersection, projection, join 

E. difference, projection, product 

Answer:

Explanation: Explanation: choose rows from a table is SELECTION, 

Choose column from a table is PROJECTION 

Bring together data in different table by creating a link between them is JOIN. 

Incorrect Answer: 

Aanswer should have SELECTION, PROJECTION and JOIN. 

Canswer should have SELECTION, PROJECTION and JOIN. 

Danswer should have SELECTION, PROJECTION and JOIN. 

Eanswer should have SELECTION, PROJECTION and JOIN. 

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 1-6 


Q4. - (Topic 1) 

Which statement is true regarding the UNION operator? 

A. The number of columns selected in all SELECT statements need to be the same 

B. Names of all columns must be identical across all SELECT statements 

C. By default, the output is not sorted 

D. NULL values are not ignored during duplicate checking 

Answer:

Explanation: 

The SQL UNION query allows you to combine the result sets of two or more SQL SELECT statements. It removes duplicate rows between the various SELECT statements. Each SQL SELECT statement within the UNION query must have the same number of fields in the result sets with similar data types. 


Q5. - (Topic 2) 

Examine the structure and data in the PRICE_LIST table: 

Name Null Type 

PROD_ID NOT NULL NUMBER(3) PROD_PRICE VARCHAR2(10) PROD_ID PROD_PRICE 

100 $234.55 101 $6,509.75 102 $1,234 

You plan to give a discount of 25% on the product price and need to display the discount amount in the same format as the PROD_PRICE. 

Which SQL statement would give the required result? 

A. SELECT TO_CHAR(prod_price* .25,'$99,999.99') FROM PRICE_LIST; 

B. SELECT TO_CHAR(TO_NUMBER(prod_price)* .25,'$99,999.00') FROM PRICE_LIST; 

C. SELECT TO_CHAR(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LIST; 

D. SELECT TO_NUMBER(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LIST; 

Answer:

Explanation: Use TO_NUMBER on the prod_price column to convert from char to number 

to be able to multiply it with 0.25. Then use the TO_CHAR function (with 

formatting'$99,999.00') to convert the number back to char. 

Incorrect: 

Not C: Use the formatting'$99,999.00' with the TO_CHAR function, not with the 

TO_NUMBER function. 

Note: 

Using the TO_CHAR Function The TO_CHAR function returns an item of data type VARCHAR2. When applied to items of type NUMBER, several formatting options are available. The syntax is as follows: TO_CHAR(number1, [format], [nls_parameter]), The number1 parameter is mandatory and must be a value that either is or can be implicitly converted into a number. The optional format parameter may be used to specify numeric formatting information like width, currency symbol, the position of a decimal point, and group (or thousands) separators and must be enclosed in single 

Syntax of Explicit Data Type Conversion Functions TO_NUMBER(char1, [format mask], [nls_parameters]) = num1 TO_CHAR(num1, [format mask], [nls_parameters]) = char1 TO_DATE(char1, [format mask], [nls_parameters]) = date1 TO_CHAR(date1, [format mask], [nls_parameters]) = char1 


Q6. - (Topic 1) 

Which SQL statement displays the date March 19, 2001 in a format that appears as “Nineteenth of March 2001 12:00:00 AM”? 

A. SELECT TO_CHAR(TO_DATE('19-Mar-2001’, ‘DD-Mon-YYYY’), ‘fmDdspth “of” Month YYYY fmHH:MI:SS AM’) NEW_DATE FROM dual; 

B. SELECT TO_CHAR(TO_DATE(’19-Mar-2001’, ‘DD-Mon-YYYY’), ‘Ddspth “of” Month YYYY fmHH:MI:SS AM’) NEW_DATE FROM dual; 

C. SELECT TO_CHAR(TO_DATE(’19-Mar-2001’, ‘DD-Mon-YYYY’), ‘fmDdspth “of” Month YYYY HH:MI:SS AM’) NEW_DATE FROM dual; 

D. SELECT TO_CHAR(TO_DATE(’19-Mar-2001’, ‘DD-Mon-YYYY), ‘fmDdspth “of” Month YYYYfmtHH:HI:SS AM') NEW_DATE FROM dual; 

Answer:


Q7. - (Topic 1) 

Evaluate the following SQL statements: Exhibit: 

Which is the correct output of the above query? 

A. +00-300, +54-02,+00 11:12:10.123457 

B. +00-300,+00-650,+00 11:12:10.123457 

C. +25-00, +54-02, +00 11:12:10.123457 

D. +25-00,+00-650,+00 11:12:10.123457 

Answer:


Q8. - (Topic 1) 

Evaluate the following SQL statements: Exhibit: 

Exhibit: 

The above command fails when executed. What could be the reason? 

A. The BETWEEN clause cannot be used for the CHECK constraint 

B. SYSDATE cannot be used with the CHECK constraint 

C. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY 

D. The CHECK constraint cannot be placed on columns having the DATE data type 

Answer:

Explanation: 

CHECK Constraint The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as the query conditions, with the following exceptions: References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns Calls to SYSDATE, UID, USER, and USERENV functions Queries that refer to other values in other rows A single column can have multiple CHECK constraints that refer to the column in its definition. There is no limit to the number of CHECK constraints that you can define on a column. CHECK constraints can be defined at the column level or table level. CREATE TABLE employees (... salary NUMBER(8,2) CONSTRAINT emp_salary_min CHECK (salary > 0), 


Q9. - (Topic 2) 

What are two reasons to create synonyms? (Choose two.) 

A. You have too many tables. 

B. Your tables names are too long. 

C. Your tables have difficult names. 

D. You want to work on your own tables. 

E. You want to use another schema's tables. 

F. You have too many columns in your tables. 

Answer: B,C 

Explanation: 

Create a synonyms when the names of the tables are too long or the table names are difficult. 


Q10. - (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: