--1 Display the identities of classrooms, where classes in subject identified by 20 are given.
select ROOM_ID 
from schedules 
where SUBJECT_ID=20
--2 Display students’ names which start with M.
select STUDENT_NAME 
from students 
where STUDENT_NAME like 'M%'
--3 Display the names of subjects supervised by employee JOHNNY.
select s.SUBJECT_NAME 
from subjects s 
join employees e on s.SUPERVISOR_ID=e.EMPLOYEE_ID 
where e.EMP_NAME='JOHNNY'
--4 Display the identities of classrooms where classes start before 2 p.m. Put this information in order, starting with these classes, which begin at the earliest hour.
select distinct room_id 
from schedules 
where start_time<14 
order by start_time asc
--5 Display the types and the names of these subjects whose classes are given in classrooms identified by 2 or 4.
select s.SUBJECT_NAME,s.SUB_TYPE_ID 
from subjects s 
join schedules sh on s.SUBJECT_ID=sh.SUBJECT_ID 
where sh.ROOM_ID=2 or sh.ROOM_ID=4
--6 Display in alphabetical order the names of employees who give classes in classrooms which have screen and can hold more than 20 students.
select emp_name 
from employees emp 
join schedules sh on emp.EMPLOYEE_ID=sh.EMPLOYEE_ID 
join classrooms cl on sh.ROOM_ID=cl.ROOM_ID 
where ROOM_SIZE>20 AND SCREEN = 'Y' order by emp_name asc
--7 Display the names of subjects and the identities of teams whose employees give classes which start on Wednesdays at 10 a.m.
select su.SUBJECT_NAME,em.TEAM_ID 
from subjects su 
join schedules sh on su.SUBJECT_ID=sh.SUBJECT_ID 
join employees em on em.EMPLOYEE_ID=sh.EMPLOYEE_ID 
where sh.START_TIME =10 and sh.DAY_OF_WEEK='WED'      
--8 Display the names of subjects with their parent subjects.
select sy.SUBJECT_NAME, su.SUBJECT_NAME 
from subjects su 
join subjects sy on sy.SUPER_SUB_ID=su.SUBJECT_ID
--9 Display how many different subjects are assigned to employee JOHNNY.
select COUNT(*) 
from employees 
where EMP_NAME='JOHNNY' 
--10 Display the date of birth of the youngest subject supervisor employee.
select MAX(DATE_OF_BIRTH) 
from employees e 
join subjects s on e.EMPLOYEE_ID=s.SUPERVISOR_ID
--11 Display the size of the largest classroom where classes are given on Mondays from noon to 4 p.m.
select MAX(cl.ROOM_SIZE) 
from classrooms cl 
join schedules sh on cl.ROOM_ID=sh.ROOM_ID 
where (sh.START_TIME BETWEEN 12 AND 16) and sh.DAY_OF_WEEK='MON' 
--12 Display the number of classes given in each classroom from different subjects.
select ROOM_ID, COUNT(distinct SUBJECT_ID) 
from schedules 
group by ROOM_ID
--13 Display an average of grades for each subject.
select su.SUBJECT_NAME,AVG(gr.GRADE) 
from subjects su 
join grades gr on su.SUBJECT_ID=gr.SUBJECT_ID 
group by su.SUBJECT_NAME     
--14 Display the number of classes given by each subject supervisor.
select em.EMP_NAME, count(sh.EMPLOYEE_ID)
from schedules sh
join employees em on sh.EMPLOYEE_ID=em.EMPLOYEE_ID
join subjects su on su.SUBJECT_ID=sh.SUBJECT_ID
where su.SUPERVISOR_ID=em.EMPLOYEE_ID
group by em.EMP_NAME
--15 Display the names of subjects and the number of employees who give classes from these subjects.
select su.SUBJECT_NAME,COUNT(sh.EMPLOYEE_ID)
from schedules sh
join employees em on sh.EMPLOYEE_ID=em.EMPLOYEE_ID
join subjects su on su.SUBJECT_ID=sh.SUBJECT_ID
group by su.SUBJECT_NAME                
 
--16 Display the names of employees who give more than one class on Mondays. NOT WORKING
select e.EMP_NAME ,COUNT(e.EMP_NAME)
from employees e
join schedules sh on e.EMPLOYEE_ID=sh.EMPLOYEE_ID
where sh.DAY_OF_WEEK='MON'
group by e.EMP_NAME
 
--17 Display the names of subjects whose classes are given in more than one classroom NOT WORKING
select su.SUBJECT_NAME
from  schedules sh
oin subjects su on sh.SUBJECT_ID=su.SUBJECT_ID
 where 'COUNT(sh.ROOM_ID)' IN (
 select COUNT(sh.ROOM_ID)
 from schedules sh
 join subjects su on sh.SUBJECT_ID=su.SUBJECT_ID
--group by su.SUBJECT_NAME
 )>1
 
--18 Display the names of subjects whose supervisors are younger than employee JOHNNY.
select sub.SUBJECT_NAME,emp.DATE_OF_BIRTH as 'supervisor dob'
from employees emp
join subjects sub on emp.EMPLOYEE_ID=sub.SUPERVISOR_ID
where emp.DATE_OF_BIRTH > 
(select DATE_OF_BIRTH
from employees
where EMP_NAME='JOHNNY')

--19 Display the names of employees whose average of earnings is lower than employee MESUREA’s.
select emp1.EMP_NAME, AVG(sal1.AMOUNT)
from salaries sal1
join employees emp1 on sal1.EMPLOYEE_ID=emp1.EMPLOYEE_ID
group by emp1.EMP_NAME
having AVG(sal1.AMOUNT)<
(select AVG(sal.AMOUNT)
from salaries sal
join employees emp on sal.EMPLOYEE_ID=emp.EMPLOYEE_ID
where emp.EMP_NAME='MESUREA')

--20 Display the names of subjects whose classes are given by employees who belong to more teams than employees giving classes in ALGEBRA.