|
|
| Line 27: |
Line 27: |
| | [https://cs.senecac.on.ca/~nconkic/as1.doc Assignment1] | | [https://cs.senecac.on.ca/~nconkic/as1.doc Assignment1] |
| | Submit PRINTOUT and EMAIL with ATTACHMENT | | Submit PRINTOUT and EMAIL with ATTACHMENT |
| − |
| |
| − | ==Solution==
| |
| − |
| |
| − |
| |
| − | ===1.===
| |
| − | ''YuJin''
| |
| − | <pre>
| |
| − | SET SERVEROUTPUT ON
| |
| − | SET VERIFY OFF
| |
| − | ACCEPT country_code PROMPT 'Enter value for country: '
| |
| − | VARIABLE b_loc_id NUMBER
| |
| − | DECLARE
| |
| − | v_country_code COUNTRIES.country_id%TYPE := '&country_code';
| |
| − | v_city LOCATIONS.city%TYPE;
| |
| − | v_city_letter CHAR(1);
| |
| − | v_street LOCATIONS.street_address%TYPE;
| |
| − | v_street_len NUMBER;
| |
| − | v_state_province LOCATIONS.state_province%TYPE := ' ';
| |
| − | BEGIN
| |
| − | SELECT location_id, street_address, city INTO :b_loc_id, v_street, v_city
| |
| − | FROM LOCATIONS
| |
| − | WHERE country_id = v_country_code
| |
| − | AND state_province IS NULL;
| |
| − |
| |
| − | v_street_len := LENGTH(v_street);
| |
| − | v_city_letter := SUBSTR(UPPER(v_city), 1, 1);
| |
| − |
| |
| − | IF v_city_letter IN ('A', 'B', 'C', 'F') THEN
| |
| − | v_state_province := RPAD(v_state_province, v_street_len, '*');
| |
| − | ELSIF v_city_letter IN ('C', 'D', 'G', 'H') THEN
| |
| − | v_state_province := RPAD(v_state_province, v_street_len, '&');
| |
| − | ELSE
| |
| − | v_state_province := RPAD(v_state_province, v_street_len, '#');
| |
| − | END IF;
| |
| − |
| |
| − | UPDATE LOCATIONS
| |
| − | SET state_province = v_state_province
| |
| − | WHERE location_id = :b_loc_id;
| |
| − |
| |
| − | DBMS_OUTPUT.PUT_LINE('City ' || v_city || ' has modified its province to ' || v_state_province);
| |
| − |
| |
| − | EXCEPTION
| |
| − | WHEN NO_DATA_FOUND THEN
| |
| − | DBMS_OUTPUT.PUT_LINE('This country has NO cities listed.');
| |
| − | WHEN TOO_MANY_ROWS THEN
| |
| − | DBMS_OUTPUT.PUT_LINE('City ' || v_city || 'has MORE THAN ONE City without province listed.');
| |
| − | END;
| |
| − | /
| |
| − |
| |
| − | SELECT *
| |
| − | FROM LOCATIONS
| |
| − | WHERE location_id = :b_loc_id;
| |
| − |
| |
| − | ROLLBACK;
| |
| − | </pre>
| |
| − | ''Seung Yeon''
| |
| − | <pre>
| |
| − | set serveroutput on
| |
| − | set verify off
| |
| − | set pagesize 200
| |
| − | accept id prompt 'Enter value for region: ';
| |
| − |
| |
| − | DECLARE
| |
| − | v_region REGIONS.region_id%TYPE := &id;
| |
| − | v_region_name regions.region_name%TYPE;
| |
| − | v_country_id countries.country_id%TYPE;
| |
| − | v_country_name countries.country_name%TYPE;
| |
| − | v_region_id countries.region_id%TYPE;
| |
| − | v_country_count NUMBER:=0;
| |
| − | BEGIN
| |
| − | SELECT region_name INTO v_region_name
| |
| − | FROM regions
| |
| − | WHERE region_id = v_region;
| |
| − |
| |
| − | SELECT t1.country_id, t1.country_name, t1.region_id INTO v_country_id, v_country_name, v_region_id
| |
| − | FROM (SELECT country_id, country_name, region_id
| |
| − | FROM countries
| |
| − | WHERE country_id NOT IN
| |
| − | (SELECT country_id
| |
| − | FROM locations
| |
| − | GROUP BY country_id)) t1
| |
| − | WHERE t1.region_id = v_region;
| |
| − | SELECT COUNT(country_id) INTO v_country_count
| |
| − | FROM countries
| |
| − | WHERE country_id NOT IN
| |
| − | (SELECT country_id
| |
| − | FROM locations
| |
| − | GROUP BY country_id);
| |
| − | DBMS_OUTPUT.PUT_LINE ('In the region ' || v_region || ' there is ONE country ' || v_country_name || ' with NO city.');
| |
| − | DBMS_OUTPUT.PUT_LINE ('Number of countries with NO cities listed is: ' || v_country_count);
| |
| − | UPDATE countries
| |
| − | SET flag = concat('Empty_',to_char(region_id))
| |
| − | WHERE country_id NOT IN
| |
| − | (SELECT country_id
| |
| − | FROM locations
| |
| − | GROUP BY country_id);
| |
| − | EXCEPTION
| |
| − | WHEN NO_DATA_FOUND THEN
| |
| − | DBMS_OUTPUT.PUT_LINE ('This region ID does NOT exist: ' || v_region);
| |
| − | WHEN TOO_MANY_ROWS THEN
| |
| − | DBMS_OUTPUT.PUT_LINE('This region ID has MORE THAN ONE country without cities listed: ' || v_region);
| |
| − | END;
| |
| − | /
| |
| − | select * from countries where flag IS NOT NULL;
| |
| − | ROLLBACK;
| |
| − | </pre>
| |
| − | ''Li Shi''
| |
| − | <pre>
| |
| − | SET ECHO OFF;
| |
| − | SET SERVEROUTPUT ON;
| |
| − | SET VERIFY OFF;
| |
| − | ACCEPT country PROMPT 'Please input the two letter Country Code: ';
| |
| − |
| |
| − | DECLARE
| |
| − | counter NUMBER(3) := 1;
| |
| − | a VARCHAR2(100);
| |
| − | b NUMBER(2) := 0;
| |
| − | v_city locations.city%TYPE;
| |
| − |
| |
| − |
| |
| − | BEGIN
| |
| − | SELECT city,LENGTH(street_address) INTO v_city,b
| |
| − | FROM locations
| |
| − | WHERE country_id = UPPER('&country') AND state_province IS NULL;
| |
| − |
| |
| − | IF UPPER(v_city) LIKE 'A%' OR UPPER(v_city) LIKE 'B%'
| |
| − | OR UPPER(v_city) LIKE 'E%' OR UPPER(v_city) LIKE 'F%' THEN
| |
| − | WHILE counter <= b LOOP
| |
| − | a := a || '*' ;
| |
| − | counter := counter + 1;
| |
| − | END LOOP;
| |
| − | ELSIF UPPER(v_city) LIKE 'C%' OR UPPER(v_city) LIKE 'D%'
| |
| − | OR UPPER(v_city) LIKE 'G%' OR UPPER(v_city) LIKE 'H%' THEN
| |
| − | WHILE counter <= b LOOP
| |
| − | a := a || '&' ;
| |
| − | counter := counter + 1;
| |
| − | END LOOP;
| |
| − | ELSE
| |
| − | WHILE counter <= b LOOP
| |
| − | a := a || '#' ;
| |
| − | counter := counter + 1;
| |
| − | END LOOP;
| |
| − | END IF;
| |
| − | UPDATE locations
| |
| − | SET state_province = a
| |
| − | WHERE city = v_city;
| |
| − |
| |
| − | DBMS_OUTPUT.PUT_LINE('City ' || v_city || ' has modified its province to ' || a);
| |
| − |
| |
| − | EXCEPTION
| |
| − | WHEN NO_DATA_FOUND THEN
| |
| − | DBMS_OUTPUT.PUT_LINE('This country has NO cities listed.');
| |
| − | WHEN TOO_MANY_ROWS THEN
| |
| − | DBMS_OUTPUT.PUT_LINE('This country has MORE THAN ONE City without province listed.');
| |
| − |
| |
| − | END;
| |
| − | /
| |
| − | SELECT * FROM locations
| |
| − | WHERE state_province LIKE '#%' OR state_province LIKE '*%' OR state_province LIKE '&';
| |
| − |
| |
| − | ROLLBACK;
| |
| − |
| |
| − | </pre>
| |
| − |
| |
| − |
| |
| − | ===2.===
| |
| − |
| |
| − |
| |
| − | ''YuJin''
| |
| − |
| |
| − | <pre>
| |
| − |
| |
| − | SET SERVEROUTPUT ON
| |
| − | SET VERIFY OFF
| |
| − | ACCEPT region PROMPT 'Enter value for region: '
| |
| − |
| |
| − |
| |
| − | DECLARE
| |
| − | v_region_id REGIONS.region_id%TYPE;
| |
| − | country COUNTRIES.country_name%TYPE;
| |
| − | BEGIN
| |
| − | SELECT region_id INTO v_region_id
| |
| − | FROM REGIONS
| |
| − | WHERE region_id = ®ion;
| |
| − |
| |
| − |
| |
| − | BEGIN <<inner>>
| |
| − | SELECT country_name INTO country
| |
| − | FROM COUNTRIES
| |
| − | WHERE region_id = ®ion
| |
| − | AND country_id NOT IN (
| |
| − | SELECT DISTINCT country_id
| |
| − | FROM LOCATIONS
| |
| − | );
| |
| − |
| |
| − |
| |
| − | IF SQL%ROWCOUNT = 1 THEN
| |
| − | DBMS_OUTPUT.PUT_LINE('In the region ' || ®ion || ' there is ONE country ' || country || ' with NO city');
| |
| − |
| |
| − |
| |
| − | UPDATE COUNTRIES
| |
| − | SET flag = 'Empty_' || region_id
| |
| − | WHERE country_id NOT IN (
| |
| − | SELECT DISTINCT country_id
| |
| − | FROM LOCATIONS
| |
| − | );
| |
| − |
| |
| − |
| |
| − | DBMS_OUTPUT.PUT_LINE('Number of countries with NO cities listed is: ' || SQL%ROWCOUNT);
| |
| − | END IF;
| |
| − |
| |
| − |
| |
| − | EXCEPTION
| |
| − | WHEN TOO_MANY_ROWS THEN
| |
| − | DBMS_OUTPUT.PUT_LINE('This region ID has MORE THAN ONE country without cities listed: ' || ®ion);
| |
| − | END inner;
| |
| − |
| |
| − |
| |
| − | EXCEPTION
| |
| − | WHEN NO_DATA_FOUND THEN
| |
| − | DBMS_OUTPUT.PUT_LINE('This region ID does NOT exist: ' || ®ion);
| |
| − | END;
| |
| − | /
| |
| − |
| |
| − |
| |
| − | SELECT *
| |
| − | FROM COUNTRIES
| |
| − | WHERE country_id NOT IN (
| |
| − | SELECT DISTINCT country_id
| |
| − | FROM LOCATIONS
| |
| − | )
| |
| − | AND flag IS NOT NULL
| |
| − | ORDER BY region_id, country_name;
| |
| − |
| |
| − |
| |
| − | ROLLBACK;
| |
| − | </pre>
| |
| − |
| |
| − |
| |
| − | ''Seung Yeon''
| |
| − | <pre>
| |
| − |
| |
| − | </pre>
| |
| − |
| |
| − | ''Li Shi''
| |
| − |
| |
| − | <pre>
| |
| − | </pre>
| |
| − |
| |
| − | ===3.===
| |
| − |
| |
| − |
| |
| − |
| |
| − |
| |
| − | ''YuJin''
| |
| − |
| |
| − | <pre>
| |
| − |
| |
| − |
| |
| − | SET SERVEROUTPUT ON
| |
| − | SET VERIFY OFF
| |
| − | ACCEPT region PROMPT 'Enter value for region: '
| |
| − |
| |
| − |
| |
| − | DECLARE
| |
| − | CURSOR c_region_cursor IS
| |
| − | SELECT region_id
| |
| − | FROM REGIONS
| |
| − | WHERE region_id = ®ion;
| |
| − |
| |
| − |
| |
| − | TYPE empty_cntry_tab_type IS TABLE OF
| |
| − | COUNTRIES%ROWTYPE
| |
| − | INDEX BY PLS_INTEGER;
| |
| − |
| |
| − |
| |
| − | CURSOR c_empty_cntry_cursor IS
| |
| − | SELECT region_id, country_name
| |
| − | FROM COUNTRIES
| |
| − | WHERE country_id NOT IN (
| |
| − | SELECT DISTINCT country_id
| |
| − | FROM LOCATIONS
| |
| − | )
| |
| − | ORDER BY country_name;
| |
| − |
| |
| − |
| |
| − | v_region_id REGIONS.region_id%TYPE;
| |
| − | v_index NUMBER := 1;
| |
| − | v_counter NUMBER := 0;
| |
| − | empty_cntry_tab empty_cntry_tab_type;
| |
| − |
| |
| − |
| |
| − | BEGIN
| |
| − |
| |
| − |
| |
| − | FOR region IN c_region_cursor LOOP
| |
| − | v_counter := v_counter + 1;
| |
| − | END LOOP;
| |
| − |
| |
| − |
| |
| − | IF v_counter = 0 THEN
| |
| − | DBMS_OUTPUT.PUT_LINE('This region ID does NOT exist: ' || ®ion);
| |
| − | ELSE
| |
| − | v_counter := 1;
| |
| − |
| |
| − |
| |
| − | UPDATE COUNTRIES
| |
| − | SET flag = 'Empty_' || region_id
| |
| − | WHERE country_id NOT IN (
| |
| − | SELECT DISTINCT country_id
| |
| − | FROM LOCATIONS
| |
| − | );
| |
| − |
| |
| − |
| |
| − | FOR cntry IN c_empty_cntry_cursor LOOP
| |
| − | empty_cntry_tab(v_index).region_id := cntry.region_id;
| |
| − | empty_cntry_tab(v_index).country_name := cntry.country_name;
| |
| − | DBMS_OUTPUT.PUT_LINE('Index Table Key: ' || v_index || ' has a value of ' || empty_cntry_tab(v_index).country_name);
| |
| − | v_index := v_index + 5;
| |
| − | END LOOP;
| |
| − |
| |
| − |
| |
| − | DBMS_OUTPUT.PUT_LINE('========================================================================');
| |
| − | DBMS_OUTPUT.PUT_LINE('Total number of elements in the Index Table or Number of countries with NO cities listed is: ' ||
| |
| − | empty_cntry_tab.COUNT);
| |
| − | DBMS_OUTPUT.PUT_LINE('Second element (Country) in the Index Table is: ' ||
| |
| − | empty_cntry_tab(empty_cntry_tab.NEXT(empty_cntry_tab.FIRST)).country_name);
| |
| − | DBMS_OUTPUT.PUT_LINE('Before the last element (Country) in the Index Table is: ' ||
| |
| − | empty_cntry_tab(empty_cntry_tab.PRIOR(empty_cntry_tab.LAST)).country_name);
| |
| − | DBMS_OUTPUT.PUT_LINE('========================================================================');
| |
| − |
| |
| − |
| |
| − | FOR cntry IN c_empty_cntry_cursor LOOP
| |
| − | IF cntry.region_id = ®ion THEN
| |
| − | DBMS_OUTPUT.PUT_LINE('In the region : ' || ®ion || ' there is country ' || cntry.country_name || ' with NO city.');
| |
| − | v_counter := v_counter + 1;
| |
| − | END IF;
| |
| − | END LOOP;
| |
| − |
| |
| − |
| |
| − | DBMS_OUTPUT.PUT_LINE('========================================================================');
| |
| − | DBMS_OUTPUT.PUT_LINE('Total Number of countries with NO cities listed in the Region ' || ®ion || ' is: ' || v_counter);
| |
| − | END IF;
| |
| − | END;
| |
| − | /
| |
| − |
| |
| − |
| |
| − | SELECT *
| |
| − | FROM COUNTRIES
| |
| − | WHERE country_id NOT IN (
| |
| − | SELECT DISTINCT country_id
| |
| − | FROM LOCATIONS
| |
| − | )
| |
| − | AND flag IS NOT NULL
| |
| − | ORDER BY region_id, country_name;
| |
| − |
| |
| − |
| |
| − | ROLLBACK;
| |
| − |
| |
| − |
| |
| − | </pre>
| |
| − |
| |
| − |
| |
| − | ''Seung Yeon''
| |
| − | <pre>
| |
| − |
| |
| − | </pre>
| |
| − |
| |
| − | ''Li Shi''
| |
| − |
| |
| − | <pre>
| |
| − | </pre>
| |
| − |
| |
| − | ===4.===
| |
| − |
| |
| − |
| |
| − | ''YuJin''
| |
| − |
| |
| − | <pre>
| |
| − | </pre>
| |
| − |
| |
| − |
| |
| − | ''Seung Yeon''
| |
| − | <pre>
| |
| − |
| |
| − | </pre>
| |
| − |
| |
| − | ''Li Shi''
| |
| − |
| |
| − | <pre>
| |
| − | </pre>
| |
| − |
| |
| − |
| |
| − |
| |
| − |
| |
| − |
| |
| − | ===5.===
| |
| − |
| |
| − |
| |
| − |
| |
| − | ''YuJin''
| |
| − |
| |
| − | <pre>
| |
| − | </pre>
| |
| − |
| |
| − |
| |
| − | ''Seung Yeon''
| |
| − | <pre>
| |
| − |
| |
| − | </pre>
| |
| − |
| |
| − | ''Li Shi''
| |
| − |
| |
| − | <pre>
| |
| − | </pre>
| |
| | | | |
| | =Assignment 2 (worth 10%)= | | =Assignment 2 (worth 10%)= |
