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

November 4, 2019

1z0-061 Dumps 2019-11

, , , , , ,

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

Question No.221

Which statement is true regarding sub queries?

  1. The LIKE operator cannot be used with single- row subqueries.

  2. The NOT IN operator is equivalent to IS NULL with single- row subqueries.

C. =ANY and =ALL operators have the same functionality in multiple- row subqueries.

D. The NOT operator can be used with IN, ANY, and ALL operators in multiple- row subqueries.

Correct Answer: D

Explanation:

Using the ANY Operator in Multiple-Row Subqueries

The ANY operator (and its synonym, the SOME operator) compares a value to each value returned by a subquery.

lt;ANY means less than the maximum.

gt;ANY means more than the minimum.

=ANY is equivalent to IN

Using the ALL Operator in Multiple-Row Subqueries

The ALL operator compares a value to every value returned by a subquery.

gt;ALL means more than the maximum and

lt;ALL means less than the minimum.

The NOT operator can be used with IN, ANY, and ALL operators.

Question No.222

Which constraint can be defined only at the column level?

  1. UNIQUE

  2. NOT NULL

  3. CHECK

  4. PRIMARY KEY

  5. FOREIGN KEY

Correct Answer: B

Explanation:

The NOT NULL constraint can be defined only at the column level. It enforces that a value must be defined for this column such that the column may not be NULL for any row.

Incorrect Answers

A: The UNIQUE constraint enforces uniqueness on values in the constrained column. It can be defined not only at the column level.

C: The CHECK constraint enforces that values added to the constrained column must be present in a static list of values permitted for the column.

D: The PRIMARY KEY constraint stipulates that values in the constrained column(s) must be unique and not NULL. If the primary key applies to multiple columns, then the combination of values in the columns must be unique and not NULL.

E: The FOREIGN KEY constraint enforces that only values in the primary key of a parent table may be included as values in the constrained column(s) of the child table.

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

Question No.223

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: EMPLOYEES

EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE NEW_EMPLOYEES

EMPLOYEE_ID NUMBER Primary Key NAME VARCHAR2(60)

Which MERGE statement is valid?

  1. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||#39;, #39;|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||#39;, #39;||e.last_name);

  2. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||#39;, #39;|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||#39;, #39;||e.last_name);

  3. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||#39;, #39;|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||#39;, #39;||e.last_name);

  4. MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||#39;, #39;|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees VALUES (e.employee_id, e.first_name ||#39;, #39;||e.last_name);

Correct Answer: A

Explanation:

The correct statement for MERGE is MERGE INTO table_name

Incorrect answer:

  1. Wrong statement with the keyword EXISTS

  2. Wrong statement with the keyword EXISTS

  3. Wrong statement on the MERGE new_employees

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

Question No.224

Which two statements are true about WHERE and HAVING clauses? (Choose two)

  1. A WHERE clause can be used to restrict both rows and groups.

  2. A WHERE clause can be used to restrict rows only.

  3. A HAVING clause can be used to restrict both rows and groups.

  4. A HAVING clause can be used to restrict groups only.

  5. A WHERE clause CANNOT be used in a query of the query uses a HAVING clause.

  6. A HAVING clause CANNOT be used in sub queries.

Correct Answer: BD

Explanation:

B: WHERE clause cannot be use to restrict groups

WHERE clause cannot be use when there is group functions. D: A HAVING clause can only e used to restrict GROUPS.

Note:

HAVING clause to specify which groups are to be displayed and thus further restrict the groups on

the basis of aggregate information. The Oracle server performs the following steps when you use the Having clause

rows are grouped

the group function is applied to the group

the group that match the criteria in the Having clause are displayed.

Incorrect Answers :

  1. Where clause cannot be use to restrict groups

  2. A HAVING clause can only e used to restrict GROUPS.

  3. WHERE clause cannot be use when there is group function, instead HAVING is to be use.

  4. There is no constraint to use HAVING clause in a sub queries.

Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Aggregating Data using Group Functions, p. 5-20

Question No.225

View the Exhibit to examine the description for the SALES table. Which views can have all DML operations performed on it? (Choose all that apply.)

image

  1. CREATE VIEW v3AS SELECT * FROM SALESWHERE cust_id = 2034WITH CHECK OPTION;

  2. CREATE VIEW v1AS SELECT * FROM SALESWHERE time_id lt;= SYSDATE – 2*365WITH CHECK OPTION;

  3. CREATE VIEW v2AS SELECT prod_id, cust_id, time_id FROM SALESWHERE time_id lt;= SYSDATE – 2*365WITH CHECK OPTION;

  4. CREATE VIEW v4AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM SALESWHERE time_id lt;= SYSDATE – 2*365GROUP BY prod_id, cust_idWITH CHECK OPTION;

Correct Answer: AB

Explanation:

Creating a View

You can create a view by embedding a subquery in the CREATE VIEW statement. In the syntax:

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view

[(alias[, alias]…)]

AS subquery

[WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]];

OR REPLACE Re-creates the view if it already exists FORCE Creates the view regardless of whether or not the base tables exist NOFORCE Creates the view only if the base tables exist (This is the default.) View Is the name of the view alias Specifies names for the expressions selected by the view#39;s query (The number of aliases must match the number of expressions selected by the view.)

subquery Is a complete SELECT statement (You can use aliases for the columns in the SELECT list.)

WITH CHECK OPTION Specifies that only those rows that are accessible to the view can be inserted or updated ANSWER D constraint Is the name assigned to the CHECK OPTION constraint

WITH READ ONLY Ensures that no DML operations can be performed on this view Rules for Performing DML Operations on a View

You cannot add data through a view if the view includes: Group functions

A GROUP BY clause The DISTINCT keyword

The pseudocolumn ROWNUM keyword Columns defined by expressions

NOT NULL columns in the base tables that are not selected by the view ?ANSWER C

Question No.226

Evaluate the SQL statement DROP TABLE DEPT:

Which four statements are true of the SQL statement? (Choose four.)

  1. You cannot roll back this statement.

  2. All pending transactions are committed.

  3. All views based on the DEPT table are deleted.

  4. All indexes based on the DEPT table are dropped.

  5. All data in the table is deleted, and the table structure is also deleted.

  6. All data in the table is deleted, but the structure of the table is retained.

  7. All synonyms based on the DEPT table are deleted.

Correct Answer: ABDE

Explanation:

You cannot roll back DROP TABLE statement. All pending transactions related on this table are committed. If the table is dropped, Oracle automatically drops any index, trigger and constraint associated with the table as well. All data in the table is deleted, and the table structure is also deleted.

Incorrect Answers

C: All views based on the DEPT table become invalid, but they are not deleted.

F: All data in the table is deleted, and the table structure is also deleted. Command TRUNCATE deletes all data in the table, but does not delete the structure of the table.

G: All synonyms based on the DEPT table are not deleted after dropping the table.

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

Question No.227

Which three arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database? (Choose three.)

  1. addition

  2. subtraction

  3. raising to a power

  4. finding the quotient

  5. finding the lowest value

Correct Answer: ACE

Question No.228

The following data exists in the PRODUCTS table: PROD_ID PROD_LIST_PRICE

– 123456 152525.99

You issue the following query:

SQLgt; SELECT RPAD(( ROUND(prod_list_price)), 10, #39;*#39;) FROM products

WHERE prod_id = 123456; What would be the outcome?

A. 152526**** B. **152525.99 C. 152525**

D. an error message

Correct Answer: A

Explanation:

The LPAD(string, length after padding, padding string) and RPAD(string, length after padding, padding string) functions add a padding string of characters to the left or right of a string until it reaches the specified length after padding.

Question No.229

You own a table called EMPLOYEES with this table structure: EMPLOYEE_ID NUMBER Primary Key

FIRST_NAME VARCHAR2(25)

LAST_NAME VARCHAR2(25) HIRE_DATE DATE

What happens when you execute this DELETE statement? DELETE employees;

  1. You get an error because of a primary key violation.

  2. The data and structure of the EMPLOYEES table are deleted.

  3. The data in the EMPLOYEES table is deleted but not the structure.

  4. You get an error because the statement is not syntactically correct.

Correct Answer: C

Explanation:

You can remove existing rows from a table by using the DELETE statement.

DELETE [FROM] table

[WHERE. condition];

Incorrect answer:

  1. Statement will not cause error

  2. Delete statement will not delete the table structure

D. Statement will not cause error

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

Question No.230

Which two statements are true regarding the USING clause in table joins? (Choose two.)

  1. It can be used to join a maximum of three tables.

  2. It can be used to restrict the number of columns used in a NATURAL join.

  3. It can be used to access data from tables through equijoins as well as nonequijoins.

  4. It can be used to join tables that have columns with the same name and compatible data types.

Correct Answer: BD

Explanation:

NATURAL JOIN operation

A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables.

If the SELECT statement in which the NATURAL JOIN operation appears has an asterisk (*) in the select list, the asterisk will be expanded to the following list of columns (in this order):

All the common columns

Every column in the first (left) table that is not a common column Every column in the second (right) table that is not a common column An asterisk qualified by a table name (for example, COUNTRIES.*) will be expanded to every column of that table that is not a common column. If a common column is referenced without being qualified by a table name, the column reference points to the column in the first (left) table if the join is an INNER JOIN or a LEFT OUTER JOIN. If it is a RIGHT OUTER JOIN, unqualified references to a common column point to the column in the second (right) table.

Syntax

TableExpression NATURAL [ { LEFT | RIGHT } [ OUTER ] | INNER ] JOIN {

TableViewOrFunctionExpression |

( TableExpression ) }

Examples

If the tables COUNTRIES and CITIES have two common columns named COUNTRY and COUNTRY_ISO_CODE, the following two SELECT statements are equivalent:

SELECT * FROM COUNTRIES NATURAL JOIN CITIES SELECT * FROM COUNTRIES JOIN CITIES

USING (COUNTRY, COUNTRY_ISO_CODE)

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

Related Posts