COMP440- Assignment 1

For all the below queries (1-8) write the algebra expressions (chapter 2): (12.5 points each).

1- Consider the below database schema:

Professor(ssn, profname, status)

                                         Course(crscode, crsname, credits)

                                         Taught(crscode, semester, ssn)

Query1) Return the ssn of professors who have taught ‘COMP440’.

Query2) Return the ssn of professors who have taught ‘COMP440’ in ‘Spring 2020’ semester.

Query3) Return the ssn of professors who have taught both ‘COMP440’ and ‘COMP541’.

Query4) Return the codes of courses that have never been taught.

Query5) Return the names of courses that have never been taught.

Query6) Return the names of professors who ever taught ‘COMP440’.

Query7) Return the names of full professors who ever taught ‘COMP440’.

 

2- Consider the below database schema: 

                                   Student(id, name, address, status)

                                   Transcript(studid, crscode, semester, grade)

Query8) Return the names of students and the codes of courses which they got grade A.

 

Bonus: There is 10 extra credits if you write an equivalent query for at least four of the above queries (2.5 points each).