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: 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%'