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

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

Question No.171

Which four are valid Oracle constraint types? (Choose four.)

  1. CASCADE

  2. UNIQUE

  3. NONUNIQUE

  4. CHECK

  5. PRIMARY KEY

  6. CONSTANT

  7. NOT NULL

Correct Answer: BDEG

Explanation:

Oracle constraint type is Not Null, Check, Primary Key, Foreign Key and Unique

Incorrect answer:

A. Is not Oracle constraint

C. Is not Oracle constraint

F. Is not Oracle constraint

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-3

Question No.172

What is true regarding sub queries?

  1. The inner query always sorts the results of the outer query

  2. The outer query always sorts the results of the inner query

  3. The outer query must return a value to the outer query

  4. The inner query returns a value to the outer query

  5. The inner query must always return a value or the outer query will give an error

Correct 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

Question No.173

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

Using the PROMOTIONS table, you need to find out the names and cost of all the promos done on #39;TV#39; and #39;internet#39; that ended in the time interval 15th March #39;00 to 15th October #39;00.

Which two queries would give the required result? (Choose two.)

image

  1. SELECT promo_name, promo_costFROM promotionsWHERE promo_category IN (#39;TV#39;, #39;internet#39;) ANDpromo_end_date BETWEEN #39;15-MAR-00#39; AND #39;15-OCT-00#39;;

  2. SELECT promo_name, promo_costFROM promotionsWHERE promo_category = #39;TV#39; OR promo_category =#39;internet#39; AND promo_end_date gt;=#39;15-MAR-00#39; OR promo_end_date lt;=#39;15- OCT-00#39;;

  3. SELECT promo_name, promo_costFROM promotionsWHERE (promo_category BETWEEN #39;TV#39; AND #39;internet#39;) AND(promo_end_date IN (#39;15-MAR-00#39;, #39;15-OCT-00#39;));

  4. SELECT promo_name, promo_costFROM promotionsWHERE (promo_category = #39;TV#39; OR promo_category =#39;internet#39;) AND (promo_end_date gt;=#39;15-MAR-00#39; AND promo_end_date lt;=#39;15- OCT-00#39;);

Correct Answer: AD

Question No.174

image

EMPLOYEES and DEPARTMENTS data: EMPLOYEES

DEPARTMENTS

image

On the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID managers and refers to the EMPLOYEE_ID.

On the DEPARTMENTS table DEPARTMENT_ID is the primary key. Evaluate this UPDATE statement.

UPDATE employees SET mgr_id =

. (SELECT mgr_id

. FROM. employees

. WHERE dept_id=

. (SELECT department_id

. FROM departments

. WHERE department_name = #39;Administration#39;)),

. Salary = (SELECT salary

. . FROM employees

. . WHERE emp_name = #39;Smith#39;) WHERE job_id = #39;IT_ADMIN#39;;

What happens when the statement is executed?

  1. The statement executes successfully, leaves the manager ID as the existing value, and changes the salary to 4000 for the employees with ID 103 and 105.

  2. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 4000 for the employees with ID 103 and 105.

  3. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 3000 for the employees with ID 103 and 105.

  4. The statement fails because there is more than one row matching the employee name Smith.

  5. The statement fails because there is more than one row matching the IT_ADMIN job ID in the EMPLOYEES table.

  6. The statement fails because there is no #39;Administration#39; department in the DEPARTMENTS table.

Correct Answer: D

Explanation:

#39;=#39; is use in the statement and sub query will return more than one row. Employees table has 2

row matching the employee name Smith. The update statement will fail.

Incorrect Answers :

  1. The Update statement will fail no update was done.

  2. The update statement will fail no update was done.

  3. The update statement will fail no update was done.

  1. The update statement will fail but not due to job_it=#39;IT_ADMIN#39;

  2. The update statement will fail but not due to department_id=#39;Administration#39;

Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Sub queries, p. 6-12

Question No.175

You need to calculate the number of days from 1st January 2007 till date . Dates are stored in the default format of dd-mon-rr. Which two SQL statements would give the required output? (Choose two.)

  1. SELECT SYSDATE – #39;01-JAN-2007#39; FROM DUAL:

  2. SELECT SYSDATE – TOJDATE(X)1/JANUARY/2007quot;) FROM DUAL:

  3. SELECT SYSDATE – TOJDATE(#39;01-JANUARY-2007#39;) FROM DUAL:

  4. SELECT TO_CHAR(SYSDATE. #39;DD-MON-YYYY#39;) – #39;01-JAN-2007#39; FROM DUAL:

  5. SELECT TO_DATE(SYSDATE. *DD/MONTH/YYYY#39;) – #39;01/JANUARY/2007#39; FROM DUAL:

Correct Answer: BC

Question No.176

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

image

You need to generate a report in the following format: CATEGORIES

5MP Digital Photo Camera#39;s category is Photo Y Box#39;s category is Electronics

Envoy Ambassador#39;s category is Hardware

Which two queries would give the required output? (Choose two.)

  1. SELECT prod_name || q#39;#39;#39;s category is #39; || prod_category CATEGORIES FROM products;

  2. SELECT prod_name || q#39;[#39;s ]#39;category is #39; || prod_category CATEGORIES FROM products;

  3. SELECT prod_name || q#39;\#39;s\#39; || #39; category is #39; || prod_category CATEGORIES FROM products;

  4. SELECT prod_name || q#39;lt;#39;s gt;#39; || #39;category is #39; || prod_category CATEGORIES FROM products;

Correct Answer: CD

Explanation:

So, how are words that contain single quotation marks dealt with? There are essentially two mechanisms available. The most popular of these is to add an additional single quotation mark next to each naturally occurring single quotation mark in the character string

Oracle offers a neat way to deal with this type of character literal in the form of the alternative quote (q) operator. Notice that the problem is that Oracle chose the single quote characters as the special pair of symbols that enclose or wrap any other character literal. These character- enclosing symbols could have been anything other than single quotation marks.

Bearing this in mind, consider the alternative quote (q) operator. The q operator enables you to choose from a set of possible pairs of wrapping symbols for character literals as alternatives to the single quote symbols. The options are any single-byte or multibyte character or the four brackets: (round brackets), {curly braces}, [squarebrackets], or lt;angle bracketsgt;. Using the q operator, the character delimiter can effectively be changed from a single quotation mark to any other character The syntax of the alternative quote operator is as follows:

q#39;delimiter#39;character literal which may include the single quotes delimiter#39; where delimiter can be any character or bracket.

Alternative Quote (q) Operator

Specify your own quotation mark delimiter. Select any delimiter.

Increase readability and usability.

SELECT department_name || q#39;[ Department#39;s Manager Id: ]#39; || manager_id AS quot;Department and Managerquot;

FROM departments; Alternative Quote (q) Operator

Many SQL statements use character literals in expressions or conditions. If the literal itself contains a single quotation mark, you can use the quote (q) operator and select your own quotation mark delimiter.

You can choose any convenient delimiter, single-byte or multibyte, or any of the following character pairs: [ ], { }, ( ), or lt; gt;.

In the example shown, the string contains a single quotation mark, which is normally interpreted as a delimiter of a character string. By using the q operator, however, brackets [] are used as the quotation mark delimiters. The string between the brackets delimiters is interpreted as a literal character string.

Question No.177

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:

There are no privileges for the user Scott at this point. They are not added themselves to the user immediately after creation. The DBA needs to grant all privileges explicitly.

Incorrect Answers

B: There are no privileges for the user Scott at this point. SELECT privilege needs to be added to the user Scott.

C: There are no privileges for the user Scott at this point. CONNECT privilege needs to be added to the user Scott.

D: There is no default user in Oracle.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 348-351 Chapter 8: User Access in Oracle

Question No.178

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

image

There is only one customer with the CUST_LAST_NAME column having value Roberts.

Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600?

  1. INSERT INTO ordersVALUES (1, #39;10-mar-2007#39;, #39;direct#39;,(SELECT customer_idFROM customersWHERE cust_last_name=#39;Roberts#39; ANDcredit_limit=600), 1000);

  2. INSERT INTO orders (order_id, order_date, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name=#39;Roberts#39; ANDcredit_limit=600), order_total)VALUES(1, #39;10- mar-2007#39;, #39;direct#39;, amp;amp;customer_id, 1000);

  3. INSERT INTO(SELECT o.order_id, o.order_date, o.order_mode, c.customer_id, o.order_totalFROM orders o, customers cWHERE o.customer_id = c.customer_idAND c.cust_last_name=#39;Roberts#39; ANDc.credit_limit=600 )VALUES (1, #39;10-mar-2007#39;, #39;direct#39;, (SELECT customer_idFROM customersWHERE cust_last_name=#39;Roberts#39; ANDcredit_limit=600), 1000);

  4. INSERT INTO orders (order_id, order_date, order_mode,(SELECT customer_idFROM

customersWHERE cust_last_name=#39;Roberts#39; ANDcredit_limit=600), order_total)VALUES(1, #39;10- mar-2007#39;, #39;direct#39;, amp;customer_id, 1000);

Correct Answer: A

Question No.179

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

You need to generate a report of all promos from the PROMOTIONS table based on the following conditions:

  1. The promo name should not begin with #39;T#39; or #39;N#39;.

  2. The promo should cost more than $20000.

  3. The promo should have ended after 1st January 2001. Which WHERE clause would give the required result?

  1. WHERE promo_name NOT LIKE #39;T%#39; OR promo_name NOT LIKE #39;N%#39; AND promo_cost gt; 20000 AND promo_end_date gt; #39;1-JAN-01#39;

  2. WHERE (promo_name NOT LIKE #39;T%#39; AND promo_name NOT LIKE #39;N%#39;) OR promo_cost gt; 20000 OR promo_end_date gt; #39;1-JAN-01#39;

  3. WHERE promo_name NOT LIKE #39;T%#39; AND promo_name NOT LIKE #39;N%#39; AND promo_cost gt; 20000 AND promo_end_date gt; #39;1-JAN-01#39;

  4. WHERE (promo_name NOT LIKE #39;%T%#39; OR promo_name NOT LIKE #39;%N%#39;) AND (promo_cost gt; 20000 AND promo_end_date gt; #39;1-JAN-01#39;)

Correct Answer: C

Question No.180

Which two statements about creating constraints are true? (Choose two)

  1. Constraint names must start with SYS_C.

  2. All constraints must be defined at the column level.

  3. Constraints can be created after the table is created.

  4. Constraints can be created at the same time the table is created.

  5. Information about constraints is found in the VIEW_CONSTRAINTS dictionary view.

Correct Answer: CD

Explanation:

Constraints can be created after the table is created. Use ALTER TABLE command for that. Constraints can be created at the same time the table is created (CREATE TABLE command).

Incorrect Answers

A: There is no requirements in Oracle that constraint names must start with SYS_

C.Oracle can use prefix quot;SYSquot; to build indexes for UNIQUE and NOT NULL constraints, but it is not required for user to follow this naming rule.

B: Not all constraints must be defines at the column level. Only NOT NULL constraint must be. E: There is no VIEW_CONSTRAINTS dictionary view in Oracle.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 227-232 Chapter 5: Creating Oracle Database Objects

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