All the vital guides are with here that may help you prepare for your Oracle 1Z0-051 exam. We have designed our Study guides, Q&As with Detailed Explanations, Full Preparation labs to ensure a person pass your analyze on the very first try. The Pdf files are printable as well as portable. You can carry all of them with a person and review the Oracle exam questions as well as answers anytime and anyplace. If you dont pass the Oracle Oracle exam, Testking.com can offer a person full refund or even another no cost product according to your require. You should contact our buyer support as well as claim the exam dump youd like. If you claim the full income , you should email us the failed transcript. Our workers can remit you right after making sure your details.
2016 Sep exam 1z0-051:
Q181. - (Topic 2)
Which SQL statement returns a numeric value?
A. SELECT ADD_MONTHS(MAX(hire_Date), 6) FROM EMP;
B. SELECT ROUND(hire_date) FROM EMP;
C. SELECT sysdate-hire_date FROM EMP;
D. SELECT TO_NUMBER(hire_date + 7) FROM EMP;
DATE value subtract DATE value will return numeric value.
Incorrect Answer: Adoes not return numeric value Bdoes not return numeric value Ddoes not return numeric value
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 9-13
Q182. - (Topic 1)
See the Exhibit and examine the structure and data in the INVOICE table: Exhibit:
Which two SQL statements would execute successfully? (Choose two.)
A. SELECT MAX(inv_date),MIN(cust_id) FROM invoice;
B. SELECT AVG(inv_date-SYSDATE),AVG(inv_amt) FROM invoice;
C. SELECT MAX(AVG(SYSDATE-inv_date)) FROM invoice;
D. SELECT AVG(inv_date) FROM invoice;
Q183. - (Topic 2)
Evaluate this SQL statement:
SELECT ename, sal, 12*sal+100 FROM emp;
The SAL column stores the monthly salary of the employee. Which change must be made to the above syntax to calculate the annual compensation as "monthly salary plus a monthly bonus of $100, multiplied by 12"?
A. No change is required to achieve the desired results.
B. SELECT ename, sal, 12*(sal+100) FROM emp;
C. SELECT ename, sal, (12*sal)+100 FROM emp;
D. SELECT ename, sal+100,*12 FROM emp;
to achieve the result you must add 100 to sal before multiply with 12. Select ename, sal, 12*(sal+100) from EMP;
Incorrect Answer: AMultiplication and division has priority over addition and subtraction in Operator precedence. CGive wrong results DWrong syntax
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 1-11
Q184. - (Topic 2)
You need to produce a report where each customer's credit limit has been incremented by $1000.
In the output, the customer's last name should have the heading Name and the incremented credit limit should be labeled New Credit Limit.
The column headings should have only the first letter of each word in uppercase .
Which statement would accomplish this requirement?
A. SELECT cust_last_name Name, cust_credit_limit + 1000
"New Credit Limit"
B. SELECT cust_last_name AS Name, cust_credit_limit + 1000
AS New Credit Limit
C. SELECT cust_last_name AS "Name", cust_credit_limit + 1000
AS "New Credit Limit"
D. SELECT INITCAP(cust_last_name) "Name", cust_credit_limit + 1000 INITCAP("NEW
A column alias:
-Renames a column heading
-Is useful with calculations
-Immediately follows the column name (There can also be the optional AS keyword between the column name and the alias.)
-Requires double quotation marks if it contains spaces or special characters, or if it is case sensitive.
Q185. - (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;
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.
Not C: Use the formatting'$99,999.00' with the TO_CHAR function, not with the
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
Most up-to-date 1z0-047 vs 1z0-051:
Q186. - (Topic 1)
Which four are types of functions available in SQL? (Choose 4)
Explanation: Explanation: SQL have character, numeric, date, conversion function.
ASQL have character, numeric, date, conversion function.
CSQL have character, numeric, date, conversion function.
DSQL have character, numeric, date, conversion function.
FSQL have character, numeric, date, conversion function.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 3-3
Q187. - (Topic 2)
Which substitution variable would you use if you want to reuse the variable without prompting the user each time?
To reuse the variable without prompting the user each time you can use && substitution
A:This substitution variable will prompt the user each time.
B:ACCEPT is command, not substitution variable. It used to define more accurate or
specific prompt or when you want more output to display as the values are defined.
C:PROMPT is part of the ACCEPT command, it is not a variable.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 165-173
Chapter 4: Sub queries
Q188. - (Topic 1)
Which statement is true regarding synonyms?
A. Synonyms can be created only for a table
B. Synonyms are used to reference only those tables that are owned by another user
C. The DROP SYNONYM statement removes the synonym and the table on which the synonym has been created becomes invalid
D. A public synonym and a private synonym can exist with the same name for the same table
Q189. - (Topic 2)
Which are iSQL*Plus commands? (Choose all that apply.)
The only SQL*Plus command in this list : DESCRIBE. It cannot be used as SQL command. This command returns a description of tablename, including all columns in that table, the datatype for each column and an indication of whether the column permits storage of NULL values.
Incorrect Answer: A INSERT is not a SQL*PLUS command B UPDATE is not a SQL*PLUS command C SELECT is not a SQL*PLUS command E DELETE is not a SQL*PLUS command F RENAME is not a SQL*PLUS command
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 7
Q190. - (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
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