Exam Code: oracle 1z0 061 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Oracle Database 12c SQL Fundamentals
Certification Provider: Oracle
Free Today! Guaranteed Training- Pass 1z0 061 practice test Exam.
Q17. View the Exhibit and examine the data in the products table.
You need to display product names from the products table that belong to the 'software/other' category with minimum prices as either S2000 or S4000 and no unit of measure.
You issue the following query:
Which statement is true regarding the above query?
A. It executes successfully but returns no result.
B. It executes successfully and returns the required result.
C. It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.
D. It generates an error because the condition specified for the prod category column is not valid.
Answer: A
Q18. YOU need to display the date ll-oct-2007 in words as ‘Eleventh of October, Two Thousand Seven'.
Which SQL statement would give the required result?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: A
Q19. Examine the structure of the employees table.
You want to display the maximum and minimum salaries of employees hired 1 year ago. Which two statements would get the correct output?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: B,D
Explanation:
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z1 0.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_sql_subselectexamples.htm.
Q20. View the Exhibit and examine the structures of the employees and departments tables.
You want to update the employees table as follows:
-Update only those employees who work in Boston or Seattle (locations 2900 and 2700).
-Set department_id for these employees to the department_id corresponding to London (location_id 2100).
-Set the employees' salary in iocation_id 2100 to 1.1 times the average salary of their department.
-Set the employees' commission in iocation_id 2100 to 1.5 times the average commission of their department.
You issue the following command:
What is the outcome?
A. It executes successfully and gives the correct result.
B. It executes successfully but does not give the correct result.
C. It generates an error because a subquery cannot have a join condition in an update statement.
D. It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an update statement.
Answer: B
Q21. Examine the data in the CUST_NAME column of the customers table.
You need to display customers' second names where the second name starts with "Mc" or "MC."
Which query gives the required output?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: B
Q22. You need to list the employees in DEPARTMENT_ID 30 in a single row, ordered by HIRE_DATE.
Examine the sample output:
Which query will provide the required output?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: B
Reference: http://docs.oracle.com/cd/E11882_01/server.112/e10592/functions089.htm
Q23. You want to display 5 percent of the rows from the sales table for products with the lowest AMOUNT_SOLD and also want to include the rows that have the same AMOUNT_SOLD even if this causes the output to exceed 5 percent of the rows.
Which query will provide the required result?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: D
Q24. Which two statements are true regarding single row functions?
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
Answer: B,D
Explanation:
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-Manipulation Functions 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: