-- create a table
CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  gender TEXT NOT NULL
);
-- insert some values
INSERT INTO students VALUES (1, 'Ryan', 'M');
INSERT INTO students VALUES (2, 'Joanna', 'F');
-- fetch some values
SELECT * FROM students WHERE gender = 'F';

-- From the following table, write a SQL query to find those employees who get higher salary than the employee whose ID is 163. Return first name, last name.
SELECT first_name,last_name FROM employees WHERE salary>(SELECT Salary FROM employees WHERE employee_id=163);

-- From the following table, write a SQL query to find those employees whose designation is the same as the employee whose ID is 169. Return first name, last name, department ID and job ID.
SELECT first_name,last_name,salary,department_id,job_id FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE employee_id=169);

-- From the following table, write a SQL query to find those employees whose salary matches the smallest salary of any of the departments. Return first name, last name and department ID.
SELECT first_name,last_name,salary,department_id
FROM employees  WHERE salary IN(SELECT MIN(Salary) FROM employees GROUP BY department_id);

-- From the following table, write a SQL query to find those employees who earn more than the average salary. Return employee ID, first name, last name.
SELECT employee_id, first_name,last_name  
FROM employees  
WHERE salary >  
( SELECT AVG(salary)  
FROM employees 
);
-- From the following table, write a SQL query to find those employees who report that manager whose first name is ‘Payam’. Return first name, last name, employee ID and salary.
SELECT first_name, last_name, employee_id, salary  
FROM employees  
WHERE manager_id = 
(SELECT employee_id  
FROM employees  
WHERE first_name = 'Payam' 
);
-- From the following tables, write a SQL query to find all those employees who work in the Finance department. Return department ID, name (first name), job ID and department name.
SELECT e.department_id,e.first_name,e.job_id,d.department_name FROM employees e,departments d WHERE e.department_id=d.department_id AND d.department_name='Finance';

-- From the following table, write a SQL query to find the employee whose salary is 3000 and reporting person’s ID is 121. Return all fields
SELECT * FROM employees WHERE (salary,manager_id)=(SELECT 3000,121);

-- From the following table, write a SQL query to find those employees whose ID matches any of the number 134, 159 and 183. Return all the fields.
SELECT * FROM employees WHERE employee_id IN(134,159,183);

-- From the following table, write a SQL query to find those employees whose salary is in the range 1000, and 3000 (Begin and end values have included.). Return all the fields.
SELECT * FROM employees WHERE salary BETWEEN 1000 AND 3000;

-- From the following table and write a SQL query to find those employees whose salary is in the range of smallest salary, and 2500. Return all the fields.
SELECT * FROM employees WHERE Salary BETWEEN (SELECT MIN (salary) FROM employees) AND 2500;

-- From the following tables, write a SQL query to find those employees who do not work in those departments where manager ids are in the range 100, 200 (Begin and end values are included.) Return all the fields of the employees.
SELECT * FROM employees WHERE department_id NOT IN (SELECT department_id FROM departments WHERE manager_id BETWEEN 100 AND 200);

-- From the following table, write a SQL query to find those employees who get second-highest salary. Return all the fields of the employees.
SELECT * FROM employees WHERE employee_id IN (SELECT employee_id FROM employees WHERE salary=(SELECT MAX(salary) FROM employees WHERE salary<(SELECT MAX(salary) FROM employees )));

-- From the following tables, write a SQL query to find those employees who work in the same department where 'Clara' works. Exclude all those records where first name is 'Clara'. Return first name, last name and hire date.
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';

--