CREATE TABLE regions (
    REGION_ID NUMBER PRIMARY KEY,
    REGION_NAME TEXT
);
CREATE TABLE countries (
    COUNTRY_ID TEXT PRIMARY KEY,
    COUNTRY_NAME TEXT,
    REGION_ID NUMBER,
    FOREIGN KEY ("REGION_ID") REFERENCES regions("REGION_ID")
);
CREATE TABLE departments (
    DEPARTMENT_ID NUMBER PRIMARY KEY,
    DEPARTMENT_NAME TEXT,
    MANAGER_ID NUMBER,
    LOCATION_ID NUMBER
);
CREATE TABLE jobs (
    JOB_ID TEXT PRIMARY KEY,
    JOB_TITLE TEXT,
    MIN_SALARY NUMBER,
    MAX_SALARY NUMBER
);
CREATE TABLE employees (
    EMPLOYEE_ID NUMBER PRIMARY KEY,
    FIRST_NAME TEXT,
    LAST_NAME TEXT,
    EMAIL TEXT,
    PHONE_NUMBER TEXT,
    HIRE_DATE TIME,
    JOB_ID TEXT,
    SALARY NUMBER,
    COMMISSION_PCT NUMBER,
    MANAGER_ID NUMBER,
    DEPARTMENT_ID NUMBER,
    FOREIGN KEY ("JOB_ID") REFERENCES jobs("JOB_ID"),
    FOREIGN KEY ("DEPARTMENT_ID") REFERENCES departments("DEPARTMENT_ID")
);
CREATE TABLE job_history (
    EMPLOYEE_ID NUMBER PRIMARY KEY,
    START_DATE TIME,
    END_DATE TIME,
    JOB_ID TEXT,
    DEPARTMENT_ID NUMBER,
    FOREIGN KEY ("JOB_ID") REFERENCES jobs("JOB_ID"),
    FOREIGN KEY ("DEPARTMENT_ID") REFERENCES departments("DEPARTMENT_ID"),
    FOREIGN KEY ("EMPLOYEE_ID") REFERENCES employees("EMPLOYEE_ID")
);
CREATE TABLE locations (
    LOCATION_ID NUMBER PRIMARY KEY,
    STREET_ADDRESS TEXT,
    POSTAL_CODE TEXT,
    CITY TEXT,
    STATE_PROVINCE TEXT,
    COUNTRY_ID TEXT,
    FOREIGN KEY ("COUNTRY_ID") REFERENCES countries("COUNTRY_ID")
);

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)