Consider the following schemas: Student(ssn, firstName, lastName, streetAddress,

Question

Consider the following schemas:

Student(ssn, firstName, lastName, streetAddress, city, state, zipCode)
Registration(ssn, courseId, credit)
Course(courseId, courseTitle, credit)

Write both SQL and relational algebra statements for each of the following queries:

a) List all the students from the state of Mississippi

b) The total number of students who are taking the course with title "Database Systems?

c) List ssn, firstName, lastName of all the students who are not taking any course

d) List ssn, firstName, lastName of all the students who are taking 18 or more hours

e) List the courseId, courseTitle, the total number of students in each course with more than 10 students enrolled

Explanation / Answer

a) List all the students from the state of Mississippi

select (firstName,' ',lastName) as name
from Student
where state='mississippi';

b) The total number of students who are taking the course with
title "Database Systems?

select count(*)
from Registration as A
inner join Course as B
on A.courseId=B.courseId
where B.courseTitle ='Database Systems';

c) List ssn, firstName, lastName of all the students
who are not taking any course.

select ssn, firstName, lastName
from Student as a
where ssn not in (select ssn from registration);

d) List ssn, firstName, lastName of all the students
who are taking 18 or more hours.

select A.ssn, A.firstName, A.lastName
from Student as A
inner join Registration as B
on A.ssn=B.ssn
where credit>=18;

e) List the courseId, courseTitle, the total number of students
in each course with more than 10 students enrolled

select A.courseId, A.courseTitle, count(B.ssn) as noofstudents
from Course as A
inner join Registration as B
on A.courseId=B.courseId
group by A.courseId,A.courseTitle
Having noofstudents>1

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.