Halaman

Monday, August 12, 2019

Semester 1 Midterm Exam


Semester 1 Midterm Exam

Which of the following can be done using PL/SQL?
                                                                               
                                               
                Manage database security

                                               
                All of these can be done (*)

                                               
                Develop Web applications using the Web Application Toolkit


                                              
                Create customized reports

                                               
                Update data (DML)

                                                                               
You can create a Web site application written entirely in PL/SQL. True or False?  
                                                                               
                                               
                True

                                               
                False (*)

                                                                               
How can you display results to check that a PL/SQL block is working correctly?    
                                                                               
                                               
                Use DBMS_OUTPUT.PUT_LINE (*)

                                               
                You don't need to do anything, the results will display automatically.

                                               
                Use an Exception section

                                               
                Write a C or Java program to display the results

                                                                               
Which of the following is NOT a PL/SQL programming environment?      
                                                                               
                                               
                SQL*Plus

                                               
                SQL Workshop in Application Express

                                               
                gSQL*Plus (*)

                                               
                Oracle jDeveloper

                                                                               

Which component of Oracle Application Express is used to enter and run SQL statements and PL/SQL blocks?  
                                                                               
                                               
                SQL Workshop (*)

                                               
                Application Builder

                                               
                Utilities

                                               
                Object Browser

Which lines of code will correctly display the message "Hello World" ? (Choose two.)      
                                                                               
                                               
                DBMS_OUTPUT.PUT_LINE('Hello' || 'World');

                                               
                DBMS_OUTPUT('Hello World');

                                               
                DBMS_OUTPUT.PUT_LINE('Hello World'); (*)

                                               
                DBMS_OUTPUT.PUT_LINE('Hello' || ' ' || 'World'); (*)

                                                                               
                                                               
PL/SQL extends SQL by including all of the following except:        
                                                                               
                                               
                constants

                                               
                reusable program units

                                               
                nonprocedural constructs (*)

                                               
                conditional statements

                                               
                variables

                                                                               
                                                               
Which of the following statements about PL/SQL and SQL is true?          

                                                                               
                                               
                PL/SQL allows basic program logic and control flow to be combined with SQL statements. (*)

                                               
                PL/SQL and SQL are both ANSI-compliant.

                                               
                PL/SQL and SQL can be used with many types of databases, including Oracle.

                                               
                PL/SQL and SQL are both Oracle proprietary programming languages.

                                                                               
                                                               
A variable must have a value if NOT NULL is specified. True or False?      
                                                                               
                                               
                True (*)

                                               
                False

                                                                               
                                                               
Which of the following variable declarations does NOT use a number data type?          

                                               
                v_count PLS_INTEGER := 0;

                                               
                v_count BINARY_INTEGER;

                                               
                v_median_age NUMBER(6,2);

                                               
                v_students LONG; (*)

Which of the following are examples of good programming practice? (Choose two.)        

                                               
                Declare one or more identifiers per line for improved performance.

                                               
                Use meaningful names for identifiers. (*)

                                               
                Use the %TYPE attribute to declare a variable according to another previously declared variable or database column. (*)

                                               
                For clarity, use column names as identifiers.

                                                                               
                                                               
Which good programming practice guideline would make this code easier to read?
DECLARE
v_sal NUMBER(8,2);
BEGIN
SELECT salary INTO v_sal
FROM employees WHERE employee_id = 100;
UPDATE employees
SET salary = v_sal;
END;
                                                              
                                               
                Declaring variables using %TYPE

                                               
                Indenting each level of code (*)

                                               
                Using a consistent naming convention for variables

                                               
                Avoiding implicit data type conversions

                                                                               
A Scalar data type holds a(n) ____ value.              
                                                                               
                                               
                Large

                                               
                Single (*)

                                               
                image

                                               
                Multi

                                                                               
                                                               
Which of the following are scalar data types? (Choose three.)   
                                                                               
                                               
                Boolean (*)

                                               
                Table

                                               
                Array

                                               
                Date (*)

                                               
                Character (*)

                                                                               
                                                               
PL/SQL can convert a VARCHAR2 value containing alphabetic characters to a NUMBER value. True or False? 
                                                                               
                                               
                True

                                               
                False (*)

The LENGTH and ROUND functions can be used in PL/SQL statements. True or False?
                                               
                True (*)

                                               
                False

                                                                               
                                                               
What is wrong with this assignment statement?
myvar :=              'To be or not to be';
'That is the question';

                                                                               
                                               
                An assignment statement must have a single semicolon at the end (*)

                                               
                An assignment statement must be a single line of code

                                               
                "myvar" is not a valid name for a variable

                                               
                Nothing is wrong, the statement is fine

                                               
                Character literals should not be enclosed in quotes

                                                                               
                                                               
Examine the following code. What is the final value of V_MYBOOL ?
DECLARE
    v_mynumber NUMBER;
    v_mybool BOOLEAN ;
BEGIN
    v_mynumber := 6;
    v_mybool := (v_mynumber BETWEEN 10 AND 20);
    v_mybool := NOT (v_mybool);
END;
                                                                               
                                               
                TRUE (*)

                                               
                FALSE

                                               
                NULL

                                               
                NOT FALSE

                                               
                NOT TRUE

                                                                               
                                                               
Constants must be initialized. True or False?  
  
                                               
                True (*)

                                               
                False

                                                                               
Variables can be used in the following ways in a PL/SQL block. (Choose two.)     
                                                                               
                                               
                To rename tables and columns.

                                               
                To comment code.

                                               
                To store data values. (*)

                                               
                To refer to a single data value several times. (*)

Variables may be reused. True or False?              

                                               
                True (*)

                                               
                False

                                                                               
                                                               
Which of the following is a valid naming convention for an identifier? (Choose two.)      

                                               
                Can include letters or numbers (*)

                                               
                Can start with a number or special character

                                               
                Can be over 30 characters

                                               
                Cannot contain a reserved word (*)

                                                                               
                                                               
Reserved words can be used as identifiers. True or False?            

                                               
                True

                                               
                False (*)

                                                                               
                                                               
If a variable definition is not found in an inner block where it is being referenced, where does it look for it?          
                                                                               
                                               
                This will result in an error.

                                               
                It looks upward in the parent blocks. (*)

                                               
                It downward in any other inner blocks.

                                                                               
Two variables with the same name can be declared in an outer block and an inner block. True or False                                                              
                                               
                True (*)

                                               
                False


What will be displayed when the following code is executed?
DECLARE
    varA NUMBER := 12;
BEGIN
    DECLARE
       varB NUMBER := 8;
    BEGIN
       varA := varA + varB;
    END;
    DBMS_OUTPUT.PUT_LINE(varB);
END;
                                                                               
                                               
                8

                                               
                Nothing, the block will fail with an error (*)

                                               
                20

                                               
                12

                                               
                VarB

                                                                               

Which one of these SQL statements can be directly included in a PL/SQL executable block?                 
                                                                               
                                               
                SELECT salary FROM employees
WHERE department_id=60;

                                               
                DROP TABLE locations;

                                               
                DELETE FROM employees
WHERE department_id=60;
(*)


                                               
                CREATE TABLE new_emps (last_name VARCHAR2(10), first_name VARCHAR2(10));

                                                                               
Given this first section of code:
DECLARE
    v_result employees.salary%TYPE;
BEGIN

Which statement will always return exactly one value?

                                                           
                                               
                SELECT salary
INTO v_result
FROM employees
WHERE department_id = 80;

                                               
                SELECT SUM(salary)
INTO v_result
FROM employees;
(*)


                                               
                SELECT salary
INTO v_result
FROM employees;

                                               
                SELECT salary
INTO v_result
FROM employees
WHERE last_name ='Smith';

                                                                               
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 $50000. Which of the following statements are true? 
                                                                               
                                               
                The SELECT will fail because it does NOT return exactly one row.

                                               
                The SELECT will return value 0 into V_COUNT. (*)

                                               
                The block will fail because variable V_SALARY was not declared.

                                               
                The block will fail because no results are displayed to the user.

                                               
                The SELECT returns exactly one row. (*)

                                                                               
                                                               
In a PL/SQL block, where can you code a COMMIT statement?   
                                                                               
                                               
                In the Executable and/or the Exception sections. (*)

                                               
                Only the Executable section.

                                               
                In any section of the block: Declaration, Executable, or Exception.

                                               
                Nowhere; the COMMIT statement must be outside the block.


How many INSERTs can you have in one transaction?     
                                                                               
                                               
                As many as you want until you do a COMMIT or ROLLBACK. (*)

                                               
                As many as you want until a different DML statement (UPDATE, DELETE or MERGE) is executed.

                                               
                As many as you can execute before the database does an AUTOSAVE.

                                               
                One

                                                                               
You can use implicit cursor attributes such as SQL%ROWCOUNT directly inside a DML statement. For example:
INSERT INTO log_table
   VALUES (SYSDATE, USER, SQL%ROWCOUNT);

True or False?   
                                                                               
                                               
                True

                                               
                False (*)

                                                                               
                                                               
There are no employees in Department 77. What will happen when the following block is executed?
BEGIN
DELETE FROM employees
WHERE department_id=77;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT)
END;
                                                                               
                                               
                An exception is raised because the block does not contain a COMMIT statement.

                                               
                A NULL is displayed.

                                               
                A zero (0) is displayed. (*)

                                               
                A NO_DATA_FOUND exception is raised.

                                                                               
                                                               
Which is the correct way to erase one row from a table?         
                                                                               
                                               
                DELETE FROM employees
WHERE employee_id=100;
(*)


                                               
                REMOVE employee_id=100
FROM employees;

                                               
                TRUNCATE employees
WHERE employee_id=100;

                                               
                DROP TABLE employees
WHERE employee_id=100;

                                                                               
                                                               
To modify an existing row in a table, you can use the ________ statement.        
                                                                               
                                               
                ALTER

                                               
                INSERT

                                               
                MODIFY

                                               
                UPDATE (*)


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, ....);

                                                          
                                               
                The INSERT clause must include a column list as well as a list of column values.

                                               
                The UPDATE clause must include the target table name: UPDATE emps SET ....

                                               
                Nothing is wrong, the statement will execute correctly.

                                               
                The SET clause is trying to update the source table from the target table. (*)

                                                                               
                                                               
What clause will leave the outer loop at Point A?
DECLARE
  i INTEGER := 0;
BEGIN
   << i_loop >>
  WHILE i <= 10 LOOP
    i := i+1;
     << j_loop >>
      FOR j IN 1..5 LOOP
      _______ WHEN i = j*2; -- Point A
      DBMS_OUTPUT.PUT_LINE(i || j);
    END LOOP;
  END LOOP;
END;

                                                                               
                                               
                EXIT outerloop

                                               
                EXIT   << outerloop>>

                                               
                EXIT j_loop

                                               
                EXIT i_loop (*)

                                                                               
                                                               
Examine the following code:
DECLARE
    v_outer_count NUMBER := 1;
    v_inner_count NUMBER := 1;
BEGIN
    LOOP
       LOOP
          v_inner_count := v_inner_count + 1;
          EXIT WHEN v_inner_count > 5; -- Line A
       END LOOP;
       v_outer_count := v_outer_count + 1;
       EXIT WHEN v_outer_count > 3;
    END LOOP;
END;

What happens at Line A when the value of V_INNER_COUNT equals 6?

                                                                               
                                               
                Both loops are exited and the block's execution is terminated.

                                               
                An error condition is returned.

                                               
                The outer loop is exited but the inner loop continues execution.

                                               
                The inner loop is exited but the outer loop continues execution. (*)

                                                                               
You need to execute a set of statements 10 times, increasing a counter by 1 each time. Which of the following PL/SQL constructs can do this? (Choose three)   
                                                                               
                                               
                IF ... THEN ... ELSE

                                               
                CASE ... WHEN ... THEN

                                               
                A basic loop (*)

                                               
                A FOR loop (*)

                                               
                A WHILE loop (*)

                                                                               
                                                               
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;
                                                                               
                                               
                ELSIF v_var = 20 THEN (*)

                                               
                ELSIF v_var = 20

                                               
                IF v_var = 20 THEN


                ELSE IF v_var = 20 THEN


What is wrong with the following trivial IF statement:
IF (v_job='President')
THEN v_salary := 10000;
                                                              
                                               
                The condition should be coded: IF (v_job := 'President')

                                               
                IF and THEN must be on the same line: IF (v_job='President') THEN ...

                                               
                END IF; is missing (*)

                                               
                ELSE is missing

                                                                               
                                                               
What is the correct name for CASE, LOOP, WHILE, and IF-THEN-ELSE structures ?             
                                                                               
                                               
                Array structures

                                               
                Control structures (*)

                                               
                Memory structures

                                               
                Cursor structures

                                                                               
                                                               
You should use a WHILE loop when the number of iterations of the loop is known in advance. True or False?  
                                                                               
                                               
                True

                                               
                False (*)

                                                                               
                                                               
In a WHILE loop, the controlling condition is checked at the start of each iteration. True or False?  
                                                                               
                                               
                True (*)

                                               
                False

                                                                               
                                                               
In a FOR loop, an explicitly declared counter is automatically incremented by 1 for each iteration of the loop. True or False?                                  
                                               
                True

                                               
                False (*)


Which kind of loop is this?
v_count := 1;
LOOP
    v_count := v_count + 1;
    EXIT WHEN i > 20;
END LOOP;
                                                        
                                               
                IF-THEN loop

                                               
                WHILE loop

                                               
                Basic loop (*)

                                               
                CASE loop

                                               
                FOR loop

                                                                               
                                                               
Examine the following block:
DECLARE
    v_counter PLS_INTEGER := 1;
BEGIN
    LOOP
       DBMS_OUTPUT.PUT_LINE(v_counter);
       v_counter := v_counter + 1;
    EXIT WHEN v_counter = 5;
    END LOOP;
END;

What is the last value of V_COUNTER that is displayed?
                                                         
                                               
                5

                                               
                4 (*)

                                               
                6

                                               
                This is an infinite loop; the loop will never finish.

                                                                               
                                                               
 A PL/SQL block contains the following code:
v_counter := 1;
LOOP
    EXIT WHEN v_counter=5;
END LOOP;
v_counter := v_counter + 1;
What is the value of V_COUNTER after the loop is finished?

                                                             
                                               
                6

                                               
                1

                                               
                This is an infinite loop; the loop will never finish. (*)

                                               
                5

                                                                               
 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;
                                                                               
                                               
                Equal

                                               
                Unequal

                                               
                Undefined (*)

                                               
                Nothing will be displayed because V_MESSAGE is set to NULL.

                                                                               
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?
                                                              
                                               
                WHEN 90 THEN v_grade := 'A';

                                               
                WHEN 90 THEN = 'A';

                                               
                WHEN 90 THEN grade := 'A'

                                               
                WHEN 90 THEN 'A' (*)

No comments:

Post a Comment

Final Exam Java Programming 2019 Learner - English

Final Exam