SQL Exercises: Employees whose job title is MK_MAN, exclude MK_MAN
From the following table, write a SQL query to find those employees whose salary is lower than that of employees whose job title is "MK_MAN". Exclude employees of Job title ‘MK_MAN’. Return employee ID, first name, last name, job ID.
Sample table: employees
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL | PHONE_NUMBER | HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | 100 | Steven | King | SKING | 515.123.4567 | 2003-06-17 | AD_PRES | 24000.00 | 0.00 | 0 | 90 | | 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 2005-09-21 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 2001-01-13 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 2006-01-03 | IT_PROG | 9000.00 | 0.00 | 102 | 60 | | 104 | Bruce | Ernst | BERNST | 590.423.4568 | 2007-05-21 | IT_PROG | 6000.00 | 0.00 | 103 | 60 | | 105 | David | Austin | DAUSTIN | 590.423.4569 | 2005-06-25 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 2006-02-05 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 2007-02-07 | IT_PROG | 4200.00 | 0.00 | 103 | 60 | | 108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | 2002-08-17 | FI_MGR | 12008.00 | 0.00 | 101 | 100 | | 109 | Daniel | Faviet | DFAVIET | 515.124.4169 | 2002-08-16 | FI_ACCOUNT | 9000.00 | 0.00 | 108 | 100 | | 110 | John | Chen | JCHEN | 515.124.4269 | 2005-09-28 | FI_ACCOUNT | 8200.00 | 0.00 | 108 | 100 | .................... +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
Sample Solution:
-- Selecting specific columns (employee_id, first_name, last_name, job_id) from the 'employees' table
SELECT employee_id, first_name, last_name, job_id
-- Filtering rows based on the condition that the 'salary' is less than any salary in the result set of a subquery
FROM employees
-- Subquery to find salaries where the 'job_id' is 'MK_MAN' in the 'employees' table
WHERE salary < ANY
(SELECT salary
FROM employees
-- Subquery to select salaries where the 'job_id' is 'MK_MAN'
WHERE job_id = 'MK_MAN'
)
-- Additional condition to exclude rows where the 'job_id' is 'MK_MAN' from the result set
AND job_id <> 'MK_MAN';
Sample Output:
employee_id first_name last_name job_id 103 Alexander Hunold IT_PROG 104 Bruce Ernst IT_PROG 105 David Austin IT_PROG 106 Valli Pataballa IT_PROG 107 Diana Lorentz IT_PROG 108 Nancy Greenberg FI_MGR 109 Daniel Faviet FI_ACCOUNT 110 John Chen FI_ACCOUNT 111 Ismael Sciarra FI_ACCOUNT 112 Jose Manuel Urman FI_ACCOUNT 113 Luis Popp FI_ACCOUNT 114 Den Raphaely PU_MAN 115 Alexander Khoo PU_CLERK 116 Shelli Baida PU_CLERK 117 Sigal Tobias PU_CLERK 118 Guy Himuro PU_CLERK 119 Karen Colmenares PU_CLERK 120 Matthew Weiss ST_MAN 121 Adam Fripp ST_MAN 122 Payam Kaufling ST_MAN 123 Shanta Vollman ST_MAN 124 Kevin Mourgos ST_MAN 125 Julia Nayer ST_CLERK 126 Irene Mikkilineni ST_CLERK 127 James Landry ST_CLERK 128 Steven Markle ST_CLERK 129 Laura Bissot ST_CLERK 130 Mozhe Atkinson ST_CLERK 131 James Marlow ST_CLERK 132 TJ Olson ST_CLERK 133 Jason Mallin ST_CLERK 134 Michael Rogers ST_CLERK 135 Ki Gee ST_CLERK 136 Hazel Philtanker ST_CLERK 137 Renske Ladwig ST_CLERK 138 Stephen Stiles ST_CLERK 139 John Seo ST_CLERK 140 Joshua Patel ST_CLERK 141 Trenna Rajs ST_CLERK 142 Curtis Davies ST_CLERK 143 Randall Matos ST_CLERK 144 Peter Vargas ST_CLERK 147 Alberto Errazuriz SA_MAN 148 Gerald Cambrault SA_MAN 149 Eleni Zlotkey SA_MAN 150 Peter Tucker SA_REP 151 David Bernstein SA_REP 152 Peter Hall SA_REP 153 Christopher Olsen SA_REP 154 Nanette Cambrault SA_REP 155 Oliver Tuvault SA_REP 156 Janette King SA_REP 157 Patrick Sully SA_REP 158 Allan McEwen SA_REP 159 Lindsey Smith SA_REP 160 Louise Doran SA_REP 161 Sarath Sewall SA_REP 162 Clara Vishney SA_REP 163 Danielle Greene SA_REP 164 Mattea Marvins SA_REP 165 David Lee SA_REP 166 Sundar Ande SA_REP 167 Amit Banda SA_REP 168 Lisa Ozer SA_REP 169 Harrison Bloom SA_REP 170 Tayler Fox SA_REP 171 William Smith SA_REP 172 Elizabeth Bates SA_REP 173 Sundita Kumar SA_REP 174 Ellen Abel SA_REP 175 Alyssa Hutton SA_REP 176 Jonathon Taylor SA_REP 177 Jack Livingston SA_REP 178 Kimberely Grant SA_REP 179 Charles Johnson SA_REP 180 Winston Taylor SH_CLERK 181 Jean Fleaur SH_CLERK 182 Martha Sullivan SH_CLERK 183 Girard Geoni SH_CLERK 184 Nandita Sarchand SH_CLERK 185 Alexis Bull SH_CLERK 186 Julia Dellinger SH_CLERK 187 Anthony Cabrio SH_CLERK 188 Kelly Chung SH_CLERK 189 Jennifer Dilly SH_CLERK 190 Timothy Gates SH_CLERK 191 Randall Perkins SH_CLERK 192 Sarah Bell SH_CLERK 193 Britney Everett SH_CLERK 194 Samuel McCain SH_CLERK 195 Vance Jones SH_CLERK 196 Alana Walsh SH_CLERK 197 Kevin Feeney SH_CLERK 198 Donald OConnell SH_CLERK 199 Douglas Grant SH_CLERK 200 Jennifer Whalen AD_ASST 202 Pat Fay MK_REP 203 Susan Mavris HR_REP 204 Hermann Baer PR_REP 205 Shelley Higgins AC_MGR 206 William Gietz AC_ACCOUNT
Code Explanation:
The said query in SQL that retrieves the employee ID, first name, last name, and job ID of all employees who have a salary lower than any employee with the job ID "MK_MAN", and do not have the job ID "MK_MAN" themselves.
The subquery in the "WHERE" clause retrieves the salary of all employees with the job ID "MK_MAN".
The outer query then compares the salary of each employee with the result of the subquery using the keyword "ANY". The additional condition in the "WHERE" clause, "AND job_id <> 'MK_MAN'", excludes employees with the job ID "MK_MAN" from the result set.
Visual Presentation:
Alternative Statements:
Using JOIN:
SELECT e1.employee_id, e1.first_name, e1.last_name, e1.job_id
FROM employees e1
JOIN employees e2 ON e1.salary < e2.salary
WHERE e2.job_id = 'MK_MAN' AND e1.job_id <> 'MK_MAN';
Using EXISTS:
SELECT employee_id, first_name, last_name, job_id
FROM employees e1
WHERE EXISTS (
SELECT 1
FROM employees e2
WHERE e1.salary < e2.salary
AND e2.job_id = 'MK_MAN'
AND e1.job_id <> 'MK_MAN'
);
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
Contribute your code and comments through Disqus.
Previous SQL Exercise: Employees salary is smaller than job title MK_MAN.
Next SQL Exercise: Employees whose salary is higher than title is PU_MAN.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.