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>If anyone know how to put these two linesROLLBACK;select * from countries where flag IS NOT NULL;after END; in pl/sql block please let me know how....I finally figured out how to put set serveroutput on before DECLAREbut if i put those two lines after END; program doesnt work...If you have same problem remove last two line and try everything else should work fine.This is Question 2</pre><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;ROLLBACK;select * from countries where flag IS NOT NULL;/</pre>''Li Shi''<pre>select department_id, department_name, substr(nvl(city,'Not Assigned Yet'),1,25) City,count(distinct job_id) "# of Jobs"from employees right outer join departmentsusing (department_id)left outer join locationsusing (location_id)group by department_id, department_name, city</pre> ''FINAL''<pre>SELECT department_id, department_name, SUBSTR(NVL(city, 'Not Assinged Yet'),1, 25) "City", COUNT(DISTINCT job_id) "# of Jobs"FROM employeesRIGHT OUTER JOIN departments USING (department_id)LEFT OUTER JOIN locations USING (location_id)GROUP BY department_id, department_name, city; </pre> =Assignment 2 (worth 10%)===Due Date==Friday, December 2nd, X:XXpm ==Description==[https://cs.senecac.on.ca/~nconkic/as2.doc Assignment2] ==Solution==
