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 easch course with more than 10 students enrolled.

Explanation / Answer

a.select * from students where staet='Mississppi'

b.select count(*) from students s,course c,Registration r where s.ssn=r.ssn and r.courseId=c.courseId

c. .select ssn, firstName, lastName from students s,course c,Registration r where s.ssn=r.ssn and r.courseId=(+)c.courseId

d.more info need

e. .select courseId, courseTitle, count(ssn) from students s,course c,Registration r where s.ssn=r.ssn and r.courseId=c.courseId group by courseId haveing count(*)>=10

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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