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 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