Database: hr_1
Schema:
CREATE TABLE `regions` (
  `REGION_ID` decimal(5,0) NOT NULL,
  `REGION_NAME` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`REGION_ID`)
)

CREATE TABLE `countries` (
  `COUNTRY_ID` varchar(2) NOT NULL,
  `COUNTRY_NAME` varchar(40) DEFAULT NULL,
  `REGION_ID` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`COUNTRY_ID`),
  FOREIGN KEY (`REGION_ID`) REFERENCES regions (`REGION_ID`)
)

CREATE TABLE `departments` (
  `DEPARTMENT_ID` decimal(4,0) NOT NULL DEFAULT '0',
  `DEPARTMENT_NAME` varchar(30) NOT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `LOCATION_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`DEPARTMENT_ID`)
)

CREATE TABLE `jobs` (
  `JOB_ID` varchar(10) NOT NULL DEFAULT '',
  `JOB_TITLE` varchar(35) NOT NULL,
  `MIN_SALARY` decimal(6,0) DEFAULT NULL,
  `MAX_SALARY` decimal(6,0) DEFAULT NULL,
  PRIMARY KEY (`JOB_ID`)
)

CREATE TABLE `employees` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL DEFAULT '0',
  `FIRST_NAME` varchar(20) DEFAULT NULL,
  `LAST_NAME` varchar(25) NOT NULL,
  `EMAIL` varchar(25) NOT NULL,
  `PHONE_NUMBER` varchar(20) DEFAULT NULL,
  `HIRE_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `SALARY` decimal(8,2) DEFAULT NULL,
  `COMMISSION_PCT` decimal(2,2) DEFAULT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`),
  FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES departments(`DEPARTMENT_ID`),
  FOREIGN KEY (`JOB_ID`) REFERENCES jobs(`JOB_ID`)
)

CREATE TABLE `job_history` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL,
  `START_DATE` date NOT NULL,
  `END_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`,`START_DATE`),
  FOREIGN KEY (`EMPLOYEE_ID`) REFERENCES employees(`EMPLOYEE_ID`),
  FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES departments(`DEPARTMENT_ID`),
  FOREIGN KEY (`JOB_ID`) REFERENCES jobs(`JOB_ID`)
)

CREATE TABLE `locations` (
  `LOCATION_ID` decimal(4,0) NOT NULL DEFAULT '0',
  `STREET_ADDRESS` varchar(40) DEFAULT NULL,
  `POSTAL_CODE` varchar(12) DEFAULT NULL,
  `CITY` varchar(30) NOT NULL,
  `STATE_PROVINCE` varchar(25) DEFAULT NULL,
  `COUNTRY_ID` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`LOCATION_ID`),
  FOREIGN KEY (`COUNTRY_ID`) REFERENCES countries(`COUNTRY_ID`)
)

Question: Display the first name and department name for each employee.
SQL: SELECT T1.first_name ,  T2.department_name FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id

Question: What are the first name and department name of all employees?
SQL: SELECT T1.first_name ,  T2.department_name FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id

Question: List the full name (first and last name), and salary for those employees who earn below 6000.
SQL: SELECT first_name ,  last_name ,  salary FROM employees WHERE salary  <  6000

Question: What are the full names and salaries for any employees earning less than 6000?
SQL: SELECT first_name ,  last_name ,  salary FROM employees WHERE salary  <  6000

Question: Display the first name, and department number for all employees whose last name is "McEwen".
SQL: SELECT first_name ,  department_id FROM employees WHERE last_name  =  'McEwen'

Question: What are the first names and department numbers for employees with last name McEwen?
SQL: SELECT first_name ,  department_id FROM employees WHERE last_name  =  'McEwen'

Question: Return all the information for all employees without any department number.
SQL: SELECT * FROM employees WHERE department_id  =  "null"

Question: What are all the employees without a department number?
SQL: SELECT * FROM employees WHERE department_id  =  "null"

Question: Display all the information about the department Marketing.
SQL: SELECT * FROM  departments WHERE department_name  =  'Marketing'

Question: What is all the information about the Marketing department?
SQL: SELECT * FROM  departments WHERE department_name  =  'Marketing'

Question: when is the hire date for those employees whose first name does not containing the letter M?
SQL: SELECT hire_date FROM employees WHERE first_name NOT LIKE '%M%'

Question: On what dates were employees without the letter M in their first names hired?
SQL: SELECT hire_date FROM employees WHERE first_name NOT LIKE '%M%'

Question: display the full name (first and last), hire date, salary, and department number for those employees whose first name does not containing the letter M.
SQL: SELECT first_name ,  last_name ,  hire_date ,  salary ,  department_id FROM employees WHERE first_name NOT LIKE '%M%'

Question: What are the full name, hire date, salary, and department id for employees without the letter M in their first name?
SQL: SELECT first_name ,  last_name ,  hire_date ,  salary ,  department_id FROM employees WHERE first_name NOT LIKE '%M%'

Question: display the full name (first and last), hire date, salary, and department number for those employees whose first name does not containing the letter M and make the result set in ascending order by department number.
SQL: SELECT first_name ,  last_name ,  hire_date ,  salary ,  department_id FROM employees WHERE first_name NOT LIKE '%M%' ORDER BY department_id

Question: What are the full name, hire data, salary and department id for employees without the letter M in their first name, ordered by ascending department id?
SQL: SELECT first_name ,  last_name ,  hire_date ,  salary ,  department_id FROM employees WHERE first_name NOT LIKE '%M%' ORDER BY department_id

Question: what is the phone number of employees whose salary is in the range of 8000 and 12000?
SQL: SELECT phone_number FROM employees WHERE salary BETWEEN 8000 AND 12000

Question: Return the phone numbers of employees with salaries between 8000 and 12000.
SQL: SELECT phone_number FROM employees WHERE salary BETWEEN 8000 AND 12000

Question: display all the information of employees whose salary is in the range of 8000 and 12000 and commission is not null or department number does not equal to 40.
SQL: SELECT * FROM employees WHERE salary BETWEEN 8000 AND 12000 AND commission_pct != "null" OR  department_id != 40

Question: Return all information about employees with salaries between 8000 and 12000 for which commission is not null or where their department id is not 40.
SQL: SELECT * FROM employees WHERE salary BETWEEN 8000 AND 12000 AND commission_pct != "null" OR  department_id != 40

Question: What are the full name (first and last name) and salary for all employees who does not have any value for commission?
SQL: SELECT first_name ,  last_name ,  salary FROM  employees WHERE commission_pct  =  "null"

Question: Return the full names and salaries of employees with null commissions.
SQL: SELECT first_name ,  last_name ,  salary FROM  employees WHERE commission_pct  =  "null"

Question: Display the first and last name, and salary for those employees whose first name is ending with the letter m.
SQL: SELECT first_name ,  last_name ,  salary FROM employees WHERE first_name LIKE '%m'

Question: Return the full names and salaries for employees with first names that end with the letter m.
SQL: SELECT first_name ,  last_name ,  salary FROM employees WHERE first_name LIKE '%m'

Question: Find job id and date of hire for those employees who was hired between November 5th, 2007 and July 5th, 2009.
SQL: SELECT job_id ,  hire_date FROM employees WHERE hire_date BETWEEN '2007-11-05' AND '2009-07-05'

Question: What are the job ids and dates of hire for employees hired after November 5th, 2007 and before July 5th, 2009?
SQL: SELECT job_id ,  hire_date FROM employees WHERE hire_date BETWEEN '2007-11-05' AND '2009-07-05'

Question: What are the first and last name for those employees who works either in department 70 or 90?
SQL: SELECT first_name ,  last_name FROM employees WHERE department_id  =  70 OR department_id  =  90

Question: What are the full names of employees who with in department 70 or 90?
SQL: SELECT first_name ,  last_name FROM employees WHERE department_id  =  70 OR department_id  =  90

Question: Find the salary and manager number for those employees who is working under a manager.
SQL: SELECT salary ,  manager_id FROM employees WHERE manager_id != "null"

Question: What are the salaries and manager ids for employees who have managers?
SQL: SELECT salary ,  manager_id FROM employees WHERE manager_id != "null"

Question: display all the details from Employees table for those employees who was hired before 2002-06-21.
SQL: SELECT * FROM employees WHERE hire_date  <  '2002-06-21'

Question: What is all the information about employees hired before June 21, 2002?
SQL: SELECT * FROM employees WHERE hire_date  <  '2002-06-21'

Question: display all the information for all employees who have the letters D or S in their first name and also arrange the result in descending order by salary.
SQL: SELECT * FROM employees WHERE first_name LIKE '%D%' OR first_name LIKE '%S%' ORDER BY salary DESC

Question: What is all the information about employees with D or S in their first name, ordered by salary descending?
SQL: SELECT * FROM employees WHERE first_name LIKE '%D%' OR first_name LIKE '%S%' ORDER BY salary DESC

Question: display those employees who joined after 7th September, 1987.
SQL: SELECT * FROM employees WHERE hire_date  >  '1987-09-07'

Question: Which employees were hired after September 7th, 1987?
SQL: SELECT * FROM employees WHERE hire_date  >  '1987-09-07'

Question: display the job title of jobs which minimum salary is greater than 9000.
SQL: SELECT job_title FROM jobs WHERE min_salary  >  9000

Question: Which job titles correspond to jobs with salaries over 9000?
SQL: SELECT job_title FROM jobs WHERE min_salary  >  9000

Question: display job Title, the difference between minimum and maximum salaries for those jobs which max salary within the range 12000 to 18000.
SQL: SELECT job_title ,  max_salary - min_salary FROM jobs WHERE max_salary BETWEEN 12000 AND 18000

Question: What are the job titles, and range of salaries for jobs with maximum salary between 12000 and 18000?
SQL: SELECT job_title ,  max_salary - min_salary FROM jobs WHERE max_salary BETWEEN 12000 AND 18000

Question: display the emails of the employees who have no commission percentage and salary within the range 7000 to 12000 and works in that department which number is 50.
SQL: SELECT email FROM employees WHERE commission_pct  =  "null" AND salary BETWEEN 7000 AND 12000 AND department_id = 50

Question: What are the emails of employees with null commission, salary between 7000 and 12000, and who work in department 50?
SQL: SELECT email FROM employees WHERE commission_pct  =  "null" AND salary BETWEEN 7000 AND 12000 AND department_id = 50

Question: display the employee ID for each employee and the date on which he ended his previous job.
SQL: SELECT employee_id ,  MAX(end_date) FROM job_history GROUP BY employee_id

Question: What are the employee ids for each employee and final dates of employment at their last job?
SQL: SELECT employee_id ,  MAX(end_date) FROM job_history GROUP BY employee_id

Question: display those departments where more than ten employees work who got a commission percentage.
SQL: SELECT department_id FROM employees GROUP BY department_id HAVING COUNT(commission_pct) > 10

Question: What are the department ids for which more than 10 employees had a commission?
SQL: SELECT department_id FROM employees GROUP BY department_id HAVING COUNT(commission_pct) > 10

Question: Find the ids of the departments where any manager is managing 4 or more employees.
SQL: SELECT DISTINCT department_id FROM employees GROUP BY department_id ,  manager_id HAVING COUNT(employee_id)  >= 4

Question: What are department ids for departments with managers managing more than 3 employees?
SQL: SELECT DISTINCT department_id FROM employees GROUP BY department_id ,  manager_id HAVING COUNT(employee_id)  >= 4

Question: display the average salary of employees for each department who gets a commission percentage.
SQL: SELECT department_id ,  AVG(salary) FROM employees WHERE commission_pct != "null" GROUP BY department_id

Question: What is the average salary of employees who have a commission percentage that is not null?
SQL: SELECT department_id ,  AVG(salary) FROM employees WHERE commission_pct != "null" GROUP BY department_id

Question: display the country ID and number of cities for each country.
SQL: SELECT country_id ,  COUNT(*) FROM locations GROUP BY country_id

Question: Give the country id and corresponding count of cities in each country.
SQL: SELECT country_id ,  COUNT(*) FROM locations GROUP BY country_id

Question: display job ID for those jobs that were done by two or more for more than 300 days.
SQL: SELECT job_id FROM job_history WHERE end_date - start_date  > 300 GROUP BY job_id HAVING COUNT(*) >= 2

Question: What are the job ids for jobs done more than once for a period of more than 300 days?
SQL: SELECT job_id FROM job_history WHERE end_date - start_date  > 300 GROUP BY job_id HAVING COUNT(*) >= 2

Question: display the ID for those employees who did two or more jobs in the past.
SQL: SELECT employee_id FROM job_history GROUP BY employee_id HAVING COUNT(*)  >= 2

Question: What are the employee ids for employees who have held two or more jobs?
SQL: SELECT employee_id FROM job_history GROUP BY employee_id HAVING COUNT(*)  >= 2

Question: Find employee with ID and name of the country presently where (s)he is working.
SQL: SELECT T1.employee_id ,  T4.country_name FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id JOIN locations AS T3 ON T2.location_id  =  T3.location_id JOIN countries AS T4 ON T3.country_id  =  T4.country_id

Question: What are all the employee ids and the names of the countries in which they work?
SQL: SELECT T1.employee_id ,  T4.country_name FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id JOIN locations AS T3 ON T2.location_id  =  T3.location_id JOIN countries AS T4 ON T3.country_id  =  T4.country_id

Question: display the department name and number of employees in each of the department.
SQL: SELECT T2.department_name ,  COUNT(*) FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id GROUP BY T2.department_name

Question: Give the name of each department and the number of employees in each.
SQL: SELECT T2.department_name ,  COUNT(*) FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id GROUP BY T2.department_name

Question: Can you return all detailed info of jobs which was done by any of the employees who is presently earning a salary on and above 12000?
SQL: SELECT * FROM  job_history AS T1 JOIN employees AS T2 ON T1.employee_id  =  T2.employee_id WHERE T2.salary  >=  12000

Question: What is all the job history info done by employees earning a salary greater than or equal to 12000?
SQL: SELECT * FROM  job_history AS T1 JOIN employees AS T2 ON T1.employee_id  =  T2.employee_id WHERE T2.salary  >=  12000

Question: display job title and average salary of employees.
SQL: SELECT job_title ,  AVG(salary) FROM employees AS T1 JOIN jobs AS T2 ON T1.job_id  =  T2.job_id GROUP BY T2.job_title

Question: What is the average salary for each job title?
SQL: SELECT job_title ,  AVG(salary) FROM employees AS T1 JOIN jobs AS T2 ON T1.job_id  =  T2.job_id GROUP BY T2.job_title

Question: What is the full name ( first name and last name ) for those employees who gets more salary than the employee whose id is 163?
SQL: SELECT first_name ,  last_name FROM employees WHERE salary  >  (SELECT salary FROM employees WHERE employee_id = 163 )

Question: Provide the full names of employees earning more than the employee with id 163.
SQL: SELECT first_name ,  last_name FROM employees WHERE salary  >  (SELECT salary FROM employees WHERE employee_id = 163 )

Question: return the smallest salary for every departments.
SQL: SELECT MIN(salary) ,  department_id FROM employees GROUP BY department_id

Question: What is the minimum salary in each department?
SQL: SELECT MIN(salary) ,  department_id FROM employees GROUP BY department_id

Question: Find the first name and last name and department id for those employees who earn such amount of salary which is the smallest salary of any of the departments.
SQL: SELECT first_name ,  last_name ,  department_id FROM employees WHERE salary IN (SELECT MIN(salary) FROM employees GROUP BY department_id)

Question: What are the full names and department ids for the lowest paid employees across all departments.
SQL: SELECT first_name ,  last_name ,  department_id FROM employees WHERE salary IN (SELECT MIN(salary) FROM employees GROUP BY department_id)

Question: Find the employee id for all employees who earn more than the average salary.
SQL: SELECT employee_id FROM employees WHERE salary  >  (SELECT AVG(salary) FROM employees)

Question: What are the employee ids for employees who make more than the average?
SQL: SELECT employee_id FROM employees WHERE salary  >  (SELECT AVG(salary) FROM employees)

Question: display the employee id and salary of all employees who report to Payam (first name).
SQL: SELECT employee_id ,  salary FROM employees WHERE manager_id  =  (SELECT employee_id FROM employees WHERE first_name  =  'Payam' )

Question: What are the employee ids of employees who report to Payam, and what are their salaries?
SQL: SELECT employee_id ,  salary FROM employees WHERE manager_id  =  (SELECT employee_id FROM employees WHERE first_name  =  'Payam' )

Question: find the name of all departments that do actually have one or more employees assigned to them.
SQL: SELECT DISTINCT T2.department_name FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id

Question: What are the names of departments that have at least one employee.
SQL: SELECT DISTINCT T2.department_name FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id

Question: get the details of employees who manage a department.
SQL: SELECT DISTINCT * FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id WHERE T1.employee_id  =  T2.manager_id

Question: What is all the information regarding employees who are managers?
SQL: SELECT DISTINCT * FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id WHERE T1.employee_id  =  T2.manager_id

Question: display all the information about the department Marketing.
SQL: SELECT * FROM  departments WHERE department_name  =  'Marketing'

Question: What is all the information about the Marketing department?
SQL: SELECT * FROM  departments WHERE department_name  =  'Marketing'

Question: display the ID for those employees who did two or more jobs in the past.
SQL: SELECT employee_id FROM job_history GROUP BY employee_id HAVING COUNT(*)  >= 2

Question: What are the employee ids for those who had two or more jobs.
SQL: SELECT employee_id FROM job_history GROUP BY employee_id HAVING COUNT(*)  >= 2

Question: What are the unique ids of those departments where any manager is managing 4 or more employees.
SQL: SELECT DISTINCT department_id FROM employees GROUP BY department_id ,  manager_id HAVING COUNT(employee_id)  >= 4

Question: Give the distinct department ids of departments in which a manager is in charge of 4 or more employees?
SQL: SELECT DISTINCT department_id FROM employees GROUP BY department_id ,  manager_id HAVING COUNT(employee_id)  >= 4

Question: Find the job ID for those jobs which average salary is above 8000.
SQL: SELECT job_id FROM employees GROUP BY job_id HAVING AVG(salary) > 8000

Question: What are the job ids corresponding to jobs with average salary above 8000?
SQL: SELECT job_id FROM employees GROUP BY job_id HAVING AVG(salary) > 8000

Question: display the employee ID and job name for all those jobs in department 80.
SQL: SELECT T1.employee_id ,  T2.job_title FROM employees AS T1 JOIN jobs AS T2 ON T1.job_id  =  T2.job_id WHERE T1.department_id = 80

Question: what are the employee ids and job titles for employees in department 80?
SQL: SELECT T1.employee_id ,  T2.job_title FROM employees AS T1 JOIN jobs AS T2 ON T1.job_id  =  T2.job_id WHERE T1.department_id = 80

Question: What is the first name and job id for all employees in the Finance department?
SQL: SELECT T1.first_name ,  T1.job_id FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id WHERE T2.department_name  =  'Finance'

Question: Give the first name and job id for all employees in the Finance department.
SQL: SELECT T1.first_name ,  T1.job_id FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id WHERE T2.department_name  =  'Finance'

Question: display all the information of the employees whose salary if within the range of smallest salary and 2500.
SQL: SELECT * FROM employees WHERE salary BETWEEN (SELECT MIN(salary) FROM employees) AND 2500

Question: What is all the information regarding employees with salaries above the minimum and under 2500?
SQL: SELECT * FROM employees WHERE salary BETWEEN (SELECT MIN(salary) FROM employees) AND 2500

Question: Find the ids of the employees who does not work in those departments where some employees works whose manager id within the range 100 and 200.
SQL: SELECT * FROM employees WHERE department_id NOT IN (SELECT department_id FROM departments WHERE manager_id BETWEEN 100 AND 200)

Question: What are the ids for employees who do not work in departments with managers that have ids between 100 and 200?
SQL: SELECT * FROM employees WHERE department_id NOT IN (SELECT department_id FROM departments WHERE manager_id BETWEEN 100 AND 200)

Question: display the employee name ( first name and last name ) and hire date for all employees in the same department as Clara.
SQL: SELECT first_name ,  last_name ,  hire_date FROM employees WHERE department_id  =  (SELECT department_id FROM employees WHERE first_name  =  "Clara")

Question: What are the full names and hire dates for employees in the same department as someone with the first name Clara?
SQL: SELECT first_name ,  last_name ,  hire_date FROM employees WHERE department_id  =  (SELECT department_id FROM employees WHERE first_name  =  "Clara")

Question: display the employee name ( first name and last name ) and hire date for all employees in the same department as Clara excluding Clara.
SQL: SELECT first_name ,  last_name ,  hire_date FROM employees WHERE department_id  =  ( SELECT department_id FROM employees WHERE first_name  =  "Clara") AND first_name != "Clara"

Question: What are the full names and hire dates for employees in the same department as someone with the first name Clara, not including Clara?
SQL: SELECT first_name ,  last_name ,  hire_date FROM employees WHERE department_id  =  ( SELECT department_id FROM employees WHERE first_name  =  "Clara") AND first_name != "Clara"

Question: display the employee number and name( first name and last name ) for all employees who work in a department with any employee whose name contains a ’T’.
SQL: SELECT employee_id ,  first_name ,  last_name FROM employees WHERE department_id IN ( SELECT department_id FROM employees WHERE first_name LIKE '%T%' )

Question: What are the ids and full names for employees who work in a department that has someone with a first name that contains the letter T?
SQL: SELECT employee_id ,  first_name ,  last_name FROM employees WHERE department_id IN ( SELECT department_id FROM employees WHERE first_name LIKE '%T%' )

Question: display the employee number, name( first name and last 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 first name.
SQL: SELECT employee_id ,  first_name ,  last_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%')

Question: What are the ids, full names, and salaries for employees making more than average and who work in a department with employees who have the letter J in their first name?
SQL: SELECT employee_id ,  first_name ,  last_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%')

Question: display the employee number and job id for all employees whose salary is smaller than any salary of those employees whose job title is MK_MAN.
SQL: SELECT employee_id ,  job_id FROM employees WHERE salary  <  ( SELECT min(salary) FROM employees WHERE job_id  =  'MK_MAN' )

Question: What are the employee ids and job ids for employees who make less than the lowest earning employee with title MK_MAN?
SQL: SELECT employee_id ,  job_id FROM employees WHERE salary  <  ( SELECT min(salary) FROM employees WHERE job_id  =  'MK_MAN' )

Question: display the employee number, name( first name and last name ) and job title for all employees whose salary is more than any salary of those employees whose job title is PU_MAN.
SQL: SELECT employee_id ,  first_name ,  last_name ,  job_id FROM employees WHERE salary  >  ( SELECT max(salary) FROM employees WHERE job_id  =  'PU_MAN' )

Question: What are the employee ids, full names, and job ids for employees who make more than the highest earning employee with title PU_MAN?
SQL: SELECT employee_id ,  first_name ,  last_name ,  job_id FROM employees WHERE salary  >  ( SELECT max(salary) FROM employees WHERE job_id  =  'PU_MAN' )

Question: display the department id and the total salary for those departments which contains at least two employees.
SQL: SELECT department_id ,  SUM(salary) FROM employees GROUP BY department_id HAVING count(*)  >=  2

Question: What are total salaries and department id for each department that has more than 2 employees?
SQL: SELECT department_id ,  SUM(salary) FROM employees GROUP BY department_id HAVING count(*)  >=  2

Question: display all the information of those employees who did not have any job in the past.
SQL: SELECT * FROM employees WHERE employee_id NOT IN (SELECT employee_id FROM job_history)

Question: What is all the information about employees who have never had a job in the past?
SQL: SELECT * FROM employees WHERE employee_id NOT IN (SELECT employee_id FROM job_history)

Question: display the department ID, full name (first and last name), salary for those employees who is highest salary in every department.
SQL: SELECT first_name ,   last_name ,   salary ,  department_id ,  MAX(salary) FROM employees GROUP BY department_id

Question: What are the department ids, full names, and salaries for employees who make the most in their departments?
SQL: SELECT first_name ,   last_name ,   salary ,  department_id ,  MAX(salary) FROM employees GROUP BY department_id

Question: display the first and last name, department, city, and state province for each employee.
SQL: SELECT T1.first_name ,  T1.last_name ,  T2.department_name ,  T3.city ,  T3.state_province FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id JOIN locations AS T3 ON T2.location_id  =  T3.location_id

Question: What are the full names, departments, cities, and state provinces for each employee?
SQL: SELECT T1.first_name ,  T1.last_name ,  T2.department_name ,  T3.city ,  T3.state_province FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id JOIN locations AS T3 ON T2.location_id  =  T3.location_id

Question: display those employees who contain a letter z to their first name and also display their last name, city.
SQL: SELECT T1.first_name ,  T1.last_name ,  T3.city FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id JOIN locations AS T3 ON T2.location_id  =  T3.location_id WHERE T1.first_name LIKE '%z%'

Question: What are the full names and cities of employees who have the letter Z in their first names?
SQL: SELECT T1.first_name ,  T1.last_name ,  T3.city FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id JOIN locations AS T3 ON T2.location_id  =  T3.location_id WHERE T1.first_name LIKE '%z%'

Question: display the department name, city, and state province for each department.
SQL: SELECT T1.department_name ,  T2.city ,  T2.state_province FROM departments AS T1 JOIN locations AS T2 ON T2.location_id  =  T1.location_id

Question: What are the department names, cities, and state provinces for each department?
SQL: SELECT T1.department_name ,  T2.city ,  T2.state_province FROM departments AS T1 JOIN locations AS T2 ON T2.location_id  =  T1.location_id

Question: display the full name (first and last name ) of employee with ID and name of the country presently where (s)he is working.
SQL: SELECT T1.first_name ,  T1.last_name , T1.employee_id ,  T4.country_name FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id JOIN locations AS T3 ON T2.location_id  =  T3.location_id JOIN countries AS T4 ON T3.country_id  =  T4.country_id

Question: What the full names, ids of each employee and the name of the country they are in?
SQL: SELECT T1.first_name ,  T1.last_name , T1.employee_id ,  T4.country_name FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id JOIN locations AS T3 ON T2.location_id  =  T3.location_id JOIN countries AS T4 ON T3.country_id  =  T4.country_id

Question: display the department name and number of employees in each of the department.
SQL: SELECT department_name ,  COUNT(*) FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id GROUP BY department_name

Question: What are the department names and how many employees work in each of them?
SQL: SELECT department_name ,  COUNT(*) FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id GROUP BY department_name

Question: display the full name (first and last name), and salary of those employees who working in any department located in London.
SQL: SELECT first_name ,  last_name ,  salary FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id JOIN  locations AS T3 ON T2.location_id  =  T3.location_id WHERE  T3.city  =  'London'

Question: What are full names and salaries of employees working in the city of London?
SQL: SELECT first_name ,  last_name ,  salary FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id JOIN  locations AS T3 ON T2.location_id  =  T3.location_id WHERE  T3.city  =  'London'