Skip to content

VNSGU BCA Sem 2: Desktop Publishing (205_02) Practical Solutions - 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.

flowchart TD
A["Import"] --> B["Connect"]
B --> C["Create Table"]
C --> D["Close"]
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.

flowchart TD
q[Query: Bill > 75000]
f[Fetch Result Set]
w[Write to Bill.csv]

q --> f
f --> w
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?
  2. A: It ensures that every order must have a product name; it cannot be empty (null).
  3. Q: What is the benefit of using with open()?
  4. A: It automatically closes the file when the block ends, even if an error occurs inside.
  5. Q: How do you handle names that are also SQL keywords?
  6. A: By enclosing them in double quotes (e.g., "order") or square brackets.
  7. Q: What does the fetchall() method return?
  8. A: It returns a list of tuples, where each tuple represents a row from the query result.
  9. Q: How can you check if a table exists before creating it?
  10. A: Use the CREATE TABLE IF NOT EXISTS syntax in SQLite.
  11. Q: Why is Total_Bill stored as REAL?
  12. 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

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

Last Updated: April 2025 F | Solutions |

Last Updated: April 2025