YSL

This page is to efficiently manage our group assignments!

Member List

• Email All

First Name	Last Name	Student ID	Learn e-mail	Phone
YuJin	Jeong	048 - 056 - 097	yjeong	(647) 863 - 5555
Seung Yeon	Moon	-	symoon	(647) 678 - 6511
Li Shi	Zhou	057 - 268 - 070	lzhou	(416) 887 - 6758

Assignment 1 (worth 10%)

Due Date

Friday, October 24st, 4:00pm

Description

Assignment1 Submit PRINTOUT and EMAIL with ATTACHMENT

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;

Assignment 2 (worth 10%)

Due Date

Friday, December 2nd, X:XXpm

Description

Assignment2