Classes SQL Solutions
- List the students in the database. (num, name)
SELECT *
FROM student
- In which courses did the student with number 4 enrolled? (ref, year, name)
SELECT ref, year, name
FROM course JOIN
enrollment USING (ref)
WHERE num = 4
- In which courses did Michael enroll? (ref, year, name)
SELECT ref, year, course.name
FROM student JOIN
enrollment USING (num) JOIN
course USING(ref)
WHERE student.name = 'Michael'
- How many students are enrolled in the 2007 edition of the SINF course? (total)
SELECT COUNT(*)
FROM enrollment JOIN
course USING(ref)
WHERE name = 'SINF' AND year = 2007
- What was the average grade in the first exam of the 2008 edition of the SINF course? (average)
SELECT ROUND(AVG(grade1), 2)
FROM enrollment JOIN
course USING(ref)
WHERE name = 'SINF' AND year = 2008
- Which students went to the first SINF exam but did not go to the second one in 2007? (num, name)
SELECT num, student.name
FROM student JOIN
enrollment USING (num) JOIN
course USING(ref)
WHERE course.name = 'SINF' AND
year = 2007 AND
grade1 IS NOT NULL AND
grade2 IS NULL
- Which students scored higher in the second exam than in the first one in the 2007 edition of the SINF course? (num, name)
SELECT num, student.name
FROM student JOIN
enrollment USING (num) JOIN
course USING(ref)
WHERE course.name = 'SINF' AND
year = 2007 AND
grade1 < grade2
- For each course, list the average grade of both exams in all editions. (ref, name, average1, average2)
SELECT course.name, ROUND(AVG(grade1),2), ROUND(AVG(grade2),2)
FROM enrollment JOIN
course USING(ref)
GROUP BY course.ref
- For each course and year, list the average grade of both exams. (ref, name, year, average1, average2)
SELECT course.name, year, ROUND(AVG(grade1),2), ROUND(AVG(grade2),2)
FROM enrollment JOIN
course USING(ref)
GROUP BY course.ref, year
- What was the higher grade, considering both exams, obtained in the SINF course? (grade)
SELECT MAX(GREATEST(grade1, grade2))
FROM enrollment JOIN
course USING(ref)
WHERE name = 'SINF'
- Who got that grade? (num, name)
SELECT num, student.name
FROM student JOIN
enrollment USING (num) JOIN
course USING(ref)
WHERE course.name = 'SINF' AND
GREATEST(grade1, grade2) IN (
SELECT MAX(GREATEST(grade1, grade2))
FROM enrollment JOIN
course USING(ref)
WHERE name = 'SINF'
)