Express the following queries using SQL. Assume that the Student table is augmented with an additional attribute, Age, and that the Professor table has additional attributes, Age and Salary.

Question

a. Find the average age of students who received an A for some course.
b. Find the minimum age among straight A students per course .
c. Find the minimum age among straight A students per course among the students who have taken CS305 or MAT123.
d. Raise by 10% the salary of every professor who is now younger than 40 and who taught MAT123 in the spring 1997 or fall 1997 semester.
e. Find the professors whose salaries are at least 10% higher than the average salary of all professors.
f. Find all professors whose salaries are at least 10% higher than the average salary of all professors in their departments .

Answer 1

a. Note: the following is incorrect , because some students’ ages might be counted more than once:
```
SELECT AVG(S.Age)
FROM Student S, Transcript T
WHERE S.Id =T.StudId AND T.Grade = ’A’
```

The correct solution requires a subquery with a DISTINCT clause:
```
SELECT AVG(S.Age)
FROM Student S
WHERE S.Id IN
(SELECT DISTINCT S.Id
FROM Student S, Transcript T
WHERE S.Id =T.StudId AND T.Grade = ’A’ )
```

b. ```
SELECT T.CrsCode, MIN(S.Age)
FROM Student S, Transcript T
WHERE S.Id = T.StudId AND
NOT EXISTS (SELECT T.Grade
FROM Transcript T
WHERE S.Id = T.StudId AND T.Grade = A)
GROUP BY T.CrsCode
```

c. Add the following HAVING clause to the above query:
```
HAVING T.CrsCode IN (’CS305’,’MAT123’)
```

d. ```
UPDATE Professor
SET Salary = Salary * 1.1
WHERE Id IN
(SELECT P.Id
FROM Professor P, Teaching T
WHERE P.Id = T.ProfId
AND P.Age < 40
AND T.CrsCode = ’MAT123’
AND (T.Semester = ’S1997’
OR T.Semester = ’F1997’) )
```

e. ```
CREATE VIEW AvgSa l (Avgsal) AS
SELECT AVG(P.Salary)
FROM Professor P
```

The actual query that uses the above view:
```
SELECT P.Id
FROM Professor P, AvgSa l A
WHERE P.Salary > 1.1 * A.Avgsal
```

f. ```
CREATE VIEW DeptAvgSal(DeptId, Avgsal) AS
SELECT P.DeptId, AVG(P.Salary)
FROM Professor P
GROUP BY P.DeptId
```

The actual query, which uses the above view:
```
SELECT P.Id
FROM Professor P, DeptAvgSal D
WHERE P.DeptId = D.DeptId AND P.Salary > 1.1 * D.Avgsal
```