Consider the following schemas: Student(ssn, firstName, lastName, streetAddress,
ID: 3571709 • Letter: C
Question
Consider the following schemas: Student(ssn, firstName, lastName, streetAddress, city, state, zipcode) Registration(ssn, coursed, credit) Course (coursed, courseTitle, creit) Write both SQL and relational algebra statements for each of the following queries. List all the students from the state of Mississippi. The total number of students who are taking the course with title 'Database Systems'? List ssn, firstName, lastName of all the students who are not taking any course. List ssn, firstName, lastName of all the students who are taking 18 or more hours.. List the coursed, courseTitle, the total number of students in each course with more than 10 students enrolled.Explanation / Answer
Student(ssn, firstName, lastName, streetAddress, city, state, zipcode)
Registration(ssn, courseId, credit)
Course(courseId, courseTitle, credit)
a. List all the students from the state of Mississippi.
SELECT * from Student WHERE state = 'Mississippi';
b. The total number of students who are taking the course with title 'Database Systems'?
SELECT courseTitle, count(*) FROM Student S, Registration R, Course C WHERE S.ssn = R.ssn
and C.courseId = R.courseId and courseTitle = 'Database Systems' GROUP BY courseTitle;
c. List ssn, firstName, lastName of all the students who are not taking any course?
SELECT S.ssn, S.firstName, S.lastName FROM Student S WHERE S.ssn NOT IN (SELECT ssn FROM Registrations);
d. List ssn, firstName, lastName of all the students who are taking 18 or more hours?
SELECT ssn, firstName, lastName FROM Student, Registration WHERE Student.ssn = Registration.ssn GROUP BY credit HAVING sum(credit) >= 18;