SQL SELECT: match a PK to a tuple and from value of that tuple select others that have same value to that tuple?

Answer 1

Classes that sID = '03' is taking:

Select cID from StudentsInClasses where sID = '03'

Students who at some time took the same class as sID = '03' (including sID = '03'):

Select 
    sID
From
    StudentsInClasses
Where
    cID in (Select cID from StudentsInClasses where sID = '03')

Answer 2

This gets you the cID(s) where sID is '03':

select cID from table where sID = '03'

One way to get other sID(s) with the same cID, is to put the above query into a subquery:

select * from table
where cID in (
    select cID from table where sID = '03'
)

Answer 3

 SELECT cId, SId
 from table
 WHERE (cId, SID) IN (Select CId, SId from Table
                 WHERE sId = '03')

Result will be:

01 L32D
02 L32D
03 L32D

Answer 4

Use:

SELECT t.cid, 
            t.sid
  FROM TABLE t
  JOIN TABLE x ON x.cid = t.cid
                    AND x.sid = '03'

It's more readable as IN:

SELECT t.cid,
            t.sid
  FROM TABLE t
 WHERE t.cid IN (SELECT x.cid
                          FROM TABLE x
                         WHERE x.sid = '03')

Using EXISTS:

SELECT t.cid,
            t.sid
    FROM TABLE t
WHERE EXISTS(SELECT NULL
                        FROM TABLE x
                      WHERE x.cid = t.cid
                          AND x.sid = '03')