Difference between revisions of "J.Y.S"
Yujin.jeong (talk | contribs) (→DDL Statements (CREATE TABLE)) |
Yujin.jeong (talk | contribs) (→DDL Statements (CREATE TABLE)) |
||
Line 50: | Line 50: | ||
CREATE TABLE PROPERTY | CREATE TABLE PROPERTY | ||
( | ( | ||
− | PropertyID NUMBER(6) | + | PropertyID NUMBER(6), |
Street VARCHAR2(40) NOT NULL, | Street VARCHAR2(40) NOT NULL, | ||
City VARCHAR2(30) NOT NULL, | City VARCHAR2(30) NOT NULL, | ||
Province CHAR(2) NOT NULL, | Province CHAR(2) NOT NULL, | ||
PostalCode CHAR(6) NOT NULL, | PostalCode CHAR(6) NOT NULL, | ||
− | Bedrooms NUMBER(1) NOT NULL | + | Bedrooms NUMBER(1) NOT NULL, |
− | + | Bathrooms NUMBER(1) NOT NULL, | |
− | + | ListingPrice NUMBER(6) NOT NULL, | |
− | + | AreaID NUMBER(4) NOT NULL, | |
− | + | CONSTRAINT property_propertyid_pk PRIMARY KEY(PropertyID), | |
− | + | CONSTRAINT property_bedrooms_ck CHECK(Bedrooms BETWEEN 1 AND 9), | |
− | + | CONSTRAINT property_bathrooms_ck CHECK(Bathrooms BETWEEN 1 AND 9), | |
− | + | CONSTRAINT property_listingprice_ck CHECK(ListingPrice BETWEEN 1 AND 4999999), | |
+ | CONSTRAINT property_areaid_fk FOREIGN KEY(AreaID) REFERENCES AREA(AreaID) | ||
); | ); | ||
</pre> | </pre> |
Revision as of 15:09, 27 November 2010
This page is to efficiently manage our group assignments!
Contents
Member List
First Name | Last Name | wiki id | Learn e-mail | Phone |
---|---|---|---|---|
YuJin | Jeong | yujin.jeong | yjeong | 647 - 832 - 6771 |
Saad | ||||
James |
Assignment 3 (worth 9%)
Due Date
Thursday, December 2nd, 3:00pm Printout of all TIC charts and DDL / DML statements
Solution
DDL Statements (CREATE TABLE)
1. AREA
CREATE TABLE AREA ( AreaID NUMBER(4) CONSTRAINT area_areaid_pk PRIMARY KEY, AreaName VARCHAR2(30) NOT NULL CONSTRAINT area_areaname_uk UNIQUE, ElmSchool VARCHAR2(40), MidSchool VARCHAR2(40), HighSchool VARCHAR2(40), Comments VARCHAR2(100) );
2. AGENCY
CREATE TABLE AGENCY ( AgencyID NUMBER(4) CONSTRAINT agency_agencyid_pk PRIMARY KEY, AgencyName VARCHAR2(40) NOT NULL CONSTRAINT agency_agencyname_uk UNIQUE, AgencyPhone VARCHAR2(15) NOT NULL CONSTRAINT agency_agencyphone_uk UNIQUE );
3. PROPERTY
CREATE TABLE PROPERTY ( PropertyID NUMBER(6), Street VARCHAR2(40) NOT NULL, City VARCHAR2(30) NOT NULL, Province CHAR(2) NOT NULL, PostalCode CHAR(6) NOT NULL, Bedrooms NUMBER(1) NOT NULL, Bathrooms NUMBER(1) NOT NULL, ListingPrice NUMBER(6) NOT NULL, AreaID NUMBER(4) NOT NULL, CONSTRAINT property_propertyid_pk PRIMARY KEY(PropertyID), CONSTRAINT property_bedrooms_ck CHECK(Bedrooms BETWEEN 1 AND 9), CONSTRAINT property_bathrooms_ck CHECK(Bathrooms BETWEEN 1 AND 9), CONSTRAINT property_listingprice_ck CHECK(ListingPrice BETWEEN 1 AND 4999999), CONSTRAINT property_areaid_fk FOREIGN KEY(AreaID) REFERENCES AREA(AreaID) );
4. SINGLE
CREATE TABLE SINGLE ( PropertyID NUMBER(6), LotSize NUMBER(8,2) NOT NULL, CONSTRAINT single_propertyID_pk PRIMARY KEY(PropertyID), CONSTRAINT single_propertyID_fk FOREIGN KEY(PropertyID) REFERENCES PROPERTY(PropertyID), CONSTRAINT single_lotsize_ck CHECK(LotSize > 0) );
5. CONDO
CREATE TABLE CONDO ( PropertyID NUMBER(6), Fee NUMBER(8,2) NOT NULL, CONSTRAINT condo_propertyID_pk PRIMARY KEY(PropertyID), CONSTRAINT condo_propertyID_fk FOREIGN KEY(PropertyID) REFERENCES PROPERTY(PropertyID), CONSTRAINT condo_fee_ck CHECK(Fee > 0) );
6. OUTLET
CREATE TABLE OUTLET ( OutletID NUMBER(4), OutletName VARCHAR2(40) NOT NULL, OutletPhone VARCHAR2(15) NOT NULL, OutletType CHAR(1) NOT NULL, CONSTRAINT outlet_outletid_pk PRIMARY KEY(OutletID), CONSTRAINT outlet_outlettype_ck CHECK(OutletType IN('N','M','W','F')), CONSTRAINT outlet_outletname_type_uk UNIQUE(OutletName, OutletType) );
7. ADVERTISEMENT
CREATE TABLE OUTLET ( OutletID NUMBER(4), OutletName VARCHAR2(40) NOT NULL, OutletPhone VARCHAR2(15) NOT NULL, OutletType CHAR(1) NOT NULL, CONSTRAINT outlet_outletid_pk PRIMARY KEY(OutletID), CONSTRAINT outlet_outlettype_ck CHECK(OutletType IN('N','M','W','F')), CONSTRAINT outlet_outletname_type_uk UNIQUE(OutletName, OutletType) );
8. CLIENT
CREATE TABLE CLIENT ( ClientID NUMBER(6), Fname VARCHAR2(30) NOT NULL, Lname VARCHAR2(30) NOT NULL, Phone VARCHAR2(15) NOT NULL, Email VARCHAR2(40), RefID NUMBER(6), CONSTRAINT client_clientid_pk PRIMARY KEY(ClientID), CONSTRAINT client_phone_uk UNIQUE(Phone), CONSTRAINT client_email_uk UNIQUE(Email), CONSTRAINT client_refid_fk FOREIGN KEY(RefID) REFERENCES CLEINT(ClientID) );
9. DEED
CREATE TABLE DEED ( PropertyID NUMBER(6), SellerID NUMBER(6), OwnPct NUMBER(5,2) NOT NULL, BuyerID NUMBER(6), BuyPrice NUMBER(7), CONSTRAINT deed_deedid_pk PRIMARY KEY(PropertyID, SellerID), CONSTRAINT deed_propertyid_fk FOREIGN KEY(PropertyID) REFERENCES PROPERTY(PropertyID), CONSTRAINT deed_sellerid_fk FOREIGN KEY(SellerID) REFERENCES CLIENT(ClientID), CONSTRAINT deed_buyerid_fk FOREIGN KEY(BuyerID) REFERENCES CLIENT(ClientID), CONSTRAINT deed_ownpct_ck CHECK(OwnPct > 0 AND OwnPct <= 100), CONSTRAINT deed_buyprice_ck CHECK(BuyPrice BETWEEN 1 AND 4999999) );
Assignment 1
Due Date
Thursday, October 21st, 4:00pm Printout submission only( SELECT Statements + ALL outputs)
Description
Solution
SQL> SELECT employee_id, RPAD(CONCAT(CONCAT(last_name, ', '),first_name), 25) "Full Name", 2 job_id, 3 TO_CHAR(TRUNC(hire_date, 'MONTH'), 'fmMonth Ddspth "in the year" YYYY') 4 "Start Date" 5 FROM employees 6 WHERE TO_CHAR(hire_date, 'mm') IN (05, 11) 7 ORDER BY 4 DESC; EMPLOYEE_ID Full Name JOB_ID Start Date ----------- ------------------------- ---------- ----------------------------------------- 124 Mourgos, Kevin ST_MAN November First in the year 1999 178 Grant, Kimberely SA_REP May First in the year 1999 174 Abel, Ellen SA_REP May First in the year 1996 104 Ernst, Bruce IT_PROG May First in the year 1991 SQL> SELECT 'Employee named ' || first_name || ' ' || last_name || ' who is ' 2 || job_id || ' will have a new salary of $' || salary * 1.15 "Happy Employees" 3 FROM employees 4 WHERE salary BETWEEN 5000 AND 12000 5 AND job_id IN ('IT_PROG', 'ST_CLERK') 6 ORDER BY employee_id; Happy Employees ---------------------------------------------------------------------------------------------------- Employee named Alexander Hunold who is IT_PROG will have a new salary of $10350 Employee named Bruce Ernst who is IT_PROG will have a new salary of $6900 SQL> SELECT last_name, salary, job_id, 2 RPAD(NVL(TO_CHAR(manager_id), 'No Manager'), 10) "Manager #", 3 LPAD(TO_CHAR(salary * 12, '$999,999'), 12) "Total Income" 4 FROM employees 5 WHERE (job_id LIKE 'MK%' OR manager_id IS NULL) 6 AND salary * (1 + NVL(commission_pct, 0)) + 1000 > 10000 7 ORDER BY 2 DESC; LAST_NAME SALARY JOB_ID Manager # Total Income ------------------------- ---------- ---------- ---------- ------------ King 24000 AD_PRES No Manager $288,000 Hartstein 13000 MK_MAN 100 $156,000 SQL> SELECT RPAD(CONCAT(CONCAT(first_name,' '),last_name), 25) "Employee", job_id, 2 LPAD(TRIM(TO_CHAR(salary, '$999,999')), 12, '=') "Salary", department_name 3 FROM employees e JOIN departments d 4 ON (e.department_id = d.department_id) 5 WHERE UPPER(department_name) IN ('MARKETING', 'SALES') 6 AND salary < 7 (SELECT MAX(salary) 8 FROM employees 9 WHERE SUBSTR(job_id, 4) NOT IN ('PRES','VP','MAN','MGR')) 10 ORDER BY 1; Employee JOB_ID Salary DEPARTMENT_NAME ------------------------- ---------- ------------ ------------------------------ Eleni Zlotkey SA_MAN =====$10,500 Sales Jonathon Taylor SA_REP ======$8,600 Sales Pat Fay MK_REP ======$6,000 Marketing SQL> SQL> SELECT last_name, salary, job_id 2 FROM employees 3 WHERE UPPER(job_id) NOT LIKE '%PRES' 4 AND salary > 5 (SELECT MAX(salary) 6 FROM employees 7 JOIN departments USING (department_id) 8 JOIN locations USING (location_id) 9 WHERE UPPER(city) IN ('TORONTO','OXFORD')); LAST_NAME SALARY JOB_ID ------------------------- ---------- ---------- Kochhar 17000 AD_VP De Haan 17000 AD_VP SQL> SELECT last_name, first_name, job_id, hire_date 2 FROM employees 3 WHERE hire_date > 4 (SELECT MAX(hire_date) 5 FROM employees 6 WHERE department_id = 7 (SELECT department_id 8 FROM departments 9 WHERE UPPER(department_name) = 'IT')) 10 AND (department_id != 11 (SELECT department_id 12 FROM departments 13 WHERE UPPER(department_name) = 'EXECUTIVE') 14 OR department_id IS NULL) 15 ORDER BY 4 DESC; LAST_NAME FIRST_NAME JOB_ID HIRE_DATE ------------------------- -------------------- ---------- --------- Zlotkey Eleni SA_MAN 29-JAN-00 Mourgos Kevin ST_MAN 16-NOV-99 Grant Kimberely SA_REP 24-MAY-99 SQL> SELECT department_id, job_id, AVG(salary) "Average Dept/Job Pay", 2 MIN(salary) "Lowest Dept/Job Pay" 3 FROM employees 4 WHERE UPPER(job_id) NOT LIKE '%VP' 5 AND department_id NOT IN 6 (SELECT department_id 7 FROM departments 8 WHERE UPPER(department_name) IN ('IT','SALES')) 9 GROUP BY department_id, job_id 10 HAVING MIN(salary) BETWEEN 5000 AND 15000 11 ORDER BY 1, 2; DEPARTMENT_ID JOB_ID Average Dept/Job Pay Lowest Dept/Job Pay ------------- ---------- -------------------- ------------------- 20 MK_MAN 13000 13000 20 MK_REP 6000 6000 50 ST_MAN 5800 5800 110 AC_ACCOUNT 8300 8300 110 AC_MGR 12000 12000 SQL> SELECT department_id, department_name, 2 RPAD(TRIM(NVL(city, 'Not Assinged Yet')), 25, ' ') "City", 3 COUNT(DISTINCT job_id) "# of Jobs" 4 FROM employees 5 RIGHT OUTER JOIN departments USING (department_id) 6 LEFT OUTER JOIN locations USING (location_id) 7 GROUP BY department_id, department_name, city; DEPARTMENT_ID DEPARTMENT_NAME City # of Jobs ------------- ------------------------------ ------------------------- ---------- 110 Accounting Seattle 2 50 Shipping South San Francisco 2 80 Sales Oxford 2 10 Administration Seattle 1 190 Contracting Seattle 0 90 Executive Seattle 2 20 Marketing Toronto 2 60 IT Southlake 1 8 rows selected. SQL> spool off
SQL Statements
1.
YuJin
SELECT employee_id, RPAD(CONCAT(CONCAT(last_name, ', '),first_name), 25) "Full Name", job_id, TO_CHAR(TRUNC(hire_date, 'MONTH'), 'fmMonth Ddspth "in the year" YYYY') "Start Date" FROM employees WHERE TO_CHAR(hire_date, 'mm') IN (05, 11) ORDER BY 4 DESC;
Saad
SELECT employee_id, SUBSTR(last_name ||' '|| first_name, 1, 25) "Full Name", job_id, TO_CHAR(hire_date,'fmMonth "First in the year" YYYY') "Start Date" FROM employees WHERE TO_CHAR(hire_date,'fmMON') IN ('MAY','NOV') ORDER BY hire_date DESC /
James
select employee_id "Emp#", (last_name ||', '|| first_name) "Full Name", job_id "Job", to_char(trunc(hire_date, 'MONTH'), 'FMMonth Ddspth "in the year" YYYY') "Start Date" from employees where to_char(hire_date, 'fmMonth') in ('May', 'November') order by hire_date desc;
FINAL
SELECT employee_id, SUBSTR(last_name ||' '|| first_name, 1, 25) "Full Name", job_id, TO_CHAR(TRUNC(hire_date, 'MONTH'), 'fmMonth Ddspth "in the year" YYYY') "Start Date" FROM employees WHERE TO_CHAR(hire_date, 'mm') IN (05, 11) ORDER BY hire_date DESC;
2.
YuJin
SELECT 'Employee named ' || first_name || ' ' || last_name || ' who is ' || job_id || ' will have a new salary of $' || salary * 1.15 "Happy Employees" FROM employees WHERE salary BETWEEN 5000 AND 12000 AND job_id IN ('IT_PROG', 'ST_CLERK') ORDER BY employee_id;
Saad
SELECT 'Employee named '||first_name||' '||last_name||' who is '||job_id||' will have a new salary of $'|| salary *1.15 "Happy Employees" FROM employees WHERE job_id IN ('IT_PROG','ST CLERK') AND (salary BETWEEN 5000 AND 12000) /
James
Select ('Employee named ' || first_name ||' '|| last_name || ' who is ' || job_id || ' will have a new salary of ' || (salary*1.15)) "Happy Employees" From employees Where salary between 5000 and 12000 And upper(job_id) in ('IT_PROG', 'ST_CLERK') order by employee_id;
FINAL
SELECT 'Employee named ' || first_name || ' ' || last_name || ' who is ' || job_id || ' will have a new salary of ' || TO_CHAR(salary * 1.15, 'fm$999999') "Happy Employees" FROM employees WHERE salary BETWEEN 5000 AND 12000 AND UPPER(job_id) IN ('IT_PROG', 'ST_CLERK') ORDER BY employee_id;
3.
YuJin
SELECT last_name, salary, job_id, RPAD(NVL(TO_CHAR(manager_id), 'No Manager'), 10) "Manager #", LPAD(TO_CHAR(salary * 12, '$999,999'), 12) "Total Income" FROM employees WHERE (job_id LIKE 'MK%' OR manager_id IS NULL) AND salary * (1 + NVL(commission_pct, 0)) + 1000 > 10000 ORDER BY 2 DESC;
Saad
SELECT last_name, salary, job_id, NVL(TO_CHAR(manager_id),'No Manager') Manager#, TO_CHAR(salary*12,'$999,999') "Total Income" FROM employees WHERE (manager_id IS NULL OR job_id LIKE 'MK%') AND ( salary* (NVL(commission_pct,0) + 1) + 1000 ) > 10000 ORDER BY salary DESC /
James
select last_name, salary, job_id, nvl(to_char(manager_id),'No Manager') Manager#, to_char(salary*12,'$999,999') "Total Income" from employees where (upper(job_id) like 'MK%' or manager_id IS NULL) and salary * (1 + nvl(commission_pct, 0)) + 1000 > 10000 order by 2 desc;
FINAL
SELECT last_name, salary, job_id, SUBSTR(NVL(TO_CHAR(manager_id), 'No Manager'), 1, 10) "Manager #", LPAD(TO_CHAR(salary * 12, '$999,999'), 12) "Total Income" FROM employees WHERE (UPPER(job_id) LIKE 'MK%' OR manager_id IS NULL) AND salary * (1 + NVL(commission_pct, 0)) + 1000 > 10000 ORDER BY salary DESC;
4.
YuJin
SELECT RPAD(CONCAT(CONCAT(first_name,' '),last_name), 25) "Employee", job_id, LPAD(TRIM(TO_CHAR(salary, '$999,999')), 12, '=') "Salary", department_name FROM employees e JOIN departments d ON (e.department_id = d.department_id) WHERE UPPER(department_name) IN ('MARKETING', 'SALES') AND salary < (SELECT MAX(salary) FROM employees WHERE SUBSTR(job_id, 4) NOT IN ('PRES','VP','MAN','MGR')) ORDER BY 1;
Saad
SELECT SUBSTR(first_name||' '||last_name, 25) Employee, job_id, LPAD(TO_CHAR(salary,'$99,999'), 12, '=') Salary, department_name FROM employees e JOIN departments d ON (d.department_id=e.department_id) WHERE salary < (SELECT MAX(salary) FROM employees WHERE UPPER(SUBSTR(job_id,4)) NOT IN ('VP', 'PRES', 'MAN', 'MGR') ) AND UPPER(department_name) IN ('MARKETING','SALES') ORDER BY first_name, last_name /
James
select substr(first_name ||' '|| last_name) "Employee", job_id, to_char(salary, '$999,999', 12, '=') Salary, department_name from employees e join departments d on (e.department_id = d.department_id) where upper(department_name) in ('MARKETING', 'SALES') and salary < (select max(salary) from employees where substr(job_id,4) not in ('PRES','VP','MAN','MGR')) order by "Employee";
FINAL
SELECT SUBSTR(first_name ||' '|| last_name, 1, 25) "Employee", job_id, LPAD(TO_CHAR(salary, 'fm$999,999'), 12, '=') "Salary", department_name FROM employees e JOIN departments d ON (e.department_id = d.department_id) WHERE UPPER(department_name) IN ('MARKETING', 'SALES') AND salary < (SELECT MAX(salary) FROM employees WHERE SUBSTR(UPPER(job_id), 4) NOT IN ('PRES','VP','MAN','MGR')) ORDER BY 1;
5.
YuJin
SELECT last_name, salary, job_id FROM employees WHERE UPPER(job_id) NOT LIKE '%PRES' AND salary > (SELECT MAX(salary) FROM employees JOIN departments USING (department_id) JOIN locations USING (location_id) WHERE UPPER(city) IN ('TORONTO','OXFORD'));
Saad
SELECT last_name, salary, job_id FROM employees WHERE salary > (SELECT MAX(salary) FROM locations JOIN departments USING (location_id) JOIN employees USING (department_id) WHERE UPPER(city) IN ('TORONTO','OXFORD') ) AND UPPER(job_id) NOT LIKE '%PRES' /
James
select last_name, salary, job_id from employees where salary > (select max(salary) from employees join departments using (department_id) join locations using (location_id) where upper(city) in ('TORONTO','OXFORD')) and upper(job_id) not like '%PRES'
FINAL
SELECT last_name, salary, job_id FROM employees WHERE UPPER(job_id) NOT LIKE '%PRES' AND salary > (SELECT MAX(salary) FROM employees JOIN departments USING (department_id) JOIN locations USING (location_id) WHERE UPPER(city) IN ('TORONTO','OXFORD'));
6.
YuJin
SELECT last_name, first_name, job_id, hire_date FROM employees WHERE hire_date > (SELECT MAX(hire_date) FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE UPPER(department_name) = 'IT')) AND (department_id != (SELECT department_id FROM departments WHERE UPPER(department_name) = 'EXECUTIVE') OR department_id IS NULL) ORDER BY 4 DESC;
Saad
SELECT last_name, first_name, job_id, hire_date FROM employees e WHERE hire_date > (SELECT MAX(hire_date) FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE UPPER(department_name) = 'IT' ) ) AND (department_id <> (SELECT department_id FROM departments WHERE UPPER(department_name) ='EXECUTIVE') OR department_id IS NULL) ORDER BY hire_date DESC /
James
select last_name, first_name, job_id, hire_date from employees where hire_date > (select max(hire_date) from employees where department_id = (select department_id from departments where upper(department_name) = 'IT')) and (department_id != (select department_id from departments where upper(department_name) = 'EXECUTIVE') or department_id IS NULL) order by hire_date desc;
FINAL
SELECT last_name, first_name, job_id, hire_date FROM employees WHERE hire_date > (SELECT MAX(hire_date) FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE UPPER(department_name) = 'IT')) AND (department_id != (SELECT department_id FROM departments WHERE UPPER(department_name) = 'EXECUTIVE') OR department_id IS NULL) ORDER BY hire_date DESC;
7.
YuJin
SELECT department_id, job_id, AVG(salary) "Average Dept/Job Pay", MIN(salary) "Lowest Dept/Job Pay" FROM employees WHERE UPPER(job_id) NOT LIKE '%VP' AND department_id NOT IN (SELECT department_id FROM departments WHERE UPPER(department_name) IN ('IT','SALES')) GROUP BY department_id, job_id HAVING MIN(salary) BETWEEN 5000 AND 15000 ORDER BY 1, 2;
Saad
SELECT department_id, job_id, AVG(salary) "Average Dept/Job Pay", MIN(salary) "Lowest Dept/Job Pay" FROM employees WHERE department_id <> ALL (SELECT department_id FROM departments WHERE UPPER(department_name) IN ('IT','SALES') ) AND job_id NOT LIKE '%VP' GROUP BY department_id, job_id HAVING MIN(salary) BETWEEN 5000 AND 15000 ORDER BY department_id, job_id /
James
select department_id, job_id, avg(salary) "Average Dept/Job Pay", min(salary) "Lowest Dept/Job Pay" from employees where upper(job_id) not like '%VP' and department_id != all(select department_id from departments where upper(department_name) in ('IT','SALES')) group by department_id, job_id having min(salary) between 5000 and 15000 order by department_id, job_id
FINAL
SELECT department_id, job_id, AVG(salary) "Average Dept/Job Pay", MIN(salary) "Lowest Dept/Job Pay" FROM employees WHERE UPPER(job_id) NOT LIKE '%VP' AND department_id != ALL (SELECT department_id FROM departments WHERE UPPER(department_name) IN ('IT','SALES')) GROUP BY department_id, job_id HAVING MIN(salary) BETWEEN 5000 AND 15000 ORDER BY department_id, job_id;
8.
YuJin
SELECT department_id, department_name, RPAD(TRIM(NVL(city, 'Not Assinged Yet')), 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;
Saad
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 /
James
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;