The Oracle engineers are always looking for a way to decipher the latest 1Z0-051 on the internet questions and answers. It is possible to download the latest 1Z0-051 places on our Exambible internet site. We not necessarily only offer 1Z0-051 pdf exams but also the computer software edition. Exambible 1Z0-051 test engine creates a simulator with the atmosphere if the 1Z0-051 appears, offers a great possibility to exercise the particular Oracle test communicative atmosphere. Based on the exam preparing, it is possible to choose any kind of 1Z0-051 edition to review flexibility. Moving 1Z0-051 exam will end up simpler faster simply by researching Oracle 1Z0-051 exercise exams.
2016 Jul 1z0-051 dumps free pdf:
Q31. - (Topic 2)
Which two statements are true regarding subqueries? (Choose two.)
A. A subquery can retrieve zero or more rows.
B. Only two subqueries can be placed at one level.
C. A subquery can be used only in SQL query statements.
D. A subquery can appear on either side of a comparison operator.
E. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.
Using a Subquery to Solve a Problem Suppose you want to write a query to find out who earns a salary greater than Abel’s salary. To solve this problem, you need two queries: one to find how much Abel earns, and a second query to find who earns more than that amount. You can solve this problem by combining the two queries, placing one query inside the other query. The inner query (or subquery) returns a value that is used by the outer query (or main query). Using a subquery is equivalent to performing two sequential queries and using the result of the first query as the search value in the second query. Subquery Syntax A subquery is a SELECT statement that is embedded in the clause of another SELECT statement. You can build powerful statements out of simple ones by using subqueries. They can be very useful when you need to select rows from a table with a condition that depends on the data in the table itself. You can place the subquery in a number of SQL clauses, including the following: WHERE clause HAVING clause FROM clause In the syntax: operator includes a comparison condition such as >, =, or IN Note: Comparison conditions fall into two classes: single-row operators (>, =, >=, <, <>, <=) and multiple-row operators (IN, ANY, ALL, EXISTS). The subquery is often referred to as a nested SELECT, sub-SELECT, or inner SELECT statement. The subquery generally executes first, and its output is used to complete the query condition for the main (or outer) query. Guidelines for Using Subqueries Enclose subqueries in parentheses. Place subqueries on the right side of the comparison condition for readability. (However, the subquery can appear on either side of the comparison operator.) Use single-row operators with single-row subqueries and multiple-row operators with multiple-row subqueries.
Subqueries can be nested to an unlimited depth in a FROM clause but to “only” 255 levels in a WHERE clause. They can be used in the SELECT list and in the FROM, WHERE, and HAVING clauses of a query.
Q32. - (Topic 2)
You want to display the date for the first Monday of the next month and issue the following command:
SQL>SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),'MON'), 'dd "is the first Monday for"fmmonth rrrr') FROM DUAL;
What is the outcome?
A. It executes successfully and returns the correct result.
B. It executes successfully but does not return the correct result.
C. It generates an error because TO_CHAR should be replaced with TO_DATE.
D. It generates an error because rrrr should be replaced by rr in the format string.
E. It generates an error because fm and double quotation marks should not be used in the format string.
NEXT_DAY(date, 'char'): Finds the date of the next specified day of the week ('char') following date. The value of char may be a number representing a day or a character string.
LAST_DAY(date): Finds the date of the last day of the month that contains date The second innermost function is evaluated next. TO_CHAR('28-OCT-2009', 'fmMonth') converts the given date based on the Month format mask and returns the character string October. The fm modifier trims trailing blank spaces from the name of the month.
Q33. - (Topic 2)
View the Exhibit and examine the data in the PROJ_TASK_DETAILS table.
The PROJ_TASK_DETAILS table stores information about tasks involved in a project and the relation between them.
The BASED_ON column indicates dependencies between tasks. Some tasks do not depend on the completion of any other tasks.
You need to generate a report showing all task IDs, the corresponding task ID they are dependent on, and the name of the employee in charge of the task it depends on.
Which query would give the required result?
SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p JOIN proj_task_details d ON (p.based_on = d.task_id);
SELECT p.task_id, p.based_on, d.task_in_charge
FROM proj_task_details p LEFT OUTER JOIN proj_task_details d ON (p.based_on =
SELECT p.task_id, p.based_on, d.task_in_charge
FROM proj_task_details p FULL OUTER JOIN proj_task_details d ON (p.based_on =
SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p JOIN proj_task_details d ON (p.task_id = d.task_id);
Q34. - (Topic 2)
Examine the structure of the EMP_DEPT_VU view:
Which SQL statement produces an error?
A. SELECT *
B. SELECT department_id, SUM(salary)
GROUP BY department_id;
C. SELECT department_id, job_id, AVG(salary)
GROUP BY department_id, job_id;
D. SELECT job_id, SUM(salary)
WHERE department_id IN (10,20)
GROUP BY job_id
HAVING SUM(salary) > 20000;
E. None of the statements produce an error; all are valid.
Explanation: Explanation: None of the statements produce an error. Incorrect Answer: AStatement will not cause error BStatement will not cause error CStatement will not cause error DStatement will not cause error
Q35. - (Topic 2)
The EMPLOYEES table contains these columns:
ENAME VARCHAR2 (25)
Which SQL statement will return the ENAME, length of the ENAME, and the numeric position of the letter "a" in the ENAME column, for those employees whose ENAME ends with a the letter "n"?
A. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, 'a') FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = 'n'
B. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, ,-1,1) FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = 'n'
C. SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR(ENAME, 1, 1) = 'n'
D. SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR(ENAME, -1, 1) = 'n'
INSTR is a character function return the numeric position of a named string.
BDid not return a numeric position for ‘a’.
CDid not return a numeric position for ‘a’.
DDid not return a numeric position for ‘a’.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 3-8
Improved 1z0-051 free dumps pdf:
Q36. - (Topic 1)
Which three statements/commands would cause a transaction to end? (Choose three.)
Q37. - (Topic 1)
See the structure of the PROGRAMS table:
Which two SQL statements would execute successfully? (Choose two.)
A. SELECT NVL(ADD_MONTHS(END_DATE,1),SYSDATE) FROM programs;
B. SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE)) FROM programs;
C. SELECT NVL(MONTHS_BETWEEN(start_date,end_date),'Ongoing') FROM programs;
D. SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date)),'Ongoing') FROM programs;
Converts a null value to an actual value:
Data types that can be used are date, character, and number.
Data types must match:
NVL(job_id,'No Job Yet')
MONTHS_BETWEEN(date1, date2): Finds the number of months between date1 and date2 The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month. MONTHS_BETWEEN returns a numeric value. - answer C NVL has different datatypes -numeric and strings, which is not possible!
The data types of the original and if null parameters must always be compatible. They must either be of the same type, or it must be possible to implicitly convert if null to the type of the original parameter. The NVL function returns a value with the same data type as the original parameter.
Q38. - (Topic 1)
Which arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database? (Choose three.)
C. raising to a power
D. finding the quotient
E. finding the lowest value
Q39. - (Topic 1)
See the Exhibit and examine the structure of the PROMOSTIONS table: Exhibit:
Which SQL statements are valid? (Choose all that apply.)
A. SELECT promo_id, DECODE(NVL(promo_cost,0), promo_cost,
promo_cost * 0.25, 100) "Discount"
B. SELECT promo_id, DECODE(promo_cost, 10000,
DECODE(promo_category, 'G1', promo_cost *.25, NULL),
C. SELECT promo_id, DECODE(NULLIF(promo_cost, 10000),
NULL, promo_cost*.25, 'N/A') "Catcost"
D. SELECT promo_id, DECODE(promo_cost, >10000, 'High',
<10000, 'Low') "Range"
The DECODE Function Although its name sounds mysterious, this function is straightforward. The DECODE function implements ifthen-else conditional logic by testing its first two terms for equality and returns the third if they are equal and optionally returns another term if they are not. The DECODE function takes at least three mandatory parameters, but can take many more. The syntax of the function is DECODE(expr1,comp1, iftrue1, [comp2,iftrue2...[ compN,iftrueN]], [iffalse]).
Q40. - (Topic 2)
Examine this statement:
SELECT student_id, gpa FROM student_grades WHERE gpa > &&value;
You run the statement once, and when prompted you enter a value of 2.0. A report is produced. What happens when you run the statement a second time?
A. An error is returned.
B. You are prompted to enter a new value.
C. A report is produced that matches the first report produced.
D. You are asked whether you want a new value or if you want to run the report based on the previous value.
use the double-ampersand if you want to reuse the variable value without prompting the user each time.
Incorrect Answer: Ais not an error
B&& will not prompt user for second time D&& will not ask the user for new value
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 7-13