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

SET SERVEROUTPUT ON
SET VERIFY OFF
ACCEPT country_code PROMPT 'Enter value for country: '
VARIABLE b_loc_id NUMBER
DECLARE
    v_country_code    COUNTRIES.country_id%TYPE := '&country_code';   
    v_city            LOCATIONS.city%TYPE;
    v_city_letter     CHAR(1);
    v_street          LOCATIONS.street_address%TYPE;
    v_street_len      NUMBER;
    v_state_province  LOCATIONS.state_province%TYPE := ' ';
BEGIN
    SELECT location_id, street_address, city INTO :b_loc_id, v_street, v_city
    FROM   LOCATIONS
    WHERE  country_id = v_country_code
    AND    state_province IS NULL;

    v_street_len  := LENGTH(v_street);
    v_city_letter := SUBSTR(UPPER(v_city), 1, 1);

    IF v_city_letter IN ('A', 'B', 'C', 'F') THEN
	v_state_province := RPAD(v_state_province, v_street_len, '*');
    ELSIF v_city_letter IN ('C', 'D', 'G', 'H') THEN
	v_state_province := RPAD(v_state_province, v_street_len, '&');
    ELSE
	v_state_province := RPAD(v_state_province, v_street_len, '#');
    END IF;

    UPDATE LOCATIONS
    SET    state_province = v_state_province
    WHERE  location_id = :b_loc_id;

    DBMS_OUTPUT.PUT_LINE('City ' || v_city || ' has modified its province to ' || v_state_province);

    EXCEPTION 
	WHEN NO_DATA_FOUND THEN
	    DBMS_OUTPUT.PUT_LINE('This country has NO cities listed.');
	WHEN TOO_MANY_ROWS THEN
	    DBMS_OUTPUT.PUT_LINE('City ' || v_city || 'has MORE THAN ONE City without province listed.');
END;
/

SELECT * 
FROM   LOCATIONS 
WHERE  location_id = :b_loc_id; 

ROLLBACK;

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