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

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

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.

Q2. You want to display 5 percent of the employees with the highest salaries in the EMPLOYEES table.

Which query will generate the required result?

A. Option A

B. Option B

C. Option C

D. Option D

Answer: D

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

Q4. YOU need to display the date ll-oct-2007 in words as ‘Eleventh of October, Two Thousand Seven'.

Which SQL statement would give the required result?

A. Option A

B. Option B

C. Option C

D. Option D

Answer: A

Q5. View the Exhibit and examine the structure of the products table.

Evaluate the following query:

What would be the outcome of executing the above SQL statement?

A. It produces an error.

B. It shows the names of all products in the table.

C. It shows the names of products whose list price is the second highest in the table.

D. It shows the names of all products whose list price is less than the maximum list price.

Answer: C

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

All products have a list price.

You issue the following command to display the total price of each product after a discount of 25% and a tax of 15% are applied on it. Freight charges of $100 have to be applied to all the products.

What would be the outcome if all the parentheses are removed from the above statement?

A. It produces a syntax error.

B. The result remains unchanged.

C. The total price value would be lower than the correct value.

D. The total price value would be higher than the correct value.

Answer: B

Q7. View the Exhibit and examine the structure of the customers table.

Using the customers table, you need to generate a report that shows the average credit limit for customers in Washington and NEW YORK.

Which SQL statement would produce the required result?

A. Option A

B. Option B

C. Option C

D. Option D

Answer: C

Q8. View the Exhibit and examine the structure of the customers table.

Using the customers table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.

Which SQL statement would produce the required result?

A. Option A

B. Option B

C. Option C

D. Option D

Answer: D

Explanation:

NVL Function

Converts a null value to an actual value:

Data types that can be used are date, character, and number.

Data types must match:

– NVL(commission_pct, 0)

– NVL(hire_date, '01-JAN-97')

– NVL(job_id, 'No Job Yet')

Q9. You want to display 5 percent of the rows from the sales table for products with the lowest AMOUNT_SOLD and also want to include the rows that have the same AMOUNT_SOLD even if this causes the output to exceed 5 percent of the rows.

Which query will provide the required result?

A. Option A

B. Option B

C. Option C

D. Option D

Answer: D

Q10. View the Exhibit and examine the structure of the customers table.

NEW_CUSTOMERS is a new table with the columns CUST_ID, CUST_NAME and CUST_CITY that have the same data types and size as the corresponding columns in the customers table.

Evaluate the following insert statement: The insert statement fails when executed.

What could be the reason?

A. The values clause cannot be used in an INSERT with a subquery.

B. Column names in the NEW_CUSTOMERS and CUSTOMERS tables do not match.

C. The where clause cannot be used in a subquery embedded in an INSERT statement.

D. The total number of columns in the NEW_CUSTOMERS table does not match the total number of columns in the CUSTOMERS table.

Answer: A

Explanation:

Copying Rows from Another Table

Write your INSERT statement with a subquery:

Do not use the VALUES clause.

Match the number of columns in the INSERT clause to those in the subquery.

Inserts all the rows returned by the subquery in the table, sales_reps.