7 03 2014
2014 Latest 100% Pass Guaranteed Oracle 1Z0-061 Practice Tests (51-60)
QUESTION 51
You issue the following command to alter the country column in the departments table:
Which statement is true?
A. It produces an error because column definitions cannot be altered to add default values.
B. It executes successfully and all the rows that have a null value for the country column will be updated
with the value ‘USA’.
C. It executes successfully. The modification to add the default value takes effect only from subsequent
insertions to the table.
D. It produces an error because the data type for the column is not specified.
Answer: B
QUESTION 52
Which two statements are true regarding constraints?
A. A foreign key cannot contain null values.
B. A column with the unique constraint can contain null values.
C. A constraint is enforced only for the insert operation on a table.
D. A constraint can be disabled even if the constraint column contains data.
E. All constraints can be defined at the column level as well as the table level.
Answer: BD
QUESTION 53
Guaranteed success with TestInsides practice guides 71 Oracle 1z0-061 : Practice Test
Which statement adds a column called salary to the employees table having 100 rows, which cannot contain null?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: A
Explanation:
http://www.comp.nus.edu.sg/~ooibc/courses/sql/ddl_table.htm (see changing table structures)
QUESTION 54
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: BD
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:
QUESTION 55
Which two statements are true regarding the count function?
A. The count function can be used only for CHAR, VARCHAR2, and NUMBER data types.
B. Count (*) returns the number of rows including duplicate rows and rows containing null value in any
of the columns.
C. Count (cust_id) returns the number of rows including rows with duplicate customer IDs and NULL
value in the CUST_ID column.
D. Count (distinct inv_amt) returns the number of rows excluding rows containing duplicates and NULL
values in the INV_AMT column.
E. A select statement using the COUNT function with a DISTINCT keyword cannot have a where clause.
Answer: BD
Explanation:
Using the COUNT Function
The COUNT function has three formats:
COUNT(*)
COUNT(expr)
COUNT(DISTINCT expr)
COUNT(*) returns the number of rows in a table that satisfy the criteria of the SELECT statement, including duplicate rows and rows containing null values in any of the columns. If a WHERE clause is included in the SELECT statement, COUNT(*) returns the number of rows that satisfy the condition in the WHERE clause.
In contrast,
COUNT(expr) returns the number of non-null values that are in the column identified by expr.
COUNT(DISTINCT expr) returns the number of unique, non-null values that are in the column identified by expr.
QUESTION 56
Evaluate the following SQL statement:
Which statement is true regarding the outcome of the above query?
A. It executes successfully and displays rows in the descending order of PROMO_CATEGORY.
B. It produces an error because positional notation cannot be used in the order by clause with set operators.
C. It executes successfully but ignores the order by clause because it is not located at the end of the
compound statement.
D. It produces an error because the order by clause should appear only at the end of a compound query-that
is, with the last select statement.
Answer: D
QUESTION 57
View the Exhibit and examine the structure of the product, component, and PDT_COMP tables.
In product table, PDTNO is the primary key.
In component table, COMPNO is the primary key.
In PDT_COMP table, <PDTNO, COMPNO) is the primary key, PDTNO is the foreign key referencing PDTNO in product table and COMPNO is the foreign key referencing the COMPNO in component table.
You want to generate a report listing the product names and their corresponding component names, if the component names and product names exist.
Evaluate the following query:
SQL>SELECT pdtno, pdtname, compno, compname
FROM product _____________ pdt_comp
USING (pdtno) ____________ component USING (compno)
WHERE compname IS NOT NULL;
Which combination of joins used in the blanks in the above query gives the correct output?
A. JOIN; JOIN
B. FULL OUTER JOIN; FULL OUTER JOIN
C. RIGHT OUTER JOIN; LEFT OUTER JOIN
D. LEFT OUTER JOIN; RIGHT OUTER JOIN
Answer: C
QUESTION 58
View the Exhibit for the structure of the student and faculty tables.
You need to display the faculty name followed by the number of students handled by the faculty at the base location.
Examine the following two SQL statements:
Which statement is true regarding the outcome?
A. Only statement 1 executes successfully and gives the required result.
B. Only statement 2 executes successfully and gives the required result.
C. Both statements 1 and 2 execute successfully and give different results.
D. Both statements 1 and 2 execute successfully and give the same required result.
Answer: D
QUESTION 59
View the Exhibits and examine products and sales tables.
You issue the following query to display product name and the number of times the product has been sold:
What happens when the above statement is executed?
A. The statement executes successfully and produces the required output.
B. The statement produces an error because item_cnt cannot be displayed in the outer query.
C. The statement produces an error because a subquery in the from clause and outer-joins cannot
be used together.
D. The statement produces an error because the group by clause cannot be used in a subquery in
the from clause.
Answer: A
QUESTION 60
You want to create a table employees in which the values of columns EMPLOYEES_ID and LOGIN_ID must be unique and not null. Which two SQL statements would create the required table?
A. Option A
B. Option B
C. Option C
D. Option D
E. Option E
F. Option F
Answer: DE
…go to http://www.lead2pass.com/1z0-061.html to download the full version Q&As.
2014 Latest 100% Pass Guaranteed Oracle 1Z0-061 Practice Tests (41-50) Passed CIW 1D0-435 Exam with Pass4sure and Lead2pass PDF & VCE Part: A (1-10)
Comments are currently closed.