Scribd
Upload
9 views51 pages

Programm File Class 12

The document is a practical file for the academic year 2025-26 from S.S. Motasingh Sr. Sec. Model School, detailing various methods for creating and manipulating DataFrames using Python's pandas library. It includes code examples for creating DataFrames from different data structures, performing operations on rows and columns, accessing and filtering data, and importing/exporting CSV files. Additionally, it provides application-based questions to demonstrate practical use cases of DataFrames.

Uploaded by

harneet.export
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
9 views51 pages

Programm File Class 12

The document is a practical file for the academic year 2025-26 from S.S. Motasingh Sr. Sec. Model School, detailing various methods for creating and manipulating DataFrames using Python's pandas library. It includes code examples for creating DataFrames from different data structures, performing operations on rows and columns, accessing and filtering data, and importing/exporting CSV files. Additionally, it provides application-based questions to demonstrate practical use cases of DataFrames.

Uploaded by

harneet.export
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

S.S. MOTASINGH SR. SEC.

MODEL SCHOOL
JANAKPURI NEW DELHI 110058

ACADEMIC YEAR:2025-26

PRACTICAL FILE
NAME: GURNOOR KAUR
CLASS&SECTION: XII-C
SUBJECT: INFORMATICS PRACTICES
SUBJECT CODE: 065
SUBJECT TEACHER- MRS CHHAYA SINGH
DATAFRAME
1.1Dataframe creation using different method.
SOURCE CODE
# CREATING AN EMPTY DATAFRAME
import pandas as pd
df=[Link]()
print('EMPTY DATAFRAME')
print(df)
#1 CREATION OF DATAFRAME FROM DICTIONARY OF SERIES
S1=[Link](['ABC','DEF','GHI','JKL'])
S2=[Link](['W','X','Y','Z'])
S3=[Link]([28,31,13,35])
Dict={'NAME':S1,'SECTION':S2,'MARKS':S3}
DF=[Link](Dict)
print(DF)
#2 CREATION OF DATAFRAME FROM DICTIONARY OF
LIST[DEFAULT INDEX]
L1=[100,200,300,400]
L2=['A','S','D','F']
L3=[20,30,27,37]
D={'ICODE':L1,'NAME':L2,'QTY':L3}
DF=[Link](D)
print(DF)
#CREATION OF DATAFRAME FROM DICTIONARY OF
LIST[LABELLED INDEX]
L1=[100,200,300,400]
L2=['A','S','D','F']
L3=[20,30,27,37]
D={'ICODE':L1,'NAME':L2,'QTY':L3}
DF1=[Link](D,index=['I1','I2','I3','I4'])
print(DF1)
#3 CREATION OF DATAFRAME FROM LIST OF
DICTIONARY[DEFAULT INDEX]
D1={'ICODE':100,'NAME':'A','QTY':20}
D2={'ICODE':200,'NAME':'S','QTY':30}
D3={'ICODE':300,'NAME':'D','QTY':27}
D4={'ICODE':400,'NAME':'F','QTY':37}
LIST=[D1,D2,D3,D4]
df=[Link](LIST)
print(df)
#CREATION OF DATAFRAME FROM LIST OF
DICTIONARY[LABELLED INDEX]
D1={'ICODE':100,'NAME':'A','QTY':20}
D2={'ICODE':200,'NAME':'S','QTY':30}
D3={'ICODE':300,'NAME':'D','QTY':27}
D4={'ICODE':400,'NAME':'F','QTY':37}
LIST=[D1,D2,D3,D4]
df1=[Link](LIST,index=['I1','I2','I3','I4'])
print(df1)
#4 CREATION OF DATAFRAME FROM LIST OF LIST
DF2=[Link]([[75,69,60],[65,60,68],[60,62,50]],

index=['DEMON','ELINA','STEFAN'],columns=['ENGLISH','IP','E
CONOMICS'])
print(DF2)
#5 CREATION OF DATAFRAME FROM LIST OF SERIES
Ser1=[Link]([40000,80000,20000],index=['TANYA','AARAV'
,'ISHAAN'])
Ser2=[Link]([75000,90000,50000],index=['TANYA','AARAV'
,'ISHAAN'])
Ser3=[Link]([90000,55000,95000],index=['TANYA','AARAV'
,'ISHAAN'])
Emp=[Link]([Ser1,Ser2,Ser3],columns=['Jan','Feb','M
arch'])
print(Emp)

#6 CREATION OF DATAFRAME FROM DICTIONARY OF


DICTIONARY
Employee=[Link]({'NAME':
{'E101':'ATUL','E102':'RAJ','E103':'DARPAN','E104':'ANMOL','E
105':'PIYUSH'},
'DESIGNATION':
{'E101':'MANAGER','E102':'CLERK','E103':'ANALYST','E104':'CL
ERK','E105':'MANAGER'},
'SALARY':
{'E101':56000,'E102':25000,'E103':35000,'E104':28000,'E105':
58000},
'BONUS':
{'E101':15000,'E102':7000,'E103':9000,'E104':13000,'E105':12
000}})
print(Employee)
OUTPUT
EMPTY DATAFRAME
Empty DataFrame
Columns: []
Index: []

NAME SECTION MARKS


0 ABC W 28
1 DEF X 31
2 GHI Y 13
3 JKL Z 35

ICODE NAME QTY


0 100 A 20
1 200 S 30
2 300 D 27
3 400 F 37
ICODE NAME QTY
I1 100 A 20
I2 200 S 30
I3 300 D 27
I4 400 F 37

ICODE NAME QTY


0 100 A 20
1 200 S 30
2 300 D 27
3 400 F 37

ICODE NAME QTY


I1 100 A 20
I2 200 S 30
I3 300 D 27
I4 400 F 37
ENGLISH IP ECONOMICS
DEMON 75 69 60
ELINA 65 60 68
STEFAN 60 62 50

Jan Feb March


0 NaN NaN NaN
1 NaN NaN NaN
2 NaN NaN NaN

NAME DESIGNATION SALARY BONUS


E101 ATUL MANAGER 56000 15000
E102 RAJ CLERK 25000 7000
E103 DARPAN ANALYST 35000 9000
E104 ANMOL CLERK 28000 13000
E105 PIYUSH MANAGER 58000 12000
1.2 Operations on rows/columns in DataFrame
SOURCE CODE
import pandas as pd
df=[Link]({'NAME':['AMAN','KIRAN','CHETAN'],
'MARKS':[85,90,78]})
print(df)
# ADDING A NEW COLUMN IN DATAFRAME
df['GRADE']=['A','A+','B']
print(df)
#ADDING A NEW ROW
[Link][3]=['DINESH',92,'A']
print(df)
#DELETING A ROW IN DATAFRAME
df=[Link](1)
print(df)
#DELETING MULTIPLE ROWS IN DATAFRAME
df=[Link]([0,2])
print(df)
#DELETING A SINGLE COLUMN IN DATAFRAME
df=[Link]('GRADE',axis=1)
print(df)
#RENAMING ROW LABELS OF A DATAFRAME
df1=[Link](index={0:'Row1',2:'Row3'})
print(df1)
#RENAMING COLUMN LABELS OF A DATAFRAME
df=[Link](columns={'NAME':'STUDENT','MARKS':'SCORE'}
)
print(df)
OUTPUT
NAME MARKS
0 AMAN 85
1 KIRAN 90
2 CHETAN 78

NAME MARKS GRADE


0 AMAN 85 A
1 KIRAN 90 A+
2 CHETAN 78 B

NAME MARKS GRADE


0 AMAN 85 A
1 KIRAN 90 A+
2 CHETAN 78 B
3 DINESH 92 A

NAME MARKS GRADE


0 AMAN 85 A
2 CHETAN 78 B
3 DINESH 92 A

NAME MARKS GRADE


3 DINESH 92 A

NAME MARKS
3 DINESH 92

NAME MARKS
3 DINESH 92

STUDENT SCORE
3 DINESH 92
1.3 Accessing , slicing , filtering in dataframe
# DATAFRAME CREATION
import pandas as pd
result={'ARNAB':[Link]([90,97,91],index=['IP','ECONOMICS
','ACCOUNTS']),

'RIYA':[Link]([85,96,89],index=['IP','ECONOMICS','ACCOUN
TS']),

'CHETAN':[Link]([94,87,70],index=['IP','ECONOMICS','ACC
OUNTS']),

'TANYA':[Link]([95,71,67],index=['IP','ECONOMICS','ACCO
UNTS']),

'SURBHI':[Link]([99,65,75],index=['IP','ECONOMICS','ACCO
UNTS'])}
resultdf=[Link](result)
print(resultdf)
# LABEL BASED INDEX
result=[Link]['ACCOUNTS']
print(result)

result=[Link][:,'TANYA']
print(result)
result=[Link][['IP','ECONOMICS']]
print(result)
#BOOLEAN INDEXING
result=[Link]['ACCOUNTS']>90
print(result)

result=[Link][:,'CHETAN']>90
print(result)

result=[Link]['IP']<80
print(result)

result=[Link][:,'SURBHI']<70
print(result)
#ACCESSING THROUGH SLICING
result=[Link]['IP': 'ECONOMICS']
print(result)

result=[Link]['ECONOMICS':'ACCOUNTS', 'RIYA']
print(result)
result=[Link]['IP':'ECONOMICS',['ARNAB','CHETAN']]
print(result)
#FILTERING ROWS IN DATAFRAMES
result=[Link][[True, False, True]]
print(result)
OUTPUT
1. ARNAB RIYA CHETAN TANYA SURBHI
IP 90 85 94 95 99
ECONOMICS 97 96 87 71 65
ACCOUNTS 91 89 70 67 75

2. ARNAB 91
RIYA 89
CHETAN 70
TANYA 67
SURBHI 75
Name: ACCOUNTS, dtype: int64

3. IP 95
ECONOMICS 71
ACCOUNTS 67
Name: TANYA, dtype: int64
4. ARNAB RIYA CHETAN TANYA SURBHI
IP 90 85 94 95 99
ECONOMICS 97 96 87 71 65

5. ARNAB True
RIYA False
CHETAN False
TANYA False
SURBHI False
Name: ACCOUNTS, dtype: bool

6. IP True
ECONOMICS False
ACCOUNTS False
Name: CHETAN, dtype: bool

6. ARNAB False
RIYA False
CHETAN False
TANYA False
SURBHI False
Name: IP, dtype: bool
7. IP False
ECONOMICS True
ACCOUNTS False
Name: SURBHI, dtype: bool

8. ARNAB RIYA CHETAN TANYA SURBHI


IP 90 85 94 95 99
ECONOMICS 97 96 87 71 65

9. ECONOMICS 96
ACCOUNTS 89
Name: RIYA, dtype: int64

10. ARNAB CHETAN


IP 90 94
ECONOMICS 97 87

11. ARNAB RIYA CHETAN TANYA SURBHI


IP 90 85 94 95 99
ACCOUNTS 91 89 70 67 75
1.4 DataFrame and CSV
#IMPORTING AND EXPORTING DATA BETWEEN CSV FILES AND
DATAFRAMES
#IMPORTING A CSV FILE TO A DATAFRAME
import pandas as pd
df2=pd.read_csv
print(df2)
# EXPORTING A CSV FILE TO A DATAFRAME
df3=[Link]({'Roll no':[1,2,3],
'Name':['A','B','C']
})
df3.to_csv('PROGRAMM [Link]',index=False)
print(df3)
OUTPUT
<function read_csv at 0x000001D46AD9BE20>

Roll no Name
0 1 A
1 2 B
2 3 C

1.5 Application based question of dataframe


QUES1- A school wants to store the marks of 5 students in a
DataFrame. Create the DataFrame and display only the
names and maths marks.
CODE
import pandas as pd
data={'NAME':['Ankit','Mehul','Tara','Sonia'],
'ECO':[18,23,51,40],
'MATHS':[57,45,37,60]}
df=[Link](data)
print(df[['NAME','MATHS']])
OUTPUT
NAME MATHS
0 Ankit 57
1 Mehul 45
2 Tara 37
3 Sonia 60
QUES2- A company stores employee details. Create a
DataFrame and show only employees with salary above
30000.
CODE
data = {
'Name': ['Tanya', 'Ishaan', 'Aarav', 'Kritika'],
'Department': ['HR', 'Sales', 'IT', 'Admin'],
'Salary': [25000, 40000, 55000, 28000]
}
df = [Link](data)
print(df[df['Salary'] > 30000])
OUTPUT
Name Department Salary
1 Ishaan Sales 40000
2 Aarav IT 55000
QUES3- Create a DataFrame of 4 products. Add a new column
“DiscountPrice” = Price – 10% of Price.
CODE
import pandas as pd
data = {
'Product': ['Pen', 'Notebook', 'Bag', 'Bottle'],
'Price': [20, 50, 800, 150]
}
df = [Link](data)
df['DiscountPrice'] = df['Price'] - (0.10 * df['Price'])
print(df)

OUTPUT
Product Price DiscountPrice
0 Pen 20 18.0
1 Notebook 50 45.0
2 Bag 800 720.0
3 Bottle 150 135.0
QUES4- . A store keeps stock record. Create a DataFrame and
show only those items whose quantity is less than 20.
CODE
data = { 'Item': ['Soap', 'Shampoo', 'Oil', 'Detergent'],
'Quantity': [12, 35, 18, 8]}
df = [Link](data)
print(df[df['Quantity'] < 20])
OUTPUT
Item Quantity
0 Soap 12
2 Oil 18
3 Detergent 8
QUES5- A school stores marks of 5 students. Display the
marks of the student named Divyam.
CODE
import pandas as pd
df = [Link]({ 'Name': ['Arnav', 'Kritika', 'Divyam',
'Vivaan', 'Aarush'],
'Eco': [18, 23, 51, 40, 18],
'Maths': [57, 45, 37, 60, 27]})
print([Link][2])
OUTPUT
Name Divyam
Eco 51
Maths 37
Name: 2, dtype: object
5.1- Display the first 3 rows of the DataFrame of students.
CODE
print([Link][0:3])
OUTPUT
Name Eco Maths
0 Arnav 18 57
1 Kritika 23 45
2 Divyam 51 37
5.2- Show students from row index 1 to 3.
CODE
print(df[1:4])
OUTPUT
Name Eco Maths
1 Kritika 23 45
2 Divyam 51 37
3 Vivaan 40 60
5.3- Show only those students whose Economics marks are
more than 40.
CODE
print(df[df['Eco'] > 40])
OUTPUT
Name Eco Maths
2 Divyam 51 37
QUES6- A company stores employee records. Add a new
column AnnualSalary = Salary × 12.
CODE
import pandas as pd
df = [Link]({
'EmpID': [101, 102, 103, 104],
'Name': ['Amit', 'Ritu', 'Karan', 'Megha'],
'Salary': [35000, 42000, 50000, 28000]})
df['AnnualSalary'] = df['Salary'] * 12
print(df)
OUTPUT
EmpID Name Salary AnnualSalary
0 101 Amit 35000 420000
1 102 Ritu 42000 504000
2 103 Karan 50000 600000
3 104 Megha 28000 336000
QUES7-Increase the price of all grocery items by 5%.
CODE
df = [Link]({ 'Item': ['Rice', 'Sugar', 'Oil', 'Flour'],
'Price': [60, 45, 120, 40]})
df['Price'] = df['Price'] * 1.05
print(df)
OUTPUT
Item Price
0 Rice 63.00
1 Sugar 47.25
2 Oil 126.00
3 Flour 42.00
QUES8- Sort the DataFrame by Price in descending order.
CODE
df = [Link]({ 'Book': ['Gita', 'Ramayana',
'Mahabharata', 'Shiv Puran'],
'Price': [250, 400, 350, 300]})
df = df.sort_values(by='Price', ascending=False)
print(df)
OUTPUT
Book Price
1 Ramayana 400
2 Mahabharata 350
3 Shiv Puran 300
0 Gita 250

DATA VISUALISATION
1.1 Line chart (Single)
CODE
import [Link] as plt
INFO=['GOLD','SILVER','BRONZE']
INDIA=[26,20,20]
[Link](INFO,INDIA,color='red',linestyle=':')
[Link]('MEDAL TYPE')
[Link]('MEDAL COUNT')
[Link]("INDIA'S MEDAL TALLY")
[Link]()
[Link]('[Link]')
OUTPUT
QUES- Create a line chart to show the monthly sales of a shop for 6
months:
Jan–50, Feb–70, Mar–65, Apr–80, May–75, Jun–90.
CODE
import [Link] as plt
months = ['Jan','Feb','Mar','Apr','May','Jun']
sales = [50,70,65,80,75,90]
[Link](months, sales)
[Link]("Months")
[Link]("Sales")
[Link]("Monthly Sales Line Chart")
[Link]()
[Link](‘[Link]’)
OUTPUT

1.2 Line Chart (Multiple)


QUES- Plot a multiple line chart showing sales of Store A and
Store B for 6 months.
MONTH A B
JAN 50 40
FEB 60 55
MAR 65 60
APR 70 68
MAY 75 72
JUN 80 78
CODE
import [Link] as plt
months = ['Jan','Feb','Mar','Apr','May','Jun']
storeA = [50,60,65,70,75,80]
storeB = [40,55,60,68,72,78]
[Link](months, storeA, label="Store A")
[Link](months, storeB, label="Store B")
[Link]("Months")
[Link]("Sales")
[Link]("Sales Comparison of Store A and Store B")
[Link]()
[Link]()
OUTPUT
QUES- A factory has 3 production units. Plot their output
across 5 days.
DAY UNIT 1 UNIT 2 UNIT 3
1 120 100 140
2 130 110 135
3 125 105 150
4 140 120 155
5 135 118 160

CODE
import [Link] as plt
days = ['1','2','3','4','5']
u1 = [120,130,125,140,135]
u2 = [100,110,105,120,118]
u3 = [140,135,150,155,160]
[Link](days, u1, label="Unit 1")
[Link](days, u2, label="Unit 2")
[Link](days, u3, label="Unit 3")
[Link]("Days")
[Link]("Units Produced")
[Link]("Production of 3 Units")
[Link]()
[Link]()
OUTPUT

1.3 Bar Chart (Single)


QUES- Create a single bar chart showing sales of five
products:
A–40, B–55, C–30, D–70, E–60.
CODE
import [Link] as plt
products = ['A','B','C','D','E']
sales = [40,55,30,70,60]
[Link](products, sales)
[Link]("Products")
[Link]("Sales")
[Link]("Sales of Products")
[Link]()
OUTPUT

QUES- Production Units per Day


Production over 5 days: 100, 140, 135, 150, 145.
CODE
import [Link] as plt
days = ['Day1','Day2','Day3','Day4','Day5']
production = [100,140,135,150,145]
[Link](days, production)
[Link]("Days")
[Link]("Units Produced")
[Link]("Daily Production")
[Link]()
OUTPUT

1.4 Bar Chart (Multiple)


QUES- Marks of 2 students in 5 subjects.
SUBJECT STUDENT A STUDENT B
MATHS 88 76
ENGLISH 82 70
SCIENCE 90 85
ECONOMICS 75 80
IP 95 92
CODE
import [Link] as plt
import numpy as np
subjects = ['Maths','English','Science','Economics','IP']
A = [88,82,90,75,95]
B = [76,70,85,80,92]
x = [Link](len(subjects))
width = 0.35
[Link](x, A, width, label='Student A')
[Link](x + width, B, width, label='Student B')
[Link](x + width/2, subjects)
[Link]("Subjects")
[Link]("Marks")
[Link]("Marks Comparison")
[Link]()
[Link]()
OUTPUT

QUES- Revenue of Two Years.


QUARTER 2023 2024
Q1 40 55
Q2 50 60
Q3 65 70
Q4 75 80
CODE
import [Link] as plt
import numpy as np
quarters = ['Q1','Q2','Q3','Q4']
year23 = [40,50,65,75]
year24 = [55,60,70,80]
x = [Link](len(quarters))
width = 0.35
[Link](x, year23, width, label='2023')
[Link](x + width, year24, width, label='2024')
[Link](x + width/2, quarters)
[Link]("Quarters")
[Link]("Revenue")
[Link]("Revenue Comparison of Two Years")
[Link]()
[Link]()
OUTPUT

1.5 HISTOGRAM
QUES- Plot a histogram of marks of 20 students:
[45, 56, 78, 89, 67, 45, 56, 88, 90, 72, 61, 59, 48, 55, 62, 73,
80, 91, 53, 47]
CODE
import [Link] as plt
marks=[45,56,78,89,67,45,56,88,90,72,61,59,48,55,62,73,80,
91,53,47]
[Link](marks, bins=5)
[Link]("Marks Range")
[Link]("Number of Students")
[Link]("Marks Distribution")
[Link]()
OUPUT
QUES- Plot a histogram showing monthly income of 12
people:[20000, 25000, 27000, 30000, 32000, 28000, 26000,
29000, 31000, 33000, 35000, 36000]
CODE
import [Link] as plt
income=[20000,25000,27000,30000,32000,28000,26000,290
00,31000,33000,35000,36000]
[Link](income, bins=4)
[Link]("Income Range")
[Link]("Number of People")
[Link]("Income Distribution")
[Link]()
OUTPUT
SQL
create database customer;

Query OK, 1 row affected (0.02 sec)

mysql> use customer;

Database changed

mysql> CREATE TABLE CUSTOMER ( CustID VARCHAR(10) PRIMARY KEY,CustName


VARCHAR(30),CustAdd VARCHAR(50),Phone BIGINT,Email VARCHAR(50));

Query OK, 0 rows affected (0.18 sec)

mysql> desc table customer;

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

| id | select_type | table | partitions | type | possible_keys | key |


key_len | ref | rows | filtered | Extra |

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

| 1 | SIMPLE | customer | NULL | ALL | NULL | NULL |


NULL | NULL | 1 | 100.00 | NULL |

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

1 row in set, 1 warning (0.05 sec)

mysql> ALTER TABLE Customer ADD City VARCHAR(20);

Query OK, 0 rows affected (0.16 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE Customer RENAME TO Client;

Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO CUSTOMER (CustID, CustName, CustAdd, Phone, Email)

-> VALUES ('C001', 'Aarav Singh', 'Delhi', 9876543210,


'aarav@[Link]');

Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO CUSTOMER (CustID, CustName, CustAdd, Phone, Email)VALUES


('C002', 'Simran Kaur', 'Mumbai', 9898989898, 'simran@[Link]');

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO CUSTOMER (CustID, CustName, CustAdd, Phone, Email)VALUES


('C003', 'Rahul Mehta', 'Chandigarh', 9123456789, 'rahul@[Link]');

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO CUSTOMER (CustID, CustName, CustAdd, Phone, Email)VALUES


('C004', 'Priya Sharma', 'Kolkata', 9001122334, 'priya@[Link]');

Query OK, 1 row affected (0.01 sec)


mysql> INSERT INTO CUSTOMER (CustID, CustName, CustAdd, Phone, Email)VALUES
('C005', 'Karan Patel', 'Ahmedabad', 9090909090, 'karan@[Link]');

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO CUSTOMER (CustID, CustName, CustAdd, Phone, Email)VALUES


('C006', 'Neha Verma', 'Jaipur', 9988776655, 'neha@[Link]');

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO CUSTOMER (CustID, CustName, CustAdd, Phone, Email)VALUES


('C007', 'Rohan Gupta', 'Bengaluru', 9345127890, 'rohan@[Link]');

Query OK, 1 row affected (0.01 sec)

mysql> ALTER TABLE CUSTOMER DROP EMAIL;

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql> ALTER TABLE CUSTOMER MODIFY CustID CHAR(5);

Query OK, 7 rows affected (0.09 sec)

Records: 7 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE CUSTOMER MODIFY CustName VARCHAR(30) NOT NULL;

Query OK, 0 rows affected (0.14 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> UPDATE CUSTOMER SET Phone = 9876501234 WHERE CustID = 'C001';

Query OK, 1 row affected (0.03 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> UPDATE CUSTOMER SET CustName = 'Aman Singh'WHERE CustID = 'C002';

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> DELETE FROM CUSTOMER WHERE CustID = 'C003';

Query OK, 1 row affected (0.01 sec)

mysql> DELETE FROM CUSTOMER WHERE Phone = 9876543210;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM CUSTOMER;

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

| CustID | CustName | CustAdd | Phone |

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

| C001 | Aarav Singh | Delhi | 9876501234 |

| C002 | Aman Singh | Mumbai | 9898989898 |

| C004 | Priya Sharma | Kolkata | 9001122334 |

| C005 | Karan Patel | Ahmedabad | 9090909090 |


| C006 | Neha Verma | Jaipur | 9988776655 |

| C007 | Rohan Gupta | Bengaluru | 9345127890 |

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

6 rows in set (0.01 sec)

mysql> SELECT CustName, CustAdd FROM CUSTOMER WHERE CustAdd='Delhi';

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

| CustName | CustAdd |

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

| Aarav Singh | Delhi |

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

1 row in set (0.01 sec)

mysql> SELECT CustName, CustAdd FROM CUSTOMER ORDER BY CustName;

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

| CustName | CustAdd |

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

| Aarav Singh | Delhi |

| Aman Singh | Mumbai |

| Karan Patel | Ahmedabad |

| Neha Verma | Jaipur |

| Priya Sharma | Kolkata |

| Rohan Gupta | Bengaluru |

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

6 rows in set (0.01 sec)

mysql> SELECT CustAdd, COUNT(*) FROM CUSTOMER GROUP BY CustAdd;

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

| CustAdd | COUNT(*) |

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

| Delhi | 1 |

| Mumbai | 1 |

| Kolkata | 1 |

| Ahmedabad | 1 |

| Jaipur | 1 |

| Bengaluru | 1 |

+-----------+----------+
6 rows in set (0.02 sec)

mysql> SELECT CustName, Phone FROM CUSTOMER WHERE Phone BETWEEN 9000000000
AND 9500000000;

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

| CustName | Phone |

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

| Priya Sharma | 9001122334 |

| Karan Patel | 9090909090 |

| Rohan Gupta | 9345127890 |

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

3 rows in set (0.01 sec)

mysql> SELECT CustName FROM CUSTOMER WHERE CustName LIKE 'S%';

Empty set (0.01 sec)

mysql> SELECT CustName FROM CUSTOMER WHERE CustName LIKE '%Singh';

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

| CustName |

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

| Aarav Singh |

| Aman Singh |

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

2 rows in set (0.00 sec)

mysql> SELECT CustID, POWER(LENGTH(CustName), 2) AS Result FROM CUSTOMER;

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

| CustID | Result |

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

| C001 | 121 |

| C002 | 100 |

| C004 | 144 |

| C005 | 121 |

| C006 | 100 |

| C007 | 121 |

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

6 rows in set (0.01 sec)

mysql> SELECT CustName, ROUND(Phone / 10000000) AS Rounded FROM CUSTOMER;

+--------------+---------+
| CustName | Rounded |

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

| Aarav Singh | 988 |

| Aman Singh | 990 |

| Priya Sharma | 900 |

| Karan Patel | 909 |

| Neha Verma | 999 |

| Rohan Gupta | 935 |

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

6 rows in set (0.01 sec)

mysql> SELECT CustID, MOD(SUBSTR(CustID,2), 3) AS ModValue FROM CUSTOMER;

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

| CustID | ModValue |

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

| C001 | 1 |

| C002 | 2 |

| C004 | 1 |

| C005 | 2 |

| C006 | 0 |

| C007 | 1 |

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

6 rows in set (0.01 sec)

mysql> SELECT UPPER(CustName) FROM CUSTOMER;

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

| UPPER(CustName) |

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

| AARAV SINGH |

| AMAN SINGH |

| PRIYA SHARMA |

| KARAN PATEL |

| NEHA VERMA |

| ROHAN GUPTA |

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

6 rows in set (0.01 sec)


mysql> SELECT LOWER(CustName) FROM CUSTOMER;

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

| LOWER(CustName) |

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

| aarav singh |

| aman singh |

| priya sharma |

| karan patel |

| neha verma |

| rohan gupta |

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

6 rows in set (0.01 sec)

mysql> SELECT CustName, LENGTH(CustName) AS Len FROM CUSTOMER;

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

| CustName | Len |

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

| Aarav Singh | 11 |

| Aman Singh | 10 |

| Priya Sharma | 12 |

| Karan Patel | 11 |

| Neha Verma | 10 |

| Rohan Gupta | 11 |

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

6 rows in set (0.00 sec)

mysql> SELECT CustName, SUBSTR(CustName, 1, 3) AS First3 FROM CUSTOMER;

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

| CustName | First3 |

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

| Aarav Singh | Aar |

| Aman Singh | Ama |

| Priya Sharma | Pri |

| Karan Patel | Kar |

| Neha Verma | Neh |

| Rohan Gupta | Roh |


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

6 rows in set (0.00 sec)

mysql> SELECT CustName, LEFT(CustName, 4) FROM CUSTOMER;

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

| CustName | LEFT(CustName, 4) |

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

| Aarav Singh | Aara |

| Aman Singh | Aman |

| Priya Sharma | Priy |

| Karan Patel | Kara |

| Neha Verma | Neha |

| Rohan Gupta | Roha |

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

6 rows in set (0.01 sec)

mysql> SELECT CustName, RIGHT(CustName, 4) FROM CUSTOMER;

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

| CustName | RIGHT(CustName, 4) |

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

| Aarav Singh | ingh |

| Aman Singh | ingh |

| Priya Sharma | arma |

| Karan Patel | atel |

| Neha Verma | erma |

| Rohan Gupta | upta |

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

6 rows in set (0.00 sec)

mysql> SELECT CustName, INSTR(CustName, ' ') AS SpacePos FROM CUSTOMER;

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

| CustName | SpacePos |

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

| Aarav Singh | 6 |

| Aman Singh | 5 |

| Priya Sharma | 6 |

| Karan Patel | 6 |
| Neha Verma | 5 |

| Rohan Gupta | 6 |

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

6 rows in set (0.00 sec)

mysql> SELECT TRIM(' ' || CustName || ' ') AS Trimmed FROM CUSTOMER;

+---------+

| Trimmed |

+---------+

| 0 |

| 0 |

| 0 |

| 0 |

| 0 |

| 0 |

+---------+

6 rows in set, 8 warnings (0.00 sec)

mysql> SELECT LTRIM(' ' || CustName) FROM CUSTOMER;

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

| LTRIM(' ' || CustName) |

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

| 0 |

| 0 |

| 0 |

| 0 |

| 0 |

| 0 |

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

6 rows in set, 7 warnings (0.01 sec)

mysql> SELECT RTRIM(CustName || ' ') FROM CUSTOMER;

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

| RTRIM(CustName || ' ') |

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

| 0 |

| 0 |
| 0 |

| 0 |

| 0 |

| 0 |

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

6 rows in set, 7 warnings (0.00 sec)

mysql> ALTER TABLE CUSTOMER ADD JoinDate DATE;

Query OK, 0 rows affected (0.07 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> UPDATE CUSTOMER SET JoinDate = '2023-01-15' WHERE CustID = 'C001';

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> UPDATE CUSTOMER SET JoinDate = '2023-02-10' WHERE CustID = 'C002';

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> UPDATE CUSTOMER SET JoinDate = '2023-02-28' WHERE CustID = 'C004';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> UPDATE CUSTOMER SET JoinDate = '2023-03-05' WHERE CustID = 'C005';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> UPDATE CUSTOMER SET JoinDate = '2023-04-12' WHERE CustID = 'C006';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> UPDATE CUSTOMER SET JoinDate = '2023-05-20' WHERE CustID = 'C007';

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT CustName, YEAR(JoinDate) AS JoinYear FROM CUSTOMER;

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

| CustName | JoinYear |

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

| Aarav Singh | 2023 |

| Aman Singh | 2023 |

| Priya Sharma | 2023 |


| Karan Patel | 2023 |

| Neha Verma | 2023 |

| Rohan Gupta | 2023 |

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

6 rows in set (0.00 sec)

mysql> SELECT CustName, MONTH(JoinDate) AS JoinMonth FROM CUSTOMER;

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

| CustName | JoinMonth |

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

| Aarav Singh | 1 |

| Aman Singh | 2 |

| Priya Sharma | 2 |

| Karan Patel | 3 |

| Neha Verma | 4 |

| Rohan Gupta | 5 |

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

6 rows in set (0.01 sec)

mysql> SELECT CustName, DAY(JoinDate) AS JoinDay FROM CUSTOMER;

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

| CustName | JoinDay |

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

| Aarav Singh | 15 |

| Aman Singh | 10 |

| Priya Sharma | 28 |

| Karan Patel | 5 |

| Neha Verma | 12 |

| Rohan Gupta | 20 |

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

6 rows in set (0.00 sec)

mysql> SELECT NOW() AS TodayDate;

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

| TodayDate |

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

| 2025-11-25 16:49:14 |
+---------------------+

1 row in set (0.00 sec)

mysql> SELECT DATE(NOW()) AS OnlyDate;

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

| OnlyDate |

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

| 2025-11-25 |

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

1 row in set (0.00 sec)

mysql> SELECT CustName, MONTHNAME(JoinDate) AS JoinMonthName FROM CUSTOMER;

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

| CustName | JoinMonthName |

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

| Aarav Singh | January |

| Aman Singh | February |

| Priya Sharma | February |

| Karan Patel | March |

| Neha Verma | April |

| Rohan Gupta | May |

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

6 rows in set (0.00 sec)

mysql> SELECT CustName, DAYNAME(JoinDate) AS WeekDay FROM CUSTOMER;

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

| CustName | WeekDay |

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

| Aarav Singh | Sunday |

| Aman Singh | Friday |

| Priya Sharma | Tuesday |

| Karan Patel | Sunday |

| Neha Verma | Wednesday |

| Rohan Gupta | Saturday |

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

6 rows in set (0.01 sec)

mysql> SELECT CustName, JoinDate FROM CUSTOMER WHERE JoinDate BETWEEN


'2023-02-01' AND '2023-04-30';
+--------------+------------+

| CustName | JoinDate |

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

| Aman Singh | 2023-02-10 |

| Priya Sharma | 2023-02-28 |

| Karan Patel | 2023-03-05 |

| Neha Verma | 2023-04-12 |

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

4 rows in set (0.00 sec)

mysql> SELECT COUNT(*) AS FebCustomers FROM CUSTOMER WHERE MONTH(JoinDate)


= 2;

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

| FebCustomers |

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

| 2 |

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

1 row in set (0.01 sec)

mysql> SELECT COUNT(*) AS TotalCustomers FROM CUSTOMER;

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

| TotalCustomers |

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

| 6 |

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

1 row in set (0.01 sec)

mysql> SELECT COUNT(Phone) AS CountPhones FROM CUSTOMER;

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

| CountPhones |

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

| 6 |

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

1 row in set (0.00 sec)

mysql> SELECT MAX(Phone) AS MaxPhone FROM CUSTOMER;

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

| MaxPhone |

+------------+
| 9988776655 |

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

1 row in set (0.00 sec)

mysql> SELECT MIN(Phone) AS MinPhone FROM CUSTOMER;

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

| MinPhone |

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

| 9001122334 |

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

1 row in set (0.01 sec)

mysql> SELECT SUM(Phone) AS TotalPhoneSum FROM CUSTOMER;

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

| TotalPhoneSum |

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

| 57201427101 |

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

1 row in set (0.00 sec)

mysql> SELECT AVG(Phone) AS AvgPhone FROM CUSTOMER;

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

| AvgPhone |

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

| 9533571183.5000 |

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

1 row in set (0.00 sec)

mysql> SELECT COUNT(*) AS FebJoined FROM CUSTOMER WHERE MONTH(JoinDate) =


2;

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

| FebJoined |

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

| 2 |

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

1 row in set (0.00 sec)

mysql> SELECT MAX(JoinDate) AS LatestJoin FROM CUSTOMER;

+------------+
| LatestJoin |

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

| 2023-05-20 |

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

1 row in set (0.01 sec)

mysql> SELECT MIN(JoinDate) AS FirstJoin FROM CUSTOMER;

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

| FirstJoin |

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

| 2023-01-15 |

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

1 row in set (0.00 sec)

mysql> SELECT CustAdd AS City, COUNT(*) AS Total FROM CUSTOMER GROUP BY


CustAdd;

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

| City | Total |

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

| Delhi | 1 |

| Mumbai | 1 |

| Kolkata | 1 |

| Ahmedabad | 1 |

| Jaipur | 1 |

| Bengaluru | 1 |

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

6 rows in set (0.00 sec)

mysql> SELECT CustAdd, COUNT(*) AS Total FROM CUSTOMER GROUP BY CustAdd


HAVING COUNT(*) > 1;

Empty set (0.01 sec)

mysql> SELECT CustID, CustName,POWER(SUBSTR(CustID,2),2) AS SquareValue


FROM Customer;

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

| CustID | CustName | SquareValue |

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

| C001 | Aarav Singh | 1 |

| C002 | Aman Singh | 4 |

| C004 | Priya Sharma | 16 |


| C005 | Karan Patel | 25 |

| C006 | Neha Verma | 36 |

| C007 | Rohan Gupta | 49 |

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

6 rows in set (0.00 sec)

mysql> SELECT CustID, CustName,MOD(SUBSTR(CustID,2), 3) AS Remainder FROM


Customer;

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

| CustID | CustName | Remainder |

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

| C001 | Aarav Singh | 1 |

| C002 | Aman Singh | 2 |

| C004 | Priya Sharma | 1 |

| C005 | Karan Patel | 2 |

| C006 | Neha Verma | 0 |

| C007 | Rohan Gupta | 1 |

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

6 rows in set (0.00 sec)

mysql> SELECT CustName, Phone,ROUND(Phone/10000000, 2) AS RoundedValue FROM


Customer;

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

| CustName | Phone | RoundedValue |

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

| Aarav Singh | 9876501234 | 987.65 |

| Aman Singh | 9898989898 | 989.90 |

| Priya Sharma | 9001122334 | 900.11 |

| Karan Patel | 9090909090 | 909.09 |

| Neha Verma | 9988776655 | 998.88 |

| Rohan Gupta | 9345127890 | 934.51 |

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

6 rows in set (0.00 sec)

You might also like