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
SELECT department_id, department_name, SUBSTR(NVL(city,'Not Assigned Yet'),1,25) City,
COUNT(DISTINCT job_id) "# of Jobs"
FROM locations l RIGHT OUTER JOIN departments d
USING (location_id)
LEFT OUTER JOIN employees e
USING (department_id)
GROUP BY department_id, department_name, city
/
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;