lab5

--get full details of employees, their number itp and also dep number, location etc
SELECT emp_no, emp_fname, emp_lname, department.dept_no, department.location, department.dept_name
FROM employee
JOIN department
ON employee.dept_no=department.dept_no

--------------------------------------------
--get full details of all employees who work on the project Gemini
SELECT *
FROM employee
JOIN works_on
ON employee.emp_no=works_on.emp_no
JOIN project
ON works_on.project_no=project.project_no
WHERE project.project_name='Gemini'

---------------------------------------------
--get the dept number of all employees that entered their ptojects on 15 October 2007
SELECT department.dept_no
FROM department
JOIN employee
ON department.dept_no=employee.dept_no
JOIN works_on
ON employee.emp_no=works_on.emp_no
WHERE enter_date='2007-10-15'

---------------------------------------------
--get the first and last names of all analysts whose dep is located in Settle
SELECT emp_lname, emp_fname
FROM employee
JOIN works_on
ON employee.emp_no=works_on.emp_no

JOIN department
ON employee.dept_no=department.dept_no
WHERE works_on.job= 'analyst'
AND department.location='Seattle'

---------------------------------------------
--get the names of projects(with reduntant duplicated eliminated) being worked on by employees in the Accounting dep
SELECT DISTINCT project_name
FROM project
JOIN works_on
ON project.project_no=works_on.project_no
JOIN employee
ON works_on.emp_no=employee.emp_no
JOIN department
ON employee.dept_no=department.dept_no
WHERE dept_name='accounting'

---------------------------------------------
--get full details of employee and dep info where the domicle of an employee aphabetically
--precedes the location of the dep where he works
SELECT *
FROM employee
JOIN department
ON employee.dept_no=department.dept_no
WHERE city>location