|
|
(3 intermediate revisions by the same user not shown) |
Line 1: |
Line 1: |
− | This page is to efficiently manage our group assignments!
| + | {{db-g7}} |
− | == Member List ==
| |
− | *[mailto:yjeong@learn.senecac.on.ca,?subject=dbs501,symoon@learn.senecac.on.ca,?subject=dbs501,lzhou@learn.senecac.on.ca,?subject=dbs501 Email All]
| |
− | | |
− | {| class="wikitable sortable" border="1" cellpadding="5" | |
− | ! First Name !! Last Name !! Student ID !! Learn e-mail !! Phone
| |
− | |-
| |
− | |YuJin ||Jeong|| 048 - 056 - 097 ||[mailto:yjeong@learn.senecac.on.ca?sujbect=dbs501 yjeong]|| (647) 863 - 5555
| |
− | |-
| |
− | | |
− | |-
| |
− | |Seung Yeon||Moon || - ||[mailto:symoon@learn.senecac.on.ca?subject=dbs501 symoon] ||(647) 678 - 6511
| |
− | |-
| |
− | | |
− | |-
| |
− | |Li Shi|| Zhou || 057 - 268 - 070 || [mailto:lzhou@learn.senecac.on.ca?subject=dbs501 lzhou] || (416) 887 - 6758
| |
− | |-
| |
− | |}
| |
− | | |
− | =Assignment 1 (worth 10%)=
| |
− | | |
− | | |
− | ==Due Date==
| |
− | Friday, October 24st, 4:00pm
| |
− | | |
− | ==Description==
| |
− | [https://cs.senecac.on.ca/~nconkic/as1.doc Assignment1]
| |
− | Submit PRINTOUT and EMAIL with ATTACHMENT
| |
− | | |
− | ==Solution==
| |
− | | |
− | | |
− | ===1.===
| |
− | ''YuJin''
| |
− | <pre>
| |
− | 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;
| |
− | </pre>
| |
− | ''Seung Yeon''
| |
− | <pre>
| |
− | 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;
| |
− | </pre>
| |
− | ''Li Shi''
| |
− | <pre>
| |
− | 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;
| |
− | | |
− | </pre>
| |
− | | |
− | | |
− | ===2.===
| |
− | | |
− | | |
− | ''YuJin''
| |
− | | |
− | <pre>
| |
− | | |
− | 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 = ®ion;
| |
− | | |
− | | |
− | BEGIN <<inner>>
| |
− | SELECT country_name INTO country
| |
− | FROM COUNTRIES
| |
− | WHERE region_id = ®ion
| |
− | AND country_id NOT IN (
| |
− | SELECT DISTINCT country_id
| |
− | FROM LOCATIONS
| |
− | );
| |
− | | |
− | | |
− | IF SQL%ROWCOUNT = 1 THEN
| |
− | DBMS_OUTPUT.PUT_LINE('In the region ' || ®ion || ' 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: ' || ®ion);
| |
− | END inner;
| |
− | | |
− | | |
− | EXCEPTION
| |
− | WHEN NO_DATA_FOUND THEN
| |
− | DBMS_OUTPUT.PUT_LINE('This region ID does NOT exist: ' || ®ion);
| |
− | 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;
| |
− | </pre>
| |
− | | |
− | | |
− | ''Seung Yeon''
| |
− | <pre>
| |
− | | |
− | </pre>
| |
− | | |
− | ''Li Shi''
| |
− | | |
− | <pre>
| |
− | </pre>
| |
− | | |
− | ===3.===
| |
− | | |
− | | |
− | | |
− | | |
− | ''YuJin''
| |
− | | |
− | <pre>
| |
− | </pre>
| |
− | | |
− | | |
− | ''Seung Yeon''
| |
− | <pre>
| |
− | | |
− | </pre>
| |
− | | |
− | ''Li Shi''
| |
− | | |
− | <pre>
| |
− | </pre>
| |
− | | |
− | | |
− | | |
− | | |
− | ===4.===
| |
− | | |
− | | |
− | ''YuJin''
| |
− | | |
− | <pre>
| |
− | </pre>
| |
− | | |
− | | |
− | ''Seung Yeon''
| |
− | <pre>
| |
− | | |
− | </pre>
| |
− | | |
− | ''Li Shi''
| |
− | | |
− | <pre>
| |
− | </pre>
| |
− | | |
− | | |
− | | |
− | | |
− | | |
− | ===5.===
| |
− | | |
− | | |
− | | |
− | ''YuJin''
| |
− | | |
− | <pre>
| |
− | </pre>
| |
− | | |
− | | |
− | ''Seung Yeon''
| |
− | <pre>
| |
− | | |
− | </pre>
| |
− | | |
− | ''Li Shi''
| |
− | | |
− | <pre>
| |
− | </pre>
| |
− | | |
− | =Assignment 2 (worth 10%)=
| |
− | ==Due Date==
| |
− | Friday, December 2nd, X:XXpm
| |
− | | |
− | ==Description==
| |
− | [https://cs.senecac.on.ca/~nconkic/as2.doc Assignment2]
| |
− | | |
− | ==Solution==
| |