5.18

Redo Exercise 5.12 using the language of your database system for coding stored procedures and functions. Note that you are likely to have to consult the online documentation for your system as a reference, since most systems use syntax differing from the SQL standard version followed in the text. Specifically, write a procedure that takes an instructor ID as an argument and produces printed output in the format specified in Exercise 5.12, or an appropriate message if the instructor does not exist or has taught no courses. (For a simpler version of this exercise, rather than providing printed output, assume a relation with the appropriate schema and insert your answer there without worrying about testing for erroneous argument values.)

---

The code below is tested on PostgreSQL 14 and uses PL/pgSQL.

CREATE FUNCTION get_instructor_history(inst_id VARCHAR(8))
RETURNS TABLE (dept_name VARCHAR(10), course_id VARCHAR(5), course_title VARCHAR(20),
    sec_id VARCHAR(8),semester VARCHAR(6), year NUMERIC(4,0), total_enrollment INTEGER)
    AS $$

    WITH sections_taught_by_inst_id(course_id,sec_id,semester,year) AS (
        SELECT course_id,sec_id,semester,year
        FROM section NATURAL JOIN teaches
        WHERE id = inst_id
    ), sections_taught_by_inst_id_with_enrollment(course_id,sec_id,semester,year,enrollment) AS ( 
        SELECT course_id,sec_id,semester,year,COUNT(id)
        FROM sections_taught_by_inst_id NATURAL JOIN takes
        GROUP BY (course_id,sec_id,semester,year)
    )
    SELECT dept_name,course_id,title,sec_id,semester,year,enrollment AS total_enrollment
    FROM sections_taught_by_inst_id_with_enrollment NATURAL JOIN course
    ORDER BY dept_name,course_id,year,semester;
$$ LANGUAGE SQL;

To get the entire history of the instructor with id, say, 74420 we write the following query:-

SELECT * FROM get_instructor_history('74420');

The output I got:-

 dept_name | course_id |       course_title       | sec_id | semester | year | total_enrollment 
-----------+-----------+--------------------------+--------+----------+------+------------------
 Physics   | 239       | The Music of the Ramones | 1      | Fall     | 2006 |              328
 Physics   | 376       | Cost Accounting          | 1      | Fall     | 2006 |              297
 Physics   | 443       | Journalism               | 2      | Spring   | 2002 |              268
 Physics   | 443       | Journalism               | 1      | Spring   | 2010 |              298
 Physics   | 612       | Mobile Computing         | 1      | Fall     | 2007 |              285
 Physics   | 959       | Bacteriology             | 1      | Fall     | 2006 |              303
(6 rows)

My instance is using data from largeRelationsInsertFile.sql.

I know seeing “Journalism” as a course in the “Physics” department is crazy. But it’s just a mock data. :)