human
Schema:
regions (REGION_ID, REGION_NAME)
countries (COUNTRY_ID, COUNTRY_NAME, REGION_ID)
departments (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
jobs (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
job_history (EMPLOYEE_ID, START_DATE, END_DATE, JOB_ID, DEPARTMENT_ID)
locations (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
Please answer with SQL.
assistant
Sure, I understand.
human
List the full name (first and last name), and salary for those employees who earn below 6000.
assistant
SELECT first_name , last_name , salary FROM employees WHERE salary < 6000
human
What is the first name and job id for all employees in the Finance department?
assistant
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'
human
display the first and last name, department, city, and state province for each employee.
assistant
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
human
What is the full name ( first name and last name ) for those employees who gets more salary than the employee whose id is 163?
assistant
SELECT first_name , last_name FROM employees WHERE salary > (SELECT salary FROM employees WHERE employee_id = 163 )
human
Find employee with ID and name of the country presently where (s)he is working.
assistant
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
human
What the full names, ids of each employee and the name of the country they are in?
assistant
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 ;