Skip to content

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

Paper Details

  • Subject: Data Analysis Using Python (DAUP)
  • Subject Code: 205_04
  • Set: D
  • 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: Product Data Analysis Pipeline

Max Marks: 20

Create a table PRODUCT in SQLite with the following columns: PRODUCT_ID, PRODUCT_NAME, CATEGORY, PRICE, RELEASE_DATE using Python. 1. Insert 10 records into the table. 2. Display records where the price is greater than 25. 3. Export product data and convert into dataframe. 4. Convert dataframe into product.csv file and save it.

1. SQLite Table Creation & Data Entry

Initialize the product catalog and populate it with sample inventory.

Hint

Define the PRODUCT table with appropriate types: INTEGER for ID and REAL for Price.

flowchart TD
start[Start]
conn[Connect to inventory.db]
tbl[Create PRODUCT 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('inventory.db')
cursor = conn.cursor()

cursor.execute('''
    CREATE TABLE IF NOT EXISTS PRODUCT (
        PRODUCT_ID INTEGER PRIMARY KEY,
        PRODUCT_NAME TEXT,
        CATEGORY TEXT,
        PRICE REAL,
        RELEASE_DATE TEXT
    )
''')

# Insert 10 records
product_data = [
    (1, 'Pen', 'Stationery', 10, '2024-01-01'),
    (2, 'Notebook', 'Stationery', 45, '2024-01-05'),
    (3, 'Eraser', 'Stationery', 5, '2024-01-10'),
    (4, 'Sharpener', 'Stationery', 8, '2024-01-12'),
    (5, 'Calculator', 'Electronics', 500, '2024-02-01'),
    (6, 'Scale', 'Stationery', 15, '2024-02-05'),
    (7, 'Compass Box', 'Stationery', 120, '2024-02-10'),
    (8, 'Marker', 'Stationery', 30, '2024-02-15'),
    (9, 'File Folder', 'Stationery', 60, '2024-03-01'),
    (10, 'Ink Bottle', 'Stationery', 20, '2024-03-05')
]

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

Step-by-Step Explanation: 1. Initialization: Connect to inventory.db and initialize the cursor for executing SQL commands. 2. Logic Flow: Create the PRODUCT table with structured columns and populate it using executemany() with 10 sample items. 3. Completion: Finalize the process by committing the records and printing a confirmation message.

2. Price Filtering Query

Fetch and display products priced above 25.

Hint

The SQL condition is WHERE PRICE > 25.

flowchart TD
q[Query: PRICE > 25]
f[Fetch Records]
p[Print Rows]

q --> f
f --> p
View Solution & Output 
# [2] Display records where price > 25
cursor.execute('SELECT * FROM PRODUCT WHERE PRICE > 25')
expensive_products = cursor.fetchall()

print("\nProducts with Price > 25:")
for row in expensive_products:
    print(row)

Step-by-Step Explanation: 1. Initialization: Prepare the database cursor to filter inventory data. 2. Logic Flow: Use a SELECT statement combined with a WHERE clause to find products where PRICE is greater than 25. 3. Completion: Execute the fetch command and print the retrieved records line-by-line.

3. Data Export to Pandas & CSV

Convert the inventory database into a CSV format for spreadsheet compatibility.

Hint

Use pd.read_sql_query() to pull the entire table into a DataFrame at once.

flowchart TD
sql[Read inventory.db]
df[Load into DataFrame]
csv[Save to product.csv]

sql --> df
df --> csv
View Solution & Output 
import pandas as pd

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

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

conn.close()

Step-by-Step Explanation: 1. Initialization: Load the pandas library for high-level data handling. 2. Logic Flow: Read the entire PRODUCT table directly from the database into a Pandas DataFrame. 3. Completion: Export the data to product.csv without including the index and close the database connection.

Concept Deep Dive: Column-wise Filtering in Pandas

While SQL filters data at the source (database level), Pandas can filter it in memory. For example, df[df['PRICE'] > 25] would give the same result as the SQL query. In large systems, SQL filtering is preferred to reduce memory usage, while in smaller scripts, Pandas is more flexible.

Q2: Viva Preparation

Max Marks: 5

Potential Viva Questions
  1. Q: What does pd.DataFrame() do?
  2. A: It creates a DataFrame object, which is a primary structure in Pandas for storing tabular data.
  3. Q: How do you check the first few rows of a DataFrame?
  4. A: Use df.head(n), where n is the number of rows (default 5).
  5. Q: What is the primary use of SQLite in Python?
  6. A: It allows lightweight, file-based relational database management without needing a separate server.
  7. Q: How do you drop a table in SQLite via Python?
  8. A: cursor.execute('DROP TABLE IF EXISTS PRODUCT').
  9. Q: How do you count the number of rows in a DataFrame?
  10. A: Use len(df) or df.shape[0].
  11. Q: What is the purpose of df.sample(n)?
  12. A: It returns a random sample of n rows from the DataFrame, which is useful for quick data inspection.

Common Pitfalls

  • Integer Division: In older versions of Python/SQL, be careful with division of prices.
  • Overwrite Warning: If you run the insert script multiple times, you might get duplicate PRODUCT_ID errors unless you clear the table first.

Quick Navigation

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

Last Updated: April 2025