YSL

This page is to efficiently manage our group assignments!

Member List

• Email All

First Name	Last Name	wiki id	Learn e-mail	Phone
YuJin	Jeong	yujin.jeong	yjeong	647 - 863 - 5555
Seung Yeon	Moon	saadism	symoon	(647) 678 - 6511
Li Shi	Zhou

Assignment 2 (worth 10%)

Due Date

Friday, December 2nd, X:XXpm

Description

Assignment1

Assignment 1 (worth 10%)

Due Date

Friday, October 21st, X:XXpm Printout submission only( SELECT Statements + ALL outputs)

Description

Assignment1

Solution

1.

YuJin

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;

Seung Yeon

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
/

Li Shi

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

FINAL

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;