SQL and Relational Algebra queries for three tables: student, studies and course

Question

I have a following tables, where primary keys are bolded.

student (SID, SName, semester)
studies (SID, CID)
course (CID, CName, CCode)

Write the SQL queries and Relational Algebra for the following statements.

1. Find the names of all students in 'third' semester.
2. Find the names of all courses studied by 'Ram'.
3. Find the total number of students who study 'DBMS'.

Queries that I tried:

SQL:

1. SELECT SName FROM student WHERE semester='third'

2. SELECT CNAME
   FROM student s, studies st, course c
   WHERE s.SID = st.SID AND st.SID = c.CID AND SName = 'Ram'
   

3. SELECT count(*)
   FROM student s, studies st, course c
   WHERE s.SID = st.SID AND st.SID = c.CID AND CName = 'DBMS'
   

Relational Algebra:

1. ∏ Sname (σ semester='third' (student))
2. ∏ Cname (σ Sname='Ram' (student ⋈ studies ⋈ course))
3. ρ count(*) (σ Cname='DBMS' (student ⋈ studies ⋈ course))

Are the queries that I have written correct? If they are wrong, what is the correct solution?

Answer 1

For the first question, you're right

For the second question:

SELECT CName
FROM course, studies, student
WHERE course.CID = studies.CID AND
studies.SID = student.SID AND
SName = 'Ram'

For the third question:

SELECT COUNT(SName)
FROM course, studies, student
WHERE course.CID = studies.CID AND
student.SID = studies.SID AND
CName = 'DBMS'