1. --1 Display the identities of classrooms, where classes in subject identified by 20 are given.
2. select ROOM_ID
3. from schedules
4. where SUBJECT_ID=20
5. --2 Display students’ names which start with M.
6. select STUDENT_NAME
7. from students
8. where STUDENT_NAME like 'M%'
9. --3 Display the names of subjects supervised by employee JOHNNY.
10. select s.SUBJECT_NAME
11. from subjects s
12. join employees e on s.SUPERVISOR_ID=e.EMPLOYEE_ID
13. where e.EMP_NAME='JOHNNY'
14. --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.
15. select distinct room_id
16. from schedules
17. where start_time<14
18. order by start_time asc
19. --5 Display the types and the names of these subjects whose classes are given in classrooms identified by 2 or 4.
20. select s.SUBJECT_NAME,s.SUB_TYPE_ID
21. from subjects s
22. join schedules sh on s.SUBJECT_ID=sh.SUBJECT_ID
23. where sh.ROOM_ID=2 or sh.ROOM_ID=4
24. --6 Display in alphabetical order the names of employees who give classes in classrooms which have screen and can hold more than 20 students.
25. select emp_name
26. from employees emp
27. join schedules sh on emp.EMPLOYEE_ID=sh.EMPLOYEE_ID
28. join classrooms cl on sh.ROOM_ID=cl.ROOM_ID
29. where ROOM_SIZE>20 AND SCREEN = 'Y' order by emp_name asc
30. --7 Display the names of subjects and the identities of teams whose employees give classes which start on Wednesdays at 10 a.m.
31. select su.SUBJECT_NAME,em.TEAM_ID
32. from subjects su
33. join schedules sh on su.SUBJECT_ID=sh.SUBJECT_ID
34. join employees em on em.EMPLOYEE_ID=sh.EMPLOYEE_ID
35. where sh.START_TIME =10 and sh.DAY_OF_WEEK='WED'      
36. --8 Display the names of subjects with their parent subjects.
37. select sy.SUBJECT_NAME, su.SUBJECT_NAME
38. from subjects su
39. join subjects sy on sy.SUPER_SUB_ID=su.SUBJECT_ID
40. --9 Display how many different subjects are assigned to employee JOHNNY.
41. select COUNT(*)
42. from employees
43. where EMP_NAME='JOHNNY'
44. --10 Display the date of birth of the youngest subject supervisor employee.
45. select MAX(DATE_OF_BIRTH)
46. from employees e
47. join subjects s on e.EMPLOYEE_ID=s.SUPERVISOR_ID
48. --11 Display the size of the largest classroom where classes are given on Mondays from noon to 4 p.m.
49. select MAX(cl.ROOM_SIZE)
50. from classrooms cl
51. join schedules sh on cl.ROOM_ID=sh.ROOM_ID
52. where (sh.START_TIME BETWEEN 12 AND 16) and sh.DAY_OF_WEEK='MON'
53. --12 Display the number of classes given in each classroom from different subjects.
54. select ROOM_ID, COUNT(distinct SUBJECT_ID)
55. from schedules
56. group by ROOM_ID
57. --13 Display an average of grades for each subject.
58. select su.SUBJECT_NAME,AVG(gr.GRADE)
59. from subjects su
60. join grades gr on su.SUBJECT_ID=gr.SUBJECT_ID
61. group by su.SUBJECT_NAME    
62. --14 Display the number of classes given by each subject supervisor.
63. select em.EMP_NAME, count(sh.EMPLOYEE_ID)
64. from schedules sh
65. join employees em on sh.EMPLOYEE_ID=em.EMPLOYEE_ID
66. join subjects su on su.SUBJECT_ID=sh.SUBJECT_ID
67. where su.SUPERVISOR_ID=em.EMPLOYEE_ID
68. group by em.EMP_NAME
69. --15 Display the names of subjects and the number of employees who give classes from these subjects.
70. select su.SUBJECT_NAME,COUNT(sh.EMPLOYEE_ID)
71. from schedules sh
72. join employees em on sh.EMPLOYEE_ID=em.EMPLOYEE_ID
73. join subjects su on su.SUBJECT_ID=sh.SUBJECT_ID
74. group by su.SUBJECT_NAME                
75.  
76. --16 Display the names of employees who give more than one class on Mondays. NOT WORKING
77. select e.EMP_NAME ,COUNT(e.EMP_NAME)
78. from employees e
79. join schedules sh on e.EMPLOYEE_ID=sh.EMPLOYEE_ID
80. where sh.DAY_OF_WEEK='MON'
81. group by e.EMP_NAME
82.  
83. --17 Display the names of subjects whose classes are given in more than one classroom NOT WORKING
84. select su.SUBJECT_NAME
85. from  schedules sh
86. oin subjects su on sh.SUBJECT_ID=su.SUBJECT_ID
87.  where 'COUNT(sh.ROOM_ID)' IN (
88.  select COUNT(sh.ROOM_ID)
89.  from schedules sh
90.  join subjects su on sh.SUBJECT_ID=su.SUBJECT_ID
91. --group by su.SUBJECT_NAME
92.  )>1
93.  
94. --18 Display the names of subjects whose supervisors are younger than employee JOHNNY.
95. select sub.SUBJECT_NAME,emp.DATE_OF_BIRTH as 'supervisor dob'
96. from employees emp
97. join subjects sub on emp.EMPLOYEE_ID=sub.SUPERVISOR_ID
98. where emp.DATE_OF_BIRTH >
99. (select DATE_OF_BIRTH
100. from employees
101. where EMP_NAME='JOHNNY')
102.  
103. --19 Display the names of employees whose average of earnings is lower than employee MESUREA’s.
104. select emp1.EMP_NAME, AVG(sal1.AMOUNT)
105. from salaries sal1
106. join employees emp1 on sal1.EMPLOYEE_ID=emp1.EMPLOYEE_ID
107. group by emp1.EMP_NAME
108. having AVG(sal1.AMOUNT)<
109. (select AVG(sal.AMOUNT)
110. from salaries sal
111. join employees emp on sal.EMPLOYEE_ID=emp.EMPLOYEE_ID
112. where emp.EMP_NAME='MESUREA')
113.  
114. --20 Display the names of subjects whose classes are given by employees who belong to more teams than employees giving classes in ALGEBRA.
115.