[Free] 2019(Nov) EnsurePass Oracle 1z0-061 Dumps with VCE and PDF 241-250

Get Full Version of the Exam
http://www.EnsurePass.com/1z0-061.html

Question No.241

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

image

Evaluate the query statement:

image

What would be the outcome of the above statement?

  1. It executes successfully.

  2. It produces an error because the condition on CUST_LAST_NAME is invalid.

  3. It executes successfully only if the CUST_CREDIT_LIMIT column does not contain any null values.

  4. It produces an error because the AND operator cannot be used to combine multiple BETWEEN clauses.

Correct Answer: A

Question No.242

The DBA issues this SQL command: CREATE USER scott IDENTIFIED by tiger;

What privileges does the user Scott have at this point?

  1. no privileges

  2. only the SELECT privilege

  3. only the CONNECT privilege

  4. all the privileges of a default user

Correct Answer: A

Explanation:

When a user is created, by default no privilege is granted

Incorrect answer:

  1. SELECT is not grant

  2. CONNECT is not grant

  3. default profile is grant by default not privilege.

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-6

Question No.243

Examine the statement: GRANT select, insert, update ON student_grades

TO manager

WITH GRANT OPTION;

Which two are true? (Choose two.)

  1. MANAGER must be a role.

  2. It allows the MANAGER to pass the specified privileges on to other users.

  3. It allows the MANAGER to create tables that refer to the STUDENT_GRADES table.

  4. It allows the MANAGER to apply all DML statements on the STUDENT_GRADES table.

  5. It allows the MANAGER the ability to select from, insert into, and update the STUDENT_GRADES table.

  6. It allows the MANAGER the ability to select from, delete from, and update the

STUDENT_GRADES table.

Correct Answer: BE

Explanation:

GRANT ROLE to ROLE/USER

Incorrect answer:

A. Role can be grant to user

  1. Create table privilege is not granted

  2. Execute privilege is not granted

F. Delete privilege is not granted

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-15

Question No.244

View the Exhibits and examine the structure of the products and sales tables.

image

Which two SQL statements would give the same output? (Choose two.)

  1. SELECT prod_id FROM productsINTERSECTSELECT prod_id FROM sales;

  2. SELECT prod_id FROM productsMINUSSELECT prod_id FROM sales;

  3. SELECT DISTINCT p.prod_idFROM products p JOIN sales sON p.prod_id=s.prod_id;

  4. SELECT DISTINCT p.prod_idFROM products p JOIN sales sON p.prod_id lt;gt; s.prod_id;

Correct Answer: AC

Question No.245

View the Exhibit and examine the structure of the PRODUCTS, SALES, and SALE_SUMMARY tables.

SALE_VW is a view created using the following command: SQLgt;CREATE VIEW sale_vw AS

SELECT prod_id, SUM(quantity_sold) QTY_SOLD FROM sales GROUP BY prod_id;

You issue the following command to add a row to the SALE_SUMMARY table: SQLgt;INSERT INTO sale_summary

SELECT prod_id, prod_name, qty_sold FROM sale_vw JOIN products USING (prod_id) WHERE prod_id = 16;

What is the outcome?

  1. It executes successfully.

  2. It gives an error because a complex view cannot be used to add data into the SALE_SUMMARY table.

  3. It gives an error because the column names in the subquery and the SALE_SUMMARY table do not match.

  4. It gives an error because the number of columns to be inserted does not match with the number of columns in the SALE_SUMMARY table.

Correct Answer: D

Question No.246

Evaluate the following query: SELECT INTERVAL #39;300#39; MONTH, INTERVAL #39;54-2#39; YEAR TO MONTH,

INTERVAL #39;11:12:10.1234567#39; HOUR TO SECOND

FROM dual;

What is the correct output of the above query? A. 25-00, 54-02, 00 11:12:10.123457

B. 00-300, 54-02, 00 11:12:10.123457

C. 25-00, 00-650, 00 11:12:10.123457

D. 00-300, 00-650, 00 11:12:10.123457

Correct Answer: A Explanation: Datetime Data Types

You can use several datetime data types:

INTERVAL YEAR TO MONTH

Stored as an interval of years and months INTERVAL DAY TO SECOND

Stored as an interval of days, hours, minutes, and seconds

Question No.247

Examine this statement:

SELECT student_id, gpa FROM student_grades WHERE gpa gt; amp;amp;value;

You run the statement once, and when prompted you enter a value of 2.0. A report is produced. What happens when you run the statement a second time?

  1. An error is returned.

  2. You are prompted to enter a new value.

  3. A report is produced that matches the first report produced.

  4. You are asked whether you want a new value or if you want to run the report based on the previous value.

Correct Answer: C

Explanation:

Use the double-ampersand if you want to reuse the variable value without prompting the user each time.

Incorrect answer:

  1. is not an error

  2. amp;amp; will not prompt user for second time

D. amp;amp; will not ask the user for new value

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 7-13

Question No.248

What is true about sequences?

  1. Once created, a sequence belongs to a specific schema.

  2. Once created, a sequence is linked to a specific table.

  3. Once created, a sequence is automatically available to all users.

  4. Only the DBA can control which sequence is used by a certain table.

  5. Once created, a sequence is automatically used in all INSERT and UPDATE statements.

Correct Answer: A

Question No.249

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?

  1. 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 lt; MAX(sal) GROUP BY b.dept_id;

  2. 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 lt; b.maxsal;

  3. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a WHERE a.sal lt; (SELECT MAX(sal) maxsal FROM employees b GROUP BY dept_id);

  4. SELECT emp_name, sal, dept_id, maxsal FROM employees, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) WHERE a.sal lt; maxsal;

Correct Answer: B

Explanation:

function MAX(column_name)

Incorrect answer:

A. invalid statement

  1. inner query return more than one line

  2. column maxsal does not exists.

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 5-7

Question No.250

Examine the data in the LIST_PRICE and MIN_PRICE columns of the PRODUCTS table:

image

Which two expressions give the same output? (Choose two.)

  1. NVL(NULLIF(list_price, min_price), 0)

  2. NVL(COALESCE(list_price, min_price), 0)

  3. NVL2(COALESCE(list_price, min_price), min_price, 0)

  4. COALESCE(NVL2(list_price, list_price, min_price), 0)

Correct Answer: BD

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.

Get Full Version of the Exam
1z0-061 Dumps
1z0-061 VCE and PDF

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.