|
|
| (6 intermediate revisions by 2 users not shown) |
| Line 1: |
Line 1: |
| − | | + | {{db-g7}} |
| − | This page is to efficiently manage our group assignments!
| |
| − | == 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>
| |
| − | 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
| |
| − | </pre>
| |
| − | | |
| − | ''FINAL''
| |
| − | <pre>
| |
| − | 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;
| |
| − | | |
| − | </pre>
| |
| − | | |
| − | =Assignment 2 (worth 10%)=
| |
| − | ==Due Date==
| |
| − | Friday, December 2nd, X:XXpm
| |
| − | | |
| − | ==Description==
| |
| − | [https://cs.senecac.on.ca/~nconkic/as2.doc Assignment2]
| |
| − | | |
| − | ==Solution==
| |
