Top Quality of 1Z0-061 exam answers materials and preparation labs for Oracle certification for consumer, Real Success Guaranteed with Updated 1Z0-061 pdf dumps vce Materials. 100% PASS Oracle Database 12c SQL Fundamentals exam Today!

## 2021 Apr 1Z0-061 free exam questions

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

Reference: http://www.comp.nus.edu.sg/~ooibc/courses/sql/ddl_table.htm (see changing table structures)

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

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:

Q3. Which three SQL statements would display the value 1890.55 as \$1, 890.55?

A. Option A

B. Option B

C. Option C

D. Option D

E. Option E

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

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

### Most up-to-date 1Z0-061 brain dumps:

Q6. You need to produce a report where each customer's credit limit has been incremented by \$1000. In the output, the customer's last name should have the heading Name and the incremented credit limit should be labeled New credit Limit. The column headings should have only the first letter of each word in uppercase.

Which statement would accomplish this requirement?

A. Option A

B. Option B

C. Option C

D. Option D

Explanation:

A column alias:

-Is useful with calculations

-Immediately follows the column name (There can also be the optional AS keyword between the column name and the alias.)

-Requires double quotation marks if it contains spaces or special characters, or if it is case sensitive.

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.

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 Exhibit and examine the description of SALES and PROMOTIONS tables.

You want to delete rows from the sales table, where the PROMO_NAME column in the promotions table has either blowout sale of everyday low prices as values.

Which three delete statements are valid?

A. Option A

B. Option B

C. Option C

D. Option D

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

Q10. Examine the data in the PROMO_BEGIN_DATE column of the promotions table:

You want to display the number of promotions started in 1999 and 2000. Which query gives the correct output?

A. Option A

B. Option B

C. Option C

D. Option D