[2021-New] Oracle 1Z0-061 Dumps With Update Exam Questions (1-10)

1Z0-061 Dumps

1Z0-061 Exam Questions - Online Test

1Z0-061 Premium VCE File

Learn More 100% Pass Guarantee - Dumps Verified - Instant Download
150 Lectures, 20 Hours

Q1. 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: B,D

Q2. 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: D,E

Q3. 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

Q4. You want to display the date for the first Monday of the next month and issue the following command:

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.

Answer: A

Q5. 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:

Q6. View the Exhibit and examine the structure of the SALES table.

The following query is written to retrieve all those product IDs from the SALES table that have more than 55000 sold and have been ordered more than 10 times.

Which statement is true regarding this SQL statement?

A. It executes successfully and generates the required result.

B. It produces an error because count(*) should be specified in the SELECT clause also.

C. It produces an error because count{*) should be only in the HAVING clause and not in the WHERE clause.

D. It executes successfully but produces no result because COUNT (prod_id) should be used instead of COUNT (*).

Answer: C

Explanation:

Restricting Group Results with the HAVING Clause

You use the HAVING clause to specify the groups that are to be displayed, thus further

restricting the groups on the basis of aggregate information.

In the syntax, group_condition restricts the groups of rows returned to those groups for

which the specified condition is true.

The Oracle server performs the following steps when you use the HAVING clause:

1. Rows are grouped.

2. The group function is applied to the group.

3. The groups that match the criteria in the HAVING clause are displayed.

The HAVING clause can precede the GROUP BY clause, but it is recommended that you

place the GROUP BY clause first because it is more logical. Groups are formed and group

functions are calculated before the HAVING clause is applied to the groups in the SELECT

list.

Note: The WHERE clause restricts rows, whereas the HAVING clause restricts groups.

Q7. View the Exhibit and evaluate the structure and data in the CUST_STATUS table. You issue the following SQL statement:

Which statement is true regarding the execution of the above query?

A. It produces an error because the AMT_SPENT column contains a null value.

B. It displays a bonus of 1000 for all customers whose AMT_SPENT is less than CREDIT_LIMIT.

C. It displays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT, or AMT_SPENT is null.

D. It produces an error because the TO_NUMBER function must be used to convert the result of the NULLIF function before it can be used by the NVL2 function.

Answer: C

Explanation:

The NULLIF Function The NULLIF function tests two terms for equality. If they are equal the function returns a null, else it returns the first of the two terms tested. The NULLIF function takes two mandatory parameters of any data type. The syntax is NULLIF(ifunequal, comparison_term), where the parameters ifunequal and comparison_term are compared. If they are identical, then NULL is returned. If they differ, the ifunequal parameter is returned.

Q8. View the Exhibits and examine the structures of the products, sales, and customers tables.

You need to generate a report that gives details of the customer's last name, name of the product, and the quantity sold for a customers in 'Tokyo'.

Which two queries give the required result?

A. Option A

B. Option B

C. Option C

D. Option D

Answer: A,C

Q9. Examine the structure of the sales table:

Evaluate the following create table statement:

Which two statements are true about the creation of the SALES1 table?

A. The SALES1 table is created with no rows but only a structure.

B. The SALES1 table would have primary key and unique constraints on the specified columns.

C. The SALES1 table would not be created because of the invalid where clause.

D. The SALES1 table would have not null and unique constraints on the specified columns.

E. The SALES1 table would not be created because column-specified names in the select and create table clauses do not match,

Answer: A

Q10. View the Exhibit and examine the data in the promotions table.

PROMO_BEGIN_DATE is stored in the default date format, dd-mon-rr.

You need to produce a report that provides the name, cost, and start date of all promos in the post category that were launched before January 1, 2000.

Which SQL statement would you use?

A. Option A

B. Option B

C. Option C

D. Option D

Answer: D