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

SET ECHO OFF;
SET  SERVEROUTPUT ON;
SET  VERIFY  OFF;
ACCEPT country PROMPT 'Please input the two letter Country Code: ';

DECLARE 
        counter NUMBER(3) := 1;
        a          VARCHAR2(100);
        b          NUMBER(2) := 0;
        v_city   locations.city%TYPE;
             

BEGIN
      SELECT  city,LENGTH(street_address) INTO v_city,b
        FROM     locations
        WHERE  country_id = UPPER('&country') AND state_province IS NULL; 
     
      IF UPPER(v_city) LIKE 'A%' OR UPPER(v_city) LIKE 'B%'
         OR UPPER(v_city) LIKE 'E%' OR UPPER(v_city) LIKE 'F%' THEN
        WHILE  counter <= b  LOOP
              a := a || '*' ;
              counter := counter + 1;
        END LOOP;
      ELSIF UPPER(v_city) LIKE 'C%' OR UPPER(v_city) LIKE 'D%'
        OR UPPER(v_city) LIKE 'G%' OR UPPER(v_city) LIKE 'H%' THEN   
        WHILE  counter <= b  LOOP
              a := a || '&' ;
              counter := counter + 1;
        END LOOP;
      ELSE    
        WHILE  counter <= b  LOOP
              a := a || '#' ;
              counter := counter + 1;
        END LOOP;
      END IF;
        UPDATE locations
        SET state_province = a
        WHERE city = v_city;
   
     DBMS_OUTPUT.PUT_LINE('City ' || v_city || ' has modified its province to ' || a);
    
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('This country has MORE THAN ONE City without province listed.');

END;
/
     SELECT *  FROM  locations
     WHERE state_province LIKE '#%' OR state_province  LIKE '*%' OR state_province LIKE '&';
 
 ROLLBACK;

2.

YuJin

Seung Yeon

Li Shi

3.

YuJin

Seung Yeon

Li Shi

4.

YuJin

Seung Yeon

Li Shi

5.

YuJin

Seung Yeon

Li Shi

Assignment 2 (worth 10%)

Due Date

Friday, December 2nd, X:XXpm

Description

Assignment2