[PDF and VCE] Free 1z0-061 VCE and PDF, Exam Materials 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 Newest 1z0-061 QAs Oracle Database 12c: SQL Fundamentals exam. We will assist you clear the Apr 06,2022 Newest 1z0-061 free download exam with Oracle Database 1z0-061 practice tests. We 1z0-061 actual tests 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 Exhibit and examine the structure of the products table.

Using the products table, you issue the following query to generate the names, current list price, and discounted list price for all those products whose list price falls below $10 after a discount of 25% is applied on it.

The query generates an error. What is the reason for the error?

A. The parenthesis should be added to enclose the entire expression.

B. The double quotation marks should be removed from the column alias.

C. The column alias should be replaced with the expression in the where clause.

D. The column alias should be put in uppercase and enclosed within double quotation marks in the where clause.

Correct Answer: C


Question 2:

Examine the structure of the transactions table:

You want to display the date, time, and transaction amount of transactions that where done before 12 noon. The value zero should be displayed for transactions where the transaction amount has not been entered. Which query gives the required result?

A. Option A

B. Option B

C. Option C

D. Option D

Correct Answer: B


Question 3:

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

Correct Answer: BCD


Question 4:

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

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 (*).

Correct Answer: C

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.


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:

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

Examine the data in the ename and hiredate columns of the employees table:

You want to generate a list of user IDs as follows:

You issue the following query:

What is the outcome?

A. It executes successfully and gives the correct output.

B. It executes successfully but does not give the correct output.

C. It generates an error because the REPLACE function is not valid.

D. It generates an error because the SUBSTR function cannot be nested in the CONCAT function.

Correct Answer: A

REPLACE (text, search_string, replacement_string) Searches a text expression for a character string and, if found, replaces it with a specified replacement string The REPLACE Function The REPLACE function replaces all occurrences of a search item in a source string with a replacement term and returns the modified source string. If the length of the replacement term is different from that of the search item, then the lengths of the returned and source strings will be different. If the search string is not found, the source string is returned unchanged. Numeric and date literals and expressions are evaluated before being implicitly cast as characters when they occur as parameters to the REPLACE function. The REPLACE function takes three parameters, with the first two being mandatory. Its syntax is REPLACE (source string, search item, [replacement term]). If the replacement term parameter is omitted, each occurrence of the search item is removed from the source string. In other words, the search item is replaced by an empty string. . The following queries illustrate the REPLACE function with numeric and date expressions: Query 1: select replace(10000-3, \’9\’, \’85\’) from dual Query 2: select replace(sysdate, \’DEC\’, \’NOV\’) from dual


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:

Examine the types and examples of relationships that follow:

1.

One-to-one a) Teacher to students

2.

One-to-many b) Employees to Manager

3.

Many-to-one c) Person to SSN

4.

Many-to-many d) Customers to products

Which option indicates the correctly matched relationships?

A. 1-a, 2-b, 3-c, and 4-d

B. 1-c, 2-d, 3-a, and 4-b

C. 1-c, 2-a, 3-b, and 4-d

D. 1-d, 2-b, 3-a, and 4-c

Correct Answer: C


Question 11:

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.

Correct Answer: C

It does not affect old lines.


Question 12:

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

You need to generate a list of all customer last names with their credit limits from the customers table.

Those customers who do not have a credit limit should appear last in the list.

Which two queries would achieve the required result? (Choose two.)

A. Option A

B. Option B

C. Option C

D. Option D

Correct Answer: BC

If the ORDER BY clause is not used, the sort order is undefined, and the Oracle server may not fetch rows in the same order for the same query twice. Use the ORDER BY clause to display the rows in a specific order.

Note: Use the keywords NULLS FIRST or NULLS LAST to specify whether returned rows containing null values should appear first or last in the ordering sequence. ANSWER C Sorting

The default sort order is ascending:

?Numeric values are displayed with the lowest values first (for example, 1 to 999). ?Date values are displayed with the earliest value first (for example, 01-JAN-92 before 01- JAN-95).

?Character values are displayed in the alphabetical order (for example, “A” first and “Z” last).

?Null values are displayed last for ascending sequences and first for descending sequences.

-ANSWER B

?You can also sort by a column that is not in the SELECT list.


Question 14:

The customers table has the following structure:

You need to write a query that does the following tasks:

1.

Display the first name and tax amount of the customers. Tax is 5% of their credit limit.

2.

Only those customers whose income level has a value should be considered.

3.

Customers whose tax amount is null should not be considered. Which statement accomplishes all the required tasks?

A. Option A

B. Option B

C. Option C

D. Option D

Correct Answer: B


Question 15:

Which two statements are true regarding constraints? (Choose two.)

A. A table can have only one primary key and one foreign key.

B. A table can have only one primary key but multiple foreign keys.

C. Only the primary key can be defined at the column and table levels.

D. The foreign key and parent table primary key must have the same name.

E. Both primary key and foreign key constraints can be defined at both column and table levels.

Correct Answer: BE