--L4.EX1
SELECT p.nume , f.nume as facultate
 FROM Profesor p, Facultate f
 WHERE p.fid = f.fid;

--L4.EX2
SELECT cu.titlu, s.nr_locuri
 FROM Sala s INNER JOIN Curs cu
 ON s.cods = cu.sala
 WHERE cu.ora >= 12;

--L4.EX3
SELECT cu.titlu, s.etaj
 FROM Sala s INNER JOIN Curs cu
 ON s.cods = cu.sala
 WHERE s.etaj = 1;

--L4.EX4
SELECT cu.titlu, p.nume
 FROM curs cu INNER JOIN profesor p
 ON cu.pid = p.pid
 -- where (...)

--L4.EX5
SELECT stu.nume, cu.titlu, cu.sala
 FROM student stu INNER JOIN (
     contract con INNER JOIN curs cu
     ON con.cid = cu.cid
 )
 ON stu.sid = con.sid

--L4.EX6
SELECT stu.sid, stu.nume
 FROM student stu FULL OUTER JOIN contract con
 ON stu.sid = con.sid
 WHERE con.sid IS NULL
 ORDER BY stu.nume, stu.an

--L4.EX7
SELECT stu.sid, stu.nume
 FROM student stu FULL OUTER JOIN contract con
 ON stu.sid = con.sid
 WHERE con.sid IS NULL
 ORDER BY stu.nume, stu.an

--L4.EX8
SELECT stu.nume, cu.zi
 FROM student stu INNER JOIN (
     contract con INNER JOIN curs cu
     ON con.cid = cu.cid
 )
  ON stu.sid = con.sid
  WHERE cu.zi = 'Luni'

--L4.EX9
SELECT stu.nume
 FROM (( student stu INNER JOIN contract con ON stu.sid = con.sid) INNER JOIN curs cu ON cu.cid = con.cid)  INNER JOIN sala s ON cu.sala = s.cods
 WHERE s.nr_locuri<200
 ORDER BY   stu.an,
            SUBSTR( NUME, 1, INSTR( NUME, ' ', 1, 1 ) ),
    		SUBSTR( NUME, INSTR( NUME, ' ', 1, 1 ) );