CREATE TABLE countries (country_id CLOB, country_name CLOB, region_id NUMBER)
CREATE TABLE job_history (employee_id NUMBER, start_date TIME, end_date TIME, job_id CLOB, department_id NUMBER)
CREATE TABLE jobs (job_id CLOB, job_title CLOB, min_salary NUMBER, max_salary NUMBER)
CREATE TABLE departments (department_id NUMBER, department_name CLOB, manager_id NUMBER, location_id NUMBER)
CREATE TABLE employees (employee_id NUMBER, first_name CLOB, last_name CLOB, email CLOB, phone_number CLOB, hire_date TIME, job_id CLOB, salary NUMBER, commission_pct NUMBER, manager_id NUMBER, department_id NUMBER)
CREATE TABLE locations (location_id NUMBER, street_address CLOB, postal_code CLOB, city CLOB, state_province CLOB, country_id CLOB)
CREATE TABLE regions (region_id NUMBER, region_name CLOB)

display the employee number, name( first name and last name ), and salary for all employees who earn more than the average salary and who work in a department with any employee with a 'J' in their first name.
SELECT employee_id, first_name, last_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees) AND department_id IN (SELECT department_id FROM employees WHERE first_name LIKE '%J%')
SELECT "employee_id", "first_name", "last_name", "salary" FROM "employees" WHERE "department_id" IN (SELECT "department_id" FROM "employees" WHERE "first_name" LIKE '%J%') AND "salary" > (SELECT AVG("salary") FROM "employees")