Class 12 · Practical file

Class XII Informatics Practices: Practical File (2025-26)

19 Pandas Programs6 Matplotlib Programs23 SQL Queries

About the practical file

The CBSE practical examination for Class XII Informatics Practices carries 30 marks: programs using Pandas and Matplotlib (8), SQL queries (7), the practical file (5), project work (5) and viva-voce (5).

The practical file must include at least 15 programs based on Pandas, 4 based on Matplotlib and 15 SQL queries. This file gives model solutions for all items in the CBSE suggested list (sections 5.1 to 5.3) and more: 19 Pandas programs, 6 Matplotlib programs and 23 SQL tasks.

What each entry in a student's file should have:

Program number, title and aim
The code with comments
The output: printed output for Pandas programs, a printout or neat sketch of each chart for Matplotlib programs, and result tables for SQL queries
Date of the lab session and the teacher's signature

How to assess it: check the file through the year. In the practical exam, ask the student to run one or two programs from the file and to explain a line of code; the viva questions given with each item can be used. Encourage students to replace the sample data with their own (their class result, a local shop, data from data.gov.in) so that every file is original. Charts should always have a title, axis labels and, where there is more than one series, a legend.

All the Pandas outputs here are from real runs and the code avoids features removed in recent pandas versions, so it works on pandas 1.x, 2.x and 3.x. The Matplotlib code was run and the charts checked; the result field describes each chart. SQL statements are MySQL syntax, run in order on a MySQL-compatible server, so later outputs reflect earlier INSERT, DELETE and UPDATE commands.

Criterion	What the teacher checks	Marks
Pandas programs	At least 15 programs covering Series, DataFrames and CSV import/export; correct code with real outputs	2
Matplotlib programs	At least 4 charts (line, bar, histogram) with title, labels and legend	1
SQL queries	At least 15 queries covering functions, GROUP BY, HAVING, ORDER BY and joins, with outputs	1
Documentation and regularity	Index, aims, dates, signatures, original data, submitted on time	1
Total		5

Pandas Programs

1Series from a dictionary and an ndarray

Create a pandas Series from a dictionary (number of students in each school house) and another Series from a NumPy ndarray (sample maximum temperatures of four cities) with your own index. (CBSE list 5.1, item 1)

Solution

Python

import pandas as pd
import numpy as np

# From a dictionary: the keys become the index
house = {"Tagore": 48, "Raman": 39, "Kalam": 31, "Sarojini": 42}
s1 = pd.Series(house)
print("Series from dictionary:")
print(s1)

# From an ndarray: we supply the index
temps = np.array([41.2, 38.5, 44.0, 36.8])
s2 = pd.Series(temps, index=["Jaipur", "Bhopal", "Nagpur", "Pune"])
print("\nSeries from ndarray:")
print(s2)

Output

Series from dictionary:
Tagore      48
Raman       39
Kalam       31
Sarojini    42
dtype: int64

Series from ndarray:
Jaipur    41.2
Bhopal    38.5
Nagpur    44.0
Pune      36.8
dtype: float64

Viva questions (4)

Where does the index come from when a Series is made from a dictionary? From the keys of the dictionary.
What is the default index if none is given for an ndarray? 0, 1, 2, ... (a RangeIndex).
Why is the dtype of s2 float64? The ndarray holds decimal numbers.
Is a Series size-mutable? No; its values can be changed, but not its size in place.

2Series from a scalar and a list; Series attributes

Create a Series from a scalar value (the same fee for every student) and a Series from a list with a name. Display the attributes index, values, size, name and empty.

Solution

Python

import pandas as pd

# Scalar value: repeated for every index label
fee = pd.Series(500, index=["Ravi", "Neha", "Joseph", "Simran"])
print(fee)

charges = pd.Series([1200, 1500, 900], index=["Bus", "Lab", "Library"], name="Charges")
print(charges)
print("Index  :", list(charges.index))
print("Values :", charges.values)
print("Size   :", charges.size)
print("Name   :", charges.name)
print("Empty? :", charges.empty)

Output

Ravi      500
Neha      500
Joseph    500
Simran    500
dtype: int64
Bus        1200
Lab        1500
Library     900
Name: Charges, dtype: int64
Index  : ['Bus', 'Lab', 'Library']
Values : [1200 1500  900]
Size   : 3
Name   : Charges
Empty? : False

Viva questions (3)

Is an index required when creating a Series from a scalar? Yes, it decides how many times the value is repeated.
What does the values attribute return? A NumPy array of the data.
What is the difference between size and count()? size counts all elements; count() leaves out NaN values.

3Mathematical operations on Series

Two Series hold the marks of students in Term 1 and Term 2. Add them, find the average, and see how pandas handles labels that are present in only one Series. Use add() with fill_value to avoid NaN.

Solution

Python

import pandas as pd

t1 = pd.Series([78, 85, 62, 90], index=["Asha", "Bilal", "Charu", "Dev"])
t2 = pd.Series([82, 79, 70, 88], index=["Asha", "Charu", "Dev", "Esha"])

print("t1 + t2 (labels are matched):")
print(t1 + t2)
print("\nt1.add(t2, fill_value=0):")
print(t1.add(t2, fill_value=0))
print("\nAverage of the two terms:")
print((t1 + t2) / 2)
print("\nTerm 1 marks out of 50:")
print(t1 / 2)

Output

t1 + t2 (labels are matched):
Asha     160.0
Bilal      NaN
Charu    141.0
Dev      160.0
Esha       NaN
dtype: float64

t1.add(t2, fill_value=0):
Asha     160.0
Bilal     85.0
Charu    141.0
Dev      160.0
Esha      88.0
dtype: float64

Average of the two terms:
Asha     80.0
Bilal     NaN
Charu    70.5
Dev      80.0
Esha      NaN
dtype: float64

Term 1 marks out of 50:
Asha     39.0
Bilal    42.5
Charu    31.0
Dev      45.0
dtype: float64

Viva questions (3)

Why do Bilal and Esha get NaN in t1 + t2? Each appears in only one Series, so there is no matching value to add.
What does fill_value=0 do? It treats a missing value as 0 before the operation.
Why did the dtype change to float64? NaN is a float value, so the whole result becomes float.

4head(), tail(), indexing and slicing a Series

A Series stores a batter's runs in eight matches. Use head() and tail(), select values by label and by position, and slice the Series both ways.

Solution

Python

import pandas as pd

runs = pd.Series([34, 72, 8, 101, 45, 0, 66, 19],
                 index=["M1", "M2", "M3", "M4", "M5", "M6", "M7", "M8"])

print("First 3 matches:")
print(runs.head(3))
print("\nLast 2 matches:")
print(runs.tail(2))
print("\nRuns in M4 (label):", runs["M4"])
print("Runs in first match (position):", runs.iloc[0])
print("\nSelected matches:")
print(runs[["M2", "M5"]])
print("\nLabel slice M3 to M5 (end included):")
print(runs["M3":"M5"])
print("\nPosition slice 2 to 5 (end excluded):")
print(runs.iloc[2:5])

Output

First 3 matches:
M1    34
M2    72
M3     8
dtype: int64

Last 2 matches:
M7    66
M8    19
dtype: int64

Runs in M4 (label): 101
Runs in first match (position): 34

Selected matches:
M2    72
M5    45
dtype: int64

Label slice M3 to M5 (end included):
M3      8
M4    101
M5     45
dtype: int64

Position slice 2 to 5 (end excluded):
M3      8
M4    101
M5     45
dtype: int64

Viva questions (3)

How many rows does head() show by default? 5.
Is the end label included in a label slice? Yes; in a position slice the end is excluded.
Why do we use runs.iloc[0] instead of runs[0]? With a text index, iloc makes it clear that 0 is a position; plain [0] on labels is not allowed in newer pandas.

5Elements above the 75th percentile

Given a Series of marks, print all the elements that are above the 75th percentile. (CBSE list 5.1, item 2)

Solution

Python

import pandas as pd

marks = pd.Series([67, 89, 45, 92, 78, 55, 81, 73, 98, 60],
                  index=["Anu", "Ben", "Chirag", "Dia", "Ekta",
                         "Farhan", "Gita", "Hari", "Isha", "Jai"])

q3 = marks.quantile(0.75)
print("75th percentile:", q3)
print("Students above the 75th percentile:")
print(marks[marks > q3])

Output

75th percentile: 87.0
Students above the 75th percentile:
Ben     89
Dia     92
Isha    98
dtype: int64

Viva questions (3)

What does quantile(0.75) return? The value below which 75% of the data lies.
What is marks > q3? A Boolean Series with True where the mark is above q3.
How would you find the median with quantile? marks.quantile(0.5).

6Boolean conditions on a Series

A Series stores the maximum temperature of a city for a week. Display the days hotter than 40 degrees, count them, and display the days that were not above 38 degrees.

Solution

Python

import pandas as pd

temp = pd.Series([38.4, 41.2, 42.0, 39.7, 40.5, 37.9, 41.8],
                 index=["Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"])

hot = temp[temp > 40]
print("Days above 40 degrees:")
print(hot)
print("Number of such days:", hot.count())
print("\nDays not above 38 degrees:")
print(temp[~(temp > 38)])
print("\nHottest day:", temp.idxmax(), "with", temp.max())

Output

Days above 40 degrees:
Tue    41.2
Wed    42.0
Fri    40.5
Sun    41.8
dtype: float64
Number of such days: 4

Days not above 38 degrees:
Sat    37.9
dtype: float64

Hottest day: Wed with 42.0

Viva questions (3)

What does the ~ operator do on a Boolean Series? It reverses True and False.
What does idxmax() return? The index label of the largest value.
Is the original Series changed by temp[temp > 40]? No; a new Series is returned.

7DataFrame from a dictionary of Series

Create a DataFrame of marks from a dictionary whose values are Series (English and IP marks). Note how labels missing in one Series are handled.

Solution

Python

import pandas as pd

english = pd.Series([78, 85, 92], index=["Asha", "Bilal", "Charu"])
ip = pd.Series([88, 91, 76, 69], index=["Asha", "Bilal", "Charu", "Dev"])

df = pd.DataFrame({"English": english, "IP": ip})
print(df)
print("\nShape:", df.shape)

Output

       English  IP
Asha      78.0  88
Bilal     85.0  91
Charu     92.0  76
Dev        NaN  69

Shape: (4, 2)

Viva questions (3)

What becomes the row index of this DataFrame? The union of the indexes of the two Series.
Why is Dev's English mark NaN? Dev is not present in the English Series.
What do the dictionary keys become? Column labels.

8DataFrame from a list of dictionaries

Create a DataFrame from a list of dictionaries, each describing a library book issued to a student. One dictionary has an extra key.

Solution

Python

import pandas as pd

issues = [
    {"BookID": "B101", "Title": "Wings of Fire", "Student": "Tanvi", "Days": 12},
    {"BookID": "B205", "Title": "Python Basics", "Student": "Arif", "Days": 5},
    {"BookID": "B317", "Title": "Malgudi Days", "Student": "Nikhil", "Days": 20, "Fine": 30},
]
df = pd.DataFrame(issues)
print(df)
print("\nColumns:", list(df.columns))

Output

  BookID          Title Student  Days  Fine
0   B101  Wings of Fire   Tanvi    12   NaN
1   B205  Python Basics    Arif     5   NaN
2   B317   Malgudi Days  Nikhil    20  30.0

Columns: ['BookID', 'Title', 'Student', 'Days', 'Fine']

Viva questions (3)

What does each dictionary become? One row of the DataFrame.
Why do the first two rows have NaN for Fine? Their dictionaries have no Fine key.
How are the columns ordered? In the order the keys first appear in the list.

9Quarterly sales: total expenditure per category

Create a DataFrame of quarterly purchases of a school store where each row has the item category, item name and expenditure. Group the rows by category and print the total expenditure per category. (CBSE list 5.1, item 3)

Solution

Python

import pandas as pd

sales = pd.DataFrame({
    "Category": ["Stationery", "Electronics", "Stationery", "Sports",
                 "Electronics", "Sports", "Stationery"],
    "Item": ["Notebooks", "Calculators", "Pens", "Footballs",
             "Headphones", "Cricket bats", "Geometry boxes"],
    "Expenditure": [12500, 18400, 6200, 9800, 15600, 14250, 4800]})
print(sales)

total = sales.groupby("Category")["Expenditure"].sum()
print("\nTotal expenditure per category:")
print(total)
print("\nHighest spending category:", total.idxmax())

Output

      Category            Item  Expenditure
0   Stationery       Notebooks        12500
1  Electronics     Calculators        18400
2   Stationery            Pens         6200
3       Sports       Footballs         9800
4  Electronics      Headphones        15600
5       Sports    Cricket bats        14250
6   Stationery  Geometry boxes         4800

Total expenditure per category:
Category
Electronics    34000
Sports         24050
Stationery     23500
Name: Expenditure, dtype: int64

Highest spending category: Electronics

Viva questions (3)

What does groupby('Category') do? It splits the rows into groups having the same category.
Why do we select ['Expenditure'] before sum()? So that only the numeric column is added; text columns cannot be summed.
In what order are the groups shown? Sorted by the group key (alphabetically here).

10Exam result: labels, data types and dimensions

Create a DataFrame for an examination result and display its row labels, column labels, the data type of each column and its dimensions. (CBSE list 5.1, item 4)

Note for teachers: pandas 3 shows the text column Name as str; pandas 1.x and 2.x show it as object.

Solution

Python

import pandas as pd

result = pd.DataFrame({
    "Name": ["Kriti", "Yash", "Zoya", "Om"],
    "English": [84, 72, 91, 66],
    "Maths": [95, 68, 88, 74],
    "IP": [97, 81, 93, 79],
    "Percent": [92.0, 73.67, 90.67, 73.0]},
    index=["R1", "R2", "R3", "R4"])
print(result)
print("\nRow labels   :", list(result.index))
print("Column labels:", list(result.columns))
print("\nData types:")
print(result.dtypes)
print("\nDimensions (ndim):", result.ndim)
print("Shape (rows, columns):", result.shape)
print("Number of values (size):", result.size)

Output

     Name  English  Maths  IP  Percent
R1  Kriti       84     95  97    92.00
R2   Yash       72     68  81    73.67
R3   Zoya       91     88  93    90.67
R4     Om       66     74  79    73.00

Row labels   : ['R1', 'R2', 'R3', 'R4']
Column labels: ['Name', 'English', 'Maths', 'IP', 'Percent']

Data types:
Name           str
English      int64
Maths        int64
IP           int64
Percent    float64
dtype: object

Dimensions (ndim): 2
Shape (rows, columns): (4, 5)
Number of values (size): 20

Viva questions (3)

What does shape return? A tuple (number of rows, number of columns).
What is the difference between size and len(df)? size is rows x columns; len() gives only the number of rows.
Why is Percent float64 but English int64? Percent has decimal values; English has only whole numbers.

11Filtering rows: duplicates and conditions

Students registered for the sports day, and some entries were made twice. Find the duplicate rows, remove them, and then filter the clean data by different criteria. (CBSE list 5.1, item 5)

Solution

Python

import pandas as pd

entries = pd.DataFrame({
    "Name":  ["Ritu", "Kunal", "Ritu", "Sam", "Neel", "Kunal", "Asha"],
    "Event": ["100 m", "Long jump", "100 m", "Relay", "100 m", "Relay", "Long jump"],
    "House": ["Tagore", "Raman", "Tagore", "Kalam", "Raman", "Raman", "Kalam"]})

print("Duplicate rows:")
print(entries[entries.duplicated()])

clean = entries.drop_duplicates()
print("\nAfter removing duplicates:")
print(clean)

print("\nEntries for 100 m:")
print(clean[clean["Event"] == "100 m"])

print("\nRaman house entries other than Relay:")
print(clean[(clean["House"] == "Raman") & (clean["Event"] != "Relay")])

Output

Duplicate rows:
   Name  Event   House
2  Ritu  100 m  Tagore

After removing duplicates:
    Name      Event   House
0   Ritu      100 m  Tagore
1  Kunal  Long jump   Raman
3    Sam      Relay   Kalam
4   Neel      100 m   Raman
5  Kunal      Relay   Raman
6   Asha  Long jump   Kalam

Entries for 100 m:
   Name  Event   House
0  Ritu  100 m  Tagore
4  Neel  100 m   Raman

Raman house entries other than Relay:
    Name      Event  House
1  Kunal  Long jump  Raman
4   Neel      100 m  Raman

Viva questions (4)

Which row is marked as a duplicate by duplicated()? The second and later copies; the first copy is kept as original.
Why is Kunal not removed though his name appears twice? His two rows differ in Event, so they are not duplicate rows.
Why are brackets needed around each condition with &? & has higher precedence than == and !=.
How would you remove repeated names only? drop_duplicates(subset=['Name']).

12Add, rename and delete rows and columns

Start with a DataFrame of marks. Add a Total column and a new row, rename a column and a row label, then delete a row and a column.

Solution

Python

import pandas as pd

df = pd.DataFrame({"Maths": [88, 64, 79], "Science": [91, 70, 85]},
                  index=["Arjun", "Bela", "Dev"])
df["Total"] = df["Maths"] + df["Science"]          # add a column
df.loc["Esha"] = [72, 80, 152]                    # add a row
print(df)

df = df.rename(columns={"Maths": "Mathematics"}, index={"Dev": "Devansh"})
print("\nAfter renaming:")
print(df)

df = df.drop("Bela")                              # delete a row
df = df.drop(columns="Total")                     # delete a column
print("\nAfter deleting row Bela and column Total:")
print(df)

Output

       Maths  Science  Total
Arjun     88       91    179
Bela      64       70    134
Dev       79       85    164
Esha      72       80    152

After renaming:
         Mathematics  Science  Total
Arjun             88       91    179
Bela              64       70    134
Devansh           79       85    164
Esha              72       80    152

After deleting row Bela and column Total:
         Mathematics  Science
Arjun             88       91
Devansh           79       85
Esha              72       80

Viva questions (4)

How is a new column added? By assigning to a new label: df['Total'] = ...
How is a new row added? With df.loc['label'] = list of values.
Does rename() change the DataFrame in place? No, it returns a new DataFrame unless inplace=True is used.
What is axis 0 and axis 1? Axis 0 refers to rows, axis 1 to columns.

13Iterating over a DataFrame

Use iterrows() to print each student's result row by row, and items() to go through the DataFrame column by column.

Solution

Python

import pandas as pd

df = pd.DataFrame({"Name": ["Aman", "Bindu", "Chris"],
                   "Marks": [72, 28, 91]}, index=[1, 2, 3])

print("Row-wise with iterrows():")
for idx, row in df.iterrows():
    status = "Pass" if row["Marks"] >= 33 else "Fail"
    print(idx, row["Name"], row["Marks"], status)

print("\nColumn-wise with items():")
for col, values in df.items():
    print(col, values.tolist())

Output

Row-wise with iterrows():
1 Aman 72 Pass
2 Bindu 28 Fail
3 Chris 91 Pass

Column-wise with items():
Name ['Aman', 'Bindu', 'Chris']
Marks [72, 28, 91]

Viva questions (3)

What does iterrows() give in each step? A pair: the row label and the row as a Series.
What does items() give in each step? A pair: the column label and the column as a Series.
Why is iteritems() not used? It was removed in pandas 2.0; items() is the correct method.

14Selecting with loc and iloc

Use label-based (loc) and position-based (iloc) indexing to select single values, rows, columns and slices from a DataFrame of state-wise data.

Solution

Python

import pandas as pd

df = pd.DataFrame({"Capital": ["Bhopal", "Jaipur", "Patna", "Shimla", "Ranchi"],
                   "Schools": [520, 610, 480, 150, 300],
                   "Students": [18000, 21000, 17500, 4200, 9800]},
                  index=["MP", "RJ", "BR", "HP", "JH"])

print("Capital of RJ:", df.loc["RJ", "Capital"])
print("\nRows BR to HP, two columns (loc):")
print(df.loc["BR":"HP", ["Capital", "Students"]])
print("\nFirst two rows, last two columns (iloc):")
print(df.iloc[0:2, 1:3])
print("\nThird row as a Series:")
print(df.iloc[2])

Output

Capital of RJ: Jaipur

Rows BR to HP, two columns (loc):
   Capital  Students
BR   Patna     17500
HP  Shimla      4200

First two rows, last two columns (iloc):
    Schools  Students
MP      520     18000
RJ      610     21000

Third row as a Series:
Capital     Patna
Schools       480
Students    17500
Name: BR, dtype: object

Viva questions (3)

Is the end label included in loc slicing? Yes; in iloc slicing the end position is excluded.
What does df.iloc[0:2, 1:3] select? Rows at positions 0 and 1, columns at positions 1 and 2.
What is returned when a single row is selected? A Series whose index is the column labels.

15Boolean indexing on a DataFrame

From a DataFrame of employees of a small firm, display the rows that satisfy conditions combined with & (and), | (or) and ~ (not).

Solution

Python

import pandas as pd

emp = pd.DataFrame({"Name": ["Neha", "Ravi", "Sara", "Tom", "Uday", "Vani"],
                    "Dept": ["Sales", "IT", "IT", "HR", "Sales", "IT"],
                    "Salary": [42000, 65000, 58000, 39000, 47000, 71000],
                    "Exp": [3, 8, 5, 2, 6, 10]})

print("IT staff earning above 60000:")
print(emp[(emp["Dept"] == "IT") & (emp["Salary"] > 60000)])
print("\nHR staff or anyone with less than 3 years:")
print(emp[(emp["Dept"] == "HR") | (emp["Exp"] < 3)])
print("\nEveryone not in Sales:")
print(emp[~(emp["Dept"] == "Sales")])

Output

IT staff earning above 60000:
   Name Dept  Salary  Exp
1  Ravi   IT   65000    8
5  Vani   IT   71000   10

HR staff or anyone with less than 3 years:
  Name Dept  Salary  Exp
3  Tom   HR   39000    2

Everyone not in Sales:
   Name Dept  Salary  Exp
1  Ravi   IT   65000    8
2  Sara   IT   58000    5
3   Tom   HR   39000    2
5  Vani   IT   71000   10

Viva questions (3)

Why can we not use the keywords and/or between conditions on a DataFrame? They work on single True/False values; & and | work element by element.
What type of object is emp['Exp'] < 3? A Boolean Series.
Does boolean indexing keep the original row labels? Yes.

16Sorting, head()/tail() and conditional update

Sort a class result by percentage, display the top three and bottom two students, and add a Result column that is 'Pass' or 'Fail' using loc with a condition.

Solution

Python

import pandas as pd

df = pd.DataFrame({"Name": ["Gopal", "Heena", "Irfan", "Jaya", "Karan", "Lata"],
                   "Percent": [68.5, 91.2, 29.0, 77.4, 84.0, 31.5]})

ranked = df.sort_values("Percent", ascending=False)
print("Top three:")
print(ranked.head(3))
print("\nBottom two:")
print(ranked.tail(2))

df["Result"] = "Pass"
df.loc[df["Percent"] < 33, "Result"] = "Fail"
print("\nWith result column:")
print(df)

Output

Top three:
    Name  Percent
1  Heena     91.2
4  Karan     84.0
3   Jaya     77.4

Bottom two:
    Name  Percent
5   Lata     31.5
2  Irfan     29.0

With result column:
    Name  Percent Result
0  Gopal     68.5   Pass
1  Heena     91.2   Pass
2  Irfan     29.0   Fail
3   Jaya     77.4   Pass
4  Karan     84.0   Pass
5   Lata     31.5   Fail

Viva questions (3)

What does ascending=False do in sort_values()? Sorts from the largest value to the smallest.
Why do we use df.loc[condition, 'Result'] = 'Fail'? It updates only the matching rows in one step and works in all pandas versions.
Does sort_values() change df? No, it returns a sorted copy; df keeps its original order.

17Exporting a DataFrame to CSV and importing it back

Save a DataFrame of student fees to a CSV file, display the file's contents, and read it back into a new DataFrame. (CBSE list 5.1, item 6)

Solution

Python

import pandas as pd

fees = pd.DataFrame({"AdmNo": [3011, 3012, 3013, 3014],
                     "Name": ["Ira", "Jatin", "Kavya", "Lakshay"],
                     "Paid": [24000, 18000, 24000, 12000]})

fees.to_csv("fees.csv", index=False)          # export
print("Contents of fees.csv:")
with open("fees.csv") as f:
    print(f.read(), end="")

back = pd.read_csv("fees.csv")                # import
print("\nDataFrame read from CSV:")
print(back)
print("\nTotal fee collected:", back["Paid"].sum())

Output

Contents of fees.csv:
AdmNo,Name,Paid
3011,Ira,24000
3012,Jatin,18000
3013,Kavya,24000
3014,Lakshay,12000

DataFrame read from CSV:
   AdmNo     Name   Paid
0   3011      Ira  24000
1   3012    Jatin  18000
2   3013    Kavya  24000
3   3014  Lakshay  12000

Total fee collected: 78000

Viva questions (3)

What does index=False do in to_csv()? It stops pandas from writing the row labels as an extra column.
What is a CSV file? A plain text file in which values are separated by commas, one record per line.
Which function reads a CSV file into a DataFrame? pd.read_csv().

18read_csv() and to_csv() options

Read a stock file of a school canteen using the options index_col, usecols and nrows, then export only the items with low stock to another CSV file with selected columns and a different separator.

Solution

Python

import pandas as pd

# Create the input file (in the lab, make it in Notepad or a spreadsheet)
with open("stock.csv", "w") as f:
    f.write("Code,Item,Price,Qty\n"
            "C1,Samosa,15,40\nC2,Sandwich,30,12\nC3,Juice,25,8\n"
            "C4,Idli,20,35\nC5,Muffin,35,6\n")

df = pd.read_csv("stock.csv", index_col="Code")
print(df)

print("\nOnly Item and Qty, first 3 rows:")
print(pd.read_csv("stock.csv", usecols=["Item", "Qty"], nrows=3))

low = df[df["Qty"] < 10]
low.to_csv("reorder.csv", columns=["Item", "Qty"], sep=";")
print("\nContents of reorder.csv:")
with open("reorder.csv") as f:
    print(f.read(), end="")

Output

          Item  Price  Qty
Code                      
C1      Samosa     15   40
C2    Sandwich     30   12
C3       Juice     25    8
C4        Idli     20   35
C5      Muffin     35    6

Only Item and Qty, first 3 rows:
       Item  Qty
0    Samosa   40
1  Sandwich   12
2     Juice    8

Contents of reorder.csv:
Code;Item;Qty
C3;Juice;8
C5;Muffin;6

Viva questions (3)

What does index_col='Code' do? It uses the Code column as the row labels.
What does usecols do? It reads only the listed columns.
Why might you use sep=';'? Some files use a semicolon instead of a comma, for example when values contain commas.

19School result analysis: subject-wise and section-wise

Analyse a Class XII result: find the subject-wise average, the section-wise average in each subject, the topper in each subject, and the top three students overall. (Data for CBSE list 5.2, items 1 and 2)

Solution

Python

import pandas as pd

res = pd.DataFrame({
    "Name": ["Aditi", "Bhanu", "Chetna", "Daksh", "Ela", "Faiz", "Gauri", "Harsh"],
    "Section": ["A", "A", "A", "A", "B", "B", "B", "B"],
    "English": [82, 74, 91, 66, 88, 59, 77, 84],
    "Maths": [95, 68, 72, 81, 90, 64, 70, 99],
    "IP": [97, 85, 88, 79, 92, 71, 83, 94]})
subjects = ["English", "Maths", "IP"]

print("Subject-wise average:")
print(res[subjects].mean().round(2))

print("\nSection-wise average:")
print(res.groupby("Section")[subjects].mean().round(2))

print("\nTopper in each subject:")
for sub in subjects:
    print(sub, "->", res.loc[res[sub].idxmax(), "Name"], res[sub].max())

res["Total"] = res[subjects].sum(axis=1)
print("\nTop three overall:")
print(res.sort_values("Total", ascending=False).head(3)[["Name", "Section", "Total"]])

Output

Subject-wise average:
English    77.62
Maths      79.88
IP         86.12
dtype: float64

Section-wise average:
         English  Maths     IP
Section                       
A          78.25  79.00  87.25
B          77.00  80.75  85.00

Topper in each subject:
English -> Chetna 91
Maths -> Harsh 99
IP -> Aditi 97

Top three overall:
    Name Section  Total
7  Harsh       B    277
0  Aditi       A    274
4    Ela       B    270

Viva questions (3)

What does sum(axis=1) do? It adds across each row, giving one total per student.
Why do we select res[subjects] before mean()? Name and Section are text and cannot be averaged.
What does round(2) do to a Series? Rounds every value to 2 decimal places.

Matplotlib Programs

1Line plot: monthly attendance

Plot the monthly attendance percentage of Class XI and Class XII on one line chart. Add a title, axis labels, a legend and markers, and save the chart as a PNG file.

Solution

Python

import matplotlib.pyplot as plt

months = ["Apr", "May", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", "Jan", "Feb"]
xi = [92, 88, 85, 90, 87, 83, 89, 80, 78, 91]
xii = [95, 91, 90, 93, 92, 88, 90, 86, 84, 89]

plt.plot(months, xi, marker="o", color="blue", label="Class XI")
plt.plot(months, xii, marker="s", linestyle="--", color="green", label="Class XII")
plt.title("Monthly Attendance (%)")
plt.xlabel("Month")
plt.ylabel("Attendance %")
plt.ylim(70, 100)
plt.grid(True)
plt.legend()
plt.savefig("attendance.png")
plt.show()

A line chart with ten months (Apr to Feb, June skipped as a vacation month) on the x-axis and attendance from 70 to 100 on the y-axis. Class XI is a solid blue line with circle markers; Class XII is a dashed green line with square markers that stays above Class XI in most months. Both lines dip in December and January. The title reads "Monthly Attendance (%)", grid lines are shown, and a legend names the two classes. The same chart is saved as attendance.png.

Viva questions (3)

What does the label argument in plot() do? It gives the text shown for that line in the legend.
Why is savefig() written before show()? show() may clear the figure, so saving afterwards can produce a blank image.
Name two values for linestyle. '-' (solid) and '--' (dashed); ':' gives dotted.

2Bar graph: subject-wise average marks

Using the school result data, calculate the average marks in each subject with pandas and draw a bar graph with a title and axis labels.

Solution

Python

import pandas as pd
import matplotlib.pyplot as plt

res = pd.DataFrame({
    "English": [82, 74, 91, 66, 88, 59, 77, 84],
    "Maths": [95, 68, 72, 81, 90, 64, 70, 99],
    "IP": [97, 85, 88, 79, 92, 71, 83, 94],
    "Physics": [76, 62, 80, 70, 85, 58, 66, 90]})

avg = res.mean()
plt.bar(avg.index, avg.values, color=["orange", "teal", "purple", "gray"])
plt.title("Subject-wise Average Marks, Class XII")
plt.xlabel("Subject")
plt.ylabel("Average marks")
plt.ylim(0, 100)
plt.show()

A vertical bar chart with four bars: English (about 77.6), Maths (79.9), IP (86.1) and Physics (73.4), coloured orange, teal, purple and gray. The y-axis runs from 0 to 100 and is labelled "Average marks"; the title is "Subject-wise Average Marks, Class XII". IP has the tallest bar and Physics the shortest.

Viva questions (3)

Which function draws a vertical bar chart? plt.bar(); plt.barh() draws horizontal bars.
What are avg.index and avg.values here? The subject names and their average marks.
When should a bar chart be used instead of a line chart? To compare separate categories; line charts show change over time.

3Histogram: distribution of marks

Draw a histogram of the IP marks of 40 students using class intervals of 10 marks, with edges on the bars, a title and axis labels.

Solution

Python

import matplotlib.pyplot as plt

marks = [34, 45, 52, 58, 61, 63, 65, 67, 68, 70,
         71, 72, 73, 74, 75, 76, 77, 78, 79, 80,
         81, 82, 83, 84, 85, 86, 87, 88, 89, 90,
         91, 92, 93, 94, 95, 96, 97, 98, 99, 100]

plt.hist(marks, bins=[30, 40, 50, 60, 70, 80, 90, 100],
         color="skyblue", edgecolor="black")
plt.title("Distribution of IP Marks (40 students)")
plt.xlabel("Marks")
plt.ylabel("Number of students")
plt.savefig("marks_hist.png")
plt.show()

A histogram with seven touching bars for the intervals 30-40, 40-50, ..., 90-100, light blue with black edges. The heights are 1, 1, 2, 5, 10, 10 and 11: few students below 60 and most of the class between 70 and 100. (The last bin also counts the 100, which is why it has 11.) Title, x-axis label "Marks" and y-axis label "Number of students" are shown, and the chart is saved as marks_hist.png.

Viva questions (3)

How is a histogram different from a bar chart? A histogram shows the frequency of values in continuous intervals, so its bars touch.
What does the bins list do? It gives the edges of the class intervals.
Which interval does the value 70 fall into? 70-80, because each bin includes its left edge (only the last bin includes its right edge too).

4Grouped bar chart: section-wise comparison

For the result data, plot the average marks of Section A and Section B side by side for each subject, with a legend. (CBSE list 5.2, item 2)

Solution

Python

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

res = pd.DataFrame({
    "Section": ["A", "A", "A", "A", "B", "B", "B", "B"],
    "English": [82, 74, 91, 66, 88, 59, 77, 84],
    "Maths": [95, 68, 72, 81, 90, 64, 70, 99],
    "IP": [97, 85, 88, 79, 92, 71, 83, 94]})
avg = res.groupby("Section")[["English", "Maths", "IP"]].mean()

x = np.arange(len(avg.columns))      # positions 0, 1, 2
w = 0.35
plt.bar(x - w / 2, avg.loc["A"], width=w, label="Section A")
plt.bar(x + w / 2, avg.loc["B"], width=w, label="Section B")
plt.xticks(x, avg.columns)
plt.title("Section-wise Average Marks")
plt.xlabel("Subject")
plt.ylabel("Average marks")
plt.legend()
plt.show()

A grouped bar chart with three groups (English, Maths, IP). In each group the Section A bar (blue) stands to the left of the Section B bar (orange). Section A averages are 78.25, 79.0 and 87.25; Section B averages are 77.0, 80.75 and 85.0, so the bars in each pair are close in height, with Section B slightly ahead only in Maths. A legend identifies the two sections, and the chart has a title and axis labels.

Viva questions (3)

Why are the bars shifted by w/2? So that the two bars of a subject sit side by side instead of on top of each other.
What does xticks(x, avg.columns) do? It places the subject names under the groups.
Where does the bar colour come from when none is given? Matplotlib's default colour cycle (blue first, then orange).

5Open data: aggregate and plot

Students download a CSV file from an open data portal such as data.gov.in, aggregate it with pandas and plot it. Here a small file of vehicle registrations in a city by year and fuel type is used (the figures are illustrative; replace the file with the one your class downloads). Plot the yearly total as a line and the total by fuel type as a horizontal bar chart. (CBSE list 5.2, item 3)

Solution

Python

import pandas as pd
import matplotlib.pyplot as plt

# Illustrative data file (in the lab, use the CSV downloaded from data.gov.in)
with open("vehicles.csv", "w") as f:
    f.write("Year,Fuel,Registrations\n"
            "2021,Petrol,41000\n2021,Diesel,18000\n2021,CNG,9000\n2021,Electric,1200\n"
            "2022,Petrol,43500\n2022,Diesel,16500\n2022,CNG,11000\n2022,Electric,3400\n"
            "2023,Petrol,44800\n2023,Diesel,15200\n2023,CNG,12600\n2023,Electric,7900\n"
            "2024,Petrol,45200\n2024,Diesel,14100\n2024,CNG,13900\n2024,Electric,12500\n")

data = pd.read_csv("vehicles.csv")
by_year = data.groupby("Year")["Registrations"].sum()
by_fuel = data.groupby("Fuel")["Registrations"].sum().sort_values()
plt.plot(by_year.index.astype(str), by_year.values, marker="o", color="red")
plt.title("Total Vehicle Registrations per Year")
plt.xlabel("Year")
plt.ylabel("Registrations")
plt.show()

plt.barh(by_fuel.index, by_fuel.values, color="seagreen")
plt.title("Registrations by Fuel Type (2021-2024)")
plt.xlabel("Registrations")
plt.show()

Two charts are shown. The first is a red line with circle markers rising each year from 69,200 registrations in 2021 to 85,700 in 2024. The second is a horizontal bar chart of totals by fuel type, sorted so that the smallest is at the bottom: Electric (25,000), then CNG (46,500), Diesel (63,800) and, at the top, Petrol (174,500), which has by far the longest bar. Each chart has its own title and axis labels.

Viva questions (3)

Why is groupby() used before plotting? The raw file has one row per year and fuel type; groupby adds them up into one value per year or per fuel.
Why is by_year.index converted with astype(str)? So that the years appear as labels 2021 to 2024 and not as numbers with decimals on the axis.
Name two open data sources for Indian data. data.gov.in and the websites of government ministries (for example, the census portal).

6Cricket charts: Manhattan (bar) and worm (line)

Runs scored in each over of a T20 match by two teams are given. Draw a bar chart of Team A's runs per over (a 'Manhattan') and a line chart of the cumulative runs of both teams (a 'worm') with a legend, and save both charts.

Solution

Python

import numpy as np
import matplotlib.pyplot as plt

overs = np.arange(1, 21)
team_a = np.array([6, 9, 4, 12, 8, 10, 5, 7, 11, 6, 9, 8, 14, 7, 10, 13, 9, 15, 12, 18])
team_b = np.array([8, 5, 10, 7, 11, 6, 9, 4, 8, 12, 7, 9, 6, 11, 10, 8, 14, 9, 16, 11])

plt.bar(overs, team_a, color="navy")
plt.title("Team A: Runs per Over")
plt.xlabel("Over")
plt.ylabel("Runs")
plt.xticks(overs)
plt.savefig("manhattan.png")
plt.show()

plt.plot(overs, team_a.cumsum(), color="navy", label="Team A")
plt.plot(overs, team_b.cumsum(), color="darkorange", label="Team B")
plt.title("Worm Chart: Cumulative Runs")
plt.xlabel("Over")
plt.ylabel("Total runs")
plt.legend()
plt.savefig("worm.png")
plt.show()

The first chart has 20 navy bars, one for each over, with every over number marked on the x-axis; the tallest bar is the 20th over (18 runs). The second chart shows two rising lines: Team A (navy) finishes at 193 runs and Team B (orange) at 181. The lead changes several times in the first 12 overs (the lines keep crossing), and Team A stays ahead from the 13th over onwards. A legend labels both teams, and the two charts are saved as manhattan.png and worm.png.

Viva questions (3)

What does cumsum() return? Running totals: each value is the sum of all values up to that position.
Why are two plt.show() calls used? Each call displays the current figure; the next plot starts on a new figure.
Which chart shows the scoring rate better, the bar or the line? The bar chart shows runs in each over; the worm shows the total and who was ahead.

SQL Queries

1Create and open a database

Create a database school and open it.

Solution

MySQL

CREATE DATABASE school;
USE school;

Output

Query OK, 1 row affected

Database changed

Viva questions (2)

Which command shows the database currently in use? SELECT DATABASE();
Is CREATE DATABASE a DDL or DML command? DDL.

2Create the STUDENT table

Create a table STUDENT with the student id, name and marks as attributes, where the student id is the primary key. Also store gender, stream, date of birth and city for later queries. (CBSE list 5.3, item 1)

Solution

MySQL

CREATE TABLE STUDENT (
    StudentID INT PRIMARY KEY,
    Name      VARCHAR(30) NOT NULL,
    Gender    CHAR(1),
    Stream    VARCHAR(15),
    DOB       DATE,
    City      VARCHAR(20),
    Marks     INT
);
DESC STUDENT;

Output

Query OK, 0 rows affected

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| StudentID | int         | NO   | PRI | NULL    |       |
| Name      | varchar(30) | NO   |     | NULL    |       |
| Gender    | char(1)     | YES  |     | NULL    |       |
| Stream    | varchar(15) | YES  |     | NULL    |       |
| DOB       | date        | YES  |     | NULL    |       |
| City      | varchar(20) | YES  |     | NULL    |       |
| Marks     | int         | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
7 rows in set

Viva questions (3)

What two properties does a primary key column have? Its values are unique and can never be NULL.
What does NOT NULL on Name mean? Every row must have a name.
Which command shows a table's structure? DESC or DESCRIBE.

3Insert records

Insert the records of ten students. Eshan's marks are not available yet.

Solution

MySQL

INSERT INTO STUDENT VALUES
(1201, 'Aarav Mehta',    'M', 'Science',    '2007-05-14', 'Delhi',     91),
(1202, 'Bhavya Kapoor',  'F', 'Commerce',   '2008-01-22', 'Jaipur',    78),
(1203, 'Chetan Yadav',   'M', 'Humanities', '2007-09-03', 'Lucknow',   64),
(1204, 'Deepika Rao',    'F', 'Science',    '2007-12-29', 'Hyderabad', 85),
(1205, 'Eshan Malik',    'M', 'Commerce',   '2008-03-11', 'Delhi',     NULL),
(1206, 'Farah Siddiqui', 'F', 'Humanities', '2007-07-30', 'Bhopal',    88),
(1207, 'Gaurav Thakur',  'M', 'Science',    '2008-02-05', 'Shimla',    72),
(1208, 'Harleen Kaur',   'F', 'Commerce',   '2007-10-17', 'Amritsar',  95),
(1209, 'Imran Sheikh',   'M', 'Science',    '2007-06-08', 'Pune',      58),
(1210, 'Jyoti Pandey',   'F', 'Humanities', '2008-04-25', 'Patna',     81);

Output

Query OK, 10 rows affected
Records: 10  Duplicates: 0  Warnings: 0

Viva questions (2)

How do we insert values into only some columns? Name the columns: INSERT INTO STUDENT (StudentID, Name) VALUES (1300, 'Ria');
What is stored in a column that is left out of such an INSERT? NULL (or the column's default value).

4Insert a new student

Insert the details of a new student and display the whole table. (CBSE list 5.3, item 2)

Solution

MySQL

INSERT INTO STUDENT VALUES
(1211, 'Kavya Nambiar', 'F', 'Science', '2008-05-19', 'Kochi', 89);
SELECT * FROM STUDENT;

Output

Query OK, 1 row affected

+-----------+----------------+--------+------------+------------+-----------+-------+
| StudentID | Name           | Gender | Stream     | DOB        | City      | Marks |
+-----------+----------------+--------+------------+------------+-----------+-------+
|      1201 | Aarav Mehta    | M      | Science    | 2007-05-14 | Delhi     |    91 |
|      1202 | Bhavya Kapoor  | F      | Commerce   | 2008-01-22 | Jaipur    |    78 |
|      1203 | Chetan Yadav   | M      | Humanities | 2007-09-03 | Lucknow   |    64 |
|      1204 | Deepika Rao    | F      | Science    | 2007-12-29 | Hyderabad |    85 |
|      1205 | Eshan Malik    | M      | Commerce   | 2008-03-11 | Delhi     |  NULL |
|      1206 | Farah Siddiqui | F      | Humanities | 2007-07-30 | Bhopal    |    88 |
|      1207 | Gaurav Thakur  | M      | Science    | 2008-02-05 | Shimla    |    72 |
|      1208 | Harleen Kaur   | F      | Commerce   | 2007-10-17 | Amritsar  |    95 |
|      1209 | Imran Sheikh   | M      | Science    | 2007-06-08 | Pune      |    58 |
|      1210 | Jyoti Pandey   | F      | Humanities | 2008-04-25 | Patna     |    81 |
|      1211 | Kavya Nambiar  | F      | Science    | 2008-05-19 | Kochi     |    89 |
+-----------+----------------+--------+------------+------------+-----------+-------+
11 rows in set

Viva questions (2)

Is INSERT a DDL or DML command? DML.
In which order must the values be given when column names are not listed? In the order of the columns in the table.

5Delete a student

Delete the details of the student with StudentID 1209 and display the remaining IDs and names. (CBSE list 5.3, item 3)

Solution

MySQL

DELETE FROM STUDENT WHERE StudentID = 1209;
SELECT StudentID, Name FROM STUDENT;

Output

Query OK, 1 row affected

+-----------+----------------+
| StudentID | Name           |
+-----------+----------------+
|      1201 | Aarav Mehta    |
|      1202 | Bhavya Kapoor  |
|      1203 | Chetan Yadav   |
|      1204 | Deepika Rao    |
|      1205 | Eshan Malik    |
|      1206 | Farah Siddiqui |
|      1207 | Gaurav Thakur  |
|      1208 | Harleen Kaur   |
|      1210 | Jyoti Pandey   |
|      1211 | Kavya Nambiar  |
+-----------+----------------+
10 rows in set

Viva questions (2)

What happens if the WHERE clause is missed in DELETE? All rows are deleted.
How is DELETE different from DROP TABLE? DELETE removes rows; DROP removes the table and its structure.

6Students with marks above 80

Display the details of the students with marks more than 80. (CBSE list 5.3, item 4)

Solution

MySQL

SELECT StudentID, Name, Stream, Marks FROM STUDENT
WHERE Marks > 80;

Output

+-----------+----------------+------------+-------+
| StudentID | Name           | Stream     | Marks |
+-----------+----------------+------------+-------+
|      1201 | Aarav Mehta    | Science    |    91 |
|      1204 | Deepika Rao    | Science    |    85 |
|      1206 | Farah Siddiqui | Humanities |    88 |
|      1208 | Harleen Kaur   | Commerce   |    95 |
|      1210 | Jyoti Pandey   | Humanities |    81 |
|      1211 | Kavya Nambiar  | Science    |    89 |
+-----------+----------------+------------+-------+
6 rows in set

Viva questions (2)

Why is Eshan not listed? His marks are NULL, and NULL > 80 is not true.
How would you include students with exactly 80? Use Marks >= 80.

7MIN, MAX, SUM and AVG

Find the minimum, maximum, total and average marks. (CBSE list 5.3, item 5)

Solution

MySQL

SELECT MIN(Marks), MAX(Marks), SUM(Marks), AVG(Marks)
FROM STUDENT;

Output

+------------+------------+------------+------------+
| MIN(Marks) | MAX(Marks) | SUM(Marks) | AVG(Marks) |
+------------+------------+------------+------------+
|         64 |         95 |        743 |    82.5556 |
+------------+------------+------------+------------+
1 row in set

Viva questions (3)

Do aggregate functions count NULL values? No; NULL marks are ignored, so AVG divides by 9 here, not 10.
Why does AVG show four decimal places? MySQL returns the average of integers as a decimal with four places.
Can aggregate functions be used in a WHERE clause? No; conditions on aggregates go in HAVING.

8COUNT(*) and COUNT(column)

Count the rows in STUDENT, the students whose marks are entered, and the number of different streams.

Solution

MySQL

SELECT COUNT(*), COUNT(Marks), COUNT(DISTINCT Stream)
FROM STUDENT;

Output

+----------+--------------+------------------------+
| COUNT(*) | COUNT(Marks) | COUNT(DISTINCT Stream) |
+----------+--------------+------------------------+
|       10 |            9 |                      3 |
+----------+--------------+------------------------+
1 row in set

Viva questions (2)

Why do COUNT() and COUNT(Marks) differ? COUNT() counts all rows; COUNT(Marks) skips NULL marks.
What does COUNT(DISTINCT Stream) count? The number of different stream values.

9ORDER BY in descending order

Display StudentID and marks in descending order of marks. (CBSE list 5.3, item 7)

Solution

MySQL

SELECT StudentID, Marks FROM STUDENT
ORDER BY Marks DESC;

Output

+-----------+-------+
| StudentID | Marks |
+-----------+-------+
|      1208 |    95 |
|      1201 |    91 |
|      1211 |    89 |
|      1206 |    88 |
|      1204 |    85 |
|      1210 |    81 |
|      1202 |    78 |
|      1207 |    72 |
|      1203 |    64 |
|      1205 |  NULL |
+-----------+-------+
10 rows in set

Viva questions (3)

What is the default order of ORDER BY? Ascending (ASC).
Where does the NULL value appear in descending order in MySQL? At the end (NULL is treated as smaller than any value).
How do you sort by stream and then by marks? ORDER BY Stream, Marks DESC.

10GROUP BY

Display, for each stream, the number of students, the highest marks and the average marks.

Solution

MySQL

SELECT Stream, COUNT(*) AS Students, MAX(Marks) AS Highest, AVG(Marks) AS Average
FROM STUDENT
GROUP BY Stream;

Output

+------------+----------+---------+---------+
| Stream     | Students | Highest | Average |
+------------+----------+---------+---------+
| Commerce   |        3 |      95 | 86.5000 |
| Humanities |        3 |      88 | 77.6667 |
| Science    |        4 |      91 | 84.2500 |
+------------+----------+---------+---------+
3 rows in set

Viva questions (3)

What does GROUP BY do? It puts rows with the same value in a column into one group so that aggregates are found per group.
Which columns may appear in SELECT with GROUP BY? The grouped column(s) and aggregate functions.
Why does Commerce show 3 students but its average uses only 2 marks? COUNT(*) counts Eshan's row, but AVG ignores his NULL marks.

11GROUP BY with HAVING

Display the streams whose average marks are more than 80, with the average rounded to one decimal place.

Solution

MySQL

SELECT Stream, ROUND(AVG(Marks), 1) AS AvgMarks
FROM STUDENT
GROUP BY Stream
HAVING AVG(Marks) > 80;

Output

+----------+----------+
| Stream   | AvgMarks |
+----------+----------+
| Commerce |     86.5 |
| Science  |     84.3 |
+----------+----------+
2 rows in set

Viva questions (2)

What is the difference between WHERE and HAVING? WHERE filters rows before grouping; HAVING filters groups after grouping.
Can HAVING use an aggregate function? Yes, that is its main use.

12Math functions: POWER, ROUND, MOD

Use the math functions on values: POWER(), ROUND() with different positions, and MOD().

Solution

MySQL

SELECT POWER(2, 5), ROUND(1234.5678, 2), ROUND(1234.5678),
       ROUND(1234.5678, -2), MOD(47, 5);

Output

+-------------+---------------------+------------------+----------------------+------------+
| POWER(2, 5) | ROUND(1234.5678, 2) | ROUND(1234.5678) | ROUND(1234.5678, -2) | MOD(47, 5) |
+-------------+---------------------+------------------+----------------------+------------+
|          32 |             1234.57 |             1235 |                 1200 |          2 |
+-------------+---------------------+------------------+----------------------+------------+
1 row in set

Viva questions (3)

What does ROUND(x, -2) do? Rounds to the nearest hundred.
What does MOD(47, 5) return? The remainder 2.
Is POW() the same as POWER()? Yes, both raise a number to a power.

13Math functions on table data

Show the stream-wise average marks rounded to 2 places, and the students whose StudentID is an even number.

Solution

MySQL

SELECT Stream, ROUND(AVG(Marks), 2) AS AvgMarks FROM STUDENT GROUP BY Stream;
SELECT StudentID, Name FROM STUDENT WHERE MOD(StudentID, 2) = 0;

Output

+------------+----------+
| Stream     | AvgMarks |
+------------+----------+
| Commerce   |    86.50 |
| Humanities |    77.67 |
| Science    |    84.25 |
+------------+----------+
3 rows in set

+-----------+----------------+
| StudentID | Name           |
+-----------+----------------+
|      1202 | Bhavya Kapoor  |
|      1204 | Deepika Rao    |
|      1206 | Farah Siddiqui |
|      1208 | Harleen Kaur   |
|      1210 | Jyoti Pandey   |
+-----------+----------------+
5 rows in set

Viva questions (2)

When is MOD(n, 2) = 0? When n is even.
Can a function be used inside another function? Yes, for example ROUND(AVG(Marks), 2).

14Text functions: UPPER, LOWER, LENGTH, LEFT, RIGHT

For Science students, display the name in capitals, the city in small letters, the length of the name, the first three letters of the name and the last three letters of the city.

Solution

MySQL

SELECT UPPER(Name), LOWER(City), LENGTH(Name), LEFT(Name, 3), RIGHT(City, 3)
FROM STUDENT
WHERE Stream = 'Science';

Output

+---------------+-------------+--------------+---------------+----------------+
| UPPER(Name)   | LOWER(City) | LENGTH(Name) | LEFT(Name, 3) | RIGHT(City, 3) |
+---------------+-------------+--------------+---------------+----------------+
| AARAV MEHTA   | delhi       |           11 | Aar           | lhi            |
| DEEPIKA RAO   | hyderabad   |           11 | Dee           | bad            |
| GAURAV THAKUR | shimla      |           13 | Gau           | mla            |
| KAVYA NAMBIAR | kochi       |           13 | Kav           | chi            |
+---------------+-------------+--------------+---------------+----------------+
4 rows in set

Viva questions (2)

Name the other spellings of UPPER() and LOWER() in MySQL. UCASE() and LCASE().
Does LENGTH count spaces? Yes, every character including spaces is counted.

15Text functions: INSTR, MID/SUBSTRING

For Commerce students, find the position of the space in the name, and extract the first name, the surname and three characters starting from the second character.

Solution

MySQL

SELECT Name, INSTR(Name, ' ') AS SpaceAt,
       LEFT(Name, INSTR(Name, ' ') - 1) AS FirstName,
       SUBSTRING(Name, INSTR(Name, ' ') + 1) AS Surname,
       MID(Name, 2, 3) AS Part
FROM STUDENT
WHERE Stream = 'Commerce';

Output

+---------------+---------+-----------+---------+------+
| Name          | SpaceAt | FirstName | Surname | Part |
+---------------+---------+-----------+---------+------+
| Bhavya Kapoor |       7 | Bhavya    | Kapoor  | hav  |
| Eshan Malik   |       6 | Eshan     | Malik   | sha  |
| Harleen Kaur  |       8 | Harleen   | Kaur    | arl  |
+---------------+---------+-----------+---------+------+
3 rows in set

Viva questions (3)

What does INSTR return if the text is not found? 0.
Are MID(), SUBSTRING() and SUBSTR() the same? Yes, all three extract part of a string.
If no length is given to SUBSTRING, what is returned? All the characters from the start position to the end.

16Text functions: LTRIM, RTRIM, TRIM

Show the effect of LTRIM(), RTRIM() and TRIM() on a string with spaces on both sides, using LENGTH() to prove which spaces were removed.

Solution

MySQL

SELECT LENGTH('   IP Lab   ') AS Original,
       LENGTH(LTRIM('   IP Lab   ')) AS After_LTRIM,
       LENGTH(RTRIM('   IP Lab   ')) AS After_RTRIM,
       LENGTH(TRIM('   IP Lab   ')) AS After_TRIM;

Output

+----------+-------------+-------------+------------+
| Original | After_LTRIM | After_RTRIM | After_TRIM |
+----------+-------------+-------------+------------+
|       12 |           9 |           9 |          6 |
+----------+-------------+-------------+------------+
1 row in set

Viva questions (2)

Does TRIM remove the space between IP and Lab? No; only leading and trailing spaces are removed.
Why is LENGTH used here? Spaces are invisible in the output, but the length shows how many were removed.

17Date functions on DOB

For Humanities students, display the day, month number, month name, year and weekday of birth. Then list the students born in 2008.

Solution

MySQL

SELECT Name, DAY(DOB), MONTH(DOB), MONTHNAME(DOB), YEAR(DOB), DAYNAME(DOB)
FROM STUDENT
WHERE Stream = 'Humanities';
SELECT Name, DOB FROM STUDENT WHERE YEAR(DOB) = 2008;

Output

+----------------+----------+------------+----------------+-----------+--------------+
| Name           | DAY(DOB) | MONTH(DOB) | MONTHNAME(DOB) | YEAR(DOB) | DAYNAME(DOB) |
+----------------+----------+------------+----------------+-----------+--------------+
| Chetan Yadav   |        3 |          9 | September      |      2007 | Monday       |
| Farah Siddiqui |       30 |          7 | July           |      2007 | Monday       |
| Jyoti Pandey   |       25 |          4 | April          |      2008 | Friday       |
+----------------+----------+------------+----------------+-----------+--------------+
3 rows in set

+---------------+------------+
| Name          | DOB        |
+---------------+------------+
| Bhavya Kapoor | 2008-01-22 |
| Eshan Malik   | 2008-03-11 |
| Gaurav Thakur | 2008-02-05 |
| Jyoti Pandey  | 2008-04-25 |
| Kavya Nambiar | 2008-05-19 |
+---------------+------------+
5 rows in set

Viva questions (2)

What is the difference between MONTH() and MONTHNAME()? MONTH() gives a number (1-12); MONTHNAME() gives the name.
What does DAYNAME('2025-08-15') return? Friday.

18NOW() and DATE()

Display the current date and time, only the current date, and the weekday, month name and year of today.

The output depends on when the query is run. The output below is from a run on 14 November 2025 at 10:30 a.m.

Solution

MySQL

SELECT NOW(), DATE(NOW()), DAYNAME(NOW()), MONTHNAME(NOW()), YEAR(NOW());

Output

+---------------------+-------------+----------------+------------------+-------------+
| NOW()               | DATE(NOW()) | DAYNAME(NOW()) | MONTHNAME(NOW()) | YEAR(NOW()) |
+---------------------+-------------+----------------+------------------+-------------+
| 2025-11-14 10:30:00 | 2025-11-14  | Friday         | November         |        2025 |
+---------------------+-------------+----------------+------------------+-------------+
1 row in set

Viva questions (2)

What is the difference between NOW() and DATE(NOW())? NOW() gives the date and time; DATE() keeps only the date part.
Why is the output different each time? NOW() returns the server's current date and time.

19CUSTOMER table

Create a table CUSTOMER (customer ID, customer name, country) and insert eight customers of an online store.

Solution

MySQL

CREATE TABLE CUSTOMER (
    CustID   INT PRIMARY KEY,
    CustName VARCHAR(30),
    Country  VARCHAR(20)
);
INSERT INTO CUSTOMER VALUES
(1, 'Ritika Sharma', 'India'), (2, 'Ahmed Rashid', 'UAE'),
(3, 'Tan Wei Ling', 'Singapore'), (4, 'Rahul Desai', 'India'),
(5, 'Oliver Brown', 'UK'), (6, 'Fatima Noor', 'UAE'),
(7, 'Ananya Pillai', 'India'), (8, 'Lim Jia Hui', 'Singapore');
SELECT * FROM CUSTOMER;

Output

Query OK, 0 rows affected

Query OK, 8 rows affected
Records: 8  Duplicates: 0  Warnings: 0

+--------+---------------+-----------+
| CustID | CustName      | Country   |
+--------+---------------+-----------+
|      1 | Ritika Sharma | India     |
|      2 | Ahmed Rashid  | UAE       |
|      3 | Tan Wei Ling  | Singapore |
|      4 | Rahul Desai   | India     |
|      5 | Oliver Brown  | UK        |
|      6 | Fatima Noor   | UAE       |
|      7 | Ananya Pillai | India     |
|      8 | Lim Jia Hui   | Singapore |
+--------+---------------+-----------+
8 rows in set

Viva questions (1)

Why is CustID chosen as the primary key? Two customers can have the same name or country, but the ID is unique.

20Number of customers from each country

Find the total number of customers from each country, with the country having the most customers first. (CBSE list 5.3, item 6)

Solution

MySQL

SELECT Country, COUNT(*) AS Customers
FROM CUSTOMER
GROUP BY Country
ORDER BY Customers DESC, Country;

Output

+-----------+-----------+
| Country   | Customers |
+-----------+-----------+
| India     |         3 |
| Singapore |         2 |
| UAE       |         2 |
| UK        |         1 |
+-----------+-----------+
4 rows in set

Viva questions (2)

Can an alias be used in ORDER BY? Yes, ORDER BY Customers works.
Why is Country added after Customers in ORDER BY? To sort countries with equal counts alphabetically.

21ORDERS table and equi-join

Create an ORDERS table, insert orders, and display each order with the customer's name and country by joining the two tables on CustID.

Solution

MySQL

CREATE TABLE ORDERS (
    OrderID   INT PRIMARY KEY,
    CustID    INT,
    OrderDate DATE,
    Amount    DECIMAL(8,2)
);
INSERT INTO ORDERS VALUES
(501, 1, '2025-07-02', 2450.00), (502, 3, '2025-07-05', 1899.50),
(503, 1, '2025-07-11', 999.00),  (504, 4, '2025-08-01', 5200.00),
(505, 6, '2025-08-09', 760.25),  (506, 2, '2025-08-15', 3100.00),
(507, 4, '2025-09-03', 1450.75), (508, 8, '2025-09-21', 2675.00);
SELECT O.OrderID, C.CustName, C.Country, O.Amount
FROM CUSTOMER C, ORDERS O
WHERE C.CustID = O.CustID;

Output

Query OK, 0 rows affected

Query OK, 8 rows affected
Records: 8  Duplicates: 0  Warnings: 0

+---------+---------------+-----------+---------+
| OrderID | CustName      | Country   | Amount  |
+---------+---------------+-----------+---------+
|     501 | Ritika Sharma | India     | 2450.00 |
|     502 | Tan Wei Ling  | Singapore | 1899.50 |
|     503 | Ritika Sharma | India     |  999.00 |
|     504 | Rahul Desai   | India     | 5200.00 |
|     505 | Fatima Noor   | UAE       |  760.25 |
|     506 | Ahmed Rashid  | UAE       | 3100.00 |
|     507 | Rahul Desai   | India     | 1450.75 |
|     508 | Lim Jia Hui   | Singapore | 2675.00 |
+---------+---------------+-----------+---------+
8 rows in set

Viva questions (3)

What is an equi-join? A join in which rows are matched using equality (=) of a common column.
Why are customers 5 and 7 missing from the result? They have no matching rows in ORDERS.
What are C and O in the query? Table aliases, short names used to qualify column names.

22Join with GROUP BY

Display each customer's name, number of orders and total amount spent, highest spender first.

Solution

MySQL

SELECT C.CustName, COUNT(*) AS Orders, SUM(O.Amount) AS Total
FROM CUSTOMER C, ORDERS O
WHERE C.CustID = O.CustID
GROUP BY C.CustName
ORDER BY Total DESC;

Output

+---------------+--------+---------+
| CustName      | Orders | Total   |
+---------------+--------+---------+
| Rahul Desai   |      2 | 6650.75 |
| Ritika Sharma |      2 | 3449.00 |
| Ahmed Rashid  |      1 | 3100.00 |
| Lim Jia Hui   |      1 | 2675.00 |
| Tan Wei Ling  |      1 | 1899.50 |
| Fatima Noor   |      1 |  760.25 |
+---------------+--------+---------+
6 rows in set

Viva questions (2)

In which order are WHERE, GROUP BY and ORDER BY applied? WHERE filters rows, GROUP BY forms groups, then ORDER BY sorts the result.
Why must CustID in WHERE be written as C.CustID? Both tables have a CustID column, so the table must be named.

23ALTER and UPDATE with conditions

Add a column Grade to STUDENT, fill it ('A' for 85 and above, 'B' for 70-84, 'C' for the rest with marks) and display the name, marks and grade in descending order of marks.

Solution

MySQL

ALTER TABLE STUDENT ADD Grade CHAR(1);
UPDATE STUDENT SET Grade = 'A' WHERE Marks >= 85;
UPDATE STUDENT SET Grade = 'B' WHERE Marks BETWEEN 70 AND 84;
UPDATE STUDENT SET Grade = 'C' WHERE Marks < 70;
SELECT Name, Marks, Grade FROM STUDENT ORDER BY Marks DESC;

Output

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 5 rows affected
Rows matched: 5  Changed: 5  Warnings: 0

Query OK, 3 rows affected
Rows matched: 3  Changed: 3  Warnings: 0

Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

+----------------+-------+-------+
| Name           | Marks | Grade |
+----------------+-------+-------+
| Harleen Kaur   |    95 | A     |
| Aarav Mehta    |    91 | A     |
| Kavya Nambiar  |    89 | A     |
| Farah Siddiqui |    88 | A     |
| Deepika Rao    |    85 | A     |
| Jyoti Pandey   |    81 | B     |
| Bhavya Kapoor  |    78 | B     |
| Gaurav Thakur  |    72 | B     |
| Chetan Yadav   |    64 | C     |
| Eshan Malik    |  NULL | NULL  |
+----------------+-------+-------+
10 rows in set

Viva questions (2)

Why does Eshan's grade remain NULL? None of the conditions is true for NULL marks.
Is ALTER TABLE DDL or DML? DDL; UPDATE is DML.