Problem no 6

q6.sql

-- correct but not for mysql
SELECT s.sid,s.lname, s.fname 
FROM course c, student s, enrolled e
where c.cid=e.cid and e.sid=s.sid and c.`name`='network'
Intersect (
SELECT s.sid,s.lname, s.fname 
FROM course c, student s, enrolled e
where c.cid=e.cid and e.sid=s.sid and c.`name`='database');

-- version 1
SELECT s.sid,s.lname, s.fname 
FROM course c, student s, enrolled e
where c.cid=e.cid and e.sid=s.sid and c.`name`='network'
and EXISTS(
SELECT *
FROM course c1, student s1, enrolled e1
where c1.cid=e1.cid and e1.sid=s1.sid and c1.`name`='database'
and s.sid=s1.sid

);

-- version 2
 SELECT 
 s.fname, s.lname
 FROM STUDENT s1,  enrolled e1, course c1, student s, enrolled e, course c
 where s1.sid=e1.sid and e1.cid=c1.cid and s.sid=e.sid and e.cid=c.cid 
 and s1.sid=s.sid
AND ( c1.`name`='database' AND c.`name`='network');