1
edit
Changes
YSL
,Created page with ' 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,lzho…'
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 !! wiki id !! Learn e-mail !! Phone
|-
|[[User:Yujin.jeong | YuJin]]||Jeong||[[Special:Contributions/yujin.jeong | yujin.jeong]]||[mailto:yjeong@learn.senecac.on.ca?sujbect=dbs301 yjeong]|| 647 - 863 - 5555
|-
|-
|Seung Yeon||Moon || saadism ||[mailto:symoon@learn.senecac.on.ca?subject=dbs301 symoon] ||(647) 678 - 6511
|-
|-
|Li Shi|| Zhou || || ||
|-
|}
=Assignment 2 (worth 10%)=
==Due Date==
Friday, December 2nd, X:XXpm
==Description==
[https://cs.senecac.on.ca/~nconkic/as1.doc Assignment1]
==Solution==
=Assignment 1 (worth 10%)=
==Due Date==
Friday, October 21st, X:XXpm
Printout submission only( SELECT Statements + ALL outputs)
==Description==
[https://cs.senecac.on.ca/~nconkic/as1.doc Assignment1]
==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 employees
RIGHT 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 d
USING (location_id)
LEFT OUTER JOIN employees e
USING (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 departments
using (department_id)
left outer join locations
using (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 employees
RIGHT OUTER JOIN departments USING (department_id)
LEFT OUTER JOIN locations USING (location_id)
GROUP BY department_id, department_name, city;
</pre>