Insert,Update,Delete

he operations are:

1. Insert Department 60: (60, 'vlrtraining', 'kphb')
2. Update Department 40 location: Change BOSTON to 'attava'
3. Delete Department 20

Python

import sqlite3
import os

# --- Configuration ---
DB_FILE = 'company.db'

# --- Initial Data (for setup) ---
dept_data = [
    (10, 'ACCOUNTING', 'NEW YORK'), (20, 'RESEARCH', 'DALLAS'),
    (30, 'SALES', 'CHICAGO'), (40, 'OPERATIONS', 'BOSTON'),
    (50, 'MARKETING', 'SAN FRANCISCO')
]
emp_data = [
    # Initial 20 employee records (necessary to keep the script runnable)
    (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.00, None, 20), (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30),
    (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30), (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, None, 20),
    (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.00, 1400.00, 30), (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, None, 30),
    (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, None, 10), (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000.00, None, 20),
    (7839, 'KING', 'PRESIDENT', None, '1981-11-17', 5000.00, None, 10), (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00, 30),
    (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100.00, None, 20), (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, None, 30),
    (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, None, 20), (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, None, 10),
    (8001, 'JAYNE', 'SALESMAN', 7698, '1982-03-01', 1400.00, 200.00, 30), (8002, 'KELLY', 'ANALYST', 7902, '1982-03-05', 3200.00, None, 20),
    (8101, 'BROWN', 'ENGINEER', 7566, '1983-01-10', 4000.00, None, 40), (8102, 'DAVIS', 'TECHNICIAN', 7788, '1983-02-15', 3500.00, None, 40),
    (8201, 'GARCIA', 'MARKETER', 7839, '1984-06-20', 2000.00, None, 50), (8202, 'EVANS', 'SPECIALIST', 7782, '1984-07-01', 2100.00, None, 50)
]
# -----------------------------------------------------------

# Clean up previous file for a fresh start
if os.path.exists(DB_FILE):
    os.remove(DB_FILE)

# Connect to the database and get a cursor
conn = sqlite3.connect(DB_FILE)
cursor = conn.cursor()
print(f"Successfully connected to the database: {DB_FILE}")

# --- Setup: Create Tables and Insert Initial Data ---
cursor.execute('CREATE TABLE dept (deptno INTEGER PRIMARY KEY, dname TEXT NOT NULL, loc TEXT);')
cursor.execute('CREATE TABLE emp (empno INTEGER PRIMARY KEY, ename TEXT NOT NULL, job TEXT, mgr INTEGER, hiredate TEXT, sal REAL, comm REAL, deptno INTEGER, FOREIGN KEY (deptno) REFERENCES dept (deptno));')
cursor.executemany("INSERT INTO dept VALUES (?, ?, ?)", dept_data)
cursor.executemany("INSERT INTO emp VALUES (?, ?, ?, ?, ?, ?, ?, ?)", emp_data)
print("Initial tables and data inserted for setup.")

# ==========================================================
#                      DATABASE OPERATIONS
# ==========================================================

print("\nExecuting requested operations:")

# 1. Insert new record into dept table: 60, vlrtraining, kphb
NEW_DEPT_RECORD = (60, 'vlrtraining', 'kphb')
cursor.execute(
    "INSERT INTO dept VALUES (?, ?, ?)",
    NEW_DEPT_RECORD
)
print(f" -> INSERTED: Department {NEW_DEPT_RECORD[0]}")

# 2. Change dept no 40 location to attava
cursor.execute(
    "UPDATE dept SET loc = 'attava' WHERE deptno = 40"
)
print(" -> UPDATED: Department 40 location changed to 'attava'")

# 3. Delete deptno 20
cursor.execute(
    "DELETE FROM dept WHERE deptno = 20"
)
print(" -> DELETED: Department 20 removed")

# Commit all changes permanently
conn.commit()
print("All changes committed successfully.")

# ==========================================================
#                      FINAL QUERY
# ==========================================================

print("\n" + "="*50)
print("     FINAL STATE OF DEPARTMENT (dept) TABLE")
print("="*50)

# Query and display final data from the 'dept' table
cursor.execute("SELECT deptno, dname, loc FROM dept ORDER BY deptno")
dept_records = cursor.fetchall()