SQL Queries, Joins and Updates

CREATE TABLE defines a relation’s columns, types and constraints. PRIMARY KEY identifies rows; foreign keys express valid references where supported and enforced by the database. INSERT adds rows using the declared column meanings. A schema is not the same operation as creating one data record.

Use a small known dataset to check query results. SQL dialects vary in types and some syntax, so the examples here use a simple widely supported subset. Constraints such as NOT NULL or a composite key should reflect actual requirements rather than simply making an example run.

SELECT chooses result columns and FROM identifies the input relation. WHERE filters rows using a predicate, while ORDER BY explicitly defines result ordering. Without an ordering requirement, do not assume the database will return the same visual sequence as insertion order.

A join combines matching rows using a stated condition, such as Student.StudentID=Enrolment.StudentID. Missing or incorrect conditions can multiply unrelated combinations. Use table aliases to make repeated column names unambiguous, and distinguish a relationship match from a later score filter.

COUNT, SUM, AVG, MIN and MAX summarise suitable values. GROUP BY forms groups before aggregate results are calculated. WHERE restricts source rows, while HAVING restricts groups using their resulting properties. COUNT(column) excludes null values in that column, while COUNT(*) counts rows.

Select grouping columns consistently with aggregate expressions. Filtering for high individual scores before computing a course mean answers a different question from computing the mean of all scores and then retaining courses above a threshold. Trace the source rows to prevent a plausible-looking but different result.

UPDATE changes matching stored rows, while DELETE removes matching rows. Omitting an intended WHERE can affect the entire table. Use INSERT for new rows and CREATE TABLE for schema definition; these are not interchangeable forms of SELECT.

In practical applications, bind external input using the database driver’s parameter mechanism rather than concatenating untrusted text into SQL. Placeholders are driver-specific and should not be quoted as literal text. Authorisation and transaction handling remain server responsibilities beyond the query’s visible syntax.

CREATE TABLE Student (
    StudentID INTEGER PRIMARY KEY,
    Name TEXT NOT NULL
);
CREATE TABLE Enrolment (
    StudentID INTEGER,
    CourseID INTEGER,
    Score INTEGER,
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
);
INSERT INTO Student VALUES (1, 'Ada'), (2, 'Ben');
INSERT INTO Enrolment VALUES (1, 10, 80), (1, 20, 90), (2, 10, 60);

SELECT s.Name, e.CourseID, e.Score
FROM Student AS s
JOIN Enrolment AS e ON s.StudentID = e.StudentID
WHERE e.Score >= 80
ORDER BY e.Score DESC;

SELECT CourseID, AVG(Score) AS MeanScore
FROM Enrolment
GROUP BY CourseID
HAVING AVG(Score) >= 75
ORDER BY MeanScore DESC;

UPDATE Enrolment SET Score = 82
WHERE StudentID = 1 AND CourseID = 10;

DELETE FROM Enrolment
WHERE StudentID = 2 AND CourseID = 10;

Attempt the task before opening its worked response. Record each change of state, explain the rule used, and check the result against the original requirements. Code examples labelled Python 3 use that language’s conventions; pseudocode states its own assumptions.