Question

[15] 15. We are given two tables Teaching and Transcript. Teaching table has attributes: ProfId: Integer, CrsCode: String, Semester. Transcript table has attributes: StudId: Integer, CrsCode: String, Semester: String, Grade: String. Let’s assume that a course is taught by only one professor in a particular semester. a. Write down an SQL query to retrieve ProfId, StudId, CrsCode, Semester and grade. Each tuple in the result will provide the information about a professor who taught a student in a particular course in a semester and the student received a particular grade. b. Create a View called ProfStud with attributes Prof and Stud. Here Prof is the same as the Profld from Teaching, and Stud is the same as StudId from Transcript.

Answer 1

Part a:

We have to select ProfId,CrsCode,Semester from the teaching table and StudId,Grade from transcript table.

So , SQL query to retrieve this information from the tables is as follows :

select ProfId,StudId,Teaching.CrsCode,Teaching.Semester,Grade from Teaching,Transcript where Teaching.CrsCode = Transcript.CrsCode;

Part b:

We have to create a view called ProfStud with attributes Prof and Stud using the two given table.

So, SQL query for creating this view will be : (assuming CrsCode act as Foreign Key between two tables).

create view ProfStud (Prof,Stud) as select ProfId,StudId from Teaching,Transcript where Teaching.CrsCode=Transcript.CrsCode;

If CrsCode is not the foreign key between two tables , simply remove the where clause.

i.e. now the SQL query will be :

create view ProfStud (Prof,Stud) as select ProfId,StudId from Teaching,Transcript;