We provide real 1z0 051 practice test exam questions and answers braindumps in two formats. Download PDF & Practice Tests. Pass Oracle oracle 1z0 051 Exam quickly & easily. The 1z0 051 latest dumps free download pdf PDF type is available for reading and printing. You can print more and practice many times. With the help of our Oracle 1z0 051 pdf dumps pdf and vce product and material, you can easily pass the 1z0 051 practice test 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
Q71. - (Topic 1)
View the Exhibit and examine the structure of the PROMOTIONS table. Which SQL statements are valid? (Choose all that apply.)
A. SELECT promo_id. DECODE(NVL(promo_cost.O).promo_cost * 0.25. 100) "Discount"
FROM promotions;
B. SELECT promo id. DECODE(promo_cost. 10000.
DECODE(promo_category. 'Gl\ promo_cost * 25. NULL). NULL) "Catcost" FROM
promotions;
C. SELECT promo_id. DECODE(NULLIF(promo_cost. 10000). NULL. promo_cost*.25,
*N/A') "Catcost"
FROM promotions;
D. SELECT promo_id. DECODE(promo_cost. >10000. 'High'. <10000. 'Low')
"Range"FROM promotions;
Answer: A,B
Explanation:
Note: there are some syntax issues in this question.
Q72. - (Topic 1)
View the Exhibits and examine the structures of the PRODUCTS SALES and CUSTOMERS tables.
You need to generate a report that gives details of the customer's last name, name of the product, and the quantity sold for all customers in Tokyo'. Which two queries give the required result? (Choose two.)
A.
SELECT c.cust_last_name,p.prod_name, s.quantity_sold FROM sales s JOIN products p
USING(prod_id)
JOIN customers c
USING(cust_id)
WHERE c.cust_city='Tokyo'
B.
SELECT c.cust_last_name, p.prod_name, s.quantity_sold
FROM products p JOIN sales s JOIN customers c
ON(p.prod_id=s.prod_id)
ON(s.cust_id=c.cust_id)
WHERE c.cust_city='Tokyo'
C.
SELECT c.cust_last_name, p.prod_name, s.quantity_sold
FROM products p JOIN sales s
ON(p.prod_id=s.prod_id)
JOIN customers c
ON(s.cust_id=c.cust_id)
AND c.cust_city='Tokyo'
D.
SELECT c.cust_id,c.cust_last_name,p.prod_id, p.prod_name, s.quantity_sold FROM
products p JOIN sales s
USING(prod_id)
JOIN customers c
USING(cust_id)
WHERE c.cust_city='Tokyo'
Answer: A,C
Q73. - (Topic 2)
Which SQL statement accepts user input for the columns to be displayed, the table name, and WHERE condition?
A. SELECT &1, "&2"
FROM &3
WHERE last_name = '&4'
B. SELECT &1, '&2'
FROM &3
WHERE '&last_name = '&4' '
C. SELECT &1, &2
FROM &3
WHERE last_name = '&4'
D. SELECT &1, '&2'
FROM EMP
WHERE last_name = '&4'
Answer: C
Explanation:
In a WHERE clause, date and characters values must be enclosed within single quotation marks.
Sample of the correct syntax
SELECT EMPLOYEE_ID, &COLUMN_NAME FROM EMPLOYEES
Incorrect Answers :
A. Incorrect use of " symbol
B. Incorrect use of ' symbol
D. No input for table name as EMP has been use in the statement.
Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Producing Readable Output with iSQL*PLUS, p. 7-8
Q74. - (Topic 1)
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:
Which MERGE statement is valid?
A.
MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET
B. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||', '||e.last_name);
C.
MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET
D. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT valueS(e.employee_id, e.first_name ||', '||e.last_name);
E.
MERGE INTO new_employees cUSING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET
F. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||', '||e.last_name);
G.
MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET
H. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees valueS(e.employee_id, e.first_name ||', '||e.last_name);
Answer: A
Explanation: Explanation: this is the correct MERGE statement syntax
Incorrect Answer: Bit should MERGE INTO table_name Cit should be WHEN MATCHED THEN Dit should MERGE INTO table_name Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-29
Q75. - (Topic 1)
You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit:
Examine the structure of PRODUCTS table.
Using the PRODUCTS table, you issue the following query to generate the names, current list price and discounted list price for all those products whose list price fails below $10 after a discount of 25% is applied on it.
Exhibit:
The query generates an error.
What is the reason of generating error?
A. The column alias should be put in uppercase and enclosed within double quotation marks in the WHERE clause
B. The parenthesis should be added to enclose the entire expression
C. The column alias should be replaced with the expression in the WHERE clause
D. The double quotation marks should be removed from the column alias
Answer: C
Explanation: Note: You cannot use column alias in the WHERE clause.
Q76. - (Topic 1)
You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit:
Evaluate the following query: Exhibit:
The above query produces an error on execution. What is the reason for the error?
A. An alias cannot be used in an expression
B. The alias MIDPOINT should be enclosed within double quotation marks for the CUST_CREDIT_LIMIT/2 expression
C. The MIDPOINT +100 expression gives an error because CUST_CREDIT_LIMIT contains NULL values
D. The alias NAME should not be enclosed within double quotation marks
Answer: A
Q77. - (Topic 1)
Which three statements are true regarding sub queries? (Choose three.)
A. Multiple columns or expressions can be compared between the main query and sub query
B. Sub queries can contain GROUP BY and ORDER BY clauses
C. Only one column or expression can be compared between the main query and subqeury
D. Main query and sub query can get data from different tables
E. Main query and sub query must get data from the same tables
F. Sub queries can contain ORDER BY but not the GROUP BY clause
Answer: A,B,D
Q78. - (Topic 1)
Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)
A.
SELECT TO_CHAR(1890.55,'$99G999D00') FROM DUAL;
B.
SELECT TO_CHAR(1890.55,'$9,999V99') FROM DUAL;
C.
SELECT TO_CHAR(1890.55,'$0G000D00') FROM DUAL;
D.
SELECT TO_CHAR(1890.55,'$99G999D99')
FROM DUAL;
E.
SELECT TO_CHAR(1890.55,'$9,999D99') FROM DUAL;
Answer: A,C,D
Q79. - (Topic 2)
Which describes the default behavior when you create a table?
A. The table is accessible to all users.
B. Tables are created in the public schema.
C. Tables are created in your schema.
D. Tables are created in the DBA schema.
E. You must specify the schema when the table is created.
Answer: C
Explanation:
sorted by highest to lowest is DESCENDING order
Incorrect Answer: Agrant the table privilege to PUBLIC Blogin as sysoper Dlogin as DBA or sysdba Eno such option is allow.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 9-9
Q80. - (Topic 2)
You need to write a SQL statement that returns employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department.
Which statement accomplishes this task?
A. SELECT a.emp_name, a.sal, b.dept_id, MAX(sal) FROM employees a, departments b WHERE a.dept_id = b.dept_id AND a.sal < MAX(sal) GROUP BY b.dept_id;
B. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) b WHERE a.dept_id = b.dept_id AND a.sal < b.maxsal;
C. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a WHERE a.sal < (SELECT MAX(sal) maxsal FROM employees b GROUP BY dept_id);
D. SELECT emp_name, sal, dept_id, maxsal FROM employees, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) WHERE a.sal < maxsal;
Answer: B
Explanation: function MAX(column_name)
Incorrect Answer:
Ainvalid statement
Cinner query return more than one line
Dcolumn maxsal does not exists.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 5-7
