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

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

Question No.311

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

1. A table can have only one primary key and one foreign key.

2. A table can have only one primary key but multiple foreign keys.

3. Only the primary key can be defined at the column and table levels.

4. The foreign key and parent table primary key must have the same name.

5. Both primary key and foreign key constraints can be defined at both column and table levels.

Correct Answer: BE

Question No.312

Evaluate this SQL statement:

SELECT ename, sal, 12*sal 100 FROM emp;

The SAL column stores the monthly salary of the employee. Which change must be made to the above syntax to calculate the annual compensation as quot;monthly salary plus a monthly bonus of

$100, multiplied by 12quot;?

1. No change is required to achieve the desired results.

2. SELECT ename, sal, 12*(sal 100) FROM emp;

3. SELECT ename, sal, (12*sal) 100 FROM emp;

4. SELECT ename, sal 100, *12 FROM emp;

Correct Answer: B

Explanation:

To achieve the result you must add 100 to sal before multiply with 12. Select ename, sal, 12*(sal 100) from EMP;

Incorrect answer:

A. Multiplication and division has priority over addition and subtraction in Operator precedence.

1. Give wrong results

2. Wrong syntax

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

Question No.313

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

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.

Correct Answer: C

Question No.314

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

Which two SQL statements would execute successfully? (Choose two.)

1. UPDATE promotionsSET promo_cost = promo_cost 100 WHERE

   TO_CHAR(promo_end_date,#39;yyyy#39;) gt; #39;2000#39;;

2. SELECT promo_begin_dateFROM promotions WHERE

   TO_CHAR(promo_begin_date,#39;mon dd yy#39;)=#39;jul 01 98#39;;

3. UPDATE promotions

   SET promo_cost = promo_cost 100

   WHERE promo_end_date gt; TO_DATE(SUBSTR(#39;01-JAN-2000#39;, 8));

4. SELECT TO_CHAR(promo_begin_date,#39;dd/month#39;)FROM promotions

WHERE promo_begin_date IN (TO_DATE(#39;JUN 01 98#39;), TO_DATE(#39;JUL 01 98#39;));

Correct Answer: AB

Question No.315

You want to display the date for the first Monday of the next month and issue the following command:

SQLgt;SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE), #39;MON#39;), #39;dd quot;is the first Monday

for #39;fmmonth rrrr#39;) FROM DUAL; What is the outcome?

1. It executes successfully and returns the correct result.

2. It executes successfully but does not return the correct result.

3. It generates an error because TO_CHAR should be replaced with TO_DATE.

4. It generates an error because rrrr should be replaced by rr in the format string.

5. It generates an error because fm and double quotation marks should not be used in the format string.

Correct Answer: A

Explanation:

NEXT_DAY(date, #39;char#39;): Finds the date of the next specified day of the week (#39;char#39;) following date. The value of char may be a number representing a day or a character string.

LAST_DAY(date): Finds the date of the last day of the month that contains date The second innermost function is evaluated next. TO_CHAR(#39;28-OCT-2009#39;, #39;fmMonth#39;) converts the given date based on the Month format mask and returns the character string October. The fm modifier trims trailing blank spaces from the name of the month.

Question No.316

The PRODUCTS table has these columns: PRODUCT_ID NUMBER(4) PRODUCT_NAME VARCHAR2(45) PRICE NUMBER(8, 2)

Evaluate this SQL statement: SELECT *

FROM PRODUCTS

ORDER BY price, product_name; What is true about the SQL statement?

1. The results are not sorted.

2. The results are sorted numerically.

3. The results are sorted alphabetically.

4. The results are sorted numerically and then alphabetically.

Correct Answer: D

Explanation:

The result is sort by price which is numeric and follow by product_name which is alphabetically.

Incorrect answer:

1. the results are sorted

2. the results are sorted with alphabetically as well

3. the results are sorted with numerically as well

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

Question No.317

Examine the structure of the EMPLOYEES table:

You want to generate a report that contains the department IDs and last names of employees ordered by hire date in their respective departments. Employees from one department must appear in a single row. Which query will provide the required output?

1. SELECT department_id quot;Dept.quot;,LISTAGG (last_name, `;#39;) WITHIN GROUP (ORDER BY hire_date)quot; Employeesquot;FROM employeesGROUP BY department_idORDER BY department_id;

2. SELECT department_id quot;Dept.quot;,LISTAGG (last_name, `;#39;) WITHIN GROUP (ORDER BY hire_date)quot; Employeesquot;FROM employeesGROUP BY department_idORDER BY hire_date;

3. SELECT department_id quot;Dept.quot;,LISTAGG (last_name, `;#39;) WITHIN GROUP (ORDER BY hire_date)quot; Employeesquot;)FROM employeesGROUP BY department_idORDER BY hire_date;

4. SELECT department_id quot;Dept.quot;,LISTAGG (last_name, `;#39;) WITHIN GROUP ORDER BY (hire_date)quot; Employeesquot;)FROM employeesGROUP BY department_idORDER BY department_id;

Correct Answer: C

Question No.318

Which best describes an inline view?

1. a schema object

2. a sub query that can contain an ORDER BY clause

3. another name for a view that contains group functions

4. a sub query that is part of the FROM clause of another query

Correct Answer: D

Explanation:

A sub query that is part of the FROM clause of another query.

Incorrect answer:

1. is not a schema object

2. sub query can contain GROUP BY clause as well.

3. does not necessary contains group functions

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

Question No.319

Examine the following SQL commands:

Which statement is true regarding the execution of the above SQL commands?

1. Both commands execute successfully.

2. The first CREATE TABLE command generates an error because the NULL constraint is not valid.

3. The second CREATE TABLE command generates an error because the CHECK constraint is not valid.

4. The first CREATE TABLE command generates an error because CHECK and PRIMARY KEY constraints cannot be used for the same column.

5. The first CREATE TABLE command generates an error because the column PROD_ID cannot be used in the PRIMARY KEY and FOREIGN KEY constraints.

Correct Answer: B Explanation: Defining Constraints

The slide gives the syntax for defining constraints when creating a table. You can create constraints at either the column level or table level. Constraints defined at the column level are included when the column is defined. Table-level constraints are defined at the end of the table definition and must refer to the column or columns on which the constraint pertains in a set of parentheses. It is mainly the syntax that differentiates the two; otherwise, functionally, a columnlevel constraint is the same as a table-level constraint. NOT NULL constraints must be defined at the column level. Constraints that apply to more than one column must be defined at the table level.

Question No.320

Examine the SQL statement that creates ORDERS table:

CREATE TABLE orders (SER_NO NUMBER UNIQUE, ORDER_ID NUMBER, ORDER_DATE DATE NOT NULL, STATUS VARCHAR2(10) CHECK (status IN (#39;CREDIT#39;, #39;CASH#39;)), PROD_ID NUMBER REFERENCES PRODUCTS(PRODUCT_ID), ORD_TOTAL NUMBER, PRIMARY KEY

(order_id, order_date));

For which columns would an index be automatically created when you execute the above SQL statement? (Choose two.)

1. SER_NO

2. ORDER_ID

3. STATUS

4. PROD_ID

5. ORD_TOTAL

6. composite index on ORDER_ID and ORDER_DATE

Correct Answer: AF

Explanation:

Index exist for UNIQUE and PRIMARY KEY constraints

Incorrect answer:

1. ORDER_ID is neither UNIQUE nor PRIMARY KEY

2. STATUS is neither UNIQUE nor PRIMARY KEY

3. PROD_ID is neither UNIQUE nor PRIMARY KEY

4. ORD_TOTAL is neither UNIQUE nor PRIMARY KEY

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

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