3. MySQL_HR_Questions_Queries.txt

Filterting and Sorting Queries : 

	1.	Write a SQL query to find those employees whose salary is less than 6000. Return full name (first and last name), and salary.
          	SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) AS full_name, EMAIL,salary FROM tbl_employees WHERE SALARY <6000;

	2.	Write a SQL query to find those employees whose salary is higher than 8000. Return first name, last name and department number and salary.
		SELECT FIRST_NAME,LAST_NAME, DEPARTMENT_ID as Department_number , salary FROM tbl_employees WHERE SALARY>8000;

	3.	Write a SQL query to find those employees whose last name is "McEwen". Return first name, last name and department ID.
		SELECT FIRST_NAME,LAST_NAME, DEPARTMENT_ID  FROM tbl_employees WHERE LAST_NAME="McEwen";

	4.	Write a SQL query to find those employees who have no department number. Return employee_id, first_name, last_name, email,phone_number,hire_date, job_id, salary,commission_pct,manager_id and department_id.
		SELECT FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID, DEPARTMENT_ID  FROM tbl_employees WHERE  DEPARTMENT_ID IS NULL OR DEPARTMENT_ID =0;

    5.	Write a SQL query to find the details of 'Marketing' department. Return all fields.
        SELECT DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID  FROM tbl_departments WHERE DEPARTMENT_NAME="Marketing"

	6.	Write a SQL query to find those employees whose first name does not contain the letter ‘M’. Sort the result-set in ascending order by department ID. Return full name (first and last name together), hire_date, salary and department_id.
		SELECT concat(FIRST_NAME," ",LAST_NAME) as Full_Name, HIRE_DATE, DEPARTMENT_ID, SALARY  FROM tbl_employees WHERE FIRST_NAME NOT LIKE '%M%' ORDER BY DEPARTMENT_ID ASC;

	
	7.	Write a SQL query to find those employees who falls one of the following criteria : 
        1. whose salary is in the range of 8000, 12000 (Begin and end values are included.) and get some commission.
        2. those employees who joined before ‘2003-06-05’ and not included in the department number 40, 120 and 70. Return all fields. Go to the editor  
		  SELECT * FROM tbl_employees WHERE (SALARY BETWEEN 8000 AND 12000 AND COMMISSION_PCT IS NOT NULL) OR ( HIRE_DATE < "2003-06-05" AND DEPARTMENT_ID NOT IN (40,120,70));


   	8.	Write a SQL query to find those employees who do not earn any commission.Return full name (first and last name), and salary.
        SELECT concat(FIRST_NAME," ",LAST_NAME) as Full_Name,SALARY  FROM tbl_employees WHERE COMMISSION_PCT=0;

	9.	Write a SQL query to find those employees whose salary is in the range 9000,17000 (Begin and end values are included). Return full name, contact details and salary.
		SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) AS Full_Name,SALARY,CONCAT(PHONE_NUMBER, ',', EMAIL) AS contact_details FROM tbl_employees WHERE SALARY BETWEEN 9000 AND 17000;

	10.	Write a SQL query to find those employees whose first name ends with the letter ‘m’. Return the first and last name, and salary.
        SELECT FIRST_NAME, LAST_NAME ,SALARY FROM tbl_employees WHERE FIRST_NAME LIKE "%m";


	11.	Write a SQL query to find those employees whose salary is not in the range 7000 and 15000 (Begin and end values are included). Sort the result-set in ascending order by the full name (first and last). Return full name and salary.
		SELECT  CONCAT(FIRST_NAME, ' ', LAST_NAME) AS Full_Name ,SALARY FROM tbl_employees WHERE SALARY NOT BETWEEN 7000 AND 15000 ORDER BY (Full_Name) ASC;


	12.	Write a SQL query to find those employees who were hired during November 5th, 2007 and July 5th, 2009. Return full name (first and last), job id and hire date. Go to the editor
        SELECT concat(first_name , ' ' , last_name) AS full_name,job_id,hire_date FROM tbl_employees WHERE hire_date BETWEEN '2007-11-05' AND '2009-07-05';

	13.	Write a SQL query to find those employees who works either in department 70 or 90. Return full name (first and last name), department id.
		SELECT concat(first_name , ' ' , last_name) AS full_name,DEPARTMENT_ID  FROM tbl_employees 
		WHERE DEPARTMENT_ID IN (70,90);

	14.	Write a SQL query to find those employees who work under a manager. Return full name (first and last name), salary, and manager ID.
		SELECT concat(first_name , ' ' , last_name) AS full_name,salary, manager_id FROM tbl_employees 
		WHERE MANAGER_ID IS NOT NULL;

	15.	Write a SQL query to find those employees who were hired before June 21st, 2002. Return all fields.
		SELECT * FROM tbl_employees 
		WHERE HIRE_DATE < "2002-06-21";	

	16.	Write a SQL query to find those employees whose managers hold the ID 120 or 103 or 145. Return first name, last name, email, salary and manager ID.
		SELECT first_name, last_name, email, salary, manager_id
		FROM tbl_employees
		WHERE manager_id IN (120, 103, 145);

	17.	Write a SQL query to find those employees whose first name contains the letters D, S, or N. Sort the result-set in descending order by salary. Return                 all fields.
		SELECT * FROM tbl_employees
		WHERE first_name LIKE '%D%' OR first_name LIKE '%S%' OR first_name LIKE '%N%'
		ORDER BY salary DESC;
	
	18.	Write a SQL query to find those employees who earn above 11000 or the seventh character in their phone number is 3. Sort the result-set in descending                 order by first name. Return full name (first name and last name), hire date, commission percentage, email, and telephone separated by '-', and salary.
	
		SELECT 
    		CONCAT(first_name, ' ', last_name) AS full_name, 
    		hire_date,
    		commission_pct, 
    		email, 
   		REPLACE(phone_number, '.', '-') AS telephone, 
   		salary
		FROM 
   		 tbl_employees
		WHERE 
    		salary > 11000 OR SUBSTRING(phone_number, 7, 1) = '3'
		ORDER BY 
    		full_name DESC;

    19.	Write a SQL query to find those employees whose first name contains a character ‘s’ in 3rd position. Return first name, last name and department id. Go to the editor
        SELECT first_name, last_name, department_id
        FROM tbl_employees
        WHERE SUBSTRING(first_name, 3, 1) = 's';
	
    20.	Write a SQL query to find those employees who are working in the departments, which are not included in the department number 50 or 30 or 80. Return employee_id, first_name, job_id, department_id.
        SELECT employee_id, first_name, job_id, department_id
        FROM tbl_employees
        WHERE department_id NOT IN (50, 30, 80);
            
    21.	Write a SQL query to find those employees whose department numbers are included in 30 or 40 or 90. Return employee id, first name, job id, department id.
        SELECT employee_id, first_name, job_id, department_id
        FROM tbl_employees
        WHERE department_id IN (30, 40, 90);

    22.	Write a SQL query to find those employees who worked more than two jobs in the past. Return employee id.
        SELECT employee_id
        FROM (
            SELECT employee_id, COUNT(DISTINCT job_id) as job_count
            FROM tbl_jobhistory
            GROUP BY employee_id
        ) AS sub
        WHERE job_count >2;

    23.	Write a SQL query to count the number of employees, sum of all salary, and difference between the highest salary and lowest salary by each job id. Return job_id, count, sum, salary_difference.
        SELECT 
        job_id,
        COUNT(employee_id) as count,
        SUM(salary) as sum,
        MAX(salary) - MIN(salary) as salary_difference
        FROM tbl_employees
        GROUP BY job_id;	

    24.	Write a SQL query to find each job ids where two or more employees worked for more than 300 days. Return job id.
        SELECT JOB_ID
        FROM TBL_JobHistory
        GROUP BY JOB_ID
        HAVING COUNT(EMPLOYEE_ID) >= 2 AND SUM(DATEDIFF(END_DATE, START_DATE)) > 300;

    25.	Write a SQL query to count the number of cities in each country has. Return country ID and number of cities.
        SELECT country_id, COUNT(city) as number_of_cities
        FROM tbl_locations
        GROUP BY country_id;

    26.	Write a SQL query to count the number of employees worked under each manager. Return manager ID and number of employees.
        SELECT manager_id, COUNT(employee_id) as number_of_employees
        FROM tbl_employees
        GROUP BY manager_id;	
        
    27.	Write a SQL query to find all jobs. Sort the result-set in descending order by job title. Return all fields.
        SELECT *
        FROM tbl_jobs
        ORDER BY job_title DESC;

    28.	Write a SQL query to find all those employees who are either Sales Representative or Salesman. Return first name, last name and hire date.
        SELECT first_name, last_name, hire_date
        FROM tbl_employees
        WHERE job_id IN (
            SELECT job_id
            FROM tbl_jobs
            WHERE job_title IN ('Sales Representative', 'Salesman')
        );

    29.	Write a SQL query to calculate average salary of those employees for each department who get a commission percentage. Return department id, average salary.
        SELECT department_id, AVG(salary) as average_salary
        FROM tbl_employees
        WHERE commission_pct IS NOT NULL
        GROUP BY department_id;	

    30.	Write a SQL query to find those departments where a manager can manage four or more employees. Return department_id.
        SELECT department_id
        FROM (
            SELECT department_id, COUNT(employee_id) as employee_count
            FROM tbl_employees
            GROUP BY department_id
        ) AS sub
        WHERE employee_count >= 4;

    31.	Write a SQL query to find those departments where more than ten employees work, who got a commission percentage. Return department id.
        SELECT department_id
        FROM (
            SELECT department_id, COUNT(employee_id) as employee_count
            FROM tbl_employees
            WHERE commission_pct IS NOT NULL 
            GROUP BY department_id
        ) AS sub
        WHERE employee_count > 10;	

    32.	Write a SQL query to find those employees who have completed their previous jobs. Return employee ID, end_date.
        SELECT employee_id, end_date
        FROM tbl_jobhistory
        WHERE end_date IS NOT NULL;

    33.	Write a SQL query to find those employees who have no commission percentage and salary within the range 7000, 12000 (Begin and end values are included.) and works in the department number 50. Return all the fields of employees.
        SELECT *
        FROM tbl_employees
        WHERE commission_pct IS NULL
        AND salary BETWEEN 7000 AND 12000
        AND department_id = 50;

    34.	Write a SQL query to compute the average salary of each job ID. Exclude those records where average salary is higher than 8000. Return job ID, average salary.
        SELECT job_id, AVG(salary) as average_salary
        FROM tbl_employees
        GROUP BY job_id
        HAVING average_salary <= 8000;

    35.	Write a SQL query to find those job titles where the difference between minimum and maximum salaries is in the range the range 12000, 18000 (Begin and end values are included.). Return job_title, max_salary-min_salary.
        SELECT job_title, (MAX(max_salary) - MIN(min_salary)) as salary_difference
        FROM tbl_jobs
        GROUP BY job_title
        HAVING salary_difference BETWEEN 12000 AND 18000;

    36.	Write a SQL query to find those employees whose first name or last name starts with the letter ‘D’. Return first name, last name.
        SELECT first_name, last_name
        FROM tbl_employees
        WHERE first_name LIKE 'D%' OR last_name LIKE 'D%';

    37.	Write a SQL query to find details of those jobs where minimum salary exceeds 9000. Return all the fields of jobs.
        SELECT *
        FROM tbl_jobs
        WHERE min_salary > 9000;

    38.	Write a SQL query to find those employees who joined after 7th September 1987. Return all the fields.
        SELECT *
        FROM tbl_employees
        WHERE hire_date > '1987-09-07';

SubQueries : 
            
    39.	Write a SQL query to find those employees who get higher salary than the employee whose ID is 163. Return first name, last name.
        SELECT *
        FROM tbl_employees
        WHERE SALARY > ( select SALARY from tbl_employees where EMPLOYEE_ID = 163);

    40.	Write a SQL query to find those employees whose designation is the same as the employee whose ID is 169. Return first name, last name, department ID and job ID.
        SELECT ep.first_name, ep.last_name, ep.department_id, ep.job_id
        FROM tbl_employees ep, tbl_employees ep2
        WHERE ep.job_id = ep2.job_id AND ep2.employee_id = 169;	

    41.	Write a SQL query to find those employees whose salary matches the smallest salary of any of the departments. Return first name, last name and department ID.
        SELECT first_name, last_name, department_id 
        FROM tbl_employees 
        WHERE salary = (SELECT MIN(salary) FROM tbl_employees);	

    42.	Write a SQL query to find those employees who earn more than the average salary. Return employee ID, first name, last name.
        SELECT employee_id, first_name, last_name
        FROM tbl_employees
        WHERE salary > (SELECT AVG(salary) FROM tbl_employees);	

    43.	Write a SQL query to find those employees who report that manager whose first name is ‘Payam’. Return first name, last name, employee ID and salary.
        SELECT ep.first_name, ep.last_name, ep.employee_id, ep.salary
        FROM tbl_employees ep, tbl_employees ep2
        WHERE ep.manager_id = ep2.employee_id AND ep2.first_name = 'Payam';	

    44.	Write a SQL query to find all those employees who work in the Finance department. Return department ID, name (first), job ID and department name.
        SELECT ep.department_id, ep.first_name, ep.job_id,
 	(SELECT department_name FROM tbl_departments WHERE department_id = ep.department_id )AS department_name 
        FROM tbl_employees ep
        where ep.department_id =(
        select department_id FROM tbl_departments
        WHERE department_name = 'Finance');	

    45.	Write a SQL query to find the employee whose salary is 3000 and reporting person’s ID is 121. Return all fields.
        SELECT *
        FROM tbl_employees
        WHERE salary = 3000 AND manager_id = 121;

    46.	Write a SQL query to find those employees whose ID matches any of the number 134, 159 and 183. Return all the fields. 
        SELECT *
        FROM tbl_employees
        WHERE employee_id IN (134, 159, 183);

    47.	Write a SQL query to find those employees whose salary is in the range 1000, and 3000 (Begin and end values have included.). Return all the fields.
        SELECT *
        FROM tbl_employees
        WHERE salary BETWEEN 1000 AND 3000;

    48. From the following table and write a SQL query to find those employees whose salary is in the range of smallest salary, and 2500. Return all the fields.
        SELECT *
        FROM tbl_employees
        WHERE salary BETWEEN (SELECT MIN(salary) FROM tbl_employees) AND 2500;

    49.	Write a SQL query to find those employees who do not work in those departments where manager ids are in the range 100, 200 (Begin and end values are included.) Return all the fields of the employees.
        SELECT *
        FROM tbl_employees
        WHERE department_id NOT IN (
            SELECT department_id
            FROM tbl_departments
            WHERE manager_id BETWEEN 100 AND 200
        );

    50.	Write a SQL query to find those employees who get second-highest salary. Return all the fields of the employees.
        SELECT *
        FROM tbl_employees
        WHERE salary = (
            SELECT DISTINCT salary
            FROM tbl_employees
            ORDER BY salary DESC
            LIMIT 1 OFFSET 1
        );

    51.	Write a SQL query to find those employees who work in the same department where ‘Clara’ works. Exclude all those records where first name is ‘Clara’. Return first name, last name and hire date.
        SELECT ep.first_name, ep.last_name, ep.hire_date
        FROM tbl_employees ep
        WHERE ep.department_id = (
            SELECT department_id
            FROM tbl_employees
            WHERE first_name = 'Clara'
        ) AND ep.first_name <> 'Clara';

    52.	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.
        SELECT employee_id, first_name, last_name
        FROM tbl_employees
        WHERE first_name LIKE '%T%';

    53.	Write a SQL query to find those employees who earn more than the average salary and work in a department with any employee whose first name contains a character a 'J'. Return employee ID, first name and salary.
        SELECT ep.employee_id, ep.first_name, ep.salary
        FROM tbl_employees ep
        WHERE ep.salary > (
            SELECT AVG(salary)
            FROM tbl_employees
        ) AND ep.department_id IN (
            SELECT department_id
            FROM tbl_employees
            WHERE first_name LIKE '%J%'
        );

    54.	Write a SQL query to find those employees whose department located at 'Toronto'. Return first name, last name, employee ID, job ID.
        SELECT first_name, last_name, employee_id, job_id
        FROM tbl_employees  where
        department_id = (select department_id from tbl_departments where LOCATION_ID= (select LOCATION_ID from tbl_locations where city = 'Toronto'))

    55.Write a SQL query to find those employees whose salary is lower than any salary of those employees whose job title is ‘MK_MAN’. Return employee ID, first name, last name, job ID.
        SELECT employee_id, first_name, last_name, job_id
        FROM tbl_employees 
        WHERE salary <  (
            SELECT salary
            FROM tbl_employees
            WHERE job_id = 'MK_MAN'
        );

     56.Write a SQL query to find those employees whose salary is lower than any salary of those employees whose job title is 'MK_MAN'. Exclude employees of Job title ‘MK_MAN’. Return employee ID, first name, last name, job ID.
        SELECT ep.employee_id, ep.first_name, ep.last_name, ep.SALARY, ep.job_id
        FROM tbl_employees ep
        WHERE ep.salary <  (
            SELECT salary
            FROM tbl_employees
            WHERE job_id = 'MK_MAN'
        ) AND ep.job_id <> 'MK_MAN';

    57.Write a SQL query to find those employees whose salary is more than any salary of those employees whose job title is 'PU_MAN'. Exclude job title 'PU_MAN'. Return employee ID, first name, last name, job ID.
        SELECT employee_id, first_name, last_name, job_id
        FROM tbl_employees
        WHERE salary >  (
            SELECT salary
            FROM tbl_employees
            WHERE job_id = 'PU_MAN'
        ) AND job_id != 'PU_MAN';
            
    58.	Write a SQL query to find those employees whose salary is more than average salary of any department. Return employee ID, first name, last name, job ID.
        SELECT employee_id, first_name, last_name, job_id
        FROM tbl_employees ep
        WHERE salary >  (
            SELECT AVG(salary)
            FROM tbl_employees ep2 
            where ep2.department_id = ep.department_id
        );

    59.	Write a SQL query to find any existence of those employees whose salary exceeds 3700. Return first name, last name and department ID.
        SELECT first_name, last_name, department_id
        FROM tbl_employees
        WHERE salary > 3700;

    60.	Write a SQL query to find total salary of those departments where at least one employee works. Return department ID, total salary.
        SELECT department_id, SUM(salary) as total_salary
        FROM tbl_employees
        GROUP BY department_id;

    61. Write a query to display the employee id, name ( first name and last name ) and the job id column with a modified title SALESMAN for those employees whose job title is ST_MAN and DEVELOPER for whose job title is IT_PROG.
        SELECT employee_id,
        concat(first_name ,' ' ,last_name) AS name,
            CASE 
                WHEN job_id = 'ST_MAN' THEN 'SALESMAN'
                WHEN job_id = 'IT_PROG' THEN 'DEVELOPER'
                ELSE job_id
            END AS modified_job_title
        FROM tbl_employees;

    62. Write a query to display the employee id, name ( first name and last name ), salary and the SalaryStatus column with a title HIGH and LOW respectively for those employees whose salary is more than and less than the average salary of all employees.
        SELECT employee_id,
        concat(first_name ,' ' ,last_name) AS name,
            salary,
            CASE 
                WHEN salary > (SELECT AVG(salary) FROM tbl_employees) THEN 'HIGH'
                ELSE 'LOW'
            END AS SalaryStatus
        FROM tbl_employees;

    63. Write a query to display the employee id, name ( first name and last name ), SalaryDrawn, AvgCompare (salary - the average salary of all employees) and the SalaryStatus column with a title HIGH and LOW respectively for those employees whose salary is more than and less than the average salary of all employees.
        SELECT employee_id,
            concat(first_name ,' ' ,last_name) AS name,
            salary AS SalaryDrawn,
            salary - (SELECT AVG(salary) FROM tbl_employees) AS AvgCompare,
            CASE 
                WHEN salary > (SELECT AVG(salary) FROM tbl_employees) THEN 'HIGH'
                ELSE 'LOW'
            END AS SalaryStatus
        FROM tbl_employees;

    64.	Write a SQL query to find all those departments where at least one or more employees work.Return department name.
        SELECT DISTINCT department_name
        FROM tbl_departments
        WHERE department_id IN (SELECT department_id FROM tbl_employees);	

    65.	Write a SQL query to find those employees who work in departments located at 'United Kingdom'. Return first name.
        SELECT ep.FIRST_NAME
	    FROM tbl_employees ep, tbl_departments dp, tbl_locations lc, tbl_countries co
	    WHERE ep.DEPARTMENT_ID = dp.DEPARTMENT_ID
  	    AND dp.LOCATION_ID = lc.LOCATION_ID
  	    AND lc.COUNTRY_ID = co.COUNTRY_ID
  	    AND co.COUNTRY_NAME = 'United Kingdom';


    66.	Write a SQL query to find those employees who earn more than average salary and who work in any of the 'IT' departments. Return last name.
        SELECT last_name
        FROM tbl_employees
        WHERE salary > (SELECT AVG(salary) FROM tbl_employees)
        AND department_id IN (SELECT department_id FROM tbl_departments WHERE department_name LIKE 'IT%');

    67.	Write a SQL query to find all those employees who earn more than an employee whose last name is 'Ozer'. Sort the result in ascending order by last name. Return first name, last name and salary.
        SELECT first_name, last_name, salary
        FROM tbl_employees
        WHERE salary > (SELECT salary FROM tbl_employees WHERE last_name = 'Ozer')
        ORDER BY last_name ASC;

    68.	Write a SQL query to find those employees who work under a manager based in ‘US’. Return first name, last name.SELECT ep.FIRST_NAME, ep.LAST_NAME
        FROM tbl_employees ep, tbl_employees ep2, tbl_departments dp, 
        tbl_locations lo, tbl_countries co
   	    WHERE ep.MANAGER_ID = ep2.EMPLOYEE_ID
 	    AND ep.DEPARTMENT_ID = dp.DEPARTMENT_ID
	    AND dp.LOCATION_ID = lo.LOCATION_ID
  	    AND lo.COUNTRY_ID = co.COUNTRY_ID
 	    AND co.COUNTRY_NAME = 'United States of America';
    
    69.	Write a SQL query to find those employees whose salary is greater than 50% of their department's total salary bill. Return first name, last name.
        SELECT ep.first_name, ep.last_name
        FROM tbl_employees ep
        WHERE ep.salary > 0.5 * (
            SELECT SUM(salary) AS total_salary
            FROM tbl_employees
            WHERE department_id = ep.department_id
        );
    
    70.	Write a SQL query to find those employees who are managers. Return all the fields of employees table.
        SELECT *
        FROM tbl_employees
        WHERE employee_id IN (SELECT DISTINCT manager_id FROM tbl_employees);
            
    71.	Write a SQL query to find those employees who manage a department. Return all the fields of employees table.
        SELECT *
        FROM Tbl_Employees
        WHERE EMPLOYEE_ID IN (
            SELECT MANAGER_ID
            FROM Tbl_Departments
        );

    72.	Write a SQL query to find those employees who get such a salary, which is the maximum of salaried employee, joining within January 1st, 2002 and December 31st, 2003. Return employee ID, first name, last name, salary, department name and city.
      
        SELECT ep.EMPLOYEE_ID, ep.FIRST_NAME, ep.LAST_NAME, ep.SALARY, 
       (SELECT DEPARTMENT_NAME FROM Tbl_Departments WHERE DEPARTMENT_ID = ep.DEPARTMENT_ID) AS DEPARTMENT_NAME,
       CONCAT(
           (SELECT STREET_ADDRESS FROM tbl_locations WHERE LOCATION_ID = 
               (SELECT LOCATION_ID FROM Tbl_Departments WHERE DEPARTMENT_ID = ep.DEPARTMENT_ID)),
           " ,",
           (SELECT CITY FROM tbl_locations WHERE LOCATION_ID = 
               (SELECT LOCATION_ID FROM Tbl_Departments WHERE DEPARTMENT_ID = ep.DEPARTMENT_ID)),
           ", ",
           (SELECT POSTAL_CODE FROM tbl_locations WHERE LOCATION_ID = 
               (SELECT LOCATION_ID FROM Tbl_Departments WHERE DEPARTMENT_ID = ep.DEPARTMENT_ID)),
           ", ",
           (SELECT STATE_PROVINCE FROM tbl_locations WHERE LOCATION_ID = 
               (SELECT LOCATION_ID FROM Tbl_Departments WHERE DEPARTMENT_ID = ep.DEPARTMENT_ID))
       ) AS location
FROM Tbl_Employees ep
WHERE ep.SALARY = (
    SELECT MAX(SALARY)
    FROM Tbl_Employees
    WHERE HIRE_DATE BETWEEN '2002-01-01' AND '2003-12-31'
);
    
    73.	Write a SQL query to find those departments, located in the city ‘London’. Return department ID, department name.
        SELECT DEPARTMENT_ID, DEPARTMENT_NAME
        FROM Tbl_Departments
        WHERE LOCATION_ID = (
            SELECT LOCATION_ID
            FROM Tbl_Locations
            WHERE LOCATION_NAME = 'London'
        );

    74.	Write a SQL query to find those employees who earn more than the average salary. Sort the result-set in descending order by salary. Return first name, last name, salary, and department ID.
        SELECT FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID
        FROM Tbl_Employees
        WHERE SALARY > (
            SELECT AVG(SALARY)
            FROM Tbl_Employees
        )
        ORDER BY SALARY DESC;
    
    75.	Write a SQL query to find those employees who earn more than the maximum salary of a department of ID 40. Return first name, last name and department ID.
        SELECT FIRST_NAME, LAST_NAME, DEPARTMENT_ID
        FROM Tbl_Employees
        WHERE SALARY > (
            SELECT MAX(SALARY)
            FROM Tbl_Employees
            WHERE DEPARTMENT_ID = 40
        );

    76.	Write a SQL query to find departments for a particular location. The location matches the location of the department of ID 30. Return department name and department ID.
        SELECT DEPARTMENT_NAME, DEPARTMENT_ID
        FROM Tbl_Departments
        WHERE LOCATION_ID = (
            SELECT LOCATION_ID
            FROM Tbl_Departments
            WHERE DEPARTMENT_ID = 30
        );

    77.	Write a SQL query to find those employees who work in that department where the employee works of ID 201. Return first name, last name, salary, and department ID.
        SELECT ep.FIRST_NAME, ep.LAST_NAME, ep.SALARY, ep.DEPARTMENT_ID
        FROM Tbl_Employees ep
        WHERE ep.DEPARTMENT_ID = (
            SELECT DEPARTMENT_ID
            FROM Tbl_Employees
            WHERE EMPLOYEE_ID = 201
        );

    78.	Write a SQL query to find those employees whose salary matches to the salary of the employee who works in that department of ID 40. Return first name, last name, salary, and department ID.
        SELECT ep.FIRST_NAME, ep.LAST_NAME, ep.SALARY, ep.DEPARTMENT_ID
        FROM Tbl_Employees ep
        WHERE ep.SALARY = (
            SELECT SALARY
            FROM Tbl_Employees
            WHERE DEPARTMENT_ID = 40
        )

    79.	Write a SQL query to find those employees who work in the department 'Marketing'. Return first name, last name and department ID.
        SELECT FIRST_NAME, LAST_NAME, DEPARTMENT_ID
        FROM Tbl_Employees
        WHERE DEPARTMENT_ID = (
            SELECT DEPARTMENT_ID
            FROM Tbl_Departments
            WHERE DEPARTMENT_NAME = 'Marketing'
        );

    80.	Write a SQL query to find those employees who earn more than the minimum salary of a department of ID 40. Return first name, last name, salary, and department ID.
        SELECT FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID
        FROM Tbl_Employees
        WHERE SALARY > (
            SELECT MIN(SALARY)
            FROM Tbl_Employees
            WHERE DEPARTMENT_ID = 40
        );	

    81.	Write a SQL query to find those employees who joined after the employee whose ID is 165. Return first name, last name and hire date.
            SELECT FIRST_NAME, LAST_NAME, HIRE_DATE
        FROM Tbl_Employees
        WHERE HIRE_DATE > (
            SELECT HIRE_DATE
            FROM Tbl_Employees
            WHERE EMPLOYEE_ID = 165
        );

    82.	Write a SQL query to find those employees who earn less than the minimum salary of a department of ID 70. Return first name, last name, salary, and department ID.
       SELECT FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID
        FROM Tbl_Employees
        WHERE SALARY < (
            SELECT MIN(SALARY)
            FROM Tbl_Employees
            WHERE DEPARTMENT_ID = 70
        );

    83.	Write a SQL query to find those employees who earn less than the average salary, and work at the department where the employee 'Laura' (first name) works. Return first name, last name, salary, and department ID.
        SELECT ep.FIRST_NAME, ep.LAST_NAME, ep.SALARY, ep.DEPARTMENT_ID
        FROM Tbl_Employees ep
        WHERE ep.SALARY < (
            SELECT AVG(SALARY)
            FROM Tbl_Employees
            WHERE DEPARTMENT_ID = (
                SELECT DEPARTMENT_ID
                FROM Tbl_Employees
                WHERE FIRST_NAME = 'Laura'
            )
        )AND ep.DEPARTMENT_ID = (
            SELECT DEPARTMENT_ID
            FROM Tbl_Employees
            WHERE FIRST_NAME = 'Laura'
        );
        

    84.	Write a SQL query to find those employees whose department is located in the city 'London'. Return first name, last name, salary, and department ID.    
        SELECT ep.FIRST_NAME, ep.LAST_NAME, ep.SALARY, ep.DEPARTMENT_ID
        FROM Tbl_Employees ep, Tbl_Departments dp, Tbl_Locations lo
        WHERE ep.DEPARTMENT_ID = dp.DEPARTMENT_ID
        AND dp.LOCATION_ID = lo.LOCATION_ID
        AND lo.CITY = 'London';

    85.	Write a SQL query to find the city of the employee of ID 134. Return city.
        SELECT (SELECT lo.CITY
        FROM Tbl_Locations lo, Tbl_Departments dp
        WHERE dp.LOCATION_ID = lo.LOCATION_ID
        AND dp.DEPARTMENT_ID = ep.DEPARTMENT_ID) AS LOCATION_NAME
        FROM Tbl_Employees ep
        WHERE ep.EMPLOYEE_ID = 134;

    86.	Write a SQL query to find those departments where maximum salary is 7000 and above. The employees worked in those departments have already completed one or more jobs. Return all the fields of the departments.
        SELECT *
        FROM Tbl_Departments
        WHERE DEPARTMENT_ID IN (
            SELECT DEPARTMENT_ID
            FROM TBL_JobHistory
            WHERE EMPLOYEE_ID IN (
                SELECT EMPLOYEE_ID
                FROM Tbl_Employees
                WHERE SALARY >= 7000
            )
            GROUP BY DEPARTMENT_ID
        );

    87.	Write a SQL query to find those departments where starting salary is at least 8000. Return all the fields of departments.
        SELECT *
        FROM Tbl_Departments
        WHERE DEPARTMENT_ID IN (
            SELECT DEPARTMENT_ID
            FROM Tbl_Employees
            WHERE SALARY >= 8000
        );

    88.	Write a SQL query to find those managers who supervise four or more employees. Return manager name, department ID.
        SELECT concat( FIRST_NAME ," ", LAST_NAME) AS MANAGER_NAME, DEPARTMENT_ID
        FROM Tbl_Employees
        WHERE EMPLOYEE_ID IN (
            SELECT MANAGER_ID
            FROM Tbl_Employees
            GROUP BY MANAGER_ID
            HAVING COUNT(*) >= 4
        );

    89.	Write a SQL query to find those employees who worked as a 'Sales Representative' in the past. Return all the fields of jobs.
        SELECT *
        FROM tbl_jobs
        WHERE JOB_TITLE = 'Sales Representative';
        
    90.	Write a SQL query to find those employees who earn second-lowest salary of all the employees. Return all the fields of employees.
        SELECT *
        FROM Tbl_Employees
        WHERE SALARY = (
            SELECT DISTINCT SALARY
            FROM Tbl_Employees
            ORDER BY SALARY
            LIMIT 1 OFFSET 1
        );
    
    91.	Write a SQL query to find those departments managed by ‘Susan’. Return all the fields of departments.
        SELECT *
        FROM Tbl_Departments
        WHERE MANAGER_ID = (
            SELECT EMPLOYEE_ID
            FROM Tbl_Employees
            WHERE FIRST_NAME = 'Susan'
        );	
    
    92.	Write a SQL query to find those employees who earn highest salary in a department. Return department ID, employee name, and salary.
        SELECT DEPARTMENT_ID,concat( FIRST_NAME ," ", LAST_NAME) AS EMPLOYEE_NAME, SALARY
        FROM Tbl_Employees ep
        WHERE SALARY = (
            SELECT MAX(SALARY)
            FROM Tbl_Employees
            WHERE DEPARTMENT_ID = ep.DEPARTMENT_ID
        );
            
    93.	Write a SQL query to find those employees who did not have any job in the past. Return all the fields of employees.
        SELECT *
        FROM Tbl_Employees
        WHERE EMPLOYEE_ID NOT IN (
            SELECT DISTINCT EMPLOYEE_ID
            FROM TBL_JobHistory
        );

Joins : 

    94.	Write a SQL query to find the first name, last name, department number, and department name for each employee.
        SELECT ep.FIRST_NAME, ep.LAST_NAME, dp.DEPARTMENT_ID, dp.DEPARTMENT_NAME
        FROM Tbl_Employees ep
        JOIN Tbl_Departments dp ON ep.DEPARTMENT_ID = dp.DEPARTMENT_ID;
            
    95.	Write a SQL query to find the first name, last name, department, city, and state province for each employee.
       SELECT ep.FIRST_NAME, ep.LAST_NAME, dp.DEPARTMENT_NAME, lo.CITY, lo.STATE_PROVINCE
        FROM Tbl_Employees ep
        inner join Tbl_Departments dp ON ep.DEPARTMENT_ID = dp.DEPARTMENT_ID
        inner join Tbl_Locations lo ON dp.LOCATION_ID = lo.LOCATION_ID;	
    
    96.	Write a SQL query to find the first name, last name, salary, and job grade for all employees.
        SELECT 
         ep.first_name, 
         ep.last_name, 
         ep.salary, 
         Jg.grade_level
        FROM 
         tbl_employees ep
        JOIN 
        tbl_job_grade jg ON ep.salary BETWEEN jg.lowest_sal AND jg.highest_sal;

    97.	Write a SQL query to find all those employees who work in department ID 80 or 40. Return first name, last name, department number and department name.
        SELECT ep.FIRST_NAME, ep.LAST_NAME, dp.DEPARTMENT_ID, dp.DEPARTMENT_NAME
        FROM Tbl_Employees ep
        LEFT JOIN Tbl_Departments dp ON ep.DEPARTMENT_ID = dp.DEPARTMENT_ID
        WHERE dp.DEPARTMENT_ID IN (40, 80);
            
    98.	Write a SQL query to find those employees whose first name contains a letter ‘z’. Return first name, last name, department, city, and state province.
        SELECT ep.FIRST_NAME, ep.LAST_NAME, dp.DEPARTMENT_NAME, lo.CITY, lo.STATE_PROVINCE
        FROM Tbl_Employees ep
        INNER JOIN Tbl_Departments dp ON ep.DEPARTMENT_ID = dp.DEPARTMENT_ID
        INNER JOIN Tbl_Locations lo ON dp.LOCATION_ID = lo.LOCATION_ID
        WHERE ep.FIRST_NAME LIKE '%z%';

    99.	Write a SQL query to find all departments including those without any employee. Return first name, last name, department ID, department name.
        SELECT ep.first_name ,ep.last_name ,dp.DEPARTMENT_ID, dp.DEPARTMENT_NAME
        FROM Tbl_Departments dp
        LEFT JOIN Tbl_Employees ep ON dp.DEPARTMENT_ID = ep.DEPARTMENT_ID
        GROUP BY dp.DEPARTMENT_ID, dp.DEPARTMENT_NAME;

    100.Write a SQL query to find those employees who earn less than the employee of ID 182. Return first name, last name and salary.
        SELECT e1.FIRST_NAME, e1.LAST_NAME, e1.SALARY
        FROM Tbl_Employees e1
        JOIN Tbl_Employees e2 ON e1.SALARY < e2.SALARY
        WHERE e2.EMPLOYEE_ID = 182;

    101.Write a SQL query to find the employees and their managers. Return the first name of the employee and manager.
      SELECT ep.FIRST_NAME AS EMPLOYEE_NAME, m.FIRST_NAME AS MANAGER_NAME
      FROM Tbl_Employees ep
      JOIN Tbl_Employees m ON ep.MANAGER_ID = m.EMPLOYEE_ID;

    102.Write a SQL query to display the department name, city, and state province for each department.
        SELECT dp.DEPARTMENT_NAME, lo.CITY, lo.STATE_PROVINCE
        FROM Tbl_Departments dp
        INNER JOIN Tbl_Locations lo ON dp.LOCATION_ID = lo.LOCATION_ID
    
    103.Write a SQL query to find those employees who have or not any department. Return first name, last name, department ID, department name.
        SELECT ep.first_name, ep.last_name, dp.department_id, dp.department_name 
        FROM tbl_employees ep 
        LEFT JOIN tbl_departments dp
        ON ep.department_id = dp.department_id;


    104.Write a SQL query to find the employees and their managers. These managers do not work under any manager. Return the first name of the employee and manager.
        SELECT ep.FIRST_NAME AS EMPLOYEE_NAME, m.FIRST_NAME AS MANAGER_NAME
        FROM Tbl_Employees ep
        LEFT JOIN Tbl_Employees m ON ep.MANAGER_ID = m.EMPLOYEE_ID
        WHERE m.MANAGER_ID IS NULL;

    105.Write a SQL query to find those employees who work in a department where the employee of last name 'Taylor' works. Return first name, last name and department ID.
       SELECT ep.first_name, ep.last_name, ep.department_id
       FROM tbl_employees ep
       INNER JOIN tbl_employees t ON ep.department_id = t.department_id
       WHERE t.last_name = 'Taylor';;

    106.Write a SQL query to find those employees who joined between 1st January 1993 and 31 August 1997. Return job title, department name, employee name, and joining date of the job.
        SELECT j.JOB_TITLE, dp.DEPARTMENT_NAME, concat(FIRST_NAME , ' ' , ep.LAST_NAME) AS EMPLOYEE_NAME, ep.HIRE_DATE
        FROM Tbl_Employees ep
        JOIN tbl_jobs j ON ep.JOB_ID = j.JOB_ID
        JOIN Tbl_Departments dp ON ep.DEPARTMENT_ID = dp.DEPARTMENT_ID
        WHERE ep.HIRE_DATE BETWEEN '1993-01-01' AND '1997-08-31';

    107.Write a SQL query to find the difference between maximum salary of the job and salary of the employees. Return job title, employee name, and salary difference.
        SELECT j.JOB_TITLE, concat(ep.FIRST_NAME , ' ' , ep.LAST_NAME) AS EMPLOYEE_NAME, j.MAX_SALARY - ep.SALARY AS SALARY_DIFFERENCE
        FROM Tbl_Employees ep
        NATURAL JOIN tbl_jobs j;
        
    108.Write a SQL query to compute the average salary, number of employees received commission in that department. Return department name, average salary and number of employees.
        SELECT dp.DEPARTMENT_NAME, AVG(ep.SALARY) AS AVERAGE_SALARY, COUNT(ep.COMMISSION_PCT) AS NUM_EMPLOYEES_WITH_COMMISSION
        FROM Tbl_Employees ep
        JOIN Tbl_Departments dp ON ep.DEPARTMENT_ID = dp.DEPARTMENT_ID
        GROUP BY dp.DEPARTMENT_NAME;
            
    109.Write a SQL query to compute the difference between maximum salary and salary of all the employees who works the department of ID 80. Return job title, employee name and salary difference.
        SELECT j.JOB_TITLE,concat(ep.FIRST_NAME , ' ' , ep.LAST_NAME) AS EMPLOYEE_NAME, j.MAX_SALARY - ep.SALARY AS SALARY_DIFFERENCE
        FROM Tbl_Employees ep
        NATURAL JOIN tbl_jobs j
        WHERE ep.DEPARTMENT_ID = 80;
            
    110.Write a SQL query to find the name of the country, city, and departments, which are running there.
        SELECT co.COUNTRY_NAME, lo.CITY, dp.DEPARTMENT_NAME
        FROM Tbl_Locations lo
        JOIN Tbl_Departments dp ON lo.LOCATION_ID = dp.LOCATION_ID
        JOIN tbl_countries co ON lo.COUNTRY_ID = co.COUNTRY_ID;
            
    111.Write a SQL query to find the department name and the full name (first and last name) of the manager.
        SELECT dp.DEPARTMENT_NAME,concat(m.FIRST_NAME , ' ' , m.LAST_NAME) AS MANAGER_NAME
        FROM Tbl_Departments dp
        JOIN Tbl_Employees m ON dp.MANAGER_ID = m.EMPLOYEE_ID;

    112.Write a SQL query to compute the average salary of employees for each job title.
        SELECT j.JOB_TITLE, AVG(ep.SALARY) AS AVERAGE_SALARY
        FROM Tbl_Employees ep
        NATURAL JOIN tbl_jobs j 
        GROUP BY j.JOB_TITLE;
           
    113.Write a SQL query to find those employees who earn $12000 and above. Return employee ID, starting date, end date, job ID and department ID.
        SELECT ep.EMPLOYEE_ID, j.START_DATE, j.END_DATE, j.JOB_ID, ep.DEPARTMENT_ID
        FROM Tbl_Employees ep
        JOIN TBL_JobHistory j ON ep.EMPLOYEE_ID = j.EMPLOYEE_ID
        WHERE ep.SALARY >= 12000;
    
    114.Write a SQL query to find those departments where at least 2 employees work. Group the result set on country name and city. Return country name, city, and number of departments.
        SELECT co.COUNTRY_NAME, lo.CITY, COUNT(dp.DEPARTMENT_ID) AS NUM_DEPARTMENTS
        FROM Tbl_Locations lo
        JOIN Tbl_Departments dp ON lo.LOCATION_ID = dp.LOCATION_ID
        JOIN Tbl_Employees ep ON dp.DEPARTMENT_ID = ep.DEPARTMENT_ID
        JOIN tbl_countries co ON lo.COUNTRY_ID = co.COUNTRY_ID
        GROUP BY co.COUNTRY_NAME, lo.CITY
        HAVING COUNT(ep.EMPLOYEE_ID) >= 2;
            
    115.Write a SQL query to find the department name, full name (first and last name) of the manager and their city.
        SELECT dp.DEPARTMENT_NAME, concat(m.FIRST_NAME , ' ' , m.LAST_NAME) AS MANAGER_NAME, lo.CITY
        FROM Tbl_Departments dp
        JOIN Tbl_Employees m ON dp.MANAGER_ID = m.EMPLOYEE_ID
        JOIN Tbl_Locations lo ON dp.LOCATION_ID = lo.LOCATION_ID;
    
    116.Write a SQL query to compute the number of days worked by employees in a department of ID 80. Return employee ID, job title, number of days worked.    
        SELECT j.EMPLOYEE_ID, jb.JOB_TITLE, 
        DATEDIFF(j.END_DATE, j.START_DATE) AS NUM_DAYS_WORKED
        FROM TBL_JobHistory j
        NATURAL JOIN tbl_jobs jb
        WHERE j.DEPARTMENT_ID = 80;

    117.Write a SQL query to find full name (first and last name), and salary of those employees who work in any department located in 'London' city.
        SELECT concat(ep.FIRST_NAME , ' ' , ep.LAST_NAME) AS EMPLOYEE_NAME, ep.SALARY
        FROM Tbl_Employees ep
        JOIN Tbl_Departments dp ON ep.DEPARTMENT_ID = dp.DEPARTMENT_ID
        JOIN Tbl_Locations lo ON dp.LOCATION_ID = lo.LOCATION_ID
        WHERE lo.CITY = 'London';
    
    118.Write a SQL query to find full name (first and last name), job title, starting and ending date of last jobs of employees who worked without a commission percentage.
         SELECT CONCAT(ep.FIRST_NAME, ' ', ep.LAST_NAME) AS EMPLOYEE_NAME,
             jb.JOB_TITLE,
             jh.START_DATE,
             jh.END_DATE
        FROM Tbl_Employees ep
        JOIN (
        SELECT EMPLOYEE_ID, MAX(END_DATE) AS MAX_END_DATE
        FROM TBL_JobHistory
        GROUP BY EMPLOYEE_ID
        ) latest_job ON ep.EMPLOYEE_ID = latest_job.EMPLOYEE_ID
        JOIN TBL_JobHistory jh ON ep.EMPLOYEE_ID = jh.EMPLOYEE_ID AND latest_job.MAX_END_DATE = jh.END_DATE
        JOIN tbl_jobs jb ON ep.job_id = jb.job_id
        WHERE ep.COMMISSION_PCT = 0 OR ep.COMMISSION_PCT IS NULL;

        
    119.Write a SQL query to find the department name, department ID, and number of employees in each department.
        SELECT dp.DEPARTMENT_NAME, dp.DEPARTMENT_ID, COUNT(ep.EMPLOYEE_ID) AS NUM_EMPLOYEES
        FROM Tbl_Departments dp
        LEFT JOIN Tbl_Employees ep ON dp.DEPARTMENT_ID = ep.DEPARTMENT_ID
        GROUP BY dp.DEPARTMENT_NAME, dp.DEPARTMENT_ID;
    
    120.Write a SQL query to find the full name (first and last name) of the employee with ID and name of the country presently where he/she is working.
        SELECT concat(ep.FIRST_NAME , ' ' , ep.LAST_NAME ,", ", ep.EMPLOYEE_ID) AS EMPLOYEE_NAME_WITH_ID  , co.COUNTRY_NAME
        FROM Tbl_Employees ep
        JOIN Tbl_Departments dp ON ep.DEPARTMENT_ID = dp.DEPARTMENT_ID
        JOIN Tbl_Locations lo ON dp.LOCATION_ID = lo.LOCATION_ID
        JOIN tbl_countries co ON lo.COUNTRY_ID = co.COUNTRY_ID