Skip to content

Week 6 - SQLite3 II

Objective
The primary objective of this assignment is to deepen your understanding of SQL JOIN operations, specifically INNER JOIN and LEFT JOIN, and to enhance your skills in writing complex WHERE conditions. This will enable you to retrieve interconnected data from multiple tables effectively.

Background
In real-world scenarios, databases often consist of multiple interrelated tables. Mastering JOIN operations is essential for querying such databases to extract meaningful information. This assignment builds upon your foundational knowledge of SQL, focusing on advanced data retrieval techniques.

Tasks

  1. Database Expansion:
    • Add a new table to your university database:
      • enrollments: Should include fields such as enrollment_id (primary key), student_id, course_id, and enrollment_date.
    • Ensure student_id and course_id in enrollments are foreign keys referencing students and courses, respectively.
  2. Data Manipulation (CRUD Operations):
    • Insert relevant records into the enrollments table to reflect students’ course enrollments.
  3. Advanced Query Writing:
    1. Write a query using INNER JOIN to list all students enrolled in a specific course.
    2. Write a query using LEFT JOIN to show all courses along with the enrolled students (if any).
    3. Write queries to demonstrate complex WHERE conditions, such as finding students enrolled in more than one course or courses with no enrollments.
  4. Analysis and Reporting:
    • Analyze the results obtained from your queries.
    • Reflect on the importance of JOIN operations in relational databases.

Deliverables

  • Upload the new database file your_last_name_university.db.
  • Upload the SQL script used for modifying the database and inserting records named your_last_name_part1.sql.
  • Upload the SQL queries written for task 3, along with the output and analysis of each query named your_last_name_part2.sql. Make sure you comment which query is which.
  • A reflection on the challenges faced and insights gained regarding JOIN operations and complex queries (put in comments section).

Evaluation Criteria

  • Correct implementation and use of JOIN operations in SQL queries.
  • Accuracy in data manipulation and relationship establishment between tables.
Upon completing this assignment, you will have a solid understanding of how to retrieve and analyze data from interconnected tables in a database, a crucial skill for any database professional. This knowledge is fundamental for dealing with complex databases in real-world applications.
  •