Question 1
5 out of 5 points
According to the the code fragment below, which variable variable is declared declared using the type_basket data data type? DECLARE TYPE type_basket IS RECORD( basket bb_basket.idBasket%TYPE, bb_basket.idBasket%TYPE, created bb_basket.dtcreated%TYPE, bb_basket.dtcreated%TYPE, total bb_basket.cost%TYPE, qty bb_basket.quantity%TYPE, bb_basket.quantity%TYPE, sub bb_basket.subtotal%TYPE); bb_basket.subtotal%TYPE); rec_basket type_basket; BEGIN --- executable code --END; Answer Selected Answ Answer: er: b. rec_basket Correctt Answer: b. rec_basket Correc Question 2
0 out of 5 points
According to the the code fragment below, how many times times does the loop iterate? iterate? DECLARE lv_cnt_num NUMBER :=1; BEGIN WHILE lv_cnt_num lv_cnt_num <= 5 LOOP LOOP DBMS_OUTPUT.PUT_LINE(lv_cnt_num); lv_cnt_num := lv_cnt_num + 1; END LOOP; END; / Answer Selected Answer: b. 4 Correctt Answer: c. 5 Correc Question 3
0 out of 5 points
Which of the following following clauses ensures ensures that a basic loop loop runs at least least once? Answer Selected Answer: d. LOOP Correctt Answer: a. EXIT WHEN Correc Question 4
5 out of 5 points
Which of the following following evaluates conditions conditions and returns returns a value in an assignment assignment statement? statement? Answer Selected Answ Answer: er: c. CASE expression Correctt Answer: c. CASE expression Correc Question 5
5 out of 5 points
The ____ uses the LOOP and END LOOP markers markers to begin and end the loop code. Answer Selected Answer: a. basic loop Correctt Answer: a. basic loop Correc
Question 6
0 out of 5 points
Which of the following following statements statements is true? Answer Selected Answer: b. The WHEN clause of a CASE statement ends with "END CASE;". Correctt Answer: c. The WHEN clause of a CASE expression does not end with a Correc semicolon. Question 7
0 out of 5 points
Which of the statements statements in the code fragment fragment below below ensures that the loop loop executes at least least once? BEGIN LOOP DBMS_OUTPUT.PUT_LINE(lv_cnt_num); lv_cnt_num := lv_cnt_num + 1; EXIT WHEN lv_cnt_num >= 5; END LOOP; END; Answer Selected Answer: a. LOOP Correctt Answer: c. EXIT WHEN lv_cnt_num >= 5; Correc Question 8
0 out of 5 points
One major advantage of using ____ as part of the physical database database is being able to retrieve multiple mu ltiple values with a query of a single column. Answer Selected Answer: c. index by tables Correctt Answer: b. collections Correc Question 9
5 out of 5 points
Which of the following following code fragments fragments correctly correctly uses a record variable variable to hold the row row of data queried for an employee? Answer Selected Answer: a. DECLARE rec_employee employees%ROWTYPE; BEGIN SELECT * INTO rec_employee FROM employees WHERE employee_id employee_id = :g_emp_id :g_emp_id;; DBMS_OUTPUT.PUT_LINE(rec_employee.last_name); DBMS_OUTPUT.PUT_LINE(rec_employee.first_name); DBMS_OUTPUT.PUT_LINE(rec_employee.email); END; Correctt Answer: a. DECLARE Correc rec_employee employees%ROWTYPE; BEGIN SELECT * INTO rec_employee FROM employees WHERE employee_id employee_id = :g_emp_id :g_emp_id;; DBMS_OUTPUT.PUT_LINE(rec_employee.last_name); DBMS_OUTPUT.PUT_LINE(rec_employee.first_name);
DBMS_OUTPUT.PUT_LINE(rec_employee.email); END; Question 10
5 out of 5 points
Which of the clauses clauses in the code fragment fragment below would not cause cause the IF statement statement to raise an error error if it were excluded? IF rec_order.state rec_order.state = 'VA' ' VA' THEN lv_tax_num := rec_order.sub * .06; ELSIF rec_order.state rec_order.state = 'ME' THEN lv_tax_num := rec_order.sub * .05; ELSE lv_tax_num := rec_order.sub * .04; END IF; Answer Selected Answer: a. ELSE Correctt Answer: a. ELSE Correc Question 11
5 out of 5 points
Which of the following following statements statements is correct? Answer Selected Answer: c. The CONTINUE statement cannot appear outside a loop at all. Correctt Answer: c. The CONTINUE statement cannot appear Correc appear outside a loop at all. Question 12
5 out of 5 points
Why would the following following code raise an an error? IF rec_order.state rec_order.state = 'VA' ' VA' THEN lv_tax_num := rec_order.sub * .06; ELSEIF rec_order.state rec_order.state = 'ME' THEN lv_tax_num := rec_order.sub * .05; ELSE lv_tax_num := rec_order.sub * .04; END IF; Answer Selected Answer: c. ELSEIF is not a keyword Correctt Answer: c. ELSEIF is not a keyword Correc Question 13
0 out of 5 points
Which of the following following statements statements about collections collections is NOT true? true? Answer Selected Answer: d. The values in each row of the collection must be of the same type. Correctt Answer: c. A collection may hold many rows of data and many fields. Correc Question 14
5 out of 5 points
Which of the following following code fragments fragments would not raise raise an error? error? Answer Selected Answer:
b. IF rec.state = 'VA' OR rec.state rec.state = 'PA' 'PA' THEN x := y * .06; ELSE x := y * .04; END IF; Correct Answer: b. IF rec.state = 'VA' OR rec.state rec.state = 'PA' 'PA' THEN x := y * .06; ELSE x := y * .04; END IF; Question 15
5 out of 5 points
Which of the following following code fragments fragments correctly correctly creates a record data type that will hold four variables? Answer Selected Answer: d. DECLARE TYPE type_basket IS RECORD ( basket bb_basket.idBasket%TYPE, bb_basket.idBasket%TYPE, created bb_basket.dtcr bb_basket.dtcreated%TYPE, eated%TYPE, qty bb_basket.q bb_basket.quantity%TYPE, uantity%TYPE, sub bb_basket bb_basket.subtotal%TYP .subtotal%TYPE E ); rec_basket type_basket; BEGIN --- executable code --END; Correct Answer: d. DECLARE TYPE type_basket IS RECORD ( basket bb_basket.idBasket%TYPE, bb_basket.idBasket%TYPE, created bb_basket.dtcr bb_basket.dtcreated%TYPE, eated%TYPE, qty bb_basket.q bb_basket.quantity%TYPE, uantity%TYPE, sub bb_basket bb_basket.subtotal%TYP .subtotal%TYPE E ); rec_basket type_basket; BEGIN --- executable code --END; Question 16
5 out of 5 points
Evaluate the following PL/SQL block. DECLARE v_sum_sal NUMBER(10,2) NUMBER(10,2) NOT NOT NULL; BEGIN v_sum_sal := SUM(employees.sa SUM(employees.salary); lary); DBMS_OUTPUT.PUT_LIN DBMS_OUTPUT.PUT_LINE('The E('The sum of salary is '|| v_sum_sal); v_sum_sal); END; / Which of the followings followings is true about about this block? Answer Selected Answer: b. Group functions cannot be used in PL/SQL syntax.
Correctt Answer: b. Group functions cannot be used in PL/SQL syntax. Correc
Question 17
5 out of 5 points
Which of the following following statements statements is true about the statement statement given below? below? Assume that that there is only one employee with employee id 100 and the statement is executed in a PL/SQL block. SELECT hire_date, salary, last_name INTO v_hire, v_sal, v_last FROM employees WHERE employee_id employee_id = 100; Answer Selected Answer: b. The statement creates an implicit cursor. cursor. Correctt Answer: b. The statement creates an implicit cursor. Correc Question 18
5 out of 5 points
Evaluate the following PL/SQL block. DECLARE v_weight NUMBER(3) NUMBER(3) := 600; v_message VARCHAR2(255) VARCHAR2(255) := 'Product 'Product 10012'; BEGIN DECLARE v_weight NUMBER(3) := 1; 1; v_message VARCHAR2(255) VARCHAR2(255) := 'Product 'Product 11001'; v_new_locn VARCHAR2(50) VARCHAR2(50) := 'Europe'; 'Europe'; BEGIN v_weight := v_weight + 1; v_new_locn := 'Western 'Western ' || v_new_locn; Position 1 >>>>>>>>> END; v_weight := v_weight + 1; v_message := v_message v_message || ' is in stock'; v_new_locn := 'Western 'Western ' || v_new_locn; v_new_locn; END; / What is the value value of v_weight at position 1? Answer Selected Answer: d. 2 Correctt Answer: d. 2 Correc Question 19
5 out of 5 points
Evaluate the following PL/SQL block. DECLARE v_customer VARCHAR2(50) VARCHAR2(50) := 'Womansport 'Womansport'; '; v_credit_rating v_credit_rating VARCHAR2(50) VARCHAR2(50) := 'EXCELLENT'; BEGIN
DECLARE v_customer NUMBER(7) NUMBER(7) := 201; v_name VARCHAR2(25) VARCHAR2(25) := 'Unisports'; 'Unisports'; BEGIN v_credit_rating v_credit_rating :='GOOD'; ..... END; ..... END; / What is the value value of v_credit_rating in the the main block? Answer Selected Answer: c. GOOD Correctt Answer: c. GOOD Correc Question 20
0 out of 5 points
Assume that the the value of test1 is 5 and the the value of test2 is NULL. NULL. What is the value value of test3 in the PL/SQL statement below? test3 := test1 > test2; Answer Selected Answer: a. boolean Correctt Answer: d. NULL Correc Question 21
5 out of 5 points
Evaluate the following PL/SQL block. Which of the following is correct value of variable v_weight at position 1 according to the rules of scoping? DECLARE v_weight NUMBER(3) NUMBER(3) := 600; v_message VARCHAR2(255) VARCHAR2(255) := 'Product 'Product 10012'; BEGIN DECLARE v_weight NUMBER(3) := 1; 1; v_message VARCHAR2(255) VARCHAR2(255) := 'Product 11001'; 11001'; v_new_locn VARCHAR2(50) VARCHAR2(50) := 'Europe'; 'Europe'; BEGIN v_weight := v_weight + 1; v_new_locn := 'Western 'Western ' || v_new_locn; END; v_weight := v_weight + 1; v_message := v_message v_message || ' is in stock'; v_new_locn := 'Western 'Western ' || v_new_locn; position 1-------------END; / Answer Selected Answer: c. 601 Correctt Answer: c. 601 Correc Question 22
5 out of 5 points
Which of the following following is true about about this PL/SQL block? block? DECLARE sal_increaseemployees.sa sal_increaseemployees.salary%TYPE lary%TYPE := 800; BEGIN UPDATE employees SET salary := salary + sal_increase WHERE job_id = 'ST_CLERK'; 'ST_CLERK'; END; / Answer Selected Answer: Answer: c. The block block generates generates a syntax syntax error. Correctt Answer: c. The block generates a syntax error. Correc Question 23
5 out of 5 points
The ________ clause is mandatory and is placed between the SELECT and FROM clauses. Answer Selected Answer: b. INTO Correctt Answer: b. INTO Correc Question 24
5 out of 5 points
Examine the structures of the TEXT_TAB1 and TEXT_TAB2 tables. (Both have the same structure.) Name TEXT_ID DOC
Null?
Type NUMBER CLOB
You issue the following following INSERT commands: commands: INSERT INTO text_tab1 VALUES (1, 'This is the first document'); INSERT INTO text_tab1 VALUES (2, 'This is the second document'); INSERT INTO text_tab2 text_tab2 VALUES (1, 'This ' This is the addendum'); Evaluate the following SQL statements: SELECT * FROM text_tab1 WHERE SUBSTR(doc, 14, 2) LIKE '%i%'; SELECT DISTINCT doc from text_tab1; SELECT tab1.text_id FROM text_tab1 tab1 JOIN text_tab2 tab2 ON (tab1.doc = tab2.doc); SELECT COUNT(*) FROM text_tab1 WHERE doc = NOT NULL; Which of the above SQL SQL statements statements execute successfully? successfully? Answer Selected Answer: Answer: a. statement 1 Correctt Answer: a. statement Correc state ment 1 Question 25
5 out of 5 points
Which of the following following statement statement types is directly directly supported by PL/SQL? PL/SQL? Answer Selected Answer: a. Data Manipulation Language (DML) Correctt Answer: a. Data Manipulation Language (DML) Correc Question 26
5 out of 5 points
Evaluate the following PL/SQL block. Which of the following is correct value of variable v_new_locn at position 1 according to the rules of scoping? DECLARE v_weight NUMBER(3) NUMBER(3) := 600; v_message VARCHAR2(255) VARCHAR2(255) := 'Product 'Product 10012'; BEGIN DECLARE v_weight NUMBER(3) := 1; 1; v_message VARCHAR2(255) VARCHAR2(255) := 'Product 'Product 11001'; v_new_locn VARCHAR2(50) VARCHAR2(50) := 'Europe'; 'Europe'; BEGIN v_weight := v_weight + 1; v_new_locn := 'Western 'Western ' || v_new_locn; position 1-------------END; v_weight := v_weight + 1; v_message := v_message v_message || ' is in stock'; v_new_locn := 'Western 'Western ' || v_new_locn; END; / Answer Selected Answer: d. Western Europe Correctt Answer: d. Western Europe Correc Question 27
5 out of 5 points
Which of the following following functions is available available only in SQL SQL statements statements in a PL/SQL block? Answer Selected Answer: c. AVG Correctt Answer: c. AVG Correc Question 28
5 out of 5 points
Evaluate the following PL/SQL block. DECLARE v_weight NUMBER(3) NUMBER(3) := 600; v_message VARCHAR2(255) VARCHAR2(255) := 'Product 'Product 10012'; BEGIN DECLARE v_weight NUMBER(3) := 1; 1; v_message VARCHAR2(255) VARCHAR2(255) := 'Product 'Product 11001'; v_new_locn VARCHAR2(50) VARCHAR2(50) := 'Europe'; 'Europe'; BEGIN v_weight := v_weight + 1; v_new_locn := 'Western 'Western ' || v_new_locn; END; v_weight := v_weight + 1; v_message := v_message v_message || ' is in stock'; v_new_locn := 'Western 'Western ' || v_new_locn; v_new_locn; Position 1 >>>>>>>>>
END; / What is the value value of v_new_locn at position position 1? Answer Selected Answer: d. None of the above. The v_new_locn variable is not visible outside the subblock. Correctt Answer: d. None of the above. The v_new_locn variable is not visible outside Correc the subblock. Question 29
5 out of 5 points
Evaluate the following PL/SQL block. DECLARE mgr_no NUMBER(6) := 122; BEGIN DELETE FROM employees WHERE manager_id = mgr_no; DBMS_OUTPUT.PUT_LIN DBMS_OUTPUT.PUT_LINE('Number E('Number of employees deleted: ' || TO_CHAR(?????)); TO_CHAR(?????)); END; / Which of the followings followings should be replaced replaced with ????? to to return the expected expected result? Answer Selected Answer: a. SQL%ROWCOUNT Correctt Answer: a. SQL%ROWCOUNT Correc Question 30
5 out of 5 points
Which of the following following should be placed placed in a PL/SQL PL/SQL block that selects selects the maximum department department ID in the departments departments table and stores it in the v_max_deptno v_max_deptno variable? Answer Selected Answer: c. c . SELECT MAX(department_i MAX(department_id) d) INTO v_max_deptno FROM departments; Correctt Answer: c. SELECT MAX(department_i Correc MAX(department_id) d) INTO v_max_deptno FROM departments; Question 31
5 out of 5 points
A(n) ____ of data typically typically includes a number number of different different fields. Answer Selected Answ Answer: er: a. row Correctt Answer: a. row Correc Question 32
5 out of 5 points
The variable name ____ conforms to Oracle naming standards. Answer Selected Answer: b. ship_date Correctt Answer: b. ship_date Correc Question 33
5 out of 5 points
Which of the following following is a legal declaration declaration statement? statement? Answer
Selected Answer: b. DECLARE test NUMBER(5); Correctt Answer: b. DECLARE test NUMBER(5); Correc Question 34
5 out of 5 points
Which of the following following sections is mandatory mandatory in a PL/SQL PL/SQL block? Answer Selected Answer: b. An executable section which begins begins with the keyword BEGIN and ends with END. Correctt Answer: b. An executable section which begins Correc begins with the keyword BEGIN and ends with END. Question 35
5 out of 5 points
Which of the following following constraints constraints must be used if a variable variable must hold a value? value? Answer Selected Answer: Answer: b. NOT NULL Correctt Answer: b. NOT NULL Correc Question 36
5 out of 5 points
A(n) ________ variable variable is one that exists in the application application environment environment and can range range from a field field on an application screen to an SQL*Plus variable. Answer Selected Answer: b. host Correctt Answer: b. host Correc Question 37
5 out of 5 points
The statements that are used to control the flow of logic processing in your programs are commonly referred referred to as ____. Answer Selected Answer: b. control structure structures s Correctt Answer: b. control structure Correc structures s Question 38
5 out of 5 points
The ____ section of a PL/SQL block contains code that creates variables, variables, cursors, and types. Answer Selected Answer: a. DECLARE Correctt Answer: a. DECLARE Correc Question 39
5 out of 5 points
The ____ markers are used in PL/SQL P L/SQL to identify a label. Answer Selected Answer: Answer: c. << >> Correctt Answer: c. << >> Correc Question 40
5 out of 5 points
Which of the following following is a valid identifier? identifier? Answer Selected Answer: d. number1to7
Correctt Answer: d. number1to7 Correc
