GRADE 12 : PRACTICAL 21

 GRADE 12 : PRACTICAL 21

'''WAP using Python-MySQL connectivity to connect to the Database DB21 that contains Table ACTIVITY as shown below : 

PS. - THE BELOW SHOULD BE CREATED BY YOU IN MYSQL & CAN TABLE CAN BE STUCK ALONGSIDE OR BELOW , INSTEAD OF WRITING IT OUT

TABLE : ACTIVITY

ACODE| ANAME         | STADIUM     | NO | PRIZE | SCHEDULE_DATE  

---------------------------------------------------------------------

1001  | RELAY 100M    | STAR ANNEX  | 16 | 10000 | 2004-01-23

1002  | HIGH JUMP     | STAR ANNEX  | 10 | 12000 | 2003-12-12

1003  | SHOTPUT       | SUPER POWER | 12 | 8000  | 2004-02-14

1005  | LONG JUMP     | STAR ANNEX  | 12 | 9000  | 2004-01-01

1008  | DISCUS THROW  | SUPER POWER | 10 | 15000 | 2004-03-19

---------------------------------------------------------------------

Your task is to : 

1.DISPLAY THE ACTIVITIES THAT ARE SCHEDULED AT A STADIUM ACCEPTED BY THE USER. ALSO DISPLAY TOTAL NUMBER OF ACTIVITIES AT THIS STADIUM

2.INSERT A RECORD INTO THE TABLE

3.UPDATE NO. OF PARTICIPANTS AFTER ACCEPTING THE ACTIVITY NAME

4.DELETE DATA AFTER ACCEPTING ACTIVITY CODE FROM USER

- '''

GRADE 12 : PRACTICAL 20 : PYTHON MYSQL CONNECTIVITY

 '''WAP using Python-MySQL connectivity to connect to the database DB20 and do the following :

> DISPLAY ALL RECORDS AND TOTAL NUMBER OF ROWS

> UPDATE THE TRAINS HAVING START AS 'PUNE JUNCTION' TO 'MUMBAI' INSTEAD

> DELETE DATA WHOSE END IS ACCEPTED.

> DISPLAY RECORDS OF A REQURED TNO.

P.S DB20 contains the following table

TABLE : TRAIN

 TNO  | TNAME                | START              | END 

-----------------------------------------------------------------------

11096 | AHIMSA EXPRESS       | PUNE JUNCTION      | AHMEDABAD JUNCTION

12015 | AJMER SHATABDI       | NEW DELHI          | AJMER JUNCTION

16513 | PUNE HBJ SPECIAL     | PUNE JUNCTION      | HABIBGANJ

13005 | AMRITSAR MAIL        | HOWRAH JUNCTION    | AMRITSAR JUNCTION  

12002 | BHOPAL SHATABDI      | NEW DELHI          | HABIBGANJ

12417 | PRAYAG RAJ EXPRESS   | ALLAHABAD JUNCTION | NEW DELHI

14673 | SHAHEED EXPRESS      | JAYNAGAR           | AMRITSAR JUNCTION

12314 | SEALDAH RAJDHANI     | NEW DELHI          | SEALDAH

12498 | SHANE PUNJAB         | AMRITSAR JUNCTION  | NEW DELHI 

12451 | SHRAM SHAKTI EXPRESS | KANPUR CENTRAL     | NEW DELHI

12030 | SWARNA SHATABDI      | AMRITSAR JUNCTION  | NEW DELHI

---------------------------------------------------------------------- '''

GRADE 12 : LAB 19 : Python MySQL connectivity

 GRADE 12 . LAB 19

 

'''Write a program to connect python with mysql using database connectivity and create the given table with values. Create a menu driven program for:

>display 1 record

>display many records

>display all records

>count the total records

>update the train no of a particular passenger

>delete data with a certain pnr

>display data of all the female passengers whose age is greater than 30

(The below is created in mysql in Database DB19)

TABLE : PASSENGER

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

| PNR | TNO   |    PNAME   | GENDER | AGE | TRAVELDATE |

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

|P001 | 13005 |R N AGARWAL |  MALE  | 45  | 2018-12-25 |

|P002 | 12015 |P TIWARY    |  MALE  | 28  | 2018-11-10 |

|P003 | 12015 |S TIWARI    | FEMALE | 22  | 2018-11-10 |

|P004 | 12030 |S K SAXENA  |  MALE  | 42  | 2018-10-12 |

|P005 | 12030 |S SAXENA    | FEMALE | 35  | 2018-10-12 |

|P006 | 12030 |P SAXENA    | FEMALE | 12  | 2018-10-12 |

|P007 | 13005 |N S SINGH   |  MALE  | 52  | 2018-05-09 |

|P008 | 12030 |J K SHARMA  |  MALE  | 65  | 2018-05-09 |

|P009 | 12030 |R SHARMA    | FEMALE | 58  | 2018-05-09 |

+------------------------------------------------------+  '''

GRADE 12 - LAB 18 - Aug 27 2026

 GRADE 12 - LAB 18

'''WAP using Python-MySQL connectivity to create the given

table and insert values. Create a menu driven program for :

MENU:

1.DISPLAY ONE RECORD

2.DISPLAY ALL RECORDS

3.DISPLAY DESIRED NUMBER OF RECORDS

4.DISPLAY TOTAL NUMBER OF ROWS

5.UPDATE SALARY

6.DELETE RECORD

7.SEARCH EMPLOYEES WITH SALARY ABOVE RS. 50000

TABLE : EMPLOYEE

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

| EOCDE | ENAME         | DEPCODE | salary | AGE  | JOINDATE   |

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

|    15 | SAMEER SHARMA |     123 |  75000 |   39 | 2007-04-01 |

|    21 | RAGVINDER K   |     101 |  86000 |   29 | 2005-11-11 |

|    34 | RAMA GUPTA    |     119 |  52500 |   43 | 2010-02-03 |

|    46 | C R MENON     |     103 |  67000 |   38 | 2004-07-12 |

|    77 | MOHAN KUMAR   |     103 |  63000 |   55 | 2000-11-25 |

|    81 | RAJESH KUMAR  |     119 |  74500 |   48 | 2008-12-11 |

|    89 | SANJEEV P     |     101 |  92600 |   54 | 2009-01-12 |

|    93 | PRAGYA JAIN   |     123 |  32000 |   29 | 2006-08-05 |

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

GRADE 12 - LAB 17 - 20th aug 2026

Consider the tables dept and employee alongside. Create the tables accordingly and write SQL Queries for the same : 

Employee

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

| eno  | name         | doj        | dob        | gender | dcode | salary     |

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

| 1001 | george k     | 2013-09-02 | 1991-09-01 | male   | d01   | 1000000.00 |

| 1002 | ryma sen     | 2012-12-15 | 1990-12-15 | female | d03   | 1500000.00 |

| 1003 | mohitesh     | 2013-02-03 | 1987-09-04 | male   | d05   | 1200000.00 |

| 1004 | manila sahai | 2012-12-09 | 1986-11-14 | female | d01   | 1300000.00 |

| 1005 | r sahay      | 2013-11-18 | 1987-03-31 | male   | d02   | 1400000.00 |

| 1006 | jaya priya   | 2014-06-09 | 1985-06-23 | female | d05   | 1100000.00 |

| 1007 | anil jha     | 2014-01-17 | 1984-10-19 | male   | d04   | 1400000.00 |

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

Dept

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

| dcode | department     | location |

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

| d01   | infrastructure | delhi    |

| d02   | marketing      | delhi    |

| d03   | media          | mumbai   |

| d04   | human resource | mumbai   |

| d05   | finance        | kolkata  |

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

CREATE DATABASE Lab17;

USE Lab18;

CREATE TABLE DEPT (

    dcode CHAR(3) PRIMARY KEY,

    department VARCHAR(30),

    location VARCHAR(30)

);

INSERT INTO DEPT VALUES

('d01', 'infrastructure', 'delhi'),

('d02', 'marketing', 'delhi'),

('d03', 'media', 'mumbai'),

('d04', 'human resource', 'mumbai'),

('d05', 'finance', 'kolkata');

CREATE TABLE EMPLOYEE (

    eno INT PRIMARY KEY,

    name VARCHAR(30),

    doj DATE,

    dob DATE,

    gender CHAR(6),

    dcode CHAR(3),

    salary DECIMAL(12, 2),

    CONSTRAINT fk FOREIGN KEY (dcode) REFERENCES DEPT (dcode)

);

INSERT INTO EMPLOYEE VALUES

(1001, 'George K', '2013-09-02', '1991-09-01', 'male', 'd01', 1000000.00),

(1002, 'Ryma Sen', '2012-12-15', '1990-12-15', 'female', 'd03', 1500000.00),

(1003, 'Mohitesh', '2013-02-03', '1987-09-04', 'male', 'd05', 1200000.00),

(1004, 'Manila Sahai', '2012-12-09', '1986-11-14', 'female', 'd01', 1300000.00),

(1005, 'R Sahay', '2013-11-18', '1987-03-31', 'male', 'd02', 1400000.00),

(1006, 'Jaya Priya', '2014-06-09', '1985-06-23', 'female', 'd05', 1100000.00),

(1007, 'Anil Jha', '2014-01-17', '1984-10-19', 'male', 'd04', 1400000.00);

i) Display Eno, Name, Gender, Dob from the Table Employee in ascending order of age.

ii) To display the name of all male employees from the table employee who were born in the year 1987

iii) To display the count of male and female employees with the headings as gender, and count.

iv) To display the eno and name of all employees who were born between ‘ 1987-01-01’ and ‘1991-12-01’ , the oldest first.

v) Display Name and department of each employee.

vi)Display the count of employees and average salary(rounded to nearest integer) in each department.

vii) Display the employee no, name , location and salary of the employees who work at Mumbai or Delhi and have a salary of more than 1300000.

viii) Display each city and the average salary of corresponding employees where the average salary is greater than 1200000. 

ix) Increase the salary of female employees by 15% and male employees by 12%

x) Display the details (eno, name, salary) of the employee(s) receiving the highest salary in the company.

xi) Display the department code (dcode) and the total salary paid to employees in that department, but only for departments where the total salary exceeds 2000000.

xii)Display the name and salary of all employees who work in the 'delhi' location.

xiii)Display the gender and average salary for each gender, showing only those groups whose average salary is greater than 1250000.

xiv) Display the names of all employees who belong to either the 'infrastructure' or 'finance' department.

xv) Delete all employees from the EMPLOYEE table who work in the 'media' department.

What is the output of the following queries?

i)Select  dcode,count(*) from employee group by dcode having count(*) >1;

ii)Select name, department from employee e, dept d where e.dcode = d.dcode and eno <1003;

iii) Select max(doj),min(dob) from employee;

stack practice

 A stack, named ClrStack, contains records of some colors. Each

record is represented as a tuple containing four elements -

ColorName, RED, GREEN, BLUE. For example, ('Yellow', 237, 250, 68).

Write the following user-defined functions in Python to

perform the specified operations on ClrStack:

(i) push_Clr(ClrStack, new_Clr): This function takes the stack

ClrStack and a new record new_Clr as arguments and pushes this new

record onto the stack.

(ii) pop_Clr(ClrStack): This function pops the topmost record from the

stack and returns it. If the stack is already empty, the function

should display the message "Underflow".

(iii) isEmpty(ClrStack): This function checks whether the stack is

empty. If the stack is empty, the function should return True,

otherwise the function should return False

(iv) display(ClrStack): This function to display all the elements of

the stack by popping it. In the end it should display "Stack Empty"

GRADE 12 : PRACTICAL 16 : 17th August 2026

 Create the below tables and perform the required queries:

FURNITURE:

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

| no | itemname        | type         | dateofstock | price    | discount |

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

|  1 | dolphin         | baby cot     | 19-02-2002  |  9500.00 |       20 |

|  2 | decent          | office table | 01-01-2002  | 25000.00 |       30 |

|  3 | comfort zone    | double bed   | 12-01-2002  | 25000.00 |       25 |

|  4 | donald          | baby cot     | 24-02-2002  |  6500.00 |       15 |

|  5 | royal finish    | office table | 20-02-2002  | 18000.00 |       90 |

|  6 | royal tiger     | sofa         | 22-02-2002  | 31000.00 |       30 |

|  7 | economy sitting | sofa         | 13-12-2001  |  9500.00 |       25 |

|  8 | eating paradise | dining table | 19-02-2002  | 11500.00 |       25 |

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

ARRIVALS:

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

| no | itemname     | type       | dateofstock | price    | discount |

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

|  1 | wood comfort | double bed | 23-03-2003  | 25000.00 |       25 |

|  2 | old fox      | sofa       | 20-03-2003  | 17000.00 |       20 |

|  3 | micky        | baby cot   | 1-4-2003    |  7500.00 |       15 |

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

1)Show all the information about baby cots from the furniture table 2)To list the itemname which are priced at more than 15000 from the furniture table with a heading as ‘Highend_items’ 3)To list itemnames and types of those items in which date of stock is before 22/02/2002 from the furniture table in descending order of the item name 4)To display item name and date of stock of those items in which the discount percentage is less than 20 or more than 25. 5)To display the item type and corresponding count. 6) Insert all the rows from arrivals into the furniture table

7) Delete all the rows from Arrivals table; 8) Display item no, item name, mrp and the discounted price for all items 9) Display all the furniture types that have an average discount percent greater than 20.

10) Display item details whose names start with 'royal' or end with 'comfort'.

11) Display items from furniture whose price is above the average price of all items.

12) Combine all unique item types available in the FURNITURE table.

13) Display the item name and price of the item(s) that have the highest price in the FURNITURE table.

14) Display the item name, price, and year of stock for all items stocked during the year 2002

grade 12 : lab 15 : 7th Aug 2026

 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