YSL
This page is to efficiently manage our group assignments!
Contents
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 = ®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;
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
