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>SELECT department_id, department_name, SUBSTR(NVL(city,'Not Assigned Yet'),1,25) City, COUNT(DISTINCT job_id) "# of Jobs"FROM locations l RIGHT OUTER JOIN departments dUSING (location_id)LEFT OUTER JOIN employees eUSING (department_id)GROUP BY department_id, department_name, city/</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==