VNSGU BCA Sem 2: Data Analysis Using Python (205_04) Practical Solutions - April 2025 Set A

Paper Details

• Subject: Data Analysis Using Python (DAUP)
• Subject Code: 205_04
• Set: A
• Semester: 2
• Month/Year: April 2025
• Max Marks: 25
• Time Recommendation: 45 Minutes
• Paper: View Paper | Download PDF

---

Questions & Solutions

All questions are compulsory

Q1: Student Data Analysis Pipeline

Max Marks: 20

Create a table STUDENTS in SQLite with the following columns: STUDENT_ID, NAME, COURSE, MARKS, GRADE, ENROLL_DATE using Python.

1. Insert 10 records into the table.
2. Display records where marks are greater than 75.
3. Export student data and convert it into a Data Frame.
4. Convert the Data Frame into students.csv file and save it.

1. SQLite Table Creation & Data Entry

Setup the database schema and populate it with sample records.

Hint

Use the sqlite3 module to connect and execute SQL commands. executemany() is the most efficient way to insert 10 records at once.

View Solution & Output

import sqlite3

# [1] Create database and table
conn = sqlite3.connect('school.db')
cursor = conn.cursor()

cursor.execute('''
    CREATE TABLE IF NOT EXISTS STUDENTS (
        STUDENT_ID INTEGER PRIMARY KEY,
        NAME TEXT,
        COURSE TEXT,
        MARKS INTEGER,
        GRADE TEXT,
        ENROLL_DATE TEXT
    )
''')

# Insert 10 records
students_data = [
    (1, 'Aarav', 'BCA', 85, 'A', '2024-06-01'),
    (2, 'Diya', 'BCA', 72, 'B', '2024-06-02'),
    (3, 'Krish', 'BBA', 90, 'A+', '2024-06-03'),
    (4, 'Mira', 'BCA', 65, 'C', '2024-06-04'),
    (5, 'Aryan', 'B.Com', 78, 'B+', '2024-06-05'),
    (6, 'Sia', 'BCA', 88, 'A', '2024-06-06'),
    (7, 'Rohan', 'BBA', 55, 'D', '2024-06-07'),
    (8, 'Isha', 'BCA', 95, 'A+', '2024-06-08'),
    (9, 'Karan', 'B.Sc', 82, 'A', '2024-06-09'),
    (10, 'Nehal', 'BCA', 70, 'B', '2024-06-10')
]

cursor.executemany('INSERT INTO STUDENTS VALUES (?,?,?,?,?,?)', students_data)
conn.commit()
print("Table created and 10 records inserted.")

Step-by-Step Explanation:

1. Initialization: Import sqlite3 and connect to 'school.db'.
2. Logic Flow: Create the STUDENTS table and use executemany() to insert 10 sample records.
3. Completion: Commit the transaction to save data to the disk.

2. Querying High Scorers

Fetch and display filtered records from the database.

Hint

Use SELECT * FROM STUDENTS WHERE MARKS > 75 to filter high-scoring students.

View Solution & Output

# [2] Display records where marks > 75
cursor.execute('SELECT * FROM STUDENTS WHERE MARKS > 75')
high_scorers = cursor.fetchall()

print("\nStudents with Marks > 75:")
for row in high_scorers:
    print(row)

Step-by-Step Explanation:

1. Initialization: Use the existing database cursor.
2. Logic Flow: Execute a SELECT query to filter students with MARKS > 75 and retrieve them using fetchall().
3. Completion: Iterate through the result set and print each record.

3. Data Export to Pandas & CSV

Bridge the SQLite database with the Pandas library for data analysis and file export.

Hint

pd.read_sql_query() is the easiest way to convert SQL results directly into a DataFrame.

View Solution & Output

import pandas as pd

# [3] Export to Data Frame
df = pd.read_sql_query('SELECT * FROM STUDENTS', conn)
print("\nData Frame View:")
print(df.head())

# [4] Convert to CSV
df.to_csv('students.csv', index=False)
print("\nFile 'students.csv' saved successfully.")

conn.close()

Step-by-Step Explanation:

1. Initialization: Import pandas and use pd.read_sql_query() to fetch all student data.
2. Logic Flow: Convert the SQL result set directly into a DataFrame and then use to_csv() to save it.
3. Completion: Print confirmation messages and close the database connection.

Concept Deep Dive: Why export to CSV?

While databases are powerful for storage, CSV files are the "universal language" of data science. Tools like Excel, PowerBI, and Tableau can easily read CSVs. By converting an SQLite table into a CSV via Pandas, you make the data portable and ready for advanced visualization.

Q2: Viva Preparation

Max Marks: 5

Potential Viva Questions

1. Q: What is the benefit of pd.read_sql_query over fetchall()?
   • A: read_sql_query automatically handles column names and data types, returning a structured DataFrame ready for analysis.
2. Q: What does index=False do in to_csv()?
   • A: It prevents Pandas from saving the row numbers (0, 1, 2...) as a separate column in the CSV file.
3. Q: Which SQLite method is used to save changes to the disk?
   • A: conn.commit().
4. Q: What is the difference between sqlite3.connect() and cursor()?
   • A: connect() establishes the link to the database file, while the cursor is the object used to execute actual SQL commands and fetch results.
5. Q: How do you handle dates in SQLite?
   • A: SQLite does not have a dedicated DATETIME type; dates are typically stored as TEXT (ISO 8601 strings), REAL (Julian days), or INTEGER (Unix timestamps).
6. Q: What is the purpose of the IF NOT EXISTS clause in CREATE TABLE?
   • A: It prevents the script from crashing with an error if the table already exists in the database.

Common Pitfalls

• Connection Leaks: Always ensure conn.close() is called to release database locks.
• Date Format: Standardize your dates as YYYY-MM-DD strings in SQLite for easier sorting and filtering.

---

Quick Navigation

• ← Back to DAUP Papers
• ← Back to Semester 2
• ← Back to All Past Papers

Related Solutions

Set	Link
Set A	Current Page
Set B	Solutions
Set C	Solutions
Set D	Solutions
Set E	Solutions
Set F	Solutions

---

Last Updated: April 2026