TA1 - Database

SET SERVEROUTPUT ON;
SET VERIFY OFF;
-- Part 1
DECLARE
    V_AIRPORTS COUNTRIES.AIRPORTS%TYPE;
    V_CNTRYNAME COUNTRIES.COUNTRY_NAME%TYPE := 'Canada';
    V_MESSAGE VARCHAR(70);
BEGIN
    SELECT AIRPORTS
    INTO V_AIRPORTS
    FROM COUNTRIES
    WHERE COUNTRY_NAME = V_CNTRYNAME;

V_MESSAGE := CASE
    WHEN V_AIRPORTS BETWEEN 0 AND 100 THEN 'There are 100 or fewer airports.'
    WHEN V_AIRPORTS BETWEEN 101 AND 1000 THEN 'There are between 101 and 1,000 airports.'
    WHEN V_AIRPORTS BETWEEN 1001 AND 10000 THEN 'There are between 1,001 and 10,000 airports.'
    WHEN V_AIRPORTS > 10000 THEN 'There are more than 10,000 airports.'
    ELSE 'Airport Count not Available.'
    END;
DBMS_OUTPUT.PUT_LINE('SELECTED COUNTRY: ' || V_CNTRYNAME);
DBMS_OUTPUT.PUT_LINE(V_MESSAGE);
END;
/


DROP TABLE WF_COUNTRIES;
CREATE TABLE WF_COUNTRIES AS SELECT * FROM COUNTRIES WHERE 1 = 0;
INSERT INTO WF_COUNTRIES SELECT * FROM COUNTRIES;


DECLARE
V_CNTRYID WF_COUNTRIES.COUNTRY_ID%TYPE;
V_CNTRYNAME WF_COUNTRIES.COUNTRY_NAME%TYPE;
BEGIN
FOR CNTRY_ITE IN (SELECT COUNTRY_ID, COUNTRY_NAME FROM WF_COUNTRIES WHERE COUNTRY_ID BETWEEN 51 AND 55 ORDER BY COUNTRY_ID)
LOOP
    V_CNTRYID := CNTRY_ITE.COUNTRY_ID;
    V_CNTRYNAME := CNTRY_ITE.COUNTRY_NAME;

    DBMS_OUTPUT.PUT_LINE('COUNTRY ID: ' || V_CNTRYID || ' | COUNTRY NAME: ' || V_CNTRYNAME);

    IF V_CNTRYID = 55 THEN
    EXIT;
    END IF;
    END LOOP;
END;
/



-- Part2
DECLARE

CURSOR emp_cursor IS
SELECT first_name, last_name, job_id, salary
FROM EMPLOYEES
ORDER BY salary DESC;


emp_record emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;

FOR i IN 1..6 LOOP
FETCH emp_cursor INTO emp_record;
EXIT WHEN emp_cursor%ROWCOUNT = 0;
DBMS_OUTPUT.PUT_LINE('First Name: ' || emp_record.first_name || ', Last Name: ' || emp_record.last_name ||
', Job ID: ' || emp_record.job_id || ', Salary: ' || emp_record.salary);
END LOOP;

CLOSE emp_cursor;
END;
/

DECLARE
    TYPE COUNTRY_RECORD IS RECORD (
        COUNTRY_NAME COUNTRIES.COUNTRY_NAME%TYPE,
        NATIONAL_HOLIDAY_DATE COUNTRIES.NATIONAL_HOLIDAY_DATE%TYPE,
        NATIONAL_HOLIDAY_NAME COUNTRIES.NATIONAL_HOLIDAY_NAME%TYPE
    );

    V_COUNTRY COUNTRY_RECORD;
    V_INPUT_COUNTRY_NAME COUNTRIES.COUNTRY_NAME%TYPE;
    V_ROW_COUNT NUMBER := 0; -- Counter for rows found
BEGIN
    V_INPUT_COUNTRY_NAME := '&ENTER_COUNTRY_NAME'; -- user input

    FOR COUNTRY_ROW IN (
        SELECT COUNTRY_NAME, NATIONAL_HOLIDAY_DATE, NATIONAL_HOLIDAY_NAME
        FROM COUNTRIES
        WHERE REGION_ID = 5 AND LOWER(COUNTRY_NAME) = LOWER(V_INPUT_COUNTRY_NAME)
    )
    LOOP
        V_COUNTRY.COUNTRY_NAME := COUNTRY_ROW.COUNTRY_NAME;
        V_COUNTRY.NATIONAL_HOLIDAY_DATE := COUNTRY_ROW.NATIONAL_HOLIDAY_DATE;
        V_COUNTRY.NATIONAL_HOLIDAY_NAME := COUNTRY_ROW.NATIONAL_HOLIDAY_NAME;

        DBMS_OUTPUT.PUT_LINE('Country Name: ' || V_COUNTRY.COUNTRY_NAME);
        DBMS_OUTPUT.PUT_LINE('National Holiday Date: ' || V_COUNTRY.NATIONAL_HOLIDAY_DATE);
        DBMS_OUTPUT.PUT_LINE('National Holiday Name: ' || V_COUNTRY.NATIONAL_HOLIDAY_NAME);
        DBMS_OUTPUT.PUT_LINE('----------------------------------------');

        V_ROW_COUNT := V_ROW_COUNT + 1;
    END LOOP;

    IF V_ROW_COUNT = 0 THEN
        DBMS_OUTPUT.PUT_LINE('Country "' || V_INPUT_COUNTRY_NAME || '" not found in region 5 (South America).');
    END IF;
END;
/


--Part 4
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 'al%'; -- for example 'ab%';
    --ba, ce. There are several language_names beginning with “Ba,” but none beginning with “Ce”.
    --al. There is exactly one language_name beginning with“Al”. Note that language_id 80 (Arabic) already exists
    --Now (keeping the substring as “al”), add a non_predefined exception handler to trap then encountered oracle exception code
    INSERT INTO languages (language_id, language_name)
    VALUES (80, NULL);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Error: Language not found.');

    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Error: More than one language found.');

    WHEN DUP_VAL_ON_INDEX THEN
        DBMS_OUTPUT.PUT_LINE('Error: Duplicate language_id insertion attempt.');

    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/