Skip to content

VNSGU BCA Sem 2: Desktop Publishing (205_02) Practical Solutions - Set E

Paper Details

  • Subject: Desktop Publishing (DRA) / Python SQLite
  • Subject Code: 205_02
  • Set: E
  • 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: Customer Database Management

Max Marks: 10

Write a Python Script to do following: 1. Create customer (cust_id, Cust_Name, City, Bill) table. 2. Insert new records in table.

1. Database Initialization

Create the SQLite database and the customer table.

Hint

cust_id should be an INTEGER PRIMARY KEY. Use REAL for the Bill amount to support decimal values.

flowchart TD
start[Start]
conn[Connect to bank.db]
exec[Execute CREATE TABLE]
done[Close Connection]

start --> conn
conn --> exec
exec --> done
View Solution & Output 
import sqlite3

# [1] Setup Database
conn = sqlite3.connect('bank.db')
cursor = conn.cursor()

cursor.execute('''
    CREATE TABLE IF NOT EXISTS customer (
        cust_id INTEGER PRIMARY KEY,
        Cust_Name TEXT NOT NULL,
        City TEXT,
        Bill REAL
    )
''')

print("Customer table created successfully.")
conn.close()

Step-by-Step Explanation: 1. Initialization: Import sqlite3 and establish a connection to 'bank.db'. 2. Logic Flow: Define the customer table schema with cust_id, Cust_Name, City, and Bill. 3. Completion: Execute the CREATE TABLE command and print a success message.

2. Inserting Records

Add at least 5 sample customer records into the table.

View Solution & Output 
import sqlite3

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

# [2] Insert multiple records
customers = [
    (1, 'Rahul Sharma', 'Surat', 45000.0),
    (2, 'Priya Patel', 'Ahmedabad', 62000.0),
    (3, 'Amit Kumar', 'Mumbai', 25000.0),
    (4, 'Sneha Gupta', 'Delhi', 75000.0),
    (5, 'Vikram Singh', 'Surat', 30000.0)
]

cursor.executemany('INSERT INTO customer VALUES (?, ?, ?, ?)', customers)
conn.commit()

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

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

2. Customer Billing Filter

Max Marks: 10

Write a Python Script to print customers whose Bill amount is more than 50,000.

Hint

Use the SELECT query with the WHERE clause. You can iterate through the results and print them in a formatted table.

flowchart TD
query[SELECT * WHERE Bill > 50000]
fetch[Fetch All]
print[Loop & Print]

query --> fetch
fetch --> print
View Solution & Output 
import sqlite3

# [1] Fetch high-bill customers
conn = sqlite3.connect('bank.db')
cursor = conn.cursor()

cursor.execute('SELECT * FROM customer WHERE Bill > 50000')
results = cursor.fetchall()

# [2] Display results
print("-" * 50)
print(f"{'ID':<5} {'Name':<20} {'City':<15} {'Bill':<10}")
print("-" * 50)

for row in results:
    print(f"{row[0]:<5} {row[1]:<20} {row[2]:<15} {row[3]:<10.2f}")

conn.close()

Step-by-Step Explanation: 1. Initialization: Connect to 'bank.db' and initialize a cursor object. 2. Logic Flow: Execute a SELECT query to filter customers with Bill > 50000 and fetch all matching records. 3. Completion: Iterate through the results and print them in a formatted table before closing the connection.

Concept Deep Dive: Logical Comparisons in SQL

The WHERE clause uses standard comparison operators (>, <, >=, <=, =, <>). When you execute this through Python, SQLite filters the data on the disk, and only the matching rows are transferred to your Python script, making it very efficient for large datasets.

Q2: Viva Preparation

Max Marks: 5

Potential Viva Questions
  1. Q: What is the difference between fetchone() and fetchall()?
  2. A: fetchone() retrieves the next single row of the result set, while fetchall() retrieves all remaining rows into a list.
  3. Q: How do you format a float to 2 decimal places in Python?
  4. A: Use f-string formatting like f"{value:.2f}".
  5. Q: Can you search for a specific City using WHERE?
  6. A: Yes, use SELECT * FROM customer WHERE City = 'Surat'.
  7. Q: How do you update a customer's bill in SQLite?
  8. A: Use the query UPDATE customer SET Bill = 55000 WHERE cust_id = 1.
  9. Q: What is the benefit of the cursor.execute() placeholders ??
  10. A: They prevent SQL injection attacks by safely escaping user-provided data.
  11. Q: How do you find the total number of customers in the table?
  12. A: Use the query SELECT COUNT(*) FROM customer.

Common Pitfalls

  • Empty Results: If no customer has a bill > 50,000, fetchall() will return an empty list []. Always check if the list is empty before printing.
  • Connection Overhead: Opening and closing connections for every single query can be slow; keep it open for related operations.

Quick Navigation

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

Last Updated: April 2025 F | Solutions |

Last Updated: April 2025