CREATE DATABASE menagerie;

USE menagerie;

CREATE TABLE pet (

    name    VARCHAR(20),

    owner   VARCHAR(20),

    species VARCHAR(20),

    sex     CHAR(1),

    birth   DATE,

    death   DATE

);

CREATE TABLE event (

    name    VARCHAR(20),

    date    DATE,

    type    VARCHAR(15),

    remark  VARCHAR(255)

);

INSERT INTO pet (name, owner, species, sex, birth, death) VALUES 

('Fluffy', 'Harold', 'cat', 'f', '1993-02-04', NULL),

('Claws', 'Gwen', 'cat', 'm', '1994-03-17', NULL),

('Buffy', 'Harold', 'dog', 'f', '1989-05-13', NULL),

('Fang', 'Benny', 'dog', 'm', '1990-08-27', NULL),

('Bowser', 'Diane', 'dog', 'm', '1979-08-31', '1995-07-29'),

('Chirpy', 'Gwen', 'bird', 'f', '1998-09-11', NULL),

('Whistler', 'Gwen', 'bird', NULL, '1997-12-09', NULL),

('Slim', 'Benny', 'snake', 'm', '1996-04-29', NULL),

('Puffball', 'Diane', 'hamster', 'f', '1999-03-30', NULL);

INSERT INTO event (name, date, type, remark) VALUES 

('Fluffy', '1995-05-15', 'litter', '4 kittens, 3 female, 1 male'),

('Buffy', '1993-06-23', 'litter', '5 puppies, 2 female, 3 male'),

('Buffy', '1994-06-19', 'litter', '3 puppies, 3 female'),

('Chirpy', '1999-03-21', 'vet', 'needed beak straightened'),

('Slim', '1997-08-03', 'vet', 'broken rib'),

('Bowser', '1991-10-12', 'kennel', NULL),

('Fang', '1991-10-12', 'kennel', NULL),

('Fang', '1998-08-28', 'birthday', 'Gave him a new chew toy'),

('Claws', '1998-03-17', 'birthday', 'Gave him a new flea collar');

Practice table

 

TABLE : teacher

i)To display the entire table

ii)To show all the information about the teacher of Computer Department.

iii) To display the names of teachers who get a salary of at least 25000

iv)To display the list of distinct departments in the table.

v)To list the names of male teachers who are  in the math department.

vi)To display teacher’s name , salary ,age for  non-History teachers only.

vii)To display the Name, Salary , and Tax(10% of salary) of the teacher.

viii)To display the details of Math and Computer teachers.

ix)To display the name of teachers who are either above 40 years of age or have a salary of at least 30000

x)To display the names of the teachers with no department assigned.

CREATE AND INSERT

create database if not exists mydb1;

use mydb1;

create table teacher(

     No int primary key auto_increment,

     Name varchar(20) not null,

     Age numeric(2) check(age between 20 and 70),

     Department varchar(20),

     DOJ date,

     Salary numeric(7),

     Sex varchar(1) check(Sex in ('M','F')));

insert into teacher(Name,Age,Department,DOJ,Salary,Sex) values

     ('Jugal',34,'Computer','1997-01-10',12000,'M'),

     ('Sharmila',31,'History','1998-03-24',20000,'F'),

     ('Sandeep',32,'Math','1996-12-12',30000,'M'),

     ('Sangeeta',35,'History','1999-07-01',40000,'F'),

     ('Rakesh',42,'Math','1997-09-05',25000,'M'),

     ('Shyam',50,NULL,'1998-06-27',30000,'M'),

     ('Shiv Om',44,'Computer','1997-02-25',21000,'M'),

     ('Shalaka',33,'Math','1997-07-31',20000,'F');

select * from teacher;

QUERIES

i)select * from teacher;

ii)select * from teacher where Department = 'Computer';

iii)select * from teacher where salary>25000;

iv)select distinct Department from teacher;

v)select * from teacher where Department = 'Math' and Sex = 'M';

vi)Select Name,Salary,Age from teacher where Department != 'History';

vii)Select Name,Salary,10/100*Salary as Tax from teacher;

viii)Select * from teacher where Department = 'Math' or Department = 'Computer';

ix)select * from teacher where age>40 or salary>=30000;

x)Select * from teacher where Department is NULL;

 WAP to create the below tables where student refers to stream and perform the queries that follow : 

STUDENT:

STREAM:

a) Alter the table to include a column named sex char(1) after lname.

GRADE 12 - PRACTICAL 13 - BINARY FILES 2 July 2026

 '''GRADE 12 - PRACTICAL 13 - BINARY FILES

A binary file "Inventory.dat" stores product records

where each record is as below:

{"pname": <str>, "categ": <str>, "price": <float>}

i) WAF Create() to input multiple products and add

them into the file.

ii) WAF Display() to display all records and the average

price of the products.

iii) WAF Search() for a certain pname and display its

category if found.

iv) WAF Update() to increase the cost of all items in the

'food' category by 5 percent.'''

def Create():

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

        n = int(input("Enter no of records : "))

        for i in range(n):

            pname = input("Enter Product Name: ")

            categ = (input("Enter Category: ").lower())

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

            record = {"pname": pname, "categ": categ, "price": price}

            pickle.dump(record, f)

            

def Display():

    pass

def Search():

    pass

def Update():

    pass

while True:

    print("""\nMENU

1. Add Records

2. Display

3. Search

4. Update

5. Exit""")

    choice = input("Enter your choice (1-5): ")

    if choice == "1":

        Create()

    elif choice == "2":

        Display()

    elif choice == "3":

        Search()

    elif choice == "4":

        Update()

    elif choice == "5":

        print("Exiting program. Goodbye!")

        break

    else:

        print("Invalid Choice!")

GRADE 12 - PRACTICAL 12 - BINARY FILES (29 june 26)

 """GRADE 12 - PRACTICAL 12 - BINARY FILES

A binary file "Scores.dat" stores cricketers' names (string)

and their average runs (float) per match as:

["Rahul Dravid", 52.31]

i) WAF Create() to input records into Scores.dat

ii) WAF Display() to display all records in tabular form

iii) WAF CountAvg() to display the overall average score

     and display players whose average is above 50.

iv) WAF Update() to update the average runs of a given player.

"""

GRADE 12 : PRACTICAL 11: BINARY FILES

'''LAB 11 : WAPP to demonstrate the following options

by creating different methods using binary files for

a hospital management application :

(i) adding records of the structure :  (pat_id,pat_name,diagnosis,fees)

(ii)reading and displaying all the records in a

tabular format

(iii)searching and displaying patients who have 'corona'. Display if no such patient.

(iv)update the file to decrease the fees of a given patient by 10%.Display if not found.

'''

GRADE 12 : PRACTICAL 10 .... 15/6/26

 '''WAP to operate on the file market.csv with the headers

MARKET NO, MARKET NAME, NO OF VISITORS .

Write functions to:

> Accept values and populate the file

> Search for the market with the Highest Number of Visitors

> Update the no. of visitors. (market No is parameter to the function)

> Display the details of the file and the total

  number of visitors in tabular format.

'''

Binary Practice :

 '''A binary file Transport.dat stores records in the following format:[StudentId, StudentName, RouteNo, RouteName]

Write suitable functions for the following:

>addStudent()

Accept the details of a student and append the record to the binary file Transport.dat.

>changeRoute(routeNo , newRouteName)

Display student names with that route.

Update the route name to the passed value

Display if no student in that route

'''

grade 12 : PRACTICAL 9 - CSV FILES (8-6-26)

 """Lab Program 9: Mr Ram stores data of his gift store

in a CSV file name gift.csv in the format : 

Gift code, Gift name, and Gift price of the item for his gift store. Write functions to :

1> Add records to the file.Add a header if the file is new.

2> Display all the records, as well as the average gift price.

3> Search for a certain gift code. Display Error , if not found. 

3> Update the gift price, given the gift code. Error, if not found"""

GRADE 12 - PRACTICAL 8 - TEXT FILES (4-6-26)

#GRADE 12 - PRACTICAL 8 - TEXT FILES """WAP to perform the following operations on ‘ Song.txt’

i) Accept a song(until stop is entered ) & write to the file. Display the file.

ii)Display alternate lines of the file. Also display the number of uppercase & lowercase characters in the file.

iii) Copy all the words starting with ‘a’,'b','c','d' or ‘e’(any case ) into 'abcde.txt’.Display ‘abcde.txt’

iv) Copy all the lines containing the word ‘you’ into a file named ‘you.txt’ after changing it to YOU.

NOTE : Ensure no error is thrown if a non existent file is being read"""

def create():

    f = open("Song.txt", "w")

    while True:

        line = input("Enter line: ")

        if line.lower()=='stop':

            break

        f.write(line + "\n")

    f.close()

    print("Song written to file successfully.")

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

            print(f.read())

            

def display():

    try:

        f = open("Song.txt", "r")

        data = f.read()

        print("\n of the file:")

        lines=data.splitlines()

        for line in lines[::2]:

            print(line)

        upper = 0

        lower = 0   

        for ch in data:

            if ch.isupper():

                upper += 1

            elif ch.islower():

                lower += 1

        print("Uppercase characters:", upper)

        print("Lowercase characters:", lower)

        f.close()

    except FileNotFoundError:

        print('Sorry , song file is not found ')

    except exception as e:

        print('Unexpected error : ',e)

while True:

    print("\nMENU")

    print("1. Write song to file")

    print("2. Display file and count uppercase & lowercase characters")

    print("3. Copy words starting with a/b/c/d/e into abcde.txt")

    print("4. Copy lines containing 'you' into you.txt (change to YOU)")

    print("5. Exit")

    

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

    

    if ch == 1:

        create()

    elif ch == 2:

        display()

    elif ch == 3:

        abcde()

    elif ch == 4:

        you_lines()

    elif ch == 5:

        break

    else:

        print("Invalid choice")

GRADE 12 : PRACTICAL 7: TEXT FILES (1 - 6 - 26)

 WAP to create functions that work on 'essay.txt' :

1. Accept an essay on nature and store it in the file. Display the Content of the file

2. Display the file and the number of times the word 'nature' occurs.(any case)

3. Make a new file green.txt to have all the lines that contain the word 'green'.Display the file

4. Change all the occurrences of 'nature/Nature' to NATURE. 

5. Add an extra paragraph to the essay.

6. Exit

Sample project report

PROJECT REPORT TEMPLATE