Question

Write the following queries using the schema below

Class (Number, Department, Term, Title)

Student (Username, FirstName, LastName, Year)

Takes (Username, Department, Number, Term, Grade)

[clarification] In Student, Year is an integer. A student can be a 2^nd year student. In that case, Year value would be 2.

For the Takes relation:

·         Grade is NA for current semester students

·         Username is foreign key to Student

·         (Department, Number, Term) is foreign key to Class

a)       Write an SQL query that returns the Term when students with username “alex” and “bob” take at least 1 class together. Include the number of class that they have taken together in that Term.

b)      Write an SQL query that returns the usernames of the students who take exactly two classes in the “CSE” department in the term “Fall.2020”

c)       Write an SQL query that returns the title of all classes which have not been taken by any first-year student.

Answer 1

the above 3 give the sql query to the above question