We provide real 1z0 051 practice test exam questions and answers braindumps in two formats. Download PDF & Practice Tests. Pass Oracle 1z0 051 latest dumps free download pdf Exam quickly & easily. The 1z0 051 dumps PDF type is available for reading and printing. You can print more and practice many times. With the help of our Oracle 1z0 051 practice test dumps pdf and vce product and material, you can easily pass the oracle 1z0 051 exam.
♥♥ 2021 NEW RECOMMEND ♥♥
Free VCE & PDF File for Oracle 1Z0-051 Real Exam (Full Version!)
★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions
Free Instant Download NEW 1Z0-051 Exam Dumps (PDF & VCE):
Available on:
http://www.surepassexam.com/1Z0-051-exam-dumps.html
Q21. - (Topic 2)
Which three statements are true regarding subqueries? (Choose three.)
A. Subqueries can contain GROUP BY and ORDER BY clauses.
B. Main query and subquery can get data from different tables.
C. Main query and subquery must get data from the same tables.
D. Subqueries can contain ORDER BY but not the GROUP BY clause.
E. Only one column or expression can be compared between the main query and subquery.
F. Multiple columns or expressions can be compared between the main query and subquery.
Answer: A,B,F
Explanation:
SUBQUERIES can be used in the SELECT list and in the FROM, WHERE, and HAVING
clauses of a query.
A subquery can have any of the usual clauses for selection and projection. The following
are required clauses:
A SELECT list
A FROM clause
The following are optional clauses: WHERE GROUP BY HAVING
The subquery (or subqueries) within a statement must be executed before the parent query that calls it, in order that the results of the subquery can be passed to the parent.
Q22. - (Topic 1)
Which statements are correct regarding indexes? (Choose all that apply.)
A. For each data manipulation language (DML) operation performed, the corresponding indexes are automatically updated.
B. A nondeferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.
C. A FOREIGN KEY constraint on a column in a table automatically creates a non unique key
D. When a table is dropped, the corresponding indexes are automatically dropped
Answer: A,B,D
Q23. - (Topic 1)
The PRODUCTS table has the following structure:
Evaluate the following two SQL statements:
Which statement is true regarding the outcome?
A. Both the statements execute and give the same result
B. Both the statements execute and give different results
C. Only the second SQL statement executes successfully
D. Only the first SQL statement executes successfully
Answer: B
Explanation:
Using the NVL2 Function The NVL2 function examines the first expression. If the first expression is not null, the NVL2 function returns the second expression. If the first expression is null, the third expression is returned.
Syntax NVL2(expr1, expr2, expr3) In the syntax: expr1 is the source value or expression that may contain a null expr2 is the value that is returned if expr1 is not null expr3 is the value that is returned if expr1 is null
Q24. - (Topic 2)
Which statement is true regarding transactions? (Choose all that apply.)
A. A transaction can consist only of a set of DML and DDL statements.
B. A part or an entire transaction can be undone by using ROLLBACK command.
C. A transaction consists of a set of DML or DCL statements.
D. A part or an entire transaction can be made permanent with a COMMIT.
E. A transaction can consist of only a set of queries or DML or DDL statements.
Answer: B,C
Q25. - (Topic 1)
Exhibit contains the structure of 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 products whose list price is the second highest in the table.
C. It shown the names of all products whose list price is less than the maximum list price
D. It shows the names of all products in the table
Answer: B
Q26. - (Topic 2)
Examine the data in the LIST_PRICE and MIN_PRICE columns of the PRODUCTS table:
Which two expressions give the same output? (Choose two.)
A. NVL(NULLIF(list_price, min_price), 0)
B. NVL(COALESCE(list_price, min_price), 0)
C. NVL2(COALESCE(list_price, min_price), min_price, 0)
D. COALESCE(NVL2(list_price, list_price, min_price), 0)
Answer: B,D
Explanation:
Using the COALESCE Function
.
The advantage of the COALESCE function over the NVL function is that the COALESCE
function can take multiple alternate values.
.
If the first expression is not null, the COALESCE function returns that expression;
otherwise, it does a COALESCE of the remaining expressions.
Using the COALESCE Function
The COALESCE function returns the first non-null expression in the list.
Syntax
COALESCE (expr1, expr2, ... exprn) In the syntax:
.
expr1 returns this expression if it is not null
.
expr2 returns this expression if the first expression is null and this expression is not null
.
exprn returns this expression if the preceding expressions are null Note that all expressions must be of the same data type.
Q27. - (Topic 2)
View the Exhibit and examine the structure of the PRODUCTS table.
You want to display only those product names with their list prices where the list price is at least double the minimum price. The report should start with the product name having the maximum list price satisfying this
condition.
Evaluate the following SQL statement:
SQL>SELECT prod_name,prod_list_price FROM products WHERE prod_list_price >= 2 * prod_min_price
Which ORDER BY clauses can be added to the above SQL statement to get the correct output?
(Choose all that apply.)
A. ORDER BY prod_list_price DESC, prod_name;
B. ORDER BY (2*prod_min_price)DESC, prod_name;
C. ORDER BY prod_name, (2*prod_min_price)DESC;
D. ORDER BY prod_name DESC, prod_list_price DESC;
E. ORDER BY prod_list_price DESC, prod_name DESC;
Answer: A,E
Explanation:
Using the ORDER BY Clause The order of rows that are returned in a query result is undefined. The ORDER BY clause can be used to sort the rows. However, if you use the ORDER BY clause, it must be the last clause of the SQL statement. Further, you can specify an expression, an alias, or a column position as the sort condition. Syntax SELECT expr FROM table [WHERE condition(s)] [ORDER BY {column, expr, numeric_position} [ASC|DESC]]; In the syntax: ORDER BY specifies the order in which the retrieved rows are displayed ASC orders the rows in ascending order (This is the default order.)
DESC orders the rows in descending order 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.
Q28. - (Topic 2)
Which tasks can be performed using SQL functions that are built into Oracle database? (Choose three.)
A. finding the remainder of a division
B. adding a number to a date for a resultant date value
C. comparing two expressions to check whether they are equal
D. checking whether a specified character exists in a given string
E. removing trailing, leading, and embedded characters from a character string
Answer: A,C,D
Q29. - (Topic 2)
Examine the structure of the EMPLOYEES and DEPARTMENTS tables:
You want to create a report displaying employee last names, department names, and locations. Which query should you use to create an equi-join?
A. SELECT last_name, department_name, location_id FROM employees , departments ;
B. SELECT employees.last_name, departments.department_name,
departments.location_id FROM employees e, departments D WHERE e.department_id =d.department_id;
C. SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE manager_id =manager_id;
D. SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE e.department_id =d.department_id;
Answer: D
Explanation:
Equijoins are also called simple joins or inner joins. Equijoin involve primary key and foreign key.
Incorrect Answer: Athere is no join B invalid syntax Cdoes not involve the join in the primary and foreign key
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 4-8
Q30. - (Topic 2)
Which three statements are true regarding views? (Choose three.)
A. Views can be created only from tables.
B. Views can be created from tables or other views.
C. Only simple views can use indexes existing on the underlying tables.
D. Both simple and complex views can use indexes existing on the underlying tables.
E. Complex views can be created only on multiple tables that exist in the same schema.
F. Complex views can be created on multiple tables that exist in the same or different schemas.
Answer: B,D,F
Explanation:
Creating a Sequence (continued)
CYCLE | NOCYCLE Specifies whether the sequence continues to generate values after
reaching its maximum or minimum value
(NOCYCLE is the default option.)
CACHE n | NOCACHE Specifies how many values the Oracle server preallocates and
keeps in memory (By default, the Oracle server caches 20 values.)
