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>SELECT department_id, department_name, RPAD(TRIM(NVL(city, 'Not Assinged Yet')), 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>''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==