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

set serveroutput on
set verify off
set pagesize 200
accept id prompt 'Enter value for region: ';

DECLARE
   v_region REGIONS.region_id%TYPE := &id;
   v_region_name regions.region_name%TYPE;
   v_country_id countries.country_id%TYPE;
   v_country_name countries.country_name%TYPE;
   v_region_id countries.region_id%TYPE;
   v_country_count NUMBER:=0;
BEGIN
   SELECT region_name INTO v_region_name 
   FROM regions
   WHERE region_id = v_region;
	
   SELECT t1.country_id, t1.country_name, t1.region_id INTO v_country_id, v_country_name, v_region_id
   FROM (SELECT country_id, country_name, region_id
	FROM countries
      	WHERE country_id NOT IN
			(SELECT country_id
			FROM locations
			GROUP BY country_id)) t1
   WHERE t1.region_id = v_region; 
   SELECT COUNT(country_id) INTO v_country_count
   FROM countries
   WHERE country_id NOT IN
	(SELECT country_id
	FROM locations
	GROUP BY country_id);
   DBMS_OUTPUT.PUT_LINE ('In the region ' || v_region || ' there is ONE country ' || v_country_name || ' with NO city.');
   DBMS_OUTPUT.PUT_LINE ('Number of countries with NO cities listed is: ' || v_country_count);
   UPDATE countries
   SET    flag = concat('Empty_',to_char(region_id))
   WHERE country_id NOT IN
		(SELECT country_id
		FROM locations
		GROUP BY country_id);
EXCEPTION
	WHEN NO_DATA_FOUND THEN
	DBMS_OUTPUT.PUT_LINE ('This region ID does NOT exist: ' || v_region);
	WHEN TOO_MANY_ROWS THEN
	DBMS_OUTPUT.PUT_LINE('This region ID has MORE THAN ONE country without cities listed: ' || v_region);   
END;
/
select * from countries where flag IS NOT NULL;
ROLLBACK;

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