Certified of 1z0 051 latest dumps free download pdf free download materials and rapidshare for Oracle certification for candidates, Real Success Guaranteed with Updated 1z0 051 pdf pdf dumps vce Materials. 100% PASS Oracle Database: SQL Fundamentals I exam Today!
♥♥ 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
Q121. - (Topic 1)
You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit:
You want to create a SALE_PROD view by executing the following SQL statements:
Which statement is true regarding the execution of the above statement?
A. The view will be created and you can perform DLM operations on the view
B. The view will not be created because the join statements are not allowed for creating a view
C. The view will not be created because the GROUP BY clause is not allowed for creating a view
D. The view will be created but no DML operations will be allowed on the view
Answer: D
Explanation:
Rules for Performing DML Operations on a View You cannot add data through a view if the view includes: Group functions A GROUP BY clause The DISTINCT keyword The pseudocolumn ROWNUM keyword Columns defined by expressions NOT NULL columns in the base tables that are not selected by the view
Q122. - (Topic 2)
The CUSTOMERS table has these columns: CUSTOMER_ID NUMBER(4) NOT NULL
CUSTOMER_NAME VARCHAR2(100) NOT NULL
CUSTOMER_ADDRESS VARCHAR2(150)
CUSTOMER_PHONE VARCHAR2(20)
You need to produce output that states "Dear Customer customer_name, ".
The customer_name data values come from the CUSTOMER_NAME column in the CUSTOMERS table.
Which statement produces this output?
A. SELECT dear customer, customer_name, FROM customers;
B. SELECT "Dear Customer", customer_name || ',' FROM customers;
C. SELECT 'Dear Customer ' || customer_name ',' FROM customers;
D. SELECT 'Dear Customer ' || customer_name || ',' FROM customers;
E. SELECT "Dear Customer " || customer_name || "," FROM customers;
F. SELECT 'Dear Customer ' || customer_name || ',' || FROM customers;
Answer: D
Explanation: Concatenation operator to create a resultant column that is a character expression.
Incorrect Answer: Ano such dear customer column Binvalid syntax Cinvalid syntax Einvalid syntax Finvalid syntax
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 1-18
Q123. - (Topic 2)
Examine the data in the CUSTOMERS table:
You want to list all cities that have more than one customer along with the customer details. Evaluate the following query:
SQL>SELECT c1.custname, c1.city FROM Customers c1 __________________ Customers c2 ON (c1.city=c2.city AND c1.custname<>c2.custname);
Which two JOIN options can be used in the blank in the above query to give the correct output? (Choose two.)
A. JOIN
B. NATURAL JOIN
C. LEFT OUTER JOIN
D. FULL OUTER JOIN
E. RIGHT OUTER JOIN
Answer: A,E
Q124. - (Topic 1)
View the Exhibit and examine the data in the COSTS table.
You need to generate a report that displays the IDs of all products in the COSTS table whose unit price is at least 25% more than the unit cost. The details should be displayed in the descending order of 25% of the unit cost. You issue the following query:
Which statement is true regarding the above query?
A. It executes and produces the required result.
B. It produces an error because an expression cannot be used in the ORDER BY clause.
C. It produces an error because the DESC option cannot be used with an expression in the ORDER BY clause.
D. It produces an error because the expression in the ORDER BY clause should also be specified in the SELECT clause.
Answer: A
Q125. - (Topic 2)
Which two statements are true about WHERE and HAVING clauses? (Choose two)
A. A WHERE clause can be used to restrict both rows and groups.
B. A WHERE clause can be used to restrict rows only.
C. A HAVING clause can be used to restrict both rows and groups.
D. A HAVING clause can be used to restrict groups only.
E. A WHERE clause CANNOT be used in a query of the query uses a HAVING clause.
F. A HAVING clause CANNOT be used in sub queries.
Answer: B,D Explanation:
B: WHERE clause cannot be use to restrict groups
WHERE clause cannot be use when there is group functions.
D: A HAVING clause can only e used to restrict GROUPS.
Note: HAVING clause to specify which groups are to be displayed and thus further restrict the groups on the basis of aggregate information. 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 group that match the criteria in the Having clause are displayed.
Incorrect Answers :
A. Where clause cannot be use to restrict groups
C. A HAVING clause can only e used to restrict GROUPS.
E. WHERE clause cannot be use when there is group function, instead HAVING is to be use.
F. There is no constraint to use HAVING clause in a sub queries.
Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Aggregating Data using Group Functions, p. 5-20
Q126. - (Topic 1)
View the Exhibit and examine the structure of the CUSTOMERS table. Evaluate the query statement:
What would be the outcome of the above statement?
A. It executes successfully.
B. It produces an error because the condition on CUST_LAST_NAME is invalid.
C. It executes successfully only if the CUST_CREDIT_LIMIT column does not contain any null values.
D. It produces an error because the AND operator cannot be used to combine multiple BETWEEN clauses.
Answer: A
Q127. - (Topic 1)
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 S100 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
Q128. - (Topic 1)
Where can sub queries be used? (Choose all that apply)
A. field names in the SELECT statement
B. the FROM clause in the SELECT statement
C. the HAVING clause in the SELECT statement
D. the GROUP BY clause in the SELECT statement
E. the WHERE clause in only the SELECT statement
F. the WHERE clause in SELECT as well as all DML statements
Answer: A,B,C,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.
Q129. - (Topic 1)
What is true regarding sub queries?
A. The inner query always sorts the results of the outer query
B. The outer query always sorts the results of the inner query
C. The outer query must return a value to the outer query
D. The inner query returns a value to the outer query
E. The inner query must always return a value or the outer query will give an error
Answer: D
Explanation: The inner query returns a value to the outer query. If the inner query does not return a value, the outer query does not return a result
Q130. - (Topic 2)
View the Exhibits and examine PRODUCTS and SALES tables.
You issue the following query to display product name and the number of times the product has been sold:
SQL>SELECT p.prod_name, i.item_cnt FROM (SELECT prod_id, COUNT(*) item_cnt FROM sales GROUP BY prod_id) i RIGHT OUTER JOIN products p
ON i.prod_id = p.prod_id;
What happens when the above statement is executed?
A. The statement executes successfully and produces the required output.
B. The statement produces an error because ITEM_CNT cannot be displayed in the outer query.
C. The statement produces an error because a subquery in the FROM clause and outer-joins cannot be used together.
D. The statement produces an error because the GROUP BY clause cannot be used in a subquery in the FROM clause.
Answer: A