SQL This database has 3 tables Student(StudentID, Name, BirthDate, Address, GPA)
ID: 3587454 • Letter: S
Question
SQL
This database has 3 tables
Student(StudentID, Name, BirthDate, Address, GPA)
Course(CourseID, Name, StartDate, EndDate, Credit, Instructor)
Registration(StudentID, CourseID, LetterGrade)
Write SQL queries for following tasks:
- List all students received grade `A` in that course
- List each grade and the number of students receiving that grade in that course (i.e. how many students get an `A`, how many students get a `B`...)
- List each course and its number of registered students sorted descendingly
- List each instructor with the number of courses he/she has taught and their total credits
- List all instructors who teaches large classes (i.e. at least a course with at least 50 students)
- List each student with the number of courses he/she has registered and their total credits
Explanation / Answer
- List all students received grade `A` in that course
select Student.Name //We need only Student names
from Student, Registration
where Registration.StudentID = Student.StudentID //Join Student and Registration table on StudentID
and LetterGrade = 'A' //Filter rows on basis of grade = A condition
- List each grade and the number of students receiving that grade in that course (i.e. how many students get an `A`, how many students get a `B`...)
select LetterGrade, count(StudentID) //The column name on which we do the grouping in the 'group //by' clause must be there in the the 'select' clause. The //'count(StudentID)' counts the number of students in each group.
from Registration
group by LetterGrade //Make groups on the basis of letter grade.
- List each course and its number of registered students sorted descendingly
select Course.Name, count(StudentID), Registration.CourseID
from Course, Registration
where Registration.CourseID = Course.CourseID //Join condition
group by Registration.CourseID
- List each instructor with the number of courses he/she has taught and their total credits
select Instructor, count(CourseID), sum(Credit) //sum(Credit) calculates the sum of credits for each courseID. //(As the grouping is done on 'CourseID')
from Course
group by CourseID
- List all instructors who teaches large classes (i.e. at least a course with at least 50 students)
- List each student with the number of courses he/she has registered and their total credits
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.