1. | D. where C.COW_NAME = 'BESS' AND C.CARTON_NUM = C1.CARTON_NUM;
Explanation Two components are required in your where clause—you need a join clause and something that only pulls records from COW_MILK for BESS. The right answer is where C.COW_NAME = 'BESS' AND C.CARTON_NUM = C1.CARTON_NUM;. Another choice is similar to this one, but because it uses the not equal (<>) clause for getting information only for BESS, it is not the choice you want. The other two choices are incomplete, and therefore wrong. (Topic 2.1) |
2. | D. The insert statements contain duplicate data due to the reset sequence.
Explanation The correct answer is that the insert statements contain duplicate data due to the reset sequence. When you drop and re-create the sequence from its original code, you reset the start value for that sequence. Subsequent insert statements will then attempt to add rows where the value in the primary key is duplicated information. The question has no information about read-only status, so you should assume that the answer concerning the table being read-only is not correct. Dropping a sequence does nothing to a table's primary key—no relationship exists between the two. Finally, although it is true that any cached sequence values that existed when the sequence was dropped are now unusable, this point has little relevance to the question at hand. (Topic 12.1) |
3. | B. exists
Explanation Only when using the exists statement must you use a correlated subquery. Although you can use a subquery with your use of in, you are not required to do so because you can specify a set of values instead. The between keyword indicates a range of values and does not permit the use of a subquery. The like keyword is used for wildcard comparisons and also does not permit the use of a subquery. (Topic 2.1) |
4. | D. abs()
Explanation All of the functions except for abs() will give you a result of 4093 when you pass them 4093.505. abs() returns the absolute value of the number you pass into the function. round() can give you a result of 4093 if you also pass in a second parameter defining the precision to which you want to round the function, whereas trunc() will give you a result of 4093 with only 4093.505 as input. floor() gives you a result of 4093, because it is the logical opposite of the ceil() function. (Topic 3.2) |
5. | B. Use accept to capture the name value for each run.
Explanation The accept command is the best way to handle the situation. Although you could use define to assign a value to a variable used throughout the script, only accept enables you to dynamically enter a value for that variable. Lexical substitutions identified with the & character will only work for the current statement, meaning that the same value assigned in one statement will not be used in the next statement unless you reenter it. (Topic 7.3) |
6. | B. truncate
Explanation Once a truncate operation is complete, that's it—the change is made and saved. This is because truncate is not a DML operation that can be performed as part of a transaction. The truncate command is a DDL operation, and as such, it has an implied commit at the end of its execution. If you want to get the data back after truncating, you need to recover it. For the other operations listed as choices in this question—insert, update, and delete statements—Oracle enables you to discard the changes using the rollback command. (Topic 9.5) |
7. | A. savepoint
Explanation savepoint operations simply act as logical breakpoints in a transaction. They do not cause Oracle to save or discard data, but merely act as a breakpoint with which you can perform partial transaction rollbacks later. The set transaction and commit commands indicate the beginning of a new transaction. Creating a new session with Oracle implicitly begins a transaction as well. (Topic 8.6) |
8. | B. A from clause
Explanation No SQL statement can survive without a from clause. For this reason, Oracle provides you with the DUAL table, so that you can perform arithmetic operations on expressions and not on table data while still satisfying this syntactic construct. Because this statement already has a select clause, you don't need to add another. The where clause is optional, but because the statement already has one, you don't need to add another. Finally, your SQL statement does not require an order by clause. (Topic 1.2) |
9. | A. set transaction
Explanation The set transaction command is used to define the transaction state to be read-only. rollback and commit statements are used to end the transaction. The savepoint command denotes logical breakpoints for the transaction. (Topic 8.6) |
10. | C. Bitmap index
Explanation Bitmap indexes work well in situations where the data in the column is static. In this case, the column contains gender information, which rarely changes. The number of distinct possible values is limited to only two as well. Thus, this column is a bad candidate for B-tree indexes of any sort, but perfect for bitmap indexes. Remember that B-tree indexes work well for columns with high cardinality or number of distinct values corresponding to the overall number of entries in the column. (Topic 12.2) |
11. | A. Parent to child
Explanation This question describes the relationship between the INVOICE and INVOICE_ITEM table, and the appropriate answer is parent to child. This is because the relationship described between invoices and invoice items is optional, given that invoices may have no invoice items, but that all invoice items must have a corresponding invoice. (Topic 10.2) |
12. | D. ACTIVE
Explanation The decode() function acts as a case statement. The first parameter indicates the column whose values you want decoded. If the value in the column equals parameter 2, then decode() returns parameter 3. If the value in the column equals parameter 4, decode() returns parameter 5, and so on. If the value in the column doesn't equal any of the other parameters specified, then decode() returns the default value specified as the last parameter. Thus, because the column value is not specified for any of the parameters, the returned value is the default, ACTIVE. (Topic 3.2) |
13. | D. where COL_A in (select NUM from TAB_OF_NUMS)
Explanation The where clause in choice D is an excellent example of the definition of a subquery, which is the example being asked for in the question. Choice A is not a comparison operation between a column and a set of values, because only one value is being compared. Choice B is a comparison of a column to a set of values, but the set is static and defined at the time the query is issued. Choice C is a range-comparison operation, a variant on choice B, and therefore also wrong. Only choice D enables Oracle to dynamically generate the list of values to which COL_A will be compared. (Topic 6.2) |
14. | D. select NAME from CONTESTANT where COUNTRY in (select COUNTRY from MEDALS where NUM_GOLD + NUM_SILVER + NUM_BRONZE > 10)
Explanation The query in choice D is correct because it contains the subquery that correctly returns a subset of countries that have contestants who won ten or more medals of any type. Choice A is incorrect because it contains a join operation, not a subquery. Choice B is simply a rewrite of choice A to use a multiple-row subquery, but does not go far enough to restrict return data. Choice C is a single-row subquery that does essentially the same thing as choice B. (Topic 6.4) |
15. | C. Multiple-column subquery, the youngest contestant from all countries
Explanation Because the main query compares against the results of two columns returned in the subquery, this is a multiple-column subquery that will return the youngest contestant from every country in the table. This multiple-column subquery is also a multiple-row subquery, but because the defining factor is that two columns are present, you should focus more on that fact than on the rows being returned. This fact eliminates choices A and B. The subquery does return multiple rows, however. You should also be sensitive to the fact that the main query must use an in clause, not the equal sign (=), making choice D incorrect as well. (Topic 6.3) |
16. | A. SOO
Explanation The correct answer is SOO because the subquery operation specified by the in clause ignores NULL values implicitly. Thus, because SOO has no country defined, that row is not selected as part of the subquery. As a result, SOO won't show up as having the youngest age in the results of this query. (Topic 6.4) |
17. | B. Table
Explanation The object being referred to is a table. A table has many columns, each of which is functionally dependent on the key column. Choice A is incorrect because a synonym is simply another name you can use to reference a table, not an actual table itself. A sequence is a number generator in Oracle that, again, does not require storage in a segment other than a dictionary segment, making choice C incorrect. Finally, a view is similar to a table in that it contains many columns, each of which is functionally dependent on the key. However, views contain no data needing to be stored in a segment, so choice D is wrong as well. (Topic 9.1) |
18. | B. cube
Explanation The cube keyword included in a group by clause of a SQL statement in Oracle8i enables you to perform N-dimensional cross-tabulations within the Oracle database, returning the result set directly to the client. This keyword is useful in queries within data warehouses. Choice C is incorrect because even though the rollup keyword was also added to SQL queries in Oracle8i, this keyword supports subtotal and grand total calculations of grouped data. Although the having expression is also available in group operations, choice A is incorrect because you do not need to define a having clause in order to use either cube or rollup. Finally, choice D is incorrect because the trim() function combines the capabilities of ltrim() and rtrim(). (Topic 5.3) |
19. | B. Reverse-key indexes
Explanation A reverse-key index is one where the contents of the indexed column are reversed. This gives a higher amount of lead-in selectivity than a straight B-tree index would, because the cardinality of the root node in the B-tree would be low. This is based on the fact that most records would begin with the digit 1 (recall the question content if you don't understand why), whereas the reverse of that key would have greater cardinality. Be careful of choice A because although cardinality is high, choice B gives a better option for performance. Choice C is incorrect because bitmap indexes are designed for low-cardinality records like status or gender. Finally, choice D indicates an index type that wouldn't suit this situation. (Topic 12.2) |
20. | A. VARCHAR2
Explanation Because the text blocks are within the size limits imposed in Oracle8i for the VARCHAR2 datatype, it is best to use the scalar type rather than a large object for simplicity sake. If the block were larger than 4,000 bytes, you would most likely use a CLOB, but because the size requirement is less than 4,000 bytes, choice C is incorrect. You would use a BLOB to store binary large objects, making choice B incorrect. Finally, the text block is not stored as an external file (you would not use the BFILE type), making choice D incorrect. (Topic 9.3) |
21. | B. The index is dropped.
Explanation Like automatically generated indexes associated with a table's primary key, the indexes created manually on a table to improve performance will be dropped if the table is dropped. Choices A, C, and D are therefore invalid. (Topic 12.2) |
22. | C. Columns with high cardinality are handled well by B-tree indexes.
Explanation Columns with low cardinality are the bane of B-tree indexes, eliminating choice A. Furthermore, bitmap indexes are primarily used for performance gains on columns with low cardinality, eliminating choice B. (Topic 12.2) |
23. | D. Drop and recreate the view with references to select more columns.
Explanation Choice A is incorrect because adding columns to the underlying table will not add columns to the view, but will likely invalidate the view. Choice B is incorrect because the alter view statement simply recompiles an existing view definition, whereas the real solution here is to change the existing view definition by dropping and recreating the view. Choice C is incorrect because a correlated subquery will likely worsen performance, and underscores the real problem—a column must be added to the view. (Topic 11.2) |
24. | C. maxvalue
Explanation The maxvalue option is a valid option for sequence creation. Choices A and B are both part of the create user statement, whereas choice D is a part of a constraint declaration in an alter table or create table statement. (Topic 12.1) |
25. | F. This statement contains no errors.
Explanation Even though the reference to with check option is inappropriate, considering that inserts into complex views are not possible, the statement will not actually produce an error when compiled. Therefore, the view has no errors. This is not something that can be learned. It requires hands-on experience with Oracle. (Topic 11.4) |
26. | A. references
Explanation The references privilege gives the user the ability to refer back to your table in order to link to it via a foreign key from his or her table to yours. Choice B is incorrect because the index privilege enables the user to create an index on a table, whereas choice C is incorrect because the select privilege enables users to query data in your table. Finally, choice D is incorrect because the delete privilege is only required for enabling the other user to delete data into your table. (Topic 13.3) |
27. | A, B, and C. Roles can be granted to other roles, privileges can be granted to roles, and roles can be granted to users.
Explanation Choice D is the only option not available to managing roles. Roles cannot be granted to synonyms. (Topic 13.2) |
28. | C. It is equal to NEXTVAL.
Explanation Once NEXTVAL is referenced, the sequence increments the integer and changes the value of CURRVAL to be equal to NEXTVAL. (Topic 12.1) |
29. | D. with check option
Explanation The appropriate clause is with check option. You can add this clause to a create view statement so that the view will not let you to add rows to the underlying table that cannot then be selected in the view. The with admin option and with grant option clauses are used to assign administrative ability to users along with granting them a privilege. The with security option is a work of fiction—it does not exist in Oracle. (Topic 11.4) |
30. | C. The statement fails due to the primary key constraint.
Explanation It should be obvious that the statement fails—the real question here is why. The reason is because of the primary key constraint on UPC_CODE. As soon as you try to add a duplicate record, the table will reject the addition. Although the view has with check option specified, this is not the reason the addition fails. It would be the reason an insert fails if you attempt to add a record for a day other than today, however. (Topic 10.2) |
31. | A. drop view
Explanation When a table is dropped, Oracle eliminates all related database objects, such as triggers, constraints, and indexes. However, Oracle does not remove views. Views are actually considered separate objects, and although the view will not function properly after you drop the underlying table, Oracle will keep the view around after the table is dropped. (Topic 11.2) |
32. | C. The select receives NO ROWS SELECTED.
Explanation Although the query will succeed (translation: you won't receive an error), you must beware of the distracter in choice B. In reality, choice C is the better answer because it more accurately identifies what really will occur when you issue this statement. This view will behave as any select statement would when you list criteria in the where clause that no data satisfies—by returning NO ROWS SELECTED. This is not an error condition, but you wouldn't call it a successful search for data either, making both those choices incorrect. Finally, select statements never add data to a table. (Topic 1.2) |
33. | B and D. unique constraints and primary keys
Explanation Every constraint that enforces uniqueness creates an index to assist in the process. The two integrity constraints that enforce uniqueness are unique constraints and primary keys. (Topic 10.2) |
34. | C. ALL_IND_COLUMNS
Explanation This view is the only one listed that provides column positions in an index. Because primary keys create an index, the index created by the primary key will be listed with all the other indexed data. Choice A is incorrect because no view exists in Oracle called ALL_PRIMARY_KEYS. Choice B is incorrect because, although USER_CONSTRAINTS lists information about the constraints in a database, it does not contain information about the index created by the primary key. Choice D is incorrect because ALL_TABLES contains no information related to the position of a column in an index. (Topic 12.2) |
35. | C. A table named ANIMALS is created in the ANJU schema with the same data as the ANIMALS table owned by MASTER.
Explanation This question requires you to look carefully at the create table statement in the question and to know some things about table creation. First, a table is always created in the schema of the user who created it. Second, because the create table as select clause was used, choices B and D are both incorrect because they identify the table being created as something other than ANIMALS, among other things. Choice A identifies the schema into which the ANIMALS table will be created as MASTER, which is incorrect for the reasons just stated. (Topic 9.2) |
36. | A. insert into EMPLOYEE values (59694,'HARRIS', NULL);
Explanation This choice is acceptable because the positional criteria for not specifying column order are met by the data in the values clause. When you would like to specify that no data be inserted into a particular column, one method of doing so is to insert a NULL. Choice B is incorrect because not all columns in the table have values identified. When using positional references to populate column data, values must be present for every column in the table. Otherwise, the columns that will be populated should be named explicitly. Choice C is incorrect because when a column is named for data insert in the insert into clause, a value must definitely be specified in the values clause. Choice D is incorrect because using the multiple row insert option with a select statement is not appropriate in this situation. (Topic 8.2) |
37. | A. Two tables in the database are named VOUCHER and VOUCHER_ITEM, respectively.
Explanation This choice implies the use of a naming convention similar to the one we discussed earlier, where the two tables with a foreign key relationship are given similar names. Although it is not guaranteed that these two tables are related, the possibility is strongest in this case. Choice B implies the same naming convention, and because the two tables' names are dissimilar, it is unlikely that the tables are related in any way. Choice C is incorrect because the date a table is created has absolutely no bearing on what function the table serves in the database. Choice D is incorrect because two tables cannot be related if no common columns exist between them. (Topic 10.1) |
38. | A, B, and D. CHAR, VARCHAR2, and NUMBER
Explanation BOOLEAN is the only invalid datatype in this listing. Although BOOLEAN is a valid datatype in PL/SQL, it is not a datatype available in the Oracle database, meaning that you cannot create a column in a table that uses the BOOLEAN datatype. (Topic 9.3) |
39. | D. The delete statement removes all records from the table.
Explanation Only one effect is produced by leaving off the where clause from any statement that permits one: The requested operation is performed on all records in the table. (Topic 8.4) |
40. | C. GOOD_NAME VARCHAR2(20) check(GOOD_NAME in (select NAME from AVAIL_GOODS)),
Explanation A check constraint cannot contain a reference to another table, nor can it reference a virtual column, such as ROWID or SYSDATE. The other lines of the create table statement contain correct syntax. (Topic 10.2) |
41. | A. Locks
Explanation Locks are the mechanisms that prevent more than one user at a time from making changes to the database. All other options refer to the commands that are issued to mark the beginning, middle, and end of a transaction. Remember, the commit and rollback keywords end the current transaction and begin a new one, whereas the savepoint keyword marks a logical breakpoint within the transaction. (Topic 8.6) |
42. | A. Use the alter table statement.
Explanation The alter table statement is the only choice offered that enables the developer to increase the number of columns per table. Choice B is incorrect because setting a column to all NULL values for all rows does simply that. Choice C is incorrect because increasing the adjacent column sizes simply increases the sizes of the columns. Choice D is incorrect because the listed steps outline how to add a column with a not NULL constraint, something not specified by the question. (Topic 9.4) |
43. | B. Truncate the table.
Explanation Choices A and C may work, but an upgrade of hardware and software will cost far more than truncating the table (choice B). Choice D is partly correct, as some change will be required to the high-water mark, but the change is to reset, not eliminate entirely. (Topic 9.5) |
44. | A. unique
Explanation Only unique and primary key constraints require Oracle to generate an index that supports or enforces the uniqueness of the column values. foreign key, check, and not NULL constraints do not require an index. (Topic 10.1) |
45. | B. All values in the referenced column in the parent table must be present in the referencing column in the child.
Explanation Referential integrity is from child to parent, not vice versa. The parent table can have many values that are not present in child records, but the child record must correspond to something in the parent. Thus, the correct answer is all values in the referenced column in the parent table must be present in the referencing column in the child. (Topic 10.1) |
46. | B. Values must be part of a fixed set defined by create or alter table.
Explanation A check constraint may only use fixed expressions defined when you create or alter the table with the constraint definition. The reserved words like sysdate and user, or values from a lookup table, are not permitted, making those answers incorrect. Finally, NULL values in a column are constrained by not NULL constraints, a relatively unsophisticated form of check constraints. (Topic 10.1) |
47. | B. sqrt()
Explanation Square root operations are performed on one column value. (Topic 3.2) |
48. | B. The tables in the join need to have common columns.
Explanation It is possible that a join operation will produce no return data, just as it is possible for any select statement not to return any data. Choices A, C, and D represent the spectrum of possibilities for shared values that may or may not be present in common columns. However, joins themselves are not possible without two tables having common columns. (Topic 4.1) |
49. | D. Until the session completes
Explanation A variable defined by the user during a session with SQL*Plus will remain defined until the session ends or until the user explicitly undefines the variable. (Topic 1.3) |
50. | B and C. Alter the prompt clause of the accept command and enter a new prompt in the login.sql file.
Explanation Choice D should be eliminated immediately, leaving the user to select between choices A, B, and C. Choice A is incorrect because config.ora is a feature associated with Oracle's client/server network communications product. Choice C is correct because you can use the set sqlprompt command within your login.sql file. This is a special Oracle file that will automatically configure aspects of the SQL*Plus session, such as the default text editor, column and NLS data formats, and other items. (Topic 7.1) |
51. | A and C. select * from EMPLOYEE where EMPID = &empid; and select * from EMPLOYEE where EMPID = (select empid from invoice where INVOICE_NO = 4399485);
Explanation Choice A details the use of a runtime variable that can be used to have the user input appropriate search criteria after the statement has begun processing. Choice C details the use of a subquery that enables the user to select unknown search criteria from the database using known methods for obtaining the data. Choice B is incorrect because the statement simply provides a known search criterion; choice D is incorrect because it provides no search criteria at all. (Topic 7.1) |
52. | A. Ampersand
Explanation The ampersand (&) character is used by default to define runtime variables in SQL*Plus. (Topic 7.1) |
53. | C. select e.empid, d.head from EMPLOYEE e, dept d where e.dept# = d.dept# (+);
Explanation Choice C details the outer join operation most appropriate to this user's needs. The outer table in this join is the DEPT table, as identified by the (+) marker next to the DEPT# column in the comparison operation that defines the join. (Topic 4.2) |
54. | B, C, and D. To exclude certain data groups based on known criteria, to include certain data groups based on unknown criteria, and to include certain data groups based on known criteria
Explanation All exclusion or inclusion of grouped rows is handled by the having clause of a select statement. Choice A is not an appropriate answer because sort order is given in a select statement by the order by clause. (Topic 5.4) |
55. | B. Produced as a result of a join select statement with no where clause
Explanation A Cartesian product is the result dataset from a select statement where all data from both tables is returned. A potential cause of a Cartesian product is not specifying a where clause for the join select statement. (Topic 4.1) |
56. | D. Issuing the set define command
Explanation Choice A is incorrect because a change to the init.ora file alters the parameters Oracle uses to start the database instance. Choice B is incorrect because, although the login.sql file can define many properties in a SQL*Plus session, the character that denotes runtime variables is not one of them. Choice C is incorrect because the define command is used to define variables used in a session, not an individual statement. (Topic 1.3) |
57. | B. set role sales_analyzer;
Explanation The problem likely occurs because SALES_ANALYZER was not the default role assigned to THOMAS. She can enable this role using the set role command, making choice B the correct answer. Because the appropriate privileges are already granted to THOMAS via the role, the grant command needn't be issued, making choices C and D incorrect. Finally, choice A is incorrect because a user cannot alter his or her own user settings using the alter user statement to change role information; he or she can only use the alter user statement to change his or her own password. (Topic 13.2) |
58. | D. Immediately after the privilege is granted to SALES_ANALYZER
Explanation Access to objects granted by giving the appropriate privilege to the appropriate role takes effect immediately. DAVIS does not need to log into Oracle again, making choice A incorrect. Choice B is incorrect because the SALES_ANALYZER role needn't be granted to DAVIS again. Finally, choice C is incorrect because the privilege needn't be granted to SALES_ANALYZER again. (Topic 13.3) |
59. | D. grant create public synonym to davis;
Explanation DAVIS doesn't need the ability to create public synonyms in order to create tables. Thus, choice D is correct. However, DAVIS will need a user ID setup on Oracle by IMADBA, making choice A incorrect. DAVIS will also need the ability to log into Oracle using the privilege identified in choice B and the ability to create tables given by the privilege in choice C. Thus, those other answers are incorrect. (Topic 12.3) |
60. | C. create session
Explanation The create session privilege enables you to connect to the Oracle database, making C the correct answer. Choices A and B both identify Oracle-created roles that already have the appropriate privileges for logging into Oracle granted to them, but remember—these are roles, not privileges themselves. Finally, choice D is incorrect because the references privilege is an object privilege that does not give you the ability to connect to Oracle. (Topic 13.3) |
RSS Feed (xml)
No comments:
Post a Comment