SQL Querying with Python

This challenge will test your ability to interact with a relational database using Python. You will be provided with a simple in-memory SQLite database containing information about students and their courses. Your task is to write Python functions that execute specific SQL queries to retrieve and manipulate this data. This is a fundamental skill for data analysis and application development, enabling you to extract meaningful insights and manage structured information.

Problem Description

You are tasked with creating a set of Python functions that interact with an in-memory SQLite database. The database will contain two tables: students and courses.

students table:

• student_id (INTEGER, PRIMARY KEY)
• name (TEXT)
• major (TEXT)

courses table:

• course_id (INTEGER, PRIMARY KEY)
• title (TEXT)
• credits (INTEGER)

You will be given an initial set of data to populate these tables. Your Python code should define functions that perform the following SQL operations:

1. Retrieve all students: Return a list of all students, ordered by student_id.
2. Find students by major: Given a major, return all students enrolled in that major, ordered by student_id.
3. Count courses by credits: Given a number of credits, return the count of courses that have exactly that many credits.
4. Get student by ID: Given a student_id, return the student's name and major. If the student doesn't exist, return None.
5. Add a new student: Given a student_id, name, and major, add a new student to the students table. If a student with the same student_id already exists, do nothing.
6. Update student major: Given a student_id and a new major, update the major for that student. If the student doesn't exist, do nothing.
7. Delete student: Given a student_id, delete the student from the students table. If the student doesn't exist, do nothing.

Your Python solution should use the sqlite3 module to manage the database connection and execute queries.

Examples

Example 1: Input:

• students data: [(1, 'Alice', 'Computer Science'), (2, 'Bob', 'Physics'), (3, 'Charlie', 'Computer Science')]
• courses data: [(101, 'Introduction to Programming', 3), (102, 'Quantum Mechanics', 4), (103, 'Data Structures', 3)]
• Function to call: get_all_students()

Output:

[(1, 'Alice', 'Computer Science'), (2, 'Bob', 'Physics'), (3, 'Charlie', 'Computer Science')]

Explanation: The get_all_students function queries the students table and returns all rows, ordered by student_id.

Example 2: Input:

• students data: [(1, 'Alice', 'Computer Science'), (2, 'Bob', 'Physics'), (3, 'Charlie', 'Computer Science')]
• Function to call: get_students_by_major('Computer Science')

Output:

[(1, 'Alice', 'Computer Science'), (3, 'Charlie', 'Computer Science')]

Explanation: The get_students_by_major function filters students by the 'Computer Science' major and returns the matching rows, ordered by student_id.

Example 3: Input:

• courses data: [(101, 'Introduction to Programming', 3), (102, 'Quantum Mechanics', 4), (103, 'Data Structures', 3), (104, 'Linear Algebra', 3)]
• Function to call: count_courses_by_credits(3)

Output:

3

Explanation: The count_courses_by_credits function counts how many courses have exactly 3 credits.

Example 4: Input:

• students data: [(1, 'Alice', 'Computer Science'), (2, 'Bob', 'Physics')]
• Function to call: get_student_by_id(1)

Output:

('Alice', 'Computer Science')

Explanation: get_student_by_id fetches the name and major for the student with student_id 1.

Example 5: Input:

• students data: [(1, 'Alice', 'Computer Science')]
• Function to call: add_student(2, 'Bob', 'Physics')
• After calling add_student, call get_all_students()

Output:

[(1, 'Alice', 'Computer Science'), (2, 'Bob', 'Physics')]

Explanation: A new student 'Bob' is successfully added to the students table.

Example 6: Input:

• students data: [(1, 'Alice', 'Computer Science')]
• Function to call: update_student_major(1, 'Physics')
• After calling update_student_major, call get_student_by_id(1)

Output:

('Alice', 'Physics')

Explanation: Alice's major is updated from 'Computer Science' to 'Physics'.

Example 7: Input:

• students data: [(1, 'Alice', 'Computer Science'), (2, 'Bob', 'Physics')]
• Function to call: delete_student(1)
• After calling delete_student, call get_all_students()

Output:

[(2, 'Bob', 'Physics')]

Explanation: The student with student_id 1 ('Alice') is successfully deleted.

Constraints

• The sqlite3 module must be used for all database operations.
• All functions should return the data in the specified format (e.g., list of tuples, single tuple, integer, None).
• When inserting or updating, ensure that data is properly parameterized to prevent SQL injection.
• The database is in-memory, meaning it will be lost when the Python script finishes execution.
• The maximum number of students will not exceed 1000.
• The maximum number of courses will not exceed 500.
• student_id and course_id will be positive integers.

Notes

• You will need to set up the database and tables within your Python script before calling the provided functions.
• Use conn.commit() after any data modification queries (INSERT, UPDATE, DELETE) to save the changes.
• Use conn.close() when you are finished with the database connection.
• Consider how to handle potential errors, such as invalid inputs, although for this challenge, assume valid data types are provided.
• The problem statement implies that you will be provided with the initial data as Python lists of tuples. Your setup code should convert this into database rows.