Lab 9 · SQL Database Lab: Create Tables and Run Queries

🎯 Objective

Go from database theory to working SQL: model data, enforce relationships, and query answers instead of scrolling rows.

🧩 Skills You Will Practice

• Table design and primary/foreign keys
• CREATE and INSERT
• SELECT with WHERE and ORDER BY
• JOIN and GROUP BY

🧰 What You Need

• DB Browser for SQLite (free) or an online SQLite playground
• A notebook for the schema sketch
• Sample data you invent (no real personal data)

📋 Step-by-Step Tasks

1. Sketch the schema on paper first: students (id, name, semester), courses (id, title, credit_hours), enrolments (student_id, course_id, marks). Mark primary keys and foreign keys and justify the third table.
2. Write CREATE TABLE statements with correct types: INTEGER PRIMARY KEY, TEXT, and FOREIGN KEY references. Run them and inspect the created schema.
3. Insert at least 6 students, 4 courses and 10 enrolments with varied semesters and marks. Use one INSERT per row at first so errors are easy to locate.
4. Query 1–2: list all students in semester 3 ordered by name; list courses with credit_hours above 2 using WHERE.
5. Query 3: JOIN all three tables to show student name, course title and marks in one result. This single query is why relational databases exist.
6. Query 4–5: use GROUP BY to count students per semester, and AVG(marks) per course. Add a HAVING clause to show only courses with an average below 60.
7. Break it on purpose in a copy: try inserting an enrolment for a non-existent student, read the foreign-key error, and write one sentence on what the constraint protected.

✅ Expected Result / Deliverable

A saved .sql file plus a results sheet: schema, 5 working queries with their outputs, and the foreign-key error explained — a complete mini database portfolio piece.

⚠️ Common Mistakes

• Designing one giant table with course names repeated on every student row
• Using real names or ID numbers of real people as sample data
• Writing queries without checking the result actually answers the question