Class 11 · Practical file

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

20 Python Programs20 SQL Queries

About the practical file

The CBSE practical examination for Class XI Informatics Practices carries 30 marks: problem solving in Python (11), creating a database in MySQL and running queries (7), the practical file (7) and viva-voce (5).

The practical file must contain at least 14 Python programs and 14 SQL queries. This file gives model solutions for all 17 programs in the CBSE suggested list (section 5.1), three extra programs on lists, dictionaries and NumPy, and a complete SQL set built on one STUDENT table (section 5.2 expanded to 20 tasks).

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

Program number, title and aim (problem statement)
The code, typed or neatly written, with comments
The output: for Python, a real sample run with the input values; for SQL, the result table as shown by MySQL
Date of the lab session and the teacher's signature

How to assess it: check the file during the year, not only at the end. Ask each student to run two or three programs from their own file on the lab computer, and use the viva questions given with each item. A file with copied outputs that the student cannot run or explain should not get full marks. Encourage students to change the sample data (names, marks, prices) to their own so that every file is different.

All Python outputs here come from real runs (sample input shown after each prompt). All SQL statements are MySQL syntax and the result tables come from running them in order on a MySQL-compatible server, so the later queries reflect the earlier UPDATE, ALTER and DELETE commands.

Criterion	What the teacher checks	Marks
Python programs	At least 14 programs, covering the CBSE list; code correct and commented; real outputs	3
SQL queries	At least 14 queries from CREATE to DELETE; correct MySQL syntax with outputs	2
Documentation	Index, aims, dates, neat presentation, original sample data	1
Regularity	File completed and signed lab by lab, submitted on time	1
Total		7

Python Programs

1Average and grade for given marks

Accept a student's name and marks in five subjects (out of 100). Find the total, the average and the grade using the scale below.

90 and above: A1
80 to below 90: A2
70 to below 80: B1
60 to below 70: B2
50 to below 60: C1
40 to below 50: C2
33 to below 40: D
below 33: E

Solution

Python

# Program 1: Average and grade of a student
name = input("Enter student name: ")
total = 0
for i in range(1, 6):
    m = float(input("Enter marks in subject " + str(i) + ": "))
    total = total + m

avg = total / 5

if avg >= 90:
    grade = "A1"
elif avg >= 80:
    grade = "A2"
elif avg >= 70:
    grade = "B1"
elif avg >= 60:
    grade = "B2"
elif avg >= 50:
    grade = "C1"
elif avg >= 40:
    grade = "C2"
elif avg >= 33:
    grade = "D"
else:
    grade = "E"

print("Name    :", name)
print("Total   :", total)
print("Average :", round(avg, 2))
print("Grade   :", grade)

Output

Enter student name: Meera Joshi
Enter marks in subject 1: 78
Enter marks in subject 2: 85
Enter marks in subject 3: 92
Enter marks in subject 4: 66
Enter marks in subject 5: 74
Name    : Meera Joshi
Total   : 395.0
Average : 79.0
Grade   : B1

Viva questions (4)

Why do we use float() with input()? input() always returns a string, so it must be converted to a number before adding.
Why is the order of conditions in if-elif important? Python stops at the first true condition, so the highest range must be checked first.
What does range(1, 6) produce? The numbers 1, 2, 3, 4 and 5; the stop value 6 is not included.
What is the role of round(avg, 2)? It rounds the average to two decimal places for display.

2Sale price after discount

Accept the marked price (cost) of an item and the discount percentage. Calculate the discount amount and the sale price.

Solution

Python

# Program 2: Sale price of an item
item = input("Enter item name: ")
price = float(input("Enter marked price (Rs): "))
disc_pct = float(input("Enter discount (%): "))

discount = price * disc_pct / 100
sale_price = price - discount

print("Item           :", item)
print("Discount amount: Rs", round(discount, 2))
print("Sale price     : Rs", round(sale_price, 2))

Output

Enter item name: School bag
Enter marked price (Rs): 2499
Enter discount (%): 15
Item           : School bag
Discount amount: Rs 374.85
Sale price     : Rs 2124.15

Viva questions (3)

Which operator has higher precedence in price * disc_pct / 100? * and / have equal precedence and are evaluated left to right.
What would happen without float()? Python would raise a TypeError because a string cannot be multiplied by a float.
How can you print the sale price only if the discount is valid (0 to 100)? Put the calculation inside if 0 <= disc_pct <= 100: with an else message.

3Perimeter and area of shapes

Calculate the perimeter (circumference) and area of a triangle (three sides, using Heron's formula), a rectangle, a square and a circle. Use the math module for the square root and the value of pi.

Solution

Python

# Program 3: Perimeter and area of common shapes
import math

print("--- Triangle ---")
a = float(input("Side a: "))
b = float(input("Side b: "))
c = float(input("Side c: "))
s = (a + b + c) / 2                       # semi-perimeter
area_t = math.sqrt(s * (s - a) * (s - b) * (s - c))
print("Perimeter =", a + b + c, " Area =", round(area_t, 2))

print("--- Rectangle ---")
l = float(input("Length: "))
w = float(input("Breadth: "))
print("Perimeter =", 2 * (l + w), " Area =", l * w)

print("--- Square ---")
side = float(input("Side: "))
print("Perimeter =", 4 * side, " Area =", side ** 2)

print("--- Circle ---")
r = float(input("Radius: "))
print("Circumference =", round(2 * math.pi * r, 2), " Area =", round(math.pi * r ** 2, 2))

Output

--- Triangle ---
Side a: 3
Side b: 4
Side c: 5
Perimeter = 12.0  Area = 6.0
--- Rectangle ---
Length: 12
Breadth: 7.5
Perimeter = 39.0  Area = 90.0
--- Square ---
Side: 6
Perimeter = 24.0  Area = 36.0
--- Circle ---
Radius: 7
Circumference = 43.98  Area = 153.94

Viva questions (4)

What is the semi-perimeter in Heron's formula? Half the sum of the three sides, s = (a + b + c) / 2.
Which is evaluated first in math.pi * r 2? The exponent has higher precedence than , so r * 2 is found first.
Why do we write import math? sqrt() and pi belong to the math module, which must be imported before use.
What is the data type of math.pi? float.

4Simple and compound interest

Accept principal, annual rate of interest and time in years. Calculate simple interest and compound interest (compounded yearly).

SI = P x R x T / 100 and Amount = P(1 + R/100)^T, so CI = Amount - P.

Solution

Python

# Program 4: Simple and compound interest
p = float(input("Enter principal (Rs): "))
r = float(input("Enter rate of interest (% per annum): "))
t = float(input("Enter time (years): "))

si = p * r * t / 100
amount = p * (1 + r / 100) ** t
ci = amount - p

print("Simple interest   : Rs", round(si, 2))
print("Compound interest : Rs", round(ci, 2))
print("Amount with CI    : Rs", round(amount, 2))
print("CI exceeds SI by  : Rs", round(ci - si, 2))

Output

Enter principal (Rs): 50000
Enter rate of interest (% per annum): 8
Enter time (years): 3
Simple interest   : Rs 12000.0
Compound interest : Rs 12985.6
Amount with CI    : Rs 62985.6
CI exceeds SI by  : Rs 985.6

Viva questions (3)

Which operator is used for power in Python? The ** operator.
Why is CI more than SI for the same P, R and T (T > 1)? In CI, interest is also earned on the interest of earlier years.
Name the arithmetic operators used in this program. * (multiply), / (divide), + (add), - (subtract) and ** (exponent).

5Profit or loss

Accept the cost price and selling price of an item. Find whether there is a profit, a loss or neither, and print the amount and percentage.

Solution

Python

# Program 5: Profit or loss
cp = float(input("Enter cost price (Rs): "))
sp = float(input("Enter selling price (Rs): "))

if sp > cp:
    profit = sp - cp
    print("Profit of Rs", profit)
    print("Profit % =", round(profit / cp * 100, 2))
elif cp > sp:
    loss = cp - sp
    print("Loss of Rs", loss)
    print("Loss % =", round(loss / cp * 100, 2))
else:
    print("No profit, no loss")

Output

(Run 1)
Enter cost price (Rs): 1250
Enter selling price (Rs): 1400
Profit of Rs 150.0
Profit % = 12.0

(Run 2)
Enter cost price (Rs): 800
Enter selling price (Rs): 720
Loss of Rs 80.0
Loss % = 10.0

(Run 3)
Enter cost price (Rs): 500
Enter selling price (Rs): 500
No profit, no loss

Viva questions (3)

Why is the percentage calculated on the cost price? Profit and loss percentages are always expressed as a part of the cost price.
How many sample runs are needed to test this program fully? Three: one for profit, one for loss and one where CP equals SP.
What is the difference between if-elif-else and separate if statements? In if-elif-else only one block runs; separate ifs are all checked.

6EMI calculator

Accept the loan amount, annual interest rate and period in years. Calculate the Equated Monthly Instalment:

EMI = P x r x (1 + r)^n / ((1 + r)^n - 1), where r is the monthly rate (annual rate / 12 / 100) and n is the number of months.

Solution

Python

# Program 6: EMI calculation
p = float(input("Enter loan amount (Rs): "))
annual_rate = float(input("Enter annual interest rate (%): "))
years = int(input("Enter loan period (years): "))

r = annual_rate / 12 / 100        # monthly rate
n = years * 12                    # number of monthly instalments

emi = p * r * (1 + r) ** n / ((1 + r) ** n - 1)
total_paid = emi * n

print("Monthly EMI    : Rs", round(emi, 2))
print("Total payment  : Rs", round(total_paid, 2))
print("Total interest : Rs", round(total_paid - p, 2))

Output

Enter loan amount (Rs): 500000
Enter annual interest rate (%): 9
Enter loan period (years): 5
Monthly EMI    : Rs 10379.18
Total payment  : Rs 622750.66
Total interest : Rs 122750.66

Viva questions (3)

Why is the annual rate divided by 12? EMI is paid monthly, so a monthly rate is needed.
Why are brackets needed around ((1 + r) n - 1)? Without them, the division would apply only to (1 + r) n, giving a wrong answer.
Which data type is n? int, because years was converted with int() and multiplied by 12.

7GST calculation

Accept the price of an item, the quantity and the GST rate (%). Calculate the taxable value, total GST, the CGST and SGST shares (half each for a sale within the same state) and the bill amount.

Solution

Python

# Program 7: GST on a purchase (sale within the same state)
item = input("Item name: ")
price = float(input("Price per unit (Rs): "))
qty = int(input("Quantity: "))
rate = float(input("GST rate (%): "))

taxable = price * qty
gst = taxable * rate / 100
cgst = gst / 2
sgst = gst / 2
bill = taxable + gst

print("Item          :", item)
print("Taxable value : Rs", taxable)
print("CGST @", rate / 2, "% : Rs", cgst)
print("SGST @", rate / 2, "% : Rs", sgst)
print("Total GST     : Rs", gst)
print("Bill amount   : Rs", bill)

Output

Item name: Cricket bat
Price per unit (Rs): 1800
Quantity: 2
GST rate (%): 18
Item          : Cricket bat
Taxable value : Rs 3600.0
CGST @ 9.0 % : Rs 324.0
SGST @ 9.0 % : Rs 324.0
Total GST     : Rs 648.0
Bill amount   : Rs 4248.0

Viva questions (3)

What are CGST and SGST? Central GST and State GST; for a sale within a state, the total GST is split equally between them.
Why is qty converted with int() but price with float()? Quantity is a whole number, while price may have paise.
How would you change the program for a sale to another state? Charge the full GST as IGST instead of splitting it.

8Largest and smallest number in a list

Accept a list of numbers from the user. Find the largest and smallest numbers using a loop, and confirm the answer with the built-in functions max() and min().

Solution

Python

# Program 8: Largest and smallest in a list
data = input("Enter numbers separated by spaces: ")
nums = []
for word in data.split():
    nums.append(int(word))

largest = nums[0]
smallest = nums[0]
for x in nums:
    if x > largest:
        largest = x
    if x < smallest:
        smallest = x

print("List     :", nums)
print("Largest  :", largest, "| using max():", max(nums))
print("Smallest :", smallest, "| using min():", min(nums))

Output

Enter numbers separated by spaces: 45 12 78 -3 56 90 23
List     : [45, 12, 78, -3, 56, 90, 23]
Largest  : 90 | using max(): 90
Smallest : -3 | using min(): -3

Viva questions (3)

What does split() return? A list of the words in the string, split on spaces.
Why do we start largest with nums[0] and not 0? If all numbers were negative, 0 would wrongly stay the largest.
Name two other built-in functions that work on lists. len() and sum().

9Third largest and third smallest number in a list

Accept a list of numbers (it may contain repeated values). Print the third largest and the third smallest distinct values.

Solution

Python

# Program 9: Third largest and third smallest
data = input("Enter numbers separated by spaces: ")
nums = []
for word in data.split():
    nums.append(int(word))

unique = []                 # keep each value only once
for x in nums:
    if x not in unique:
        unique.append(x)

if len(unique) < 3:
    print("Need at least three different numbers.")
else:
    unique.sort()
    print("Distinct values in order :", unique)
    print("Third smallest :", unique[2])
    print("Third largest  :", unique[-3])

Output

Enter numbers separated by spaces: 45 12 78 12 56 90 23 90
Distinct values in order : [12, 23, 45, 56, 78, 90]
Third smallest : 45
Third largest  : 56

Viva questions (3)

Why do we remove repeated values first? Otherwise a repeated largest value, such as 90 twice, would be counted twice.
What does unique[-3] give? The third element from the end, which is the third largest after sorting in ascending order.
What does the not in operator do? It returns True when the value is not present in the list.

10Sum of squares of the first 100 natural numbers

Find 1^2 + 2^2 + ... + 100^2 using a loop. Check the answer with the formula n(n + 1)(2n + 1) / 6.

Solution

Python

# Program 10: Sum of squares of first 100 natural numbers
total = 0
for i in range(1, 101):
    total = total + i * i
print("Sum using loop    :", total)

n = 100
print("Sum using formula :", n * (n + 1) * (2 * n + 1) // 6)

Output

Sum using loop    : 338350
Sum using formula : 338350

Viva questions (3)

Why is the stop value of range 101? range() stops before the stop value, so 101 is needed to include 100.
What is the difference between / and //? / gives a float result; // gives the floor (integer) quotient.
How would you change the loop to add cubes instead? Replace i * i with i ** 3.

11First n multiples of a number

Accept a number and a count n. Print the first n multiples of the number in table form using a for loop.

Solution

Python

# Program 11: First n multiples of a number
num = int(input("Enter the number: "))
n = int(input("How many multiples? "))

for i in range(1, n + 1):
    print(num, "x", i, "=", num * i)

Output

Enter the number: 7
How many multiples? 10
7 x 1 = 7
7 x 2 = 14
7 x 3 = 21
7 x 4 = 28
7 x 5 = 35
7 x 6 = 42
7 x 7 = 49
7 x 8 = 56
7 x 9 = 63
7 x 10 = 70

Viva questions (3)

Rewrite the loop using while. i = 1; while i <= n: print(...); i = i + 1
What happens if the user enters 0 for n? range(1, 1) is empty, so nothing is printed.
Which values does range(1, n + 1) produce when n is 10? 1 to 10.

12Count vowels in a string

Accept a string from the user. Count the vowels in it (both upper and lower case) and also show how many times each vowel appears.

Solution

Python

# Program 12: Count vowels in a string
text = input("Enter a string: ")
vowels = "aeiou"
count = 0
for ch in text.lower():
    if ch in vowels:
        count = count + 1
print("Total vowels:", count)

for v in vowels:
    print(v, ":", text.lower().count(v))

Output

Enter a string: Informatics Practices is Fun
Total vowels: 9
a : 2
e : 1
i : 4
o : 1
u : 1

Viva questions (3)

Why do we use lower()? So that capital vowels such as I and A are also counted.
Is a string mutable? No. lower() returns a new string; the original text is unchanged.
What does the in operator check here? Whether the character ch is one of the characters in the string vowels.

13Words starting with a given alphabet

Accept a sentence and an alphabet. Print every word of the sentence that starts with that alphabet (ignore the case) and the number of such words.

Solution

Python

# Program 13: Words starting with a given alphabet
sentence = input("Enter a sentence: ")
letter = input("Enter the alphabet: ")

found = 0
for word in sentence.split():
    if word[0].lower() == letter.lower():
        print(word)
        found = found + 1
print("Number of words starting with", letter, ":", found)

Output

Enter a sentence: Sachin scored a superb century at Sydney in style
Enter the alphabet: s
Sachin
scored
superb
Sydney
style
Number of words starting with s : 5

Viva questions (3)

What does word[0] give? The first character of the word (index 0).
What will word[-1] give? The last character of the word.
Name another way to test the first letter. word.lower().startswith(letter.lower()).

14Occurrences of an alphabet in each string

Accept n strings into a list and an alphabet. Print how many times the alphabet occurs in each string (ignoring case) and in all strings together.

Solution

Python

# Program 14: Occurrences of an alphabet in each string
n = int(input("How many strings? "))
words = []
for i in range(n):
    words.append(input("Enter string " + str(i + 1) + ": "))
letter = input("Enter the alphabet to count: ").lower()

total = 0
for w in words:
    c = w.lower().count(letter)
    print(w, "->", c)
    total = total + c
print("Total occurrences:", total)

Output

How many strings? 4
Enter string 1: banana
Enter string 2: Apple
Enter string 3: grape
Enter string 4: papaya
Enter the alphabet to count: a
banana -> 3
Apple -> 1
grape -> 1
papaya -> 3
Total occurrences: 8

Viva questions (3)

What does count() do for a string? It returns how many times a substring occurs in the string.
Why is the list words created empty first? Strings are added one by one using append() inside the loop.
Is count() also available for lists? Yes, list.count(x) gives how many times x appears in the list.

15Dictionary of states and capitals

Create a dictionary that stores the names of some Indian states and their capitals. Display it, search the capital of a state entered by the user, and add a new state.

Solution

Python

# Program 15: States and their capitals
capitals = {"Kerala": "Thiruvananthapuram", "Assam": "Dispur",
            "Gujarat": "Gandhinagar", "Odisha": "Bhubaneswar",
            "Punjab": "Chandigarh", "Telangana": "Hyderabad"}

print("State          Capital")
for state in capitals:
    print(state.ljust(14), capitals[state])

s = input("Enter a state to find its capital: ").title()
if s in capitals:
    print("Capital of", s, "is", capitals[s])
else:
    print(s, "is not in the dictionary")

capitals["Sikkim"] = "Gangtok"        # adding a new key-value pair
print("After adding Sikkim, number of states:", len(capitals))

Output

State          Capital
Kerala         Thiruvananthapuram
Assam          Dispur
Gujarat        Gandhinagar
Odisha         Bhubaneswar
Punjab         Chandigarh
Telangana      Hyderabad
Enter a state to find its capital: kerala
Capital of Kerala is Thiruvananthapuram
After adding Sikkim, number of states: 7

Viva questions (4)

What is a key in a dictionary? The unique part of a key-value pair that is used to look up the value.
What does the in operator check for a dictionary? Whether the given key is present (it does not search the values).
What happens if we assign a value to an existing key? The old value is replaced; no new pair is added.
What does title() do? It makes the first letter of each word capital, so kerala becomes Kerala.

16Dictionary of students and marks in five subjects

Accept the names of n students and their marks in five subjects. Store them in a dictionary (name as key, list of marks as value). Display each student's marks, total and percentage, and the class topper.

Solution

Python

# Program 16: Students and marks in 5 subjects
result = {}
n = int(input("Number of students: "))
for i in range(n):
    name = input("Name: ")
    marks = []
    for m in input("Marks in 5 subjects (space separated): ").split():
        marks.append(int(m))
    result[name] = marks

print()
print("Name      Marks                   Total  Percent")
topper = ""
best = -1
for name, marks in result.items():
    tot = sum(marks)
    print(name.ljust(9), str(marks).ljust(23), tot, " ", tot / 5)
    if tot > best:
        best = tot
        topper = name
print("Topper:", topper, "with", best, "marks")

Output

Number of students: 3
Name: Aditi
Marks in 5 subjects (space separated): 82 76 91 68 88
Name: Faizan
Marks in 5 subjects (space separated): 74 89 65 90 79
Name: Harpreet
Marks in 5 subjects (space separated): 95 81 77 84 92

Name      Marks                   Total  Percent
Aditi     [82, 76, 91, 68, 88]    405   81.0
Faizan    [74, 89, 65, 90, 79]    397   79.4
Harpreet  [95, 81, 77, 84, 92]    429   85.8
Topper: Harpreet with 429 marks

Viva questions (4)

What is the value part of each dictionary entry here? A list of five integers.
What does items() return? Pairs of (key, value) that can be unpacked in a for loop.
Why is best set to -1 at the start? Any real total is at least 0, so the first student always replaces it.
Can a list be used as a dictionary key? No, keys must be immutable; a list is mutable.

17Highest and lowest values in a dictionary

A dictionary stores the points of the school houses in the annual sports meet. Find the highest and lowest points and the houses that scored them, first with a loop and then with max() and min() on values().

Solution

Python

# Program 17: Highest and lowest values in a dictionary
points = {"Tagore": 245, "Raman": 310, "Kalam": 198, "Bose": 276, "Sarojini": 230}

high_house = low_house = ""
high = None
low = None
for house, p in points.items():
    if high is None or p > high:
        high, high_house = p, house
    if low is None or p < low:
        low, low_house = p, house

print("Highest:", high_house, high)
print("Lowest :", low_house, low)
print("Check using max()/min():", max(points.values()), min(points.values()))

Output

Highest: Raman 310
Lowest : Kalam 198
Check using max()/min(): 310 198

Viva questions (3)

What does values() return? A view of all the values in the dictionary.
Can max(points) be used to find the highest points? No, max(points) compares the keys (house names) alphabetically.
What is the purpose of None here? It marks that no value has been seen yet, so the first item sets both high and low.

18List methods and built-in functions (Extra)

Use the list methods and functions in the syllabus on a list of a batter's scores in recent matches: append(), insert(), count(), index(), remove(), pop(), reverse(), sort(), len(), min(), max() and sum().

Solution

Python

# Program 18: List methods on match scores
scores = [45, 12, 78, 45, 102, 0, 33]
print("Scores        :", scores)
print("Matches       :", len(scores), "| Total:", sum(scores))
print("Best / Worst  :", max(scores), "/", min(scores))
print("Times scored 45:", scores.count(45))
print("First 45 at index:", scores.index(45))

scores.append(67)           # add at the end
scores.insert(0, 8)         # add at the beginning
print("After append and insert:", scores)

scores.remove(0)            # removes the first 0
last = scores.pop()         # removes and returns the last item
print("Popped", last, "->", scores)

scores.reverse()
print("Reversed      :", scores)
scores.sort()
print("Ascending     :", scores)
scores.sort(reverse=True)
print("Descending    :", scores)

Output

Scores        : [45, 12, 78, 45, 102, 0, 33]
Matches       : 7 | Total: 315
Best / Worst  : 102 / 0
Times scored 45: 2
First 45 at index: 0
After append and insert: [8, 45, 12, 78, 45, 102, 0, 33, 67]
Popped 67 -> [8, 45, 12, 78, 45, 102, 33]
Reversed      : [33, 102, 45, 78, 12, 45, 8]
Ascending     : [8, 12, 33, 45, 45, 78, 102]
Descending    : [102, 78, 45, 45, 33, 12, 8]

Viva questions (3)

What is the difference between remove() and pop()? remove() deletes by value; pop() deletes by index (last item by default) and returns it.
Does sort() return a new list? No, it sorts the list in place and returns None.
What does insert(0, 8) do? It places 8 at index 0 and shifts the other items right.

19Dictionary methods (Extra)

Use the dictionary functions in the syllabus on a dictionary of train numbers and train names: dict(), len(), keys(), values(), items(), update(), del and clear().

Solution

Python

# Program 19: Dictionary methods
trains = dict([(12951, "Mumbai Rajdhani"), (12002, "Bhopal Shatabdi"),
               (12627, "Karnataka Express")])
print("Trains :", trains)
print("Count  :", len(trains))
print("Numbers:", list(trains.keys()))
print("Names  :", list(trains.values()))

trains.update({12301: "Howrah Rajdhani", 12002: "New Delhi Shatabdi"})
print("After update():")
for no, name in trains.items():
    print(" ", no, name)

del trains[12627]
print("After del:", trains)
trains.clear()
print("After clear():", trains, "length", len(trains))

Output

Trains : {12951: 'Mumbai Rajdhani', 12002: 'Bhopal Shatabdi', 12627: 'Karnataka Express'}
Count  : 3
Numbers: [12951, 12002, 12627]
Names  : ['Mumbai Rajdhani', 'Bhopal Shatabdi', 'Karnataka Express']
After update():
  12951 Mumbai Rajdhani
  12002 New Delhi Shatabdi
  12627 Karnataka Express
  12301 Howrah Rajdhani
After del: {12951: 'Mumbai Rajdhani', 12002: 'New Delhi Shatabdi', 12301: 'Howrah Rajdhani'}
After clear(): {} length 0

Viva questions (3)

What does update() do when a key already exists? It replaces the old value with the new one.
What is the difference between del d[k] and d.clear()? del removes one pair; clear() removes all pairs but keeps the empty dictionary.
How was dict() used here? It built a dictionary from a list of (key, value) tuples.

20Creating NumPy arrays from lists (Extra)

Create one-dimensional and two-dimensional NumPy arrays from Python lists of marks. Display the attributes ndim, shape, size and dtype, and show how an operation applies to every element at once.

Solution

Python

# Program 20: NumPy arrays from lists
import numpy as np

marks = [78, 85, 92, 66, 74]
arr = np.array(marks)
print("Array :", arr)
print("ndim:", arr.ndim, "| shape:", arr.shape, "| size:", arr.size, "| dtype:", arr.dtype)
print("Add 5 grace marks:", arr + 5)
print("Marks out of 50  :", arr / 2)

two_d = np.array([[78, 85, 92], [66, 74, 81]])
print("2-D array:")
print(two_d)
print("shape:", two_d.shape)

Output

Array : [78 85 92 66 74]
ndim: 1 | shape: (5,) | size: 5 | dtype: int64
Add 5 grace marks: [83 90 97 71 79]
Marks out of 50  : [39.  42.5 46.  33.  37. ]
2-D array:
[[78 85 92]
 [66 74 81]]
shape: (2, 3)

Viva questions (3)

How is a NumPy array different from a list? All elements of an array have the same type, and arithmetic applies to every element.
What does shape (2, 3) mean? 2 rows and 3 columns.
What happens to [1, 2] + [3, 4] for lists and for arrays? Lists are joined to [1, 2, 3, 4]; arrays are added element-wise to [4 6].

SQL Queries

1Create a database

Create a database named school and open it for use.

Solution

MySQL

CREATE DATABASE school;
USE school;

Output

Query OK, 1 row affected

Database changed

Viva questions (3)

What does CREATE DATABASE do? It creates a new, empty database (a container for tables).
Why is USE needed? It selects the database in which the following commands will work.
Which command lists all databases on the server? SHOW DATABASES;

2Create the STUDENT table

Create a table STUDENT with the attributes student id (Rno), class, section, gender, name, date of birth and marks. Rno is the primary key. Display the structure of the table.

Solution

MySQL

CREATE TABLE STUDENT (
    Rno     INT PRIMARY KEY,
    Class   INT,
    Section CHAR(1),
    Gender  CHAR(1),
    Name    VARCHAR(30) NOT NULL,
    DOB     DATE,
    Marks   INT
);
DESCRIBE STUDENT;

Output

Query OK, 0 rows affected

+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| Rno     | int         | NO   | PRI | NULL    |       |
| Class   | int         | YES  |     | NULL    |       |
| Section | char(1)     | YES  |     | NULL    |       |
| Gender  | char(1)     | YES  |     | NULL    |       |
| Name    | varchar(30) | NO   |     | NULL    |       |
| DOB     | date        | YES  |     | NULL    |       |
| Marks   | int         | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
7 rows in set

Viva questions (4)

Why is Rno chosen as the primary key? It is unique for every student and never empty.
What is the difference between CHAR(1) and VARCHAR(30)? CHAR has a fixed length; VARCHAR stores only the characters entered, up to the limit.
In which format does MySQL store a DATE? 'YYYY-MM-DD'.
Is CREATE TABLE a DDL or a DML command? DDL (Data Definition Language).

3Insert ten records

Insert the details of ten students into STUDENT. The marks of Kabir are not available yet, so store NULL.

Solution

MySQL

INSERT INTO STUDENT VALUES
(101, 11, 'A', 'F', 'Ananya Sharma',  '2009-04-12', 88),
(102, 11, 'A', 'M', 'Rohan Verma',    '2009-08-23', 46),
(103, 11, 'B', 'F', 'Fatima Khan',    '2010-01-05', 92),
(104, 11, 'B', 'M', 'Arjun Nair',     '2009-11-30', 71),
(105, 11, 'A', 'M', 'Kabir Singh',    '2010-03-17', NULL),
(106, 12, 'A', 'F', 'Ishita Reddy',   '2008-06-09', 64),
(107, 12, 'B', 'M', 'Manpreet Gill',  '2008-12-25', 39),
(108, 12, 'B', 'F', 'Sneha Iyer',     '2009-02-14', 95),
(109, 11, 'B', 'F', 'Diya Mukherjee', '2009-07-01', 55),
(110, 12, 'A', 'M', 'Vikram Rao',     '2008-10-19', 50);

Output

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

Viva questions (4)

Can the ten rows be inserted with ten separate INSERT statements? Yes; one INSERT with many rows is just shorter.
What happens if we insert another row with Rno 101? MySQL gives a duplicate entry error because Rno is the primary key.
Why are text and dates written in quotes but numbers are not? Quotes mark string and date literals; numbers are written as they are.
What does NULL mean? The value is unknown or not available; it is not the same as 0.

4Display the entire table

Display all the rows and columns of STUDENT.

Solution

MySQL

SELECT * FROM STUDENT;

Output

+-----+-------+---------+--------+----------------+------------+-------+
| Rno | Class | Section | Gender | Name           | DOB        | Marks |
+-----+-------+---------+--------+----------------+------------+-------+
| 101 |    11 | A       | F      | Ananya Sharma  | 2009-04-12 |    88 |
| 102 |    11 | A       | M      | Rohan Verma    | 2009-08-23 |    46 |
| 103 |    11 | B       | F      | Fatima Khan    | 2010-01-05 |    92 |
| 104 |    11 | B       | M      | Arjun Nair     | 2009-11-30 |    71 |
| 105 |    11 | A       | M      | Kabir Singh    | 2010-03-17 |  NULL |
| 106 |    12 | A       | F      | Ishita Reddy   | 2008-06-09 |    64 |
| 107 |    12 | B       | M      | Manpreet Gill  | 2008-12-25 |    39 |
| 108 |    12 | B       | F      | Sneha Iyer     | 2009-02-14 |    95 |
| 109 |    11 | B       | F      | Diya Mukherjee | 2009-07-01 |    55 |
| 110 |    12 | A       | M      | Vikram Rao     | 2008-10-19 |    50 |
+-----+-------+---------+--------+----------------+------------+-------+
10 rows in set

Viva questions (3)

What does * mean in SELECT *? All columns of the table.
Is SELECT a DDL, DML or DQL command? DQL (Data Query Language).
How is NULL shown in the output? As the word NULL.

5Students scoring more than 50

Display Rno, Name and Marks of the students who scored more than 50 marks.

Solution

MySQL

SELECT Rno, Name, Marks FROM STUDENT
WHERE Marks > 50;

Output

+-----+----------------+-------+
| Rno | Name           | Marks |
+-----+----------------+-------+
| 101 | Ananya Sharma  |    88 |
| 103 | Fatima Khan    |    92 |
| 104 | Arjun Nair     |    71 |
| 106 | Ishita Reddy   |    64 |
| 108 | Sneha Iyer     |    95 |
| 109 | Diya Mukherjee |    55 |
+-----+----------------+-------+
6 rows in set

Viva questions (3)

Why is Vikram (50 marks) not shown? The condition is > 50, so 50 is excluded; >= 50 would include it.
Why is Kabir not shown? His marks are NULL, and a comparison with NULL is never true.
Name the relational operators in SQL. =, <, >, <=, >= and <> (or !=).

6Students born in a given year

Display Rno, Name and DOB of the students born between '2009-01-01' and '2009-12-31'. (The CBSE list uses the year 2005; we use 2009, which matches the age of students in Class 11 today.)

Solution

MySQL

SELECT Rno, Name, DOB FROM STUDENT
WHERE DOB BETWEEN '2009-01-01' AND '2009-12-31';

Output

+-----+----------------+------------+
| Rno | Name           | DOB        |
+-----+----------------+------------+
| 101 | Ananya Sharma  | 2009-04-12 |
| 102 | Rohan Verma    | 2009-08-23 |
| 104 | Arjun Nair     | 2009-11-30 |
| 108 | Sneha Iyer     | 2009-02-14 |
| 109 | Diya Mukherjee | 2009-07-01 |
+-----+----------------+------------+
5 rows in set

Viva questions (3)

Are the end values included in BETWEEN? Yes, BETWEEN includes both the lower and the upper value.
Rewrite this condition without BETWEEN. DOB >= '2009-01-01' AND DOB <= '2009-12-31'.
Can BETWEEN be used with numbers too? Yes, for example Marks BETWEEN 40 AND 60.

7Using AND

Display the names and marks of the students of Class 11, Section A.

Solution

MySQL

SELECT Name, Marks FROM STUDENT
WHERE Class = 11 AND Section = 'A';

Output

+---------------+-------+
| Name          | Marks |
+---------------+-------+
| Ananya Sharma |    88 |
| Rohan Verma   |    46 |
| Kabir Singh   |  NULL |
+---------------+-------+
3 rows in set

Viva questions (3)

When is a condition with AND true? Only when both parts are true.
Is 'A' the same as 'a' in this query? In MySQL's default settings, text comparison is not case-sensitive, so yes.
Name the three logical operators. AND, OR and NOT.

8Using OR

Display the details of the students who are in Class 12 or who scored less than 50 marks.

Solution

MySQL

SELECT Rno, Name, Class, Marks FROM STUDENT
WHERE Class = 12 OR Marks < 50;

Output

+-----+---------------+-------+-------+
| Rno | Name          | Class | Marks |
+-----+---------------+-------+-------+
| 102 | Rohan Verma   |    11 |    46 |
| 106 | Ishita Reddy  |    12 |    64 |
| 107 | Manpreet Gill |    12 |    39 |
| 108 | Sneha Iyer    |    12 |    95 |
| 110 | Vikram Rao    |    12 |    50 |
+-----+---------------+-------+-------+
5 rows in set

Viva questions (3)

When is a condition with OR true? When at least one of the parts is true.
Why does Rohan appear though he is in Class 11? His marks (46) are below 50, which satisfies the second part.
Which operator is evaluated first, AND or OR? AND has higher precedence; use brackets to make the order clear.

9Using NOT

Display the names and sections of the students who are not in Section B.

Solution

MySQL

SELECT Name, Class, Section FROM STUDENT
WHERE NOT Section = 'B';

Output

+---------------+-------+---------+
| Name          | Class | Section |
+---------------+-------+---------+
| Ananya Sharma |    11 | A       |
| Rohan Verma   |    11 | A       |
| Kabir Singh   |    11 | A       |
| Ishita Reddy  |    12 | A       |
| Vikram Rao    |    12 | A       |
+---------------+-------+---------+
5 rows in set

Viva questions (3)

Write the same condition without NOT. Section <> 'B' (or Section != 'B').
What does NOT do? It reverses the result of a condition.
How many rows would you expect? Every student in Section A, that is 5 rows.

10Using IS NULL

Display the Rno and Name of the students whose marks have not been entered.

Solution

MySQL

SELECT Rno, Name FROM STUDENT
WHERE Marks IS NULL;

Output

+-----+-------------+
| Rno | Name        |
+-----+-------------+
| 105 | Kabir Singh |
+-----+-------------+
1 row in set

Viva questions (3)

Why can we not write Marks = NULL? NULL is not a value, so = NULL is never true; IS NULL must be used.
What is the opposite of IS NULL? IS NOT NULL.
Is an empty string '' the same as NULL? No; '' is a known (empty) value, while NULL means unknown.

11Using IS NOT NULL with another condition

Display the names and marks of the students whose marks are entered and are below 60.

Solution

MySQL

SELECT Name, Marks FROM STUDENT
WHERE Marks IS NOT NULL AND Marks < 60;

Output

+----------------+-------+
| Name           | Marks |
+----------------+-------+
| Rohan Verma    |    46 |
| Manpreet Gill  |    39 |
| Diya Mukherjee |    55 |
| Vikram Rao     |    50 |
+----------------+-------+
4 rows in set

Viva questions (3)

Would the result change if IS NOT NULL were removed? No, because NULL < 60 is not true; it is kept here to make the intention clear.
Which keyword joins the two conditions? AND.
What is the result of the comparison NULL < 60? It is unknown (NULL), and WHERE treats unknown as false, so the row is not shown.

12Using NOT BETWEEN

Display the names and marks of the students whose marks are not between 40 and 80.

Solution

MySQL

SELECT Name, Marks FROM STUDENT
WHERE Marks NOT BETWEEN 40 AND 80;

Output

+---------------+-------+
| Name          | Marks |
+---------------+-------+
| Ananya Sharma |    88 |
| Fatima Khan   |    92 |
| Manpreet Gill |    39 |
| Sneha Iyer    |    95 |
+---------------+-------+
4 rows in set

Viva questions (3)

Is a student with exactly 80 marks shown? No; 80 lies inside the range 40 to 80, so NOT BETWEEN excludes it.
Why is Kabir not shown? NULL NOT BETWEEN 40 AND 80 is not true.
Write the same condition using OR. Marks < 40 OR Marks > 80.

13Expressions and column alias

Marks are out of 100. Display the name, marks and the marks converted to a score out of 70 for the students of Class 11. Give the new column the heading Out_of_70.

Solution

MySQL

SELECT Name, Marks, Marks * 70 / 100 AS Out_of_70
FROM STUDENT
WHERE Class = 11;

Output

+----------------+-------+-----------+
| Name           | Marks | Out_of_70 |
+----------------+-------+-----------+
| Ananya Sharma  |    88 |   61.6000 |
| Rohan Verma    |    46 |   32.2000 |
| Fatima Khan    |    92 |   64.4000 |
| Arjun Nair     |    71 |   49.7000 |
| Kabir Singh    |  NULL |      NULL |
| Diya Mukherjee |    55 |   38.5000 |
+----------------+-------+-----------+
6 rows in set

Viva questions (3)

What is an alias? A temporary heading given to a column in the output using AS.
Does this query change the marks stored in the table? No; SELECT only displays the calculated values.
Why is the result of Kabir NULL? Any arithmetic with NULL gives NULL.

14Using DISTINCT

Display the different classes present in the table, each only once. (DISTINCT is covered in the NCERT book.)

Solution

MySQL

SELECT DISTINCT Class FROM STUDENT;

Output

+-------+
| Class |
+-------+
|    11 |
|    12 |
+-------+
2 rows in set

Viva questions (3)

What does DISTINCT do? It removes duplicate values from the output.
How many rows does SELECT Class FROM STUDENT give? 10, one for each student.
Can DISTINCT be used with two columns? Yes; it then removes rows where both values repeat.

15UPDATE a single record

Kabir (Rno 105) has now taken the test and scored 67. Store his marks and display his record.

Solution

MySQL

UPDATE STUDENT SET Marks = 67
WHERE Rno = 105;
SELECT Rno, Name, Marks FROM STUDENT WHERE Rno = 105;

Output

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

+-----+-------------+-------+
| Rno | Name        | Marks |
+-----+-------------+-------+
| 105 | Kabir Singh |    67 |
+-----+-------------+-------+
1 row in set

Viva questions (3)

What would happen if the WHERE clause were left out? The marks of every student would become 67.
Is UPDATE a DDL or DML command? DML.
Why is the primary key used in the WHERE clause? It identifies exactly one row.

16UPDATE several records

Give 2 grace marks to every student who has scored less than 40, then display all students with fewer than 45 marks.

Solution

MySQL

UPDATE STUDENT SET Marks = Marks + 2
WHERE Marks < 40;
SELECT Name, Marks FROM STUDENT WHERE Marks < 45;

Output

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

+---------------+-------+
| Name          | Marks |
+---------------+-------+
| Manpreet Gill |    41 |
+---------------+-------+
1 row in set

Viva questions (3)

How many rows were changed? One, the only student with marks below 40 (Manpreet).
Can an UPDATE use the old value of a column? Yes; Marks = Marks + 2 adds 2 to the existing value.
How can you update two columns in one statement? Separate them with commas: SET Marks = 50, Section = 'A'.

17ALTER TABLE: add a column

Add a column House of type VARCHAR(15) to STUDENT and set the house of Section A students to 'Tagore' and Section B students to 'Raman'.

Solution

MySQL

ALTER TABLE STUDENT ADD House VARCHAR(15);
UPDATE STUDENT SET House = 'Tagore' WHERE Section = 'A';
UPDATE STUDENT SET House = 'Raman' WHERE Section = 'B';
SELECT Name, Section, House FROM STUDENT;

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, 5 rows affected
Rows matched: 5  Changed: 5  Warnings: 0

+----------------+---------+--------+
| Name           | Section | House  |
+----------------+---------+--------+
| Ananya Sharma  | A       | Tagore |
| Rohan Verma    | A       | Tagore |
| Fatima Khan    | B       | Raman  |
| Arjun Nair     | B       | Raman  |
| Kabir Singh    | A       | Tagore |
| Ishita Reddy   | A       | Tagore |
| Manpreet Gill  | B       | Raman  |
| Sneha Iyer     | B       | Raman  |
| Diya Mukherjee | B       | Raman  |
| Vikram Rao     | A       | Tagore |
+----------------+---------+--------+
10 rows in set

Viva questions (3)

What value does a newly added column have in old rows? NULL, until it is updated.
Is ALTER TABLE a DDL or DML command? DDL, because it changes the structure of the table.
Which command shows the new structure? DESCRIBE STUDENT; (or DESC STUDENT;).

18ALTER TABLE: modify and drop a column

Increase the size of the Name column to VARCHAR(40), remove the House column, and show the new structure.

Solution

MySQL

ALTER TABLE STUDENT MODIFY Name VARCHAR(40) NOT NULL;
ALTER TABLE STUDENT DROP COLUMN House;
DESCRIBE STUDENT;

Output

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

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

+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| Rno     | int         | NO   | PRI | NULL    |       |
| Class   | int         | YES  |     | NULL    |       |
| Section | char(1)     | YES  |     | NULL    |       |
| Gender  | char(1)     | YES  |     | NULL    |       |
| Name    | varchar(40) | NO   |     | NULL    |       |
| DOB     | date        | YES  |     | NULL    |       |
| Marks   | int         | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
7 rows in set

Viva questions (3)

Which clause of ALTER TABLE changes the data type or size of a column? MODIFY.
What happens to the data of a dropped column? It is deleted permanently along with the column.
Can we reduce VARCHAR(40) to VARCHAR(5)? Only if no existing value is longer than 5 characters; otherwise MySQL reports an error in strict mode.

19DELETE a record

Manpreet (Rno 107) has left the school. Delete his record and display the remaining students of Class 12.

Solution

MySQL

DELETE FROM STUDENT WHERE Rno = 107;
SELECT Rno, Name, Marks FROM STUDENT WHERE Class = 12;

Output

Query OK, 1 row affected

+-----+--------------+-------+
| Rno | Name         | Marks |
+-----+--------------+-------+
| 106 | Ishita Reddy |    64 |
| 108 | Sneha Iyer   |    95 |
| 110 | Vikram Rao   |    50 |
+-----+--------------+-------+
3 rows in set

Viva questions (3)

What is the difference between DELETE and DROP TABLE? DELETE removes rows; DROP TABLE removes the table itself with its structure.
What does DELETE FROM STUDENT; without WHERE do? It deletes all rows but keeps the empty table.
Can a deleted row be brought back with a command in this practical? No; the row has to be inserted again.

20DROP TABLE

Create a small table TEMP_LIST for practice, show the tables in the database, then remove TEMP_LIST with DROP TABLE.

Solution

MySQL

CREATE TABLE TEMP_LIST (Id INT, Item VARCHAR(20));
SHOW TABLES;
DROP TABLE TEMP_LIST;
SHOW TABLES;

Output

Query OK, 0 rows affected

+------------------+
| Tables_in_school |
+------------------+
| STUDENT          |
| TEMP_LIST        |
+------------------+
2 rows in set

Query OK, 0 rows affected

+------------------+
| Tables_in_school |
+------------------+
| STUDENT          |
+------------------+
1 row in set

Viva questions (3)

What is removed by DROP TABLE? The structure and all the data of the table.
Which command removes a whole database? DROP DATABASE name; (use with great care).
Is DROP a DDL command? Yes.