[Free] 2018(Jan) EnsurePass Testinsides Oracle 1z0-051 Dumps with VCE and PDF 161-170

Ensurepass.com : Ensure you pass the IT Exams
2018 Jan Oracle Official New Released 1z0-051
100% Free Download! 100% Pass Guaranteed!
http://www.EnsurePass.com/1z0-051.html

Oracle Database: SQL Fundamentals I

Question No: 161 – (Topic 2)

What is true about updates through a view?

  1. You cannot update a view with group functions.

  2. When you update a view group functions are automatically computed.

  3. When you update a view only the constraints on the underlying table will be in effect.

  4. When you update a view the constraints on the views always override the constraints on the underlying tables.

Answer: A

Question No: 162 – (Topic 2)

In which four clauses can a sub query be used? (Choose four.)

  1. in the INTO clause of an INSERT statement

  2. in the FROM clause of a SELECT statement

  3. in the GROUP BY clause of a SELECT statement

  4. in the WHERE clause of a SELECT statement

  5. in the SET clause of an UPDATE statement

  6. in the VALUES clause of an INSERT statement

Answer: A,B,D,E Explanation:

A: a sub query is valid on the INTO clause of an ISERT Statement

B: a sub query can be used in the FROM clause of a SELECT statement D: a sub query can be used in the WHERE clause of a SELECT statement, E: a sub query can be used in the SET clauses of an UPDATE statement,

Incorrect answer:

Csub query cannot be used F: is incorrect.

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

Question No: 163 – (Topic 2)

Examine the description of the CUSTOMERS table:

Ensurepass 2018 PDF and VCE

The CUSTOMER_ID column is the primary key for the table.

Which statement returns the city address and the number of customers in the cities Los Angeles or San Francisco?

  1. SELECT city_address, COUNT(*) FROM customers

    WHERE city_address IN ( ‘Los Angeles’, ‘San Fransisco’);

  2. SELECT city_address, COUNT (*) FROMcustomers

    WHERE city address IN ( ‘Los Angeles’, ‘San Fransisco’) GROUP BY city_address;

  3. SELECT city_address, COUNT(customer_id) FROMcustomers

    WHERE city_address IN ( ‘Los Angeles’, ‘San Fransisco’) GROUP BYcity_address, customer_id;

  4. SELECT city_address, COUNT (customer_id) FROM customers

GROUP BY city_address IN ( ‘Los Angeles’, ‘San Fransisco’);

Answer: B Explanation:

Not C: The customer ID in the GROUP BY clause is wrong

Question No: 164 – (Topic 2)

Which two statements are true regarding views? (Choose two.)

  1. A simple view in which column aliases have been used cannot be updated.

  2. Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword.

  3. Rows added through a view are deleted from the table automatically when the view is dropped.

  4. The OR REPLACE option is used to change the definition of an existing view without dropping and recreating it.

  5. The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the view.

Answer: B,D

Question No: 165 – (Topic 2)

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

Ensurepass 2018 PDF and VCE

You want to display the category with the maximum number of items. You issue the following query:

SQLgt;SELECT COUNT(*),prod_category_id FROM products

GROUP BY prod_category_id

HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM products);

What is the outcome?

  1. It executes successfully and gives the correct output.

  2. It executes successfully but does not give the correct output.

  3. It generates an error because the subquery does not have a GROUP BY clause.

  4. It generates an error because = is not valid and should be replaced by the IN operator.

Answer: C

Question No: 166 – (Topic 2)

Which SQL statement would you use to remove a view called EMP_DEPT_VU from your schema?

  1. DROP emp_dept_vu;

  2. DELETE emp_dept_vu;

  3. REMOVE emp_dept_vu;

  4. DROP VIEW emp_dept_vu;

  5. DELETE VIEW emp_dept_vu;

  6. REMOVE VIEW emp_dept_vu;

Answer: D Explanation:

DROP VIEW viewname;

Incorrect answer:

ANot a valid drop view statement BNot a valid drop view statement CNot a valid drop view statement ENot a valid drop view statement FNot a valid drop view statement

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-20

Question No: 167 – (Topic 2)

Which statement adds a constraint that ensures the CUSTOMER_NAME column of the CUSTOMERS table holds a value?

  1. ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL;

  2. ALTER TABLE customers MODIFY CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL;

  3. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn NOT NULL;

  4. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn IS NOT NULL;

  5. ALTER TABLE customers MODIFY name CONSTRAINT cust_name_nn NOT NULL;

  6. ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name NOT NULL;

Answer: C

Question No: 168 – (Topic 2)

View the Exhibit and examine the data in the EMPLOYEES table.

Ensurepass 2018 PDF and VCE

You want to generate a report showing the total compensation paid to each employee to date.

You issue the following query:

Ensurepass 2018 PDF and VCE

What is the outcome?

  1. It generates an error because the alias is not valid.

  2. It executes successfully and gives the correct output.

  3. It executes successfully but does not give the correct output.

  4. It generates an error because the usage of the ROUND function in the expression is not valid.

  5. It generates an error because the concatenation operator can be used to combine only two items.

Answer: C Explanation:

ROUND(column|expression, n) Rounds the column, expression, or value to n decimal places or, if n is omitted, no decimal places (If n is negative, numbers to the left of decimal point are rounded.)

Question No: 169 – (Topic 2)

View the Exhibit and examine the structure of CUSTOMERS and SALES tables.

Ensurepass 2018 PDF and VCE

Evaluate the following SQL statement:

UPDATE (SELECT prod_id, cust_id, quantity_sold, time_id FROM sales)

SET time_id = #39;22-MAR-2007#39;

WHERE cust_id = (SELECT cust_id FROM customers

WHERE cust_last_name = #39;Roberts#39; AND credit_limit = 600);

Which statement is true regarding the execution of the above UPDATE statement?

  1. It would not execute because two tables cannot be used in a single UPDATE statement.

  2. It would not execute because the SELECT statement cannot be used in place of the table name.

  3. It would execute and restrict modifications to only the columns specified in the SELECT statement.

  4. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.

Answer: C Explanation:

One UPDATE statement can change rows in only one table, but it can change any number of rows in that table.

Question No: 170 – (Topic 2)

Which two statements complete a transaction? (Choose two)

  1. DELETE employees;

  2. DESCRIBE employees;

  3. ROLLBACK TO SAVEPOINT C;

  4. GRANT SELECT ON employees TO SCOTT;

  5. ALTER TABLE employeesSET UNUSED COLUMN sal;

  6. Select MAX(sal)FROM employeesWHERE department_id = 20;

Answer: D,E Explanation:

D: GRANT is a DML operation which will cause an implicit commit

E: It is important to understand that an implicit COMMIT occurs on the database when a user exits SQL*Plus or issues a data-definition language (DDL) command such as a CREATE TABLE statement, used to create a database object, or an ALTER TABLE statement, used to alter a database object.

Incorrect Answers

A:The DELETE command is data-manipulation language (DML) command and it does not complete a transaction.

B:The DESCRIBE command is internal SQL*Plus command and it has nothing to do with completion a transaction.

C: ROLLBACK is not used to commit or complete a transaction, it is used to undo a transaction

F:SELECT command is used to retrieve data. It does not complete a transaction.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 281-282 Chapter 3: Advanced Data Selection in Oracle

100% Ensurepass Free Download!
Download Free Demo:1z0-051 Demo PDF
100% Ensurepass Free Guaranteed!
1z0-051 Dumps

EnsurePass ExamCollection Testking
Lowest Price Guarantee Yes No No
Up-to-Dated Yes No No
Real Questions Yes No No
Explanation Yes No No
PDF VCE Yes No No
Free VCE Simulator Yes No No
Instant Download Yes No No

Leave a Reply

Your email address will not be published. Required fields are marked *

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