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 the full name (first and last), hire date, salary, and department number for those employees whose first name does not containing the letter M.
assistant
SELECT first_name , last_name , hire_date , salary , department_id FROM employees WHERE first_name NOT LIKE '%M%'
human
Display the first name and department name for each employee.
assistant
SELECT T1.first_name , T2.department_name FROM employees AS T1 JOIN departments AS T2 ON T1.department_id = T2.department_id
human
What are the first and last name for those employees who works either in department 70 or 90?
assistant
SELECT first_name , last_name FROM employees WHERE department_id = 70 OR department_id = 90
human
when is the hire date for those employees whose first name does not containing the letter M?
assistant
SELECT hire_date FROM employees WHERE first_name NOT LIKE '%M%'
human
display the employee name ( first name and last name ) and hire date for all employees in the same department as Clara.
assistant
SELECT first_name , last_name , hire_date FROM employees WHERE department_id = (SELECT department_id FROM employees WHERE first_name = 'Clara')
human
What are the full names and hire dates for employees in the same department as someone with the first name Clara, not including Clara?
assistant
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" ;