-3

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?

philipxy
  • 14,867
  • 6
  • 39
  • 83
P_99
  • 9
  • 5
  • Comma joins are out of favour these days replace with explicit JOINs. https://stackoverflow.com/questions/20138355/whats-the-difference-between-comma-separated-joins-and-join-on-syntax-in-mysql – P.Salmon Aug 20 '22 at 06:56
  • Re "is this right": Show the steps of your work following your reference/textbook, with justification--not all terms/notations are standard & we don't know exactly what algorithm/method you are following & we want to check your work but not redo it & we need your choices when a process allows them & otherwise we can't tell you where you went right or wrong & we don't want to rewrite your reference. [ask] [help] [How do I ask and answer homework questions?](https://meta.stackoverflow.com/q/334822/3404097) Basic questions are faqs, research before considering asking & reflect research. – philipxy Aug 20 '22 at 07:58
  • Please ask 1 question. Please indent code reasonably. Please read the edit help re block & inline formats for code & quotes, lists, line breaks etc. PS [mre] PS Google 'run relational algebra online'. – philipxy Aug 20 '22 at 09:20

1 Answers1

0

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'
philipxy
  • 14,867
  • 6
  • 39
  • 83
learning
  • 608
  • 5
  • 15
  • Code-only answers are considered poor. Please explain what was wrong & why this code is correct. [answer] [Help] Please, if you have to "guess" & you're "not sure", you should be posting comments on the question to ask for clarification, not answering. Those don't belong in any answer. – philipxy Aug 20 '22 at 13:45