--1 For each student give the number of subjects in which he or she got grades
/*
select st.STUDENT_NAME, count (distinct g.SUBJECT_ID)
from students st, grades g
where st.STUDENT_ID = g.STUDENT_ID
group by st.STUDENT_NAME, st.STUDENT_ID
*/
--2 a) Give the ids and the names of projects which are undertaken by at most three employees.
/*
select p.PROJECT_ID, p.PROJECT_NAME
from projects p, employees e, allocations a
where a.EMPLOYEE_ID = e.EMPLOYEE_ID and a.PROJECT_ID = p.PROJECT_ID
group by p.PROJECT_ID, p.PROJECT_NAME
having  count(distinct a.EMPLOYEE_ID) <= 3
*/
--2 b)  Give the names of projects which are undertaken by the least number of employees
/*
select p.PROJECT_NAME
from projects p, employees e, allocations a
where a.EMPLOYEE_ID = e.EMPLOYEE_ID and a.PROJECT_ID = p.PROJECT_ID
group by p.PROJECT_ID, p.PROJECT_NAME
having  count(distinct a.EMPLOYEE_ID) <= all (
    select count(distinct a.EMPLOYEE_ID)
    from projects p, employees e, allocations a
    where a.EMPLOYEE_ID = e.EMPLOYEE_ID and a.PROJECT_ID = p.PROJECT_ID
)
*/
--3 a) Give the ids and the names of male students, who are older than at least one employee from SOFTWARE team.
/*
select s.STUDENT_ID, s.STUDENT_NAME
from students s
where s.GENDER = 'M' and s.DATE_OF_BIRTH < (
  select max(e.DATE_OF_BIRTH)
  from employees e, teams t
  where e.TEAM_ID = t.TEAM_ID and t.TEAM_NAME = 'SOFTWARE'
)
*/
--3 b)Give the ids and the names of COMPUTER SCIENCE students, who got grades higher than every ROBOTICS student in DATABASES subject.
/*
select s.STUDENT_ID, s.STUDENT_NAME
from students s, grades g
where s.STUDENT_ID = g.STUDENT_ID and s.MAJOR_ID = 3 and (g.SUBJECT_ID = 1 or g.SUBJECT_ID = 2) and g.GRADE > (
    select max(g.GRADE)
    from grades g, students s
    where g.STUDENT_ID = s.STUDENT_ID and s.MAJOR_ID = 6 and (g.SUBJECT_ID = 1 or g.SUBJECT_ID = 2)
)
*/
--3 c) Check if each value of foreign key manager id from PROJECTS table matches to record from ALLOCATIONS table.
/*
select count( distinct p.PROJECT_ID) = 45
from projects p, allocations a
where p.MANAGER_ID = a.EMPLOYEE_ID and a.POSITION_ID = 'MNG'
*/
--4 a) Give the ids and the names of students who haven’t got any grade yet.
/*
select s.STUDENT_ID, s.STUDENT_NAME
from students s
where s.STUDENT_ID not in  (select g.STUDENT_ID
from grades g)
*/
--4 b) Give the names of employees who haven’t been assigned to any projects but received payment(s)
/*
--select e.EMP_NAME, e.EMPLOYEE_ID
--from employees e, salaries s
--where  e.EMPLOYEE_ID = s.EMPLOYEE_ID and e.EMPLOYEE_ID not in (
--  select a.EMPLOYEE_ID
--  from allocations a
--  where a.PROJECT_ID = s.PROJECT_ID
--)
*/
--5  Give the ids, names of projects and the number of employees who are assigned to these projects.
--Include also projects with no employees and pay attention to the possibility of getting money more
--than once for the same project by employee.
/*
select p.PROJECT_ID, p.PROJECT_NAME,  count(distinct a.EMPLOYEE_ID)
from allocations a, projects p
where a.PROJECT_ID = p.PROJECT_ID
group by p.PROJECT_ID, p.PROJECT_NAME
*/
--6. a)Create the view with the ids, names of projects and the total income from these projects. Give
--the reason why you can (or can’t) update the data in this view. On successful completion of this task, remove created view.
/*
create view myView as
select p.project_id, project_name, sum(amount)
from projects p join salaries s on P.PROJECT_ID=S.PROJECT_ID
group by PROJECT_ID, PROJECT_name
*/
--we couldnt update the view because i was connected with more than one table

--6. b)For each team give an average, minimum and maximum of employees’ earnings. Do it in two steps:
/*
create view myView1 as
select t.TEAM_ID, t.TEAM_NAME, e.EMPLOYEE_ID, e.EMP_NAME, sum(amount) as sumSal
from teams t
join employees e on t.TEAM_ID=e.TEAM_ID
join salaries s on e.EMPLOYEE_ID=s.EMPLOYEE_ID
group by EMPLOYEE_ID, EMP_NAME,TEAM_ID,TEAM_NAME

create view myView2
as select m.TEAM_ID, avg(m.sumSal), min(m.sumSal), max(m.sumSal)
from myView1 m
group by TEAM_ID
*/
--7. For each team give the names of the youngest employees.
/*
select teams.TEAM_ID, teams.TEAM_NAME, employees.EMP_NAME
from teams
join employees on teams.TEAM_ID=employees.TEAM_ID
where employees.DATE_OF_BIRTH = any
(
    select min(employees.DATE_OF_BIRTH)
    from employees
    join teams on employees.TEAM_ID = teams.TEAM_ID
    group by teams.TEAM_ID
)
*/
--9 Give the names of employees who were undertaking all projects with ids in range 1-50

select e.EMP_NAME, p.PROJECT_ID
from employees e
join teams t on e.TEAM_ID=t.TEAM_ID
join projects p on t.MANAGER_ID=p.MANAGER_ID
where p.PROJECT_ID between 1 and 50