I decided to download SQL Developer and start working on getting my head around subqueries. I've stuck 'em in the SELECT, FROM and WHERE clauses to see how it works. It was fun.

Tim learns about subqueries

SELECT
	E.FIRST_NAME || ' ' || E.LAST_NAME AS "Employee",
	EJ.JOB_TITLE AS "Employee Job Title",
	ED.DEPARTMENT_NAME AS "Employee Department",
	EDLC.COUNTRY_NAME AS "Employee Country",
	E.SALARY AS "Employee Salary",
	M.FIRST_NAME || ' ' || M.last_name AS "Manager",
	-- MJ.JOB_TITLE as "Manager Job Title",
	(SELECT count(X.EMPLOYEE_ID)
		FROM EMPLOYEES X
		WHERE X.MANAGER_ID = E.EMPLOYEE_ID
		HAVING count(X.EMPLOYEE_ID) > 0) AS "Direct Reports",
	(SELECT SUM(X.SALARY)
		FROM EMPLOYEES X
		WHERE X.MANAGER_ID = E.EMPLOYEE_ID) AS "Salary Budget",
	"Salary Rank"
	
FROM EMPLOYEES E
LEFT JOIN EMPLOYEES M ON (E.MANAGER_ID = M.EMPLOYEE_ID)
LEFT JOIN JOBS EJ ON (E.JOB_ID = EJ.JOB_ID)
LEFT JOIN JOBS MJ ON (M.JOB_ID = MJ.JOB_ID)
LEFT JOIN DEPARTMENTS ED ON (E.DEPARTMENT_ID = ED.DEPARTMENT_ID)
LEFT JOIN LOCATIONS EDL ON (ED.LOCATION_ID = EDL.LOCATION_ID)
LEFT JOIN COUNTRIES EDLC ON (EDL.COUNTRY_ID = EDLC.COUNTRY_ID)
LEFT JOIN REGIONS EDLCR ON (EDLC.REGION_ID = EDLCR.REGION_ID)
/*
LEFT JOIN DEPARTMENTS MD ON (M.DEPARTMENT_ID = MD.DEPARTMENT_ID)
LEFT JOIN LOCATIONS MDL ON (MD.LOCATION_ID = MDL.LOCATION_ID)
LEFT JOIN COUNTRIES MDLC ON (MDL.COUNTRY_ID = MDLC.COUNTRY_ID)
LEFT JOIN REGIONS MDLCR ON (MDLC.REGION_ID = MDLCR.REGION_ID)
*/

LEFT JOIN (
	SELECT X.EMPLOYEE_ID,
		rank() OVER (ORDER BY X.SALARY DESC) AS "Salary Rank"
	FROM EMPLOYEES X) XE ON (XE.EMPLOYEE_ID = E.EMPLOYEE_ID)
	
WHERE E.SALARY >= ALL
  (SELECT X.SALARY
		FROM EMPLOYEES X
		LEFT JOIN DEPARTMENTS XD ON (X.DEPARTMENT_ID = XD.DEPARTMENT_ID)
		LEFT JOIN LOCATIONS XDL ON (XD.LOCATION_ID = XDL.LOCATION_ID)
		WHERE XDL.COUNTRY_ID = EDL.COUNTRY_ID)
		
ORDER BY "Employee" ASC;