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

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. Examine the structure of the orders table:

You want to find the total value of all the orders for each year and issue the following command:

Which statement is true regarding the outcome?

A. It executes successfully and gives the correct output.

B. It gives an error because the TO_CHAR function is not valid.

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

D. It gives an error because the data type conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.

Answer: D

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

Answer: B

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

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.

Answer: A,B,D

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

Answer: A,D

Explanation:

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.

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

Answer: B

Q6. Evaluate the following SQL commands:

The command to create a table fails. Identify the two reasons for the SQL statement failure?

A. You cannot use SYSDATE in the condition of a check constraint.

B. You cannot use the BETWEEN clause in the condition of a check constraint.

C. You cannot use the NEXTVAL sequence value as a default value for a column.

D. You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD_NO is also the foreign key.

Answer: A,C

Explanation:

CHECK Constraint The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as the query conditions, with the following exceptions: References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns Calls to SYSDATE, UID, USER, and USERENV functions Queries that refer to other values in other rows A single column can have multiple CHECK constraints that refer to the column in its definition. There is no limit to the number of CHECK constraints that you can define on a column. CHECK constraints can be defined at the column level or table level. CREATE TABLE employees (... Salary NUMBER(8, 2) CONSTRAINT emp_salary_min CHECK (salary > 0),

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

Answer: A

Q8. Which statement is true regarding the UNION operator?

A. By default, the output is not sorted.

B. Null values are not ignored during duplicate checking.

C. Names of all columns must be identical across all select statements.

D. The number of columns selected in all select statements need not be the same.

Answer: D

Explanation:

The SQL UNION query allows you to combine the result sets of two or more SQL SELECT statements. It removes duplicate rows between the various SELECT statements. Each SQL SELECT statement within the UNION query must have the same number of fields in the result sets with similar data types.

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

Answer: A

Q10. You need to create a table with the following column specifications:

1. Employee ID (numeric data type) for each employee

2. Employee Name (character data type) that stores the employee name

3. Hire date, which stores the date of joining the organization for each employee

4. Status (character data type), that contains the value 'active1 if no data is entered

5. Resume (character large object [CLOB] data type), which contains the resume submitted by the employee

Which is the correct syntax to create this table?

A. Option A

B. Option B

C. Option C

D. Option D

Answer: D

Explanation:

CLOB Character data (up to 4 GB)

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 –84 to 127.)