Skip to content

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

Paper Details

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

Q1A: Employee Database Management

Max Marks: 10

Write a Python Script to do following: 1. Create Employee (Eno, Ename, Salary, Designation) table. 2. Insert minimum 5 records in the table.

1. Database Connection & Table Initialization

Establish a connection to SQLite and create the table structure.

Hint

Use the sqlite3 library. The connect() method creates the database file if it doesn't already exist.

View Solution & Output 
import sqlite3

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

cursor.execute('''
    CREATE TABLE IF NOT EXISTS Employee (
        Eno INTEGER PRIMARY KEY,
        Ename TEXT NOT NULL,
        Salary REAL,
        Designation TEXT
    )
''')

print("Employee table created successfully!")

Step-by-Step Explanation: 1. Initialization: Import sqlite3 and establish a connection to 'company.db'. 2. Logic Flow: Use a cursor to execute a CREATE TABLE statement defining the Employee schema. 3. Completion: Print a confirmation message indicating the table is ready.

2. Batch Data Insertion

Insert at least 5 records and commit the changes.

flowchart TD
A[List of Tuples]
B[executemany function]
C[Database Commit]

A --> B
B --> C
View Solution & Output 
# [2] Insert 5 records
employees = [
    (101, 'Rahul Sharma', 75000, 'Manager'),
    (102, 'Priya Patel', 65000, 'Developer'),
    (103, 'Amit Kumar', 80000, 'Team Lead'),
    (104, 'Sneha Gupta', 55000, 'Designer'),
    (105, 'Vikram Singh', 90000, 'Director')
]

cursor.executemany('INSERT INTO Employee VALUES (?, ?, ?, ?)', employees)
conn.commit()

print(f"{len(employees)} records inserted successfully!")
conn.close()

Step-by-Step Explanation: 1. Initialization: Prepare a list of tuples containing employee records. 2. Logic Flow: Use executemany() for efficient bulk insertion and conn.commit() to save changes. 3. Completion: Display the count of inserted records and close the database connection.

Q1B: High Salary Filters

Max Marks: 10

Write a Python Script to print employee details who is earning more than 60,000 as a salary.

Hint

Use the SELECT * FROM ... WHERE Salary > 60000 query. You can use the fetchall() method to retrieve all matching rows into a list.

flowchart TD
conn[Database Connect]
query[Execute Select]
fetch[Fetch All Results]
print[Iterate and Print]

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

# [1] Connect and execute filter
conn = sqlite3.connect('company.db')
cursor = conn.cursor()

cursor.execute('SELECT * FROM Employee WHERE Salary > 60000')
results = cursor.fetchall()

# [2] Display formatted results
print("-" * 50)
print(f"{'Eno':<5} {'Name':<20} {'Salary':<10}")
print("-" * 50)

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

conn.close()

Step-by-Step Explanation: 1. Initialization: Connect to 'company.db' and initialize a cursor object. 2. Logic Flow: Execute a SELECT query with a WHERE clause for Salary > 60000 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: Cursor Objects

The cursor is like a pointer to the SQL result set. It allows you to execute commands and step through data. Methods like fetchone() and fetchall() allow you to move that pointer to retrieve the actual Python data types.

Q2: Viva Preparation

Max Marks: 5

Potential Viva Questions
  1. Q: Why use conn.commit()?
  2. A: Since SQLite is transactional, changes aren't saved to the disk until you call commit.
  3. Q: What is the benefit of executemany() over a loop of execute()?
  4. A: It is significantly faster and safer for large datasets.
  5. Q: What happens if you don't call conn.close()?
  6. A: The database file might remain locked and other programs might be unable to access it.
  7. Q: What is the data type REAL in SQLite?
  8. A: It is used to store floating-point numbers (decimals), similar to FLOAT or DOUBLE in other databases.
  9. Q: How do you retrieve only the names of all employees?
  10. A: Use the query SELECT Ename FROM Employee.
  11. Q: What does the connect() function return?
  12. A: It returns a Connection object that represents the database connection.

Common Pitfalls

  • Closing too early: If you close the connection before the cursor finishes fetching, you will get an error.
  • Case Sensitivity: Field names in SQL are case-insensitive, but data strings like 'Manager' are case-sensitive.

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