|
|
| (15 intermediate revisions by 3 users not shown) |
| Line 1: |
Line 1: |
| − | | + | {{db-g7}} |
| − | | |
| − | 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>
| |
