Sunday, October 16, 2016

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


2 comments: