Cause all that matters here is passing the Oracle 1Z0-051 exam. Cause all that you need is a high score of 1Z0-051 Oracle Database: SQL Fundamentals I exam. The only one thing you need to do is downloading Exambible 1Z0-051 exam study guides now. We will not let you down with our money-back guarantee.
2021 Nov pdf 1z0-051:
Q91. - (Topic 1)
View the Exhibit and examine the data in the COSTS table.
You need to generate a report that displays the IDs of all products in the COSTS table whose unit price is at least 25% more than the unit cost. The details should be displayed in the descending order of 25% of the unit cost. You issue the following query:
Which statement is true regarding the above query?
A. It executes and produces the required result.
B. It produces an error because an expression cannot be used in the ORDER BY clause.
C. It produces an error because the DESC option cannot be used with an expression in the ORDER BY clause.
D. It produces an error because the expression in the ORDER BY clause should also be specified in the SELECT clause.
Answer: A
Q92. - (Topic 2)
View the Exhibit; e xamine the structure of the PROMOTIONS table.
Each promotion has a duration of at least seven days.
Your manager has asked you to generate a report, which provides the weekly cost for each promotion done to l date.
Which query would achieve the required result?
A. SELECT promo_name, promo_cost/promo_end_date-promo_begin_date/7 FROM promotions;
B. SELECT promo_name,(promo_cost/promo_end_date-promo_begin_date)/7 FROM promotions;
C. SELECT promo_name, promo_cost/(promo_end_date-promo_begin_date/7) FROM promotions;
D. SELECT promo_name, promo_cost/((promo_end_date-promo_begin_date)/7) FROM promotions;
Answer: D
Q93. - (Topic 2)
SLS is a private synonym for the SH.SALES table.
The user SH issues the following command:
DROP SYNONYM sls;
Which statement is true regarding the above SQL statement?
A. Only the synonym would be dropped.
B. The synonym would be dropped and the corresponding table would become invalid.
C. The synonym would be dropped and the packages referring to the synonym would be dropped.
D. The synonym would be dropped and any PUBLIC synonym with the same name becomes invalid.
Answer: A
Explanation:
A synonym is an alias for a table (or a view). Users can execute SQL statements against the synonym, and the database will map them into statements against the object to which the synonym points.
Private synonyms are schema objects. Either they must be in your own schema, or they must be qualified with the schema name. Public synonyms exist independently of a schema. A public synonym can be referred to by any user to whom permission has been granted to see it without the need to qualify it with a schema name.
Private synonyms must be a unique name within their schema. Public synonyms can have the same name as schema objects. When executing statements that address objects without a schema qualifier, Oracle will first look for the object in the local schema, and only if it cannot be found will it look for a public synonym.
Q94. - (Topic 1)
The SQL statements executed in a user session as follows: Exhibit:
Which two statements describe the consequence of issuing the ROLLBACK TO SAVE POINT a command in the session? (Choose two.)
A. Both the DELETE statements and the UPDATE statement are rolled back
B. The rollback generates an error
C. Only the DELETE statements are rolled back
D. Only the seconds DELETE statement is rolled back
E. No SQL statements are rolled back
Answer: B,E
Q95. - (Topic 2)
What is true of using group functions on columns that contain NULL values?
A. Group functions on columns ignore NULL values.
B. Group functions on columns returning dates include NULL values.
C. Group functions on columns returning numbers include NULL values.
D. Group functions on columns cannot be accurately used on columns that contain NULL values.
E. Group functions on columns include NULL values in calculations if you use the keyword INC_NULLS.
Answer: A
Explanation: group functions on column ignore NULL values
Incorrect Answer: Bgroup functions on column ignore NULL values Cgroup functions on column ignore NULL values DNVL function can be use for column with NULL values Eno such INC_NULLS keyword
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 5-12
Leading 1z0-051 dumps latest:
Q96. - (Topic 1)
Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER NOT NULL, Primary Key EMP_NAME VARCHAR2(30) JOB_ID NUMBER SAL NUMBER MGR_ID NUMBER References EMPLOYEE_ID column DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column of
theDEPARTMENTS table
You created a sequence called EMP_ID_SEQ in order to populate sequential values for the EMPLOYEE_ID column of the EMPLOYEES table.
Which two statements regarding the EMP_ID_SEQ sequence are true? (Choose two.)
A. You cannot use the EMP_ID_SEQ sequence to populate the JOB_ID column.
B. The EMP_ID_SEQ sequence is invalidated when you modify the EMPLOYEE_ID column.
C. The EMP_ID_SEQ sequence is not affected by modifications to the EMPLOYEES table.
D. Any other column of NUMBER data type in your schema can use the EMP_ID_SEQ sequence.
E. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEES table.
F. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEE_ID column.
Answer: C,D
Explanation: the EMP_ID_SEQ sequence is not affected by modification to the
EMPLOYEES table. Any other column of NUMBER data type in your schema can use the
EMP_ID_SEQ sequence.
Incorrect Answer:
AEMP_ID_SEQ sequence can be use to populate JOB_ID
BEMP_ID_SEQ sequence will not be invalidate when column in EMPLOYEE_ID is modify.
EEMP_ID_SEQ sequence will be dropped automatically when you drop the EMPLOYEES
table.
FEMP_ID_SEQ sequence will be dropped automatically when you drop the
EMPLOYEE_ID column.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 12-4
Q97. - (Topic 2)
View the Exhibit and examine the structure of the PRODUCTS table.
Which two tasks would require subqueries? (Choose two.)
A. Display the minimum list price for each product status.
B. Display all suppliers whose list price is less than 1000.
C. Display the number of products whose list price is more than the average list price.
D. Display the total number of products supplied by supplier 102 and have product status as 'obsolete'.
E. Display all products whose minimum list price is more than the average list price of products and have the status 'orderable'.
Answer: C,E
Q98. - (Topic 2)
What is true about the WITH GRANT OPTION clause?
A. It allows a grantee DBA privileges.
B. It is required syntax for object privileges.
C. It allows privileges on specified columns of tables.
D. It is used to grant an object privilege on a foreign key column.
E. It allows the grantee to grant object privileges to other users and roles.
Answer: E Explanation:
The GRANT command with the WITH GRANT OPTION clause allows the grantee to grant
object privileges to other users and roles.
Incorrect Answers
A:The WITH GRANT OPTION does not allow a grantee DBA privileges.
B:It is not required syntax for object privileges. It is optional clause of GRANT command.
C:GRANT command does not allows privileges on columns of tables.
D:It is not used to grant an object privilege on a foreign key column.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 356-365
Chapter 8: User Access in Oracle
Q99. - (Topic 1)
You work as a database administrator at ABC.com. You study the exhibit carefully.
Exhibit:
You issue the following SQL statement:
Which statement is true regarding the execution of the above query?
A. It produces an error because the AMT_SPENT column contains a null value.
B. It displays a bonus of 1000 for all customers whose AMT_SPENT is less than CREDIT_LIMIT.
C. It displays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT, or AMT_SPENT is null.
D. It produces an error because the TO_NUMBER function must be used to convert the result of the NULLIF function before it can be used by the NVL2 function.
Answer: C
Explanation:
The NULLIF Function The NULLIF function tests two terms for equality. If they are equal the function returns a null, else it returns the first of the two terms tested. The NULLIF function takes two mandatory parameters of any data type. The syntax is NULLIF(ifunequal, comparison_term), where the parameters ifunequal and comparison_term are compared. If they are identical, then NULL is returned. If they differ, the ifunequal parameter is returned.
Q100. - (Topic 2)
Examine the structure proposed for the TRANSACTIONS table:
Which statements are true regarding the creation and storage of data in the above table structure? (Choose all that apply.)
A. The CUST_STATUS column would give an error.
B. The TRANS_VALIDITY column would give an error.
C. The CUST_STATUS column would store exactly one character.
D. The CUST_CREDIT_LIMIT column would not be able to store decimal values.
E. The TRANS_VALIDITY column would have a maximum size of one character.
F. The TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds, and fractions of seconds.
Answer: B,C
Explanation:
VARCHAR2(size)Variable-length character data (A maximum size must be specified:
minimum size is 1; maximum size is 4,000.)
CHAR [(size)] Fixed-length character data of length size bytes (Default and minimum size
is 1; maximum size is 2,000.)
NUMBER [(p,s)] Number having precision p and scale s (Precision is the total number of
decimal digits and scale is the number of digits to the right of the decimal point; precision
can range from 1 to 38, and scale can range from –84 to 127.)
DATE Date and time values to the nearest second between January 1, 4712 B.C., and
December 31, 9999 A.D.