Consider the following database schema for a UNIVERSITY database:
-Students (name, major, advisor)
-Courses (code, department, name, units)
-Schedule (course, term, year, taughtby)
-Enrollments (student, course, term, year, grade)
-Gradescale (lettergrade, gradepoints)
The Students relation stores information about the department/major of the student and the faculty member who is the advisor of the student.
- An example tuple: (‘JOHN WILLIAMS’, ‘COMPUTER SCIENCE’, ‘ DON KNUTH’) The Courses relation stores information about the courses offered by various departments in the university.
- An example tuple: (‘CS350’, ‘COMPUTER SCIENCE’, ‘ADVANCED ALGORITHMS’, 4)
The Schedule relation stores information about the class schedule in the university.
- An example tuple: (‘CS350’, ‘SPRING’, 1999, ‘DON KNUTH’), indicating that ‘JOHN SMITH’ taught the ‘ADVANCED ALGORITHMS’ course (with course code ‘CS350’) in the ‘SPRING’ of 1999.
The Enrollments relation stores information about students taking classes, and the grades they obtain in those classes.
- An example tuple: (‘JOHN WILLIAMS’, ‘CS350’, ‘SPRING’, 1999, ‘B’), indicating that ‘JOHN WILLIAMS’ attended the ‘ADVANCED ALGORITHMS’ course (with code ‘CS350’) in the ‘SPRING’ of 1999 and obtained a ‘B’ grade in the class.
The Gradescale relation stores information about the mapping between letter grades and grade points.
- An example tuple is as follows: (‘A’, 4), indicating that the grade scale assign 4 grade points to the ‘A’ grade. Given the above schema, write queries for the following:
1.) Find all faculty members that taught ‘MICHAEL MOORE’.
2.)Find out how ‘MICHAEL MOORE’ performed in the ‘SPRING’ term of year 2002. Specifically, for each class he has taken in that term, show the course code, course name, course units and the letter grade he obtained in the class.
3.)Find the grading pattern for each class offered in the year 2002. Specifically, for each class, show the course code, the term in which the class is offered and the average grade points (e.g., 4.5) obtained by the students of the class.
SQL: (Structured query language)
The SQL uses to store data in tabular form as well as to manipulate the data using a keyword like:
SELECT: to select tuples from the table
FROM: is the table where selects the tuple
WHERE: is used to extract only those records that fulfill the specific condition.