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()

External Practical Practice

1. Write a program to create a binary file Book.dat to store details of books available in a school library.

Each record should be of the form:
[book_id, title, author, price]

The program should include the following functions:

i) Add new book records to the binary file.
ii) Display all the book records stored in the file.
iii) Search for a book using book_id and display its details if found; otherwise display "Book not found".
iv) Calculate and display the average price of all the books.

2. Implement a stack to store library book issue details.

Each record should contain the fields:
[Issue ID, Member Name, Book Title, Fine Amount]

Write a menu-driven program to perform the following operations:

i) PUSH(stack, record) – Add a new book issue record to the stack. Display OVERFLOW when the stack limit is exceeded.

ii) POP(stack) – Remove the most recent book issue record from the stack and return it, or display UNDERFLOW if the stack is empty.

iii) DISPLAY(stack) – Display all book issue records currently in the stack. Display STACK EMPTY if there are no records.

iv) CALCULATE(stack) – Calculate and display the total fine amount of all records in the stack.

Note:

• Implement Underflow and Overflow conditions.

• The stack limit is 5 records.

3. Write a menu driven program to perform the following operation into a text file ‘ Story.txt’

i) Create a function to write n lines of a story to the text file

ii) Function to display the contents of the file. Display an error if file does not exist. 

iii)Function to display the following statistics of the file:

Character count

Word count

Line Count

No of words starting with capital letter

iv) Create a function to copy all the words starting with vowels into a new file ‘vowels.txt’ and finally display the contents of vowels.txt


4.Write a menu-driven program to manage a CSV file named Students.csv with the following information:

[Student ID, Student Name, Class, Marks]

Implement the following functions to:

i) Accept details of n students and write them into the CSV file.
ii) Display the contents of the CSV file.
iii) Calculate and display the average marks of all the students.
iv) Search for a student using a specific Student ID and display the details if found; otherwise display an appropriate error message.

DBMS PRACTICE .

Consider the below tables :

CUSTOMERS

CustomerID | CustomerName   | ContactNumber | City
---------------------------------------------------
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

---

ORDERS 

OrderID | CustomerID | OrderDate   | Amount
--------------------------------------------
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.Display the customer name and city of customers who have placed an order of amount greater than 400

2.Display the names of customers who placed orders in January 2025

3.Find the total amount spent by each customer

4.Display the maximum order amount placed by any customer

5.Display the average order amount city-wise in descending order

6.Display the names of customers who live in cities starting with letter ‘M’

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

8.Increase the order amount of the 2025 customers by 10 percent

9.Delete the orders of 2024 December

Sample practical paper II

 SECTION A

1. Write a menu driven program to operate on a csv file courier.csv with each record as a list of the

form :

[courier_id, name, city, fees].

The menu should

a) Insert records into the file.

b) Display records, the total of the fees and the average fees

c) Search and display the details of a given courier_id

2. Your Principal has chosen you to to help maintain the records of grade 12 students in a stack format.

Each record should contain { ‘s_id’:int, ‘s_name’:str , ‘s_marks’: int}

You decide to implement stack data structure to maintain the records of your classmates to perform the

following operations on the stack , in a menu driven fashion

i) Push

ii) Pop

iii) Display (at the end of the display , print the average marks of the students, and the topper’s details)

SECTION B

3.Consider the following table:

Write SQL Queries to create the above tables and insert records into them, and write queries for

the following:

a) To display employee ids, names of employees, job ids with corresponding job titles. 

b) To display names of employees, sales and job titles who have achieved sales more than 1300000.

c) To display names who have ‘SINGH’ (anywhere) in their names.

d) Write SQL command to change the jobid to 104 of the employee with ID as E4 in the table

Employee.

e) Display a list of jobid, job name and number of employees

f) Display only the jobs with salary greater than or equal to the average salary.

g) Increase “manager” salary by 5 percent.

Sample practical paper - I

 SECTION A

1. Write a menu-driven program to operate on a binary file book.dat with each record stored as a list in

the form:

[book_id (int), book_title (string), price (int)].

The menu should perform the following operations:

a) Insert records into the file.

b) Display all records, the total cost of all books, and the average price of books.

c) Update the price of a given book_id, else display a suitable message.

2. You are an excellent coder. So your neighbour Amit, who owns a departmental store tells you to help

him maintain the records of a customer’s cart in a stack format.

You decide to implement stack data structure to maintain the records of the shopping cart to perform the

following operations on the stack the, in a menu driven fashion

i) Push

ii) Pop

iii) Display (at the end of the display , print the total price of the cart)

Note : Each record is of the structure :

{‘p_id’: int , ‘p_name’ :str , ‘price’: float}.

SECTION B

3. Create the below tables and write the SQL statements for the queries that follow :

1. Display the names of borrowers who borrowed a book in December 2024.

2. List the book titles borrowed by borrowers living in Mumbai.

3. Find the total number of books borrowed by each borrower( id and name).

4. Display the city-wise count of books borrowed in descending order of count.

5. List the names of borrowers who have not returned at least one book

6. Display the borrowers who have returned all the books .

7. Update return date of Kavya Iyer’s unreturned books to 2025-09-09.

Binary files practice

 Write a Python functions :

STUDENT_ADD() that adds new students to the file student.dat. 

Each record in the file contains the following information for a student:

• Roll Number (integer)

• Name (string)

• sPercentage (float)

STUDENT_UPDATE() that changes the percentage of a certain roll number.

>Display if student not found

CSV file practice

(a) Write one point of difference between seek() and tell() functions in file handling. Write a program in Python that defines and calls the following user defined functions :

(i) Add_Device() : The function accepts and adds records of the peripheral devices to a csv file ‘peripheral.csv’. Each record consists of a list with field elements as P_id, P_name and Price to store peripheral device ID, device name, and price respectively.

(ii) Count_Device() : To count and display number of peripheral devices, whose price is less than ₹1000.

                                                   

(b) Write a program in Python that defines and calls the following user defined functions :

(i) Add_Teacher() : It accepts the values from the user and inserts record of a teacher to a csv file “Teacher.csv”. Each record consists of a list with field elements as T_id, Tname and desig to store teacher ID, teacher name and designation respectively.

(ii) Search_Teacher() : To display the records of all the PGT (designation) teachers.

Text file practice

''' Write a function in Python that copies the book names having ‘Y’ or ‘y’ in

their name from a text file

“Bookname.txt” into another file Yy.txt Finally display Yy.txt and the total

number of Books in the text..

Example:

If the file ‘Bookname.txt’ contains the names of following books :

One Hundred Years of Solitude

The Diary of a Young Girl

On the Road

After execution, the output will be :

One Hundred Years of Solitude

The Diary of a Young Girl

No of Books in Yy.txt : 2'''

def func():

    l=[]

    with open('bookname.txt','r') as f:

        text=f.read()

        lines=text.splitlines()

        for i in lines:

            if 'y' in i or 'Y' in i:

                l.append(i)

    with open('yy.txt','w') as f:

        f.writelines(l)

    with open('yy.txt','r') as f:

        text=f.read()

        lines=text.splitlines()

        print(text)

        print('no of books : ',len(lines))

'''

Write a function RevString() to read a textfile “Input.txt” and prints the words starting with

‘O’ in reverse order. The rest of the content is displayed normally.

Example:

If content in the text file is :

UBUNTU IS AN OPEN SOURCE OPERATING SYSTEM

Output will be :

UBUNTU IS AN NEPO SOURCE GNITAREPO SYSTEM

(words ‘OPEN’ and ‘OPERATING’ are displayed in reverse order)

'''       

with open('input.txt','r') as f:

        text=f.read()

        s=''

        words=text.split()

        for i in words:

            if i[0].lower()=='o':

                s=s+i[::-]+' ')

            else:

                s=s+i

print(s)

Dictionaries practice

 

1. Write a menu-driven program to create a dictionary called HOTEL and provide the following functions:

   a) ADD n rooms to the dictionary HOTEL, where each room description is stored as

   {ROOM_NO : [GUEST_NAME, ROOM_TYPE, NO_OF_DAYS, BILL]}

   • Ensure the ROOM_TYPE => Standard(default), Deluxe, Supreme or Suite

   • BILL → calculated based on room type and number of days

   • Room Type	Cost per Day (₹)
     Standard	1500
     Deluxe	2500
     Supreme	3500
     Suite	5000

   b) SEARCH for a room using its ROOM_N and increase the stay by 1 day.
   The BILL should be recalculated accordingly. Display ROOM NOT FOUND in case not found

   c) DISPLAY all room records in a tabular form showing:

   Room No | Guest Name | Room Type | Days | Bill     

• Also display the total bill amount collected. 

practical dates