Changes

This page is to efficiently manage our group assignments!== Member List == *[mailto:yjeong@learn.senecac.on.ca,?subject=dbs501,symoon@learn.senecac.on.ca,?subject=dbs501,lzhou@learn.senecac.on.ca,?subject=dbs501 Email All] {| class="wikitable sortable" border="1" cellpadding="5"! First Name !! Last Name !! Student ID !! Learn e{db-mail !! Phone|-|YuJin ||Jeong|| 048 - 056 - 097 ||[mailto:yjeong@learn.senecac.on.ca?sujbect=dbs501 yjeong]|| (647) 863 - 5555|- |-|Seung Yeon||Moon || - ||[mailto:symoon@learn.senecac.on.ca?subject=dbs501 symoon] ||(647) 678 - 6511|- |-|Li Shi|| Zhou || 057 - 268 - 070 || [mailto:lzhou@learn.senecac.on.ca?subject=dbs501 lzhou] || (416) 887 - 6758|-|g7}} =Assignment 1 (worth 10%)=  ==Due Date==Friday, October 24st, 4:00pm ==Description==[https://cs.senecac.on.ca/~nconkic/as1.doc Assignment1]Submit PRINTOUT and EMAIL with ATTACHMENT ==Solution==  ===1.===''YuJin''<pre>SET SERVEROUTPUT ONSET VERIFY OFFACCEPT country_code PROMPT 'Enter value for country: 'VARIABLE b_loc_id NUMBERDECLARE 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 onset verify offset pagesize 200accept 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 ONSET VERIFY OFFACCEPT 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 = &region;   BEGIN <<inner>> SELECT country_name INTO country FROM COUNTRIES WHERE region_id = &region AND country_id NOT IN ( SELECT DISTINCT country_id FROM LOCATIONS );   IF SQL%ROWCOUNT = 1 THEN DBMS_OUTPUT.PUT_LINE('In the region ' || &region || ' 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: ' || &region); END inner;    EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('This region ID does NOT exist: ' || &region);END;/  SELECT *FROM COUNTRIESWHERE country_id NOT IN ( SELECT DISTINCT country_id FROM LOCATIONS)AND flag IS NOT NULLORDER BY region_id, country_name;  ROLLBACK;</pre>  ''Seung Yeon''<pre> </pre> ''Li Shi'' <pre></pre> ===3.===    ''YuJin'' <pre>  SET SERVEROUTPUT ONSET VERIFY OFFACCEPT region PROMPT 'Enter value for region: '  DECLARE CURSOR c_region_cursor IS SELECT region_id FROM REGIONS WHERE region_id = &region;   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: ' || &region); 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 = &region THEN DBMS_OUTPUT.PUT_LINE('In the region : ' || &region || ' 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 ' || &region || ' is: ' || v_counter); END IF;END;/  SELECT *FROM COUNTRIESWHERE country_id NOT IN ( SELECT DISTINCT country_id FROM LOCATIONS)AND flag IS NOT NULLORDER 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%)===Due Date==Friday, December 2nd, X:XXpm ==Description==[https://cs.senecac.on.ca/~nconkic/as2.doc Assignment2] ==Solution==