-- 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';

-- write a SQL query to find the first name, last name, department number, and department name for each employee.
SELECT E.first_name,E.last_name,E.department_id,D.department_name FROM employees E join departments D ON E.department_id=d.department_id;

-- Display the first and last name, department, city, and state province for each employee
SELECT E.first_name,E.last_name,D.department_name,L.city,L.state_province
FROM employees E JOIN departmentsD ON E.department_id=D.department_id JOIN locations L ON D.location_id=L.location_id;

-- From the following table, write a SQL query to find the first name, last name, salary, and job grade for all employees
SELECT E.first_name, E.last_name, E.salary, J.grade_level
 FROM employees E 
   JOIN job_grades J
     ON E.salary BETWEEN J.lowest_sal AND J.highest_sal;
-- From the following tables, write a SQL query to find all those employees who work in department ID 80 or 40. Return first name, last name, department number and department name.
SELECT E.first_name,E.last_name,E.department_id,D.department_name FROM employees E JOIN departments D ON E.department_id=D.department_id AND E.departments_id IN (80,40) ORDER BY E.last_name;

-- From the following tables, write a SQL query to find those employees whose first name contains a letter ‘z’. Return first name, last name, department, city, and state province.
SELECT E.first_name, E.last_name,D.department_name,L.city,L.state_province FROM employees E JOIN  Departments D ON E.department_id=D.department_id JOIN Locations L ON D.location_id= L.location_id WHERE E.first_name LIKE '%z%';

-- From the following table, write a SQL query to find all departments including those without any employee. Return first name, last name, department ID, department name.

SELECT E.first_name,E.last_name,D.department_name,D.department_id FROM employees E RIGHT OUTER JOIN departments D ON E.department_id=D.department_id;

-- From the following table, write a SQL query to find those employees who earn less than the employee of ID 182. Return first name, last name and salary.
SELECT E.first_name,E.last_name,E.salary FROM employees E JOIN employees S ON E.salary=S.salary AND S.employee_id=182;

-- From the following table, write a SQL query to find the employees and their managers. Return the first name of the employee and manager.
SELECT E.first_name as 'Employee Name', M.first_name as 'Manager Name' FROM employees E JOIN employees M ON E.manager_id = M.employee_id;

-- From the following tables, write a SQL query to display the department name, city, and state province for each department.
SELECT D.department_name , L.city , L.state_province
  FROM  departments D 
    JOIN locations L 
      ON  D.location_id = L.location_id;
-- From the following tables, write a SQL query to find those employees who have or not any department. Return first name, last name, department ID, department name.
SELECT E.first_name, E.last_name, E.department_id, D.department_name 
  FROM employees E 
   LEFT OUTER JOIN departments D 
     ON E.department_id = D.department_id;

-- From the following table, write a SQL query to find the employees and their managers. These managers do not work under any manager. Return the first name of the employee and manager.
SELECT E.first_name AS "Employee Name",
   M.first_name AS "Manager"
    FROM employees E 
      LEFT OUTER JOIN employees M
       ON E.manager_id = M.employee_id;

-- From the following tables, write a SQL query to find those employees who work in a department where the employee of last name 'Taylor' works. Return first name, last name and department ID.
SELECT E.first_name, E.last_name, E.department_id 
 FROM employees E 
   JOIN employees S
     ON E.department_id = S.department_id
       AND S.last_name = 'Taylor';
-- From the following tables, write a SQL query to find those employees who joined between 1st January 1993 and 31 August 1997. Return job title, department name, employee name, and joining date of the job.
SELECT job_title, department_name, first_name || ' ' || last_name AS Employee_name, start_date 
	FROM job_history 
		JOIN jobs USING (job_id) 
			JOIN departments USING (department_id) 
				JOIN  employees USING (employee_id) 
					WHERE start_date>='1993-01-01' AND start_date<='1997-08-31';
-- From the following tables, write a SQL query to find the difference between maximum salary of the job and salary of the employees. Return job title, employee name, and salary difference.
SELECT job_title, first_name || ' ' || last_name AS Employee_name, 
	max_salary-salary AS salary_difference 
	FROM employees 
		NATURAL JOIN jobs;

-- From the following table, write a SQL query to compute the average salary, number of employees received commission in that department. Return department name, average salary and number of employees.
SELECT department_name,AVG(salary,count(commission_pct) FROM departments JOIN employeees USING (Department_id ) groupby department_name;

-- From the following tables, write a SQL query to compute the difference between maximum salary and salary of all the employees who works the department of ID 80. Return job title, employee name and salary difference.
SELECT job_title,first_name ||' '|| last_name as employee_name,
max_salary-salary As salary_difference FROM employees 
natural JOIN jobs  WHERE department_id=80;

-- From the following table, write a SQL query to find the name of the country, city, and departments, which are running there.
SELECT country_name,city,department_name FROM countries JOIN locations USING (contry_id) JOIN department USING (location_id);

-- From the following tables, write a SQL query to find the department name and the full name (first and last name) of the manager.
SELECT department_id,first_name ||' '||last_name As name_of_manager
FROM department D JOIN employee E ON (D.manager_id=E.employee_id);

-- From the following table, write a SQL query to compute the average salary of employees for each job title.

SELECT job_title, AVG(salary) 
	FROM employees 
		NATURAL JOIN jobs 
			GROUP BY job_title;
-- From the following table, write a SQL query to find those employees who earn $12000 and above. Return employee ID, starting date, end date, job ID and department ID.
SELECT a.* FROM job_history a JOIN employees m ON (a.employee_id=m.employee_id) WHERE  salary>=12000;

-- From the following tables, write a SQL query to find those departments where at least 2 employees work. Group the result set on country name and city. Return country name, city, and number of departments
SELECT country_name,city, COUNT(department_id) FROM countries JOIN locations USING (country_id) JOIN departments USING (location_id) WHERE department_id IN (SELECT department_id FROM employees GROUP BY department_id HAVING COUNT(department_id)>=2)
GROUP BY country_name,city;.

-- From the following tables, write a SQL query to find the department name, full name (first and last name) of the manager and their city.
SELECT department_name,first_name||' '||last_name from departments D join employees E ON (D.manager_id=e.employee_id) JOIN locations L USING (location_id); 

-- From the following tables, write a SQL query to compute the number of days worked by employees in a department of ID 80. Return employee ID, job title, number of days worked.
SELECT employee_id,job_title,end_date-start_date DAYS FROM job_history NATURAL JOIN jobs GROUP BY  department_id=80;

-- From the following tables, write a SQL query to find full name (first and last name), and salary of those employees who work in any department located in 'London' city.
SELECT first_name||' '|| last_name AS employee_name,salary FROM employees JOIN departments USING (department_id) JOIN location USING (location_id) WHERE city='London'; 

--  From the following tables, write a SQL query to find full name (first and last name), job title, starting and ending date of last jobs of employees who worked without a commission percentage
SELECT CONCAT(e.first_name, ' ', e.last_name) AS Employee_name,
       j.job_title,
       h.*
FROM employees e
JOIN
  (SELECT MAX(start_date),
          MAX(end_date),
          employee_id
   FROM job_history
   GROUP BY employee_id) h ON e.employee_id=h.employee_id
JOIN jobs j ON j.job_id=e.job_id
WHERE e.commission_pct = 0;

-- From the following tables, write a SQL query to find the department name, department ID, and number of employees in each department.
From the following tables, write a SQL query to find the department name, department ID, and number of employees in each department.

-- From the following tables, write a SQL query to find the full name (first and last name) of the employee with ID and name of the country presently where he/she is working.
SELECT first_name || ' ' || last_name 
AS Employee_name, employee_id, country_name 
FROM employees 
JOIN departments 
USING(department_id) 
JOIN locations 
USING( location_id) 
JOIN countries 
USING ( country_id);