1z0-007 有疑问的一些题（3）

? QUESTION NO: 74
Which three statements about subqueries are true? (Choose three)
A. A single row subquery can retrieve only one column and one row.
B. A single row subquery can retrieve only one row but many columns.
C. A multiple row subquery can retrieve multiple rows and multiple columns.
? D. A multiple row subquery can be compared by using the “>” operator.
E. A single row subquery can use the IN operator.
F. A multiple row subquery can use the “=” operator.
Answer: B, C, D
Explanation:
A single row sub-query can retrieve only one row but many columns. A multiple row subquery
can retrieve one row or multiple rows and multiple columns. A multiple row sub-query
can be compared by using the “>” operator.  
Incorrect Answers
A: A single row sub-query can retrieve only one row, but many columns..
E: A single row sub-query cannot use the IN operator.
F: A multiple row sub-query cannot use the “=” operator.

It seems we can only choose B C D .  The reason for D is right is that :A multiple row subquery can be compared by using the “>ALL”or “>ANY” operator. Without ALL or ANY it cannot be right.



? QUESTION NO: 79
Which operator can be used with a multiple-row subquery?
A. =
B. LIKE
C. BETWEEN
D. NOT IN
E. IS
F. <>
Answer: D
Explanation:
Only NOT IN operator can be used with a multi-row sub-query. All others may be used with
single-row sub-query only.
Incorrect Answers
A: When sub-queries are linked to the parent by equality comparisons, the parent query
expects only one row of data from the sub-query, so “=” operator cannot be used with a
multiple-row sub-query.
? B: Operator LIKE will work only with single-row sub-queries.
C: Operator BETWEEN will not work with a multi-row sub-queries.
E: Operator LIKE is used only for single-row sub-queries.
F: When sub-queries are linked to the parent by equality comparisons, the parent query
expects only one row of data from the sub-query, so “<>” operator cannot be used with a
multiple-row sub-query.

I agree this answer D. But I cannot understand why Operator LIKE will work only with single-row sub-queries.  



? QUESTION NO: 86
Which three statements correctly describe the functions and use of constraints? (Choose
three.)
A. Constraints provide data independence.
B. Constraints make complex queries easy.
C. Constraints enforce rules at the view level.
D. Constraints enforce rules at the table level.
E. Constraints prevent the deletion of a table if there are dependencies.
F. Constraints prevent the deletion of an index if there are dependencies.
Answer: C, D, E
Explanation:
Constraints have functions to enforce rules at the view and table levels and to prevent the
deletion of data if dependencies exist between tables.
Incorrect Answers
A: Constraints are used to put dependencies on data.
B: Constrains are not used to make complex queries more easy.
F: Constraints does not prevent the deletion of an index if there are dependencies.

? QUESTION NO: 89
What is true about joining tables through an equijoin?
A. You can join a maximum of two tables through an equijoin.
B. You can join a maximum of two columns through an equijoin.
C. You specify an equijoin condition in the SELECT or FROM clauses of a SELECT
statement.
D. To join two tables through an equijoin, the columns in the join condition must be
primary key and foreign key columns.
E. You can join n tables (all having single column primary keys) in a SQL statement by
specifying a minimum of n-1 join conditions.
Answer: E
Explanation:
For N joined tables using Oracle or ANSI/ISO syntax for table joins, you need at least N-1
equijoin conditions in the WHERE clause of your SELECT statement or N-1 JOIN
table_name ON join_condition clauses in order to avoid a Cartesian product, respectively.
Incorrect Answers
A: There is no maximum on two tables to join them through an equijoin.
B: There is no maximum on two columns to join them through an equijoin.
C: You can specify an equijoin condition only in the FROM clauses of a SELECT statement.
D: There is no limitation about primary key and foreign key for the columns to use them for
an equijoin.

? QUESTION NO: 99
Which three are true regarding the use of outer joins? (Choose three.)
A. You cannot use IN operator in a condition that involves an outerjoin.
B. You use (+) on both sides of the WHERE condition to perform an outerjoin.
C. You use (*) on both sides of the WHERE condition to perform an outerjoin.
D. You use an outerjoin to see only the rows that do not meet the join condition.
E. In the WHERE condition, you use (+) following the name of the column in the table
without matching rows, to perform an outerjoin.
F. You cannot link a condition that is involved in an outerjoin to another condition by
using the OR operator.
Answer: D, E, F
Explanation:
You can use an outerjoin to see only the rows that do not meet the join condition. In the
WHERE condition, you use (+) following the name of the column in the table without
matching rows, to perform an outerjoin. You cannot link a condition that is involved in an
outerjoin to another condition by using the OR operator.
Incorrect Answers
A: You can use IN operator in a condition that involves an outerjoin.
B: You use (+) following the name of the column in the table without matching rows, but not
on both sides of the WHERE condition to perform an outerjoin.
C: You don’t use (*) to define outerjoin operation.

My answer is A E F
A.        the book says:  A condition involving an outer join cannot use the IN operator or be linked to another condition by the OR operator.
B.        the outer join operator can appear only one side of the expression
C.        You use an outer join to ALSO (note: it is not ONLY) see rows that do not meet the join condition



? QUESTION NO: 106
Which two statements complete a transaction? (Choose two)
A. DELETE employees;
B. DESCRIBE employees;
C. ROLLBACK TO SAVEPOINT C;
D. GRANT SELECT ON employees TO SCOTT;
E. ALTER TABLE employees
SET UNUSED COLUMN sal;
F. Select MAX(sal)
FROM employees
WHERE department_id = 20;
Answer: C, E
Explanation:
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. Also after the ROLLBACK command is issued, a new transaction is
started implicitly by the database session.
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.
D: The GRANT command is data-control language (DCL) command to control user access to
data. It will not complete a transaction.
F: SELECT command is used to retrieve data. It does not complete a transaction.

My answer is D E
Book 8-37
An automatic Commit occurs under:
      -    DDL statement is issued
-        DCL statement is issued(GRANT and REVOKE)
-        Normal Exit from Isql *Plus


The key is what does it mean for ‘complete a transaction’, does roll back  mean complete a transaction?



? QUESTION NO: 110
You are granted the CREATE VIEW privilege. What does this allow you to do?
A. Create a table view.
B. Create a view in any schema.
C. Create a view in your schema.
D. Create a sequence view in any schema.
E. Create a view that is accessible by everyone.
F. Create a view only of it is based on tables that you created.
Answer: C
Explanation:
You can create a view in your own schema only if you are granted the CREATE VIEW
privilege.
Incorrect Answers
A: You can create a view in your own schema only.
B: You can create a view in your own schema only, not in any schema.
D: There is no sequence view in Oracle.
E: You cannot create a view that is accessible by everyone. You will need specially grant
SELECT privileges on this view for everyone. How to do it?
F: You can create a view in your own schema, but not only for tables in your schema. You
can use object from other users schemas if you have privileges to retrieve data from them.

? QUESTION NO: 112
Which two statements are true about WHERE and HAVING clauses? (Choose two)
A. A WHERE clause can be used to restrict both rows and groups.
B. A WHERE clause can be used to restrict rows only.
C. A HAVING clause can be used to restrict both rows and groups.
D. A HAVING clause can be used to restrict groups only.
E. A WHERE clause CANNOT be used in a query of the query uses a HAVING clause.
F. A HAVING clause CANNOT be used in subqueries.
Answer: B, C
Explanation :
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
1. rows are grouped
2. the group function is applied to the group
3. the group that match the criteria in the Having clause are displayed.
WHERE clause cannot be use to restrict groups
HAVING clause use to restrict groups
WHERE clause cannot be use when there is group functions.
Incorrect Answers :
A. Where clause cannot be use to restrict groups
D. When HAVING clause is use rows are grouped as well.
E. WHERE clause cannot be use when there is group function, instead HAVING is to be use.
F. There is no constraint to use HAVING clause in a subqueries.

My question is: A HAVING clause can be used to restrict both rows and groups.or
A HAVING clause can be used to restrict groups only?

? QUESTION NO: 114
Which SQL statement accepts user input for the columns to be displayed, the table
name, and WHERE condition?
A. SELECT &1, "&2"
FROM &3
WHERE last_name = '&4';
B. SELECT &1, '&2'
FROM &3
WHERE '&last_name = '&4' ';
C. SELECT &1, &2
FROM &3
WHERE last_name = '&4';
D. SELECT &1, '&2'
FROM EMP
WHERE last_name = '&4';
Answer: C
Explanation :
In a WHERE clause, date and characters values must be enclosed within single quotation
marks.
Sample of the correct syntax
SELECT EMPLOYEE_ID, &COLUMN_NAME
FROM EMPLOYEES
Incorrect Answers :
A. Incorrect use of " symbol
B. Incorrect use of ' symbol
D.        No input for table name as EMP has been use in the statement.

The problem is &1 itself is not  legal


? QUESTION NO: 116
The CUSTOMERS table has these columns:
CUSTOMER_ID NUMBER (4) NOT NULL
CUSTOMER_NAME VARCHAR2 (100) NOT NULL
STREET_ADDRESS VARCHAR2 (150)
CITY_ADDRESS VARHCAR2 (50)
STATE_ADDRESS VARCHAR2 (50)
PROVINCE_ADDRESS VARCHAR2 (50)
COUNTRY_ADDRESS VARCHAR2 (50)
POSTAL_CODE VARCHAR2 (12)
CUSTOMER_PHONE VARCHAR2 (20)
The CUSTOMER_ID column is the primary key for the table.
You need to determine how dispersed your customer base is.
Which expression finds the number of different countries represented in the
CUSTOMERS table?
A. COUNT(UPPER(country_address))
B. COUNT(DIFF(UPPER(country_address)))
C. COUNT(UNIQUE(UPPER(country_address)))
D. COUNT DISTINTC UPPER(country_address)
E. COUNT(DISTINTC (UPPER(country_address)))
Answer: C
Explanation :
DISTINCT keyword in the select clause will eliminate duplicate rows.
UNIQUE will generate the same result as DISTINCT.
Incorrect Answers :
A. The statement will not eliminate the duplicate row in the table.
B. DIFF is invalid identifier
D. The statement missing parenthess
E. DISTINTC is invalid identifier

UNIQUE function is not mentioned in the book.


?  QUESTION NO: 125
Examine the description of the EMPLOYEES table:
EMP_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(30) NOT NULL
FIRST_NAME VARCHAR2(30)
DEPT_ID NUMBER(2)
Which statement produces the number of different departments that have employees
with last name Smith?
A. SELECT COUNT(*) FROM employees WHERE last_name='Smith';
B. SELECT COUNT (dept_id) FROM employees WHERE last_name='Smith';
C. SELECT DISTINCT(COUNT(dept_id)) FROM employees WHERE last_name='Smith';
D. SELECT COUNT(DISTINCT dept_id) FROM employees WHERE last_name='Smith';
E. SELECT UNIQUE(dept_id) FROM employees WHERE last_name='Smith';
Answer: C
Explanation : This is the only answer that provide the correct result.
Incorrect answer :
A This will give duplication of records;
B This will give duplication of records on dept_id
D ORA-00936 : missing expression
E The statement will produce value of dept_id

The answer should be D.

? QUESTION NO: 127
Which statement adds a constraint that ensures the CUSTOMER_NAME column of the
CUSTOMERS table holds a value?
A. ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name
IS NOT NULL;
B. ALTER TABLE customers MODIFY CONSTRAINT cust_name_nn CHECK
customer_name IS NOT NULL;
C. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn
NOT NULL;
D. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn IS
NOT NULL;
E. ALTER TABLE customers MODIFY name CONSTRAINT cust_name_nn NOT NULL;
F. ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name
NOT NULL;
Answer: F
Explanation :
to alter the constraint you must
ALTER TABLE table_name ADD CONSTRAINT

Incorrect answer :
A there no condition as IS NOT NULL
B there is no such MODIFY keyword
C there is no such MODIFY keyword
D there is no such MODIFY keyword
E there is no such MODIFY keyword

Book 10-17
Use the ALTER TABLE statement to:
.Add  a  NOT NULL constraint by using the MODIFY clause.


? QUESTION NO: 133
Which four are attributes of single row functions? (Choose four.)
A. cannot be nested
B. manipulate data items
C. act on each row returned
D. return one result per row
E. accept only one argument and return only one value
F. accept arguments which can be a column or an expression
Answer: B, C, D, E
Explanation :
manipulate data items, act on each row returned, return one result per row, accept only one
argument and return only one value.
Incorrect answer :
A is not single row attributes
F is not single row attributes

My answer is BCDF


? QUESTION NO: 142
What is true of using group functions on columns that contain NULL values?
A. Group functions on columns ignore NULL values.
B. Group functions on columns returning dates include NULL values.
C. Group functions on columns returning numbers include NULL values.
D. Group functions on columns cannot be accurately used on columns that contain NULL
values.
E. Group functions on columns include NULL values in calculations if you use the keyword
INC_NULLS.
Answer: A
Explanation : group functions on column ignore NULL values

The book says: All group functions ignore null values. But how about count(*)


? QUESTION NO: 146
Which SQL statement returns a numeric value?
A. SELECT ADD_MONTHS(MAX(hire_Date), 6) FROM EMP;
B. SELECT ROUND(hire_date)FROM EMP;
C. SELECT sysdate-hire_date FROM EMP;
D. SELECT TO_NUMBER(hire_date + 7)FROM EMP;
Answer: C
Explanation :
DATE value subtract DATE value will return numeric value.
Incorrect answer :
A does not return numeric value
B does not return numeric value
D does not return numeric value

Why D does not return numberic value?  You can convert a date to a number.

?  QUESTION NO: 147
What are two reasons to create synonyms? (Choose two.)
A. You have too many tables.
B. Your tables are too long.
C. Your tables have difficult names.
D. You want to work on your own tables.
E. You want to use another schema's tables.
F. You have too many columns in your tables.
Answer: A, C
Explanation :
create a synonyms when the table name is difficult or you have too many tables

My answer is: C E. the book says: with synonyms, you can: ease referring to a table owned by another user, and shorten lenthy object names.


? QUESTION NO: 148
What is true about updates through a view?
A. You cannot update a view with group functions.
B. When you update a view group functions are automatically computed.
C. When you update a view only the constraints on the underlying table will be in effect.
D. When you update a view the constraints on the views always override the constraints on
the underlying tables.
Answer: C
Explanation :
when you update a view only the constraints on the underlying table will be in effect.

My answer is A:
On the book 11-5 You cannot modify data in a view if it contains group functions.