[https://cs.senecac.on.ca/~nconkic/as1.doc Assignment1]
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%)=