Name : Isha Rawool Roll No : C210
import sqlite3
import os
# Define your local path
db_path = 'expl0.db' # If it's in the same directory
# Connect or create
if not os.path.exists(db_path):
print(f"Database not found at {db_path}. Creating a new
database...")
connection = sqlite3.connect(db_path)
print("New database created.")
else:
print(f"Database found at {db_path}. Connecting...")
connection = sqlite3.connect(db_path)
cursor = connection.cursor()
Database not found at expl0.db. Creating a new database...
New database created.
# Create tables
# Table 1: Students
cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
student_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
gender TEXT CHECK(gender IN ('Male', 'Female')),
dob DATE
)
""")
<sqlite3.Cursor at 0x1cfbdd1a640>
# Table 2: Courses
cursor.execute("""
CREATE TABLE IF NOT EXISTS courses (
course_id INTEGER PRIMARY KEY,
course_name TEXT NOT NULL,
instructor_id INTEGER,
FOREIGN KEY (instructor_id) REFERENCES instructors(instructor_id)
)
""")
<sqlite3.Cursor at 0x1cfbdd1a640>
# Table 3: Instructors
cursor.execute("""
CREATE TABLE IF NOT EXISTS instructors (
instructor_id INTEGER PRIMARY KEY,
name TEXT NOT NULL
)
""")
<sqlite3.Cursor at 0x1cfbdd1a640>
# Table 4: Enrollments
cursor.execute("""
CREATE TABLE IF NOT EXISTS enrollments (
enrollment_id INTEGER PRIMARY KEY,
student_id INTEGER,
course_id INTEGER,
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
)
""")
<sqlite3.Cursor at 0x1cfbdd1a640>
# Table 5: Marks
cursor.execute("""
CREATE TABLE IF NOT EXISTS marks (
mark_id INTEGER PRIMARY KEY,
enrollment_id INTEGER,
marks_obtained INTEGER,
FOREIGN KEY (enrollment_id) REFERENCES enrollments(enrollment_id)
)
""")
connection.commit()
print("Tables created successfully")
Tables created successfully
cursor.execute("DELETE FROM students")
# Commit the changes to the database
connection.commit()
cursor.execute("INSERT INTO students (student_id, name, gender, dob)
VALUES (1, 'John Doe', 'Male', '2000-01-15')")
cursor.execute("INSERT INTO students (student_id, name, gender, dob)
VALUES (2, 'Jane Smith', 'Female', '1999-05-20')")
cursor.execute("INSERT INTO students (student_id, name, gender, dob)
VALUES (3, 'Bob Johnson', 'Male', '2001-09-10')")
cursor.execute("INSERT INTO students (student_id, name, gender, dob)
VALUES (4, 'Alice Brown', 'Female', '2002-03-25')")
cursor.execute("INSERT INTO students (student_id, name, gender, dob)
VALUES (5, 'Charlie White', 'Male', '1998-12-05')")
<sqlite3.Cursor at 0x1cfbdd1a640>
connection.commit()
print("Data inserted in students successfully")
# Insert data into instructors table
instructors_data = [
(1, 'Dr. Alan Grant'),
(2, 'Prof. Ellie Sattler'),
(3, 'Dr. Ian Malcolm')
]
for instructor in instructors_data:
cursor.execute("INSERT INTO instructors (instructor_id, name)
VALUES (?, ?)", instructor)
print("Data inserted in instructors successfully")
Data inserted in students successfully
Data inserted in instructors successfully
# Insert data into courses table
courses_data = [
(1, 'Biology 101', 1),
(2, 'Paleobotany', 2),
(3, 'Chaos Theory', 3)
]
for course in courses_data:
cursor.execute("INSERT INTO courses (course_id, course_name,
instructor_id) VALUES (?, ?, ?)", course)
print("Data inserted in courses successfully")
Data inserted in courses successfully
# Insert data into enrollments table
enrollments_data = [
(1, 1, 1),
(2, 2, 1),
(3, 3, 2),
(4, 4, 3),
(5, 5, 2)
]
for enrollment in enrollments_data:
cursor.execute("INSERT INTO enrollments (enrollment_id,
student_id, course_id) VALUES (?, ?, ?)", enrollment)
print("Data inserted in enrollments successfully")
Data inserted in enrollments successfully
# Insert data into marks table
marks_data = [
(1, 1, 85),
(2, 2, 90),
(3, 3, 78),
(4, 4, 88),
(5, 5, 82)
]
for mark in marks_data:
cursor.execute("INSERT INTO marks (mark_id, enrollment_id,
marks_obtained) VALUES (?, ?, ?)", mark)
print("Data inserted in marks successfully")
Data inserted in marks successfully
# Function to print query results
def printQueryResult(msg, query):
print(msg)
cursor.execute(query)
results = cursor.fetchall()
if not results:
print(0)
else:
for row in results:
print(row)
# Query 1: Top Performers by Course
query1 = """
SELECT course_id, student_id, marks_obtained
FROM (
SELECT c.course_id, e.student_id, m.marks_obtained,
RANK() OVER (PARTITION BY c.course_id ORDER BY m.marks_obtained
DESC) as rnk
FROM marks m
JOIN enrollments e ON m.enrollment_id = e.enrollment_id
JOIN courses c ON e.course_id = c.course_id ) ranked
WHERE rnk <= 2 """
printQueryResult("Result of Query 1", query1)
Result of Query 1
(1, 2, 90)
(1, 1, 85)
(2, 5, 82)
(2, 3, 78)
(3, 4, 88)
# Query 2: Gender-wise Average Marks
query2 = """
SELECT c.course_id, s.gender, AVG(m.marks_obtained) as avg_marks
FROM marks m
JOIN enrollments e ON m.enrollment_id = e.enrollment_id
JOIN students s ON e.student_id = s.student_id
JOIN courses c ON e.course_id = c.course_id
GROUP BY c.course_id, s.gender """
printQueryResult("\nResult of Query 2", query2)
Result of Query 2
(1, 'Female', 90.0)
(1, 'Male', 85.0)
(2, 'Male', 80.0)
(3, 'Female', 88.0)
# Query 3: Course Completion Status (View)
query3 = """
CREATE VIEW IF NOT EXISTS course_completion_status AS
SELECT e.student_id, e.course_id,
CASE
WHEN m.marks_obtained >= 40 THEN 'Pass'
ELSE 'Fail'
END AS status
FROM enrollments e
LEFT JOIN marks m ON e.enrollment_id = m.enrollment_id
"""
cursor.execute(query3)
print("\nResult of Query 3: View 'course_completion_status' created
successfully")
print("-" * 50)
Result of Query 3: View 'course_completion_status' created
successfully
--------------------------------------------------
# Query 4: Most Popular Course
query4 = """
SELECT course_id, COUNT(student_id) AS num_students
FROM enrollments
GROUP BY course_id
ORDER BY num_students DESC
LIMIT 1
"""
printQueryResult("\nResult of Query 4", query4)
Result of Query 4
(2, 2)
# Query 5: Rank Students by Total Marks
query5 = """
SELECT student_id, total_marks,
RANK() OVER (ORDER BY total_marks DESC) AS rank
FROM (
SELECT e.student_id, SUM(m.marks_obtained) AS total_marks
FROM marks m
JOIN enrollments e ON m.enrollment_id = e.enrollment_id
GROUP BY e.student_id
)
"""
printQueryResult("\nResult of Query 5", query5)
Result of Query 5
(2, 90, 1)
(4, 88, 2)
(1, 85, 3)
(5, 82, 4)
(3, 78, 5)
# Query 6: Missing Marks Entry
query6 = """
SELECT e.student_id, e.course_id
FROM enrollments e
LEFT JOIN marks m ON e.enrollment_id = m.enrollment_id
WHERE m.marks_obtained IS NULL
"""
printQueryResult("\nResult of Query 6", query6)
Result of Query 6
0
# Query 7: Course-wise Class Average and Deviation
query7 = """
SELECT e.course_id, AVG(m.marks_obtained) AS avg_marks,
MAX(m.marks_obtained) AS max_marks
FROM marks m
JOIN enrollments e ON m.enrollment_id = e.enrollment_id
GROUP BY e.course_id """
printQueryResult("\nResult of Query 7", query7)
Result of Query 7
(1, 87.5, 90)
(2, 80.0, 82)
(3, 88.0, 88)
# Query 8: Student Report Card Generator
student_id = 1 # or any ID you want
query8 = f"""
SELECT e.course_id, m.marks_obtained,
CASE WHEN m.marks_obtained >= 40 THEN 'Pass' ELSE 'Fail' END AS
status,
ROUND(
(SELECT SUM(m2.marks_obtained) * 1.0 / COUNT(*)
FROM marks m2
JOIN enrollments e2 ON m2.enrollment_id = e2.enrollment_id
WHERE e2.student_id = {student_id}), 2
) AS overall_percentage
FROM enrollments e
LEFT JOIN marks m ON e.enrollment_id = m.enrollment_id
WHERE e.student_id = {student_id} """
printQueryResult(f"\nResult of Query 8 for student_id={student_id}",
query8)
Result of Query 8 for student_id=1
(1, 85, 'Pass', 85.0)
# Query 9: Instructors with Highest Average Student Marks
query9 = """
SELECT i.instructor_id, i.name, AVG(m.marks_obtained) AS avg_marks
FROM marks m
JOIN enrollments e ON m.enrollment_id = e.enrollment_id
JOIN courses c ON e.course_id = c.course_id
JOIN instructors i ON c.instructor_id = i.instructor_id
GROUP BY i.instructor_id
ORDER BY avg_marks DESC
LIMIT 1 """
printQueryResult("\nResult of Query 9", query9)
Result of Query 9
(3, 'Dr. Ian Malcolm', 88.0)
# Query 10: Identify Duplicate Entries
query10 = """
SELECT name, dob, COUNT(*) AS duplicates
FROM students
GROUP BY name, dob
HAVING COUNT(*) > 1
"""
printQueryResult("\nResult of Query 10", query10)
# Close connection
connection.close()
Result of Query 10
0
