1z0-061 the Most Up to Date VCE And PDF Instant Download

Tens of thousands of competitors, pages of hard questions and unsatisfied exam preparation situations… Do not worried about all those annoying things! We, help you with your Oracle Database May 15,2022 Newest 1z0-061 QAs Oracle Database 12c: SQL Fundamentals exam. We will assist you clear the Hotest 1z0-061 free download exam with Oracle Database 1z0-061 real exam questions. We 1z0-061 dumps are the most comprehensive ones.

We Geekcert has our own expert team. They selected and published the latest 1z0-061 preparation materials from Official Exam-Center.

The following are the 1z0-061 free dumps. Go through and check the validity and accuracy of our 1z0-061 dumps.1z0-061 free dumps are questions from the latest full 1z0-061 dumps. Check 1z0-061 free questions to get a better understanding of 1z0-061 exams.

Question 1:

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

Correct Answer: AC


Question 2:

View the Exhibit and examine the structure of the CUSTOMERS table.

You have been asked to produce a report on the customers table showing the customers details sorted in descending order of the city and in the descending order of their income level in each city. Which query would accomplish this task?

A. Option A

B. Option B

C. Option C

D. Option D

Correct Answer: A


Question 3:

Which two statements are true regarding subqueries?

A. A subquery can retrieve zero or more rows.

B. Only two subqueries can be placed at one level.

C. A subquery can be used only in SQL query statements.

D. A subquery can appear on either side of a comparison operator.

E. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.

Correct Answer: AD

Using a Subquery to Solve a Problem

Suppose you want to write a query to find out who earns a salary greater than Abel\’s salary.

To solve this problem, you need two queries: one to find how much Abel earns, and a second query to find who earns more than that amount. You can solve this problem by combining the two queries, placing one query inside the other query.

The inner query (or subquery) returns a value that is used by the outer query (or main query).

Using a subquery is equivalent to performing two sequential queries and using the result of the first query as the search value in the second query.

Subquery Syntax

A subquery is a SELECT statement that is embedded in the clause of another SELECT statement. You can build powerful statements out of simple ones by using subqueries. They can be very useful when you need to select rows from a

table with a condition that depends on the data in the table itself.

You can place the subquery in a number of SQL clauses, including the following:

WHERE clause

HAVING clause

FROM clause

In the syntax:

operator includes a comparison condition such as >, =, or IN Note: Comparison conditions fall into two classes: single-row operators (>, =, >=, <, , <=) and multiple-row operators (IN, ANY, ALL, EXISTS). The subquery is often referred to

as a nested SELECT, sub-SELECT, or inner SELECT statement. The subquery generally executes first, and its output is used to complete the query condition for the main (or outer) query.

Guidelines for Using Subqueries

Enclose subqueries in parentheses. Place subqueries on the right side of the comparison condition for readability. (However, the subquery can appear on either side of the comparison operator.) Use single-row operators with single-row

subqueries and multiple- row operators with multiple-row subqueries.

Subqueries can be nested to an unlimited depth in a FROM clause but to “only” 255 levels in a WHERE clause. They can be used in the SELECT list and in the FROM, WHERE, and HAVING clauses of a query.


Question 4:

View the Exhibit and examine the data in the PROMO_CATEGORY and PROMO_COST columns of the PROMOTIONS table.

Evaluate the following two queries:

SQL>SELECT DISTINCT promo_category to_char(promo_cost)”code” FROM promotions

ORDER BY code;

SQL>SELECT DISTINCT promo_category promo_cost “code”

FROM promotions ORDER BY 1;

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

A. Only the first query executes successfully.

B. Only the second query executes successfully.

C. Both queries execute successfully but give different results.

D. Both queries execute successfully and give the same result.

Correct Answer: B


Question 5:

Examine the structure proposed for the transactions table:

Which two statements are true regarding the creation and storage of data in the above table structure?

A. The CUST_STATUS column would give an error.

B. The TRANS_VALIDITY column would give an error.

C. The CUST_STATUS column would store exactly one character.

D. The CUST_CREDIT_LIMIT column would not be able to store decimal values.

E. The TRANS_VALIDITY column would have a maximum size of one character.

F. The TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds, and fractions of seconds

Correct Answer: BC

VARCHAR2(size)Variable-length character data (A maximum size must be specified:

minimum size is 1; maximum size is 4, 000.)

CHAR [(size)] Fixed-length character data of length size bytes (Default and minimum size is 1; maximum size is 2, 000.)

NUMBER [(p, s)] Number having precision p and scale s (Precision is the total number of decimal digits and scale is the number of digits to the right of the decimal point; precision can range from 1 to 38, and scale can range from ?4 to 127.)

DATE Date and time values to the nearest second between January 1, 4712 B.C., and December 31, 9999 A.D.


Question 6:

There is a simple view SCOTT.DEPT_VIEW on the table SCOTT.DEPT.

This insert fails with an error:

SQL> insert into dept_view values(\’SUPPORT\’,\’OXFORD\’); insert into dept_view values(\’SUPPORT\’,\’OXFORD\’)

*

ERROR at line 1:

ORA-01400: cannot insert NULL into (“SCOTT”.”DEPT”.”DEPTNO”)

What might be the problem?

A. The INSERT violates a constraint on the detail table.

B. The INSERT violates a constraint on the view.

C. The view was created as WITH READ ONLY.

D. The view was created as WITH CHECK OPTION.

Correct Answer: A

B is incorrect because constraints are enforced on detail tables, not on views. C and D are incorrect because the error message would be different.


Question 7:

You need to create a table for a banking application. One of the columns in the table has the following requirements:

1) You want a column in the table to store the duration of the credit period.

2) The data in the column should be stored in a format such that it can be easily added and subtracted with date data type without using conversion functions.

3) The maximum period of the credit provision in the application is 30 days.

4) The interest has to be calculated for the number of days an individual has taken a credit for.

Which data type would you use for such a column in the table?

A. DATE

B. NUMBER

C. TIMESTAMP

D. INTERVAL DAY TO SECOND

E. INTERVAL YEAR TO MONTH

Correct Answer: D

INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. This data type is useful for representing the precise difference between two datetime values.

You can perform a number of arithmetic operations on date (DATE), timestamp (TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE) and interval (INTERVAL DAY TO SECOND and INTERVAL YEAR

TO MONTH) data.

?It stores duration of the credit as days

?The format stored is numeric format, and you know that numeric values can be easily added and subtracted with date data type without using conversion functions (i.e. SELECT SYSDATE ?1 FROM DUAL;)

?The interest has to be calculated for the number of days an individual has taken a credit for, so it will be easy to calculate interest by using the interest rate and duration of the the credit which is numeric format.

References:


Question 8:

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

Correct Answer: B


Question 9:

View the Exhibit and examine the data in the employees table:

You want to display all the employee names and their corresponding manager names. Evaluate the following query:

Which join option can be used in the blank in the above query to get the required output?

A. INNER JOIN

B. FULL OUTER JOIN

C. LEFT OUTER JOIN

D. RIGHT OUTER JOIN

Correct Answer: C


Question 10:

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.

Correct Answer: B

Not that employees is used both in the first line (UPDATE employees) and later (FROM employees, departments). This would not cause the correct output. Instead aliases should be use.

The following would be the correct query:

UPDATE employees a

SET department_id =

(SELECT department_id

FROM departments

WHERE location_id = \’2100\’),

(salary, commission_pct) =

(SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct) FROM employees b

WHERE a.department_id = b.department_id)

WHERE department_id IN

(SELECT department_id

FROM departments

WHERE location_id = 2900

OR location_id = 2700);

References:


Question 11:

You want to create a sales table with the following column specifications and data types: SALESID: Number STOREID: Number ITEMID: Number QTY: Number, should be set to 1 when no value is specified SLSDATE: Date, should be set to current date when no value is specified PAYMENT: Characters up to 30 characters, should be set to CASH when no value is specified Which statement would create the table?

A. Option A

B. Option B

C. Option C

D. Option D

Correct Answer: B

To specify the default value of payment field you must use DEFAULT \’CASH\’.

References:


Question 12:

You issue the following command to drop the products table:

SQL> DROP TABLE products;

Which three statements are true about the implication of this command? (Choose three.)

A. All data along with the table structure is deleted.

B. A pending transaction in the session is committed.

C. All indexes on the table remain but they are invalidated.

D. All views and synonyms remain but they are invalidated.

E. All data in the table is deleted but the table structure remains.

Correct Answer: ABD

A: The DROP TABLE statement moves a table or object table to the recycle bin.

B: If a user issues a DDL (CREATE, ALTER, or DROP) or DCL (GRANT or REVOKE) command, the transaction in progress (if any) will

Incorrect:

Not C: Dropping a table invalidates dependent objects, such as indexes and constraints.

References:


Question 13:

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

Correct Answer: BD

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 14:

View the Exhibit and examine the structure of the promotions table.

Evaluate the following SQL statement:

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

A. It shows COST_REMARK for all the promos in the table.

B. It produces an error because the SUBQUERY gives an error.

C. It shows COST_REMARK for all the promos in the promo category \’TV\’

D. It produces an error because SUBQUERIES cannot be used with the case expression.

Correct Answer: A


Question 15:

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

Correct Answer: C

A column alias:

-Renames a column heading

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


Leave a Reply

Your email address will not be published. Required fields are marked *