Question

Given the following relational database schema:

Student = (SSN, Name, Major)

Course = ( CourseNumber , CourseTitle, NumberOfUnits, RoomNumber, DayTime), where DayTime is of the form MW 1:0-2:00.

Enrollment = (SSN , CourseNumber, Grade)

Express the following queries using AQL statements with minimum number of tables and operations.

List the name of every student whose name starts with the letter S and ends with the letter M, and has earned the grade B in at least one course.

Answer 1

The following are the table defined with the specified schema with some data

student table


course table

NB:I can't understad how to represent The day time format.Please make sure that the table you have create includes the day time data as you like

enrolment table


SELECT S.Name FROM Students AS S INNER JOIN Enrollment AS E ON E.SSN = S.SSN GROUP BY S.SSN, S.Name IN (SELECT GradeFROM EnrollmentWHERE Grade ='B')HAVING COUNT(*) > 1 GROUP BY S.Name, S.SSN WHERE S.Name LIKE ('S%') and S.Name LIKE('%M');

In the above SQL statement,

S% and %M are the wild card selection that select a string that starts with S and ends with M respectively

The students table and enrollment table are enough for the above SQL query as both the tabkes have the required  information to get the data.