请求协助编写涉及4张数据库表的SQL查询语句

Got it, let's tackle this. First, let's recap the table structures you've shared—though it looks like the aregiven table definition got cut off mid-way. From what's visible, it's a junction table linking students to courses and assignments, which means we're probably missing a fourth table (likely assignment) that holds details for each assignmentcode.

Table Structures (Formalized + Guessed Fourth Table) #

Here's your provided schema, plus a reasonable assumption for the missing assignment table (adjust if your actual fourth table has different fields):

-- Course table
CREATE TABLE course (
    code char(11) PRIMARY KEY,
    name varchar(30),
    points int,
    CHECK (points >= 1 AND points <= 12)
);

-- Student table
CREATE TABLE student (
    id char(7) PRIMARY KEY,
    first_name varchar(12),
    surname varchar(30),
    bsn char(11),
    start_date date
);

-- Completed junction table (added composite PK and common grade field)
CREATE TABLE aregiven (
    studentid char(7) REFERENCES student(id),
    coursecode char(11) REFERENCES course(code),
    assignmentcode char(13) REFERENCES assignment(code),
    score int, -- Field to track assignment grades
    PRIMARY KEY (studentid, coursecode, assignmentcode) -- Composite key for unique student-course-assignment entries
);

-- Assumed fourth table: Assignment details
CREATE TABLE assignment (
    code char(13) PRIMARY KEY,
    coursecode char(11) REFERENCES course(code),
    due_date date,
    max_score int,
    description text
);

Example Queries #

Below are practical multi-table queries tailored to these tables. Adjust fields and filters based on your specific use case.

1. Fetch All Students with Their Courses and Assignments #

This query joins all four tables to show each student's enrolled courses and linked assignments, with context like course points and assignment due dates:

SELECT
    s.id AS student_id,
    CONCAT(s.first_name, ' ', s.surname) AS full_name,
    c.name AS course_name,
    c.points AS course_points,
    a.code AS assignment_code,
    a.due_date AS assignment_due_date
FROM student s
JOIN aregiven ag ON s.id = ag.studentid
JOIN course c ON ag.coursecode = c.code
JOIN assignment a ON ag.assignmentcode = a.code
-- Optional filter: Only students who started in 2024
WHERE s.start_date >= '2024-01-01'
ORDER BY c.name, s.surname;

2. Get Student Grades with Course and Assignment Context #

If your aregiven table includes a score field, this query calculates grade percentages and shows how students performed relative to each assignment's maximum score:

SELECT
    s.id,
    s.first_name,
    s.surname,
    c.name AS course,
    a.code AS assignment,
    ag.score AS obtained_score,
    a.max_score,
    -- Calculate percentage (handles division by zero edge case)
    CASE WHEN a.max_score > 0 THEN ROUND((ag.score::numeric / a.max_score) * 100, 2) ELSE NULL END AS grade_percent
FROM student s
JOIN aregiven ag ON s.id = ag.studentid
JOIN course c ON ag.coursecode = c.code
JOIN assignment a ON ag.assignmentcode = a.code
-- Optional filter: Only assignments due in the current month
WHERE EXTRACT(YEAR FROM a.due_date) = EXTRACT(YEAR FROM CURRENT_DATE)
  AND EXTRACT(MONTH FROM a.due_date) = EXTRACT(MONTH FROM CURRENT_DATE)
ORDER BY course, grade_percent DESC;

3. Find Students Who Haven't Submitted Any Assignments #

Use a LEFT JOIN to include students who don't have entries in aregiven (i.e., no assignments submitted yet):

SELECT
    s.id,
    CONCAT(s.first_name, ' ', s.surname) AS full_name,
    s.start_date
FROM student s
LEFT JOIN aregiven ag ON s.id = ag.studentid
WHERE ag.studentid IS NULL
ORDER BY s.start_date DESC;

Customization Tips #

• If your fourth table isn't assignment (e.g., it's an enrollment or submission table), swap out the join logic to match your actual schema.
• Adjust the JOIN type (e.g., LEFT JOIN, RIGHT JOIN) based on whether you need to include records that don't have matches in linked tables.
• Add GROUP BY, HAVING, or aggregate functions (like AVG()) to calculate metrics like average grades per course.

内容的提问来源于stack exchange，提问作者Mike Keehnen