Scribd Logo
Upload

Welcome to Scribd!

  • 44 views

    M1 M2 M3 TA1 1 Granada PDF

    Uploaded by

    Nuriel Aguilar
    1. The document provides requirements and questions for a technical assessment involving PL/SQL code and database administration tasks. 2. It includes 4 parts testing control structures, explicit cursors, record structures, and exception handling using the Oracle database. 3. Students are asked to write PL/SQL blocks to retrieve and display data from database tables, using techniques like looping, cursors, records and exception handling.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    M1 M2 M3 TA1 1 Granada PDF

    Uploaded by

    Nuriel Aguilar
    44 views11 pages
    1. The document provides requirements and questions for a technical assessment involving PL/SQL code and database administration tasks. 2. It includes 4 parts testing control structures, explicit cursors, record structures, and exception handling using the Oracle database. 3. Students are asked to write PL/SQL blocks to retrieve and display data from database tables, using techniques like looping, cursors, records and exception handling.

    Original Title

    M1 M2 M3 TA1 1 Granada.pdf

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    1. The document provides requirements and questions for a technical assessment involving PL/SQL code and database administration tasks. 2. It includes 4 parts testing control structures, explicit cursors, record structures, and exception handling using the Oracle database. 3. Students are asked to write PL/SQL blocks to retrieve and display data from database tables, using techniques like looping, cursors, records and exception handling.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    44 views11 pages

    M1 M2 M3 TA1 1 Granada PDF

    Uploaded by

    Nuriel Aguilar
    1. The document provides requirements and questions for a technical assessment involving PL/SQL code and database administration tasks. 2. It includes 4 parts testing control structures, explicit cursors, record structures, and exception handling using the Oracle database. 3. Students are asked to write PL/SQL blocks to retrieve and display data from database tables, using techniques like looping, cursors, records and exception handling.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 11

    Database System with

    Administration

    TECHNICAL ASSESSMENT
    M1, M2, M3

    Name: Mark Joseph Granada


    1-A
    Section:
    Ms. Gelai
    Professor:

    Database System with Administration Page 1 of 11


    REQUIREMENTS:
    • Screen shots of your code and output from Oracle Cloud - SQL Developer Web.
    • Paste your screen shots after each question.
    • Your screen shots must show your worskspace name.
    • Codes and output must be readable from the screen shots.
    ____________________________________________________________________________________

    PART-1. Control Structures. (10pts)

    1.) Write a PL/SQL block:

    To find the number of airports from the countries table for a supplied country_name.
    Based on this number, display a customized message as follows:

    Use a CASE statement to process your comparisons. (5 marks)

    Database System with Administration Page 2 of 11


    2.) Using any of the PL/SQL looping statement, write a PL/SQL block to display the
    country_id and country_name values from the WF_COUNTRIES table for country_id
    whose values range from 51 through 55. Test your variable to see when it reaches 55.
    EXIT the loop after you have displayed the 5 countries. (5 marks)

    Database System with Administration Page 3 of 11


    Database System with Administration Page 4 of 11
    PART-2. Explicit Cursor and Record Structure (10 Points)

    Create a PL/SQL block that fetches and displays the six employees with the highest
    salary. For each of these employees, display the first name, last name, job id, and
    salary. Order your output so that the employee with the highest salary is displayed first.
    Use %ROWTYPE and the explicit cursor attribute
    %ROWCOUNT.

    Database System with Administration Page 5 of 11


    PART-3. Explicit Cursor and User Defined Structure (15 Points)

    Write a PL/SQL block to read through rows in the countries table for all countries in region
    5 (South America region). Country name must be entered by the user. For each selected
    country, display the country_name, national_holiday_date, and national_holiday_name.
    Use a record structure (user defined) to hold all the columns selected from the countries
    table.

    Database System with Administration Page 6 of 11


    PART-4. Exception Handling. (15 Points)

    A. Add an exception handler to the following code to trap the following predefined Oracle
    Server errors:
    NO_DATA_FOUND, TOO_MANY_ROWS, and DUP_VAL_ON_INDEX. (5 pts)

    DECLARE v_language_id
    languages.language_id%TYPE;
    v_language_name
    languages.language_name%TYPE;
    BEGIN
    SELECT language_id, language_name INTO v_language_id, v_language_name FROM
    languages WHERE LOWER(language_name) LIKE '<substring%>'; -- for example
    'ab%'

    INSERT INTO languages(language_id, language_name) VALUES(80, null);


    END;

    Note: Test your block twice using each of the following language substrings: ba, ce.
    There are several language_names beginning with “Ba,” but none beginning with “Ce”.

    Database System with Administration Page 7 of 11


    1st block test (ba)

    Database System with Administration Page 8 of 11


    2nd block test (ce)

    Database System with Administration Page 9 of 11


    B. Now test your block a third time using substring: al. There is exactly one language_name
    beginning with
    “Al”. Note that language_id 80 (Arabic) already exists. Explain the output. (5 pts)
    An “NO_DATA_FOUND” appears

    Database System with Administration Page 10 of 11


    C. Now (keeping the substring as “al”), add a non_predefined exception handler to trap then
    encountered oracle exception code. Rerun the code and explain the result. (5 pts)

    Database System with Administration Page 11 of 11

    You might also like