Tutorial 2

COMP207 Lectures 2021-10-14

This tutorial wasn’t that great. It would be much better if this was on a live database so I’ve got no idea if any of these are actually right.

Part 1

1. Average grade:

    SELECT AVG(grade) 
        FROM Enrolment
        WHERE s_id=1;
   

2. Years of COMP207 teaching:

    SELECT COUNT(year) 
        FROM CoursesInYear 
        WHERE code='COMP207';
   

3. Students in COMP207 in 2021:

    SELECT COUNT(s_id) 
        FROM Enrolment 
        WHERE code='COMP207' AND year='2021';
   

Simple Joins

1. Lecturers teach me, sorted by birthday:

    SELECT DISTINCT first_name, last_name 
        FROM Lecturers L, CoursesInYear CIY, Enrolment E
        WHERE E.code = CIY.code AND CIY.l_id = L.l_id AND E.s_id = 1 
        SORT BY L.birthday;
   

2. Names of courses you attend, sorted by name:

    SELECT name
        FROM Enrollment E, Courses C
        Where e.code=c.code
        ORDER BY name;
   

3. Names of courses Rasmus Ibsen-Jensen has taught:

    SELECT DISTINCT name
        from CourseesInYear ciy, Lecturers l, Courses c
        WHERE ciy.code=c.cody AND l.l_id=ciy.L_id
        AND first_name='Rasmus' AND Last_name='Ibsen-Jensen'
        ORDER BY c.code;
   

Unions

1. What names of students, lecturers and courses exist:

    SELECT first_name AS name
        FROM Students
        UNION
        SELECT first_name AS name
        FROM Lecturers
        UNION
        SELECT name AS name
        FROM Courses
   

2. List all lecturers and students ordered by first name and last name:

    SELECT first_name, last_name
        FROM Students
        UNION
        SELECT first_name, last_name
        FROM Lecturers
        ORDER BY first_name, last_name;