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

Paper Details

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

Q1: Employee Database Operations

Max Marks: 20

Write a Python script that perform following:

1. Create emp (empno, ename, deptno, salary) table in sqlite using Python.
2. Insert at least 5 records.
3. Give 500 increments to those employees whose salary < 5000.
4. Delete employee information whose ename start with 'S'.
5. Display all records.

1. Database & Table Initialization

Setup the employee management system in SQLite.

Hint

Use CREATE TABLE with appropriate types (INTEGER, TEXT, REAL).

View Solution & Output

import sqlite3

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

cursor.execute('''
    CREATE TABLE IF NOT EXISTS emp (
        empno INTEGER PRIMARY KEY,
        ename TEXT,
        deptno INTEGER,
        salary REAL
    )
''')
print("Employee table created.")

Step-by-Step Explanation:

1. Initialization: Connect to company.db and create a database cursor.
2. Logic Flow: Define and execute a SQL CREATE TABLE statement with appropriate schema for employee data.
3. Completion: Provide a visual confirmation that the table is ready for data entry.

2. Record Management (Insert, Update, Delete)

Perform bulk insertion and logical updates to the database.

Hint

• For Increments: UPDATE emp SET salary = salary + 500 WHERE salary < 5000
• For Deletion: DELETE FROM emp WHERE ename LIKE 'S%'

View Solution & Output

# [2] Insert 5 records
employees = [
    (1, 'Amit', 10, 4500),
    (2, 'Sia', 20, 6000),
    (3, 'Rahul', 10, 4800),
    (4, 'Sneha', 30, 5200),
    (5, 'Vikram', 20, 7000)
]
cursor.executemany('INSERT INTO emp VALUES (?,?,?,?)', employees)

# [3] Increment Salary
cursor.execute('UPDATE emp SET salary = salary + 500 WHERE salary < 5000')
print(f"{cursor.rowcount} records incremented.")

# [4] Delete names starting with 'S'
cursor.execute("DELETE FROM emp WHERE ename LIKE 'S%'")
print(f"{cursor.rowcount} records deleted (Names starting with S).")

conn.commit()

Step-by-Step Explanation:

1. Initialization: Define employee records as a list of tuples for batch processing.
2. Logic Flow: Execute an INSERT command followed by logical UPDATE for salary increments and DELETE for name-based filtering.
3. Completion: Commit all changes to the database and print summary counts of affected rows.

3. Record Retrieval

Fetch and display the final state of the database.

View Solution & Output

# [5] Display all records
print("\nFinal Employee List:")
cursor.execute('SELECT * FROM emp')
for row in cursor.fetchall():
    print(row)

conn.close()

Step-by-Step Explanation:

1. Initialization: Re-access the cursor for a final read operation.
2. Logic Flow: Execute a SELECT * query to fetch all remaining records from the emp table.
3. Completion: Display each row to confirm the final state of the database and close the connection.

Concept Deep Dive: The LIKE Operator

The LIKE operator in SQL is used for pattern matching. The % wildcard matches zero or more characters. So, 'S%' matches any name starting with 'S' (Sia, Sneha, Sam), while '%a' would match names ending with 'a'.

Q2: Viva Preparation

Max Marks: 5

Potential Viva Questions

1. Q: What is the purpose of the WHERE clause in an UPDATE statement?
   • A: It specifies which rows should be updated. Without it, all rows in the table would be changed.
2. Q: How do you perform partial string matching in SQLite?
   • A: Using the LIKE operator with wildcards like % (multiple chars) or _ (single char).
3. Q: What does cursor.rowcount return?
   • A: It returns the number of rows affected by the last executed SQL statement (e.g., how many rows were updated or deleted).
4. Q: What is an In-Memory Database in SQLite?
   • A: You can use sqlite3.connect(':memory:') to create a temporary database that lives only in RAM and is deleted when the script ends.
5. Q: How do you add a column to an existing table?
   • A: Use the ALTER TABLE statement, e.g., ALTER TABLE emp ADD COLUMN phone TEXT.
6. Q: Can you use Python variables directly in a SQL string?
   • A: No, it is unsafe (SQL injection). Always use placeholders ? and pass values as a tuple, e.g., cursor.execute('... WHERE id=?', (val,)).

Common Pitfalls

• Commit Forgotten: If you don't call conn.commit(), your increments and deletions will not be saved to the database file.
• Case Sensitivity in LIKE: By default, SQLite's LIKE is case-insensitive for ASCII characters, but it's good practice to be mindful of casing.

---

Quick Navigation

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

Related Solutions

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

---

Last Updated: April 2026