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 UPPER(STATE)=UPPER(MISSISSIPPI)

/
B.SELECT COUNT(SSN) FROM STUDENT WHERE SSN IN
(
SELECT R.SSN
FROM REGISTRATION R
INNER JOIN COURSE C
ON C.COURSEID = R.COURSEID
AND C.COURSETITLE)=UPPER('DATABASE SYSTEMS')
)
;

/
C.SELECT SSN,FIRSTNAME,LASTNAME FROM STUDENT WHERE SSN IN
(
SELECT R.SSN
FROM REGISTRATION R
INNER JOIN COURSE C
ON C.COURSEID = R.COURSEID
AND C.COURSETITLE IS NULL
)
;

/

D. SELECT SSN,FIRSTNAME,LASTNAME FROM STUDENT WHERE SSN IN
(
SELECT R.SSN
FROM REGISTRATION R
INNER JOIN COURSE C
ON C.COURSEID = R.COURSEID
AND C.CREDIT>18
)
;

/

e.SELECT courseId,courseTitle FROM Course
WHERE courseId IN
(
SELECT R.courseId
FROM REGISTRATION R
INNER JOIN Student S
ON S.ssn = R.ssn
AND COUNT(courseId)>10
)

================

Please find respective quiries one by one

A.

B.

C.

D.

E.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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