Get Full Version of the Exam
http://www.EnsurePass.com/1z0-061.html
Question No.221
Which statement is true regarding sub queries?
-
The LIKE operator cannot be used with single- row subqueries.
-
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?
-
UNIQUE
-
NOT NULL
-
CHECK
-
PRIMARY KEY
-
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?
-
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);
-
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);
-
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);
-
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:
-
Wrong statement with the keyword EXISTS
-
Wrong statement with the keyword EXISTS
-
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)
-
A WHERE clause can be used to restrict both rows and groups.
-
A WHERE clause can be used to restrict rows only.
-
A HAVING clause can be used to restrict both rows and groups.
-
A HAVING clause can be used to restrict groups only.
-
A WHERE clause CANNOT be used in a query of the query uses a HAVING clause.
-
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 :
-
Where clause cannot be use to restrict groups
-
A HAVING clause can only e used to restrict GROUPS.
-
WHERE clause cannot be use when there is group function, instead HAVING is to be use.
-
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.)
-
CREATE VIEW v3AS SELECT * FROM SALESWHERE cust_id = 2034WITH CHECK OPTION;
-
CREATE VIEW v1AS SELECT * FROM SALESWHERE time_id lt;= SYSDATE – 2*365WITH CHECK OPTION;
-
CREATE VIEW v2AS SELECT prod_id, cust_id, time_id FROM SALESWHERE time_id lt;= SYSDATE – 2*365WITH CHECK OPTION;
-
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.)
-
You cannot roll back this statement.
-
All pending transactions are committed.
-
All views based on the DEPT table are deleted.
-
All indexes based on the DEPT table are dropped.
-
All data in the table is deleted, and the table structure is also deleted.
-
All data in the table is deleted, but the structure of the table is retained.
-
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.)
-
addition
-
subtraction
-
raising to a power
-
finding the quotient
-
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;
-
You get an error because of a primary key violation.
-
The data and structure of the EMPLOYEES table are deleted.
-
The data in the EMPLOYEES table is deleted but not the structure.
-
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:
-
Statement will not cause error
-
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.)
-
It can be used to join a maximum of three tables.
-
It can be used to restrict the number of columns used in a NATURAL join.
-
It can be used to access data from tables through equijoins as well as nonequijoins.
-
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