This page is to efficiently manage our group assignments!
== Member List ==
''Li Shi''
<pre>
select department_idSET 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, department_nameLENGTH(street_address) INTO v_city, substrb FROM locations WHERE country_id = UPPER(nvl'&country') AND state_province IS NULL; IF UPPER(city,v_city) LIKE 'A%' OR UPPER(v_city) LIKE 'B%' OR UPPER(v_city) LIKE 'Not Assigned YetE%'OR UPPER(v_city),LIKE 'F%' THEN WHILE counter <= b LOOP a := a || '*' ; counter := counter + 1,25; END LOOP; ELSIF UPPER(v_city) LIKE 'C%' OR UPPER(v_city) City,LIKE 'D%'count OR UPPER(distinct job_idv_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 || '# of Jobs"' ; counter := counter + 1; END LOOP; END IF; UPDATE locationsfrom employees right outer join departments SET state_province = ausing WHERE city = v_city; DBMS_OUTPUT.PUT_LINE(department_id'City ' || v_city || ' has modified its province to ' || a);left outer join locations EXCEPTION WHEN NO_DATA_FOUND THENusing DBMS_OUTPUT.PUT_LINE(location_id'This country has NO cities listed.');group by department_id, department_name, city WHEN TOO_MANY_ROWS THEN</pre> DBMS_OUTPUT.PUT_LINE('This country has MORE THAN ONE City without province listed.');
''FINAL''END;<pre>/ SELECT department_id, department_name,* FROM locations SUBSTR(NVL(city, WHERE state_province LIKE 'Not Assinged Yet#%'),1, 25) "City", COUNT(DISTINCT job_id) "# of Jobs"FROM employeesRIGHT OUTER JOIN OR state_province departments USING (department_id)LIKE '*%' OR state_province LIKE '&';LEFT OUTER JOIN locations USING (location_id)GROUP BY department_id, department_name, cityROLLBACK;
</pre>