1Z0-051 Guide

1Z0-051 Exam Royal Pack (In Stock.)

 
  • Oracle
  • Exam Number/Code 1Z0-051
  • Product Name Oracle Database: SQL Fundamentals I
  • Questions and Answers
  • 292 Q&As
  • Last Updated
  • Jan 15,2018
  • List Price
  • $128.99
  • Price
  • Today 49.99 USD

Free TrialVersion: demo Buy Now 50% OFF

Key benefits of 1z0 051 dumps pdf

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

Q11. - (Topic 2) 

Which SQL statement would you use to remove a view called EMP_DEPT_VU from your schema? 

A. DROP emp_dept_vu; 

B. DELETE emp_dept_vu; 

C. REMOVE emp_dept_vu; 

D. DROP VIEW emp_dept_vu; 

E. DELETE VIEW emp_dept_vu; 

F. REMOVE VIEW emp_dept_vu; 

Answer:

Explanation: 

DROP VIEW viewname; 

Incorrect Answer: ANot a valid drop view statement BNot a valid drop view statement CNot a valid drop view statement ENot a valid drop view statement FNot a valid drop view statement 

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-20 


Q12. - (Topic 2) 

View the Exhibit and examine the structure of the PRODUCTS tables. 

You want to generate a report that displays the average list price of product categories where the average list price is less than half the maximum in each category. 

Which query would give the correct output? 

A. 

SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) < ALL (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category); 

B. 

SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) > ANY (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category); 

C. 

SELECT prod_category,avg(prod_list_price) FROM products HAVING avg(prod_list_price) < ALL (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category); 

D. 

SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) > ANY (SELECT max(prod_list_price)/2 FROM products); 

Answer:

Explanation: 

Using the ANY Operator in Multiple-Row Subqueries 

The ANY operator (and its synonym, the SOME operator) compares a value to each value 

returned by a subquery. 

<ANY means less than the maximum. 

>ANY means more than the minimum. 

=ANY is equivalent to IN 

Using the ALL Operator in Multiple-Row Subqueries 

The ALL operator compares a value to every value returned by a subquery. 

>ALL means more than the maximum and 

<ALL means less than the minimum. 

The NOT operator can be used with IN, ANY, and ALL operators. 


Q13. - (Topic 1) 

Examine these statements: 

CREATE ROLE registrar; 

GRANT UPDATE ON student_grades TO registrar; 

GRANT registrar to user1, user2, user3; 

What does this set of SQL statements do? 

A. The set of statements contains an error and does not work. 

B. It creates a role called REGISTRAR, adds the MODIFY privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users. 

C. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users. 

D. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and creates three users with the role. 

E. It creates a role called REGISTRAR, adds the UPDATE privilege on three users, and gives the REGISTRAR role to the STUDENT_GRADES object. 

F. It creates a role called STUDENT_GRADES, adds the UPDATE privilege on three users, and gives the UPDATE role to the registrar. 

Answer:

Explanation: the statement will create a role call REGISTRAR, grant UPDATE on student_grades to registrar, grant the role to user1,user2 and user3. 

Incorrect Answer: Athe statement does not contain error Bthere is no MODIFY privilege Dstatement does not create 3 users with the role Eprivilege is grant to role then grant to user Fprivilege is grant to role then grant to user 


Q14. - (Topic 2) 

You are granted the CREATE VIEW privilege. What does this allow you to do? 

A. Create a table view. 

B. Create a view in any schema. 

C. Create a view in your schema. 

D. Create a sequence view in any schema. 

E. Create a view that is accessible by everyone. 

F. Create a view only of it is based on tables that you created. 

Answer:

Explanation: 

You can create a view in your own schema only if you are granted the CREATE VIEW 

privilege. 

Incorrect Answers 

A:You can create a view in your own schema only. 

B:You can create a view in your own schema only, not in any schema. 

D:There is no sequence view in Oracle. 

E:You cannot create a view that is accessible by everyone. You will need specially grant 

SELECT privileges on this view for everyone. 

F:You can create a view in your own schema, but not only for tables in your schema. You 

can use object from other users schemas if you have privileges to retrieve data from them. 

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

Chapter 7: Creating Other Database Objects in Oracle 


Q15. - (Topic 1) 

You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit: 

You want to create a SALE_PROD view by executing the following SQL statements: 

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

A. The view will be created and you can perform DLM operations on the view 

B. The view will not be created because the join statements are not allowed for creating a view 

C. The view will not be created because the GROUP BY clause is not allowed for creating a view 

D. The view will be created but no DML operations will be allowed on the view 

Answer:

Explanation: 

Rules for Performing DML Operations on a View You cannot add data through a view if the view includes: Group functions A GROUP BY clause The DISTINCT keyword The pseudocolumn ROWNUM keyword Columns defined by expressions NOT NULL columns in the base tables that are not selected by the view 


Q16. - (Topic 2) 

Evaluate the following SQL statement: 

SQL> SELECT cust_id, cust_last_name FROM customers WHERE cust_credit_limit IN (select cust_credit_limit FROM customers WHERE cust_city ='Singapore'); 

Which statement is true regarding the above query if one of the values generated by the subquery is NULL? 

A. It produces an error. 

B. It executes but returns no rows. 

C. It generates output for NULL as well as the other values produced by the subquery. 

D. It ignores the NULL value and generates output for the other values produced by the subquery. 

Answer:


Q17. - (Topic 1) 

Which two statements are true regarding working with dates? (Choose two.) 

A. The default internal storage of dates is in the numeric format 

B. The RR date format automatically calculates the century from the SYSDATE function but allows the user to enter the century if required 

C. The default internal storage of dates is in the character format 

D. The RR date format automatically calculates the century from the SYSDATE function and does not allow the user to enter the century 

Answer: A,B 

Explanation: 

Working with Dates The Oracle Database stores dates in an internal numeric format, representing the century, year, month, day, hours, minutes, and seconds. The default display and input format for any date is DD-MON-RR. RR Date Format The RR date format is similar to the YY element, but you can use it to specify different centuries. Use the RR date format element instead of YY so that the century of the return value varies according to the specified two digit year and the last two digits of the current year. The table in the slide summarizes the behavior of the RR element. 

untitled Note the values shown in the last two rows of the above table. As we approach the middle of the century, then the RR behavior is probably not what you want. This data is stored internally as follows: CENTURY YEAR MONTH DAY HOUR MINUTE SECOND 19 87 06 17 17 10 43 


Q18. - (Topic 1) 

Examine the structure of the INVOICE table: Exhibit: 

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

A. SELECT inv_no,NVL2(inv_date,'Pending','Incomplete') FROM invoice; 

B. SELECT inv_no,NVL2(inv_amt,inv_date,'Not Available') FROM invoice; 

C. SELECT inv_no,NVL2(inv_date,sysdate-inv_date,sysdate) FROM invoice; 

D. SELECT inv_no,NVL2(inv_amt,inv_amt*.25,'Not Available') FROM invoice; 

Answer: A,C 

Explanation: 

The NVL2 Function 

The NVL2 function provides an enhancement to NVL but serves a very similar purpose. It evaluates whether a column or expression of any data type is null or not. 5-6 The NVL function\\ If the first term is not null, the second parameter is returned, else the third parameter is returned. Recall that the NVL function is different since it returns the original term if it is not null. The NVL2 function takes three mandatory parameters. Its syntax is NVL2(original, ifnotnull, ifnull), where original represents the term being tested. Ifnotnull is returned if original is not null, and ifnull is returned if original is null. The data types of the ifnotnull and ifnull parameters must be compatible, and they cannot be of type LONG. They must either be of the same type, or it must be possible to convert ifnull to the type of the ifnotnull parameter. The data type returned by the NVL2 function is the same as that of the ifnotnull parameter. 


Q19. - (Topic 2) 

The STUDENT_GRADES table has these columns: 

STUDENT_ID NUMBER(12) 

SEMESTER_END DATE 

GPA NUMBER(4,3) 

The registrar requested a report listing the students' grade point averages (GPA) sorted from highest grade point average to lowest. 

Which statement produces a report that displays the student ID and GPA in the sorted order requested by the registrar? 

A. SELECT student_id, gpa FROM student_grades ORDER BY gpa ASC; 

B. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa ASC; 

C. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa; 

D. SELECT student_id, gpa FROM student_grades ORDER BY gpa; 

E. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa DESC; 

F. SELECT student_id, gpa FROM student_grades ORDER BY gpa DESC; 

Answer:

Explanation: 

sorted by highest to lowest is DESCENDING order 

Incorrect Answer: Aresult in ascending order Bwrong syntax with SORT keyword Cwrong syntax with SORT keyword Ddefault value for ORDER by is in ascending order Ewrong syntax with SORT keyword 

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


Q20. - (Topic 1) 

Examine the description of the EMP_DETAILS table given below: Exhibit: 

Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL table? (Choose two.) 

A. An EMP_IMAGE column can be included in the GROUP BY clause 

B. You cannot add a new column to the table with LONG as the data type 

C. An EMP_IMAGE column cannot be included in the ORDER BY clause 

D. You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column 

Answer: B,C 

Explanation: 

LONG Character data in the database character set, up to 2GB. All the functionality of LONG (and more) is provided by CLOB; LONGs should not be used in a modern database, and if your database has any columns of this type they should be converted to CLOB. 

There can only be one LONG column in a table. 

Guidelines 

A LONG column is not copied when a table is created using a subquery. 

A LONG column cannot be included in a GROUP BY or an ORDER BY clause. 

Only one LONG column can be used per table. 

No constraints can be defined on a LONG column. 

You might want to use a CLOB column rather than a LONG column. 


To know more about the 1Z0-051, click here.

Tagged as : Oracle 1Z0-051 Dumps, Download 1Z0-051 pdf, 1Z0-051 VCE, 1Z0-051 pass4sure, examcollection 1Z0-051