Scribd Logo
Upload

Welcome to Scribd!

  • 22 views

    Database System With Administration: Technical Assessment

    Uploaded by

    Nuriel Aguilar
    This document outlines the requirements and parts for a database systems administration technical assessment, including writing PL/SQL blocks using control structures, cursors, records, and exception handling to query and manipulate data from tables like countries and languages. Test cases are provided to validate the code handles scenarios like no data found, too many rows returned, duplicate key violations, and custom exceptions. Output from the code execution must be demonstrated through screenshots.

    Copyright:

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

    Database System With Administration: Technical Assessment

    Uploaded by

    Nuriel Aguilar
    22 views7 pages
    This document outlines the requirements and parts for a database systems administration technical assessment, including writing PL/SQL blocks using control structures, cursors, records, and exception handling to query and manipulate data from tables like countries and languages. Test cases are provided to validate the code handles scenarios like no data found, too many rows returned, duplicate key violations, and custom exceptions. Output from the code execution must be demonstrated through screenshots.

    Original Title

    M1_M2_M3_TA1_AGUILAR

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document outlines the requirements and parts for a database systems administration technical assessment, including writing PL/SQL blocks using control structures, cursors, records, and exception handling to query and manipulate data from tables like countries and languages. Test cases are provided to validate the code handles scenarios like no data found, too many rows returned, duplicate key violations, and custom exceptions. Output from the code execution must be demonstrated through screenshots.

    Copyright:

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

    Database System With Administration: Technical Assessment

    Uploaded by

    Nuriel Aguilar
    This document outlines the requirements and parts for a database systems administration technical assessment, including writing PL/SQL blocks using control structures, cursors, records, and exception handling to query and manipulate data from tables like countries and languages. Test cases are provided to validate the code handles scenarios like no data found, too many rows returned, duplicate key violations, and custom exceptions. Output from the code execution must be demonstrated through screenshots.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 7

    Database System with Administration

    TECHNICAL ASSESSMENT

    M1, M2, M3
    Name: Aguilar, Nuriel

    Section: TW-22

    Professor: Ms. Geliza Marie Alcober

    Database System with Administration Page 1 of 3


    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)


    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 2 of 3

    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.
    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.

    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”.

    FIRST BLOCK TEST ‘ba’


    SECOND BLOCK TEST ‘ce’

    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)
    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 3 of 3

    You might also like