SQL coding:

instruction and questions is in the query.sql

create.sql:

-- the following line causes all types, tables to be created in the solution schema by default

-- you can edit it to put them in your schema by default

SET search_path TO solution, public;

CREATE TYPE schoolyear_t AS ENUM ('FR', 'SO', 'JR', 'SR', 'GR');

CREATE TABLE student (

netid char(6) primary key,

fname varchar not null,

lname varchar not null,

yrInSchool schoolyear_t not null

);

CREATE TABLE grades (

student_id char(6) references student not null,

section_id bigint references section not null,

grade char(2),

grade_pts numeric generated always as (

CASE WHEN grade = 'A' THEN 4

WHEN grade = 'B' THEN 3

WHEN grade = 'C' THEN 2

WHEN grade = 'D' THEN 1

WHEN grade = 'F' THEN 0

END

) stored,

primary key(student_id, section_id)

);

-------------------------------------------//--------------------------------------

insert.sql:

SET search_path TO solution, public;

INSERT INTO student(netid, fname, lname, yrInSchool) VALUES

('mp4444', 'May', 'Pham', 'SR'),

('hp9999','Hal','Prince','FR'),

('rj2222','Raj','Jain','GR'),

('dc3333','Deb','Cortez','SO'),

('zs8888','Zoey','Stein','SO')

('ge0000','Gerald','Elephant','FR');

INSERT INTO grades(student_id, section_id, grade) VALUES

('mp4444', 1, 'A'),

('hp9999', 1, 'B'),

('rj2222', 3, null),

('dc3333', 4, 'B'),

('zs8888', 5, 'A'),

('mp4444', 6, 'B'),

('hp9999', 7, 'B'),

('rj2222', 8, 'A'),

('dc3333', 9, 'A'),

('zs8888', 10, 'A'),

('mp4444', 11, 'B'),

('hp9999', 11, 'B'),

('ge0000', 5, 'F' ); -- because an elephant taking a writing course would be silly

-----------------------------------------------//--------------------------------------------------------

query.sql:

SET search_path TO solution, public;

-- List netid's and grades for all students in section with id 1 (id, not section number)

-- List netid's and first and last name of students taking any section during term 9 of 2020

-- Which courses (name + grade) has student mp4444 taken?

-- Who (first name and last name) took College Writing Lab in term 5 of 2021?

-- Which student (first and last name), course name and section number (not id), term and year still have a null grade in a course?

-- Which course name, section number (not id), term and year do not have any students?

-- Which sections (course name, section number, term and year) had at least 2 students?

-- Which students (netid, first and last name) have either not taken College Writing Lab or got a D or F?

-- List all students (netid, first and last name) and their GPA's. To make the next query easier, create a view out of this query

-- This query is to display the results of the view

SELECT * FROM gpa_view;

-- List the netid, first and last name and gpa of the top 3 students