GRADE 12 - LAB 15

Write query commands for the following based on tables doctor and doc_dept .

DOCTOR

+------+------------------+--------+-----+------------+----------+

| d_id | dname            | gender | age | mobile     | salary   |

+------+------------------+--------+-----+------------+----------+

| d123 | swati garg       | f      |  35 | 9873214560 | 85000.00 |

| d234 | anirudh          | m      |  40 | 9874563210 | 91000.00 |

| d334 | Deepak           | m      |  45 | 9988774455 | 85400.00 |

| d456 | rupinder kaur    | f      |  32 | 9632587410 | 95850.00 |

| d656 | shailender gupta | m      |  42 | 9102365478 | 98750.00 |

| d734 | yashika lamba    | f      |  39 | 9899552223 | 75300.00 |

+------+------------------+--------+-----+------------+----------+

DOC_DEPT

+------+-------------+---------+----------+

| d_id | department  | charges | opd_days |

+------+-------------+---------+----------+

| d123 | gynaecology |  700.00 | mwf      |

| d234 | cardiology  |  850.00 | mwf      |

| d456 | gynaecology |  700.00 | tts      |

| d656 | cardiology  |  850.00 | mwf      |

| d734 | ent         |  900.00 | tts      |

| d334 | neurology   |  950.00 | tts      |

+------+-------------+---------+----------+

create table doctor(

d_id char(4) primary key,

dname varchar(25),

gender char(1) check(gender in ("M","F")),

age int,

mobile decimal(10),

salary decimal(7,2));

insert into doctor values

("d123","Swati Garg","F",35,9873214560,85000.00),

("d234","Anirudh","M",40,9874563210,91000.00),

("d334","Deepal","M",45,9988774455,85400.00),

("d456","Rupinder Kaur","F",32,9632587910,95850.00),

("d656","Shailendar Gupta","M",42,9102365478,98750.00),

("d734","Yashika Lamba","F",39,9899552223,75300.00);

create table doc_dept(

d_id char(4) primary key references doctor(d_id),

department varchar(20),

charges decimal(10,2),

opd_days varchar(7));

insert into doc_dept values

("d123","Gynaecology",700.00,"mwf"),

("d234","Cardiology",850.00,"mwf"),

("d456","Gynaecology",700.00,"tts"),

("d656","Cardiology",850.00,"mwf"),

("d734","ENT",900.00,"tts"),

("d334","Neurology",950.00,"tts");

i)Display id, name, department, mobile number, and charges from the above table.

ii) Display dname, department name, charges of opd_days mwf

iii)Display the total salary of the doctors department wise

iv)Increase the charges of the neurology, ent department to 20%

v)Show the details of the male doctors whose age is between 40 to 50  and have opd_days on MWF

vi)Display the average age of those departments which are less than 40

vii)Find the details of all doctors whose salary is greater than the average salary of all doctors. viii)Display the name and age of doctors who work in departments that offer OPD on 'TTS'. ix)Display the doctor names, department, and salary for all doctors whose name ends with the letter 'a'. x) Find the maximum and minimum salary for each department. xi) Display the department name that charges the highest OPD fee. 

RAILWAY DATABASE

CREATE DATABASE railway;

USE railway;

 CREATE TABLE train (

    train_number VARCHAR(10) PRIMARY KEY, -- Indian train numbers are 5-digit codes

    train_name VARCHAR(100) NOT NULL,

    origin VARCHAR(50) NOT NULL,

    destination VARCHAR(50) NOT NULL,

    fare DECIMAL(7,2) NOT NULL, 

    seats_available INT NOT NULL

);

INSERT INTO train VALUES

('22436', 'Vande Bharat ', 'Delhi', 'Varnas',  1750, 45),

('12952', 'Rajdhani', 'Hyder', 'Mumbai', 2400, 18),

('12002', 'Shatabdi ', 'Delhi', 'Chennai', 1165, 85),

('12626', 'Kerala Exp', 'Mumbai', 'Trivand',  810, 120);

 String Functions

Q1. Display each pet's name in UPPERCASE and its species in lowercase.

Q2. Format the output so it reads like a sentence: "[pet name] is owned by [owner]".

Q3. Create a short code for each pet consisting of the first 3 letters of its species in uppercase followed by the last 3 letters of pet's name.

Q4. Find the length of each pet's name and display only those pets whose names have more than 5 characters.

Q6. Display the event remarks after removing any leading or trailing spaces.

Numeric Functions

Q7. Display the rounded fee to 2 decimal places and truncated fee to 2 decimal places ,if the fee is Rs 125.6789

Q8. Assign pets into 2 alternate checkup groups(group1 or group2) based on whether their birth year is even or odd (using MOD).

Q9. Display the square root of each pet's birth year rounded to 2 decimal places.

Q10. Check the sign of the difference between 1995 and pet's birth year using SIGN().

Q11. Round the birth year of all pets to the nearest tens place (e.g., 1993 becomes 1990).

Q12. Calculate 2 raised to the power of the length of the pet's name .

Date & Time Functions

Q13. Select the current date, current time, and current timestamp in a single query.

Q14. Extract the birth year, birth month, and birth day for all pets.

Q15. Find all pets that were born in the month of May (Month 5).

Q16. Find all events that occurred in or after the year 1995.

Q17. Extract only the date part from a NOW() system call.

Q18. Demonstrate the difference between NOW() and SYSDATE() by running them with a delay using SLEEP().

grade 12 - practical 14 - 20 july 2026

 WAP to create the below tables where student refers to stream : 

STUDENT:

STREAM:

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

b)Update the sex to 'm' for admission numbers 1001,1003,1005,1007,1010 and 'f' for the others

c)Display the admno , fullname(fname + lname ) and mobile no.

d)Display stream id and stream wise total fee collected

e)Count the number of students from each area

f)Increase the fees of all students  by 10%

g)Display the unique areas from the student table

h)Display details of those students who's name starts with 'R' or last name starts with 'S'

i) Display admno,fullname(fname+lname) , class(class+sec) of all the students that belong to xii a and xii b.

j)Display the count of male students and female students

k)Display the admno, fname and stream name of the student

l) Display the count of male students and female students in class xii

m) Display the fees of students in every class/section

TERM I-REVISION WORKSHEET-2026-27

1. Answer the below ASSERTION(A) AND REASONING(R) based questions. Mark the correct choice as :

(i)Both A and R are true and R is the correct explanation for A.

(ii)Both A and R are true and R is not the correct explanation for R.

(iii)A is true but R is false.

(iv)A is false but R is true.

a) (A): The close() method is used to close the file.

(R): While closing a file, the system frees up all the resources like processor and memory allocated to it.

b)(A): To preserve the data for future purpose in Python is called pickling.

(R): Unpickling is a technique that returns the byte stream produced by pickling back into Python objects.

c) A): CSV stands for Comma separated values.

(R): CSV files are common file format for transferring and storing data

d)(A): Access mode ‘a’ opens the file for appending.

(R): So it writes text only in the end of the file, but if we use seek(0), it will change the file pointer the beginning of the file and write the text there.

e) (A): DDL commands are used to manipulate data in the table.

(R): INSERT and UPDATE are examples of DDL commands.

2) Below two questions are based on sample.txt which contains :

Apple

Banana

Cherry

Dragonfruit

Predict the output of :

i) f = open("sample.txt", "r")

f.read()

print(f.read())

f.close()

ii)  f = open("sample.txt", "r")

print(f.readline(2))

print(f.readline())

print(f.read())

f.close()

3. A table STUDENT has 6 rows and 4 columns.What is the degree and cardinality of the table?

4) def update_data(X, Y):

X.append(30)

Y = Y + [30]

print(X, Y, sep="*")

List1 = [10, 20]

Li st2 = [10, 20]

update_data(List1, List2)

print(List1, List2, sep="#")

5)Observe the following Python function definition and call:

def Assign(P, Q, R=100):

    print(P, Q, R)

Assign(10, 20, P=30)

What will be the outcome of executing this code?

6)Differentiate between :

7) a) Write a function addrec() in Python to add 1 new record at the end of the binary file “Student.dat”, assuming that the binary file contains the following structure : {Roll:<int>,sname:<str>,score:<int>}

b) Increase the score of all the students who scored between 30-34 to 35.

c) Search for a certain roll no , and display the score

d) Display all the records

8) Define a function remove_newline() which will remove new line from sample.txt:

9) Write python code that works on text files :

a) Write a function entry() that accepts10 lines on the uses of python and writes it into data.txt.

b) Write a function summary() that reads data.txt and:

>Counts the number of words that start with ‘re’

>Displays the lines that end with tion (remove the fullstop or question mark before you check)

>Prints all lines that contain the word “python”

>Replace every occurrence of red by RED.

10) A binary file (weather.dat) which contains each record as a list, as given : [“date”,lowest temperature,highest temperature] eg. [“26-08-2023”,30.0,40.0]

a) Update all the records to contain a 4th field – average temperature.

b) Search for a particular date and display its average temperature, If the record is not found, display ‘Record Not Found’.

c) Display all the records and the lowest and highest temperatures recorded

11) The following is a snapshot of customer.csv.

a) Accept records until the user wants to stop.

b) Display all the records in a tabular format. Also the total amount of sales.

c) Search for a serial number and display the corresponding Amount

d) Update all those from Bangalore to Bengaluru.

e) Accept a name delete a row from the above csv file if the record exists.

12. Your parent wishes to maintain their monthly expenses. Write the following python functions:

a) accept () to prompt the user to enter expense details and store them into the file ‘Expense_July.csv’ with appropriate headers :

b) total() to display all the records from Expense_July.csv and also print the total expense for the month.

c) update() to change the school fees to 8000

13)Given the list:

sales = [["Tom", 12000], ["Ava", 8000], ["Kia", 15000], ["Max", 9000]]

Write Python code to:

a) Push the names of salespersons into a stack only if their sales are above 10,000.

b) Display the names from the stack without deleting the elements.

c) Pop the topmost element from the stack

14) Write a SQL query to CREATE a table Faculty with columns:

Fid (INT, PRIMARY KEY)

Name (VARCHAR(30))

Department (VARCHAR(20))

Salary (DECIMAL(8,2))

a) A a new column Email (VARCHAR(40))

b) Change Department column to DeptName

c) Change Name column data type to VARCHAR(20)

d) Remove the column Salary

e)Remove the primary key

f)Make Fid,Name as the new primary key

g)Why is it important to define constraints in a table?

15) Identify and correct the errors in the following SQL statements:

a) CREATE TABLE Emp (ID int, Name varchar, Salary float);

b) ALTER TABLE Emp DROP Salary WHERE ID=3;

c) ALTER Emp RENAME COLUMN Name TO EmpName;

16) Answer the questions that follow the table given below:

a) What is the full form of RDBMS? Give one advantage of RDBMS of CSV file

b) What is the cardinality of the above table ? What is the degree of the above table ?

c) Which is/are the alternate/primary/candidate key/keys in the above table?

d) Identify the below commands as either DDL, or DML. :

INSERT, ALTER, DROP, CREATE, UPDATE, DELETE

17) Answer briefly :

a)referential integrity.

b)primary key and give one example.

c)secondary key with example

d)various constraints

e)what is the various referential actions -set null, no action , cascade and restrict?

f) one difference between CSV files and tables in MySQL.

18) What does cascade do in the below context ?

CREATE TABLE EMPLOYEE (

EmpID INT PRIMARY KEY,

EmpName VARCHAR(30),

DeptID INT,

FOREIGN KEY (DeptID) REFERENCES DEPARTMENT(DeptID) ON DELETE CASCADE

);

Practice for where, group by, having, limit, subqueries

CREATE TABLE sales (
    id INT PRIMARY KEY,
    store VARCHAR(50),
    cat VARCHAR(50),
    amt DECIMAL(10, 2),
    qty INT
);
INSERT INTO sales (id, store, cat, amt, qty) VALUES
(1, 'Downtown', 'Electronics', 1200.00, 2),
(2, 'Downtown', 'Clothing', 150.00, 3),
(3, 'Uptown', 'Electronics', 800.00, 1),
(4, 'Suburbs', 'Clothing', 45.00, 1),
(5, 'Downtown', 'Electronics', 450.00, 1),
(6, 'Uptown', 'Clothing', 300.00, 4),
(7, 'Suburbs', 'Electronics', 1500.00, 3),
(8, 'Downtown', 'Clothing', 80.00, 2),
(9, 'Uptown', 'Books', 35.00, 2),
(10, 'Suburbs', 'Books', 120.00, 5);

Q1. Find all transaction IDs ('id') and product categories ('cat') where the store location is exactly 'Downtown'.

Q2. List all data columns for transactions where the item quantity ('qty') is strictly greater than 2 and the monetary amount ('amt') is under $200.

Q3. Find all sales transactions that do NOT belong to the 'Books' category.

Q4. Calculate the total combined number of items sold ('qty') for each individual product category ('cat').

Q5. Determine the average monetary amount spent per transaction ('amt') at each distinct store location.

Q6. For each unique combination of store and category, find the maximum single transaction amount ('amt').

Q7. Which store locations ('store') have generated an average transaction amount ('amt') greater than $500?

Q8. List the product categories ('cat') that have handled more than 2 individual transaction records.

Q9. Identify the store locations where the cumulative quantity of items sold across all sales records is strictly less than 6.

Q10. Find the single lowest-paying transaction across the entire table. Display all column details.

Q11. Which top 2 product categories ('cat') have generated the most overall cumulative revenue ('amt')? Display the category names and their total revenue values.

Q12. Select all rows for transactions that sold more individual items ('qty') than the global average quantity sold across the entire table.

Q13. Find all individual transactional records that occurred in stores whose total aggregate revenue across all items combined is greater than $1,500.

Q14. Filter out transactions with a quantity ('qty') of 2 or more. Out of those qualifying rows, group the records by product category, filter the grouped categories to keep only those with a combined total revenue greater than $150, and display only the single highest-earning category.

Menagerie database

 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');

1. GROUP BY & HAVING

• Question 1: Write a query to find the total number of pets owned by each owner.

• Question 2: Find all types of species that have more than 2 pets in the database.

• Question 3: List the owners who own more than 2 pets, along with the total count of pets they own.

• Question 4: Calculate the average age of pets for each species, but only include species where the average age is greater than 30 years

2. Equijoins

• Question 5: Write a query to display each pet's name, their species, and the date of any event they participated in.

• Question 6: Find the names of all pets, their owners, and the specific type (type) of events where the remark contains the word "birthday".

• Question 7: List all pets (name and species) who have had a "vet" event, along with the date and the specific remark recorded by the vet.

3. Natural Joins

• Question 8: Using a NATURAL JOIN, retrieve a list of all pets who have recorded events, showing the pet's name, birth date, event date, and remarks.

• Question 9: Write a query using a natural join to find all female pets (sex = 'f') that have experienced an event, displaying their name, species, and the event type.

4. Subqueries

• Question 10: Write a query to find all pets that are younger than the average age of all pets in the database.

• Question 11: Find the names and species of all pets that have never participated in any event 

• Question 12: List the names of all pets that have had an event on the exact same date that 'Fluffy' had an event.

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.

'''