Use the schema de?ned for the previous problem to answer the following queries:
a. Find all students who have taken more than ?ve classes in the Mathematics Department.
b. Represent grades as a UDT, called GradeType, with a method, value(),that returns the grade’s numeric value.
c. Write a method that, for each student, computes the average grade. This method requires the value() method that you constructed for the previous problem.
a.
SELECT S.Name
FROM Student S
WHERE 5<(SELECT count(S1.Transcript->Course)
FROM Student S1
WHERE S1.Transcript->Course.DeptId = ’MAT’
AND S1.Id = S.Id)
b.
CREATE TYPE GradeType AS (
LetterValue CHAR(2) )
METHOD value() RETURNS DECIMAL(3);
CREATE METHOD value() FOR GradeType
RETURNS DECIMAL(3)
LANGUAGE SQL
BEGIN
IF LetterValue = ’A’ THEN RETURN 4;
IF LetterValue = ’A-’ THEN RETURN 3.66;
... ... ...
END
c.
SELECT S.Name, avg(S.Transcript.Grade.value())
FROM Student S
You might also like to view...
In a resource-allocation graph, an arrow points from a ________ to a ________ to indicate that the system has allocated a specific resource of a specific type to the process.
a) square, large circle b) square, small circle c) large circle, square d) small circle, square
In a table, the intersection of an individual row and column creates a specific cell
Indicate whether the statement is true or false
Google is an Internet search engine
Indicate whether the statement is true or false
The ____ text box below the gradient ramp identifies a selected color stop's location, from left to right, on the gradient ramp.
A. Space B. Location C. Ramp D. Gradient Stop