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

In this question there are 3 tables.

• CLASS having the entities number, department, term and title.
• STUDENT having the entities Username, FirstName, LastName, Year.
• TAKES having the entities Username, Department Number, Term, Grade.

Number is the primary key of CLASS table.

Username is a primary key of STUDENT table.

Username is the primary key of TAKES table and the foreign key of TAKES table are Department, Number, Term.

Ans.a)

Select Term, Sum(Number of Class together by Bob and Alex) from(Select Username,Count(Number) AS "Number of Class together by Bob and Alex" from TAKES where Username="Alex" AND Username="Bob" AND Number>=1 GROUP BY Username);

To fetch the term which are having student name Alex and Bob that are having at least one number of class. So to fetch this data three conditions are applied in which username is Alex , username is Bob and Number>=1. So that when all three conditions are satisfied then it retrieve the term.

Another in this query is to include the total number of classes that has to be taken together in that particular first term so to fetch that particular term in which Bob and Alex have at least one class together then count function is used to count the total number of classes by both these username and then sum is depicted in a column name "Number of Class together by Bob and Alex", for this sub selective query has to be written.

Ans.b)

Select Username from TAKES where Number=2 AND Department="CSE" AND Term="Fall.2020";

To fetch the data of the username who have number of classes exactly two and having the department CSE and the term name "Fall.2020". In this only and operator are used in order to satisfy all the three conditions that class number is equals to two, department equals to CSE and term is equals to fall.2020.

By simply using AND operator username are fetched.

Ans.c)

Select Title from CLASS NATURAL JOIN TAKES NATURAL JOIN STUDENT where Year!=1;

To fetch the title of those classes where student year is not equals to 2.

For this information, first of all JOIN clause is used in order to join all three tables by their respective primary key and foreign key.

First CLASS table is join with TAKES table with Number as a foreign key and then TAKES table join with STUDENT table by the username as foreign key where year of student table is not equals to 1.