Exambible 1Z0-051 Oracle Database: SQL Fundamentals I exercise check could be the most suitable choice so that you can plan for the particular Oracle test. In order to be considered a prominent Exambible technicians, you can not pass up the particular Exambible 1Z0-051 Oracle Database: SQL Fundamentals I materials preventative measure. Learn not merely the true 1Z0-051 check questions but the comprehensive reason. Passing Oracle 1Z0-051 certification is actually the first most essential thing, moreover, one can learn numerous specialised knowledge and also experiences that will used in the future of exercise perform.
2016 Jun 1Z0-051 practice exam
Q91. - (Topic 1)
You need to extract details of those products in the SALES table where the PROD_ID column contains the string '_D123'. Which WHERE clause could be used in the
SELECT statement to get the required output?
A. WHERE prod_id LIKE '%_D123%' ESCAPE '_'
B. WHERE prod_id LIKE '%\_D123%' ESCAPE '\'
C. WHERE prod_id LIKE '%_D123%' ESCAPE '%_'
D. WHERE prod_id LIKE '%\_D123%' ESCAPE '\_'
A naturally occurring underscore character may be escaped (or treated as a regular nonspecial symbol) using the ESCAPE identifier in conjunction with an ESCAPE character. The second example in Figure 3-12 shows the SQL statement that retrieves the JOBS table records with JOB_ID values equal to SA_MAN and SA_REP and which conforms to the original requirement: select job_id from jobs where job_id like 'SA\_%' escape '\'
Q92. - (Topic 1)
View the Exhibit and examine the structure of the PROMOTIONS table. Evaluate the following SQL statement:
The above query generates an error on execution.
Which clause in the above SQL statement causes the error?
C. GROUP BY
D. ORDER BY
Q93. - (Topic 2)
Which statements are true regarding single row functions? (Choose all that apply.)
A. MOD : returns the quotient of a division
B. TRUNC : can be used with NUMBER and DATE values
C. CONCAT : can be used to combine any number of values
D. SYSDATE : returns the database server current date and time
E. INSTR : can be used to find only the first occurrence of a character in a string
F. TRIM : can be used to remove all the occurrences of a character from a string
ROUND: Rounds value to a specified decimal TRUNC: Truncates value to a specified decimal MOD: Returns remainder of division SYSDATE is a date function that returns the current database server date and time.
Date functions operate on Oracle dates. All date functions return a value of the DATE data type except MONTHS_BETWEEN, which returns a numeric value. 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. ADD_MONTHS(date, n): Adds n number of calendar months to date. The value of n must be an integer and can be negative. 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 above list is a subset of the available date functions. ROUND and TRUNC number functions can also be used to manipulate the date values as shown below: ROUND(date[,'fmt']): Returns date rounded to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is rounded to the nearest day. TRUNC(date[, 'fmt']): Returns date with the time portion of the day truncated to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is truncated to the nearest day.
The CONCAT Function
The CONCAT function joins two character literals, columns, or expressions to yield one larger character expression. Numeric and date literals are implicitly cast as characters when they occur as parameters to the CONCAT function. Numeric or date expressions are evaluated before being converted to strings ready to be concatenated. The CONCAT function takes two parameters. Its syntax is CONCAT(s1, s2), where s1 and s2 represent string literals, character column values, or expressions resulting in character values. The INSTR(source string, search item, [start position],[nth occurrence of search item]) function returns a number that represents the position in the source string, beginning from the given start position, where the nth occurrence of the search item begins: instr('http://www.domain.com','.',1,2) = 18 The TRIM function literally trims off leading or trailing (or both) character strings from a given source string:
Q94. - (Topic 2)
What is true about sequences?
A. Once created, a sequence belongs to a specific schema.
B. Once created, a sequence is linked to a specific table.
C. Once created, a sequence is automatically available to all users.
D. Only the DBA can control which sequence is used by a certain table.
E. Once created, a sequence is automatically used in all INSERT and UPDATE statements.
Q95. - (Topic 1)
See the Exhibit and examine the structure of the PROMOTIONS table: Exhibit:
Using the PROMOTIONS table, you need to find out the average cost for all promos in the
range $0-2000 and $2000-5000 in category A.
You issue the following SQL statements:
What would be the outcome?
A. It generates an error because multiple conditions cannot be specified for the WHEN clause
B. It executes successfully and gives the required result
C. It generates an error because CASE cannot be used with group functions
D. It generates an error because NULL cannot be specified as a return value
CASE Expression Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement: CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END
Improved 1z0-047 vs 1z0-051:
Q96. - (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]).
Q97. - (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.
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.
Q98. - (Topic 1)
The CUSTOMERS table has the following structure: Exhibit:
You need to write a query that does the following task:
Display the first name and tax amount of the customers. Tax is 5% of their credit limit
Only those customers whose income level has a value should be considered
Customers whose tax amount is null should not be considered
Which statement accomplishes all the required tasks?
SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers WHERE cust_income_level IS NOT NULL AND tax_amount IS NOT NULL;
SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers WHERE cust_income_level IS NOT NULL AND cust_credit_limit IS NOT NULL;
SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers WHERE cust_income_level <> NULL AND tax_amount <> NULL;
SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers WHERE (cust_income_level,tax_amount) IS NOT NULL;
Q99. - (Topic 1)
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:
Which MERGE statement is valid?
MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET
B. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||', '||e.last_name);
MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET
D. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT valueS(e.employee_id, e.first_name ||', '||e.last_name);
MERGE INTO new_employees cUSING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET
F. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||', '||e.last_name);
MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET
H. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees valueS(e.employee_id, e.first_name ||', '||e.last_name);
Explanation: Explanation: this is the correct MERGE statement syntax
Incorrect Answer: Bit should MERGE INTO table_name Cit should be WHEN MATCHED THEN Dit should MERGE INTO table_name Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-29
Q100. - (Topic 1)
Evaluate the following SQL statements: Exhibit:
You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization.
Which two statements are true regarding the above view? (Choose two.)
A. It allows you to update job IDs of the existing sales staff to any other job ID in the EMPLOYEES table
B. It allows you to delete details of the existing sales staff from the EMPLOYEES table
C. It allows you to insert rows into the EMPLOYEES table
D. It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in multitable INSERT statements