YSL

From CDOT Wiki
Revision as of 13:03, 16 October 2011 by Yjeong (talk | contribs) (→‎3.)
Jump to: navigation, search

This page is to efficiently manage our group assignments!

Member List

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 


SET SERVEROUTPUT ON
SET VERIFY OFF
ACCEPT region PROMPT 'Enter value for region: '


DECLARE
    v_region_id REGIONS.region_id%TYPE;
    country     COUNTRIES.country_name%TYPE;
BEGIN
    SELECT region_id INTO v_region_id
    FROM   REGIONS
    WHERE  region_id = &region;


    BEGIN <<inner>>
        SELECT country_name INTO country
        FROM   COUNTRIES
        WHERE  region_id = &region
        AND    country_id NOT IN (
            SELECT DISTINCT country_id
            FROM   LOCATIONS
        );


        IF SQL%ROWCOUNT = 1 THEN
            DBMS_OUTPUT.PUT_LINE('In the region ' || &region || ' there is ONE country ' || country || ' with NO city');


            UPDATE COUNTRIES
            SET    flag = 'Empty_' || region_id
            WHERE  country_id NOT IN (
                SELECT DISTINCT country_id
                FROM   LOCATIONS
            );


            DBMS_OUTPUT.PUT_LINE('Number of countries with NO cities listed is: ' || SQL%ROWCOUNT);
        END IF;


        EXCEPTION
            WHEN TOO_MANY_ROWS THEN
                DBMS_OUTPUT.PUT_LINE('This region ID has MORE THAN ONE country without cities listed: ' || &region);
    END inner; 


    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('This region ID does NOT exist: ' || &region);
END;
/


SELECT *
FROM   COUNTRIES
WHERE  country_id NOT IN (
    SELECT DISTINCT country_id
    FROM   LOCATIONS
)
AND    flag IS NOT NULL
ORDER BY region_id, country_name;


ROLLBACK;


Seung Yeon

Li Shi 



3.


YuJin





SET SERVEROUTPUT ON
SET VERIFY OFF
ACCEPT region PROMPT 'Enter value for region: '


DECLARE
    CURSOR c_region_cursor IS 
        SELECT   region_id 
        FROM     REGIONS
	WHERE    region_id = &region;


    TYPE empty_cntry_tab_type IS TABLE OF
        COUNTRIES%ROWTYPE
        INDEX BY PLS_INTEGER;


    CURSOR c_empty_cntry_cursor IS 
        SELECT   region_id, country_name 
        FROM     COUNTRIES
        WHERE  country_id NOT IN (
            SELECT DISTINCT country_id
            FROM   LOCATIONS
        )
        ORDER BY country_name;


    v_region_id     REGIONS.region_id%TYPE;
    v_index         NUMBER := 1;
    v_counter       NUMBER := 0;
    empty_cntry_tab empty_cntry_tab_type;


BEGIN


    FOR region IN c_region_cursor LOOP
        v_counter := v_counter + 1;
    END LOOP;


    IF v_counter = 0 THEN
        DBMS_OUTPUT.PUT_LINE('This region ID does NOT exist: ' || &region);
    ELSE
        v_counter := 1;


        UPDATE COUNTRIES
        SET    flag = 'Empty_' || region_id
        WHERE  country_id NOT IN (
            SELECT DISTINCT country_id
            FROM   LOCATIONS
        );


        FOR cntry IN c_empty_cntry_cursor LOOP
	    empty_cntry_tab(v_index).region_id := cntry.region_id;
	    empty_cntry_tab(v_index).country_name := cntry.country_name;
	    DBMS_OUTPUT.PUT_LINE('Index Table Key: ' || v_index || ' has a value of ' || empty_cntry_tab(v_index).country_name);
	    v_index := v_index + 5;
        END LOOP;


        DBMS_OUTPUT.PUT_LINE('========================================================================');
        DBMS_OUTPUT.PUT_LINE('Total number of elements in the Index Table or Number of countries with NO cities listed is: ' || 
	                     empty_cntry_tab.COUNT);
        DBMS_OUTPUT.PUT_LINE('Second element (Country) in the Index Table is: ' || 
	                     empty_cntry_tab(empty_cntry_tab.NEXT(empty_cntry_tab.FIRST)).country_name);
        DBMS_OUTPUT.PUT_LINE('Before the last element (Country) in the Index Table is: ' || 
	                     empty_cntry_tab(empty_cntry_tab.PRIOR(empty_cntry_tab.LAST)).country_name);
        DBMS_OUTPUT.PUT_LINE('========================================================================');


        FOR cntry IN c_empty_cntry_cursor LOOP
            IF cntry.region_id = &region THEN
                DBMS_OUTPUT.PUT_LINE('In the region : ' || &region || ' there is country ' || cntry.country_name || ' with NO city.');
	        v_counter := v_counter + 1;
            END IF;
        END LOOP;


        DBMS_OUTPUT.PUT_LINE('========================================================================');
        DBMS_OUTPUT.PUT_LINE('Total Number of countries with NO cities listed in the Region ' || &region || ' is: ' || v_counter);
    END IF;
END;
/


SELECT *
FROM   COUNTRIES
WHERE  country_id NOT IN (
    SELECT DISTINCT country_id
    FROM   LOCATIONS
)
AND flag IS NOT NULL
ORDER BY region_id, country_name;


ROLLBACK;







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



Solution





					

						Retrieved from "https://wiki.cdot.senecapolytechnic.ca/w/index.php?title=YSL&oldid=71999"