lab 4 sql

--Subqueries
--• Get the first and last names of employees who work in the Research department.

/*
SELECT emp_fname, emp_lname,dept_name
FROM employee
JOIN department
ON employee.dept_no = department.dept_no
WHERE dept_name = 'Research'
*/

--• Get full details of all employees whose department is located in Dallas.

/*
SELECT  employee.* , department.location
FROM employee
JOIN
department
ON employee.dept_no = department.dept_no
WHERE  location  = 'Dallas'
*/
/*
SELECT *
FROM employee
WHERE dept_no  IN
(
SELECT dept_no
FROM department

WHERE  location  = 'Dallas'
)*/

--• Get the last names of all employees who work on the project Apollo.
/*
SELECT *--emp_lname
FROM employee
JOIN works_on
ON employee.emp_no = works_on.emp_no
JOIN project
ON project.project_no = works_on.project_no
WHERE  project_name = 'Apollo'
*/


--Havings.
--• Get project numbers for all projects employing fewer than four persons.
/*
SELECT project_no
FROM employee
JOIN works_on
ON employee.emp_no =  works_on.
GROUP BY  project_no
HAVING COUNT(project_no)<4
*/

--• Group rows of the works_on table by job and eliminate those jobs that do not begin with the letter M.

/*
SELECT job
FROM works_on
GROUP BY job
HAVING  job NOT LIKE 'm%'
*/

--Order by
--• Get department numbers and employee names for employees with employee numbers < 20000, in ascending order of last and first names.
/*
SELECT dept_no ,emp_fname ,emp_lname, emp_no
FROM  employee
WHERE emp_no < 20000
ORDER BY emp_lname , emp_fname ASC
*/


--• For each project number, get the project number and the number of all employees, in descending order of the employee number.
/*
 SELECT project_no, COUNT(emp_no)
 FROM  works_on
 GROUP BY project_no
 ORDER BY  COUNT(emp_no) DESC
*/
--Union
-- Get the employee number for employees who either belong to department d1 or entered their project before 1/1/2007,
--in ascending order of employee number.


/*
SELECT emp_no FROM employee
WHERE dept_no = 'd1'
UNION
SELECT emp_no  FROM works_on
WHERE enter_date < '01-01-2007'
ORDER BY emp_no
*/

-- Get the employee number for employees who either belong to department d1 didn't
--enter their project before 1/1/2007, in ascending order of employee number.
/*
SELECT emp_no  FROM employee
WHERE dept_no = 'd1'
EXCEPT
SELECT emp_no   FROM works_on
WHERE enter_date < '01-01-2007'
ORDER BY  emp_no ASC
*/