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