Skip to content

VNSGU BCA Sem 2: Data Analysis Using Python (205_04) Practical Solutions - 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).

flowchart TD
start[Start]
conn[Connect company.db]
tbl[Create emp Table]
done[Done]

start --> conn
conn --> tbl
tbl --> done
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%'
flowchart TD
ins[Insert 5 Records]
inc[Salary Increment +500]
del[Delete Names starting with 'S']
comm[Commit Changes]

ins --> inc
inc --> del
del --> comm
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?
  2. A: It specifies which rows should be updated. Without it, all rows in the table would be changed.
  3. Q: How do you perform partial string matching in SQLite?
  4. A: Using the LIKE operator with wildcards like % (multiple chars) or _ (single char).
  5. Q: What does cursor.rowcount return?
  6. A: It returns the number of rows affected by the last executed SQL statement (e.g., how many rows were updated or deleted).
  7. Q: What is an In-Memory Database in SQLite?
  8. A: You can use sqlite3.connect(':memory:') to create a temporary database that lives only in RAM and is deleted when the script ends.
  9. Q: How do you add a column to an existing table?
  10. A: Use the ALTER TABLE statement, e.g., ALTER TABLE emp ADD COLUMN phone TEXT.
  11. Q: Can you use Python variables directly in a SQL string?
  12. 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

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