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.

 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