Oracle Quiz Questions
Oracle Quiz Answers. Oracle Quiz Questions An asterisk (*) indicates a correct answer.
marți, 12 martie 2013
Section 4 Lesson 5: Iterative Control: Nested Loops
Iterative Control: Nested Loops
1. What statement allows you to exit the outer loop at Point A in the following block?
DECLARE
v_outer_done CHAR(3) := 'NO';
v_inner_done CHAR(3) := 'NO';
BEGIN
LOOP -- outer loop
...
LOOP -- inner loop
...
... -- Point A
EXIT WHEN v_inner_done = 'YES';
...
END LOOP;
...
EXIT WHEN v_outer_done = 'YES';
...
END LOOP;
END;
(1) Points
EXIT AT v_outer_done = 'YES';
EXIT WHEN v_outer_done = 'YES'; (*)
WHEN v_outer_done = YES EXIT;
EXIT <<outer loop>>;
2. Which one of these statements about using nested loops is true? (1) Points
All the loops must be labelled
The outer loop must be labelled, but the inner loop need not be labelled
The outer loop must be labelled if you want to exit the outer loop from within the inner loop (*)
Both loops can have the same label
3. What will be displayed when the following block is executed?
DECLARE
x NUMBER(6) := 0 ;
BEGIN
FOR i IN 1..10 LOOP
FOR j IN 1..5 LOOP
x := x+1 ;
END LOOP;
END LOOP;
DBMS_OUTPUT.PUT_LINE(x);
END;
(1) Points
5
10
15
50 (*)
4. When the following code is executed, how many lines of output will be displayed?
BEGIN
FOR i IN 1..5 LOOP
FOR j IN 1..8 LOOP
DBMS_OUTPUT.PUT_LINE(i || ',' || j);
END LOOP;
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;
(1) Points
80
45 (*)
14
41
5. What type of loop statement would you write for Point A?
BEGIN
FOR v_outerloop IN 1..3 LOOP
-- Point A
DBMS_OUTPUT.PUT_LINE('Outer loop is:'||v_outerloop||
' and inner loop is: '||v_innerloop);
END LOOP;
END LOOP;
END;
(1) Points
WHILE v_innerloop <=5 LOOP
FOR v_innerloop 1..5 LOOP (*)
LOOP
WHILE v_outerloop<v_innerloop LOOP
6. Look at the following code:
DECLARE
v_blue NUMBER(3) := 0;
v_red NUMBER(3) := 0;
BEGIN
<< blue >> LOOP
v_blue := v_blue + 1;
EXIT WHEN v_blue > 10;
<< red >> LOOP
v_red := v_red + 1;
EXIT WHEN v_red > 10;
-- Line A
END LOOP red;
END LOOP blue;
END;
What should you code at Line A to exit from the outer loop?
(1) Points
EXIT;
EXIT red;
EXIT <<blue>>;
EXIT blue; (*)
Section 4 Lesson 4: Iterative Control: While and For Loops
Iterative Control: While and For Loops
1. Look at the following code fragment:
i := 2;
WHILE i < 3 LOOP
i := 4;
DBMS_OUTPUT.PUT_LINE('The counter is: ' || i);
END LOOP;
How many lines of output will be displayed?
(1) Points
No lines
One line (*)
Two lines
The block will fail because you cannot use DBMS_OUTPUT.PUT_LINE inside a loop.
2. Look at the following block:
DECLARE
v_date DATE := SYSDATE;
BEGIN
WHILE v_date < LAST_DAY(v_date) LOOP
v_date := v_date + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(v_date);
END;
If today's date is 17th April 2007, what will be displayed when this block executes?
(1) Points
01-MAY-07
31-DEC-07
4/30/2007 (*)
4/17/2007
3. Which statement best describes when a FOR loop should be used? (1) Points
When an EXIT WHEN statement must be coded.
When an implicitly declared counter must increase by 1 in each iteration of the loop. (*)
When we want to exit from the loop when a Boolean variable becomes FALSE.
When the statements inside the loop must execute at least once.
4. You should use a WHILE loop when the number of iterations of the loop is known in advance. True or False? (1) Points
True
False (*)
5. Look at this code fragment: FOR i IN 1 .. 3 LOOP i := 4; DBMS_OUTPUT.PUT_LINE('The counter is: ' || i); END LOOP; How many lines of output will be displayed? (1) Points
One
Three
Four
The block will fail because you cannot change the value of i inside the loop. (*)
6. In a FOR loop, an explicitly declared counter is automatically incremented by 1 for each iteration of the loop. True or False? (1) Points
True
False (*)
7. In a WHILE loop, the controlling condition is checked at the start of each iteration. True or False? (1) Points
True (*)
False
8. You want a loop that counts backwards from 10 through 1. How do you code that? (1) Points
FOR i IN 10 .. 1 LOOP
FOR i IN 1 .. 10 BY -1 LOOP
FOR i IN REVERSE 1 .. 10 LOOP (*)
FOR i IN REVERSE 10 .. 1 LOOP
Section 4 Lesson 3: Iterative Control: Basic Loops
Iterative Control: Basic Loops
1. What will be displayed when this block is executed?
DECLARE
v_count NUMBER := 10;
v_result NUMBER;
BEGIN
LOOP
v_count := v_count - 1;
EXIT WHEN v_count < 5;
v_result := v_count * 2;
END LOOP;
DBMS_OUTPUT.PUT_LINE(v_result);
END;
(1) Points
8
10 (*)
12
NULL
2. For which one of these tasks should you use a PL/SQL loop? (1) Points
Updating the salary of one employee.
Executing the same set of statements repeatedly until a condition becomes true. (*)
Deciding whether a value is within a range of numbers.
Making a decision based on whether a condition is true or not.
3. Look at this code:
DECLARE
v_bool BOOLEAN := TRUE;
v_date DATE;
BEGIN
LOOP
EXIT WHEN v_bool;
SELECT SYSDATE INTO v_date FROM dual;
END LOOP;
END;
How many times will the SELECT statement execute?
(1) Points
Once.
Twice.
Never (the SELECT will not execute at all) (*)
An infinite number of times because the EXIT condition will never be true
4. Which kind of loop is this?
i := 10;
LOOP
i := i + 1;
EXIT WHEN i > 30;
END LOOP;
(1) Points
A FOR loop.
A WHILE loop.
A basic loop. (*)
An infinite loop.
A nested loop.
5. Examine the following code::
DECLARE
v_count NUMBER := 0;
v_string VARCHAR2(20);
BEGIN
LOOP
v_string := v_string || 'x';
IF LENGTH(v_string) > 10 THEN
EXIT;
END IF;
v_count := v_count + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(v_count);
END;
What will be displayed when this block is executed?
(1) Points
9
10 (*)
11
xxxxxxxxxxx
6. You want to calculate and display the multiplication table for "sevens": 7x1=7, 7x2=14, 7x3=21 and so on. Which kind of PL/SQL construct is best for this? (1) Points
A loop (*)
A CASE statement
IF ... END IF;
A Boolean variable.
7. What are the three kinds of loops in PL/SQL? (1) Points
ascending, descending, unordered
infinite, finite, recursive
IF, CASE, LOOP
FOR, WHILE, basic (*)
8. How many EXIT statements can be coded inside a basic loop? (1) Points
None.
One only.
Two.
As many as you need, there is no limit. (*)
Section 4 Lesson 2: Conditional Control: Case Statements
Conditional Control: Case Statements
Section 1
1. How must you end a CASE expression? (1) Points
END; (*)
ENDIF;
END CASE;
ENDCASE;
2. Examine the following code:
DECLARE
v_a BOOLEAN;
v_b BOOLEAN := FALSE;
v_c BOOLEAN ;
BEGIN
v_c := (v_a AND v_b);
-- Line A
....
END;
What is the value of V_C at Line A?
(1) Points
True
False (*)
NULL
Undefined
3. What will be displayed when the following block is executed?
DECLARE
v_age1 NUMBER(3);
v_age2 NUMBER(3);
v_message VARCHAR2(20);
BEGIN
CASE
WHEN v_age1 = v_age2 THEN v_message := 'Equal';
WHEN v_age1 <> v_age2 THEN v_message := 'Unequal';
ELSE v_message := 'Undefined';
END CASE;
DBMS_OUTPUT.PUT_LINE(v_message);
END;
(1) Points
Equal
Undefined (*)
Unequal
Nothing will be displayed because V_MESSAGE is set to NULL.
4. Examine the following code:
DECLARE
v_score NUMBER(3);
v_grade CHAR(1);
BEGIN
v_grade := CASE v_score
-- Line A
....
The CASE expression must convert a numeric score to a letter grade: 90 -> A, 80 -> B, 70 -> C and so on. What should be coded at Line A?
(1) Points
WHEN 90 THEN grade := 'A'
WHEN 90 THEN v_grade := 'A';
WHEN 90 THEN 'A' (*)
WHEN 90 THEN 'A';
5. What will be displayed when the following block is executed?
DECLARE
v_age NUMBER(3);
v_gender VARCHAR2(6) := 'Female';
v_status VARCHAR2(20);
BEGIN
CASE
WHEN v_age >= 18 AND v_gender = 'Male' THEN v_status := 'Adult Male';
WHEN v_age >= 18 AND v_gender = 'Female' THEN v_status := 'Adult Female';
WHEN v_age < 18 AND v_gender = 'Male' THEN v_status := 'Junior Male';
WHEN v_age < 18 AND v_gender = 'Female' THEN v_status := 'Junior Female';
ELSE v_status := 'Other Value';
END CASE;
DBMS_OUTPUT.PUT_LINE(v_status);
END;
(1) Points
Adult Male
Junior Female
Other Value (*)
Nothing will be displayed because V_STATUS is set to NULL.
6. How must you end a CASE statement? (1) Points
END;
END CASE; (*)
END IF;
ENDCASE;
7. Examine the following code:
DECLARE
v_score NUMBER(3);
v_grade CHAR(1);
BEGIN
CASE v_score
-- Line A
....
The CASE statement must convert a numeric score to a letter grade: 90 -> A, 80 -> B, 70 -> C and so on.
What should be coded at Line A?
(1) Points
WHEN 90 THEN v_grade := 'A'
WHEN 90 THEN v_grade := 'A'; (*)
WHEN 90 THEN 'A'
WHEN 90 THEN 'A';
8. Look at the following code:
DECLARE
x BOOLEAN := FALSE;
y BOOLEAN := FALSE;
z BOOLEAN ;
BEGIN
z := (x OR NOT y);
-- Line A
....
END;
What is the value of Z at Line A?
(1) Points
True (*)
False
NULL
An error will occur because you cannot combine two Boolean variables using "NOT".
Section 4 Lesson 1: Conditional Control: IF Statements
Conditional Control: IF Statements
Section 1
1. Name three types of control structures in PL/SQL. (Choose three) (1) Points
(Choose all correct answers)
LOOP statements (*)
SELECT statements
EXCEPTIONS
IF statements (*)
CASE statements (*)
2. You want to repeat a set of statements 100 times, incrementing a counter each time. What kind of PL/SQL control structure would you use? (1) Points
IF...THEN...ELSE
IF...THEN...ELSIF...ELSE
CASE...WHEN...THEN
A loop. (*)
3. A basic loop is a type of control structure used to change the logical flow of statements in a PL/SQL block. True or False? (1) Points
True (*)
False
4. Look at the following (badly written) code:
age := 5;
IF age<30 THEN mature := 'adult';
ELSIF age<22 THEN mature := 'teenager';
ELSIF age<13 THEN mature := 'child';
END IF;
DBMS_OUTPUT.PUT_LINE(mature);
What will be displayed when this code is executed?
(1) Points
child
teenager
adult (*)
adultteenagerchild
5. Which one of the following is correct syntax for an IF statement? (1) Points
IF condition THEN DO statement1; statement2; END IF;
IF condition THEN statement1; statement2; END IF; (*)
IF condition THEN statement1; statement2; ENDIF;
IF condition THEN statement1; AND statement2; END IF;
6. We want to execute one of three statements depending on whether the value in V_VAR is 10, 20 or some other value. What should be coded at Line A?
IF v_var = 10 THEN
statement1;
-- Line A
statement2;
ELSE
statement3;
END IF;
(1) Points
ELSE IF v_var = 20 THEN
ELSIF v_var = 20
ELSIF v_var = 20 THEN (*)
IF v_var = 20 THEN
7. What will be displayed when this block is executed?
DECLARE
v_birthdate DATE;
BEGIN
IF v_birthdate < '01-JAN-2000'
THEN
DBMS_OUTPUT.PUT_LINE(' Born in the 20th century ');
ELSE
DBMS_OUTPUT.PUT_LINE(' Born in the 21st century ');
END IF;
END;
(1) Points
Born in the 20th century
Born in the 21st century (*)
Exception raised because no date given
8. Which of the following statements are true about any of the PL/SQL conditional control structures such as IF ... , CASE ... and loops? (1) Points
They allow the programmer to use logical tests to determine which statements are executed and which are not.
They allow a set of statements to be executed repeatedly (i.e. more than once).
They determine a course of action based on conditions.
All of the above. (*)
9. What is wrong with the following trivial IF statement:
IF (v_job='President')
THEN v_salary := 10000;
(1) Points
IF and THEN must be on the same line: IF (v_job='President') THEN ...
The condition should be coded: IF (v_job := 'President')
END IF; is missing (*)
ELSE is missing
10. What will be displayed when this block is executed?
DECLARE
v_bool1 BOOLEAN := NULL;
v_bool2 BOOLEAN := NULL;
v_char VARCHAR(10) := 'Start';
BEGIN
IF (v_bool1 = v_bool2) THEN
v_char:='Equal';
ELSE v_char:='Not equal';
END IF;
DBMS_OUTPUT.PUT_LINE(v_char);
END;
(1) Points
Equal
Not equal (*)
Start
Nothing will be displayed. The block will fail because you cannot compare two null values.
11. What will be displayed when this block is executed?
DECLARE
v_bool1 BOOLEAN := TRUE;
v_bool2 BOOLEAN;
v_char VARCHAR(4) := 'up';
BEGIN
IF (v_bool1 AND v_bool2) THEN
v_char:='down';
ELSE v_char:='left';
END IF;
DBMS_OUTPUT.PUT_LINE(v_char);
END;
(1) Points
up
down
left (*)
null
Section 3 Lesson 2: Retrieving Data in PL/SQL
Retrieving Data in PL/SQL
Section 1
1. Look at this PL/SQL block:
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count
FROM employees WHERE salary > 50000;
END;
No employees earn more than $50,000. Which of the following statements are true? (1) Points
(Choose all correct answers)
The SELECT will return value 0 into V_COUNT. (*)
The SELECT will fail because it does NOT return exactly one row.
The block will fail because variable V_SALARY was not declared.
The SELECT returns exactly one row. (*)
The block will fail because no results are displayed to the user.
2. Which one of these SQL statements can be directly included in a PL/SQL executable block? (1) Points
IF... THEN...;
INSERT INTO...; (*)
SELECT * FROM DUAL;
SHOW USER;
3. It is good programming practice to create identifiers having the same name as column names. True or False? (1) Points
True
False (*)
4. Which of the following is NOT a valid guideline for retrieving data in PL/SQL? (1) Points
Terminate the SQL statement with a semicolon (;)
Do NOT use a WHERE clause in SELECT statements. (*)
Where possible, declare variables using the %TYPE attribute.
Specify the same number of variables in the INTO clause as database columns in the SELECT clause.
5. Which SQL statements can be used directly in a PL/SQL block? (Choose two.) (1) Points
(Choose all correct answers)
GRANT EXECUTE ON ...
SELECT * INTO ... (*)
REVOKE SELECT ON ...
UPDATE employees SET... (*)
ALTER TABLE employees ...
6. Does PL/SQL allow you to have a variable with the same name as a database column? (1) Points
No
Yes (*)
7. What will happen when the following block is executed?
DECLARE
v_last employees.last_name%TYPE;
v_first employees.first_name%TYPE;
v_salary employees.salary%TYPE;
BEGIN
SELECT first_name, last_name
INTO v_first, v_last, v_salary
FROM employees WHERE employee_id=100;
END;
(1) Points
The block will fail because the SELECT statement returns more than one row.
The block will fail because the SELECT is trying to read two columns into three PL/SQL variables. (*)
The block will fail because V_LAST was declared before V_FIRST.
The block will execute successfully, and the V_SALARY variable will be set to NULL.
8. When used in a PL/SQL block, which SQL statement must return exactly one row? (1) Points
INSERT
UPDATE
SELECT (*)
MERGE
DELETE
Section 3 Lesson 1: Review of SQL DML
Review of SQL DML
Section 1
1. When inserting a row into a table, the VALUES clause must include a value for every column of the table. True or False? (1) Points
True
False (*)
2. What would be the result of the following statement: DELETE employees; (1) Points
Nothing, no data will be changed.
All rows in the employees table will be deleted. (*)
The statement will fail because it contains a syntax error.
The row with EMPOYEE_ID=100 will be deleted.
3. Is it possible to insert more than one row at a time using an INSERT statement with a VALUES clause? (1) Points
No, you can only create one row at a time when using the VALUES clause. (*)
Yes, you can list as many rows as you want, just remember to separate the rows with commas.
No, there is no such thing as INSERT ... VALUES.
4. What is wrong with the following statement?
MERGE INTO emps e
USING new_emps ne
ON (e.employee_id = ne.employee_id)
WHEN MATCHED
THEN UPDATE SET ne.salary = e.salary
WHEN NOT MATCHED
THEN INSERT VALUES
(ne.employee_id, ne.first_name, ne.last_name, .... ne.salary, ....);
(1) Points
The UPDATE clause must include the target table name: UPDATE emps SET ....
The INSERT clause must include a column list as well as a list of column values.
The SET clause is trying to update the source table from the target table. (*)
Nothing is wrong, the statement will execute correctly.
5. You want to modify existing rows in a table. Which of the following are NOT needed in your SQL statement? (Choose two). (1) Points
A MODIFY clause.
An UPDATE clause.
The name of the table.
The name of the column(s) you want to modify.
A new value for the column you want to modify (this can be an expression or a subquery).
A WHERE clause, (*)
6. What is wrong with the following statement?
DELETE from employees WHERE salary > (SELECT MAX(salary) FROM employees);
(1) Points
You cannot code a subquery inside a DELETE statement.
You cannot use inequality operators such as "<" and ">" inside a DELETE statement.
Nothing is wrong, the statement will execute correctly. (*)
7. Look at this SQL statement:
MERGE INTO old_trans ot
USING new_trans nt
ON (ot.trans_id = nt.trans_id) .... ;
OLD_TRANS is the source table and NEW_TRANS is the target table. True or false?
(1) Points
True
False (*)
8. To modify an existing row in a table, you can use the ________ statement. (1) Points
MODIFY
INSERT
ALTER
UPDATE (*)
Abonați-vă la:
Postări (Atom)