practtiacal assgimennt 2
### ✅ Q-1: Trigger to block INSERT, UPDATE, DELETE on RESULT

sql
CREATE TABLE RESULT(rno INT, name TEXT, rank INT);

CREATE TRIGGER no_insert_result
BEFORE INSERT ON RESULT
BEGIN
  SELECT RAISE(ABORT, 'Insert not allowed on RESULT');
END;

CREATE TRIGGER no_update_result
BEFORE UPDATE ON RESULT
BEGIN
  SELECT RAISE(ABORT, 'Update not allowed on RESULT');
END;

CREATE TRIGGER no_delete_result
BEFORE DELETE ON RESULT
BEGIN
  SELECT RAISE(ABORT, 'Delete not allowed on RESULT');
END;


---

### ✅ Q-2: Prevent salary update in March (EMP table)

sql
CREATE TABLE EMP(eno INT, name TEXT, salary REAL);

CREATE TRIGGER prevent_march_salary_update
BEFORE UPDATE OF salary ON EMP
WHEN strftime('%m', 'now') = '03'
BEGIN
  SELECT RAISE(ABORT, 'Salary update not allowed in March');
END;


---

### ✅ Q-3: Log INSERT, UPDATE, DELETE in ELOG for EMP

sql
CREATE TABLE ELOG(eno INT, operation TEXT, date_and_time TEXT);

CREATE TRIGGER log_insert
AFTER INSERT ON EMP
BEGIN
  INSERT INTO ELOG VALUES (NEW.eno, 'INSERT', datetime('now'));
END;

CREATE TRIGGER log_update
AFTER UPDATE ON EMP
BEGIN
  INSERT INTO ELOG VALUES (NEW.eno, 'UPDATE', datetime('now'));
END;

CREATE TRIGGER log_delete
AFTER DELETE ON EMP
BEGIN
  INSERT INTO ELOG VALUES (OLD.eno, 'DELETE', datetime('now'));
END;


---

### ✅ Q-4: Trigger to prevent over-withdrawal from ACCOUNT

sql
CREATE TABLE ACCOUNT(ano INT, balance REAL);

CREATE TRIGGER prevent_over_withdrawal
BEFORE UPDATE OF balance ON ACCOUNT
WHEN NEW.balance < 0
BEGIN
  SELECT RAISE(ABORT, 'Withdrawal exceeds balance');
END;


---

### ✅ Q-5: Dump STUDENT table data into text file

In SQLite CLI:

bash
sqlite3 mydb1.db
.headers on
.mode list
.output student_data.txt
SELECT * FROM STUDENT;
.output stdout


---

### ✅ Q-6: Dump STUDENT table structure into word file

bash
sqlite3 mydb1.db
.schema STUDENT > student_structure.doc


---

### ✅ Q-7: Dump entire DB into SQL file

bash
sqlite3 mydb1.db .dump > mydb1_dump.sql


---

### ✅ Q-8: Export EMP table to CSV

bash
sqlite3 mydb1.db
.headers on
.mode csv
.output emp_data.csv
SELECT * FROM EMP;
.output stdout


---

### ✅ Q-9: Import CSV to STUDENT(rno, s1, s2, s3)

Create table first:

sql
CREATE TABLE STUDENT(rno INT, s1 INT, s2 INT, s3 INT);


Then in SQLite CLI:

bash
.mode csv
.separator ,
.import student.csv STUDENT


---

### ✅ Q-10: calc.py module with 4 functions

Create file: calc.py

python
def add(n1, n2):
    return n1 + n2

def sub(n1, n2):
    return n1 - n2

def mul(n1, n2):
    return n1 * n2

def div(n1, n2):
    return n1 / n2 if n2 != 0 else 'Error'


Test in Python:

python
import calc

print(calc.add(10, 5))
print(calc.sub(10, 5))
print(calc.mul(10, 5))
print(calc.div(10, 5))


---

### ✅ Q-11: Python Package mypackage

*Folder structure:*


mypackage/
├── __init__.py
├── student.py
├── professor.py


*mypackage/student.py*

python
def dispName(nam):
    print("Student Name:", nam)

def dispRollNo(rno):
    print("Roll No:", rno)


*mypackage/professor.py*

python
def dispName(nam):
    print("Professor Name:", nam)

def dispEmpNo(eno):
    print("Employee No:", eno)


*Test code:*

python
from mypackage import student, professor

student.dispName("Raj")
student.dispRollNo(101)

professor.dispName("Dr. Shah")
professor.dispEmpNo(9001)


---

### ✅ Q-12: SQLite DB Access in Python with All Methods

python
import sqlite3

# Connect to DB
con = sqlite3.connect("mydb1.db")
cur = con.cursor()

# Create table
cur.execute("CREATE TABLE IF NOT EXISTS test (id INTEGER, name TEXT)")

# Insert
cur.execute("INSERT INTO test VALUES (?, ?)", (1, 'Alice'))
con.commit()

# Update
cur.execute("UPDATE test SET name = ? WHERE id = ?", ('Bob', 1))
con.commit()

# Select one
cur.execute("SELECT * FROM test WHERE id = 1")
print("fetchone:", cur.fetchone())

# Select all
cur.execute("SELECT * FROM test")
print("fetchall:", cur.fetchall())

# Delete
cur.execute("DELETE FROM test WHERE id = 1")
con.commit()

con.close()


---

Let me know if you need .sql or .py files pre-written or zipped for submission.