IDEAL INDIAN SCHOOL-DOHA QATAR
AISSCE PRACTICAL BOARD EXAMINATION JANUARY-2025
INFORMATICS PRACTICES (065) - SET 1
CLASS: XII Time: 3 Hrs.
Max.Marks:30
Practical Marks Distribution
S. No. Unit Name Marks
1 Programs using Pandas and Matplotlib 8
2 SQL Queries 7
3 Practical file 5
4 Project Work 5
5 Viva-Voce 5
TOTAL 30
SECTION – A (MYSQL)
1) Table: Exam
Write MYSQL Queries for the following
i. Create the above table named exam. Write Mysql Query to create the table Exam assuming
suitable data types. Constraint to the table Exam is Primary key for RegNo and NOT NULL
for Name column.
ii. Insert a new record in the table having following values:[6,'Khushi','CS',85]
iii. To change the value “IP” to “Informatics Practices” in subject column.
iv. To remove the records of those students whose marks are less than 30 .
v. To add a new column Grade of suitable datatype.
vi. To display records of “Informatics Practices” subject.
vii. To display students whose marks greater than 90 and less than 100
viii. To delete column Grade
ix. To change the data type of Name to Varchar(30).
x. To drop the table Exam
i. CREATE TABLE EXAM (REGNO INT PRIMARY KEY, NAME VARCHAR(15) not null, SUBJECT
VARCHAR(25), MARKS INT);
ii. INSERT INTO EXAM VALUES(6,'Khushi','CS',85);
iii. UPDATE EXAM SET subject= "Informatics Practices" where subject = "IP";
iv. DELETE FROM EXAM WHERE marks<30;
v. ALTER TABLE EXAM ADD COLUMN grade varchar(2);
vi. Select * from exam where subject="Informatics Practices";
vii. SELECT * FROM EXAM WHERE MARKS >90 AND MARKS<100;
viii. ALTER TABLE EXAM DROP COLUMN GRADE;
ix. ALTER TABLE EXAM MODIFY NAME VARCHAR(30);
x. DROP TABLE EXAM;
2) Write the SQL queries which will perform the following operations :
a) Write a SQL query to calculate the remainder when 15 is divided by 4.
SELECT MOD(15, 4) AS Remainder;
b) Write a SQL query to retrieve the current year.
SELECT YEAR(NOW()) AS CurrentYear;
c) Write a SQL query to extract the first three characters from the string 'Hello, World!'.
SELECT LEFT('Hello, World!', 3) AS ExtractedString;
d) To calculate the exponent for 3 raised to the power of 4.
SELECT POWER(3,4);
e) To display current date and time.
SELECT NOW();
f) To round off the value -34.4567 to 2 decimal place.
SELECT ROUND(-34.4567,2);
g) To display the length of the string ‘FIFA World Cup’.
SELECT LENGTH("FIFA World Cup");
h) To convert your father’s email id ‘xyz@abc.com’ to uppercase.
SELECT UCASE('xyz@abc.com');
i) To display today’s day name i.e., Sunday or Monday ...
SELECT DAYNAME(NOW());
j) To display the day part from your date of admission, which is ‘2023-12-13’
SELECT DAY('2023-12-13');
k) To convert email id “ ANIKA@GMAIL.com ” to lowercase after removing leading and
trailing blank spaces.
SELECT LCASE(TRIM( “ ANIKA@GMAIL.com ”))
l) To get the position of the first occurrence of sub-string “zia” in another string “Fouzia
Ahmed”.
SELECT INSTR(“Fouzia Ahmed”, “zia”);
m) To extract substring of 3 characters starting at 3rd position from a given string “Maria John”.
SELECT MID(“Maria John”, 3,3)
n) To display the current year i.e 2023
SELECT YEAR(NOW())
3) Create a table Student that has the following Structure
FIELDNAME DATATYPE FIELDWIDTH CONSTRAINTS
Studentid Varchar 15 Primary key
Name Varchar 15
Class Char 5
Marks Integer 5
a) Write a SQL query to order the student table in descending order of marks
SELECT * FROM STUDENT ORDER BY MARKS DESC;
b) Display the details of Students whose name starts with the letter ‘A’ and class is X
SELECT * FROM STUDENT WHERE NAME LIKE ‘A%’ AND CLASS=’X’;
c) Find the no. of students of class XII
SELECT COUNT(*) FROM STUDENT WHERE CLASS = ‘XII’;
d) Find the max, min, sum and average marks of students in student table.
SELECT MAX(MARKS), MIN(MARKS), SUM(MARKS), AVG(MARKS) FROM STUDENT;
e) Find the Total, average, min, maximum of marks for each class.
SELECT CLASS, MAX(MARKS), MIN(MARKS), SUM(MARKS), AVG(MARKS)
FROM STUDENT GROUP BY CLASS;
f) Find the total number of students from each class that has minimum 2 students
SELECT CLASS, COUNT(*) FROM STUDENT GROUP BY CLASS having count(*) >=2;
4) Table: BANK
Table: CUSTOMER
a) Write a SQL query to list names of all customers with their Amount in ascending order:
SELECT C.CUSTOMER_NAME, B.AMOUNT FROM CUSTOMER C JOIN BANK B ON C.ACC_NO =
B.ACC_NO ORDER BY B.AMOUNT ASC;
b) Write a SQL query to list names of all customers with Account number along Branch Name
SELECT CUSTOMER_NAME, CUSTOMER.ACC_NO, BRANCH_NAME FROM CUSTOMER , BANK
WHERE CUSTOMER.ACC_NO=BANK.ACC_NO
c) Write a SQL query to list names of all customers with their Amount.
SELECT CUSTOMER_NAME, AMOUNT FROM CUSTOMER , BANK WHERE
CUSTOMER.ACC_NO=BANK.ACC_NO
d) Write a SQL query to find the total amount of money across all branches.
SELECT SUM(AMOUNT) AS TOTAL_AMOUNT FROM BANK;
e) Write a SQL query to count the total records in CUSTOMER table.
SELECT COUNT(*) from CUSTOMER;
f) Write a SQL query to find the minimum amount in a bank.
SELECT MIN(AMOUNT) from BANK;
SECTION – B (Data Handling - Python)
5) Series: C1
INDEX India NewDelhi
UK London
Japan Tokyo
Qatar Doha
Consider the above Series C1, write Python code for the following
a) To create the above Series C1 from Dictionary with series name and index name as shown below.
Display the series
Countries
India NewDelhi
UK London
Japan Tokyo
Qatar Doha
Name: Capitals, dtype: object
b) Display only indexes of the above series
c) Display only values of the above series
d) To display Series C1 in reverse order.
e) To display two items of labelled indexes UK and Qatar
f) To display the items from index UK to Qatar
g) To display item labelled Japan
h) Display first 2 and last 2 elements of the series.
i) Display the size of the series
j) Check the series is empty or not
k) Change the index of the above series to 1,2,3,4 and display series C1
l) Change the values of the series to a,b,c,d
import pandas as pd
dict1 = {'India': 'NewDelhi', 'UK':'London', 'Japan': 'Tokyo','Qatar':'Doha'}
C1 = pd.Series(dict1)
C1.name = 'Capitals'
C1.index.name = 'Countries'
print(C1)
print(C1.index)
print(C1.values)
print(C1[ : :-1])
print(C1[['UK','Qatar']])
print(C1['UK':'Qatar'])
print(C1['Japan'])
print(C1.head(2))
print(C1.head(2))
print(C1.size)
print(C1.empty)
C1.index=[1,2,3,4]
C1['India']='a'
C1['UK']='b'
C1['Japan']='c'
C1['Qatar']='d'
print(C1)
6) Consider the DataFrame: emp
Name Department Salary
1 Divya HR 95000
2 Mamta Marketing 97000
3 Payal IT 980000
4 Deepak Sales 79000
Write a Python Program
a) To create DataFrame emp with index and column headings using List of List
import pandas as pd #Statement 1
Data=[ ["Divya","HR",95000],
["Mamta","Marketing",97000],
["Payal","IT",980000],
["Deepak","Sales",79000]
]
emp=pd.DataFrame(Data,columns=["Name","Department","Salary"],index=[1,2,3,4])
print(emp)
b) Display Name and Salary for index 3 and 4. Display rows from index 1 to 3 for Name and
Department.
print(emp.loc[[3,4],['Name','Salary']])
print(emp.loc[1:3,['Name','Department']])
c) To add a row Sheetal, IT, 45000 for Name , Department and Salary columns and display
dataframe emp from 2nd row to last row by using slicing
emp.loc[5]=['Sheetal', 'IT', 45000 ]
print(emp.loc[1:])
g) To delete Row having index 3 and display the details of the emp in the reverse order of their
indexes.
emp.drop([3],axis=0,inplace=True)
print(emp.loc[::-1])
h) To change indexes 1 and 2 to a and b and display dataframe with index a and b
emp.rename({1:'a',2:'b'},axis='rows', inplace=True)
print(emp.loc[['a','b']])
7) Consider the given DataFrame 'result’:
Name Percentile
a Rohit 95
b Mohit 76
c Raman 98
d Aditya 47
Write a Python Program
a) To create DataFrame emp with index and column headings using Dictionary of Series
import pandas as pd
Data={
'Name':pd.Series(['Rohit','Mohit','Raman','Aditya'],index=['a','b','c','d']),
'Percentile':pd.Series([95,76,98,47],index=['a','b','c','d']),
}
result=pd.DataFrame(Data)
print(result)
b) To Add a new column ‘Grade’ to the dataframe having values A,B,A,C and display the top 2 rows
result['Grade']=['A','B','A','C']
print(result.head(2))
c) To rename the column ‘Percentile’ to ‘Percent’. and display the last 2 rows
result.rename({'Percentile':'Percent'},axis='columns', inplace=True)
print(result.tail(2))
d) To Change the Name of index b to Kumar and display only Name column
result.loc['b','Name']='Kumar'
print(result[['Name']])
e) To remove the column Grade and display only Percent column decreased by 10.
del result ['Grade']
print(result['Percent']-10)
8) Wizbiz Corporation is recording the quarterly sales data of its three products through different
departments. The data is as follows:
Qtr1 Qtr2 Qtr3 Qtr4
Product1 3500 4200 4800 5100
Product2 2800 3100 3600 3900
Product3 1500 1800 2100 2400
The company stores this information in a CSV file named " Sales.csv."
Write suitable python statement
a) Create a CSV file name " Sales.csv”
Qtr1,Qtr2,Qtr3,Qtr4
3500,4200,4800,5100
2800,3100,3600,3900
1500,1800,2100,2400
b) Assign 'Product1','Product2','Product3' indexes to rows.
c) To fetch the data from 'Sales.csv' into the datafarme df. Print the dataframe
d) To Plot the bar graph from the above dataframe df as shown below
e) To Save this chart as pdf.
f) Write Python statement to display total sales done in 'Qtr1' and 'Qtr2' for each product.
import pandas as pd
import matplotlib.pyplot as plt
df =pd.read_csv('c:\ip\Sales.csv')
df.index=['Product1','Product2','Product3' ]
print(df)
df.plot(kind='bar', color=['purple', 'orange', 'green', 'yellow'])
plt.title ('Quarterly Sales Report')
plt.xlabel('Product')
plt.ylabel('Sales')
plt.savefig("C:/ip/aa.jpg")
plt.show()
print(df['Qtr1']+df['Qtr2'])
9)
Write a Lab Program to plot multiple lines in one chart for Scoring Rate of 2 teams in 5 overs as
per the following details.
TEAMS / OVERS 16-20 21-25 26-30 31-35 36-40
Sri Lanka 50 48 40 60 65
New Zealand 35 50 55 65 70
import matplotlib.pyplot as plt
over=[’16-20’,’21-25’,’26-30’,’31-35’,’36-40’]
srilanka=[50,48,40,60,65]
Newzland=[35,50,55,65,70]
plt.plot(over,srilanka, color=’r’)
plt.plot(over,Newzland, color=’b’)
plt.title(“Chart for scoring rate Srilanka vs Newzland”)
plt.xlabel(“over”)
plt.ylabel(“scored runs”)
plt.show()
10) Collect the age of each employee in an office and show it in the form of a histogram to know
how many employees are there in the range 0-10 years, 10-20 years and so on. Save this chart.
11 Write a Lab Program to produce the following Bar Graph:
#Bar Graph
import matplotlib.pyplot as pl
Range=['1-10','11-20','21-30','31-40','41-50']
Runs=[65,55,70,50,80]
pl.bar(Range,Runs,width=0.3)
pl.xlabel('Over Interval')
pl.ylabel('Runs Scored')
pl.title('India\'s Scoring')
pl.show()
12
Write a program in python pandas
1) To create the above Dataframe “POP” from a dictionary of list
2) To display the columns country and population
3) To display all the rows where population is more than 40 crores
4) To Display all the countries whose population is more than 50 and percent is less than 85
5) Change the population of France to into 50 crores
6) Change the index of Greece into GRE
7) Remove the field percent
8) Display the population of Portugal and Italy
9) Display the population of all countries in descending order of their population
10) Increase the population by 10 for all countries
11) Change the column name from population to population in crores
1 import pandas as p
Data={ 'country':['ITALY','SPAIN','GREECE','FRANCE','PORTUGAL'],
'population':[61,46 ,11 ,65,10 ],
'percent':[0.83,0.63,0.15,0.88,0.14] }
pop=p.DataFrame(Data,index=['IT','ES','GR', 'FR', 'PO']); print(pop)
2 print(pop[['country','population']]) or print(pop.loc[['country','population']])
3 print(pop[pop['population']>40])
4 print(pop.loc[((pop['population']>50) & (pop['percent']<0.85)),'country'])
5 pop.loc[pop['country']=='FRANCE','population']=50 ; print(pop)
6 pop.rename({'GR':'GRE'},axis='index',inplace=True) or pop=pop.rename(index={'GR':'GRE'})
7 pop.drop('percent',axis=1,inplace=True)
8 print(pop.loc[(pop['country']=='PORTUGAL') |(pop['country']=='ITALY'),'population'])
9 pop=pop.sort_values('population',ascending=True) ; print(pop)
10 pop['population']=pop['population']+10; print(pop)
11 pop.rename({'population':'population in crores'}, axis='columns',inplace=True) ; print(pop) or
pop=pop.rename(columns={'population':'population in crores'}) ; print(pop)
