Ucertify 1z0 061 dumps pdf Questions are updated and all 1z0 061 pdf answers are verified by experts. Once you have completely prepared with our oracle database 12c sql fundamentals 1z0 061 pdf free download exam prep kits you will be ready for the real 1z0 061 pdf exam without a problem. We have Improve Oracle oracle database 12c sql fundamentals 1z0 061 pdf free download dumps study guide. PASSED oracle 1z0 061 First attempt! Here What I Did.

♥♥ 2021 NEW RECOMMEND ♥♥

Free VCE & PDF File for Oracle 1Z0-061 Real Exam (Full Version!)

★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions

Available on: http://www.surepassexam.com/1Z0-061-exam-dumps.html

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

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.

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

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

Q24. You issued the following command:

SQL> DROP TABLE employees;

Which three statements are true?

A. All uncommitted transactions are committed.

B. All indexes and constraints defined on the table being dropped are also dropped.

C. Sequences used in the employees table become invalid.

D. The space used by the employees table is reclaimed immediately.

E. The employees table can be recovered using the rollback command.

F. The employees table is moved to the recycle bin.

Reference: http://www.sqlcourse.com/drop.html

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

Q26. CORRECT TEXT

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

You need to generate a report of all promos from the promotions table based on the following conditions:

1. The promo name should not begin with 'T' or 'N'.

2. The promo should cost more than \$20000.

3. The promo should have ended after 1st January 2001.

Which where clause would give the required result?

Answer: WHERE promo_name NOT LIKE ‘T%’ AND promo_name NOT LIKE ‘N%’ AND promo_cost > 20000 AND promo_end_date > ‘1-JAN-01'

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

Q28. Which create table statement is valid?

A. Option A

B. Option B

C. Option C

D. Option D

Explanation:

PRIMARY KEY Constraint A PRIMARY KEY constraint creates a primary key for the table. Only one primary key can be created for each table. The PRIMARY KEY constraint is a column or a set of columns that uniquely identifies each row in a table. This constraint enforces the uniqueness of the column or column combination and ensures that no column that is part of the primary key can contain a null value. Note: Because uniqueness is part of the primary key constraint definition, the Oracle server enforces the uniqueness by implicitly creating a unique index on the primary key column or columns.

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

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