Question

There are four tables in the database.

1. students (sno, sname, sgender, sbirthday, class)

- sno: student number

- sname: student name

- sgender: male or female

- sbirthday: date of birth

- class: class number

- primary key: sno

2. courses (cno, cname, tno)

- cno: course number

- cname: course name

- tno: teacher number

- primary key, cno, tno

3. scores (sno, cno, grade)

- sno: student number

- cno: course number

- grade: grade

- primary key, sno, cno

4. teachers (tno, tname, tgender, tbirthday, title, department)

- tno: teacher number

- tname: teacher name

- tgender: teacher gender

- tbirthday: date of birth

- title: title of the teacher, e.g. professor, lecture, or TA

- department: department name, e.g. CS, EE.

Question 1: In the score table, find the student number that has all the grades in between 90 and 70.

Question 2: For all the courses that took by class 15033, calculate the average grade.

Question 3: Find the class number that has at least two male students.

Question 4: Find the teacher's name in CS and EE department, where they have different title. Return both name and title.

Question 5: Find the students, who took the course number "3-105" and have earned a grade, at least, higher than the students who took "3- 245" course. Return the results in a descending order of grade.

Question 6: Find the students, who took more than 1 course, and return the students' names that is not the one with highest grade.

Question 7: For each course, find the students who earned a grade less than the average grade of this course.

Answer 1