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
display those departments where more than ten employees work who got a commission percentage.
assistant
SELECT department_id FROM employees GROUP BY department_id HAVING COUNT(commission_pct) > 10
human
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?
assistant
SELECT * FROM job_history AS T1 JOIN employees AS T2 ON T1.employee_id = T2.employee_id WHERE T2.salary >= 12000
human
Return all the information for all employees without any department number.
assistant
SELECT * FROM employees WHERE department_id = 'null'
human
What are the full name (first and last name) and salary for all employees who does not have any value for commission?
assistant
SELECT first_name , last_name , salary FROM employees WHERE commission_pct = 'null'
human
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.
assistant
SELECT email FROM employees WHERE commission_pct = 'null' AND salary BETWEEN 7000 AND 12000 AND department_id = 50
human
Return all information about employees with salaries between 8000 and 12000 for which commission is not null or where their department id is not 40.
assistant
SELECT * FROM employees WHERE salary BETWEEN 8000 AND 12000 AND commission_pct != "null" OR department_id != 40 ;