Solution: Defining Tables with Primary and Foreign Keys

  Here’s the complete solution to the mini-assignment from Part 4, including:

• ✅ SQL code to create both tables

• ✅ Primary and foreign key definitions

• ✅ Sample data insertions

• ✅ A join query to display student names with their subject scores

---

🧾 Solution: Defining Tables with Primary and Foreign Keys

---

🔹 1. Create the Students Table

CREATE TABLE Students (
  StudentID INT PRIMARY KEY,
  Name VARCHAR(100),
  Class VARCHAR(20)
);

---

🔹 2. Create the Marks Table

CREATE TABLE Marks (
  MarkID INT PRIMARY KEY,
  StudentID INT,
  Subject VARCHAR(50),
  Score INT,
  FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);

🎯 This creates a relationship between Marks and Students through the StudentID.

---

📝 Inserting Sample Data

---

🔹 Insert Into Students Table

INSERT INTO Students (StudentID, Name, Class)
VALUES
  (1, 'Aisha', '10A'),
  (2, 'Ravi', '10B');

---

🔹 Insert Into Marks Table

INSERT INTO Marks (MarkID, StudentID, Subject, Score)
VALUES
  (101, 1, 'Math', 85),
  (102, 2, 'Science', 90),
  (103, 1, 'English', 88);

---

🔍 Join Query to Display Student Names with Subjects and Scores

SELECT 
  Students.Name,
  Students.Class,
  Marks.Subject,
  Marks.Score
FROM 
  Students
JOIN 
  Marks ON Students.StudentID = Marks.StudentID;

🧾 Expected Output:

| Name  | Class | Subject | Score |
|-------|-------|---------|-------|
| Aisha | 10A   | Math    | 85    |
| Ravi  | 10B   | Science | 90    |
| Aisha | 10A   | English | 88    |

---

✅ Summary

• You’ve now created two related tables.

• Used Primary and Foreign Keys properly.

• Inserted sample data.

• Ran a JOIN query to fetch meaningful, related results.

This practical exercise mirrors real-world database relationships like:

• Students & Grades

• Customers & Orders

• Employees & Departments

← Practice Quiz Next →