Nested SQL, LIKE and Schema Operations

Use Student(StudentID,Name,House), Course(CourseID,Title) and Enrolment(StudentID,CourseID), with the enrolment fields referencing the two parent tables. Our sample has Amina 1, Ben 2, Carys 3 and Dylan 4. Amina and Carys take Robotics 10; Amina and Ben take Music 20.

A query must follow the relationship keys rather than join unrelated fields with matching-looking text. INNER JOIN returns matching combinations. It can produce more than one row for a student with several enrolments; that is a consequence of the relationship, not necessarily a mistake. Specify DISTINCT if the requirement and permitted SQL need duplicate elimination.

The inner SELECT below returns StudentIDs enrolled on CourseID 10. The outer SELECT keeps student rows whose identifiers belong to that result and whose names begin with A. IN is introduced here as the membership operator connecting the nested result to the outer query. Amina is the only matching name in this dataset.

SELECT * means all selected columns. In a LIKE pattern, % matches a sequence of zero or more characters. Thus A% matches A and Amina, while %a matches names ending in a under the database’s comparison rules. Case sensitivity and escaping can vary by database; state the convention when it matters. AND and OR conditions need deliberate grouping.

INSERT adds rows. DELETE FROM Student WHERE StudentID=4 removes the matching student row while retaining the table. DROP TABLE Scratch removes the Scratch table itself. Omitting a DELETE condition can remove every row, so always inspect the intended scope in a practice database. These statements are illustrations for disposable data, not instructions to alter a live system.

Referential integrity constrains operations involving referenced rows. A database might reject deleting a parent with enrolments, or apply a declared cascade; do not assume one policy universally. The example schema uses ordinary foreign keys with no cascade. SQL dialects differ, so a valid principle still needs syntax appropriate to the stated system. Treat any extra clause introduced in an exercise according to its explanation.

CREATE TABLE Student (StudentID INTEGER PRIMARY KEY, Name TEXT, House TEXT);
CREATE TABLE Course (CourseID INTEGER PRIMARY KEY, Title TEXT);
CREATE TABLE Enrolment (
  StudentID INTEGER REFERENCES Student(StudentID),
  CourseID INTEGER REFERENCES Course(CourseID),
  PRIMARY KEY (StudentID, CourseID)
);
INSERT INTO Student VALUES
  (1, 'Amina', 'Cedar'), (2, 'Ben', 'Birch'),
  (3, 'Carys', 'Cedar'), (4, 'Dylan', 'Birch');
INSERT INTO Course VALUES (10, 'Robotics'), (20, 'Music');
INSERT INTO Enrolment VALUES (1,10), (1,20), (2,20), (3,10);

SELECT Name
FROM Student
WHERE StudentID IN (
  SELECT StudentID FROM Enrolment WHERE CourseID = 10
) AND Name LIKE 'A%';

SELECT Student.Name, Course.Title
FROM Student
INNER JOIN Enrolment ON Student.StudentID = Enrolment.StudentID
INNER JOIN Course ON Enrolment.CourseID = Course.CourseID
WHERE Course.CourseID = 10
ORDER BY Student.StudentID;

DELETE FROM Student WHERE StudentID = 4;
CREATE TABLE Scratch (Note TEXT);
DROP TABLE Scratch;

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.