revision worksheet

 https://drive.google.com/file/d/1wLdSR6VDU9dtdUw4FPUAQS-PCiDHRgnA/view

GRADE 12 PRACTICAL 19

 '''WAP using Python-MySQL connectivity to connect to an existing table(created on MySQL) and perform the following :

1 DISPLAY DETAILS OF FEMALE PASSENGERS WHO ARE MORE THAN 30 YEARS OLD and ALSO THE TOTAL NUMBER OF SUCH PASSENGERS

2.UPDATE TRAIN NO OF A PARTICULAR PASSENGER

3.DELETE RECORD WHERE PASSENGER NAME HAS SHARMA

TABLE : PASSENGER, DATABASE: PRACT19

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

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

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

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

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

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

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

DO NOT WRITE THE GREY PART IN THE NOTE BOOK. JUST PUT A PIC OF THE TABLE IN THE RECORD.

CREATE DATABASE PRACT19; USE PRACT19; CREATE TABLE PASSENGER ( PNR CHAR(4) PRIMARY KEY, TNO INT, PNAME VARCHAR(30), GENDER CHAR(6), AGE INT, TRAVELDATE DATE );

INSERT INTO PASSENGER VALUES ('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'), ('P008', 12030, 'J K SHARMA', 'MALE', 65, '2018-05-09'), ('P009', 12030, 'R SHARMA', 'FEMALE', 58, '2018-05-09');

WRITE ONLY THE BELOW PYTHON CODE

import mysql.connector as my conn = my.MySQLConnection(host='localhost', user='root', password='123456789', database='lab19') if conn.is_connected(): print('Connected to MySQL successfully!') else: print('Connection is unsuccessful!') cur = conn.cursor(buffered=True) # DISPLAY DETAILS OF FEMALE PASSENGERS WHO ARE MORE THAN 30 YEARS OLD print('''To display the details of female passengers who are more than 30 years old and the count of such passengers:''') age = 30 sex = 'female' cur.execute('SELECT * FROM passenger WHERE age > %s AND gender = %s', (age, sex)) recs = cur.fetchall() count = cur.rowcount for a, b, c, d, e, f in recs: print(a, b, c, d, e, f, sep=' | ') print('Total no. of such passengers:', count) cur.reset() print() # UPDATE TRAIN NO OF A PARTICULAR PASSENGER print('To update train no. of a particular passenger:') pnr = input('Enter your booking PNR: ') cur.execute('SELECT * FROM passenger WHERE pnr = %s', (pnr,)) rec = cur.fetchone() if not rec: print('No such record found!') else: print('Passenger found:', rec) newtno = int(input('Enter new Train No: ')) cur.execute('UPDATE passenger SET tno = %s WHERE pnr = %s', (newtno, pnr)) print('Details updated!') cur.reset() conn.commit() print() # DELETE DATA WITH 'SHARMA' print("To delete data with 'SHARMA':") cur.execute('SELECT * FROM passenger WHERE pnr = %s', (pnr,)) rec = cur.fetchone() if not rec: print('No such record found!') else: cur.execute('DELETE FROM passenger WHERE pname LIKE "%sharma%"') print('Data deleted!') cur.reset() conn.commit() print() # DISPLAY ALL RECORDS cur.execute('SELECT * FROM passenger') recs = cur.fetchall() for a, b, c, d, e, f in recs: print(a, b, c, d, e, f, sep=' | ') # Close resources cur.close() conn.close()

GRADE 12: PRACTICAL 18

'''WAP using Python-MySQL connectivity to create the given table and insert values. Create a menu driven program for : MENU: 1.DISPLAY DESIRED NUMBER OF RECORDS 2.DISPLAY TOTAL NUMBER OF ROWS 3.UPDATE SALARY 4.SEARCH EMPLOYEES WITH SALARY ABOVE RS. 50000

5.DELETE RECORD

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 | | 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 | +-------+---------------+---------+--------+-----+------------+ '''

import mysql.connector as my

conn=my.connect(host='localhost',user='root',password='123456')

if conn.is_connected():

    print('Connected to MySQL Successfully')

else:

    print('Connection is unsuccessful')

cur=conn.cursor(buffered=True)#this ensures that the cursor error doesn't occur even if all records are not read

cur.execute('drop database if exists database18')

cur.execute('create database database18')

cur.execute('use database18')

cur.execute('''create table employee

(ECODE int primary key,ENAME char(30),

DEPCODE int, SALARY int, AGE int, JOINDATE date)''')

cur.execute('''insert into employee values

(15,'SAMEER SHARMA',123,75000,39,'2007-04-01'),

(21,'RAGVINDER K',101,86000,29,'2005-11-11'),

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

conn.commit()

def menu():

    while True :

        ch=int (input("""

1.DISPLAY DESIRED NUMBER OF RECORDS

2.DISPLAY TOTAL NUMBER OF ROWS

3.UPDATE SALARY

4.SEARCH EMPLOYEES WITH SALARY ABOVE RS. 50000

5.DELETE RECORD

6.EXIT

YOUR CHOICE : """))

        if ch==1:

            disp_many()

        elif ch==2:

            tot_rows()

        elif ch==3:

            update()

        elif ch==4:

            search()

        elif ch==5:

            delete()

        else :

            cur.close()

            conn.close()

            break

def disp_many():

    cur.execute("select * from employee")

    no=int(input("Enter no of records you want to display ? "))

    recs = cur.fetchmany(no)

    for a,b,c,d,e,f in recs:

        print(str(a).ljust(5),b.ljust(20),str(c).ljust(5),str(d).ljust(10),str(e).ljust(5),f)

    cur.reset()

def tot_rows():

    cur.execute("select * from employee")

    recs = cur.fetchall()

    print("Total rows : ",cur.rowcount)

    cur.reset()

def update():

    ecode= int(input("Enter emp code whose sal you want to update"))

    cur.execute("select * from employee where ecode=%s",(ecode,))

    rec = cur.fetchone()

    cur.reset()

    if not rec:

        print('Ecode not found')

        return

    print("Record found : ",rec)

    sal=int(input("Enter revised salary"))

    cur.execute("""update employee set salary = %s

where ecode=%s""",(sal,ecode))

    cur.reset()

    conn.commit()

def search():

    print("Employees with SALARY > 50000")

    cur.execute("select * from employee where salary>50000")

    recs = cur.fetchall()  

    if not recs:

        print('NONE')

        cur.reset()

        return

    for a,b,c,d,e,f in recs:      print(str(a).ljust(5),b.ljust(20),str(c).ljust(5),str(d).ljust(10),str(e).ljust(5),f)

    cur.reset()

def delete():

    ecode= int(input("Enter emp code whose sal you want to delete"))

    cur.execute("select * from employee where ecode=%s",(ecode,))

    rec = cur.fetchone()

    cur.reset()

    if not rec:

        print('Ecode not found')

        return

    print("Record found : ",rec)

    ans=input("Are you sure you want to delete(y/n)")

    if ans=='n':

        return

    cur.execute("delete from employee where ecode=%s",(ecode,))

    cur.reset()

    conn.commit()

  

menu()

GRADE 12 : PRACTICAL 17

Create the tables given below, and perform the given queries 

ORDERS

ITEM

CREATE LAB17;

USE LAB17;

CREATE TABLE Item (

    ItemID INT PRIMARY KEY,
    ItemName VARCHAR(50),
    Category VARCHAR(30),
    Price DECIMAL(10,2),
    StockQty INT
);

INSERT INTO Item (ItemID, ItemName, Category, Price, StockQty) VALUES

(101, 'Pen', 'Stationery', 10.00, 150),

(102, 'Notebook', 'Stationery', 50.00, 100),

(103, 'Water Bottle', 'Accessories', 120.00, 75),

(104, 'Pencil Box', 'Stationery', 80.00, 60),

(105, 'Backpack', 'Bags', 500.00, 30);

CREATE TABLE Orders (

    OrderID INT ,

    OrderDate DATE,

    CustomerName VARCHAR(50),

    ItemID INT,

    Quantity INT,

    PRIMARY KEY(OrderID,ItemID),

    FOREIGN KEY (ItemID) REFERENCES Item(ItemID)

);

INSERT INTO Orders (OrderID, OrderDate, CustomerName, ItemID, Quantity) VALUES

(201, '2025-07-15', 'Riya Sharma', 101, 3),

(202, '2025-07-17', 'Neha Sinha', 102, 2),

(202, '2025-07-17', 'Neha Sinha', 103, 1),

(203, '2025-07-18', 'Isha Verma', 104, 1),

(204, '2025-07-17', 'Kanye West', 103, 1);

1. Show each customer's total bill amount

SELECT OrderID,CustomerName,sum(Quantity*Price) as Total

FROM Orders NATURAL JOIN Item GROUP BY OrderId,CustomerName;

2. Display items where Total quantity ordered per item(across orders) is more than 1

SELECT itemid,itemname,sum(quantity) FROM item NATURAL JOIN orders

GROUP BY itemid,itemname having sum(quantity)>1;

3. Show the order id, orderdate , customer name and item name of the items ordered on 17-07-2025

SELECT orderid,orderdate,customername,itemname FROM orders NATURAL JOIN item

WHERE orderdate='2025-07-17';

4. List all orders with item names in descending order of quantity

SELECT orderid,itemname,quantity FROM orders NATURAL JOIN item

ORDER BY quantity DESC;

5. Decrease the stock for pen by 5

 UPDATE item SET StockQty=StockQty-5 WHERE

 Itemname='Pen';

6. Show categories and number of items in that category sorted by average price descending 

SELECT Category, COUNT(*) as No_of_Items, Avg(Price)

FROM item GROUP BY Category ORDER BY Avg(Price) DESC;

7. Show all items with second letter as 'a'

 SELECT * FROM item WHERE ItemName LIKE '_a%';

 

8. Find the item with the maximum price

SELECT itemName, price FROM item WHERE price =

(SELECT max(price) FROM item);

9. Show the most recently placed order

SELECT * FROM orders WHERE orderdate= (SELECT max(orderdate)

FROM orders);

10. Delete order entries where pencil box is bought.

DELETE FROM orders WHERE ItemId=(SELECT ItemID FROM item WHERE

itemname='pencil box');