|
|
SQL Developer JOINS / GROUP BY issue.
06-10-2011
CREATE OR REPLACE VIEW TOP_EARNER_PER_LOCATION
AS
SELECT E.FIRST_NAME || ' ' || E.LAST_NAME AS EMPLOYEE_NAME,
L.REGIONAL_GROUP AS REGIONAL_GROUP,
E.SALARY AS SALARY,
J.JOB_ID
FROM EMPLOYEE E
LEFT OUTER JOIN DEPARTMENT D
ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
LEFT OUTER JOIN LOCATION L
ON L.LOCATION_ID = D.LOCATION_ID
LEFT OUTER JOIN JOB J
ON J.JOB_ID = E.JOB_ID
GROUP BY E.FIRST_NAME || ' ' || E.LAST_NAME
HAVING J.JOB_ID NOT IN (670,671,672)
;
REM #...SELECT STATEMENT TO RUN QUERY GOES HEREEMPLOYEE_NAME REGIONAL_GROUP SALARY ------------------------------- -------------------- ---------------------- FRED JAMES CHICAGO 950 DENISE SOMMERS CHICAGO 1850 JAMES MURRAY DALLAS 750 JOHN SMITH DALLAS 800 DIANE ADAMS DALLAS 1100 RICHARD LEWIS DALLAS 1800 DONALD SCOTT DALLAS 3000 JENNIFER FORD DALLAS 3000 ALICE JENSEN NEW YORK 750 MICHAEL DOUGLAS NEW YORK 800 BARBARA MILLER NEW YORK 1300 GRACE ROBERTS NEW YORK 2875 MATTHEW FISHER NEW YORK 3000
|
|