SQL TEST1 2018

--1.get the emploee numbers for employees working on project p2 and having employee number  lover than 10000--

SELECT emp_no ,project_no
FROM works_on
WHERE project_no = 'p2'  AND emp_no <10000


SELECT emp_no ,project.project_no
FROM works_on
JOIN project
ON works_on.project_no = project.project_no
WHERE project.project_no = 'p2'  AND works_on.emp_no <10000

--2.Get the employee numbers for employees who did't enter their project in 2007

SELECT emp_no, enter_date
FROM works_on
WHERE DATEPART(YEAR, enter_date) NOT IN (2007)

--3.GEt the employee numbers and last names of all employees whose first names contain two letters t i s

SELECT emp_no, emp_fname
FROM employee
WHERE emp_fname LIKE'%[ts]%'

--4.FIND the  employee numbers of all employees whose deprtments are located in SEATlle

SELECT  emp_no , department.location
FROM employee
JOIN
department
ON employee.dept_no = department.dept_no
WHERE  location  = 'Seattle'

--5.How does the GROUP BY clause manage to NULL values? Does it correspond to the general treatment ot these values?
NULL VALUES OF a COLUMN are grouped AS a seperate GROUP.

--6.What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) – RETURNS the total NUMBER OF records IN a TABLE (Including NULL valued records).

COUNT(COLUMN Name) – RETURNS the total NUMBER OF Non-NULL records. It means that, it ignores counting NULL valued records IN that particular COLUMN.

--7. Find the highest employee number
 SELECT MAX(emp_no) FROM employee

--8. Get the jobs that are done by more than two employees

SELECT emp_no ,job FROM works_on

WHERE job IN
  (
    SELECT job
      FROM works_on
      GROUP BY job
      HAVING COUNT(*) >=2
  )

--9.Get the employee numbers and job titles of all eployees working on 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'

--10.Get the frist and the last names of all employeees that work for departments Research or Accounting

SELECT emp_lname , emp_fname, department.dept_name
FROM employee
JOIN department
ON employee.dept_no = department.dept_no
WHERE department.dept_name = 'research' OR department.dept_name = 'accounting'

--11. Get the enter dates of all clerks that belong to the department d1

SELECT enter_date, project_no ,job ,employee.dept_no
FROM works_on
JOIN employee
ON employee.emp_no = works_on.emp_no
WHERE works_on.job = 'clerks' AND   employee.dept_no = 'd1'


--12.Get the names of projects on which two or more clerks are working-zjebane nie działa
SELECT  project_name, works_on.job
FROM project
JOIN works_on
ON project.project_no = works_on.project_no
GROUP BY project.project_name
WHERE works_on.job = 'clerk' AND COUNT(job)>1

IN (SELECT job FROM works_on GROUB BY job HAVING COUNT(job)>1)--kurwa

SELECT project_name
FROM project

WHERE project_no IN(SELECT project_no FROM works_on WHERE COUNT(job)>1 ) AND IN(SELECT job FROM works_on WHERE job= 'clerk')



--13.Get the first and last names of the employees that are managers and that worl on project Mercury
SELECT emp_lname , emp_fname,works_on.job, project.project_name
FROM employee
JOIN works_on
ON employee.emp_no = works_on.emp_no
JOIN project
ON works_on.project_no = project.project_no
WHERE works_on.job = 'manager' AND project.project_name = 'mercury'

--14. Get the eployee numbers of all employees belonging to the Marketing Department.
--Fiind TWo eQuivalent Solutions
SELECT emp_no, department.dept_name
FROM employee
JOIN department
ON employee.dept_no = department.dept_no
WHERE department.dept_name = 'marketing'