27 Single-Row Subqueries

•          An inner query that returns only one row to the outer query-single-row subquery         

1.  Write a query to return all those employees who have a salary greater than that of Lorentz and are in the same department as Abel.

SELECT  first_name, last_name

FROM EMPLOYEES

WHERE salary >  (SELECT salary FROM employees WHERE last_name = 'Lorentz') AND department_id = (SELECT department_id FROM employees WHERE last_name = 'Abel');

FIRST_NAME	LAST_NAME
Eleni	Zlotkey
Ellen	Abel
Jonathon	Taylor

2.  Write a query to return all those employees who have the same job id as Rajs and were hired after Davies.

SELECT  first_name, last_name

FROM EMPLOYEES

WHERE  job_id = (SELECT job_id  FROM employees WHERE last_name = 'Rajs') AND hire_date >  (SELECT hire_date FROM employees WHERE last_name = 'Davies') ;

FIRST_NAME	LAST_NAME
Randall	Matos
Peter	Vargas

3.  What DJs on Demand events have the same theme code as event ID = 100?

SELECT id, name

FROM d_events

WHERE theme_code =  (SELECT theme_code FROM d_events WHERE id = 100);

ID	NAME
100	Peters Graduation
105	Vigil wedding

4.  What is the staff type for those Global Fast Foods jobs that have a salary less than those of any Cook staff-type jobs?

SELECT staff_type, MAX(salary)

FROM f_staffs

GROUP BY staff_type

HAVING MAX(salary) < (SELECT MAX(SALARY) FROM f_staffs WHERE staff_type = 'Cook');

STAFF_TYPE	MAX(SALARY)
Order Taker	6.75

5.  Write a query to return a list of department id’s and average salaries where the department’s average salary is greater than Ernst’s salary.

SELECT department_id, TO_CHAR(ROUND(AVG(salary),2),'$999999.99') "Average Salary"

FROM employees

GROUP BY department_id

HAVING AVG(salary) > ( SELECT salary from employees WHERE last_name = 'Ernst');

DEPARTMENT_ID	Average Salary
	$7,000.00
90	$19,333.33
20	$9,500.00
110	$10,150.00
80	$10,033.33
60	$6,400.00

6.  Return the department ID and minimum salary of all employees, grouped by department ID, having a minimum salary greater than the minimum salary of those employees whose department ID is not equal to 50.

SELECT department_id, TO_CHAR(ROUND(MIN(salary),2),'$999999.99') "Minimum Salary"

FROM employees

GROUP BY department_id

HAVING MIN(salary) > ( SELECT MIN(salary) from employees WHERE department_id != 50);

DEPARTMENT_ID	Minimum Salary
	$7,000.00
90	$17,000.00
20	$6,000.00
110	$8,300.00
80	$8,600.00
10	$4,400.00