SQL Subquery Exercises: Display the employee number, name, and salary for all employees who earn more than the average salary and who work in a department with any employee with a 'J' in their name
SQL SUBQUERY: Exercise-15 with Solution
From the following tables, write a SQL query to find those employees who earn more than the average salary and work in the same department as an employee whose first name contains the letter 'J'. Return employee ID, first name and salary.
Sample table: employees
SELECT employee_id, first_name , salary FROM employees WHERE salary > (SELECT AVG (salary) FROM employees ) AND department_id IN ( SELECT department_id FROM employees WHERE first_name LIKE '%J%');
employee_id first_name salary 108 Nancy 12000.00 109 Daniel 9000.00 110 John 8200.00 111 Ismael 7700.00 112 Jose Manuel 7800.00 113 Luis 6900.00 120 Matthew 8000.00 121 Adam 8200.00 122 Payam 7900.00 123 Shanta 6500.00 145 John 14000.00 146 Karen 13500.00 147 Alberto 12000.00 148 Gerald 11000.00 149 Eleni 10500.00 150 Peter 10000.00 151 David 9500.00 152 Peter 9000.00 153 Christopher 8000.00 154 Nanette 7500.00 155 Oliver 7000.00 156 Janette 10000.00 157 Patrick 9500.00 158 Allan 9000.00 159 Lindsey 8000.00 160 Louise 7500.00 161 Sarath 7000.00 162 Clara 10500.00 163 Danielle 9500.00 164 Mattea 7200.00 165 David 6800.00 168 Lisa 11500.00 169 Harrison 10000.00 170 Tayler 9600.00 171 William 7400.00 172 Elizabeth 7300.00 174 Ellen 11000.00 175 Alyssa 8800.00 176 Jonathon 8600.00 177 Jack 8400.00
Contribute your code and comments through Disqus.
Previous: From the following tables, write a SQL query to find those employees who work in a department where the employee’s first name contains a letter 'T'. Return employee ID, first name and last name.
Next: From the following table, write a SQL query to find those employees whose department located at ‘Toronto’. Return first name, last name, employee ID, job ID.
Test your Programming skills with w3resource's quiz.
What is the difficulty level of this exercise?
SQL: Tips of the Day
"where 1=1" statement?
It's usually when folks build up SQL statements.
When you add and value = "Toyota" you don't have to worry about whether there is a condition before or just WHERE.
- Exercises: Weekly Top 12 Most Popular Topics
- Pandas DataFrame: Exercises, Practice, Solution
- Conversion Tools
- SQL Exercises, Practice, Solution - SUBQUERIES
- C Programming Exercises, Practice, Solution : For Loop
- Python Exercises, Practice, Solution
- Python Data Type: List - Exercises, Practice, Solution
- C++ Basic: Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - exercises on Employee Database
- SQL Exercises, Practice, Solution - exercises on Movie Database
- SQL Exercises, Practice, Solution - exercises on Soccer Database
- C Programming Exercises, Practice, Solution : Recursion