Skip to content

VNSGU BCA Sem 2: Data Analysis Using Python (205_04) Practical Solutions - 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.

flowchart TD
start[Start]
conn[Connect to school.db]
tbl[Create STUDENTS Table]
ins[Bulk Insert 10 Records]
done[Commit & Close]

start --> conn
conn --> tbl
tbl --> ins
ins --> done
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.

flowchart TD
q[Query: MARKS > 75]
f[Fetch Result Set]
p[Print Rows]

q --> f
f --> p
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.

flowchart TD
sql[Fetch All SQL Data]
df[Convert to Pandas DF]
csv[Save to students.csv]

sql --> df
df --> csv
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()?
  2. A: read_sql_query automatically handles column names and data types, returning a structured DataFrame ready for analysis.
  3. Q: What does index=False do in to_csv()?
  4. A: It prevents Pandas from saving the row numbers (0, 1, 2...) as a separate column in the CSV file.
  5. Q: Which SQLite method is used to save changes to the disk?
  6. A: conn.commit().
  7. Q: What is the difference between sqlite3.connect() and cursor()?
  8. A: connect() establishes the link to the database file, while the cursor is the object used to execute actual SQL commands and fetch results.
  9. Q: How do you handle dates in SQLite?
  10. 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).
  11. Q: What is the purpose of the IF NOT EXISTS clause in CREATE TABLE?
  12. 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

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

Last Updated: April 2025