ENROL (ENo, C_Id, Class) – ENo represents student number
TEACH (Prof, C_Id, Class) – C_Id represents course number
ADVISE (Prof, ENo) – Prof is project guide of ENo (Student’s enrol_no)
PRE_REQ (C_Id, Pre_C_Id) – Pre_C_Id is prerequisite course
GRADES (ENo, C_Id, Grade, Year)
STUDENT (ENo, SName) – SName is student name
Write SQL statements for the following :
i) List all students whose project guide is Prof.Murthy.
Answer : – SELECT STUDENT.ENo, SName FROM STUDENT, ADVISE WHERE STUDENT.ENo=ADVISE.ENo AND Prof=’Murthy’;
ii) List the grade for the student with ENo=1234
Answer : – SELECT C_Id, Grade FROM GRADES WHERE ENo=1234;
iii) List those professors who teach more than one class.
Answer : – SELECT DISTINCT T1.Prof FROM TEACH AS T1 WHERE (SELECT COUNT(T2.Class) FROM TEACH AS T2 WHERE T1.Prof=T2.Prof) > 1;
iv) List all the student names and ENo’s who got Grade A in the year 2018 in C_Id= 100.
Answer : – SELECT STUDENT.ENo, SName FROM STUDENT, GRADES WHERE STUDENT.ENo=GRADES.ENo AND Grade=’A’ AND Year=2018 AND C_Id=100;
v) List all the students who has taken the pre-requisite course Pre_C_Id= 001.
Answer : – SELECT STUDENT.ENo, SName FROM STUDENT, ENROL, PRE_REQ WHERE STUDENT.ENo=ENROL.ENo AND ENROL.C_Id=PRE_REQ.C_Id AND Pre_C_Id=001;
