SQL coding:

instruction and questions is in the query.sql. Majority of the questions had been done. please complete the section that I bold in query.sql.

If possible please help me create a 'course' table that will compiled with other query.

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)

select netid, grade from student s join grades g

on s.netid = g.student_id

where section_id = 1;

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

SELECT netid, fname, lname FROM student s JOIN grades g

on s.netid = g.student_id

WHERE section_id = 9;

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

select netid, coursename from student s join courses c

on s.netid = c.student_id

where studentid = 'mp4444';

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

select fname, lname from student s join grades g

on s.netid = g.student_id

where section_id = 5;

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

select fname, lname, section_id.yrinschool from student s join grades g

on s.netid = g.student_id

where grade is NULL;

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

select netid, fname, lname from student s join grades g

on s.netid = g.student_is

where grade NOT IN ('C','D');

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

select netid, fname, lname, gpa from studnet s join courses c

on s.netid = c.studentid;

-- 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

select Top 3 * from student s join course c

on s.netid = c.studentid;