Provided below is the student database schema for queries in this recitation.
Students (sid: int, firstName: str, lastName: str, yearStarted: int)
Majors (sid: int, major: str)
o Note: a student may have more than one major. Grades (sid: int, cid: int, credits: int, grade: int)
o Note: sid, cid=foreign keys, grades: A=4, B=3, C=2, D=1, F=0.
Courses (cid: int, number: int, professor: str, major: str, year: int, semester: str)
o Note: cid is unique across semesters.
o semester is either Summer, Fall, or Spring.
o two course offerings are the same if they have same number + major.
Q1. Provide the SQL query that will print the first and last name of all sophomore students (i.e., yearStarted = 2019).
SELECT firstName, lastName
FROM students
WHERE yearStarted = 2019
Q2. Provide the SQL query that will print the first and last name of all students who have either a CS or a COE major.
Q3. Provide the SQL query that will generate the number of students who have ASTRO as their major.
Q4. Provide the SQL query that will generate the first name, last name, yearStarted, and the total number of credits for every student. You should not consider courses with a 0 grade, since these correspond to failed courses.
Q5. Provide the SQL query that will generate the professor name and how many courses that professor has taught, for every professor.
Q6. Provide the SQL query that will show all courses and the distribution of grades (i.e., how many 4s, how many 3s, how many 2s, how many 1s, and how many 0s) for each course.
Q7. Modify the query from Q6 to only consider A grades.
