PlSql

What is the third stage of the system development cycle? may be correct) A) B) C) D) E) Build and document Design Transition Strategy and analysis Production (one or more Answer .. A is correct: Build and document Explanation The correct answer is a. The build and document step is the third stage of the system development cycle. The following is the order of the stages of the system development cycl5) 1. 2. 3. 4. 5. Strategy and analysis Design Build and document Transition Production (one or more may be 2)When will a PL/SQL block not compile? [Choose two] correct) A) B) C) D) When When When When an implicit cursor retrieves only one row an implicit cursor retrieves more than one row the data types within a SELECT statement are inconsistent an embedded SELECT statement is missing a required clause Answer .. C is correct: When the data types within a SELECT statement are inconsistent D is correct: When an embedded SELECT statement is missing a required clause Explanation The correct answers are c and d. An error in the SELECT statement format for a cursor will result in a syntax error, prohibiting the build of the PL/SQL block. Answer a is incorrect because retrieving only one row is the correct behavior for an implicit cursor; thus, it wouldn't raise an exception or prohibit a block from being built internally. Answer b is incorrect because returning multiple rows to an implicit cursor will raise an exception when the block is executed, but it won't cause a compilation error. 3)What is the purpose of the SQL*Plus command GET? A) Get the contents of a previously saved operating system file into the buffer. B) Get a printer assignment. C) Get the contents of the buffer for editing. D) Return a storage location for the buffer contents. Correct Answer(s): A) Get the contents of a previously saved operating system file into the buffer. Explanation: The correct answer is a. The purpose of the SQL*Plus GET command is to get the contents of a previously saved operating system file into the buffer. Answer b is incorrect because getting a printer assignment is the purpose of the SPOOL command. Answer c is incorrect because getting the contents of the buffer for editing is the purpose of the LIST command. Answer d is incorrect because returning a storage location for the buffer contents is not a function of any SQL*Plus command. 4)What is the purpose of a referential integrity constraint? A) Enforce business rules. B) Ensure that entities are internally consistent. C) Validate data entries of a specified type. D) Enforce the rule that a child foreign key must have a valid parent primary key. Correct Answer(s): D) Enforce the rule that a child foreign key must have a valid parent primary key. Explanation: The correct answer is d. Answer d-enforce the rule that a child foreign key must have a valid parent primary key-is actually the definition of a referential integrity constraint. Answer a is incorrect because a user-defined constraint is used to enforce business rules. Answer b is incorrect because entity constraints don't exist. Answer c is incorrect because a column constraint is used to validate data entry. 5)Which two operators cannot be used in an outer join condition? [Choose two] A) B) C) D) = IN AND OR Correct Answer(s): B) IN D) OR Explanation: The correct answers are b and d. The IN operator and the OR operator cannot be used in an outer join. Answer a is incorrect because an equal sign (=) can be used in an outer join. Answer c is incorrect because an AND operator can also be used in an outer join. 6)Which of the following is executed automatically? A) B) C) D) Anonymous PL/SQL block Function Procedure Trigger Correct Answer(s): D) Trigger Explanation: The correct answer is d. Triggers are associated with tables and are automatically fired on specified actions against the table. Answer a is incorrect because an anonymous PL/SQL block must be called into the command buffer and executed; it is not executed automatically. Answer b is incorrect because functions must be implicitly called by a user, procedure, trigger, or other function, so they are not executed automatically. Answer c is incorrect because procedures must be implicitly called by a user,procedure,trigger, or other function, so they are not executed automatically. 7)What is the default length of a CHAR column? A) B) C) D) 38 255 4000 1 Correct Answer(s): D) 1 Explanation: The correct answer is d. The default length of a CHAR column is 1. 8)Which of the following activities would take place in the production phase of the system development cycle? A) Interview users. B) Develop ERDs. C) Perform normal routine maintenance. D) Code all program modules. E) Test the system for user acceptance. Correct Answer(s): C) Perform normal routine maintenance. Explanation: The correct answer is c. In the production stage, only normal maintenance functions are performed. Answer a is incorrect because users are interviewed in the strategy and analysis phase. Answer b is incorrect because ERDs (entity relationship diagrams) are developed in the build and document phase. Answer d is incorrect because all program modules are coded in the build and document phase. Answer e is incorrect because testing the system for user acceptance is part of the transition phase. 9)Which of the following would contain the list of tables from which to retrieve data? A) B) C) D) SELECT list ORDER BY clause FROM clause GROUP BY clause Correct Answer(s): C) FROM clause Explanation: The correct answer is c. The FROM clause contains the list of tables from which to select data. Answer a is incorrect because a SELECT clause contains a list of items to retrieve from a table. Answer b is incorrect because an ORDER BY clause contains a list of columns or expressions used to determine sort order for the result set. Answer d is incorrect because the GROUP BY clause contains a specified column list to group the returned data. 10)What function would you use to convert a numeric value into a VARCHAR2? A) B) C) D) TO_CHAR TO_NUM TO_DATE TO_VARCHAR Correct Answer(s): A) TO_CHAR Explanation: The correct answer is a. The TO_CHAR function is the only function used to convert a numeric data item into a VARCHAR2 format. Answer b is incorrect because the TO_NUM function is used to convert a VARCHAR2 into a NUMBER, not the other way around. Answer c is incorrect because TO_DATE is used to convert a CHAR, VARCHAR2, or NUMBER into a DATE value. Answer d is incorrect because TO_VARCHAR is not a valid Oracle8 function. 11)Which section of a PL/SQL routine contains functions for error trapping? A) B) C) D) Declarative Definition Exception Executable Correct Answer(s): C) Exception Explanation: The correct answer is c. The exception section is used specifically to trap errors. Answer a is incorrect because the declarative section of a PL/SQL routine is used to define variables, cursors, and exceptions. Answer b is incorrect because the definition section specifies the PL/SQL object type, its name, and the input and/or output variables. Answer d is incorrect because the executable section contains the procedural logic and performs the processing for the PL/SQL object; although it may RAISE exceptions, it doesn't process them. 12)You query the database with this command: > >SELECT >CONCAT(LOWER(SUBSTR(description,10)), >LENGTH(product_name)) "Product ID" >FROM inventory; > >Which function is evaluated second? > > A) CONCAT() > B) LENGTH() > C) LOWER() Correct Answer(s): > > C) LOWER() > >Explanation: > >The correct answer is c. The LOWER() function is evaluated second. The >statement inside the parentheses is evaluated first. In this case, we have >nested parentheses, so the inner and then the outer parenthetical functions >will be executed, in that order. Answer a is incorrect because CONCAT() is >outside the parentheses. Answer b is incorrect because LENGTH() is also >outside the parentheses. 13)You query the database with this command: > >SELECT > isotope, group_id,mass_no, > DISTINCT(atomic_weight) >FROM chart_n; > >What values are displayed? > > A) Distinct combinations of isotope, group_id, mass_no, and >atomic_weight. > B) isotope and distinct combinations of group_id, mass_no, and >atomic_weight. > C) isotope, group_id, mass_no, and distinct values of atomic_weight. > D) No values will be displayed because the statement will fail. Correct Answer(s): > > D) No values will be displayed because the statement will fail. > >Explanation: > >The correct answer is d. No values will be displayed because the statement >is syntactically incorrect and will fail. A DISTINCT operator cannot be >used >in this manner. Answers a, b, and c are incorrect due to the DISTINCT >operator's improper placement, which will cause the entire statement to >fail. 14)For which of the following would you use the ALTER TABLE...MODIFY option? > > A) Add a column to the table. > B) Disable a table constraint. > C) Drop a table column. > D) Increase the precision of a numeric column. Correct Answer(s): > > D) Increase the precision of a numeric column. > >Explanation: > >The correct answer is d. The MODIFY option of the ALTER TABLE command is >used only to change the characteristics or the data type of a column. >Answer >a is incorrect because adding a column to the table must be done with an >ADD >clause. Answer b is incorrect because disabling a table constraint is not >allowed in this version of Oracle. Answer c is incorrect because dropping a >table column is not allowed. 15)Evaluate this command: > >SELECT group_id, isotope, AVG(atomic_weight) >FROM char_n >WHERE AVG(atomic_weight) > 89.00 >GROUP BY group_id, isotope >ORDER BY AVG(atomic_weight); > >Which clause will cause an error? > > A) SELECT group_id, isotope, AVG(atomic_weight) > B) WHERE AVG(atomic_weight) > 89.00 > C) GROUP BY group_id, isotope > D) ORDER BY AVG(atomic_weight); Correct Answer(s): > > B) WHERE AVG(atomic_weight) > 89.00 > >Explanation: > >The correct answer is b. You cannot use a grouping function such as AVG in >a >WHERE clause. The rest of the clauses are syntactically correct and thus >are >incorrect. > 16)Which type of PL/SQL statement would you use to increase the price values >by >10 percent for items with more than 2,000 in stock and by 20 percent for >items with fewer than 500 in stock? > > A) An IF...THEN...ELSE statement > B) A simple INSERT loop > > C) A simple UPDATE statement D) A WHILE loop Correct Answer(s): > > A) An IF...THEN...ELSE statement > >Explanation: > >The correct answer is a. In this question, you are asked to perform >conditional tests and take action based on the results of the test. The >only >PL/SQL structure capable of this is the IF...THEN...ELSE statement. Answer >b >is incorrect because a simple INSERT loop wouldn't use a condition complex >enough to handle the conditions specified. Answer c is incorrect because a >simple UPDATE statement couldn't do a conditional update as specified. >Answer d is incorrect because a WHILE loop wouldn't properly handle the >update specified. > 17)You query the database with this command: > >SELECT id_number, (quantity - 100 / 0.15 - 35 + 20) >FROM inventory; > >Which expression is evaluated first? > > A) quantity - 100 > B) 0.15 - 35 > C) 35 + 20 > D) 100 / 0.15 Correct Answer(s): > > D) 100 / 0.15 > >Explanation: > >The correct answer is d because multiplication (*) and division (/) are >evaluated first in the hierarchy of operations. The other two operators >shown (+ and -) are below division in the hierarchy of operations. 18)In light of Table 11.4, you attempt to query the database with this >command: > >SELECT NVL(100/efficiency, 'none') >FROM calibrations; > >Why does this statement cause an error when the efficiency values are null? > > A) The expression attempts to divide a NULL value. > B) The character string none is not a valid value for the NVL >substitution. > C) A NULL value cannot be converted into a string value with any >function. > D) A NULL value used in an expression cannot be converted to an actual >value. Correct Answer(s): > > B) The character string none is not a valid value for the NVL >substitution. > >Explanation: > >The correct answer is b. The character string 'none' cannot be substituted >for a NULL value using the NVL function. For the NVL command to be used, >the >value being tested for NULL and the substitution value have to be of the >same data type. In this example, we are attempting to substitute a >character >string for a numeric value, so this will generate an error. Answer a is >incorrect because the division will return a NULL value, which the NVL >command should be able to handle. Answer c is incorrect because you can >replace a NULL value with a string using the DECODE function. Answer d is >incorrect because converting a NULL value in an expression to an actual >value is the purpose of the NVL function. 19)What is the purpose of the PL/SQL FETCH command? > > A) To define a cursor to be used later > B) To retrieve values from the active set into local variables > C) To call the rows identified by a cursor query into the active set > D) To release the memory used by the cursor Correct Answer(s): > > B) To retrieve values from the active set into local variables > >Explanation: > >The correct answer is b. The FETCH command retrieves values returned by the >cursor from the active set into the local variables. Answer a is incorrect >because defining a cursor to be used later is the function of the CURSOR >command. Answer c is incorrect because calling the rows identified by a >cursor query into the active set is the function of the OPEN command. >Answer >d is incorrect because releasing memory used by the cursor is the function >of the CLOSE command. 20)After reviewing Table 11.5, and assuming that the 'RR' value is the default >date format, evaluate this command: > >DELETE FROM inventory >WHERE >order_date>TO_DATE('11.30.1999', > 'MM.DD.YYYY'); > >Which of the listed ID_NUMBER values would > > A) 43081 > B) 36023 > C) 36027 > D) None would be deleted because the statement will fail 21)Which privilege can be granted only on a DIRECTORY? > > A) ALTER > B) DELETE > C) READ > D) INSERT > >Correct Answer(s): > > C) READ > >Explanation: > >The correct answer is c. The only allowed grant on a DIRECTORY is READ. >Answers a, b, and d are incorrect because these privileges cannot be >granted >on a DIRECTORY. 22)Evaluate this procedure: CREATE OR REPLACE FUNCTION found_isotope (v_energy_line IN BOOLEAN, v_proper_ratio IN BOOLEAN) RETURN NUMBER IS Ret_val NUMBER; BEGIN IF (v_energy_line AND v_proper_ratio) THEN ret_val:=1; ELSIF NOT (v_energy_line AND v_proper_ratio) THEN ret_val:=2; ELSIF (v_energy_line AND v_proper_ratio) IS NULL THEN ret_val:=-1; END IF; RETURN ret_val; END; If v_energy_line equals TRUE, and v_proper_ratio equals NULL, which value is assigned to ret_val? A) B) C) D) 1 2 -1 None of the above Correct Answer(s): > > B) 2 > >Explanation: > >The correct answer is b. A combination of NULL and TRUE or of NULL and >FALSE >will result in a FALSE (a value of 2 in our function); a combination of >TRUE >and TRUE will result in a TRUE; and a combination of FALSE and FALSE will >result in a FALSE. A combination of TRUE and FALSE also results in a FALSE. >Answer a is incorrect because both conditions would have to be TRUE for the >result to be TRUE (corresponding to 1 in our function). Answer c is >incorrect because both conditions would have to be NULL for the answer to >be >-1, which corresponds to a NULL value. Answer d is incorrect because answer >b is correct. 23)In a SELECT statement, which character is used to pass in a value at >runtime? > > > > > A) B) C) D) \ % & _ (underscore) Correct Answer(s): > > C) & > >Explanation: > >The correct answer is c. The ampersand character (&), either by itself or >with a second ampersand, denotes substitution at runtime. Answer a is >incorrect because the backslash (\) is used to escape the percent (%) and >underscore (_) characters, unless something else is specified with the >ESCAPE keyword. Answer b is incorrect because the percent sign (%) is the >multicharacter wildcard. Answer d is incorrect because the underscore (_) >is >used as the single-character wildcard. 24)What type of join is the SELECT command? A) B) C) D) Equijoin Nonequijoin Self-join The statement is not a join query Correct Answer(s): > > A) Equijoin > >Explanation: > >The correct answer is a. Because the SELECT statement is using an equality >test (using an equal sign), this is an equijoin operation. Answer b is >incorrect because this is not a not-equal (!=, <>) type of join. Answer c >is >incorrect because a self-join is a table joined to itself, and this >statement has two tables being joined. Answer d is incorrect because answer >a is correct. 25)What is the purpose of the SUBSTR string function? > > A) To insert a capital letter for each new word in the string > > > > B) To return a specified substring from the string C) To return the number of characters in the string D) To substitute a non-null string for any null values returned Ans b)