VNSGU BCA Sem 2: Desktop Publishing (205_02) Practical Solutions - April 2025 Set C

Paper Details

• Subject: Desktop Publishing (DRA) / Python SQLite
• Subject Code: 205_02
• Set: C
• Semester: 2
• Month/Year: April 2025
• Max Marks: 25
• Time Recommendation: 45 Minutes
• Paper: View Paper | Download PDF

---

Questions & Solutions

All questions are compulsory

Q1A: Order Database Management

Max Marks: 10

Write a Python Script to do following:

1. Create order (orderid, Prod_name, Qty, Total_Bill) table.
2. Insert minimum 5 records in table.

1. Schema Definition

Setup the SQLite database and create the order table.

Hint

In SQL, order is a reserved keyword. It is better to use backticks `order` or double quotes "order" if the database requires it, or simply name the table Orders to avoid syntax errors.

View Solution & Output

import sqlite3

# [1] Connect and setup table
conn = sqlite3.connect('sales.db')
cursor = conn.cursor()

# Using "order" with quotes as it's a reserved keyword
cursor.execute('''
    CREATE TABLE IF NOT EXISTS "order" (
        orderid INTEGER PRIMARY KEY,
        Prod_name TEXT NOT NULL,
        Qty INTEGER,
        Total_Bill REAL
    )
''')

print("Order table initialized successfully!")
conn.close()

Step-by-Step Explanation:

1. Initialization: Import sqlite3 and establish a connection to 'sales.db'.
2. Logic Flow: Define the "order" table schema with orderid, Prod_name, Qty, and Total_Bill.
3. Completion: Execute the CREATE TABLE command and print a success message.

2. Data Insertion

Insert 5 sample orders into the table.

View Solution & Output

import sqlite3

conn = sqlite3.connect('sales.db')
cursor = conn.cursor()

# [2] Insert 5 records
orders = [
    (1001, 'Smartphone', 2, 80000.0),
    (1002, 'Tablet', 1, 35000.0),
    (1003, 'Monitor', 2, 44000.0),
    (1004, 'Laptop', 1, 120000.0),
    (1005, 'Printer', 3, 51000.0)
]

cursor.executemany('INSERT INTO "order" VALUES (?, ?, ?, ?)', orders)
conn.commit()

print(f"{len(orders)} records inserted.")
conn.close()

Step-by-Step Explanation:

1. Initialization: Create a list of 5 order tuples with sample data.
2. Logic Flow: Use executemany() to insert the batch into the "order" table and commit() the transaction.
3. Completion: Print the total count of inserted records and close the database connection.

Q1B: Filtered CSV Generation

Max Marks: 10

Write a Python Script to generate Bill.csv file having details of orders whose Total_Bill amount is more than 75,000.

Hint

Use SELECT with a WHERE condition to filter the high-value orders, then use csv.writer to save them.

View Solution & Output

import sqlite3
import csv

# [1] Fetch data
conn = sqlite3.connect('sales.db')
cursor = conn.cursor()

cursor.execute('SELECT * FROM "order" WHERE Total_Bill > 75000')
data = cursor.fetchall()

# [2] Write CSV
with open('Bill.csv', 'w', newline='') as f:
    writer = csv.writer(f)
    writer.writerow(['OrderID', 'Product', 'Quantity', 'Total_Bill'])
    writer.writerows(data)

print("Bill.csv generated successfully.")
conn.close()

Step-by-Step Explanation:

1. Initialization: Import sqlite3 and csv modules and connect to 'sales.db'.
2. Logic Flow: Execute a SELECT query to filter orders with Total_Bill > 75000 and fetch the results.
3. Completion: Open 'Bill.csv', write the header and filtered rows using csv.writer, and close all resources.

Concept Deep Dive: Reserved Keywords

In SQL, certain words like ORDER, GROUP, TABLE, and SELECT are reserved for commands. If you name a table or column with these words, you must enclose them in identifiers (like double quotes in SQLite/PostgreSQL or backticks in MySQL) to tell the database you mean the name, not the command.

Q2: Viva Preparation

Max Marks: 5

Potential Viva Questions

1. Q: Why is Prod_name TEXT NOT NULL used?
   • A: It ensures that every order must have a product name; it cannot be empty (null).
2. Q: What is the benefit of using with open()?
   • A: It automatically closes the file when the block ends, even if an error occurs inside.
3. Q: How do you handle names that are also SQL keywords?
   • A: By enclosing them in double quotes (e.g., "order") or square brackets.
4. Q: What does the fetchall() method return?
   • A: It returns a list of tuples, where each tuple represents a row from the query result.
5. Q: How can you check if a table exists before creating it?
   • A: Use the CREATE TABLE IF NOT EXISTS syntax in SQLite.
6. Q: Why is Total_Bill stored as REAL?
   • A: To support decimal values for pricing and billing accuracy.

Common Pitfalls

• Syntax Error: Forgetting the quotes around "order" will cause a syntax error near "order".
• Path Issues: If you don't specify a path, Bill.csv is created in the current working directory.

---

Quick Navigation

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

Related Solutions

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

---

Last Updated: April 2026 F | Solutions |

---

Last Updated: April 2026