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> =Assignment 2 (worth 10%)===Due Date==Friday, December 2nd, X:XXpm ==Description==[https://cs.senecac.on.ca/~nconkic/as2.doc Assignment2] ==Solution==