DBMS - SOLUTION

CREATE DATABASE Practical ;

USE Practical ;

 CREATE TABLE Customers (

    CustomerID INT PRIMARY KEY,

    CustomerName VARCHAR(30),

    ContactNumber NUMERIC(10),

    City VARCHAR(20)

); 

CREATE TABLE Orders (

    OrderID INT PRIMARY KEY,

    CustomerID INT,

    OrderDate DATE,

    Amount INT,

    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)

);

INSERT INTO Customers VALUES

(101, 'Aarav Sharma', 9123456780, 'Pune'),

(102, 'Vivaan Patel', 9876543210, 'Ahmedabad'),

(103, 'Aditya Singh', 9012345678, 'Mumbai'),

(104, 'Anaya Gupta', 9345678123, 'Jaipur'),

(105, 'Diya Mehta', 9567812345, 'Kochi');

INSERT INTO Orders VALUES

(321, 101, '2024-11-10', 300),

(322, 102, '2024-11-18', 650),

(323, 101, '2025-02-05', 200),

(324, 103, '2025-02-12', 350),

(325, 104, '2025-03-01', 500),

(326, 105, '2025-03-08', 750);

1. Customer name and city for orders with amount > 400

Query

SELECT CustomerName, City
FROM Customers
NATURAL JOIN Orders
WHERE Amount > 400;

Output

CustomerName	City
Vivaan Patel	Ahmedabad
Anaya Gupta	Jaipur
Diya Mehta	Kochi

---

2. Names of customers who placed orders in January 2025

Query

SELECT DISTINCT CustomerName
FROM Customers
NATURAL JOIN Orders
WHERE OrderDate BETWEEN '2025-01-01' AND '2025-01-31';

or

SELECT DISTINCT CustomerName
FROM Customers
NATURAL JOIN Orders
WHERE YEAR(OrderDate) = 2025
AND MONTH(OrderDate) = 1;

or OrderDate LIKE '2025-01%' 

Output

CustomerName
No records found

---

3. Total amount spent by each customer

Query

SELECT CustomerName, SUM(Amount) AS TotalAmount
FROM Customers
NATURAL JOIN Orders
GROUP BY CustomerName;

Output

CustomerName	TotalAmount
Aarav Sharma	500
Vivaan Patel	650
Aditya Singh	350
Anaya Gupta	500
Diya Mehta	750

---

4. Maximum order amount placed by any customer

Query

SELECT MAX(Amount) AS MaximumOrder
FROM Orders;

or

use a sub query, to show who got that maximum order

Output

MaximumOrder
750

---

5. Average order amount city-wise (descending order)

Query

SELECT City, AVG(Amount) AS AverageAmount
FROM Customers
NATURAL JOIN Orders
GROUP BY City
ORDER BY AverageAmount DESC;

Output

City	AverageAmount
Kochi	750
Ahmedabad	650
Jaipur	500
Mumbai	350
Pune	250

---

6. Customers living in cities starting with letter ‘M’

Query

SELECT CustomerName
FROM Customers
WHERE City LIKE 'M%';

Output

CustomerName
Aditya Singh

---

7. CustomerID and total amount for customers whose spending exceeds 400

Query

SELECT CustomerID, SUM(Amount) AS TotalAmount
FROM Orders
GROUP BY CustomerID
HAVING SUM(Amount) > 400;

Output

CustomerID	TotalAmount
101	500
102	650
104	500
105	750

---

8. Increase order amount of 2025 customers by 10%

Query

UPDATE Orders
SET Amount = Amount * 1.10
WHERE YEAR(OrderDate) = 2025;

Output (Effect on Orders Table)

OrderID	CustomerID	OrderDate	Amount
323	101	2025-02-05	220
324	103	2025-02-12	385
325	104	2025-03-01	550
326	105	2025-03-08	825

---

9. Delete orders of December 2024

Query

DELETE FROM Orders
WHERE OrderDate BETWEEN '2024-12-01' AND '2024-12-31';

Output

Result
No rows deleted

CSV FILE - > USE METHOD 2 (SKIP HEADER IF YOU FIND THAT TOUGH) OR METHOD 1 (LIL MORE DETAILED)

 METHOD 1 : 

import csv

# Menu function

def menu():

    while True:

        print("\n1. Write Student Records")

        print("2. Display Student Records")

        print("3. Calculate Average Marks")

        print("4. Search Student by ID")

        print("5. EXIT")

        choice = int(input("Enter your choice: "))

        if choice == 1:

            write_students()

        elif choice == 2:

            display_students()

        elif choice == 3:

            average_marks()

        elif choice == 4:

            search_student()

        elif choice == 5:

            print("Exiting program.")

            break

        else:

            print("Invalid choice")

# i) Write student records (append mode + header check)

def write_students():

    with open("Students.csv", "a", newline="") as f:

        writer = csv.writer(f)

        if f.seek(0)==0:

            writer.writerow(["Student ID", "Student Name", "Class", "Marks"])

        n = int(input("Enter number of students: "))

        for i in range(n):

            sid = input("Student ID: ")

            name = input("Student Name: ")

            clas = input("Class: ")

            marks = float(input("Marks: "))

            writer.writerow([sid, name, clas, marks])

    print("Records written successfully")

# ii) Display contents of CSV file

def display_students():

    try:

        with open("Students.csv", "r") as f:

            reader = csv.reader(f)

            print("\nStudent Records:")

            for row in reader:

                print(row)

    except FileNotFoundError:

        print("Error: File not found")

# iii) Calculate average marks (skip header)

def average_marks():

    try:

        with open("Students.csv", "r") as f:

            c = csv.reader(f)

            next(c)  # skip header

            total = count = 0

            for row in c:

                total += int(row[3])

                count += 1

        print("Average Marks =", total / count)

        

    except FileNotFoundError:

        print("Error: File not found")

# iv) Search student using Student ID (skip header)

def search_student():

    try:

        with open("Students.csv", "r") as f:

            reader = csv.reader(f)

            next(reader)  # skip header

            sid = input("Enter Student ID to search: ")

            found = False

            for row in reader:

                if row[0] == sid:

                    print("Student Found:", row)

                    found = True

                    break

        if not found:

            print("Student not found")

    except FileNotFoundError:

        print("Error: File not found")

# Call menu

menu()

METHOD 2:

import csv

# Menu function

def menu():

    while True:

        print("\n1. Write Student Records")

        print("2. Display Student Records")

        print("3. Calculate Average Marks")

        print("4. Search Student by ID")

        print("5. EXIT")

        choice = int(input("Enter your choice: "))

        if choice == 1:

            write_students()

        elif choice == 2:

            display_students()

        elif choice == 3:

            average_marks()

        elif choice == 4:

            search_student()

        elif choice == 5:

            print("Exiting program.")

            break

        else:

            print("Invalid choice")

# i) Accept details and write into CSV file

def write_students():

    with open("Students.csv", "a", newline="") as f:

        writer = csv.writer(f)

        n = int(input("Enter number of students: "))

        for i in range(n):

            sid = input("Student ID: ")

            name = input("Student Name: ")

            clas = input("Class: ")

            marks = float(input("Marks: "))

            writer.writerow([sid, name, clas, marks])

    print("Records written successfully")

# ii) Display contents of CSV file

def display_students():

    with open("Students.csv", "r") as f:

        reader = csv.reader(f)

        print("\nStudent Records:")

        for row in reader:

            print(row)

# iii) Calculate average marks

def average_marks():

    with open("Students.csv", "r") as f:

        reader = csv.reader(f)

        total = count = 0

        for row in reader:

            total += float(row[3])

            count += 1

    print("Average Marks =", total / count)

    

# iv) Search student using Student ID

def search_student():

    with open("Students.csv", "r") as f:

        reader = csv.reader(f)

        sid = input("Enter Student ID to search: ")

        found = False

        for row in reader:

            if row[0] == sid:

                print("Student Found:", row)

                found = True

                break

    if not found:

        print("Student not found")

# Call menu function

menu()

TEXT FILE : EXTERNAL PRACTICALS

 # Menu function

def menu():

    while True:

        print("\n1. Write Story")

        print("2. Display Story")

        print("3. File Statistics")

        print("4. Copy Words Starting with Vowels")

        print("5. EXIT")

        choice = int(input("Enter your choice: "))

        if choice == 1:

            write_story()

        elif choice == 2:

            display_story()

        elif choice == 3:

            statistics()

        elif choice == 4:

            copy_vowels()

        elif choice == 5:

            print("Exiting program.")

            break

        else:

            print("Invalid choice")

# i) Write n lines into Story.txt

def write_story():

    with open("Story.txt", "w") as f:

        n = int(input("Enter number of lines: "))

        for i in range(n):

            f.write(input() + "\n")

    print("Story written successfully")

# ii) Display contents of the file

def display_story():

    try:

        with open("Story.txt", "r") as f:

            print("\nStory Contents:\n")

            print(f.read())

    except FileNotFoundError:

        print("Error: File does not exist")

# iii) Display file statistics

def statistics():

    try:

        with open("Story.txt", "r") as f:

            text = f.read()

        characters = len(text)

        words = text.split()

        lines = text.split("\n")

        print("Character Count:", characters)

        print("Word Count:", len(words))

        print("Line Count:", len(lines) - 1)

        capital_count = 0

        for word in words:

            if word[0].isupper():

                capital_count += 1

        print("Words starting with capital letter:", capital_count)

    except FileNotFoundError:

        print("Error: File does not exist")

# iv) Copy words starting with vowels to vowels.txt

def copy_vowels():

    try:

        with open("Story.txt", "r") as f:

            text = f.read()

            words= text.split()

        with open("vowels.txt", "w") as fw:

            for word in words:

                if word[0].lower() in "aeiou":

                    fw.write(word + "\n")

        print("\nContents of vowels.txt:")

        with open("vowels.txt", "r") as fr:

            print(fr.read())

    except FileNotFoundError:

        print("Error: File does not exist")

# Call menu

menu()

STACKS : DO ANY METHOD THATS EASY FOR YOU , THOUGH THEY MAY ASK PUSH( STACK, RECORD) . BOTH WILL FETCH YOU FULL MARKS. NO WORRIES

 METHOD 1 : 

# Stack and limit

stack = []

limit = 5

# Menu function

def menu():

    while True:

        print("1. PUSH Issue Record")

        print("2. POP Issue Record")

        print("3. DISPLAY Issue Records")

        print("4. CALCULATE Total Fine Amount")

        print("5. EXIT")

        choice = int(input("Enter your choice: "))

        if choice == 1:

            issue_id = int(input("Enter Issue ID: "))

            member = input("Enter Member Name: ")

            book = input("Enter Book Title: ")

            fine = float(input("Enter Fine Amount: "))

            record = [issue_id, member, book, fine]

            push(stack, record)

        elif choice == 2:

            result = pop(stack)

            if result == "UNDERFLOW":

                print("UNDERFLOW\n")

            else:

                print("Removed Record:", result, "\n")

        elif choice == 3:

            display(stack)

        elif choice == 4:

            calculate(stack)

        elif choice == 5:

            print("Exiting program.")

            break

        else:

            print("Invalid choice\n")

# i) PUSH operation

def push(stack, record):

    if len(stack) == limit:

        print("OVERFLOW\n")

    else:

        stack.append(record)

        print("Issue record added successfully\n")

# ii) POP operation

def pop(stack):

    if len(stack) == 0:

        return "UNDERFLOW"

    else:

        return stack.pop()

# iii) DISPLAY operation

def display(stack):

    if len(stack) == 0:

        print("STACK EMPTY\n")

    else:

        print("Issue Records in Stack:")

        for record in stack[::-1]:

            print(record)

        print()

# iv) CALCULATE total fine amount

def calculate(stack):

    if len(stack) == 0:

        print("STACK EMPTY\n")

    else:

        total = 0

        for record in stack:

            total += record[3]

        print("Total Fine Amount:", total, "\n")

# Call menu

menu()

METHOD 2 : 

stack = []

LIMIT = 5

def menu():

    while True:

        print("\n1. PUSH  2. POP  3. DISPLAY  4. CALCULATE  5. EXIT")

        ch = int(input("Enter choice: "))

        if ch == 1:

            push()

        elif ch == 2:

            pop()

        elif ch == 3:

            display()

        elif ch == 4:

            calculate()

        elif ch == 5:

            print("Exiting program")

            break

        else:

            print("Invalid choice")

def push():

    if len(stack) >= LIMIT:

        print("OVERFLOW")

    else:

        issue_id = int(input("Issue ID: "))

        name = input("Member Name: ")

        book = input("Book Title: ")

        fine = float(input("Fine Amount: "))

        stack.append([issue_id, name, book, fine])

def pop():

    if len(stack) == 0:

        print("UNDERFLOW")

    else:

        print("Removed Record:", stack.pop())

def display():

    if len(stack) == 0:

        print("STACK EMPTY")

    else:

        for record in reversed(stack):

            print(record)

def calculate():

    total = 0

    for record in stack:

        total += record[3]

    print("Total Fine Amount =", total)

# Call menu function

menu()

BINARY FILE

import pickle

def menu():

    while True:

        print("\n------ MENU ------")

        print("1. Add Book Records")

        print("2. Display All Books")

        print("3. Search Book by ID")

        print("4. Calculate Average Price")

        print("5. Exit")

        ch = int(input("Enter your choice: "))

        if ch == 1:

            add_books()

        elif ch == 2:

            display_books()

        elif ch == 3:

            search_book()

        elif ch == 4:

            average_price()

        elif ch == 5:

            print("Exiting program")

            break

        else:

            print("Invalid choice")

def add_books():

    with open("Book.dat", "ab") as f:

        n = int(input("Enter number of books: "))

        for i in range(n):

            book_id = int(input("Book ID: "))

            title = input("Title: ")

            author = input("Author: ")

            price = float(input("Price: "))

            record = [book_id, title, author, price]

            pickle.dump(record, f)

def display_books():

    try:

        with open("Book.dat", "rb") as f:

            while True:

                record = pickle.load(f)

                print(record)

    except FileNotFoundError:

        print("File not found")

    except EOFError:

        pass

def search_book():

    try:

        bid = int(input("Enter Book ID to search: "))

        found = False

        with open("Book.dat", "rb") as f:

            while True:

                record = pickle.load(f)

                if record[0] == bid:

                    print("Book Found:", record)

                    found = True

                    break

    except FileNotFoundError:

        print("File not found")

    except EOFError:

        if not found:

            print("Book not found")

def average_price():

    try:

        total = count = 0

        with open("Book.dat", "rb") as f:

            while True:

                record = pickle.load(f)

                total += record[3]

                count += 1

    except FileNotFoundError:

        print("File not found")

    except EOFError:

        if count > 0:

            print("Average Price =", total / count)

        else:

            print("No records found")

# Call the menu function

menu()