Sub Q

mail@pastecode.io avatar
unknown
mysql
a year ago
1.2 kB
0
Indexable
Never
Sub-Query: Kind of nested query
==========

Single Query
------------
SELECT first_name, salary
FROM `employees`
HAVING salary> AVG(salary)

Sub-Query
---------
##Employee getting higher salary than the average salary
SELECT first_name
FROM `employees`
WHERE salary > (SELECT AVG(salary)
                FROM `employees`
               );

#Employee getting the maximum salary
SELECT first_name
FROM `employees`
WHERE salary =  (SELECT MAX(salary)
                 FROM `employees`
                );

#Showing employee names of the sales department after finding the sales department ID
SELECT first_name
FROM `employees`
WHERE department_id = (SELECT department_id 
			     FROM `departments` 
			     WHERE department_name = "sales"
                      );


#Find out the employee names who are in the sales department and has experience greater than 3 years
SELECT first_name
FROM `employees`
WHERE department_id = (SELECT department_id 
			     FROM `departments` 
			     WHERE department_name = "sales"
                      ) AND employee_id IN (SELECT `employee_id` 
                       FROM job_history
                       WHERE (year(end_date)-year(start_date)) > 3)