|
|
| (8 intermediate revisions by 3 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>
| |
| − | 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
| |
| − | /
| |
| − | </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==
| |
