12 SM Ip

KENDRIYA VIDYALAYA SANGATHAN
REGIONAL OFFICE RAIPUR

STUDY MATERIAL AND SAMPLE PAPER
SESSION 2022-23
For
Class - XII
SUBJECT CO-ORDINATION BY
Mrs. Sandhya Lakra,

Principal, KV No. 4 Korba
CONTENT TEAM
1. Mr. Arvind Bhatpahare, PGT CS, KV No. 2 Raipur

2. Mr. Sumit Kumar Gupta, PGT CS, KV Ambikapur
3. Mrs. Sangeeta Shilu Kujur, PGT CS, KV Janjgir
4. Mr. Ravi Kumar, PGT CS, KV Baikuntpur
5. Mr. Yogesh Gupta, PGT CS, KV BMY Bhilai
6. Ms. Anajali Devi, PGT CS, KV Raigarh
7. Mr. Sunil Kumar Gupta, PGT CS KV Dantewada
COMPILATION, REVIEW & VETTING BY
Mr. Sumit Kumar Choudhary, PGT CS, KV No. 2 Korba NTPC
Data Handling using Pandas

Introduction :
Python Pandas Pandas is a software library written for the Python programming language for data
manipulation and analysis. Pandas is defined as an open-source library that provides high-
performance data manipulation in Python. The name of Pandas is derived from the word Panel Data,
which means an Econometrics from Multidimensional data. It is used for data analysis in Python and
developed by Wes McKinney in 2008.
There are 3 well-established python libraries namely NumPy, Pandas and Matplotlib specially for
scientific and analytical use. These libraries allow us to manipulate, transform and visualise data
easily and efficiently.
Using the Pandas, we can accomplish five typical steps in the processing and analysis of data,
regardless of the origin of data. These steps are- load, prepare, manipulate, model and analyse
Benefits of Pandas :
The benefits of pandas over using the languages are
 Data representation: It can easily represent data in a form naturally suited for data analysis
through its DataFrame and Series data structures in a concise manner. Doing the equivalent in
C/C++ or Java would require many lines of custom code, as these languages were not built
for data analysis but rather networking and kernel development.
 Clear code: The clear API of the Pandas allows you to focus on the core part of the code. So,
it provides clear code.
Matplotlib :
It is an amazing visualization library in Python that used for 2D plots of arrays. It is a multi-platform
data visualization library which build NumPy arrays. Matplotlib produces publication-quality figures
in a variety of hardcopy formats and interactive environments across platforms. Matplotlib can be
used in Python scripts, the Python and IPython shell, web application servers and various graphical
user interface toolkits. To get matplotlib up and running in our environment, we need to import it.
import matplotlib.pyplot as plt.
Data structures in Pandas:
Data structure is defined as the storage and management of the data for its efficient and easy access
in the future where the data is collected, modified and the various types of operations are performed
on the data respectively. Pandas provides two data structures for processing the data, which are
explained below:
(1) Series: It is one dimensional object similar to an array, list or column in a table. It will assign a
labelled index to each item in the series. By default, each item will receive an index label from 0 to
N, where N is the length of the series minus one.
(2) DataFrame: It is a tabular data structure comprised of rows and columns. DataFrame is
defined as a standard way to store data which has two different indexes i.e., row index and column
index.
Know the Terms:

Pandas: The Pandas is a high-performance open source library for data analysis in Python.
Matplotlib: It is a visualization library in Python that used for 2D plots of arrays.
Series: It is a one-dimensional array containing a sequence of values. Each value has a data label
associated with it also called its index. DATA HANDLING USING PANDAS-I 7
Selection: This function returns data corresponding to axis labels matching criteria.
Indexing: This function is used to get or set the index labels of the given series object.
Slicing: Slicing is a powerful approach to retrieve subsets of data from a Pandas object.
Series is a one-dimensional array like structure with homogeneous data. For example, the following
series is a collection of integers 10, 23, 56, … Pandas Series is a one-dimensional labelled array
capable of holding any data type (integer values, string values, double value and more). A Series
represents a single column in memory. Series can only contain single list with index, whereas
DataFrames can be made of more than one series or we can say that a DataFrames is a collection of
series.
Key Points of Series
● Homogeneous data
● Size Immutable
● Values of Data Mutable
A pandas Series can be created using the following constructor − pandas. Series
(data, index, dtype, copy)
CREATION OF SERIES
A series can be created using various inputs like:
● List
● Tuple
● Dictionary
● Array
● Scalar value or constant
Objective Type Questions
Q. Given a Pandas series called Sequences, the command which will display the
first 4 rows is __________________.
a. print(Sequences.head(4))
b. print(Sequences.Head(4))
c. print(Sequences.heads(4)
d. print(Sequences.Heads(4))
Ans: a. print(Sequences.head(4))
Q. By default df.head( ) will display………………….. rows from the top.
Ans: 5
Q. Write a suitable Python code to create an empty DataFrame.
Ans: import pandas as pd
df=pd.DataFrame( )
Q. In a DataFrame, Axis= 1 represents the_____________ elements.
Ans: column
Q. In Pandas the function used to check for null values in a DataFrame is_______
Ans: empty ()
Q. Given the following Series S1 and S2:
S1 S2
A 10 A 80
B 40 B 20
C 34 C 74
D 60 D 90
Write the command to find the sum of series S1 and S2
Ans: print(S1+S2) or S1.sum(s2)

CASE STUDY BASED QUESTIONS
Q.1. Mr. Ankit is working in an organisation as data analyst. He uses Python Pandas and
Matplotlib for the same. He got a dataset of the passengers for the year 2010 to 2012 for January,
March and December. His manager wants certain information from him, but he is facing some
problems. Help him by answering few questions given below:
Code to create the above data frame:
import pandas as ____________ #Statement 1

data={"Year":[2010,2010,2012,2010,2012],"Month":
["Jan","Mar","Jan","Dec","Dec"],"Passengers":[25,50,35,55,65]}
df=pd.____________________(data) #Statement 2 print(df)
i. Choose the right code from the following for statement 1.
i. pd ii.df iii.data iv.p
Answer: (i) pd
ii. Choose the right code from the following for the statement 2.
i. Dataframe ii.DataFrame iii.Series iv. Dictionary
Answer: (ii) DataFrame
iii. Choose the correct statement/ method for the required output:
i. df.index ii.df.shape() iii.df.shape iv. df.size
Answer: (iii) df.shape

iv. He wants to print the details of "January" month along with the number of passengers,
Identify the correct statement:
i. df.loc[['Month','Passengers']][df['Month']=='Jan']
ii. df[['Month','Passengers']][df['Month']=='Jan']
iii. df.iloc[['Month','Passengers']][df['Month']=='Jan']
iv. df(['Month','Passengers']][df['Month']=='Jan')
Answer: (ii) df[['Month','Passengers']][df['Month']=='Jan']
v. Mr. Ankit wants to change the index of the Data Frame and the output for the same is
given below. Identify the correct statement to change the index.
i. df.index[]=["Air India","Indigo","Spicejet","Jet","Emirates"]
ii. df.index["Air India","Indigo","Spicejet","Jet","Emirates"]
iii. df.index=["Air India","Indigo","Spicejet","Jet","Emirates"]
iv. df.index()=["Air India","Indigo","Spicejet","Jet","Emirates"]
Answer: (iii) df.index=["Air India","Indigo","Spicejet","Jet","Emirates"]

Q. Sanyukta is the event incharge in a school. One of her students gave her a suggestion to use
Python Pandas and
Matplotlib for analysing and visualising the data, respectively. She has created a Data frame
“SportsDay” to keep track of the number of First, Second and Third prizes won by different
houses in various events.
Write Python commands to do the following:

i. Display the house names where the number of Second Prizes are in the range of 12 to 20.
a. df['Name'][(df['Second']>=12) and (df['Second']<=20)]
b. df[Name][(df['Second']>=12) & (df['Second']<=20)]
c. df['Name'][(df['Second']>=12) & (df['Second']<=20)]
d. df[(df['Second']>=12) & (df['Second']<=20)]
Answer: c. df['Name'][(df['Second']>=12) & (df['Second']<=20)]

ii. Display all the records in the reverse order.
a. print(df[::1])
b. print(df.iloc[::-1])
c. print(df[-1:]+df[:-1])
d. print(df.reverse())
Answer: b. print(df.iloc[::-1])
iii. Display the bottom 3 records.
a. df.last(3)
b. df.bottom(3)
c. df.next(3)
d. df.tail(3)
Answer: d. df.tail(3)
iv. Choose the correct output for the given statements:
x=df.columns[:1]
print(x)
a. 0
b. Name
c. First
d. Error
Answer: b. Name
v. Which command will give the output 24:
a. print(df.size)
b. print(df.shape)
c. print(df.index)
d. print(df.axes)
Answer: a. df.size
Short Answer Type Questions
Q. Consider the following Series object, S_amt
Table 350
Chair 200
Sofa 800
Stool 150
i. Write the command which will display the name of the furniture having rent>250.
ii. Write the command to name the series as Furniture.
Ans:
i. print(S_amt[S_amt>250])
ii. S_amt.name= 'Furniture
Q. Consider the following DataFrame, classframe
Rollno Name Class Section CGPA Stream
St1 1 Aman IX E 8.7 Science
St2 2 Preeti X F 8.9 Arts
St3 3 Kartikey IX D 9.2 Science
St4 4 Lakshay X A 9.4 Commerce
Write commands to :
i. Add a new column ‘Activity’ to the Dataframe
ii. Add a new row with values ( 5 , Mridula ,X, F , 9.8, Science)
Ans:
i. classframe[‘Activity’]=[‘Swimming’,’Dancing’,’Cricket’, ‘Singing’]
ii. classframe.loc[‘St5’]=[5,’Mridula’, ‘X’,‘F’, 9.8, ‘Science’]
Q. Create a dataframe using dictionary of ndarrays.
Ans:
import pandas as pd
data = {'Name':['Tom', 'Jack', 'Steve', 'Ricky'], 'Age':[28,34,29,42]}
df =pd.DataFrame(data, index= ['rank1','rank2','rank3','rank4'])
print (df )
Q. Name any three attributes along with example.

Ans:Three attributes are:
• at
• Transpose
• empty
Q. Write a Pandas program to get the powers of an array values element-wise.
Ans: import numpy as np
import pandas as pd
first_array=nparange(6)
second_array=np.arange(6)
print(first_array)
print(second_array)
power_array=np.power(first_array,second_array)
arr1=np.array([first_array,power_array])
df=pd.DataFrame(arr1,index=['num1','power'])
print(df)
Q. Write a Pandas program to create and display a DataFrame from a specified dictionary data
which has the index labels. Ans: import pandas aspd
importnumpyas np
exam_data={'name':['Parul','Dimple','Himanshu','Dhruv',
'Anoushka','Sushant','Adarsh'],
'score':[12.5,9,16.5,np.nan,9,20,14.5],
'attempts':[1,3,2,3,2,3,1],
qualify':['yes','no','yes','no','no','yes','yes']}
labels =['a','b','c','d','e','f', ‘g’]
df=pd.DataFrame(exam_data, index=labels)
print(df)
Q. Write a Pandas program to display a summary of the basic information about a specified
DataFrame and its data.
Ans:
import pandas aspd
importnumpyas np
exam_data={'name':['Parul','Dimple','Himanshu','Dhruv','Anoushka',
'Sushant','Adarsh'],
'score':[12.5,9,16.5,np.nan,9,20,14.5],
'attempts':[1,3,2,3,2,3,1],
'qualify':['yes','no','yes','no','no','yes','yes']}
labels =['a','b','c','d','e','f', ‘g’]
df=pd.DataFrame(exam_data,
index=labels) print(df)
print("Summary of the basic information about this DataFrame:")
print(df.info())
Q. Pushp, a student of class-XII, has been assigned a code to create a panda series S1, as shown
below.
a 100
b 200
c 300
d 400
e 500
dtype: int64
With reference to the above answer given questions:
i. Choose the command that will give the following output-
b
2
0
0
3
0
0
dtype: int64
ii. Help him to identify the correct statement that can be used to extract t hevalue with the
index 'c'
iii.Which command will give the following output-
b
2
0
0
d
4
0
0
dtype: int64
iv. Which of the following command will display the series by adding 10 in each value.
v. Pushp wants to delete the value against index 'd'. Help him to choose the suitable option
to do so:
Solution:
i. c) print(s1[1:3])
ii. d) print(S1 ['c'])
iii. d) print(S1.iloc[1:4:2])
iv. b) print(S1+10)
v. b) S1=S1.drop('d')
Q.21. HR Department of ABCTech has created following dataframe to store data about salaries
and bonus paid to their employees:
import pandas as pd import numpy as np
d1={'EName':[ 'Kavita', 'Sudha', 'Garima’]'],
'Sal':[50000,60000,55000],'Bonus':[3000,4000,5000]}
df1=pd.DataFrame(d1)
Choose the python statement using suitable functions for the following tasks:
i. Display the columns Sal and Bonus

Answer: (b)df1.loc(['Sal','Bonus'])
ii. Display the details of employee Kavita.

Answer (b) df1.loc[df1.EName=='Kavita']
iii. Display the details of the last employee.

Answer: (a) Df1.tail(1)
iv. Add a new column named ‘Email’ with the value “abc@gmail.com”.
Answer : (a) Df1[‘Email’]= ‘abc@gmail.com’
v. Write a python statement to print the details of employees having Sal more than 50000
Answer: (b) df1[df1.Sal>=5000]
Large Answer Questions

Q.Create a dataframe with these data and display with single column: [33,55,66,39,45]
import pandas as pd
def df_SingleColumn():
l = [33,55,66,39,45]
sum1=0
df = pd.DataFrame(l)
sum1=df.sum()
print("Sum of given numbers:", sum1.to_string(index=False))
df_SingleColumn()
Q.Create a dataframe with the above data (Q-5) and display the sum of the given numbers:
import pandas as pd
def df_SingleColumn():
l = [33,55,66,39,45]
sum1=0
df = pd.DataFrame(l)
sum1=df.sum()
print("Sum of given numbers:", sum1.to_string(index=False))
df_SingleColumn()
Q.What will be the output of following code:
import pandas as pd
def df_data():
df1=pd.DataFrame([22,56,78])
df2=pd.DataFrame([[11,43,67]])
print(df1)
print(df2)
df_data()
Answer:
0
0 22
1 56
2 78
0 1 2
0 11 43 67
Q.Create a dataframe named booking with the following data:
TCode Name Tickets Amount
T0001 Anuj Maheta 5 1355
T0002 Sandeep Oza 2 1169
T0003 Manvi Sharma 6 1988
import pandas as pd
def df_booking():
data = [['T0001','Anuj Maheta',5,1355],
['T0002','Sandeep Oza',2,1169,],
['T0003','Manavi Sharma',6,1988]]
booking=pd.DataFrame(data,
columns=['Tcode','Name','Tickets','Amount'])
print(booking.to_string(index=False))
df_booking()
Q. Create a dataframe furniture shown in the following table:
Item Material Colour Price
Sofa Wooden Maroon 25000
Dining Table Plywood Yellow 20000
Chair Plastic Red 1500
Stainless
Sofa Steel Silver 55000
Chair Wooden Light Blue 2500
Dining Table Aluminum Golden 65000
a) Display the details of the chair and sofa.

b) Display furniture details which price is more than 25000.
c) Display the furniture details price under 10000.
d) Display alternative rows.
Answer:
Creating Dataframe:
import pandas as pd
def df_furniture():
data = [['Sofa','Wooden','Maroon',25000],
['Dining Table','Plywood','Yellow',20000],
['Chair','Plastic', 'Red',1500],
['Sofa','Stainless Steel', 'Silver',55000],
['Chair','Wooden', 'Light Blue',2500],
['Dining Table','Aluminum', 'Golden',65000],]
furniture=pd.DataFrame(data,columns=['Item','Material','Colour','P
rice'])
print(furniture.to_string(index=False))
df_furniture()
a) print(furniture[furniture[‘Item’]==’Sofa’],”n”,furniture[furniture[‘Item’]==’Chair’])
b) print(furniture[furniture[‘Price’]>25000])
c) print(furniture[furniture[‘Price’]<10000])
d) print(furniture.iloc[::2])
Q. Create a dataframe using the 2D dictionary to store the following records:
House Activity1 Activity2 Activity3
Blue House 98 85 88
Red House 87 76 80
Green House 59 67 91
Yellow House 78 99 55
Answer:
import pandas as pd
def df_CCA():
data = {'House':['Blue
House','RedHouse','GreenHouse','YellowHouse'],
'Activity1':[98,87,59,78],
'Activity2':[85,76,67,99],
'Activity3':[88,80,91,55]}
furniture=pd.DataFrame(data)
print(furniture.to_string(index=False))
df_CCA()
 Create a dataframe using the 2d dictionary with values as dictionary objects which
store data of CCA activities for two terms:
Red Green Blue Yellow
Term1 200 350 400 380
Term2 189 250 375 300
import pandas as pd
def df_CCA():
r={'Term1':200,'Term2':189}
g={'Term1':350,'Term2':250}
b={'Term1':400,'Term2':400}
y={'Term1':380,'Term2':300}
c={'Red':r,'Green':g,'Blue':b,'Yellow':y}
df=pd.DataFrame(c)
print(df)
df_CCA()
 Create a dataframe using the following matrix by ndarray, assign row, and column
labels like MS excel.
A B C
1 98 56 87
2 32 76 65
3 55 99 88
import pandas as pd
import numpy as np
def df_ndArray():
nda=np.array([[98,56,97],[32,76,65],[55,99,88]])
df=pd.DataFrame(nda,columns=['A','B','C'],index=[1,2,3])
print(df)
df_ndArray()
Database Query using SQL

Mind Map MySQL Category of Commands
 Mind Map MySQL Aggregate Queries
 Mind Map MySQL Aggregate function


Functions in SQL
A function is used to perform some particular tasks and it returns zero or more values as a
result.
Functions can be applied to work on single or multiple records (rows) of a table. Depending on
their application in one or multiple rows, SQL functions are categorised as Single row functions
and Aggregate functions.
SQL Functions
Single Row Functions Aggregate functions
1. Math Function Count() Function
2. String Function Sum() Function
3. Date and Time Avg() Function

Function
Min() Function
Max() Function
1. Single Row Functions

These are also known as Scalar functions. Single row functions are applied on a single value and
return a single value.
Single row functions under three categories —
1. Numeric (Math)- Math functions accept numeric value as input, and return a numeric value as a
result.
2. String- String functions accept character value as input, and return either character or numeric
values as output.
3. Date and Time-Date and time functions accept date and time values as input, and return numeric
or string, or date and time values as output.
Single Row Function
Numeric Function Date Function

String Function
POWER() NOW()
UCASE()
ROUND() DATE()
LCASE() MONTH()
MOD()
MID() MONTHNAME()
LENGTH() YEAR()
LEFT() DAY()
RIGHT() DAYNAME()
INSTR()
LTRIM()
RTRIM ()
TRIM()
(A) Numeric Functions Three commonly used numeric functions are POWER(), ROUND() and
MOD().
Function Description Example with output
POWER(X,Y) Calculates X to the power Y. mysql>SELECT POWER (2,3);
OR Output: 8
POW(X,Y) mysql>SELECT POW (4,-2);
Output: 0.0625
ROUND(N,D Calculates X to the power Y. Rounds off mysql>SELECT
) number N to D number of decimal places. ROUND(2912.564, 1);
Note: If D=0, then it rounds off the Output: 2912.6
number to the nearest integer mysql>SELECT ROUND(283.2);
Output: 283
MOD(A, B) Returns the remainder after dividing mysql> SELECT MOD(21, 2);
number A by number B. Output: 1
 POWER(X,Y) OR POW(X,Y)- returns the number X raised to the power of another

number Y.
Here the number X is the base and the number Y is the exponent.
Needs 2 numbers as parameters.
SYNTAX: SELECT POW( X, Y);
Examples:
1) mysql> select power(2,3);
+---------------+
| power(2,3) |
+--------------+
|8|
+--------------+
S.N QUERY OUTPUT
O
i) SELECT POWER (10,3); 1000
ii) SELECT POW (10,-3); 0.001
iii) SELECT POWER (9,0); 0
iv) SELECT POW (0,6); 0
v) SELECT POWER (2,3); 8
 ROUND(N,D)- This function is used to round the number to the specified number of
decimal places. Parameters required: the number to be rounded and the number of decimal
places required. If the number of decimal places required is not mentioned, then the result
will not have decimal places.
Syntax: SELECT ROUND(NUMBER, NUMBER OF DECIMAL PLACES)
Examples:
mysql>select round (56.125,2);
+-----------------------+
| round (56.125,2) |
+----------------------+
| 56.12 |
+----------------------+
S.NO QUERY OUTPUT

i) SELECT ROUND(53.78, 1); 53.8
ii) SELECT ROUND(73.153, 2); 73.15
iii) SELECT ROUND(516.429); 516
iv) SELECT ROUND(516.429,0); 516
v) SELECT ROUND(798.15,-1); 800
vi) SELECT ROUND(543.98, -2); 500
Vi SELECT ROUND(453.12,-3); 0
 MOD (A,B )-This function can be used to find modulus (remainder) when one number is
divided by another.
Examples:
mysql>select mod(5,3);
+-------------+
| mod(5,3) |
+-------------+
|2|
+-------------+
S.N QUERY OUTPUT

O
i) SELECT MOD(7,2) 1
ii) SELECT MOD(16,4); 0
iii) SELECT MOD (45,0); null
iv) SELECT MOD(9,0.4); 0.2
v) SELECT MOD(0,5); 0
TABLE - INVENTORY
a) Calculate GST as 12 per cent of Price and display the result after rounding it off to one
decimal place.
mysql> SELECT ROUND(12/100*Price,1) "GST" FROM INVENTORY;
GST
69913.6
80773.4
68043.7
77743.0
42624.6
78589.7
61680.0
73680.0
Output:-
b) Calculate and display the amount to be paid each month (in multiples of 1000) which is to
be calculated after dividing the FinalPrice of the car into 10 instalments and remaining
amount.
mysql> SELECT CarId, FinalPrice, ROUND(FinalPrice-MOD(FinalPrice,1000)/10,0) "EMI"
FROM INVENTORY;
OUTPUT:-
 String Functions- String functions accept character value as input, and return either
character or numeric values as output.
UCASE(string) Converts string into uppercase. mysql> SELECT UCASE(“Informatics
OR Practices”);
UPPER(string) Output: INFORMATICS PRACTICES
LOWER(string) Converts string into lowercase. mysql> SELECT LOWER(“Informatics
OR Practices”);
LCASE(string) Output: informatics practices
MID(string, pos, n) Returns a substring of size n mysql> SELECT MID(“Informatics”, 3,

OR starting from the specified position 4);
SUBSTRING(string, (pos) of the string. If n is not Output: form
pos, n) specified, it returns the substring mysql> SELECT MID(‘Informatics’,7);
OR from the position pos till end of the Output: atics
SUBSTR(string, pos, string.
n)
LENGTH(string) Return the number of characters in mysql> SELECT
the specified string. LENGTH(“Informatics”);
Output:11
LEFT(string, N) Returns N number of characters mysql> SELECT LEFT(“Computer”,
from the left side of the string. 4);
Output:
Comp
RIGHT(string, N) Returns N number of characters mysql> SELECT RIGHT(“SCIENCE”,
from the right side of the string. 3);
Output: NCE
INSTR(string, Returns the position of the first mysql> SELECT
substring) occurrence of the substring in the INSTR(“Informatics”, “ma”);
given string. Returns 0, if the Output: 6
substring is not present in the string.
LTRIM(string) Returns the given string after mysql> SELECT LENGTH(“
removing leading white space DELHI”), LENGTH(LTRIM(“
characters. DELHI”));
Output:
+--------+--------+
|7 |5 |
+--------+--------+
row in set (0.00 sec)
RTRIM(string) Returns the given string after mysql>SELECT LENGTH(“PEN “)
removing trailing white space LENGTH(RTRIM(“PEN “));
characters. Output:
+--------+--------+
|5 |3 |
+--------+--------+
row in set (0.00 sec)
TRIM(string) Returns the given string after mysql> SELECT LENGTH(“ MADAM
removing both leading and trailing “),LENGTH(TRIM(“ MADAM “));
white space characters.
1. UCASE( ) / UPPER( )-Used to convert a character or text to uppercase.
Syntax-
Examples: mysql>SELECT UCASE(str/column); or
SELECT UCASE(str/column) FROM Table name;
mysql>SELECT UCASE('hello');
+-------------------+
| UCASE('hello') |
+-------------------+
| HELLO |
+------------------+
1. LCASE( ) / LOWER( ) :To convert a character or text to lowercase.

Examples:
mysql>select lcase('HELLO');
+-------------------+
| lcase('HELLO') |
+-------------------+
| hello |
+-------------------+
1 row in set (0.00 sec)
mysql>select LOWER('HELLO');
+-----------------------+
| LOWER('HELLO') |
+-----------------------+
| hello |
+----------------------+
1. MID(string, pos, n) / SUBSTRING(string, pos, n) /SUBSTR(string, pos, n):- To extract a
specified number of characters from the string.First parameter is the text/string. Second
parameter is the starting index and the third parameter is the number of characters required.
(Note: index starts with 1 and not 0.)
Syntax:- SELECT SUBSTR(string, pos, n); or
SELECT SUBSTR/MID(Column, pos, n) FROM table name;
Examples:
Mysql> SELECT SUBSTR ('exam@cbse.nic.in',3,4);
+-----------------------------------------+
SUBSTR ('exam@cbse.nic.in',3,5)|
+-----------------------------------------+
| am@cb |
+-----------------------------------------+
2. LENGTH( ) : This function returns the number of characters in the given text.
Syntax:- SELECT LENGTH(Sring); or
SELECT LENGTH(Column) FROM Table name;
Examples:
1) mysql> SELECT LENGTH ('HELLO WORLD');
+--------------------------------------+
| LENGTH('HELLO WORLD') |
+-------------------------------------+
| 11 |
+-------------------------------------+
3. LEFT( ) :Returns the specified number of characters including space starting from the
leftmost characters. Parameters required: text, number of characters to be extracted.
Syntax- SELECT LEFT(StrING,N); or
SELECT LEFT(column) FROM Table name;
Examples:
mysql>SELECT LEFT('INFORMATICS PRACTICES’,5);
LEFT('INFORMATICS PRACTICES’,5)
INFOR
4. RIGHT( ) :Returns the specified number of characters including space starting from the
right of the text.Parameters required : text, number of characters to be extracted.
Syntax- SELECT RIGHT(String,N);
SELECT RIGHT(column) FROM Table name;
Examples: mysql> SELECT RIGHT('INFORMATICS PRACTICES’,5);
RIGHT('INFORMATICS
PRACTICES’,5)
TICES
5. INSTR( ) :Returns the position of the first occurrence of the substring in the given string.
Returns 0, if the substring is not present in the string.
Syntax- SELECT INSTR(String,SubString); or
SELECT INSTR(String,SubString) FROM table name;
Examples:
mysql> SELECT INSTR('MySql Functions',’Sql’);
INSTR('MySql Functions',’Sql’)
3
6. LTRIM( ) :To trim the spaces, if any, from the beginning of the text.
Syntax- SELECT LTRIM(String); or
SELECT LTRIM(Column) FROM table name;
Examples:
mysql> SELECT LTRIM(' HELLO');
LTRIM(' KENDRIYA ')
‘KENDRIYA ‘
7. RTRIM( ) : Returns the given string after removing trailing white space characters.
Syntax- SELECT RTRIM(String); or
SELECT RTRIM(Column) FROM table name;
Examples:
mysql> SELECT RTRIM(' HELLO');
RTRIM(' KENDRIYA ')
‘KENDRIYA ‘
8. TRIM( ): To trim the spaces, if any, from the beginning and end of the text.
Syntax- SELECT TRIM(String); or
SELECT TRIM(Column) FROM table name;
Examples:
mysql> SELECT TRIM(' KENDRIYA ');
TRIM(' KENDRIYA ')

‘KENDRIYA‘
Let us use Customer relation shown in Table to understand the working of string functions.
TABLE -CUSTOMER
a) Display customer name in lower case and customer email in upper case from table
CUSTOMER.
mysql> SELECT LOWER(CustName), UPPER(Email) FROM CUSTOMER;
LOWER(CustName) UPPER(Email)
amitsaha AMITSAHA2@GMAIL.COM
Rehnuma REHNUMA@HOTMAIL.COM
charvinayyar CHARVI123@YAHOO.COM
gurpreet GUR_SINGH@YAHOO.COM
b) Display the length of the email and part of the email from the email id before the
character ‘@’. Note - Do not print ‘@’.
mysql> SELECT LENGTH(Email), LEFT(Email, INSTR(Email, "@")-1) FROM
CUSTOMER;
LENGTH(Email) LEFT(Email, INSTR(Email, "@")-1)
19 amitsaha2
19 rehnuma
19 charvi123
19 gur_singh
* The function INSTR will return the position of “@” in the email address. So, to print
email id without “@” we have to use position -1.
C) write the SQL query to display the area code (3 rd to 6th digit from left side in phone
no.)of the customer
mysql> SELECT MID(Phone,3,4) FROM CUSTOMER;
MID(Phone,3,4)
64587
2768
1163
1105
d) Display emails after removing the domain name extension “.com” from emails of
the customers.
mysql> SELECT TRIM(“.com” from Email) FROM CUSTOMER;
TRIM(".com" FROM Email)
amitsaha2@gmail
rehnuma@hotmail
charvi123@yahoo
gur_singh@yahoo
(C) Date and Time Functions

There are various functions that are used to perform operations on date and time data. Some of the
operations include displaying the current date, extracting each element of a date (day, month and
year), displaying day of the week and so on.
Date Functions

NOW() It returns the current system mysql> SELECT NOW();
date and time. Output:
2019-07-11 19:41:17
DATE() It returns the date part from mysql> SELECT DATE(NOW());
the given date/ time Output:
expression. 2019-07-11
MONTH(date) It returns the month in mysql> SELECT MONTH(NOW());
numeric form from the date. Output:
7
MONTHNAME It returns the month name mysql> SELECT MONTHNAME(“2003-11-
(date) from the specified date. 28”);
Output:
November
YEAR(date) It returns the year from the mysql> SELECT YEAR(“2003-10-03”);
date. Output:
2003
DAY(date) It returns the day part from mysql> SELECT DAY(“2003-03-24”);
the date. Output:
24
DAYNAME(date It returns the name of the day mysql> SELECT DAYNAME(“2019-07-11”);
) from the date. Output:
Thursday
Let us use the EMPLOYEE table of CARSHOWROOM database to illustrate the working of
some of the date and time functions.
TABLE-EMPLOYEE
a) Select the day, month number and year of joining of all employees.
mysql> SELECT DAY(DOJ), MONTH(DOJ), YEAR(DOJ) FROM EMPLOYEE;
DAY(DOJ MONTH(DOJ) YEAR(DOJ)
)
12 12 2017
5 6 2016
8 1 1999
2 12 2010
1 7 2012
b) If the date of joining is not a Sunday, then display it in the following format "Wednesday,
26, November, 1979."
mysql> SELECT DAYNAME(DOJ), DAY(DOJ), MONTHNAME(DOJ), YEAR(DOJ) FROM
EMPLOYEE WHERE DAYNAME(DOJ)!='Sunday';
+-----------------------+--------------+----------------------------+----------------+
| DAYNAME(DOJ) | DAY(DOJ) | MONTHNAME(DOJ) | YEAR(DOJ) |
+-----------------------+--------------+----------------------------+----------------+
| Tuesday | 12 | December | 2017 |
| Friday | 8 | January | 1999 |
| Thursday | 2 | December | 2010 |
+----------------------+---------------+---------------------------+-----------------+
c) Display the details of those Employee who have joined in the month of December
-> SELECT * FROM Employee WHERE Month (DOJ)=12;
d) Select the Employee name and designation of 1975 born.
->SELECT EmpName ,Designation FROM Employee WHERE Year(DOB)=1975;
 Aggregate Functions
Aggregate functions are also called multiple row functions. These functions work on a set
of records as a whole, and return a single value for each column of the records on which the
fu
 Type of Aggregate Function
S.N Function Description

O
01 SUM() Find the sum of numeric values
02 AVG() Find the average of numeric values
03 COUNT() Counts the number of records in a table
04 MAX() Find the maximum among all the values in a column
05 MIN() Find the minimum among all the values in a column
Remember the following points about group functions:

● All group functions,except count(*) ignore NULL values
● Functions -sum(),avg() are used with NUMERIC data.
● Functions -min() and max() can be used with any data type
 count (*) Vs count ()
count (*) function is used to count the number of records in query output whereas count
() is used to count values present in any column excluding NULL values.
Note:-All aggregate function ignores the NULL values.
TABLE- INVENTORY
CarId CarName Price Model YearManufacture FuelType FinalPrice
D001 Dzire 582613.00 LXI 2017 Petrol 652526.6
D002 Dzire 673112.00 VXI 2018 Petrol 753885.4
D003 Dzire 567031.00 Sigma 2019 NULL 635074.7
E001 EECO 647858.00 Delta1 2017 CNG 397829.6
S001 SWIFT 514000.00 5 STR 2018 CNG 725601.0
1. MAX(column):- Returns the largest value from the specified column.

Syntax- SELECT MAX(Column) FROM Table_name;
mysql> SELECT MAX(Price) As “Highest Price” FROM INVENTORY;
Output:-
MAX(Price)
Highest Price
*Mysql : Keyword AS (Aliases)-can be used to create a temporary name for columns or
tables.
Syntax:- Select old_ColName As “new column”/ new_column FROM table;
2. MIN(column)- Returns the smallest value from the specified column.

Syntax- SELECT MIN(Column) FROM Table_name;
mysql> SELECT MIN(CarName) FROM INVENTORY;

Output:-
MIN(CarName)
Dzire
3. AVG(column)- Returns the average of the values in the specified column.

Syntax- SELECT AVG(Column) FROM Table_name;
mysql> SELECT AVG(Price) FROM INVENTORY;

Output:-
AVG(Price)
596922.8
4. SUM(column)- Returns the sum of the values for the specified column.
Syntax- SELECT SUM(Column) FROM Table_name;
mysql> SELECT SUM(Price) FROM INVENTORY;
Output:
SUM(Price)
2984614
5. COUNT(column)-Returns the number of values in the specified column ignoring the

NULL values.
Note:- COUNT(FuelType) count all the values under FuelType column except NULL
mysql>SELECT COUNT(FuelType) from INVENTORY;

Output:
COUNT(FuelType)
4
SELECT COUNT( DISTINCTFuelType) from INVENTORY;

Output:
COUNT(FuelType)
4
 DISTINCT clause is used to remove duplicates from the result set of a Select
statement.(It include Null as unique value)
E.g SELECT DISTINCT FuelType from INVENTORY;
DISTINCT FuelType
PATROL
NULL
CNG
6. COUNT(*) Returns the number of records in a table.

Note: In order to display the number of records that matches a particular criteria in the table,
we have to use COUNT(*) with WHERE clause.
->Display the total number of records from table INVENTORY .

mysql> SELECT COUNT (*) from INVENTORY;
Output:
COUNT(*)
5
->Display the total number of records from table INVENTORY having a Car nameas Dzire.
mysql> SELECT COUNT(*) FROM INVENTORY WHERE CarName=”Dzire”;
COUNT(*)
2
 The SQL ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set in ascending or descending order.
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in
descending order, use the DESC keyword.
ORDER BY Syntax:-
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
TABLE- INVENTORY
D003 Dzire 567031.00 Sigma NULL NULL 635074.7
E001 EECO 647858.00 Delta1 2017 CNG 397829.6
S001 SWIFT 514000.00 5 STR 2018 CNG 725601.0
Example:-
->Selects details of all the Inventory in ascending order of their Price.
SELECT * FROM INVENTORY
ORDER BY Price;
*By default Price values arrange in ascending order
S001 SWIFT 514000.00 5 STR 2018 CNG 725601

D003 Dzire 567031.00 Sigma NULL NULL 635074.7
E001 EECO 647858.00 Delta1 2017 CNG 397829.6
->Display the Car name ,Price and year of manufacture in ascendingCar Name and descending
order of YearManufacture.
Mysql>SELECT CarName,PriceYearManufactureFROM Inventory
ORDER BY YearManufactureASC ,CarName DESC;
OUTPUT:-
CarName Price YearManufacture
Baleno 567031 NULL
Dzire 673112 2018
Dzire 582613 2017
EECO 647858 2017
SWIFT 514000 2018
Order By would return all records sorted by the YearManufacture column in ascending order with
a secondary sort by Car name in descending order.
*Null comes before the non-Null values in ascending order and in descending order Null will
appear last in result set.( In Mysql , Null is lower than non Null Values)
 GROUP BY in SQL- It groups the rows together that contain the same values in a
specified column. We can use the aggregate functions (COUNT, MAX, MIN, AVG and
SUM) to work on the grouped values. HAVING Clause in SQL is used to specify
conditions on the rows with GROUP BY clause.
o To divide the rows in a table into smaller groups of information, group by clause is used.
o It combines all identical rows in a group of fields.
o A column name is used for grouping
Syntax- SELECT column_name(s) FROMtable_nameWHERE Condition>
GROUP BY Column_nameHAVING ConditionORDER BY Col1 ASC/DESC, Col2

ASC/DESC;
NOTE-
● Group by expression specifies the columns whose values determine the basics for grouping rows
● WHERE clause is always before GROUP BY if required.
Having clause
● This clause is used to restrict rows resulting after grouping.
● Steps followed in execution of select with group by and having clause
1. Rows are grouped according to the columns in the group by clause.
2. Then the group function is applied.
3. Groups matching with Having clauses are displayed.
WHERE CLAUSE VS HAVING CLAUSE
Where Clause Having Clase
WHERE Clause is used to filter HAVING Clause is used to filter record
the records from the table based from the groups based on the specified
on the specified condition. condition.
WHERE Clause can be used HAVING Clause cannot be used
without GROUP BY Clause without GROUP BY Clause
Syntax:- Select * from table_name
Select * from table_name Group by col_name
Where condition Having condition;
Group by col_name;
TABLE- SALE
a) Display the number of cars purchased by each customer from the SALE table.
mysql> SELECT CustID, COUNT(*) "Number of Cars" FROM SALE GROUP BY CustID;
OUTPUT:-
CustID Number of Cars
C0001 2
C0002 2
C0003 1
C0004 1
b) Display the customer Id and number of cars purchased if the customer purchased more
than 1 car from SALE table.
mysql> SELECT CustID, COUNT(*) FROM SALE GROUP BY CustID HAVING Count(*)>1;
CustID COUNT(*)
C0001 2
C0002 2
c) Display the number of people in each category of payment mode from the table SALE.
mysql> SELECT PaymentMode, COUNT(PaymentMode) FROM SALE GROUP BY
Paymentmode ORDER BY Paymentmode;
OUTPUT:-
PaymentMod Count(PaymentMode)
e
Bank Finance 2
Cheque 1
)Credit Card 2
Online 1
d) Display the PaymentMode and number of payments made using that mode more than
once.
mysql> SELECT PaymentMode, Count(PaymentMode) FROM SALE GROUP BY Paymentmode
HAVING COUNT(*)>1 ORDER BY Paymentmode;
OUTPUT:-
PaymentMode Count(PaymentMode)
Bank Finance 2
Credit Card 2
MULTIPLE CHOICE QUESTIONS: -

1. All aggregate functions ignore NULLs except for the __________ function.
(a) count(column) (b) Count(*) (c) Average() (d) None of these
Ans:- b)count(*)
2. Which of the following are correct aggregate functions in SQL.
(a) AVERAGE() (b) MAX() (c) COUNT() (d) TOTAL()
Ans:- (b) MAX()
3. Aggregate functions can be used in the select list or the ____ clause of the select statement.
They cannot be used in a _______ clause.
(a) Where, having (b) Having, where (c) Group by, having (d) Group by where
Ans:- (c) Group by, having
4. What is the meaning of “HAVING” clause in SELECT query.
(a) To filter out the summary groups.
(b) To filter out the column groups.
(c) To filter out the row and column values.
(d) None of the mentioned.
Ans:- (a) To filter out the summary groups
5. Which of the following is not a text function?
(a) TRIM () (b) MOD() (c) LEFT() (d) MID ()
Ans:- (b) MOD()
6. What will be returned by the given query ?
SELECT INSTR(‘INDIA’, ‘DI’);
(a) 2 (b) 3 (c) -2 (d) -3
Ans:- (b) 3
7.. What will be returned by the given query ?
SELECT ROUND(153.669,2);
(a) 153.6 (b) 153.66 (c) 153.67 (d) 153.7
Ans:- (c) 153.67
8. What will be returned by the given query?
SELECT month(‘2020-05-11’);
(a) 5 (b) 11 (c) May (d) November
Ans(a) 5
9. Which of the following would arrange the rows in ascending order in SQL.
a. SORT BY b. ALIGN BY c. GROUP BY d. ORDER BY
Ans:- d. ORDER BY
10. Prachi has given the following command to obtain the highest marks
Select max(marks) from student where group by class;
but she is not getting the desired result. Help her by writing the correct command.
a. Select max(marks) from student where group by class;
b. Select class, max(marks) from student group by marks;
c. Select class, max(marks) group by class from student;
d. Select class, max(marks) from student group by class;
Ans:- (d) Select class, max(marks) from student group by class;
11. Help Ritesh to write the command to display the name of the youngest student?
a. select name,min(DOB) from student ;
b. select name,max(DOB) from student ;
c. select name,min(DOB) from student group by name ;
d. select name,maximum(DOB) from student;
Ans:- b. select name,max(DOB) from student ;
True and False Questions

1. SQL is a case sensitive.
2. The condition in a WHERE clause in a SELECT query can refer to only one value.
3. SQL provides the AS keyword, which can be used to assign meaningful column name to
the results of queries using the SQL built-in functions.
4. SQL is a programing language.
5. SELECT DISTINCT is used if a user wishes to see duplicate columns in a query.
6. ORDER BY can be combined with SELECT statement.
7. DELETE FROM <table> command is same as DROM TABLE <table> command.
8. The unique constraint can only be defined once in the CREATE TABLE command.
9. Unique and Primary Key constraints are the same.
10. Tuple based constraints can use multiple columns of the table.
11. The table-based constraints can use multiple column of the table.
12. You can add a column with a NOT NULL constraint using ALTER TABLE, only to a table
that contains no rows.
13. You can use the INSERT statement only to add one new row and not multiple new rows to
an existing table.
14. The HAVING and WHERE clause are interchangeable.
15. The HAVING clauses can take any valid SQL function in its condition.
16. MOD() is a text function.
17. Length() is a numeric function. 1
18. Functions MID() and SUBSTR() do the same thing. 1
19. INSTR() and SUBSTR() work identically.
True / False
1. False 2. False 3. True 4. False 5. False 6. True 7. False 8. False9. False 10. False 11. True 12
True13 False 14. False 15. False 16 False17 False 18 True 19 False
SHORT ANSWER TYPE QUESTION
1. Meena is learning about database terms. She is a bit confused about HAVING clause and
WHERE clause. Help her to understand the difference between them along with suitable
example.
Where Clause Having Clase
WHERE Clause is used to filter HAVING Clause is used to filter record
the records from the table based from the groups based on the specified
on the specified condition. condition.
WHERE Clause can be used HAVING Clause cannot be used
without GROUP BY Clause without GROUP BY Clause
Syntax:- Select * from table_name
Select * from table_name Group by col_name
Where condition Having condition;
Group by col_name;
Ans
2. Amar is executing date functions on MySQL but he is confused between date ()

and now() functions, so help him to clear his confusion with example
ans:-
The DATE() function extracts the date part from a datetime expression.
Syntax. DATE(expression)
Select date(“2022-03-11 10:01:12”);
->2022-03-11
The NOW() function returns the current date and time.

Note: The date and time is returned as "YYYY-MM-DD HH-MM-SS”.
Select now();
->2022-03-11 10:01:12
3. What is the purpose of GROUP BY clause in MySQL ? How is it different from ORDER
BY clause ? explain with example.
Ans:-
Group By Order By
GROUP BY clause allows the user ORDER BY clause is used to
to define a subset of the values in produce output in a logical order
particular field in term of another
field.
It also applies an aggregate function The order may be of Ascending or
to the subset. Descending for numbers or alphabet
-> SELECT col, aggregate(col) ->Select * from table_name
FROM table_name GROUP BY col; Order by col asc/desc;
4. Sanam has executed following queries based on the above given table named ‘Exam’:
Select count(*) from exam;

Select count(MARKS) from exam;
Predict the output of the above given queries.
Also give proper justifications of the output generated through each query.
Ans:- First query will produce the output 7.
Justification: count (*) will count and display total number of rows (irrespective of any null
value present in any of the column).
Second query will produce the output 6.
Justification: count (col_name) will count and display total number of not null values in the
specified column.
5. What is the purpose of Order By clause in SQL? Explain with the help of suitable example.
Ans:- Order By clause: The ORDER BY command is used to sort the result set in ascending
or descending order. The following SQL statement displays all the customer’s names in
alphabetical order: SELECT Cname FROM Customers ORDER BY Cname;
6. Explain the following SQL functions using suitable examples.

i. UCASE()
ii. TRIM()
iii. MID()
iv. DAYNAME()
v. POWER()
Ans:-1. UCASE(): It converts the string into upper case.

Example:
SELECT UCASE(‘welcome world’);
Output:
WELCOME WORLD
2. TRIM(): It removes the leading and trailing spaces from the given string.
Example:
SELECT TRIM(‘ Welcome world ‘ );
Output:
Welcome world
3. MID(): It extracts the specified number of characters from given string.

Example:
SELECT MID(‘ Welcome world,4,,4);
Output:
Come
4. DAYNAME(): It returns the weekday name for a given date

Example:
SELECT DAYNAME(‘2022-07-22’);
Output:
Friday
5. POWER(): It returns the value of a number raised to the power of another

number.
Example:
SELECT POW(6,2);
Output:
36
Q7. Gokul is using a table Employee. It has the following columns:

Code, Name, Salary, Dept_code
He wants to display maximum salary department wise. He wrote the following command:
SELECT Dept_code, Max(Salary) FROM Employee;
But he did not get the desired result.
Rewrite the above query with necessary changes to help him get the desired output?
Ans:- SELECT Dept_code, Max(Salary) FROM Employee group by Dept_code;
Q8. Why is it not allowed to give String and Date type arguments for SUM( ) and AVG( )
functions ?
Ans:- SUM( ) and AVG( ) functions works only with numbers and not with strings and dates.
Q9. Prachi has given the following command to obtain the highest marks
Select max(marks) from student where group by class;
But she is not getting the desired result. Help her by writing the correct command.
Ans. Select max(marks) from student group by class;
1. Which SQL aggregate function is used to count all records of a table?
COUNT(*)
2. Write a query to display current date on your system.
SELECT CURDATE();
3. Write a query to extract month part from date 3rd Feb 2021 .
SELECT MONTH(‘2021-08-03’);
4. Write a query to display name of weekday for date 03rd Feb 2021.
QUERY BASED QUESTIONS

1. Consider the decimal number 289.573. Write SQL commands:
(i) To round it off to a whole number
(ii) To round it to 2 places before decimal
Ans:- Ans i) Select round(289.573);
ii) Select round(289.573,-2);
2. Mr Rohan is working on a MySQL table ‘Sports’ having following records: -
Table: - Sports
Rno Class Name Game City
10 7 Sammer Cricket Shimla
11 8 Sujit Tennis Goa
12 7 Kamal Swimming Mumbai
13 7 Venna Tennis Chandigarh
Mr Rohan wants to perform following task on the table:
i. To fetch first 4 characters from the city.
ii. To display the last 3 characters of player’s name in uppercase.
iii.To display 4 characters from 3rd place from game column.
So, help him to complete his task by suggesting him correct MySQL queries.
Ans:- Select left(city,4) from sports;
ii) Select ucase/upper(right(name,3)) from sports;
iii)Select substr/mid(Game,3,4) from sports;
3. Carefully observe the following table named ‘Product’:
Table : Product
P_ID PName Category Qty Price
101 Table Plastic 10 500
102 Chair Wooden 15 1000
103 Stool Plastic 12 350
104 Table Wooden 20 1200
105 Chair Plastic 08 300
106 Bench Wooden 25 2000
Write SQL queries for the following:

(i) To display the records in decreasing order of price.
(ii) To display category and category wise total quantities of products
(iii) To display the category and its average price.
(iv) To display category and category wise lowest price of the products.
Ans:-
(i) select * from Product order by Price desc;
ii) select category, sum(Qty) from Product group by Category;
(iii) select category,avg(price) from Product group by category;
(iv) select category, min(Price) from Product group by category;
4. Based on table STUDENT given here, write suitable SQL queries for the following:
STUDENT
i. Display Class wise highest marks.

ii. Display Gender wise average marks.
iii. Display total number of male and female students.
Ans:-i. select max(marks) from student group by gender;
ii. select min(marks) from student group by city;
iii. select gender,count(gender) from student group by gender;
5. Write suitable SQL query for the following:

i. Display 7 characters extracted from 7th left character onwards from the string ‘INDIA
SHINING’.
ii. Display the position of occurrence of string ‘COME’ in the string ‘WELCOME WORLD’.
iii. Round off the value 23.78 to one decimal place.
iv. Display the remainder of 100 divided by 9.
v. Remove all the expected leading and trailing spaces from a column userid of the table
‘USERS’.
Ans:-i. select mid('INDIA SHINING',7,7);
ii. select INSTR('WELCOME WORLD','COME');
iii. select round(23.78,1);
iv. select mod(100,9);
v. select trim(userid) from users;
6. Consider the following Teacher table: Write SQL commands for (i) and (ii)
Teacher
(i) To display the details of Teacher table in ascending order of Salary.

(ii) Display Department, total Teachers in each Department.
Ans:- i) Select * from Teacher order by Salary;
ii) SELECT Department,count(*) from Teacher group by Department;
7. Carefully observe the following table named ‘student’:

Table: student
Write SQL queries for the following:

(a) To display the records in decreasing order of Name.
(b) To display category and category wise total Marks of Student.
(c) To display the category and its average Marks.
(d) To display category and category wise highest Marks of the Student.
ANS:- (a) select * from student order by Name desc;
(b) select category, sum(Marks) from student group by Name;
(c) select category,avg(price) from stock group by category;
(d) select category, max(Marks) from studentock group by category;
8. Consider the following WORKER tables. Write SQL queries for (i) to (iv) and find output for
SQL query
(v).
WORKER
(i) To display WNO, NAME, GENDER from the table WORKER in descending order of WNO.
(ii) To display the NAME of all the FEMALE workers from the table WORKER.
(iii) To display the WNO and NAME of those workers from the table WORKER, who
are born between ‘1987-01-01’ and ‘1991-12-01’
(iv) To count and display MALE workers who have joined after ‘1986-01-01’
Ans: - SELECT WNO, NAME, GENDER FROM WORKER ORDER BY WNO DESC;
(ii) SELECT NAME FROM WORKER WHERE GENDER "FEMALE";
(iii) SELECT WNO, NAME FROM WORKER WHERE DOB BETWEEN ‘1987-01-01’ AND
‘1991-12-01’;
iv) SELECT COUNT(*) FROM WORKER WHERE GENDER "MALE" AND DOJ > ‘1986-01-
01’;
Write SQL Command for table Graduate from (a) to (c)

TABLE : GRADUATE
a. List the names of those students who have obtained DIV I sorted by NAME.
b. Display a report, listing NAME, STIPEND, SUBJECT and amount of stipend received in a year
assuming that the STIPEND is paid every month.
c. To count the number of students who are either PHYSICS or COMPUTER SC graduates.
Ans:- a. SELECT NAME from GRADUATE where DIV = ‘I’ order by NAME;
b. SELECT NAME, STIPEND, SUBJECT, STIPEND*12 from GRADUATE;
c. SELECT SUBJECT, COUNT(*) from GRADUATE group by SUBJECT having
SUBJECT=’PHYISCS’ or SUBJECT=’COMPUTER SC’;
Q10. Consider the table ‘FANS’ and answer the following.
Table :-FAN
Write MySQL queries for the following:

i. To display the details of fans in descending order of their DOB
ii. To count the total number of fans of each fan mode
iii. To display the dob of the youngest fan
Ans:- i. SELECT * FROM FANS ORDER BY FAN_DOB DESC;
ii. SELECT FAN_MODE, COUNT(*) FROM FANS GROUP BY FAN_MODE;
iii. SELECT MAX(FAN_DOB) FROM FANS;
OUTPUT BASED QUESTIONS

1. Consider the table Items given below and answer the queries:
Table: Items
Item_no Item_Name Price Qty
T101 Powder 40 6
T102 Face Wash 41 5
T103 Bath Soap 53 10
T104 Shampoo 96 3
(i) Select mod(Price,5) from Items;

(ii) Select power(Qty,2) from Items;
1. Ans:- Ans:- i)
mod(Price,5)
0
1
3
1
ii)
power(Qty,2)
36
25
100
9
2. Help Riya in predicting the output of the following queries:

(i) Select power(mod(7,4),3);
(ii) Select mod (5,10);
Ans:-i) 27
ii)5
3. Help Rehan in predicting the output of the following queries:
i) select dayname(now());
ii) select monthname(date(“2022-03-11 10:01:12”));
2. Ans:- Friday
March
4. Predict the output of the following queries:
i. select instr(right(“xyz@yahoo.co.in”,12), “.in”);
ii. select substr(left('term2@cbse.nic.in',10),3,4);
iii) select length(trim(“ hello123 ”));
Ans:- i) 10
ii)rm2@
iii)8
5. Ms Reema has created the following table to store the records of Trainer:
Table: Trainer
TID TNAME CITY HIREDATE SALARY
101 SUNAINA MUMBAI 1998-10-15 90000
102 ANAMIKA DELHI 1994-12-24 80000
103 DEEPTI CHANDIGARH 2001-12-21 82000
104 MEENAKSHI DELHI 2002-12-25 78000
105 RICHA MUMBAI 1996-01-12 95000
106 MANIPRABHA CHENNAI 2001-12-12 69000
She has written following queries :

i) Select max(month(Hiredate)) from Trainer;
ii) Select tname, salary from Trainer where year(Hiredate)=2001;
iii) Select day(Hiredate) from Trainer where city in (“Delhi”,”Chennai”);
Help her to write the correct output for the above queries.
ANS:-i)
max(month(Hiredate)
)
12
ii)
TNAME SALARY
DEEPTI 82000
MANIPRABHA 69000
iii)
day(Hiredate))
24
25
12
6. Abhishek is working as a data analyst. He has created the following table.

Table: Exam
Admno SName Class Stream Marks
A101 Anushka 12 Science 90
A102 Neelam 12 Commerce 80
A103 Soma 11 Humanities 68
A104 Manish 12 Science Null
A105 Aman 11 Commerce 88
A106 Saurav 12 Humanities 75
A107 Bhanu 11 Science 96
He has written following queries:

(i) Select Sum (Marks) from Exam where stream= “commerce”;
(ii) Select Max (Marks)+Min(Marks) from Exam where Class= 12;
(iii) Select AVG (Marks) from Exam where stream= “Science” and
SNamelike “%a%”;
(iv) Select Count (Sname) from Exam where Marks between 75 and 90;
Ans: i)
Sum(Marks)
168
ii)
Max(Marks)+Min(Marks)
165
iii)
AVG (Marks)
93
iv)
Count(Sname)
4
Help him in predicting the output of the above given queries.
Q7. Consider the following Teacher table: Write SQL commands for (i) and (ii)
Teacher
i) SELECT Department, count(*) from Teacher GROUP BY Department having

count(*)>=2;
Department Count(*)
Physics 2
Ans:-
ii) SELECT avg(NoofPeriods) from Teacher;

Ans:-Avg(NoofPeriods)
25
Q8. Write the output for SQL queries (i) to (iii), which are based on the table given below:
Table:SPORTS
i) SELECT COUNT(*) FROM SPORTS WHERE NAME LIKE ‘%a%’;

(ii) SELECT MAX(Class) FROM SPORTS WHERE Grade1=Grade2;
(iii) SELECT Game1, COUNT(*) FROM SPORTS GROUP BY Game1;
COUNT(*)
5
Ans:-) i)
MAX(Class)
9
ii)
iii)
Game1 COUNT(*)
A 3
B 2
C 1
Q9. Write the output for the following SQL commands.

Table : Shop
1. Select Area ,min(sale) from shop where sale>300000 group by Area;

2. Select count(distinct city) from shop;
3. select avg(sale) from shop where Area=’South’;
4. select avg(Cust_percentage),sum(sale) from shop where rating =’A’;
Ans:-
1.
Area min(sale)
South 428000
North 380000
East 456000
count(distinct city)
6
2.
3.
avg(sale)
40600
avg(Cust_percentage) sum(sale)
82.623 1674000
4.
Assertion and Reason Based Questions
A-Having Clause is used with Group by Clause

R- Group By clause used to group the result based on distinct values in a columnand
having is used to filter the Groups
a)Both A and R are true and R is the correct explanation of A.
b)Both A and R are true, but R is not the correct explanation of A.
c)A is true, but R is false.
d)A is false, but R is true.
Ans:-a)
A-The ORDER BY keyword by default sort the result-set in ascending order.

R. Alphabetically Ascending comes first then descending.
a)Both A and R are true and R is the correct explanation of A.
b)Both A and R are true, but R is not the correct explanation of A.
c)A is true, but R is false.
d)A is false, but R is true.
Ans:-c)
COMPUTER NETWORK
Several devices connected to each other for reliable communication/transfer of data
constitute a network. A network can consist of a computer, a fax machine, a printer, a
camera, a cell phone, etc. A collection of interconnected computers is called a Computer
Network.
A computer network is a collection of interconnected computers and other devices to

share data and other resources (hardware and software resources).
A few of these advantages are:

⮚ Resource Sharing
⮚ Cost saving
⮚ Collaborative user interaction
⮚ Time saving
⮚ Increased storage
Elementary Terminology of Networks

1. Nodes (Workstations): The term node refers to computers that are attached to a
network and are seeking to share resources.
2. Server: A computer that facilitates the sharing of data, software and hardware
resources on the network.
3. Network Interface Unit (NIU) (MAC Address): A network interface unit is an
interpreter that helps in establishing communication between the server and the
client.
4. IP Address: Every machine on a TCP bar/IP Network has a unique identifying number
called an IP Address.
5. Domain Name: It is a way to identify and locate the computers connected to the
internet. It must be unique.
TYPES OF NETWORKS
On the basis of geographical spread, networks may be classified as:
1. PAN 2. LAN 3. MAN 4. WAN
PAN (Personal Area Network):
A PAN is a network of Communicating devices (Computer, Phone, MP3/MP4 Player,
Camera etc.) in the proximity of an individual. It can cover an area of a few meters radius.
Local Area Network (LAN)
LAN is a privately owned computer network covering a small geographical area (small
physical area), like a home, office or a building such as a school. It can cover an area
spread over a few metres to a radius of a few kilometres.
Data transfer rate speed over a Local Area Network can vary from 10 mbps to 1 gbps.
Metropolitan Area Network : A MAN is a network of computing/communicating devices

within a city. It can cover an area of a few kilometers to a few hundred kilometers radius.
A network of schools, or banks, or Government offices etc., within a city, are examples of
MANs. A MAN is usually formed by interconnecting a number of LANs and individual
computers.
WAN (Wide Area Network): A WAN is a telecommunication network. This type of
network spreads over a large geographical area across countries and continents. WANs
are generally used to interconnect several other types of networks such as LANs, MANs,
etc.
A WAN interconnects all the computers across the world.
The following table summarizes the characteristics of PANs, LANs, MANs and WANs.
NETWORK DEVICES :
Modem: A MODEM (Modulator DEModulator) is an electronic device that enables a
computer to transmit data over telephone lines. It is a device used to convert digital
signals into analog signals and vice versa.
RJ-45 Connector:
RJ-45 is a short term for Registered Jack-45. It is an eight-wire connector used to connect
computers on LANs, especially Ethernets.
Ethernet Card: It is a hardware device that helps in the connection of nodes within a
network. Ethernet card is also known as a network card, network adapter or NIC (network
interface card). It is a card that allows computers to communicate over a computer
network. On Ethernet card, a physical address of each communicating computer is
mentioned. Physical address is known as MAC address.
Hub:
A Hub is an electronic device that connects several nodes to form a network and
redirects the received information to all the connected nodes in broadcast mode.
Switch: A Switch is an intelligent device that connects several nodes to form a network
and redirects the received information only to the intended node(s).
A Repeater is a device that is used to regenerate a signal which is on its way
through a communication channel. A repeater regenerates the received signal
and re-transmits it to its destination.
A Gateway is a device, which is used to connect different types of networks and

perform the necessary translation so that the connected networks can
communicate properly.
ROUTER:
A router is more powerful and intelligent than hub or switch. It has advanced capabilities
as it
can analyze the data and decide the data is packed and send it to the other network. It
can
handle huge packets. It can be wired or wireless, both. A wireless router can provides
access
to many devices like smartphones, and connected devices
Network Topologies:
A Topology is an arrangement of physical connections among nodes in a network.
Types of Topologies
Basically, there are five types of topologies and each topology has some advantages and
disadvantages.
Mesh Topology
In mesh topology, each computer is connected with the other computer. There is a point-
to-point link between each dedicated node (workstation).
Star Topology:
In star topology each node is directly connected to a hub/switch. If any node has to send
some information to any other node, it sends the signal to the hub/switch. This signal is
then broadcast (in case of a hub) to all the nodes but is accepted by the intended node(s).
In the case of a switch the signal is sent only to the intended node(s).
Characteristics of Star topology:

It is more efficient topology as compared to bus topology.
It is easy to install
It is easy to diagnose the fault in Star topology.
It is easy to expand depending on the specifications of central hub/switch
Failure of hub/switch leads to failure of entire network
It requires more cable length as compared to bus topology.
Bus Topology:
In bus topology all the nodes are connected to a main cable called backbone.If any node
has to send some information to any other node, it sends the signal to the backbone. The
signal travels through the entire length of the backbone and is received by the node for
which it is intended. A small device called terminator is attached at each end of the
backbone. When the signal reaches the end of backbone, it is absorbed by the terminator
and the backbone gets free to carry another signal. This prevents the reflection of signal
back on the cable and hence eliminates the chances of signal interference.
Characteristics of Bus topology:
⮚ It is easy to install.
⮚ It requires less cable length and hence it is cost effective.
⮚ Failure of a node does not affect the network.
⮚ In case of cable (backbone) or terminator fault, the entire network breaks
⮚ down.
⮚ Fault diagnosis is difficult.
⮚ At a time only one node can transmit data.
Characteristics of Bus topology:

⮚ It is easy to install.
⮚ It requires less cable length and hence it is cost effective.
⮚ Failure of a node does not affect the network.
⮚ In case of cable (backbone) or terminator fault, the entire network breaks
⮚ down.
⮚ Fault diagnosis is difficult.
⮚ At a time only one node can transmit data.
Tree Topology:
Tree topology is a combination of bus and star topologies. It is used to combine multiple
star topology networks. All the stars are connected together like a bus.
This bus-star hybrid approach supports future expandability of the network
Characteristics of Tree topology:

It offers easy way of network expansion
Even if one network (star) fails, the other networks remain connected and
working.
The Internet
The Internet is the global network of computing devices
including desktop, laptop, servers, tablets, mobile
phones, other handheld devices
Applications of Internet
Following are some of the broad areas or services
provided through Internet:
• The World Wide Web (WWW)
• Electronic mail (Email)
• Chat
• Voice Over Internet Protocol (VoIP)
WWW (World Wide Web)/Web :

World Wide Web is a collection of webpages found on network of computers called
internet. Our web browser uses the internet to access the web. The World Wide Web is a
way of exchanging information between computers on the internet through a huge
collection of multimedia documents and elements. World Wide Web was created by
Timothy Berners Lee in 1989.
WWW is a set of programs and protocols that allows the user to create and display
multimedia web pages and is linked to the internet.
URL is Uniform Resource Locator and provides the location and mechanism (protocol) to
access the resource. Examples of URI identifying resources using location (i.e., URL) are:
https://www.mhrd.gov.
in, http://www.ncert.nic.in.
URL is sometimes also called a web address.
In the above URL, http is the protocol name, it can be https, http, FTP, Telnet, etc. www is
a subdomain. ncert.nic.in is the domain name.
Electronic Mail (Email)

Email is the short form of electronic mail. It is one of the ways of sending and receiving
message(s) using the Internet. An email can be sent anytime to any number of recipients
at anywhere. The message can be either text entered directly onto the email application
or an
attached file (text, image audio, video, etc.) stored on a secondary storage.
Some of the popular email service providers are Google (gmail), Yahoo (yahoo mail),
Microsoft (outlook), etc.
Chat
Chatting or Instant Messaging (IM) over the Internet means communicating to people at
different geographic locations in real time through text message(s). It is a forum where
multiple people connect to each other, to discuss their common interests.
Applications such as WhatsApp, Slack, Skype, Yahoo

Messenger, Google Talk, Facebook Messenger, Google
Hangout, etc., are examples of instant messengers.
VoIP
Voice over Internet Protocol or VoIP, allows us to have voice call (telephone service) over
the Internet, i.e., the voice transmission over a computer network rather than through the
regular telephone network.
VoIP works on the simple principle of converting the analogue voice signals into digital
and then transmitting them over the broadband line.
IP Address: Every machine in a network has another unique identifying number, called its
IP Address. An IP address is a group of four bytes (or 32 bits) each of which can be a
number from 0 to 255. A typical IP address looks like this:
59.177.134.72
Website : A website (usually referred to as a site in short) is a collection of web pages

related through hyperlinks, and saved on a web server. A visitor navigates from one page
to another by clicking on hyperlinks. To access a website, one has to type the address of
the website (URL) in the address bar of a browser, and press enter. The home page of the
website will be displayed.
Web Page :
A web page (also referred to as a page) is a document on the WWW that is viewed in a
web browser. Basic structure of a web page is created using HTML (HyperText
Markup Language) and CSS (Cascaded Style Sheet). A web page is usually a part of a
website and may contain information in different forms, such as:
● text in the form of paragraphs, lists, tables, etc.
● images
● audio
● video
● software application
● other interactive contents
The first page of the website is called a home page.
Static and Dynamic Web Pages

A web page can be static or dynamic.
A static webpage is one whose content always remains static, i.e., does not change for
person to person. When a web server receives a request (from browser) for a static web
page, it just locates the page on its storage media and sends it to the browser of the
client.
Static web pages are generally written in HTML, JavaScript and/or CSS and have the
extension .htm or .html.
A dynamic web page is one in which the content of the web page can be different for
different users. The difference in content may be because of different choices made by
the user. When a request for a dynamic web page is made to the web server, it does not
simply retrieve the page and send. Before sending the requested web page, the server
may perform some additional processes like getting information from the
database, updating date and time, updating weather information, etc. The content of such
pages changes frequently. They are more complex and thus take more time to load than
static web pages.
Dynamic web pages can be created using various languages such as JavaScript, PHP,
ASP.NET, Python, Java, Ruby, etc. These are complex to construct and design, as the
code
Web Server :
Web server is used to store and deliver the contents of a website to clients such as a
browser that request it. A web server can be software or hardware. The web browser from
the client computer sends a request (HTTP request) for a page containing the desired
data or service. The web server then accepts, interprets, searches and responds (HTTP
response) to the request made by the web browser. The requested web page is then
displayed in the browser of the client.
Hosting of a Website :
Web hosting is a service that allows us to put a website or a web page onto the Internet,
and make it a part of the World Wide Web. Once a website is created using a hardware
server, we need to connect it to the Internet so that users across the globe can access.
Browser : A browser is a software application that helps us to view the web page(s).
Some of the commonly used web browsers are Google Chrome, Internet Explorer, Mozilla
Firefox, Opera, etc. A web browser essentially displays the HTML documents which may
include text, images, audio, video and hyperlinks that help to navigate from one web page
to another.
Add-Ons and Plug-ins

A plug-in is a complete program or may be a third-party software. For example, Flash and
Java are plug-ins. A Flash player is required to play a video in the browser. A plug-in is a
software that is installed on the host computer and can be used by the browser for
multiple functionalities and can even be used by other applications as well.
An add-on is not a complete program and so is used to add only a particular functionality
to the browser. An add-on is also referred to as extension in some browsers. Adding the
functionality of a sound and graphics card is an example of an add-on.
Cookies :
A cookie is a text file, containing a string of information, which is transferred by the
website to the browser when we browse it. This string of information gets stored in the
form of a text file in the browser. The information stored is retransmitted to the server to
recognise the user, by identifying pages that were visited, choices that were made while
browsing various menu(s) on a particular website. It helps in customising the information
that will be displayed.
MULTIPLE CHOICE QUESTIONS
1)To prevent unauthorized access to and / or from the network, a system known as
____________, can be implemented by hardware and / or software
a. Antivirus
b. Firewall
c. Software
d. Hardware
2) URL stands for

a. Uniform Run Line
b. Uniform Resource Line
c. Uniform Resource Location
d. Uniform Resource Locator
3) Geometric arrangement of devices on the network is called

(a) topology (b) protocols (c) media (d) LAN
4) Which is the name of the network topology in which there are bi-directional links
between each possible node?
(a) Ring (b) Mesh (c) Tree (d) None of these
5) In specific, if systems use separate protocols, which one of the following devices is
used to link two dissimilar systems?
(a) Repeater (b) Gateway (c) Bridge (d) Hub
6) Network device that sends the data over optimizing paths through connected loop is :
(a) gateway (b) hub (c) router (d) bridge
7) The device that can operate in place of a hub is a:

A. Switch B. Bridge C. Router D. Gateway
8) Computer Network:
A. Is a collection of hardware components and computers?
B. Is interconnected by communication channels
C. Allows sharing of resources and information
D. All of the above
9) Which of the following is not the Networking Devices?
A. Gateways B. Linux C. Routers D. Firewalls
10) _____________ is a collection of many separate networks

A. A MAN B. An internet C. A LAN D. None of the above
11) NIC Stands for –

A.Network identity card
B.Network interface code.
C.National interface card
D.Network interface card
12) Which of the following allows user to view a webpage?
A. Operating System B. Website C. Interpreter D. Internet Browser
13) Television cable network is an example of:

a. LAN b. WAN c. MAN d. Internet
Answers :
1 a 8 d
2 d 9 b
3 a 10 b
4 b 11 c
5 b 12 d
6 c 13 b
7 a 14
Case Based MCQs:

Beauty lines fashion incorporation is a fashion company with design unit and market unit
at Bangalore 135m away from each other. The company recently connected their LANs
using Ethernet cable to share the stock related information. But after joining their LAN’s
they are not able to show the information due to loss of signal in between.
(i) Which device out of the following should you suggest to be installed for a smooth
communication?
(a) Modem (b) Repeater (c) UPS (d) None of these
(ii) Which network is suitable to connect computers across different cities?
(a) WAN (b) MAN (c) PAN (d) LAN
(iii) The company wants to increase their bandwidth and speed for communication at any
cost. Which of the following cable(s) is/are suitable for that?
(a) Coaxial Cable (b) Optical Fibre (c) Both (a) and (b) (d) None of these
(iv) What will be the best possible connectivity out of the following? You will suggest to
connect the new set up of offices in Bangalore with its London based office.
(a) Satellite Link (b) Infrared (c) Ethernet (d) None of these
(v) Which of the following device will be suggested by you to connect each computer in
each of the buildings?
(a) Switch (b) Modem (c) Gateway (d) None of these
ASSERTION AND REASONING based questions. Mark the correct choice as

i. Both A and R are true and R is the correct explanation for A
ii. Both A and R are true and R is not the correct explanation for A
iii. A is True but R is False
iv. A is false but R is True
Assertion (A): - Internet cookies are text files that contain small pieces of data, like a username,
password and user’s preferences while surfing the internet.
Reasoning (R):- To make browsing the Internet faster & easier, its required to store certain information
on the server’s computer.
ONE MARK QUESTIONS :

Q- 1) Rushil thought "www" and "Internet" are synonyms ie., they meant same and can be
used interchangeably. But the teacher said that they are not same. Help him to
understand the meaning of both the terms with the help of a suitable example of each.
Ans : World Wide Web is a collection of webpages found on network of computers
called internet. Our web browser uses the internet to access the web. The World Wide
Web is a way of exchanging information between computers on the internet through a
huge collection of multimedia documents and elements
The internet, on the other hand, is a connection between computers and countless other
devices that form a huge network of systems.
Q-2 ) I :
• am a key access point.
• acts as a “gate” between an organization’s network and the outside
world of the Internet.
• serves as the entry and exit point of a network.
• all data coming in or going out of a network must first pass through
me in order to use routing paths.
Who am I?
Ans :Gateway
Q- 3) Name any two popular web servers.
Ans:
i.Apache Web Server
ii. Microsoft Internet Information Server (Microsoft IIS)
Q-4) What are Cookies? How can we disable Cookies? 1

Ans : Cookies are small text files,created on a user’s computer. It contain small pieces of
data - like a username, password and user’s browsing history as well as preferences that
may help to improve user’s web browsing experience. Cookies can be disabled using
browser settings.
Q- 5) What is the function of a Gateway? 1

Ans : A gateway is a network node used in telecommunications that connects two
networks with different transmission protocols together. Gateways serve as an entry and
exit point for a network as all data must pass through or communicate with the gateway
prior to being routed.
Give examples of any two plug-ins.
● Ans :
● Adobe Acrobat.
● Adobe Flash.
● Java
Q-6) Give one advantage and disadvantage each of Bus and Star topology 1
Ans. Bus topology is a type of network to pology in which all devices in the network are
connected by one central network cable or coaxial cable.
Advantage: Since there is a single common data path connecting all the nodes, the bus
topology uses a very short cable length which considerably reduces the installation cost.
Disadvantage: Fault detection and isolation are difficult. This is because control of the
network is not centralized in any particular node. If a node is faulty on the bus, detection of
fault may have to be performed at many points on the network. The faulty node has then
to be rectified at that connection point.
Q- 7) I :
● am a small text file
● created on a user’s computer
● contain small pieces of data — like a username, password and user’s browsing
history as well as preferences
● may help to improve user’s web browsing experience.
● Who am I?
Ans : Cookies
Q-8) Name any two chat software.
Ans. MSN Messenger, Yahoo Messenger
Q- 9) What is difference between star topology and bus topology of network?
Аnswer: In star topology, nodes are connected to server individually whereas in bus
topology all nodes are connected to server along a single length of cable.
Q-10) Aman, a freelance web site developer, has been assigned a task to design few web
pages for a book shop. Help Aman in deciding out of static web page and dynamic web
page, what kind of web pages should be designed by clearly differentiating between static
and dynamic web pages on at least two points.
Q- 11) Differentiation between static and dynamic web pages:
Static Web page Dynamic Web page

Content of this type of webpage cannot Content of this type of webpage can be
be changed at run time. changed at run time.
No interaction with server’s database is Interaction with server’s database is
possible in case of static web pages. possible in case of dynamic web pages.
Q-12) What is the importance of URL in networking?

Ans : URL stands for Uniform Resource Locator. Each page that is created for Web
browsing is assigned a URL that effectively serves as the page’s worldwide name or
address. URL’s have three parts: the protocol, the DNS name of the machine on which
the page is located and a local name uniquely indicating the specific page(generally the
filename).
Q-13) Rohan, a class XI student, has just started understanding the basics of internet
and web technologies. He is a bit confused in between the term “World Wide
Web” and “Internet”. Help him in understanding both the terms with the help of
suitable examples of each
Ans : World Wide Web is a set of programs, standards and protocols that allows the
multimedia and hypertext files to be created, displayed and linked on the Internet.
e.g. www.microsoft.com, www.amazon.com, etc.
Internet is a computer-based worldwide communications network, which is
composed of large number of smaller interconnected networks.
e.g. Web, E-mails, Social media, etc.
While Internet is a collection of computers or networking devices connected
together; WWW is a collection of documents, linked via special links called
hyperlinks. WWW forms a large part of Internet but is not the Internet.
Q- 14) Muskan, a beginner in IT field has just started learning web technologies. Help her
in understanding the difference between website and web pages with the help of a
suitable general example of each.
Ans :
Web site Web Page

Web site is a collection of web It is part of website that includes
pages
information and content and is
displayed on the web with a displayed
client-like
on the browser to user or visitor.
browser.
It is a combination of web pages Information is usually written in
created using HTML and CSS. HTML language.
Q-15 ) Ruhani wants to edit some privacy settings of her browser. How can she
accomplish her task?
On your PC or Android phone or tablet, open the browser.
1. To the right of the address bar, tap More Settings.
2. Tap Privacy and security.
Q-16 )Priyanka, a beginner in IT field has just started learning web technologies. Help her
in understanding the difference between website and web pages with the help of a
suitable general example of each.
Ans : The difference between a website and a web page is that a website is a collection of
different web pages containing information on a particular topic. A web page is an
individual page of a big website usually containing more specific information. If we
compare a website with a book, then a webpage can be compared with a single page of
that book.
Q- 17) Navya has just created a website for her company and now need to host it. Briefly
discuss the role of a web server in hosting a website.
Ans :
Role of web server in hosting a website:
A web server is the main centralized computer system that hosts and runs the websites. It
has a computer program that distributes web pages as they are requisitioned. The basic
role of the web server is to store, process and deliver the web pages to the users as and
when required.
TWO MARKS QUESTIONS :

Q-18 ) Pulkit is surfing a website named www.trendzonline.co.in. He is confused about the
different parts of a domain name. Help him understand.
Ans. A domain name is the name of a website associated with the physical IP (Internet
Protocol) address on the internet. It consists of a top-level and second-level domain. For
example, in the domain name trendzonline.co.in:
in is the primary domain name
co is the sub-domain of in
trendzonline is the domain name.
Q-19) Parul has created a video and uploaded it on her website but she is facing a
problem in opening it on some browsers. She has heard of add-ons and plug-ins but
doesn’t know the difference between them. Help her in understanding these terms.
Add-ons and plug-ins are the tools that help to extend and modify the functionality of the
browser.
Ans : Add-ons: Add-ons are tools that get integrated into our browsers. They are similar
to regular apps or programs, but only run when the browser runs. Add-ons can allow the
viewing of certain types of web content.
Plug-in: A plug-in is a piece of software that acts as an add-on to a web browser and
gives the browser additional functionality. Plug-ins can allow a web browser to display
additional content it was not originally designed to display.
Q- 20) Sonakshi has just started learning computer networking. She is confused about the
difference between IP address and MAC address. Help her understand the difference.
IP Address MAC Address
IP stands for Internet Protocol address It stands for Media Access Control
address.
It is a logical address of a computer that It is a physical address of a computer
that
consists of 4-digit hexadecimal numbers
consists of 12-digit hexadecimal
which uniquely identify a computer
numbers
connected to a network.
assigned to the Network Interface Card
for
use as a network address in
communications
within a network segment.
It is assigned by Internet Service It is assigned by the manufacturer.

Provider.
Example of IP address: Example of MAC address:

198.20.2.18 12:CD:3E:56:5F:C3
Q-21 ) Tilak wants to set up a network in his office that deals with different products and
services. He wants to connect all the computers using bus topology. But he is confused
about the advantages and disadvantages of this topology. Help him understand this
topology. 2
Ans. Bus topology is a type of network to pology in which all devices in the network are
connected by one central network cable or coaxial cable.
Advantage: Since there is a single common data path connecting all the nodes, the bus
topology uses a very short cable length which considerably reduces the installation cost.
Disadvantage: Fault detection and isolation are difficult. This is because control of the
network is not centralized in any particular node. If a node is faulty on the bus, detection of
fault may have to be performed at many points on the network. The faulty node has then
to be rectified at that connection point.
Q- 22) Define Voice over Internet Protocol (VoIP). Also, explain its advantages.
Ans. VoIP is an IP telephony term for a set of facilities used to manage the delivery of
voice information over Internet. It enables a user to make cheap telephone calls over a
broadband Internet connection, instead of using a regular telephone service.
A major advantage of VoIP is that avoids the tolls charged by ordinary telephone service.
A user can make a call locally or
in other parts of US or Canada, or anywhere else in the world, eliminating long distance
fees by using a VoIP service.
The concept of VoIP is used in wireless LAN networks and sometimes referred to as
WVoIP, VoFI, VoWi-Fi and Wi-Fi VoIP.
Advantages of VoIP
(i) The biggest single advantage of VoIP has over standard telephone systems is low cost.
(ii) Using services such as true VoIP, subscribers can call one another at no cost to other
party.
(iii) Routing phone calls over existing data networks eliminate the need for separate voice
and data networks.
(iv) The ability to transmit more than one telephone call over a single broadband
connection.
(v) VoIP consists advance telephone features, e.g. call routing, screen POP and IVR.
CASE STUDY BASED QUESTIONS :

Q-23) Tracker Communication International (TCI) is an online corporate training provider
company for IT-related courses. The company is setting up their new campus in Kolkata.
You, as a network expert, have to study the physical locations of various blocks and the
number of computers to be installed. In the planning phase, provide the best possible
answer for the queries (a) to (d) raised by them.
Block-to-Block distance (in Mtrs.):
From To Distance
Administrative Block Finance Block 60
Administrative Block Faculty Recording Block 120
Finance Block Faculty Recording Block 70
Expected Computers to be installed in each block:
Block No. of computers

Administrative Block 30
Finance Block 20
Faculty Recording Block 80
(a) Suggest the most appropriate block where RCI should plan to install the server.
(b) Suggest the most appropriate block-to-block cable layout to connect all three blocks
for efficient communication.
(c) Which type of network out of the following is formed by connecting the computers of
these three blocks?
(i) LAN (ii) MAN (iii) WAN
(d) Which wireless channel out of the following should be opted by RCI to connect to
students from all over the world?
(i) Infrared (ii) Microwave (iii) Satellite
Ans. (a) Faculty recording block (due to maximum number of computers)
(b)
(c) (i) LAN
(d) (iii) Satellite
Q-24) Wipro Methodologies is planning to expand their network in India starting with three
cities in India to build infrastructure for research and development of their chemical
products. The company has planned to setup their main office in Pondicherry at three
different locations and have named their offices as Back Office, Research Lab and
Development Unit. The company has one more research office namely
Corporate Unit in Mumbai. A rough layout of the same is as follows:
Approximate distance between these offices is as follows:

From To Distance
Research Lab Back Office 110 m
Research Lab Development Unit 16m
Research Lab Corporate Unit 1800m
Back Office Development Unit 13m
In continuation of the above, the company experts have planned to install the following
number of computers in each of these offices.
block No. of computers
Research Lab 158
Development Unit 90
Corporate Unit 51
Back Office 79
(i) Suggest the kind of network required (out of LAN, MAN, WAN) for connection each of
the following office unit.
(a) Research Lab and Back Office
(b) Research Lab and Development Unit
(ii)Which of the following devices will you suggest for connecting all the computers with
each of their office units?
(a) Switch/Hub
(b) Modem
(c) Telephone
(iii) Suggest a cable/wiring layout for connecting the company’s local office units located in
Pondicherry. Also, suggest an effective method/technology for connecting the company’s
office located in Mumbai.
Ans :
(i) (a) The type of network between the Research Lab and the Back Office is LAN (Local
Area Network).
(b) The type of network between Research Lab and Development Unit is MAN
(Metropolitan Area Network).
(ii) (a) The suitable device for connecting all the computers within each of their office units
is switch/hub.
(iii) The cable/wiring layout for connection is as follows:
Q- 25) University of Correspondence in Allahabad is setting up a network between its

different wings. There are 4 wings named Science (S), Journalism (J), Arts (A) and Home
Science (H).
Distance between various wings:
Wing A to Wing S 100 m
Wing A to Wing J 200 m
Wing A to Wing H 400 m
Wing S to Wing J 300 m
Wing S to Wing H 100 m
Wing J to Wing H 450 m
Number of Computers:
Wing A 150
Wing S 10
Wing J 5
Wing H 50
(a) Suggest the most suitable Topology for networking the computers of all wings.
(b) Name the wing where the Server is to be installed. Justify your answer.
(c) Suggest the placement of Hub/Switch in the network.
(d) Mention how economic technology will provide internet accessibility to all wings.
Ans :
(a) Star Topology can be used to network the computers of all wings.
(b) The Server should be installed in Wing A as Wing A has maximum number of
computers and installing the server in this wing will help reduce the network traffic.
(c) Hub/Switch will be required in all the wings.
(d) The economic way to provide internet accessibility to all the wings is to use the proxy
server at Wing A and connect to the internet through an Ethernet cable using routers and
switch.
Q-26) Great Studies University is setting up its academic schools at Sunder Nagar and
planning to set up a network. The university has 3 academic schools and one
administration centre as shown in the diagram below:
Centre-to-centre distance between various buildings:
Law School to Business School 60 m

Law School to Technology School 90 m
Law School to Admin Centre 115 m
Business School to Technology School 40 m
Business School to Admin Centre 45 m
Technology School to Admin Centre 25 m
Number of computers in each of the Schools/Centre:

Law School 25
Technology School 50
Admin Centre 125
Business School 35
(a) Suggest the most suitable place (i.e., schools/centre) to install the server of this
university with a suitable reason.
(b) Suggest an ideal layout for connecting these schools/centre for a wired connectivity.
(c) Which device will you suggest to be placed/installed in each of these schools/centre to
efficiently connect all the computers within these schools/centre?
(d) The university is planning to connect its admission office in the closest big city, which
is more than 350 km from the university. Which type of network out of LAN, MAN or WAN
will be formed? Justify your answer.
Answers :
(a) Admin Centre because it has the maximum number of computers, or Business
School because it is closest to all other centres (minimum cable length required).
(b) BUS topology is the most suitable cable layout.
(c) Switch
(d) WAN is the preferred network for this purpose because 350 km is more than the range
of LAN and MAN.
Q-27 ) Sony has set up its Branch at Srinagar for its office and web-based activities. It has
four Zones of buildings as shown in the diagram:
Branch-to-branch distance is:

Zone X to Zone Z 40 m
Zone Z to Zone Y 60 m
Zone Y to Zone X 135 m
Zone Y to Zone U 70 m
Zone X to Zone U 165 m
Zone Z to Zone U 80 m
Number of Computers:
Zone X 50
Zone Z 130
Zone Y 40
Zone U 15
(a) Suggest the most suitable cable layout or Networking Topology of connections
between the Zones.
(b) Suggest the most suitable place (i.e., Zone) to house the ERP and BI Server of this
organization with a suitable reason, with justification.
(c) Suggest the placement of the following devices with justification:
(i) Repeater (ii) Hub/Switch
(d) Which is the most economic type of cable for the selected topology?
Ans. (a) Bus Topology
(b) The most suitable place (i.e., Zone) to house the ERP and BI Server is Zone Z as it
has the most number of computers; thus, cabling cost will be reduced and most
traffic will be local.
(c) Repeater: As per the suggested layout, separate repeaters need not be installed as
each building/ zone will be having a hub that acts as a repeater.
Hub/switch should be placed in each zone.
(d) An economic type of cable is Ethernet or Coaxial cable as it can connect two
computers at an economic rate though it provides lesser speed than other expensive
methods.
Unsolved
Q-28) ABC International School, Delhi has different wings Administrative Wing (W1),
Primary Wing (W2), Middle Wing(W3) and Secondary Wing(W4) as shown in the
diagram :
The school also has a branch in Mumbai. The school management wants connect all the
wings as well as all the computers of each wing (W1, W2, W3, W4).
Distance between the wings are as follows
W3 TO W1 85m
W1 TO W2 40m
W2 TO W4 25m
W4 TO W3 120m
W3 TO W2 150m
W1 TO W4 170m
Number of computers in each of the wing:

W1 125
W2 40
W3 42
W4 60
Based on the above specifications, answer the following queations:
(i) Suggest the topology and draw the most suitable cable layout for connecting all the
wings of Delhi branch.
(ii) Suggest the kind of network required (out of LAN, MAN, WAN) for connecting
(a) Administrative Wing (W1) with Middle Wing (W3)
(b) Administrative Wing (W1) with the Mumbai branch.
(iii) Suggest the placement of the following devices with justification:
(a) Repeater
(b) Switch/Hub
(iv) Due to pandemic school had to adopt Online classes. Suggest the protocol that is
used for sending the voice signals over internet. Also, give an example of an application
of WWW that helped the teachers to send messages instantly to the students.
Q-29)
Platinum Computer services Ltd. is an international educational organization. It is planning to set up its
India campus at Mumbai with its head office in Delhi. The Mumbai office campus has four main
buildings-ADMIN, ACCOUNTS, EXAMINATION and RESULT.
You as a network expert have to suggest the best network related solutions for their problems raised in
(i) to (v), keeping in mind the distances between the buildings and other given parameters.
.
Shortest distances between various buildings:
ADMIN TO ACCOUNTS 55m
90m
ADMIN TO EXAMINATION
ADMIN TO RESULT 50m
55m
ACCOUNTS TO EXAMINATION
ACCOUNTS TO RESULT 50m
EXAMINATION TO RESULT 45m
DELHI Head Office to MUMBAI 2150m
campus
Number of computers installed at various buildings are as follows:

ADMIN 110
ACCOUNTS 75
EXAMINATION 40
RESULT 12
DELHI HEAD 20
OFFICE
(i) Suggest the most appropriate location of the server inside the MUMBAI campus (out of the four
buildings) to get the best connectivity for maximum number of computers. Justify your answer.
(ii) Suggest and draw cable layout to efficiently connect various buildings within the MUMBAI
campus for a wired connectivity.
(iii) Which networking device will you suggest to be procured by the company to interconnect all the
computers of various buildings of MUMBAI campus?
(iv) Company is planning to get its website designed which will allow students to see their results after
registering themselves on its server. Out of the static or dynamic, which type of website will you
suggest?
(v) Which of the following will you suggest to establish the online face to face communication between
the people in the ADMIN office of Mumbai campus and Delhi head office?
a) Cable TV
b) Email
c) Video conferencing
d) Text chat
Societal Impacts
Digital footprint:
Digital footprints are recorded with each and every online activity we perform—whether it
is interactions on social media, chatting and connecting with friends on social media sites, online
shopping, locations through Facebook check-ins, etc.
Net and communication etiquettes,
Netiquette is a code of good behaviour while working on the internet. It includes several
aspects of the internet, such as social media, email, online chat, web forums, website
comments, multiplayer gaming and other types of online communication.
Data Protection:
Data protection secures sensitive data—from databases to big data, cloud, file system and other
crucial applications.
This can be achieved by taking proper and careful measures while working online in the
following ways:
• Through Data Encryption.
• Not sharing private information such as passwords, credit card credentials, etc.
• Not over sharing on social networking sites using public domain.
• Adopting complete security mechanism for protection against possible threats.
• Avoiding opening phishing emails.
• Being careful about using Wi-Fi connections.
• Being alert to impersonators.
Intellectual property rights (IPR)
Intellectual property refers to creations of the intellect (hence, the name); inventions,
literary and artistic works, symbols, names, images, and designs used in commerce are part of it.
Intellectual property is usually divided into two branches, namely

1. Industrial property:which includes inventions (patents), trademarks, industrial
designs, commercial names, designations and geographic indications (location-specific
brands), etc.copyright which protects literary and artistic works.
2. Copyright :which includes literary and artistic works such as novels, poems and plays,
films, musical works, artistic works such as drawings, paintings, photographs,
sculptures and architectural designs. Copyright is a legal concept, enacted by most
governments, giving the creator of original work exclusive rights to it, usually for a
limited period.
Plagiarism
Plagiarism is copying someone else’s work and then passing it off as one’s own. It is
morally wrong because it is an act of stealing.Plagiarism can be classified as
accidental/unintentional or deliberate/intentional.
Licensing and copyright:

Software Licensing is the legal right to run or the privilege given to you by a company to
access their application (or program).
Copyright is a type of intellectual property protection and licensing is a kind of risk control
measure that can be applied to control copyright and less exposure
Free and open source software (FOSS)
An OSS (open source software) refers to freedom to use, share and/or modify the source
code and allow copyrights to other users.
Free and open source software (FOSS) allows using, copying, studying and modifying the
software, and the source code to be openly shared so that people are encouraged to voluntarily
improve the design of the software.
Cybercrime and Cyber laws
Cybercrime is defined as a crime in which a computer is the object of the crime (hacking, phishing,
spamming) or is used as a tool to commit an offence (child pornography, hate crimes).
Cybercriminals may use computer technology to access personal information, business
trade secrets or use the internet for exploitative or malicious purposes.
Cyber law is the part of the legal system that deals with cyberspace, internet and legal
issues. It covers a broad area, like freedom of expression, access to and utilization of internet, and
online security or online privacy. It is commonly known as the law of the web.
Hacking
Hacking is the process of gaining unauthorized access to a computing device or a group of
computer systems. This is done through cracking of passwords and codes which gives access to the
systems.
The difference between a hacker and a cracker is that while a cracker breaks the security of
computer systems, a hacker likes to explore computer systems and master them
Phishing
Phishing is an attempt to acquire sensitive information such as usernames, passwords and
credit card details (and sometimes, indirectly, money) by masquerading as a trustworthy entity in
an electronic communication
Cyber bullying
Cyber bullying is the attack upon an individual or group through the use of electronic
means such as instant messaging, social media, email and other forms of online communication
with the intent to abuse, intimidate or overpower.
Following acts are considered as cyber bullying
: • Posting any kind of humiliating content about the victim.
• Hacking the victim’s account.
• Sending or posting vulgar messages online.
• Threatening to commit acts of violence.
• Stalking by means of calls, messages, etc.
• Threats of child pornography
Different Types of Cyber Bullying The various forms of cyber bullying are explained as:
• Doxing – Publishing revealing personal information about an individual online, for the purpose of
defaming, humiliating or harassing the victim.
• Harassment – Posting threatening, hurtful or intimidating messages online, or sending them
directly to someone, with the intention of harming that person.
• Impersonation – Creating fake accounts or gaining access to a person’s real social media
accounts and posting things to damage the victim’s reputation.
• Cyberstalking – Tracking and monitoring a person’s online activity and using the internet to
stalk or harass an individual.
Overview of Indian IT Act.
Information Technology Act, 2000 The Information Technology Act, 2000 (also known as
ITA-2000, or the IT Act) is an Act of the Indian Parliament (No. 21 of 2000) notified on 17
October, 2000. It is the primary law in India dealing with cybercrime and electronic commerce.
The original Act contained 94 Sections, divided into 13 Chapters and 4 Schedules. The laws
apply to the whole of India. Persons of other nationalities can also be indicted under the law if the
crime involves a computer or network located in India.
E-waste: hazards and management.
Whenever an electronic device covers up its working life, or becomes non-usable due to
technological advancements or becomes non-functional, it is not used anymore and comes under
the category of e-waste or electronic waste.
All electronic waste is made up of deadly chemicals such as lead, cadmium, beryllium,
mercury and brominated flame retardants. Disposing of gadgets and devices improperly increases
the chances of these dangerous chemicals contaminating the soil, polluting the air and leaching into
water bodies.
E-waste Hazards On Environment
• Acidification of soil
• Air pollution
• Pollution of groundwater
Landfills with lead and heavy metals On Human Health
• Lung cancer
• DNA damage
• Asthmatic bronchitis
• Chronic brain damage
• Damage to the heart, liver and spleen
Here are some eco-friendly waste disposal techniques that you can use to dispose of
electronic waste locally:
 Give your Electronic Waste to a Certified E-Waste Recycler
 Sell off your Outdated Technology
 Donating your Outdated Technology
 Visit Civic Institutions
 Give Back to your Electronic Companies or Leave at Drop-off Points
 Safeguard both the Environment and your Sensitive Information
Awareness about health concerns related to the usage of technology.
Various physical and psychological disorders may crop up due to prolonged and continuous
use of technology
Physical Problems:
1. Repetitive Strain Injury: The pain exists even when resting and as a result it becomes
very difficult to accomplish even easy and ordinary tasks.
2. Carpal Tunnel Syndrome: This is an illness caused by injuries that occur due to force
on the median nerve found in the wrist. Its symptoms include tingling in hands and fingers, a
feeling of lethargy, sudden pain in wrists and arms, and sometimes even in shoulders, neck and in
the body.
3. Computer Vision Syndrome: Experts believe that people blink their eyes more
frequently while using computers than they do otherwise and that this can cause various eye and
vision-related problems.
4. Radiation: Computer screens produce radiations of various types.
These radiations can cause headaches and inattentiveness.
5. Sleeping disorders and decrease in productivity
6. Loss of attention and stress Psychological Disorders:
 Fear of technology
 Computer anxiety
 Internet addiction
• Ego surfing: An illness of regularly searching for one’s own name on the
web and checking what information is available on the net.
• Infornography: The word, derived from pornography and information,
describes the state of “trying to soothe hunger for information on the net.”
• Blog streaking: A desire to spread information online that shouldn’t be
known to everybody.
• YouTube-Narcissism: Constantly uploading one’s own videos in order to
introduce and make themselves known to others.
• Google-Stalking: Trying to get information about all of one’s relatives or
acquaintances on the web.
• Photo lurking: Looking at the photo albums of others’ on the net.
• Wikipediholism: Contributing to the internet encyclopaedia, Wikipedia,
sending someone’s own writings, and revising the present texts
FILL IN THE BLANKS.
(a) Any information about you or created by you that exists in digital form is referred to
as ................. .
(b) Stealing someone’s intellectual work and representing it as your own is known as ................. .
(c) Creative creations of mind such as patents, trademark and copyright are .................property.
(d) Software which usually limit the functionality after a trial period are known as ................. .
(e) Online .................is a theft of personal information in order to commit fraud.
(f) The practice of attempting to acquire sensitive information from individuals over the internet by
means of deception is called ................. .
Answers: (a) digital property (b) plagiarism (c) intellectual (d) shareware (e) identity theft (f)
phishing
MULTIPLE CHOICE QUESTIONS (MCQS)
(a) Using someone else’s Twitter handle to post something will be termed as:
(i) Fraud (ii) Identity theft (iii) Online stealing (iv) Violation
(b) Intellectual Property Rights protect the use of information and ideas that are of:
(i) Ethical Value (ii) Moral Value (iii) Social Value (iv) Commercial Value
(c) Credit card fraud may include:
(i) Stealing of credit card (ii) Unauthorized and illegal use of credit card
(iii) Stealing information (iv) Phishing
(d) The term “Intellectual Property Rights” covers:
(i) Copyrights (ii) Trademarks (iii) Patents (iv) All of these
(e) Which of the following is a/an open-source software?
(i) Microsoft Windows (ii) Adobe Photoshop (iii) MySQL (iv) MS Powerpoint
(f) Online personal account, personal websites are examples of:
(i) Digital Wallet (ii) Digital Property (iii) Digital Certificate (iv) Digital Signature
(g) Unsolicited commercial email is known as ........................ .
(i) Spam (ii) Malware (iii) Virus (iv) Spyware
Answers: (a) (ii) (b) (iv) (c) (ii) (d) (iv) (e) (iii) (f) (ii) (g) (i)
SHORT ANSWER QUESTIONS
1. What do you understand by ‘Privacy of data’?

Ans. The ethical and legal rights that individuals have with regard to control over the
discussions and use of their personal information is known as privacy of data.
2. If someone hacks your website, who would you complain to?
Ans. The complaint has to be lodged to the police under the IT Act.
3. What is the importance of cyber law?
Ans. In internet, the communication technology uses the means of transferring textual
messages, pictures and much more. Each time there may be a number of threats on either
the sender’s or the receiver’s side which create a bridge between networking
communication. To sort out these problems, the Internet Security Council took a number of
precautions. These predefined rules are called cyber laws or law of internet.
4. Write two applications of cyber law.
Ans. Cyber law encompasses a wide variety of political and legal issues related to the
internet and other communication technologies, including intellectual property, privacy,
freedom of expression and jurisdiction.
5. Name the crimes for which cyber laws are enforced strictly in India.
Ans. These are: (a) cyber crimes, (b) electronic and digital signatures, (c) intellectual
property, and (d) data protection and privacy.
6. Explain phishing.
Ans. Phishing is the fraudulent attempt to obtain sensitive information such as usernames,
passwords and credit card details (and money), often for malicious reasons, by disguising as a
trustworthy entity in an electronic communication. Phishing is typically carried out by email
spoofing or instant messaging, and it often directs users to enter personal information at a fake
website, the look and feel of which is identical to the legitimate one and the only difference is
the URL of the website in question.
7. What do you mean by cyber security?

Ans. Cyber security is the combination of best processes and practices to ensure the security of
networks, computers, programs, data and information from attack, damage or unauthorized
access.
8. (a) Write down names of any three social networking sites.

Ans. Facebook, Twitter, LinkedIn, Google+ (any three).
(b) What are the merits of social networking?
Ans. • Lowest cost form of marketing
• Huge potential audience and the possibility of messages going viral
• Offers a closer connection with your clients
• Source of instant feedback
(c) What are the demerits of social networking?
Ans. • Unreliable information
• Lack of control
• Can be addictive
(d) What is cyber trolling?
Ans. Internet trolls hide behind their computer screens and actively go out of their
way to cause trouble on the internet.
(e) Write down the medium/ways of trolling.
Ans. YouTube video comments, blog comments, forums, email, FB, Twitter,
Instagram, social networking sites and anonymous ways of networking.
(f) What is cyber stalking?
Ans. Cyber stalking is defined as the unlawful act of harassing a person or collecting an
individual’s private information using electronic network.
9. Describe some common forms of student plagiarism.

Ans. According to ‘The Reality and Solution of College Plagiarism’ created by the Health
Informatics Department of the University of Illinois, Chicago, mainly there are 10 forms of
plagiarism that the students commit:
1. Submitting someone else’s work as their own.
2. Taking passages from their own previous work without adding citations.
3. Rewriting someone else’s work without properly citing sources.
4. Using quotations without citing the source.
5. Interweaving various sources together in the work without citations.
6. Citing some passages, and not all, that should be cited.
7. Melding together cited and uncited sections of the work.
8. Providing proper citations without changing too much the structure and language of the
borrowed ideas.
9. Citing the source inaccurately.
10. Relying too much on others’ works and failing to bring original ideas into the text.
10. Describe measures to recycle your e-waste safely.

Ans. The following measures can be adopted to recycle the e-waste safely:
1. Use a Certified E-waste Recycler
2. Visit Civic Institutions:
3. Explore Retail Options:
11. How can we block web beacons?

Ans. (a) The best defence against web beacons is to prevent pictures from downloading
until you
have had a chance to review the message.
(b) Beware of malware disguised as holiday greetings in email.
(c) Approach links in email, on social networking sites, or in IMs with caution.
KENDRIYA VIDYALAYA SANGATHAN
RAIPUR REGION
BLUE PRINT BASED ON CBSE SAMPLE PAPER
INFORMATICS PRACTICES (065) – 2022-23
Class XII
TOPICS 1 Marks 2 Marks 3 Marks 4 Marks 5 Marks Total
MCQ (1-16) Very Short Short Questions. Long
Assertion/Reaso answer answer with internal answer
ning (17-18) questions with questions options questions/
internal options . Case
study-
based
questions.
Data Handling 4(4) 3(6) 2(6) 1(4) 1(5) (25)

using Pandas
and Data
Visualization
Database 6(6) 2(4) 2(6) 1(4) 1(5) (25)
Query using
SQL
Introduction to 3(3) 1(2) - - 1(5) (10)
computer
Networks
Societal 5(5) 1(2) 1(3) - (10)
Impacts
18(18) 7(14) 5(15) 2(8) 3(15) (70)
Total
*. Marks are given inside the bracket and number of questions outside the bracket.
Note: Question paper will be prepared following the General Instructions given below.
General Instructions:
1. This question paper contains five sections, Section A to E.

2. All questions are compulsory.
3. Section A have 18 questions carrying 01 mark each.
4. Section B has 07 Very Short Answer type questions carrying 02 marks each.
5. Section C has 05 Short Answer type questions carrying 03 marks each.
6. Section D has 03 Long Answer type questions carrying 05 marks each.
7. Section E has 02 questions carrying 04 marks each. One internal choice is given in Q35
against part c only.
8. All programming questions are to be answered using Python Language only
===================*===============*=============*===========
SAMPLE QUESTION PAPER SET-1
CLASS XII
INFORMATICS PRACTICES (065)
TIME: 3 HOURS M.M.70
4. Section B has 07 Very Short Answer type questions carrying 02 marks
each.
7. Section E has 02 questions carrying 04 marks each. One internal choice
is given in Q35 against part c only.
SECTION A
1. Which among the following network topologies has the highest transmission 1
speed ?
i. LAN
ii. WAN
iii. MAN
iv. Both LAN and WAN have equal transmission speeds.
2. The practice of taking someone’s work or ideas and passing them off as one’s 1
own is known as _____.
i. Identity Theft
ii. Spamming
iii. Hacking
iv. Plagiarism
3. Which of the following constitute e-waste? 1
i. Discarded computers
ii. Damaged printers
iii. Useless CDs
iv. All of the above
4. Which is the subset of SQL commands used to manipulate Oracle Database 1
Structures, including tables?
i. Data Definition Language(DDL)

ii. Data Manipulation Language(DML)
iii. Both of above
iv. None
5. The SQL statement: 1
SELECT SUBSTR(‘abcdefghij’, INSTR(‘123321234,’2’,3,2),2 FROM

DUAL;
Prints
i. gh
ii. 23
iii. bc
iv. ab
6. Samyra’s continuous online classes and increasing use of computer made his 1
mother worried about his health. Which of the following point should not be
suggested her mother to take care of ?
i. Sit in right posture
ii. Take breaks and do some physical activity
iii. Keep the gadget above your height
iv. Periodically rotate and watering eyes
7. Which of following is incorrect SQL? 1
i. Select max(marks) from student;

ii. Select sum(marks) from student;
iii. Select max(marks1,marks2) from student;
iv. Select sum(marks1,marks2) from student;
8. Which one of the following set of operations is a valid set of aggregate functions 1
in SQL?
i. COUNT,MAX,AVG,SUM
ii. MAX,AVG,SUM,SELECT
iii. UNION,COUNT,MIN,DESC
iv. AVG,MIN,MAX,ASC
9. Which MySQL aggregate functions is used to retrieve minimum value? 1
i. MAX( )
ii. MIN( )
iii. AVG( )
iv. MINIMUM( )
10. To display first five rows of a series object ‘S’, you may write: 1
i. S.head()
ii. S.Tail(5)
iii. S.Head(5)
iv. S.tail()
11. Which of the following statement is correct for importing pandas in Python? 1
i. import pandas
ii. import pandas as pd
iii. import pandas as pds
iv. All of the above
12. Which of the following statement for creating dataframe is valid? 1
i. df=pd.dataframe(dict1)
ii. df=pd.Dataframe(dict1)
iii. df=pd.dataFrame(dict1)
iv. df=pd.DataFrame(dict1)
13. Which of the following statements is true for network hub? 1

i. A hub connects different stations in a private network.
ii. It sends data packets to the intended recipient.
iii. It contains two ports: one for input and other for output.
iv. It works at the network layer.
14. In SQL, returns the current date and time? 1
i. Date ()
ii. Time ()
iii. Current ()
iv. Now ()
15. Nitish has invented a new theory in computer science. He wants to protect it 1
legally against unauthorized use. He should take its:
i. Copyright
ii. Patent
iii. Trademark
iv. None
16. Who is known by the name of crackers in the context of computer security. 1
i. Black Hat Hackers
ii. White Hat Hackers
iii. Elite Hackers
iv. Script Kiddie
Q17 and 18 are ASSERTION AND REASONING based questions. Mark the correct choice as
i. Both (A) and (R) are true and (R) is the correct explanation for (A)
ii. Both (A) and (R) are true and (R) is not the correct explanation for (A)
iii. (A) is True but (R) is False
iv. (A) is false but (R) is True
17. Assertion (A): - The internet is a collection of interconnected computer 1
networks, linked by transmission medium such as copper wires, fiber-
optic cables, wireless connections etc.
Reasoning (R):- World Wide Web connection of interconnected documents.
18. Assertion (A):pandas is an open source Python library which offers high 1
performance , easy-to-use data structure and data analysis tools.
Reasoning (R): - Developers and professionals are using the pandas library in
data science and machine learning.
SECTION B
19. Difference between website and webpage. 2
OR
Explain Bus and Star topology.
20. Adesh, a database administrator needs to display house wise total number of 2
activities of ‘Ashoka’ and ‘Tagore’ house. He is encountering an error while
executing the following query:
SELECT HOUSE, COUNT (ACTIVITIES) FROM STUDENT ORDER

BY DESC NAME;
Help her in identifying the reason of the error and write the correct query by
suggesting the possible correction (s).
21. What is the purpose of Group By clause in SQL? Explain with the help of 2
suitable example.
22. Write a program to create a series from dictionary that stores classes 2
(6,7,8,9,10) as keys and number of students as values.
23. Mention any four steps to recycling and recovery under e-waste management. 2
OR
Write any four steps towards awareness about health concerns related to the
usage of technology.
24. What will be the output of the following code: 2
import pandas ad pd
import numpy as num
arr=num.array([31,47,121])
S1=pd.Series(arr, index=(1,11,111))
print(S1[111])
25. Write code statements for a dataframe df for the following – 2
(a) Delete an existing column from it.

(b) Delete rows from 3 to 6 from it
SECTION C
26. Write outputs for SQL queries (i) to (iii) which are based on the given table 3
STATIONARY:
TABLE: STATIONARY
INO INAME CITY QTY DOP
I01 PENCIL RAIPUR 80 2022-09-22
I02 PEN BILASPUR 120 2022-09-23
I03 PAPER DURG 500 2022-12-04
I04 ERASER BHILAI 50 2022-10-10
I05 SHARPNER JANJGIR 60 2022-10-20
i. SELECT POWER(QTY,2)) FROM STATIONARY WHERE

QTY<90;
ii. SELECT CITY FROM STATIONARY WHERE
DAY(DOP)=04;
iii. SELECT LENGTH (LEFT(INAME,3)) FROM STATIONARY
WHERE QTY=80;
27. Create the following DataFrame Sales containing year wise sales figures for five sales 3
persons in INR. Use the years as column labels, and sales person names as row labels.
2014 2015 2016 2017

Geeta 100 12000 20000 50000
Babita 150 18000 50000 60000
Rita 200 22000 70000 70000
Ankita 30000 30000 100000 80000
Samita 40000 45000 125000 90000
28. Use the DataFrame created in Question 27 above to do the following: 3

(i) Display the row labels of Sales.
(ii) Display the first two columns of Sales.
(iii) Delete the data for the year 2014 from the DataFrame Sales
29. 3
i. Explain following terms.
(a) Phishing (b) Denial-of-service (c ) Identity theft
OR
What do you mean by a hacker? Mention their types (any two).
30. Based on table EMPLOYEE given here, write suitable SQL queries for the 3
following:
Emp No Name Design Gender City Salary

1 Anushka Manager M Raipur 89430
2 Vandana HR M Durg 76440
3 Vibha Sales F Agra 65470
4 Dhrishti HR F BBSR 75492
5 Bhoomi Manager M Delhi 87360
6 Aditi Sales F Dehradun68256
7 Monika Engineer F Mysore 83324
8 Priyanshi Engineer M Mysore 84429
i. Display average salary gender wise.
ii. Display number of employee working in this company Design wise.
iii. Display maximum and minimum salary of the employee.
OR
Explain Group by clause a n d h a v i n g c o m m a n d in detail with the help of
suitable example.
SECTION D
31. Write suitable SQL query for the following: 5
i. Display ‘VIDYA’ from string ‘KENDRIYA VIDYALAYA’.
ii. Display the position of occurrence of string ‘VANSH’ in the string
‘SURYAVANSH’.
iii. Find reminder of 17 devided by 3.
iv. Display the month name from ’01-April-2022’.
v. Remove all the expected trailing spaces from a column name of the table
‘STUDENT’.
OR
Explain the following SQL functions using suitable examples.
i. ROUND()
ii. LCASE()
iii. NOW()
iv. COUNT()
v. LEFT()
32. Ravya Industries has set up its new center at Kaka Nagar for its office and web 5
based activities. The company compound has 4 buildings as shown in the
diagram below:
Raj Building Fazz
Building
Jazz
Harsh
Building
Building
Center to center distances between various building is as follows :
Harsh Building to Raj Building 50m
Raj Building to Fazz Building 60m
Fazz Building to Jazz Building 25m
Jazz Building to Harsh Building 170m
Harsh Building to Fazz Building 125m
Raj Building to Jazz Building 90m
Number of computers in each of the buildings is as follows :

Harsh Building 15
Raj Building 150
Fazz Building 15
Jazz Building 25
(i) Suggest a cable layout of connections between the buildings.

(ii) Suggest the most suitable place (i.e. building) to house the server of this
organisation with a suitable reason.
(iii) Suggest the placement of the following devices with justification:
(a) Internet Connecting Device/Modem (b) Switch
(iv) The organisation is planning to link its sale counter situated in various
parts of the same city, which type of network out of LAN, MAN or
WAN will be formed? Justify your answer.
(v) Ravya Industries is required to enable videos and animations to be played on
the web browser . Which browser tool /service can be used for the same?
33. Mr.Sharma is trying to write a code to plot line graph shown in fig-1. Help Mr. Sharma 5
to fill in the blanks of the code and get the desired output.
(i) Write python code to design above plot.

(ii) Give suitable python statement to save this chart.
(iii) Write command to make line in black color.
OR
Draw 4 horizontal bars with a beautiful green color. Give suitable
title also and save this file.
SECTION E
34. Consider the following table Teacher. Write SQL commands for the following 1+1+2
Statements.
No. Name Age Department Dateofjoin Salary Sex
1. Jugal 34 Computer 10/01/97 12000 M
2. Sharmil 31 History 24/03/98 20000 F
a
3. Sandee 32 Maths 12/12/96 30000 M
p
4. Sangeet 35 History 01/07/99 40000 F
a
5. Rakesh 42 Maths 05/09/97 25000 M
6. Shyam 50 History 27/06/98 30000 M
(i) Write a query to count number of department.

(ii) Write a query to display maximum age.
(iii) Write a query to display average salary in which Date of join is before
12/07/96.
OR (Option for part iii only)
Write a query to display sum of all teachers’ salary in which Date of
join is before 12/07/96.
35. Write the output of the following statement1 to statement 4:
import pandas as pd
list1=[1,2,3,4,5,6,7,8]
list2=['swimming','tt','skating','kho kho', 'bb', 'chess', 'football',"cricket"]
school=pd.Series(list1,index=list2)
school.name=("little") 1+1+2
print (school*2) #statement 1
print (school.tail(3)) # statement 2
print (school["tt"]) # statement 3
print (school[2:4]) # statement 4

Mr. Ankit is working in an organisation as data analyst. He uses Python Pandas . He
got a dataset of the passengers for the year 2010 to 2012 for January, March and
December. His manager wants certain information from him, but he is facing some
problems. Help him by answering few questions given below:
import pandas as ____________ #Statement 1 data={"Year":

[2010,2010,2012,2010,2012],"Month":["Jan","Mar","Jan","Dec","Dec"] ,"Passengers":
[25,50,35,55,65]}
df=pd.____________________(data) #Statement 2
print(df)
(i) Help him to fill the blank of statement1.

(ii) Help him to fill the blank of statement2.
(iii) He wants to print the details of “Jan” month along with the number of
passengers.
(iv) Mr. Ankit wants to change the index of the Data Frame with [‘Air India’,
‘Indigo’, ‘Spicejet’, ‘Jet’, ‘Emirates’]. Write the correct statement to change
the index.
MARKING SCHEME SQP CLASS XII SET-1
1. i. LAN 1
1 mark for correct answer
2. iv. Plagiarism 1
3. iv. All of the above 1
4. i. Data Definition Language(DDL) 1
5. i. gh 1
6. iii. Keep the gadget above your height 1
7. iv. Select sum(marks1,marks2) from student; 1
8. i. COUNT,MAX,AVG,SUM 1
9. ii. MIN() 1
10. i. S.head() 1
12. iv. df=pd.DataFrame(dict1) 1

13. i. A hub connects different stations in a private network. 1

14. iv. Now() 1
15. i. Copyright 1
16. i. Black Hat Hackers 1

17. ii. Both (A) and (R) are true and (R) is not the correct explanation for 1
(A)
18. i. Both A and R are true and R is the correct explanation for A 1
19. Website: A website is a collection of publicly accessible, interlinked 2
Web pages that share a single domain name.
Web Page: A Web Page is a part of a website and is commonly written
in HTML. It can be accessed through a web browser.
1 mark for correct explanation of each term

Or
Bus topology: is a network setup where each computer and network

device is connected to a single cable or backbone.
Star topology: is a network topology in which each network component
is physically connected to a central node
20. The problem with the given SQL query is order by apply on column followed 2
by ascending/descending.
To correct the error, DESC should be used after column name.
Corrected Query:
SELECT HOUSE, COUNT (ACTIVITIES) FROM STUDENT
ORDER BY NAME DESC;
1 Mark for error identification

1 Mark for writing correct query
21. Group By clause: is used to group rows by one or more columns. 2

The following SQL statement displays all the customer’s names in alphabetical
order:
SELECT Cname FROM Customers GROUP BY City;
1 mark for correct
purpose 1 mark for
correct example
22. import pandas as pd 2
D={6:140, 7:120, 8:100, 9:80, 10:60}
S1=pd.Series(D)
print(S1)
½ mark for each correct line

23. i. Dismantling 2
ii. Separation metal and plastic
iii. Refurbishment and reuse
iv. Recovery of valuable materials etc.
½ mark for each correct example.
Or
i. Isolation
ii. Depression and anxiety
iii. Eyestrain
iv. Sleep problem etc.
½ mark for each correct answer
24. 121 2
2 mark for each correct output
25. i. del df['column_name'] 2

ii. df.drop(range(3,7))
1 mark for each correct answer
26. i. 6400 3
2500
3600
ii. DURG
iii. 3

D={2014:[100,150,200,30000,40000],
2015:[12000,18000,22000,30000,45000],
2016:[20000,50000,70000,10000,125000],
2017:[50000,60000,70000,80000,90000]}
L=[‘Geeta’,’Babita’,’Rita’,’Ankita’,’Samita’]
Sales=pd.DataFrame(D,index=L)
print(Sales)
1 mark for each correct python statement
28. i. print(Sales.index) 3
ii. print(Sales[[2014,2015]]) OR print(Sales[Sales.columns[0:2]])
iii. Sales.drop(column=2014)
1 mark for each correct statement

29. (a) Phishing : Phishing refers to an activity carried out by email or SMS 3
or phone call with the demand of sensitive data like credit card details,
banking passwords or OTP and can be resulted in identity theft or
financial loss.
(b) Denial-of-service: It is also known as a DOS attack. It refers to an
activity to making the computer or devices inaccessible for the intended
users’. Most of the users targeted in this attack are employees, members
of the forum or community, account holders.
(c ) Identity theft – Identifying someone’s personal information like
name, number, credit card number, or other sensitive information without
their consent and commit fraud or crime.
OR
Hacker : A hacker is an expert or elite programmer who is trying to
break the system security and gain unauthorized access. They gain
unauthorized access to data, steal some information from your computer,
manipulate the digital data, capture user’s personal or financial
information or install malware on your systems.
(i) Black hat : A black hat hacker is someone who maliciously
searches for and exploits vulnerabilities in computer systems or
networks.
(ii) White hat : A white hat hacker is a security specialist hired to
find vulnerabilities in software, hardware and networks that black
hats may find and target.
1 mark for correct definition
½ mark for each (any) correct name
½ mark for each correct explanation
30. i. select avg(salary) from Employee group by gender; 3
ii. select count(*) from Employee group by Design;
iii. select max(salary),min(salary) from Employee;
1 mark for each correct query
OR
GROUP BY clause is used in a SELECT statement in combination with
aggregate functions to group the result based on distinct values in a column.
HAVING clause is used to apply a filter on the result of GROUP BY
based on the specified condition.
For example:
To display total number of male and female employees from the table
Employee, we need to first group records based on the gender then we should
count records with the help of count() function. To filter the record gender
wise, HAVING clause is used.
Considering the following table EMPLOYEE:
14 Aditi Sales F Dehradun 68256
SQL query for the above-mentioned task is as follows:
select gender,count(gender) from employy group by gender
Output:
Gender Count(Gender)
M 4
F 4
1 mark for each correct

answer of group by and
having clause
1 mark for correct example
31. i. Select substring(‘KENDRIYA VIDYALAYA’,10,5); 5
ii. Select instr(‘SURYAVANSH’,’VANSH’);
iii. Select mod(17,3);
iv. Select monthname(‘01-April-2022’);
v. Select rtrim(name) from STUDENT;
OR
i. ROUND(): The ROUND() function rounds a number to a

specified number of decimal places.
SELECT ROUND(345.156, 1);
O/P: 345.2
ii. LCASE():The LCASE() function converts a string to lower-

case.
SELECT LCASE(UserName)
O/P:username
iii. NOW():The NOW() function returns the current date and

time.
Select NOW()
O/P: 2022-09-20 08-10-12
iv. COUNT():The COUNT() function returns the number of

records returned by a select query.
Select count(name) from students
O/P: 10 (if name column have 10 records)
v. LEFT(): The LEFT() function extracts a number of characters

from a string (starting from left).
SELECT LEFT(CustomerName, 5)
O/P: Custo

½ mark for each correct example
32. 5
(i)
Raj Building Fazz
Building
Harsh Jazz
Building Building
(ii) The most suitable place/ block to house the server of this organisation
would be Raj Building, as this block contains the maximum number of
computers, thus decreasing the cabling cost for most of the computers
as well as increasing the efficiency of the maximum computers in the
network.
(iii) (a) Raj Building since it contains largest number of computers.
(b) In the suggested layout, a hub/switch each would be needed in all
the buildings, to interconnect the group of cables from the different
computers in each block.
(iv) The type of network that shall be formed to link the sale counters
situated in various parts of the same city would be a MAN, because
MAN (Metropolitan Area Networks) are the networks that link computer
facilities within a city.
(iv) Add ons
1 Mark for each correct answer
33. (i) 5
import matplotlib.pyplot as plt
x=[1,2,3]
y=[2,4,1]
plt.plot(x,y, color=’g’)
plt.xlabel('x-axis')
plt.ylabel('y-axis')
plt.title('IMy first graph!')
plt.show()
(ii) plt.savefig("aa.jpg")
(iii)plt.plot(x,y,color=’k
’)
2 marks for correct answer (i)
1 marks for correct answer (ii)
1 marks for correct answer (iii)
OR

import numpy as np
x = np.array(["A", "B", "C", "D"])
y = np.array([3, 8, 1, 10])
plt.bar(x, y, color = "g")
plt.show()
plt.title('IMy first graph!')

plt.savefig("aa.jpg")
2 mark for correct code

1 mark to use correct title function
1 mark for correct statement to save the file
34. i. SELECT COUNT(distinct department) FROM TEACHER; 1+1+2
ii. SELECT MAX(Age) FROM TEACHER;
iii. SELECT AVG(Salary) FROM TEACHER WHERE

Dateofjoin<{12/07/96};
OR
SELECT SUM(Salary) FROM TEACHER WHERE
Dateofjoin<{12/07/96};
2 marks for correct query

35. Output: 1+1+2
Statement1 :
swimming 2
tt 4
skating 6
khokho 8
bb 10
chess 12
football 14
cricket 16
statement2:
chess 6
football 7
cricket 8
statement3:
2
Statement4:
Skating 3
Khokho 4
OR
(i) pd
(ii) DataFrame
(iii) Df[[‘Month’,’Passengers’]]]df[‘Month’]==’Jan’]
(iv) Df.index=[“Air India”, “Indigo”,”Spicejet”,”Jet”,”Emirate”]
OR
print(df.Tot_students-df.First_Runnerup)
2 marks for correct Python statement

SAMPLE QUESTION PAPER SET-2
CLASS-XII
7. Section E has 02 questions carrying 04 marks each. One internal choice
is given in Q35 against part c only.
8. All programming questions are to be answered using Python Language only.
SECTION A
1. Which of the following is used in computer network? 1
v. Hub
vi. Repeater
vii. Gateway
viii. All the above
2. As soon we get any good news from any source in social media. 1
v. We should forward to many people so that they can be benefited.
vi. We should never forward to anyone.
vii. We should confirm the news from any reliable source before forwarding.
viii. We should forward to our family and friends only.
3. Which of the following are Net Etiquette? 1
v. Be Ethical
vi. Be Respectful
vii. Be Responsible
viii. All of the above
4. Write SQL command to display the name of current month : 1
v. SELECT MONTH(CURDATE())
vi. SELECT NAMEOFMONTH(CURDATE())
vii. SELECT MONTHNAME(CURDATE())
5. Consider the string ‘infopractice’.Write SQL command to display first five 1
characters:
v. Select left (‘infopractice’,5)

vi. Select substr(‘infopractice’,1,5);
vii. Select ltrim(‘infopractice’,5);
viii. Both (a) and (b)
6. ________ are the records and traces that we left behind as we use internet. 1
v. Digital Footprints
vi. Data Protection
vii. Plagiarism
viii. Digital Data
7. Which of following function returns an integer that indicates the position of the 1
first occurrence of the sub-string within the string?
v. INSTR()
vi. RTRIM()
vii. LENGTH()
viii. TRIM
8. Which of the following is the correct syntax of LCASE() function ? 1

v. LCASE(row_name)
vi. LCE(column_name)
vii. LCASE(column_name)
viii. All the above
9. What will be the order of the data being sorted after the execution of given 1
query:
SELECT * FROM STUDENT ORDER BY ROLL_NO;
v. Custom sort
vi. Descending
vii. Ascending
viii. None of the above
10. Which of the following command is used to install pandas? 1
v. pip install pandas
vi. install pandas
vii. pip pandas
viii. None of the above
11. Sameer wants to access ‘Author’ column from ‘Book’ dataframe. Suggest him to 1
correct statement:
v. print(Book.Author)
vi. print(Book[‘Author’])
vii. Both are true
viii. (a) True (b) False
12. What the following statement will display? 1
Df.iloc[2:7:3]
v. Display 2nd to 7th row and first three columns.
vi. Display 3rd to 7th row and first three columns.
vii. Display 2nd to 7th row and first four columns.
viii. Display 3rd to 7th row and first four columns.
13. What is a HUB? 1

v. Software
vi. Computing device
vii. Network device
viii. Calculating device
14. With SQL, how can you return the number of not null record in the Project field 1
of “Students” table?
v. Select count(Project) from Students
vi. Select columns(Project) from Students
vii. Select columns(*) from Students
viii. Select count(*) from Students
15. Online posting of rumours, giving threats online, posting the victim’s personal 1
information, comments aimed to publicly ridicule a victim is termed as_____.
v. Cyber bullying
vi. Cyber crime
vii. Cyber insult
16. What is a Firewall in Computer Network?. 1
v. The physical boundary of Network
vi. An operating system of computer network
vii. A system designed to prevent unauthorized access
viii. A web browsing software
v. Both (A) and (R) are true and (R) is the correct explanation for (A)
vi. Both (A) and (R) are true and (R) is not the correct explanation for (A)
vii. (A) is True but (R) is False
viii. (A) is false but (R) is True
17. Assertion (A): - Digital footprint is the trail of data we leave behind when 1
we visit any website (or use any online application or portal) to fll-in data
or perform any transaction.
Reasoning (R):- While online, all of us need to be aware of how to conduct

ourselves, how best to relate with others and what ethics, morals and values to
maintain.
18. Assertion (A): A Series is a one-dimensional array containing a sequence of 1
values of any data type (int, float, list,string etc.).
Reasoning (R): - Pandas Series can be imagined as a column in a spreadsheet.

SECTION B
19. ii. Shubham wants to play a video in his browser but he is not able to do so. 2
A message on the screen instructs him to install the Adobe Flash Player
plug?in. Help him to add it in his browser.
OR
Name any two mail service providers
20. Ankit has written two query in MySQL, given below: 2
(i) SELECT COUNT (MARKS) FROM STUDENT;
(ii) SELECT COUNT (*) FROM STUDENT;
Showing output of the (i) query is 10 and (ii) query is 08. Ankit is confused
about the above result. Help him to identifying the errors if any. Explain the
suggestive reason for the above output.
21. What is the purpose of Having clause in SQL? Explain with the help of suitable 2
example.
22. Write a program in Python to create the pandas series of all the characters in the 2
name accepted from user.
23. Suggest any four method to avoid the problems occurred during use of electronic 2
gadgets.
OR
Mention any four kind of illness due to heavy use of electronic devices.
24. What will be the output of the following code: 2
import pandas as pd
L1=list(“My name is Ravi Kumar”)
S1=pd.Series(L1)
print(S1[11])
25. 2
Consider the following Series object, S_num
Pen 20
Pencil 15
Gel pen 30
Eraser 10
i) Create the series S-num

ii) Write the command which will display the names
of the stationary items having price >15.
SECTION C
26. Write outputs for SQL queries (i) to (iii) which are based on the given table 3
SCHOOL:
TABLE: SCHOOL
ADN CLASS STREAM
PERCENT DOA
A001 12 SCIENCE 90.32 2022-06-11
A002 11 COMMERCE 89.6 2022-02-19
A003 11 ARTS 78.12 2021-12-04
A004 12 SCIENCE 81.83 2021-10-10
A005 12 ARTS 95.13 2021-10-20
iv.SELECT ROUND(PERCENT,1) FROM SCHOOL WHERE

CLASS=11;
v. SELECT MAX(CLASS) FROM SCHOOL
WHERE PERCENT<90;
vi. SELECT LENGTH(SUBSTR(STREAM,3,4)) FROM SCHOOL
WHERE STREAM= ‘SCIENCE’;
27. Write a Python code to create a DataFrame Result with appropriate column 3
headings from the list given below:
Arnab Ramit Samridh Riya

i
Hindi 90 92 89 81
IP 91 81 91 71
Maths 97 96 88 67
28. Consider the given DataFrame ‘Forest’: 3
State GArea VDF

0 Assam 6789 423.70
1 Delhi 123 8.37
2 Kerala 33825 1225.00
Write suitable Python statements for the following:

i. Add a column called TArea with the following data:
[5432,7896,4400].
ii. Delete the columns having labels ‘VDF’
iii. Rename the as Sub1,Sub2 and Sub3..
29. Write any three important points should be kept in mind to reduce the risk 3
of cybercrime.
OR
What do you mean by the Indian IT Act? Write any three points.
30. Based on table EMPLOYEE given here, write suitable SQL queries for the 3
following:

22 Aditi Sales F NULL 68256
iv. Display total of salary gender wise.
v. Display number of city excluding NULL value ,gender wise.
vi. Display maximum salary of the employee Design wise.
OR
Discuss the significance of ORDER by clause in detail with the help of
suitable example.
SECTION D
31. Write suitable SQL query for the following: 5
i. To display Power of 12.
ii. Display the Length of ‘Vidyanjali’.
iii. Display the date of today.
iv. Display ‘Tej’ from string ‘SaiTeja’.
v. Display total marks obtained by a student in PT1column from
student table.
OR
Explainthe followingSQLfunctions/command usingsuitable examples.
i. trim():
ii. instr():
iii. day():
iv. count(*):
v. group by :
32 Quick learn University is setting up its Academic blocks at Prayag Nagar and
planning to set up a network. The university has 3 academic blocks and one Human
Resource Centre as shown in the diagram below:
Business Technology
Block Block
Law HR
Block Center
Centre to centre distances between various blocks/centre is as follows:
Law Block to Business Block 40 m

Law Block to Technology Block 80 m
Law Block to HR Centre 105 m
Business Block to Technology Block 30 m
Business Block to HR Centre 35 m
Technology Block to HR Centre 15 m
Number of computer in each of the Blocks/Centre is as follows:
Law Block 15
Technology Block 40
HR Centre 115
Business Block 25
(i) Suggest the most suitable place (i.e. Block/Centre) to install the server of
this university with a suitable reason.
(ii) Suggest an ideal layout for connecting these blocks/centre for a wired
connectivity.
(iii) Which device you will suggest to be placed/installed in each of these
blocks/centre to efficiently connect all the computers with in these
blocks/centre.
(iv) The university is planning to connect its admission office in the closest big
city, which is more than 250 km from university, which type of network
out of LAN, MAN, WAN will be formed? Justify your answer.
(v) Suggest a device to be used to filter network traffic to/from

university network to provide network security
33. Create the DataFrame using 2 lists, and in the plot function passed the height and 5
weight columns of the DataFrame. The output is shown in Figure 4.4
OR
Write a python program to plot a line chart based on the given data to depict
the runs scored by India.
Over=[10,20,30,40,50]
Runs=[0,25,50,75,100]
SECTION E
34. 1+1+2
Find the output from (a) to (f) from given Table: Hospital:
TABLE:Hospital
No Name Department DOA Charges Sex
1 Sandeep Surgery 23/02/20 300 M
2 Ravina Orthopedic 20/01/20 200 F
3 Karan Orthopedic 19/02/20 200 M
4 Tarun Surgery 01/01/98 300 M
5 Zubin ENT 12/02/20 250 M
6 Ketaki ENT 24/02/20 300 F
7 Ankita Cardiology 20/02/20 800 F
8 Zareen Gynecology 22/02/20 300 F
9 Kush Cardiology 13/01/20 800 M
10 Shaliya Nuclear Medicine 19/02/20 400 M
( i) Write a query to display names in upper case.
(ii) Write a query to count total number of departments.
(iii) Write a query to find average of charges of female .
Write a query to count year wise total number of cloths purchased.

35. Yukta has created a Data frame “SportsDay” to keep track of the number of First,
Second and Third prizes won by different houses in various events.
House First Second Third

0 Ashoka 5 7 6
1 Shivaji 10 5 4 1+1+2
2 Tagore 8 13 15
3 Raman 12 9 12
4 Subhash 5 11 10
5 Kalam 10 5 3
Write Python commands to do the following:

(i) Display the house names where the numbers of Second Prizes are in the range of
12 to 20.
(ii) Display all the records in the reverse order.
(iii) Display the bottom 3 records.
(iv) Find output for the given statements:
x=df.columns[:1]
print(x)
Write a python code to create DataFrame “vendor” with following data:

Vname item area qty
200 A chair east 30
201 B table west 45
202 C pen south 23
203 E eeraser SW 12
204 F sketch pen NE 100
MARKING SCHEME SQP S ET-
2
CLASS XII
1. iv. All the above 1
2. iii. We should confirm the news from any reliable source before 1
forwarding.
4. iii. SELECT MONTHNAME(CURDATE()) 1
5. iv. Both (a) and (b) 1
6. i. Digital Footprints 1
7. i. INSTR() 1

8. iii. LCASE(column_name) 1

9. iii. Ascending 1
10. i. pip install pandas 1
11. iii. Both are true 1

12. ii. Display 3rd to 7th row and first three columns. 1
13. iii. Network Device 1
14. i. Select count(Project) from Students 1

15. i. Cyber bullying 1
16. iii. A system designed to prevent unauthorized access 1

17. ii. Both (A) and (R) are true and (R) is not the correct explanation for 1
(A)
18. i. Both A and R are true and R is the correct explanation for A 1
19. 2
He must see to the settings/ extension option in the browser & then
allow add ons and plugins from it to activate its working.
Or
Gmail/Outlook/ProtonMai/AOL/Zoho Mail/iCloud Mail/Yahoo!
Mail/GMX etc.
1 mark for each
20. The problem with the given SQL query is that WHERE clause should not be 2
used with Group By clause.
To correct the error, HAVING clause should be used instead of
WHERE. Corrected Query:
SELECT HOUSE, COUNT(*) FROM STUDENT GROUP BY HOUSE
HAVING HOUSE= ‘RED’ OR HOUSE=’YELLOW’;

21. 2
HAVING Clause is used to filter the records from the groups based on the
given condition in the HAVING Clause.
SELECT Age, COUNT(Roll_No) AS No_of_Students

FROM Student GROUP BY Age
HAVING COUNT(Roll_No) > 1
1 mark for correct
purpose 1 mark for
correct example
A=input(“Enter your name:”)
S1=pd.Series(list(A))
print(S1)
½ mark for each correct line

23. 2
v. Should learn how to use technologies without experiencing any
problem.
vi. Should aware about their health-related problems that they have
vii. Should immediately take education on the healthy use of these
technology.
viii. Should take physical exercise regularly etc.

Or
ix. Neck strain

x. Vision Problem
xi. Sleeping disorder
xii. Stress etc.

24. R 2
25. iii. import pandas as pd 2

S_num={[Pen,20],[Pencil,15],[Gel Pen,30],[Eraser,10]}
S=pd.Series(S_num)
print(S)
iv. print(S_num[prince>15])
26. iv. 89.6 3
78.1
v. 12
vi. 4
Result={‘Arban’,:pd.Series([90,91,97],index=[‘Hinhi’,’IP’,”Maths’]),
‘Ramit’,:pd.Series([92,81,96],index=[‘Hinhi’,’IP’,”Maths’]),
‘Samridhi’,:pd.Series([89,91,88],index=[‘Hinhi’,’IP’,”Maths’]),
‘Riya’,:pd.Series([81,71,67],index=[‘Hinhi’,’IP’,”Maths’]),
Df=pd.DataFrame(Result)
print(Df)
1 mark for each correct python statement
28. i. Forest[‘TArea’]=[5432,7896,4400] 3
ii. Forest.drop(‘VDF’,axis=1)
iii. Forest.rename({0:’Sub1’,1:’Sub2’,2:Sub3},axis=’index’)

29.  Use antivirus and keep it updated 3
 Download and use software from authorized sources only
 Use strong passwords for the accounts etc.

OR
 The Indian IT Act refers to providing guidelines and rules to the

user on the processing, storage and transmission of sensitive
information.
 The states of our country have their own cyber cells in the police
station to report cybercrime.
 The IT act provides legal provisions and implications against
cybercrime.
1 mark for correct definition

1 mark for correct justification
½ mark each for any two ways to avoid plagiarism
30. i) select sum(salary) from Employee group by gender; 3
ii) select count(City) from Employee group by gender;
iii) select max(salary) from employee group by design;
OR
ORDER BY clause to display the output table of a query in either ascending or
descending alphabetical order. The ORDER BY clause must be the last clause
that you specify in a query.
For example:
To display in any specific order ,we have to use order by clause.

Considering the following table EMPLOYEE:
25 Bhoomi Manager M Raipur 89430
27 Anushka Manager M Delhi 87360
SQL query for the above-mentioned task is as follows:
select name from employee ORDER BY Name;

Name
Anushka
Bhoomi
Priyanshi
Vandana
1 mark for correct

significance 2 marks for
correct example
31. i. select power(12,2); 5

ii. select length(‘Vidyanjali’);
iii. select date(now());
iv. select substr(‘SaiTeja’,4,3);
v. select sum(PT1) from student;
OR
i. trim():TRIM() function removes leading and trailing

spaces from a string.
SELECT TRIM('#! ' FROM ' #SQL Query! ')
O/P:#! ‘ FROM ‘ #SQL Query!
ii. instr():The INSTR() function returns the position of

the first occurrence of a string in another string.
Select instr(‘SURYAVANSH’,’VANSH’);
O/P : 6
iii. day():Returns only date

select date(now());
O/P: date(now())
2022-09-22
iv. count(*): Counts the all records which are
available(including null value).
Select count(*) from students
O/P: 10 if students table have 10 records
v. group by :The GROUP BY statement groups rows that

vi. have the same values into summary rows
select city, count(*) from customer group by city;
O/P: city count(*)

Raipur 3
Durg 2 etc.

32. (i) HR Centre because it has the most number of computers.
(ii)
30
Business Technology
Block Block
40 15
Law HR
Block Center
(iii) Switch
(iv) WAN because LAN and MAN cannot cover 250 km.
(v) Firewall

33. import matplotlib.pyplot as plt 5
import pandas as pd
height=[121.9,124.5,129.5,134.6,139
.7,147.3,152.4,157.5,162.6
]
weight=[19.7,21.3,23.5,25.9,28.5,32.
1,35.7,39.6,43.2]
df=pd.DataFrame({"height":height,"
weight":weight})
plt.xlabel('Weight in kg')
plt.ylabel('Height in cm')
plt.title('Average weight with respect
to average height')
plt.plot(df.weight,df.height,marker='
*',markersize=10,color='green
',linewidth=2, linestyle='dashdot')
plt.show()
½ mark for import modules
1 mark for create DataFrame
1 mark for proper labels
1 mark for properly define title
1 mark for ploting
½ to show the plot
OR
Over=[10,20,30,40,50]
Runs=[0,25,50,75,100]
plt.plot(Over,Runs)
plt.show()

34. iv. SELECT LOWER(CNAME) FROM CLOTH; 1+1+2
v. SELECT MIN(PRICE) FROM CLOTH;
vi. SELECT COUNT(*) FROM CLOTH GROUP BY SIZE

HAVING SIZE='M';
OR
SELECT YEAR(DOP),COUNT(*) FROM CLOTH GROUP
BY YEAR(DOP);
35. (i) df['Name'][(df['Second']>=12) & (df['Second']<=20)] 1+1+2

(ii) print(df.iloc[::-1])
(iii) df.tail(3)
(iv) Name

OR
import pandas as pd
vend={'vname':['A','B','C','E','F'],
'item':['chair','table','pen','eeraser','sketch pen'],
'area':['east','west','south', 'SW','NE'], 'qty':[30,45,23,12,100]}
vendor=pd.DataFrame(vend,index=[200,201,202,203,204])
print(vendor)
1/2 mark for correct import statement

2 marks to create dictionary with all data
1 mark to create dataframe
½ mark for print statement
SAMPLE QUESTION PAPER SET– 3 (2022-23)

CLASS: XII
SUB: INFORMATICS PRACTICES (Python) (065)
Max Marks: 70 TIME: 03 HOURS
PART A
01 The main computer in any network is called as 1
a. Client
b. Server
c. Hub
d. Switch
02 Sending the email to any cc means 1
a. Sending the mail with a carbon copy
b. Sending the mail without a carbon copy
c. Sending the email to all and hiding the address
d. All of the above
03 Which of the following reduce e-waste? 1
a. purchasing more and more gadgets
b. using them for a short time and then discarded
c. good maintenance
d. all of these
04 Consider the following query : 1
SELECT name FROM stu WHERE subject LIKE ‘_______ Computer Science’;
Which one of the following has to be added into the blank space to select the
subject which has Computer Science as its ending string?
a) $
b) _
c) ||
d) %
05 If column “Marks” contains the data set {25, 35, 25, 35, 38}, what will be the 1
output after the execution of the given query?
SELECT MARKS (DISTINCT) FROM STUDENTS;
a) 25, 35, 25, 35, 38
b) 25, 25, 35, 35
c) 25, 35, 38
d) 25, 25, 35, 35
06 The term FLOSS is associated with: 1
a) Non-open source software
b) proprietary software
c) Open Source software
d) None of these
07 Which of the following sublanguages of SQL is used to query information from 1
the data base and to insert tuples into, delete tuples from, and modify tuples
in the database?
a) DML (Data Manipulation Language)
b) DDL (Data Definition Language)
c) Query
d) Relational Schema
08 In SQL, which command is used to SELECT only one copy of each set of 1
duplicable rows
a) SELECT DISTINCT
b) SELECT UNIQUE
c) SELECT DIFFERENT
d) All of the above
09 An attribute in a relation is foreign key if it is the _________key in any other 1
relation.
a) Candidate
b) Primary
c) Super
d) Sub
10 Pandas mainly used for 1
a) Data Recovery
b) Data Backup
c) Data Visualizations
d) Data Analysis
11 Which of the following statement is not correct for Pandas? 1
a) Pandas is open source built in library
b) Pandas offers high-performance, easy to use data structures
c) Pandas provides tools for backup and recovery
d) Pandas provides tools for data analysis
12 You can create a Python pandas series using? 1
a) sequence
b) ndarray
c) tuple
d) all of the above
13 An application that can be accessed via a web browser, over a network 1
connection is called a ___________.
a. Web-based application
b. Server-based application
c. Both a) and b)
d. None of the above
14 Which of the following queries contains an error? 1
a) Select * from emp where empid = 10003;
b) Select empid from emp where empid=10006;
c) Select empid from emp;
d) Select empid where empid=1009 and lastname=’GUPTA’;
15 With SQL, how can you return the number of not null record in the Project 1
field of “Students” table?
a) SELECT COUNT (Project) FROM Students
b) SELECT COLUMNS (Project) FROM Students
c) SELECT COLUMNS (*) FROM Students
d) SELECT COUNT (*) FROM Students
16 A______ is property of the entire relation, which ensures through its value 1
that each tuple is unique in a relation.
a) Rows
b) Key
c) Attribute
d) fields
17 Assertion (A): Digital footprint is the trail of data we leave behind when we 1
visit any website (or use any online application or portal) to fill-in data or
perform any transaction.
Reason (R): While online, all of us need to be aware of how to conduct
ourselves, how best to relate with others and what ethics, morals and values
to maintain.
18 Assertion (A): pandas is an open source Python library which offers high 1
performance, easy-to-use data
structures and data analysis tools.
Reason (R): Professionals and developers are using the pandas library in data
science and machine learning.
PART B
19 What is the difference between STAR and BUS topologies? 2
OR
Why we use a domain name address in place of IP address of the Server to
access any web site?
20 Anjali writes the following commands with respect to a table employee having 2
fields, empno, name, department, commission.
Command1: Select count (*) from employee;
Command2: Select count(commission) from employee;
She gets the output 4 for the first command but get an output 3 for the second
command. Explain the output with justification.
21 Make difference between DELETE and DROP command. Explain with suitable 2
examples of each.
22 Write a python code to create a dataframe with appropriate headings from 2
the list given below:
['S101', 'Amy', 70], ['S102', 'Bandhi', 69], ['S104', 'Cathy', 75], ['S105',
'Gundaho', 82]
# initialize list of lists
data = [['S101', 'Amy', 70], ['S102', 'Bandhi', 69], ['S104', 'Cathy', 75], ['S105',
'Gundaho', 82]]
# Create the pandas DataFrame
df = pd.DataFrame(data, columns = ['ID', 'Name', 'Marks'])
# printdataframe. print(df )
(½ mark for correct initialization, 1 mark for correct
dataframe and ½ mark for printing dataframe)
23 What do you understand by e-waste management? Explain. 2
OR
Explain Digital Rights Management. How can we protect our content?
24 Predict the output of the following code fragments. 2
x=np.array([1,2,3])
y=np.array([3,2,1])
z=np.concatenate([x,y])
prit(z)
25 Write one python program to find the following from the given dataframe DF: 2
Rollno Name Age Marks
11 Aruna 18 68
12 Mohini 14 47
13 Kiya 13 78
14 Lakshmi 16 87
15 Ravisha 14 60
a) Maximum marks and minimum marks
b) sum of all the marks
PART C
26 A Gift Gallery has different stores in India. Database Administrator Abhay 3
wants to maintain database of their Salesmen in SQL to store the data, He has
decided that
Name of the database: GiftGallery
Name of the table: Salesman
Attributes of the tables: Scode – Numeric, Sname – Character 25, Address -
Character 25, Dojoin - Date, Sales – Numeric and Area – Character 10
Consider the following records in ‘Salesman’ table and answer the given
questions:
i. State the command that will give the output as:

Sname
Shushant
Priya
ii. Which of the following commands will display the details of all sales record
of North Area, regardless of case (whether North / NORTH / north):
iii. Help Priya to display sname and sales of east and west areas
27 Write a Pandas program to create and display a DataFrame from a specified 3
dictionary data which has the index labels.
Sample DataFrame:
exam_data = {'name': ['Anastasia', 'Dima', 'Katherine', 'James', 'Emily',
'Michael', 'Matthew', 'Laura', 'Kevin', 'Jonas'],
'score': [12.5, 9, 16.5, np.nan, 9, 20, 14.5, np.nan, 8, 19],
'attempts': [1, 3, 2, 3, 2, 3, 1, 1, 2, 1],
'qualify': ['yes', 'no', 'yes', 'no', 'no', 'yes', 'yes', 'no', 'no', 'yes']}
labels = ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j']
'attempts': [1, 3, 2, 3, 2, 3, 1, 1, 2, 1],
df = pd.DataFrame(exam_data , index=labels)
print(df)
28 Write the code in pandas to create the following dataframes: 3
df1 df2
mark1 mark2 mark1 mark2
0 10 15 0 30 20
1 40 45 1 20 25
2 15 30 2 20 30
3 40 70 3 50 30
Write the commands to do the following operations on the dataframes
given above:
(i) To add dataframes df1 and df2.
(ii) To subtract df2 from df1
(iii) To rename column mark1 as marks1in both the dataframes df1 and df2.
(iv) To change index label of df1 from 0 to zero and from 1 to one.
29 Rishi has to prepare a project on “Swachh Bharat Shreshth Bharat”. He decides 3
to get information from the Internet. He downloads three web pages
(webpage1, webpage 2, webpage 3) containing information on the given topic.
1. He read a paragraph on from webpage 1 and rephrased it in his own words.
He finally pasted the rephrased paragraph in his project.
2. He downloaded three images of from webpage 2. He made a collage for his
project using these images.
3. He also downloaded an icon from web page 3 and pasted it on the front
page of his project report.
i. Step1 An Example of __________
a. Plagiarism
b. Paraphrasing
c. copyright infringement
d. Intellectual Property right
ii. Step 2 An act of __________
a. plagiarism
b. copyright infringement
c. Intellectual Property right
d. Digital Footprints
iii. Step 3 An act of _________
a. Plagiarism
b. Paraphrasing
OR
Explain the role of online social media campaigns, crowdsourcing and smart
mobs in society.
30 Based on table CLUB given here, write suitable SQL queries for the following: 3
a. To show all information about the swimming coaches in the club.

b. To list names of all coaches with their date of appointment (DATOFAPP) in
descending order.
c. To display a report, showing coachname, pay, age and bonus (15% of pay)
for all the coaches. OR
Explain each of the following with illustrations using a table
(i) Candidate Key (ii) Primary Key (iii) Foreign Key
PART D
31 Write suitable SQL query for the following: 5
i. Display 7 characters extracted from 7th left character onwards from the
string ‘INDIA SHINING’.
ii. Display the position of occurrence of string ‘COME’ in the string ‘WELCOME
WORLD’.
v. Remove all the expected leading and trailing spaces from a column userid of
the table ‘USERS’.
OR

i. UCASE()
ii. TRIM()
iii. MID()
iv. DAYNAME()
v. POWER()
32 KVS consultants are setting up a secured network for their office campus at 5
Gurgaon. They are planning to have connectivity between 3 blocks and the
head office at Mumbai. Answer the questions (a) to (d) after going through the
block positions in the campus and other details, which are given below:
Distances between various buildings:

Block A to Block C 120m
Block A to Block B 55m
Block B to Block C 85m
New Delhi Campus to Head office 2060 Km
Number of computers:
Block A 32
Block B 150
Block C 45
Head office 10
a. Suggest a layout of connections between the blocks
b. Suggest the most suitable place to house the server with justification.
c. Suggest a connection medium to connect Gurgaon campus with head office.
d. Suggest the placement of the following devices with justification:
i)Switch ii) Repeater
e. The organization is planning to provide a high-speed link with its head office
situated in Mumbai using a wired connection. Which of the following cables
will be most suitable for this job?
i) Optical Fiber ii) Co-axial Cable iii) Ethernet Cable
33 Write a Python program to draw a line as shown below using given axis values 5
with suitable label in the x axis, y axis and a title.
OR
What will be the output of the following code:
plt.plot([1,2,3],[4,5,1])
plt.show()
PART E
34 Write SQL queries for (i) to (iv) which are based on the tables. 1+1+2
Table: ACCOUNT
ANO ANAME ADDRESS
101 Nirja Singh Bangalore
102 Rohan Gupta Chennai
103 Ali Reza Hyderabad
104 Rishabh Jain Chennai
105 Simran Kaur Chandigarh
Table: TRANSACT
TRNO ANO AMOUN TYPE DOT

T
T001 101 2500 Withdraw 2017-12-21
T002 103 3000 Deposit 2017-06-01
T003 102 2000 Withdraw 2017-05-12
T004 103 1000 Deposit 2017-10-22
T005 101 12000 Deposit 2017-11-06
(i)To display details of all transactions of TYPE Deposit from Table TRANSACT
(ii)To display the ANO and AMOUNT of all Deposits and Withdrawals done in
the month of October 2017 from table TRANSACT.
(iii)To display the last date of transaction (DOT) from the table TRANSACT for
the Accounts having ANO as 103.
OR (OPTION FOR PART iii ONLY)
To display all ANO, ANAME and DOT of those persons from tables ACCOUNT
and TRANSACT who have done transactions less than or equal to 3000.
35 Suppose a data frame contains information about student having columns 1+1+2
rollno, name, class and section. Write the code for the following:
(i) Add one more column as fee
(ii) Write syntax to transpose data frame.
(iii) Write python code to delete column fee of data frame.
Write the code to append df2 with df1
Marking Scheme SET-3

CLASS: XII SUB: INFORMATICS PRACTICES (Python) (065)
PART A
01 The main computer in any network is called as 1
a. Client
b. Server
c. Hub
d. Switch
Ans b
02 Sending the email to any cc means 1
a. Sending the mail with a carbon copy
b. Sending the mail without a carbon copy
c. Sending the email to all and hiding the address
d. All of the above
Ans : a
03 Which of the following reduce e-waste? 1
a. purchasing more and more gadgets
b. using them for a short time and then discarded
c. good maintenance
d. all of these
Ans c
04 Consider the following query 1
SELECT name FROM stu WHERE subject LIKE ‘_______ Computer Science’;
Which one of the following has to be added into the blank space to select the
subject which has Computer Science as its ending string?
a) $
b) _
c) ||
d) %
ans d
05 If column “Marks” contains the data set {25, 35, 25, 35, 38}, what will be the 1
output after the execution of the given query?
SELECT MARKS (DISTINCT) FROM STUDENTS;
a) 25, 35, 25, 35, 38
b) 25, 25, 35, 35
c) 25, 35, 38
d) 25, 25, 35, 35
Ans C
06 The term FLOSS is associated with: 1
a) Non-open source software
b) proprietary software
c) Open Source software
d) None of these
Ans c
07 Which of the following sublanguages of SQL is used to query information from 1
the data base and to insert tuples into, delete tuples from, and modify tuples
in the database?
a) DML (Data Manipulation Language)
b) DDL (Data Definition Language)
c) Query
d) Relational Schema
Ans A
08 In SQL, which command is used to SELECT only one copy of each set of 1
duplicable rows
a) SELECT DISTINCT
b) SELECT UNIQUE
c) SELECT DIFFERENT
d) All of the above
Ans A
09 An attribute in a relation is foreign key if it is the _________key in any other 1
relation.
a) Candidate
b) Primary
c) Super
d) Sub
Ans b
10 Pandas mainly used for 1
a) Data Recovery
b) Data Backup
c) Data Visualizations
d) Data Analysis
Ans d
11 Which of the following statement is not correct for Pandas? 1

a) Pandas is open source built in library
b) Pandas offers high-performance, easy to use data structures
c) Pandas provides tools for backup and recovery
d) Pandas provides tools for data analysis
Ans c
12 You can create a Python pandas series using? 1
a) sequence
b) ndarray
c) tuple
d) all of the above
Ans d
13 An application that can be accessed via a web browser, over a network 1
connection is called a ___________.
a. Web-based application
b. Server-based application
c. Both a) and b)
d. None of the above
Ans a

Ans d
15 With SQL, how can you return the number of not null record in the Project 1
field of “Students” table?
a) SELECT COUNT (Project) FROM Students

b) SELECT COLUMNS (Project) FROM Students
c) SELECT COLUMNS (*) FROM Students
d) SELECT COUNT (*) FROM Students
Ans A
16 A______ is property of the entire relation, which ensures through its value 1
that each tuple is unique in a relation.
a) Rows b) Key c) Attribute d) fields
Ans b
17 Assertion (A): Digital footprint is the trail of data we leave behind when we 1
visit any website (or use any online application or portal) to fill-in data or
perform any transaction.
Reason (R): While online, all of us need to be aware of how to conduct
ourselves, how best to relate with others and what ethics, morals and values
to maintain.
Ans B
18 Assertion (A): pandas is an open source Python library which offers high 1
performance, easy-to-use data
Structures and data analysis tools.
Reason (R): Professionals and developers are using the pandas library in data
science and machine learning.
Ans A

PART B
19 What is the difference between STAR and BUS topologies? 2
Ans: STAR Topology is topology in which the all the nodes are connected with
central computer. But is Bus topology a single wire runs across the network
and all the nodes are connected to the central bus.
1 mark for correct explanation each topology
OR
Why we use a domain name address in place of IP address of the Server to
access any web site?
Ans: We use Domain name as it is more easy to remember that to remember
the IP Address of the Website.
2 marks for correct reason
20 Anjali writes the following commands with respect to a table employee having 2
fields, empno, name, department, commission.
Command1: Select count (*) from employee;
Command2: Select count(commission) from employee;
She gets the output 4 for the first command but get an output 3 for the second
command. Explain the output with justification.
Ans: The Count (*) function returns the total number of records in the table
while count(<field>) will return the count of non-null values in the given field
and this is the reason for the different results by the given queries above.
The field commission must be containing a NULL value and thus
count(commission) returned the count of non-null values and count (*) return
total number of records (Irrespective of NULL values in the field).
1 Mark for output explanation
1 Mark for Justification
21 Make difference between DELETE and DROP command. Explain with suitable 2
examples of each.
Ans : 1 mark for explanation and 1 marks for examples)
22 Write a python code to create a dataframe with appropriate headings from 2
the list given below:
['S101', 'Amy', 70], ['S102', 'Bandhi', 69], ['S104', 'Cathy', 75], ['S105',
'Gundaho', 82]
Ans:
import pandas as pd
# initialize list of lists
data = [['S101', 'Amy', 70], ['S102', 'Bandhi', 69], ['S104', 'Cathy', 75], ['S105',
'Gundaho', 82]]
# Create the pandas DataFrame
df = pd.DataFrame(data, columns = ['ID', 'Name', 'Marks'])
# printdataframe. print(df )
(½ mark for correct initialization, 1 mark for correct dataframe and ½ mark
for printing dataframe)
23 What do you understand by e-waste management? Explain. 2
Ans: (1 mark for definition and 1 mark for explanation)
OR
Explain Digital Rights Management. How can we protect our content?
Ans: (1 mark for defining and 1 mark for explanation of content protection)
24 Predict the output of the following code fragments. 2
x=np.array([1,2,3])
y=np.array([3,2,1])
z=np.concatenate([x,y])
prit(z)
Ans: [1,2,3,3,2,1]
(2 marks for correct output)
11 Aruna 18 68
12 Mohini 14 47
13 Kiya 13 78
14 Lakshmi 16 87
15 Ravisha 14 60
a) Maximum marks and minimum marks
b) sum of all the marks
Ans:
a. print(“Maximum marks = “ , DF[“Marks”].max())
print(“Minimum marks = “ , DF[“Marks”].min())
b. print(“Sum of marks = “ , DF[“Marks”].sum())
PART C
26 A Gift Gallery has different stores in India. Database Administrator Abhay 3
wants to maintain database of their Salesmen in SQL to store the data, He has
decided that
questions:
i. State the command that will give the output as:

Sname
Shushant
Priya
Ans:
Select Sname from Salesman Where Not Address =” Delhi”;
or
Select Sname from Salesman Where Address NOT IN(“Delhi”);
or
Select Sname from Salesman Where Address! =“Delhi”;
ii. Which of the following commands will display the details of all sales record
of North Area, regardless of case (whether North / NORTH / north):
Ans: Select * from salesman where upper(area)=’NORTH’;
ANS: Select sname, sales from Salesman where area=” East” or area=” West”;

Sample DataFrame:
'attempts': [1, 3, 2, 3, 2, 3, 1, 1, 2, 1],
Ans:
import pandas as pd
import numpy as np

'attempts': [1, 3, 2, 3, 2, 3, 1, 1, 2, 1],
print(df)
1 mark for Import statement
1 mark for creating dictionary
1 mark for Creating DataFrame and output
28 Write the code in pandas to create the following dataframes: 3
df1 df2
mark1 mark2 mark1 mark2
0 10 15 0 30 20
1 40 45 1 20 25
2 15 30 2 20 30
3 40 70 3 50 30
Write the commands to do the following operations on the dataframes
given above:
(i) To add dataframes df1 and df2.
(ii) To subtract df2 from df1
(iii) To rename column mark1 as marks1in both the dataframes df1 and df2.
(iv) To change index label of df1 from 0 to zero and from 1 to one.
Ans:
import numpy as np import pandas as pd
df1 = pd.DataFrame({'mark1':[30,40,15,40], 'mark2':[20,45,30,70]});
df2 = pd.DataFrame({'mark1':[10,20,20,50], 'mark2':[15,25,30,30]});
print(df1) print(df2)
(i) print(df1.add(df2))
(ii) print(df1.subtract(df2))
(iii) df1.rename(columns={'mark1':'marks1'}, inplace=True) print(df1)
(iv) df1.rename(index = {0: "zero", 1:"one"}, inplace = True) print(df1)
(1 mark for creating each dataframe and ½ mark for each correct command)
29 Rishi has to prepare a project on “Swachh Bharat Shreshth Bharat”. He decides 3
to get information from the Internet. He downloads three web pages
(webpage1, webpage 2, webpage 3) containing information on the given topic.
1. He read a paragraph on from webpage 1 and rephrased it in his own words.
He finally pasted the rephrased paragraph in his project.
3. He also downloaded an icon from web page 3 and pasted it on the front
page of his project report.
a. Plagiarism
b. Paraphrasing
Answer:(b) :Paraphrasing
ii. Step 2 An act of __________
a. plagiarism
b. copyright infringement
c. Intellectual Property right
d. Digital Footprints
Answer: (a) Plagiarism
iii. Step 3 An act of _________
a. Plagiarism
b. Paraphrasing
Answer : (c) Copyright Infringement
1 mark for each correct option
OR
Explain the role of online social media campaigns, crowdsourcing and smart
mobs in society.
Ans:
Role of Social Media Campaigns: -
A social media campaign should focus around a singular business goal,
whether it's on Facebook or Instagram. Common goals for social media
campaigns include:
• Getting feedback from users.
• Building email marketing lists
• Increasing website traffic
-> Crowdsourcing is the practice of engaging a ‘crowd’ or group for a common
goal — often innovation, problem solving, or efficiency. It is powered by new
technologies, social media and web 2.0. Crowdsourcing can take place on
many different levels and across various industries.
-> Smart mobs, so named because each person in the
group uses technology to receive information on where to go and what to do.
This ability to stay on top of current events makes smart mobs extremely
effective
(1 mark for one correct role of social media campaign, 1 mark for one correct
role of Crowdsourcing and 1 mark for one correct role of Smart mob)
30 Based on table CLUB given here, write suitable SQL queries for the following: 3
a. To show all information about the swimming coaches in the club.

b. To list names of all coaches with their date of appointment (DATOFAPP) in
descending order.
c. To display a report, showing coachname, pay, age and bonus (15% of pay)
for all the coaches.
Ans:
i. SELECT * FROM CLUB WHERE SPORTS=’SWIMMING’;
ii. SELECT COACHNAME, DATOFAPP FROM CLUB ORDER BY DATOFAPP DESC;
iii. SELECT COACHNAME, PAY, AGE, PAY *0.15 AS BONUS FROM CLUB;
OR
Explain each of the following with illustrations using a table
(i) Candidate Key (ii) Primary Key (iii) Foreign Key
Ans:
(i) Candidate Key: It refers to any column/attribute that can uniquely identify
record in a table.
(ii) Primary key: It refers to designated attribute(s)/column(s) that uniquely
identifies a row/tuple in a table/relation. It is one of the candidate keys.
(iii) Foreign key: is an attribute in a table which is the primary key in linked
table
1 mark for each correct definition
PART D
i. Display 7 characters extracted from 7th left character onwards from the
string ‘INDIA SHINING’.
WORLD’.
Ans:
i. select mid('INDIA SHINING',7,7);
OR
i. UCASE()
ii. TRIM()
iii. MID()
iv. DAYNAME()
v. POWER()
Ans:
1. UCASE(): It converts the string into upper case.
Example:
Output:
WELCOME WORLD
Example:
Output:
Welcome world
Example:
Output:
Come
Example:
Output:
Friday
number.
Example: SELECT POW(6,2);
Output:
36
32 KVS consultants are setting up a secured network for their office campus at 5
Gurgaon. They are planning to have connectivity between 3 blocks and the
head office at Mumbai. Answer the questions (a) to (d) after going through the
block positions in the campus and other details, which are given below:
Distances between various buildings:

Block A to Block C 120m
New Delhi Campus to Head office 2060 Km
Number of computers:
Block A 32
Block B 150
Block C 45
Head office 10
b. Suggest the most suitable place to house the server with justification.
c. Suggest a connection medium to connect Gurgaon campus with head office.
d. Suggest the placement of the following devices with justification:
i)Switch ii) Repeater
e. The organization is planning to provide a high-speed link with its head office
situated in Mumbai using a wired connection. Which of the following cables
will be most suitable for this job?
i) Optical Fiber ii) Co-axial Cable iii) Ethernet Cable
Ans:
a. Suitable layout
b. Block C because of the highest no of computer
c. VPN in Internet or Satellite communication can be use
d. Switch in Block A, B and C. repeater in Block C or Head Office
e. Optical Fiber
33 Write a Python program to draw a line as shown below using given axis values 5
with suitable label in the x axis, y axis and a title and also save the graph in a
file.
Ans:

# x axis values
x = [1,2,3]
# y axis values
y = [2,4,1]
# Plot lines and/or markers to the Axes.
plt.plot(x, y)
# Set the x axis label of the current axis.
plt.xlabel('x - axis')
# Set the y axis label of the current axis.
plt.ylabel('y - axis')
# Set a title
plt.title('Sample graph!')
# Display a figure.
plt.show()
½ mark for each correct statement
Python statement to save the chart:
plt.savefig("aa.jpg")
1 mark for the correct statement
OR
What will be the output of the following code:
plt.plot([1,2,3],[4,5,1])
plt.show()
Ans:
Full marks for correct output

PART E
Table: ACCOUNT
ANO ANAME ADDRESS
Table: TRANSACT

T
T001 101 2500 Withdraw 2017-12-21
T002 103 3000 Deposit 2017-06-01
T003 102 2000 Withdraw 2017-05-12
T004 103 1000 Deposit 2017-10-22
T005 101 12000 Deposit 2017-11-06
(i)To display details of all transactions of TYPE Deposit from Table TRANSACT
(ii)To display the ANO and AMOUNT of all Deposits and Withdrawals done in
the month of October 2017 from table TRANSACT.
(iii)To display the last date of transaction (DOT) from the table TRANSACT for
the Accounts having ANO as 103.
Ans:
(i) SELECT * FROM TRANSACT WHERE TYPE = 'Deposit';
(ii) SELECT ANO,AMOUNT FROM TRANSACT WHERE DOT >= '2017-10-01' AND
DOT <= '2017-10-31';
OR
SELECT ANO,AMOUNT FROM TRANSACT WHERE DOT BETWEEN '2017-10-01'
AND '2017-10-31';
(iii) SELECT MAX(DOT) FROM TRANSACT WHERE ANO = 103;
To display all ANO, ANAME and DOT of those persons from tables ACCOUNT
Ans:
SELECT ACCOUNT.ANO,ANAME,DOT FROM ACCOUNT,TRANSACT WHERE
ACCOUNT.ANO=TRANSACT.ANO AND AMOUNT <=3000; OR
SELECT A.ANO,ANAME,DOT FROM ACCOUNT A,TRANSACT T WHERE
A.ANO=T.ANO AND AMOUNT <=3000;
35 Suppose a data frame contains information about student having columns 1+1+2
rollno, name, class and section. Write the code for the following:
(i) Add one more column as fee
(ii) Write syntax to transpose data frame.
(iii) Write python code to delete column fee of data frame.
Write the code to append df2 with df1
Ans:
I. Df1[‘fee’]=([100,200,300])
II. Df1=Df1.T
III. Df2.pop(‘fee’)
Df2=Df2.append(Df1)
SAMPLE QUESTION PAPER SET– 4 (2022-23)

PART A
01 A network with all client computer and no server is called 1
a. Networking
b. Peer to Peer Computing
c. Client Server Computing
d. Any of them
02 Which of these is not a communication channel? 1

(a) Satellite
(b) Microwave
(c) Radio wave
(d) Wi-Fi
03 What is the use of Bridge in the network? 1
(a) To connect LANs
(b) To separate LANs
(c) To control network speed
(d) All of the above
04 In the given query which keyword has to be inserted? 1
INSERT INTO employee______(1002, “Kausar”, 2000);
a) Table
b) Values
c) Relation
d) Field
05 If column “Salary” contains the data set {1000, 15000, 25000, 10000, 15000}, 1
what will be the output after the execution of the given query?
SELECT SUM(DISTINCT SALARY) FROM EMPLOYEE;
a)75000
b) 25000
c) 10000
d) 50000
06 Open source software is software whose source code is available for modification 1
or enhancement by anyone. Which of the following is an example of open source
software?
a) Adobe Photoshop
b) Microsoft Word
c) Libre Office
d) Skype
07 Which of the following is a SQL aggregate function? 1

a) LEFT
b) AVG
c) JOIN
d) LEN
08 The command used for modifying the records is: 1

a) update
b) add
c) updateall
d) none of the above
09 By default, ORDER BY clause lists the results in _______ order. 1

a) Descending
b) Any
c) Same
d) Ascending
10 The Pandas word has derived from 1
a) Panel Data System
b) Panel Data Structure
c) Python Data Structure
d) Python Data System
11 Which of the following is one of the feature of data structure? 1
a) Specialized way of storing data
b) Specilized way to read or wirte data
c) Reshape data into different forms
d) Support data visualizations
12 Which of the following correct statement for creating empty series? (Assume that 1
pandas library is already imported as pd)
a) ser = pd.Series(NaN)
b) ser = pd.Series(None)
c) ser = pd.Series()
d) ser = pd.Series
13 What are the actions that can be taken to make a password? 1
a. Make a password complex, like a mix case, use numbers and special characters.
b. Never use common name, date of birth and city in the password
c. Don’t share passwords with your friends or relatives.
d. All of the above
15 All aggregate functions except _______ ignore null values in their input 1
collection.
a) Count (attribute)
b) Count (*)
c) Avg
d) Sum
16 Consider following SQL statement. What type of statement is this? 1

CREATE TABLE employee (name VARCHAR, id INTEGER)
a) DML
b) DDL
c) DCL
d) Integrity constraint
17 Assertion (A): An Internet troll is a person who deliberately sows discord on the 1
Internet by starting quarrels or upsetting people.
Reason (R): We can download and use any material available on the Internet.
18 Assertion (A): Data visualization refers to the graphical representation of 1
information and data using visual elements like charts, graphs and maps etc.
Reason (R): To install matplotlib library we can use the command
pip install matplotlib.
PART B
19 What is difference between a website and a webpage? 2
OR
How website is not same as web portal?
20 Gopi Krishna is using a table Employee. It has the following columns: 2
Code, Name, Salary, Deptcode
He wants to display maximum salary departmentwise. He wrote the following
command:
SELECT Deptcode, Max (Salary) FROM Employee;
Rewrite the above query with necessary changes to help him get the desired
output.
21 What is the difference between group by and order by clause of MYSQL? Give an 2
example of each.
22 Write a small python codeto create a dataframewith headings (a and b) from the 2
list given below:
[[1,2],[3,4],[5,6],[7,8]]
23 What is plagiarism? How can you avoid plagiarism while referring to someone’s 2
else’s creation?
OR
What is the common gender and disability issues faced while teaching / using
computers in classrooms?
24 Write the output of following code: 2

import pandas as pd
import numpy as np
data = np.array([54,76,88,99,34])
s1 = pd.Series(data,index=['a','b','c','d','e'])
s2=s1.rename(index={'a':0,'b':1})
print(s2)
11 Aruna 18 68
12 Mohini 14 47
13 Kiya 13 78
14 Lakshmi 16 87
15 Ravisha 14 60
a) Mean and mode of age of the students
b) Count the no of rows present in the dataframe
PART C
26 A Gift Gallery has different stores in India. Database Administrator Abhay wants 3
to maintain database of their Salesmen in SQL to store the data, He has decided
that
questions:
i. The command to display the name of the salesman along with the sales amount
rounded off to one decimal point will be:
ii. Which of the following commands will display the details of all sales record of
North Area, regardless of case (whether North / NORTH / north):
Sample DataFrame:
exam_data = {'name': ['Anastasia', 'Dima', 'Katherine', 'James', 'Emily', 'Michael',
'Matthew', 'Laura', 'Kevin', 'Jonas'],
'attempts': [1, 3, 2, 3, 2, 3, 1, 1, 2, 1],
28 Consider the following dataframe 3
Age Name Score

0 26 Sachin 87
1 25 Dhoni 67
2 25 Virat 89
3 24 Rohit 55
4 31 Shikhar 47
Write Commands for the following:

1. to compute sum of score.
2. to compute mean of Age.
3. to find maximum score.
(1 mark for each correct command)
29 Rishi has to prepare a project on “Swachh Bharat Shreshth Bharat”. He decides to 3
get information from the Internet. He downloads three web pages (webpage1,
webpage 2, webpage 3) containing information on the given topic.
1. He read a paragraph on from webpage 1 and rephrased it in his own words. He
finally pasted the rephrased paragraph in his project.
3. He also downloaded an icon from web page 3 and pasted it on the front page
of his project report.
a. Plagiarism
b. Paraphrasing
ii. A website is a collection of ___________
a. Web Servers
b. Web pages
c. Browsers
d. Hyperlinks
iii. The process of getting web pages, images and files from a web server to local
computer is called
a. FTP
b. Uploading
c. Downloading
d. Remote access
OR
Ms Samtha has many electronics gadgets which are not usable due to outdated
hardware and software. Help her to find any three best ways to dispose the used
electronic gadgets.
30 Based on table GRADUATE given here, write suitable SQL queries for the 3
following:
(a) List the names of those students who have obtained DIV I sorted by NAME.
(b) Display a report, listing NAME, STIPEND, SUBJECT and amount of stipend
received in a year assuming that the STIPEND is paid every month.
(c) To count the number of students who are either PHYSICS or COMPUTER SC
graduates.
OR
(i) There is column salary in table employee. The following two statements are
giving different outputs. What may be the possible reasons?
Select count(*) from employee select count(salary) from employee
(ii) Mr. Sanghi created two tables with City as Primary Key in Table1 and Foreign
key in Table2 while inserting row in Table2 Mr Sanghi is not able to enter value in
the column City. What is the possible reason for it?
(iii) Difference between Alter and Drop
PART D
i. Display 7 characters extracted from 7th left character onwards from the string
‘INDIA SHINING’.
WORLD’.
OR
i. UCASE() ii. TRIM() iii. MID() iv. DAYNAME() v. POWER()
32 Zetking industries has set up its new center at Raipur for its office and web-based 5
activities. The company compound has 4 buildings as shown in the diagram
below:
Center to center distances between various building is as follows:

harsh building to raj building 50m
raj building to fazz building 60m
fazz building to jazz building 25m
jazz building to harsh building 170m
harsh building to fazz building 125m
raj building to jazz building 90m
Number of computers in each of the buildings is as follows:
harsh building 15
raj building 150
fazz building 15
jazz building 25

b. Suggest the most suitable place to house the server of this organization with a
suitable reason.
c. Suggest the placement of the following devices with justification.
i)Internet connecting device ii) switch
d. The organization is planning to link its sale counter situated in various parts of
the same city, which type of network out of LAN, MAN or WAN will be formed?
Justify your answer.e. If there will be connection between all building using mesh
topology, suggest where need to place repeater.
33 Write a Python program to draw line charts of the financial data of Alphabet Inc. 5
between October 3, 2016 to October 7, 2016.
Sample Financial data (fdata.csv):
Date,Open,High,Low,Close
10-03-16,774.25,776.065002,769.5,772.559998
10-04-16,776.030029,778.710022,772.890015,776.429993
10-05-16,779.309998,782.070007,775.650024,776.469971
10-06-16,779,780.47998,775.539978,776.859985
10-07-16,779.659973,779.659973,770.75,775.080017
The code snippet gives the output shown in the following screenshot:
OR
Write a python program to plot a line chart based on the given data to depict the
changing weekly average temperature in Delhi for four weeks.
Week=[1,2,3,4]
Avg_week_temp=[40,42,38,44]
PART E
Table: ACCOUNT
ANO ANAME ADDRESS


Table: TRANSACT

T
T001 101 2500 Withdraw 2017-12-21
T002 103 3000 Deposit 2017-06-01
T003 102 2000 Withdraw 2017-05-12
T004 103 1000 Deposit 2017-10-22
T005 101 12000 Deposit 2017-11-06
(i)To display the ANO and AMOUNT of all Deposits and Withdrawals done in the
month of October 2017 from table TRANSACT.
(ii)To display the last date of transaction (DOT) from the table TRANSACT for the
Accounts having ANO as 103.
(iii)To display all ANO, ANAME and DOT of those persons from tables ACCOUNT
(iv) SELECT ANO, ANAME FROM ACCOUNT WHERE ADDRESS NOT IN ('CHENNAI',
'BANGALORE');
To display all ANO, ANAME and DOT of those persons from tables ACCOUNT and
TRANSACT who have done transactions less than or equal to 3000.
35 Assume following data is stored in data frame named as df1 1+1+2
Write following commands:
(i)Find total sales per state (ii) find total sales per employee
(iii)find total sales both employee wise and state wise
find mean, median and min sale state wise
Marking Scheme SET - 4
PART A
01 A network with all client computer and no server is called 1
a. Networking
b. Peer to Peer Computing
c. Client Server Computing
d. Any of them
Ans b
02 Which of these is not a communication channel? 1
(a) Satellite
(b) Microwave
(c) Radio wave
(d) Wi-Fi
Ans d
03 What is the use of Bridge in the network? 1
(a) To connect LANs (b) To separate LANs
(c) To control network speed (d) All of the above
Ans a
04 In the given query which keyword has to be inserted? 1
INSERT INTO employee______(1002, “Kausar”, 2000);

a) Table
b) Values
c) Relation
d) Field
Ans b
05 If column “Salary” contains the data set {1000, 15000, 25000, 10000, 15000}, 1
what will be the output after the execution of the given query?
SELECT SUM(DISTINCT SALARY) FROM EMPLOYEE;
a)75000
b) 25000
c) 10000
d) 50000
ans d
06 Open source software is software whose source code is available for modification 1
or enhancement by anyone. Which of the following is an example of open source
software?
a) Adobe Photoshop
b) Microsoft Word
c) Libre Office
d) Skype
Ans. C
07 Which of the following is a SQL aggregate function? 1
a) LEFT
b) AVG
c) JOIN
d) LEN
Ans b
08 The command used for modifying the records is: 1
a) update
b) add
c) updateall
d) none of the above
Ans a
09 By default, ORDER BY clause lists the results in _______ order. 1
a) Descending
b) Any
c) Same
d) Ascending
Ans d
10 The Pandas word has derived from 1
a) Panel Data System b) Panel Data Structure
c) Python Data Structure d) Python Data System
Ans a
11 Which of the following is one of the feature of data structure? 1
a) Specialized way of storing data
b) Specilized way to read or wirte data
c) Reshape data into different forms
d) Support data visualizations
Ans a
12 Which of the following correct statement for creating empty series? (Assume that 1
pandas library is already imported as pd)
a) ser = pd.Series(NaN)
b) ser = pd.Series(None)
c) ser = pd.Series()
d) ser = pd.Series
Ans c
13 What are the actions that can be taken to make a password? 1
a. Make a password complex, like a mix case, use numbers and special characters.
b. Never use common name, date of birth and city in the password
c. Don’t share passwords with your friends or relatives.
d. All of the above
ans d
Ans d
15 All aggregate functions except _______ ignore null values in their input 1
collection.
a) Count (attribute)
b) Count (*)
c) Avg
d) Sum
Ans b
16 Consider following SQL statement. What type of statement is this? 1
CREATE TABLE employee (name VARCHAR, id INTEGER)
a) DML
b) DDL
c) DCL
d) Integrity constraint
Ans b
17 Assertion (A): An Internet troll is a person who deliberately sows discord on the 1
Internet by starting quarrels or upsetting people.
Reason (R): We can download and use any material available on the Internet.
Ans c
18 Assertion (A): Data visualization refers to the graphical representation of 1
information and data using visual elements like charts, graphs and maps etc.
Reason (R): To install matplotlib library we can use the command
pip install matplotlib.
Ans B
PART B
19 What is difference between a website and a webpage? 2
Ans: Website is the complete software and webpage is just one of the pages from
the website like www.ambikapur.kvs.ac.in is website and
https://ambikapur.kvs.ac.in/admin/content?
type=school_class_wise_enrolment_posi is a single web page
OR
How website is not same as web portal?
Ans: Website is a single software and web portal is a combination of both online
and offline services given by the web portal. like www.google.co.in is a website
and www.ola.com is a web portal
2 marks for correct reason
20 Gopi Krishna is using a table Employee. It has the following columns: 2
Code, Name, Salary, Deptcode
He wants to display maximum salary departmentwise. He wrote the following
command:
SELECT Deptcode, Max (Salary) FROM Employee;
Rewrite the above query with necessary changes to help him get the desired
output.
Ans: SELECT Deptcode, Max(Salary)
FROM Employee
GROUP BY Deptcode;
21 What is the difference between group by and order by clause of MYSQL? Give an 2
example of each.
And: 01 Marks for correct difference and 01 marks for correct example
22 Write a small python codeto create a dataframewith headings (a and b) from the 2
list given below:
[[1,2],[3,4],[5,6],[7,8]]
Ans:
import pandas as pd
df = pd.DataFrame([[1, 2], [3, 4]], columns = ['a','b'])
df2 = pd.DataFrame([[5, 6], [7, 8]], columns = ['a','b'])
df = df.append(df2)
(½ mark for correct initialization, 1 mark for correct dataframe and ½ mark for
printing dataframe)
23 What is plagiarism? How can you avoid plagiarism while referring to someone’s 2
else’s creation?
Ans: 01 Marks for gender issue
01 marks for correct disability issue
OR
What is the common gender and disability issues faced while teaching / using
computers in classrooms?
Ans: 01 marks for definition of computer forensics
01 marks for explanation of important practices.
24 Write the output of following code: 2
import pandas as pd
import numpy as np
data = np.array([54,76,88,99,34])
s1 = pd.Series(data,index=['a','b','c','d','e'])
s2=s1.rename(index={'a':0,'b':1})
print(s2)
Ans:
0 54
1 76
c 88
d 99
e 34
dtype: int32
(2 marks for correct output)
11 Aruna 18 68
12 Mohini 14 47
13 Kiya 13 78
14 Lakshmi 16 87
15 Ravisha 14 60
a) Mean and mode of age of the students
b) Count the no of rows present in the dataframe
Ans:
a. print(“Mean of Age = “,DF[“Age”].mean())
print(“Mode of Age = “,DF[“Age”].mode())
b. print(“No of rows = “,DF.count())
PART C
26 A Gift Gallery has different stores in India. Database Administrator Abhay wants 3
to maintain database of their Salesmen in SQL to store the data, He has decided
that
questions:
i. The command to display the name of the salesman along with the sales amount
rounded off to one decimal point will be:
Ans: Select sname, round(sales,1) from salesman;
ii. Which of the following commands will display the details of all sales record of
North Area, regardless of case (whether North / NORTH / north):
Ans: Select * from salesman where upper(area)=’NORTH’;
ANS: Select sname, sales from Salesman where area=” East” or area=” West”;
Sample DataFrame:
'attempts': [1, 3, 2, 3, 2, 3, 1, 1, 2, 1],
Ans:
import pandas as pd
import numpy as np

'attempts': [1, 3, 2, 3, 2, 3, 1, 1, 2, 1],
print(df)
1 mark for Import statement
1 mark for creating dictionary
1 mark for Creating DataFrame and output
28 Consider the following dataframe 3
Age Name Score

0 26 Sachin 87
1 25 Dhoni 67
2 25 Virat 89
3 24 Rohit 55
4 31 Shikhar 47
Write Commands for the following:

1. to compute sum of score.
2. to compute mean of Age.
3. to find maximum score.
Ans:
1. df[['Score']].sum()
2. df[['Age']].mean()
3. df[['Score']].max()
(1 mark for each correct command)

29 Rishi has to prepare a project on “Swachh Bharat Shreshth Bharat”. He decides to 3
get information from the Internet. He downloads three web pages (webpage1,
webpage 2, webpage 3) containing information on the given topic.
1. He read a paragraph on from webpage 1 and rephrased it in his own words. He
finally pasted the rephrased paragraph in his project.
3. He also downloaded an icon from web page 3 and pasted it on the front page
of his project report.

a. Plagiarism
b. Paraphrasing
Answer:(b) :Paraphrasing
ii. A website is a collection of ___________

a. Web Servers
b. Web pages
c. Browsers
d. Hyperlinks
Answer : (b)
iii. The process of getting web pages, images and files from a web server to local
computer is called
a. FTP
b. Uploading
c. Downloading
d. Remote access
Answer : (c) Downloading
1 mark for each correct option
OR
Ms Samtha has many electronics gadgets which are not usable due to outdated
hardware and software. Help her to find any three best ways to dispose the used
electronic gadgets.
Ans:
1. Give Your Electronic Waste to a Certified E-Waste Recycler
2. Donating Your Outdated Technology
3. Give Back to Your Electronic Companies and Drop Off Points.
(1 mark for each correct ways of disposing e waste)
30 Based on table GRADUATE given here, write suitable SQL queries for the 3
following:
(a) List the names of those students who have obtained DIV I sorted by NAME.
(b) Display a report, listing NAME, STIPEND, SUBJECT and amount of stipend
received in a year assuming that the STIPEND is paid every month.
(c) To count the number of students who are either PHYSICS or COMPUTER SC
graduates.
Ans:
(a) SELECT NAME FROM GRADUATE WHERE DIV='I’ ORDER BY NAME;
(b) SELECT NAME, STIPEND, SUBJECT, STIPEND*12 STIPEND_YEAR FROM
GRADUATE;
(c) SELECT SUBJECT, COUNT(NAME) FROM GRADUATE GROUPBY (SUBJECT)
HAVING SUBJECT='PHYSICS' OR SUBJECT='COMP. Sc.';
OR
(i) There is column salary in table employee. The following two statements are
giving different outputs. What may be the possible reasons?
Select count(*) from employee select count(salary) from employee
(ii) Mr. Sanghi created two tables with City as Primary Key in Table1 and Foreign
key in Table2 while inserting row in Table2 Mr Sanghi is not able to enter value in
the column City. What is the possible reason for it?
(iii) Difference between Alter and Drop
Ans:
(i) contain null values
(ii) 14 Mr Sanghi was trying to enter the name of City in Table2 which is not
present in Table1 i.e., Referential Integrity ensures that value must exist in
referred table.
(iii) alter is used to change the structure of object while update is used to update
the record in a table
1 mark for each correct Answer
PART D
i. Display 7 characters extracted from 7th left character onwards from the string
‘INDIA SHINING’.
WORLD’.
Ans:
i. select mid('INDIA SHINING',7,7);
OR
i. UCASE()
ii. TRIM()
iii. MID()
iv. DAYNAME()
v. POWER()
Ans:
1. UCASE(): It converts the string into upper case.
Example:
Output:
WELCOME WORLD
Example:
Output:
Welcome world
Example:
Output:
Come

Example:
Output:
Friday
number.
Example:
SELECT POW(6,2);
Output:
36
32 Zetking industries has set up its new center at Raipur for its office and web-based 5
activities. The company compound has 4 buildings as shown in the diagram
below:
Center to center distances between various building is as follows:

harsh building to raj building 50m
raj building to fazz building 60m
fazz building to jazz building 25m
jazz building to harsh building 170m
harsh building to fazz building 125m
raj building to jazz building 90m
Number of computers in each of the buildings is as follows:
harsh building 15
raj building 150
fazz building 15
jazz building 25
b. Suggest the most suitable place to house the server of this organization with a
suitable reason.
c. Suggest the placement of the following devices with justification.
i)Internet connecting device ii) switch
d. The organization is planning to link its sale counter situated in various parts of
the same city, which type of network out of LAN, MAN or WAN will be formed?
Justify your answer.
e. If there will be connection between all building using mesh topology, suggest
where need to place repeater.
Ans:
a. Suitable layout
b. Raj Building because of Max Number of Computers
c. Both in Raj Building
d. MAN
e. Jazz Building
33 Write a Python program to draw line charts of the financial data of Alphabet Inc. 5
between October 3, 2016 to October 7, 2016.
Sample Financial data (fdata.csv):
Date,Open,High,Low,Close
10-03-16,774.25,776.065002,769.5,772.559998
10-04-16,776.030029,778.710022,772.890015,776.429993
10-05-16,779.309998,782.070007,775.650024,776.469971
10-06-16,779,780.47998,775.539978,776.859985
10-07-16,779.659973,779.659973,770.75,775.080017
The code snippet gives the output shown in the following screenshot:
Ans:
import pandas as pd
df = pd.read_csv('fdata.csv', sep=',', parse_dates=True, index_col=0)
df.plot()
plt.show() 01 mark for each correct statement
OR
Write a python program to plot a line chart based on the given data to depict the
changing weekly average temperature in Delhi for four weeks.
Week=[1,2,3,4]
Ans:
Week=[1,2,3,4]
plt.plot(Week,Avg_week_temp)
plt.show()
PART E
34. Write SQL queries for (i) to (iv) which are based on the tables. 1+1+2
Table: ACCOUNT
ANO ANAME ADDRESS

Table: TRANSACT

T
T001 101 2500 Withdraw 2017-12-21
T002 103 3000 Deposit 2017-06-01
T003 102 2000 Withdraw 2017-05-12
T004 103 1000 Deposit 2017-10-22
T005 101 12000 Deposit 2017-11-06
(i)To display the ANO and AMOUNT of all Deposits and Withdrawals done in the month of
October 2017 from table TRANSACT.
(ii)To display the last date of transaction (DOT) from the table TRANSACT for the Accounts
having ANO as 103.
(iii)To display all ANO, ANAME and DOT of those persons from tables ACCOUNT and
(iv) SELECT ANO, ANAME FROM ACCOUNT WHERE ADDRESS NOT IN ('CHENNAI',
'BANGALORE');
Ans:(i) SELECT * FROM TRANSACT WHERE TYPE = 'Deposit';
(ii) SELECT ANO,AMOUNT FROM TRANSACT WHERE DOT >= '2017-10-01' AND DOT <=
'2017-10-31'; OR
SELECT ANO,AMOUNT FROM TRANSACT WHERE DOT BETWEEN '2017-10-01' AND '2017-
10-31';
(iii) SELECT MAX(DOT) FROM TRANSACT WHERE ANO = 103;
To display all ANO, ANAME and DOT of those persons from tables ACCOUNT and
Ans:
SELECT ACCOUNT.ANO,ANAME,DOT FROM ACCOUNT,TRANSACT WHERE
ACCOUNT.ANO=TRANSACT.ANO AND AMOUNT <=3000; OR
SELECT A.ANO,ANAME,DOT FROM ACCOUNT A,TRANSACT T WHERE A.ANO=T.ANO AND
AMOUNT <=3000;
Assume following data is stored in data frame named as df1 1+1+2
Write following commands:
(i)Find total sales per state
(ii) find total sales per employee
(iii)find total sales both employee wise and state wise
find mean, median and min sale state wise
Ans:
(i) pv1=pd.pivot_table(dfN,index=[‘State’], values=[‘Sales’],aggfunc=np.sum)
(ii) pv1=pd.pivot_table(dfN,index=[‘Name of Employee’],
values=[‘Sales’],aggfunc=np.sum)
(iii) pv1=pd.pivot_table(dfN,index=[‘Name of
Employee’,’State’],values=[‘Sales’],aggfunc=np.sum) 2 marks for correct query
KENDRIYA VIDYALAYA SANGATHAN, RAIPUR REGION
SAMPLE QUESTION PAPER, SET -5 (2022-2023)
Class XII
Max Marks: 70 Marks Time: 3 Hrs
1. This question paper contains two parts A and B. Each part is compulsory.
2. Both Part A and Part B have choices.
3. Part-A has 2 sections:
a. Section – I is short answer questions, to be answered in one word or one line.
b. Section – II has two case studies questions. Each case study has 4 case-based sub-parts.
An examinee is to attempt any 4 out of the 5 sub parts.
4. Part - B is Descriptive Paper.
5. Part- B has three sections
a. Section-I is short answer questions of 2 marks each in which two questions have internal
options.
b. Section-II is long answer questions of 3 marks each in which two questions have
internal options.
c. Section-III is very long answer questions of 5 mark each in which one question has
internal option.
PART A
Section - I
Attempt any 15 questions from questions 1 to 21
1 Which of the following is not covered under IPR? 1
(a) Music (b) Insurance (c) Logo Designed (d) Invention
2 Point out the incorrect combination with regards to kind keyword for graph plotting. 1
(a) ‘bar’ for bar plot (b) ‘hist’ for histogram
(c) ‘line’ for line plot (d) All above are correct
3 What will be the output of the following code? 1
SELECT MOD(14,3);

import pandas as pd
s = pd.Series(6,index=range(0,5))
print(s)
5 If series s1 is having following data, 1
What would the command print(s1[3:6]) result in?
6 Fill in the blanks with correct library to execute the given code. 1
import ________ as plt
import numpy as np
ypoints = np.array([3, 8, 1, 10, 5, 7])
plt.plot(ypoints)
plt.show()
7 An_______ is a software extension that adds extra features to a program. 1
8 Rahul wants to print the row labels of the dataframe. He should use the ______ attribute of a
dataframe. 1
9 __________websites are ones that are fixed and display the same content for every user, 1
usually written exclusively in HTML.
10 Which of the network topology can be a preferred for a company that would like to keep 1
adding more and more computers to the topology economically as they grow?
11 What will be the result of the following query based on the table given here. 1
SELECT COUNT(Salary) FROM Instructor;
12 What is the maximum number of years that a patent would be valid in India? 1
import pandas as pd
import numpy as np
s = pd.Series(np.random.randn(4))
print (s.ndim)
14 The networking device that forwards data packets between computer network is 1
15 Write the expansion of the following terms 1
(a) HTTP (b) URL
16 Give any two examples of e-waste. 1
17 A mail or message sent to a large number of people indiscriminately without their
consent is called ________________
18 Plagiarism is a cybercrime under Indian IT Act. (True / False) 1
19 Write the command to delete all the data of the table ‘activity’ retaining only structure. 1
20 Which networking device with similar functionality can be used to replace a hub? 1
21 Give one disadvantage of Open Source Software. 1
Section -II
Both the case study based questions (22 & 23) are compulsory. Attempt any four sub
parts from each question. Each sub question carries 1 mark.
22 Consider the following Dataframe named happy_df created using following command
happy_df=pd.read_csv("Dataset3.csv")
Country Region Happiness Happiness Family

Rank Score
Switzerland Western Europe 1 7.587 1.34951
Iceland Western Europe 2 7.561 1.40223
Denmark Western Europe 3 7.527 1.36058
Norway Western Europe 4 7.522 1.33095
Canada North America 5 7.427 1.32261
Finland Western Europe 6 7.406 1.31826
Netherlands Western Europe 7 7.378 1.28017
Sweden Western Europe 8 7.364 1.28907
New Zealand Australia and New Zealand 9 7.286 1.31967
Australia Australia and New Zealand 10 7.284 1.30923
(i) Complete the following command to display first five rows of the above Dataframe. 1
print(happy_df.iloc[:]
(ii) Write the command to display number of rows and columns of the above Dataframe. 1
(a) print(happy_df.row, happy_df.columns)

(b) print(happy_df.shape() )
(c) print(happy_df.shape() )
(iii) Which command(s) of the following would display only Region column of the above 1
Dataframe.
(a) print( happy_df.Region )

(b) print( happy_df.iloc [ , ‘Region’] )
(c) print( happy_df.iloc [ : ,’Region’] )
(d) print( happy_df.iloc [: , 1] )
(iv) What will be the output of the following command? 1
print(happy_df.loc[4:6,'Country'])
(v) Which of the following commands would display maximum value of every column? 1
(a) print(happy_df.max)
(b) print(happy_df.max())
(c) print(happy_df.max(axis=1))
(d) print(happy_df.max, axis=1)
23 Consider a table showing air quality of a city taken over period of time. The name of the table is
cityair.
Odate PM25 PM10 CO SO2
2019-03-01 114.56 104.08 0.82 12.89
2019-03-02 176.07 97.02 0.66 30.02
2019-03-03 38.85 95.51 0.6 20.68
2019-03-04 43.48 98.1 0.81 6.32
2019-03-05 53.2 95.25 0.81 17.25
2019-03-06 58.19 83.69 0.81 11.03
2019-03-07 39.87 85.68 0.78 4.02
2019-03-08 38.91 89.41 0.77 5.58
2019-03-09 36.46 115.57 0.83 5.6
(i) Which of the following command will give the latest date for which data is available in 1
the above table?
(a) SELECT MAX(ODate) FROM cityair;
(b) SELECT MIN(ODate) FROM cityair;
(c) SELECT COUNT(ODate) FROM cityair;
(ii) Which of the following commands will display ‘Mar’ for Date column? 1
(a) SELECT MONTH(ODate) FROM cityair;
(b) SELECT MONTHNAME(ODate) FROM cityair;
(c) SELECT DATE(ODate) from cityair;
(iii) What will be the output of the following query? 1
SELECT ROUND(PM25,1) FROM cityair WHERE Odate = ‘2019-03-01’
(iv) What will be the output of the following query? 1
SELECT DAY(Odate) FROM cityair WHERE SO2 <5;
(a) 7
(b) 3
(c) Empty Table
(v) Write a command to delete the data of 1st Mar 2019 in the table. 1
Part – B
Section – I
24 Write a program to create a Series having 10 random numbers in the range of 10 and 20. 2
25 What is importance of primary key in a table? How many primary keys can be there for a 2
table?
OR
Explain working of TRIM( ) function with proper examples.
26 Consider the following ‘Student’ table. 2
(i) What will be the most suitable datatype for the grade column and why?
(ii) Write a command to insert Suman’s record with the data as shown in the table.
27 Consider the following Series ‘s’ 2
0 4.0
1 5.0
2 7.0
3 NaN
4 1.0
5 10.0
dtype: float64
(i) Write a Python code to add 1 to all the elements.
(ii) Rewrite the above code to add 1 to all the elements assuming NaN to be value 0.
5 of 8
28 Explain the working of ORDER BY clause of SELECT query with proper example. 2
29 Consider a string “AS YOU know MORE” 2

Write the queries for the following tasks.
(i) Write a command to display “know”.
(ii) Write a command to display number of characters in the string.
OR
Consider a string “You Grow more” stored in a column str. What wil
be the output of the following queries?
(i) SELECT UPPER(str);
(ii) SELECT substr(str,-9,4);
30 Given here is a Dataframe of Sales data of four months stored with name sales_df. 2
(i) Write a Python code to find total sales of July month.

(ii) Write a Python code to add the sales of August month with [70,94,80,93] data.
31 Give any two advantages of star topology. 2
32 Write any four net netiquettes you should follow while working with Internet. 2
33 What is cyber bullying? What are the two actions you would take if you feel that you are 2
a victim of cyber bullying?
34 Predict the output of the following code. 3
data = {'one':'a','two':'b','three':'c'}
s=pd.Series(data)
print(s)
print(s.size)
35 What is Phishing? Write any two precautions that you would take to avoid being victim of phishing. 3
36 Consider a Dataframe ‘emp_df’ - 3
Name Age Salary
Shalini 25 32000
Gaurav 31 35000
Bhavya 29 37000
Divansh 28 27000
Write a Python code to display a line graph with names on x-axis and age on y-axis. Give appropriate
names for axis and title for the graph.
OR
Write a Python code to display a bar graph with names on x-axis and salary on y-axis. Give
appropriate names for axis and title for the graph.
6 of 8
37 Consider a MySQL table ‘product’ 3
P_ID PROD_NAME PROD_PRICE PROD_QTY

P01 Notebook 85 500
P02 Pencil Box 76 200
P03 Water Bottle 129 50
P04 School Bag 739 70
(i) Display maximum PROD_QTY.
(ii) Display the value of each product where the value of each product is calculated as
PROD_PRICE * PROD_QTY
(iii) Display average PROD_PRICE.
Section -III
38 Consider the following Dataframe named housing_df. 5
area_type location size society total_sqft bath balcony price

Super built- Anand Vihar 2 BHK DDA 1056 2 1 39.07
up Area
Plot Area Mundka 4 BHK PWD 2600 5 3 120
Built-up Peeragarhi 3 BHK DLF 1440 2 3 62
Area
Super built- Lajpat Nagar 3 BHK DDA 1521 3 1 95
up Area
Super built- Patel Nagar 2 BHK PWD 1200 2 1 51
up Area
Super built- Rajendra Place 2 BHK DLF 1170 2 1 38
up Area
Super built- Old Airport Road 4 BHK DDA 2732 4 2 204
up Area
Super built- Rajaji Nagar 4 BHK PWD 3300 4 2 600
up Area
Write a Python program to
(i) Create the above Dataframe from a csv file named housing.csv and display it. Import
necessary libraries.
(ii) Display the houses having 2 bathrooms.
(iii) Display the price of house in lakhs. Assume the numbers in price column represent price in
lakhs.
(iv) Display first 3 rows of the Dataframe
7 of 8
39 Consider the below mentioned table of ‘CLOTH’ 5
DCODE DESCRIPTION PRICE MCODE LAUNCHDATE
10001 FORMAL SHIRT 1250 M001 12–JAN–08
10020 FROCK 750 M004 09–SEP–07
10012 INFORMAL SHIRT 1450 M002 06–JUN–08
10019 EVENING GOWN 850 M003 06–JUN–08
10090 TULIP SKIRT 850 M002 31–MAR–07
10023 PENCIL SKIRT 1250 M003 19–DEC–08
10089 SLACKS 850 M003 20–OCT–08
Write the commands for the following:

(i) Display first three letters of description e.g. ‘FRO’ for ‘FROCK’
(ii) Display the description after removing leading spaces if any.
(iii) Display number of characters taken by each description.
(iv) Display the number of MCODE in the table.
(v) Display the day of the LAUNCHDATE. Eg. ‘Monday’, ’Tuesday’ etc
OR
(i) Display total price of products launched in year 2008.
(ii) Display minimum price of product for each material code(MCODE).
(iii) Display the most recent LAUNCHDATE.
(iv) Display the description in lower case alphabets.
(v) Display remainder of price divided by 10.
40 Software Development Company has set up its new center at Raipur for its office and 5 web
based activities. It has 4 blocks of buildings named Block A, Block B, Block C, Block D.
Block Number of Computers
A 25
B 50
C 125
D 10
Shortest distances between various Blocks in meters:
Block C to Block A 30m
Block D to Block C 50m
(i) Suggest most suitable place to house server of this company with proper reason.
(ii) Suggest the layout of connection between the blocks and identify the topology of the
layout.
(iii) Suggest the placement of the Modem in the network with justification.
The company wants to design a website for its customers that can be changing the
contents as per the responses of customers. What type of website static or dynamic can
they design for this purpose?
(iv) What type of network would be formed if the Raipur office is connected to their New Delhi
8 of 8
office?
9 of 8
KENDRIYA VIDYALAYA SANGATHAN, RAIPUR REGION
Class XII INFORMATICS PRACTICES (065)
(2022-2023)
MARKING SCHEME-SET 5
PART A
S.No. Section - I Marks
Attempt any 15 questions from questions 1 to 21
1 Insurance 1
2 ‘line’ for line plot 1
3 4 1
4 1
1 mark for correct answer.

5 1
6 matplotlib.pyplot 1
7 Add-on 1
8 Index 1
9 Static 1
10 Tree/Star 1
11 5 1
12 Surfing website / online ticket booking or any two correct activities that can leave 1
digital footprint.
13 1 1
14 Router 1
15 (a) Hyper Text Transfer Protocol (b) Uniform Resource Locator 1
16 Any two examples of e-waste. 1
17 Spam 1
18 Any two health hazards 1
10 of
19 Delete from activity; 1
20 Switch 1
21 Any one disadvantage 1
22 (i) print(happy_df.iloc[0 : 5 ] 1
(ii) print(df.shape) 1
(iii) (a) print(happy_df.Region) and (d) print(happy_df.iloc[:,1]) 1
(iv) 4 Canada 1
5 Finland
6 Netherlands
Name: Country, dtype: object
(v) (b) print(happy_df.max()) 1
23 (i) (a) SELECT MAX(ODate) FROM cityair; 1
(ii) (b) SELECT MONTHNAME(ODate) FROM cityair; 1
(iii) 114.6 1
(iv) 7 1
(v) DELETE FROM cityair WHERE Odate = “2019-03-01”; 1
24 import pandas as pd 2
import numpy as
np
arr= np.random.randint(10,20,(10,))
s = pd.Series(arr)
½ mark for correct imports.
1 mark for correct numpy array creation.
½ mark for correct creation of Series.
25 Primary Key : A column of collection of columns to identify a tuple in a relation. It is 2

used to search / locate row in a relation. There can be only one primary key in a table.
1 mark for correct definition with proper significance.
2 mark for stating only one primary key in a table.
OR
TRIM () function is used to remove leading and trailing spaces from a string a table. It
can be used as
TRIM(String)
For example;
11 of
SELECT TRIM(' bar ');
-> 'bar'
1 mark for stating purpose of the functions
1 mark for correct example.
26 (i) Gender column datatype char(1) as all the possible values can be accommodated and 2
it will be space efficient.
(ii) INSERT INTO Student (Rollno, Sname, Subject, Marks) VALUES
(“003”, ”SUMAN”, “IP”, 75);

27 (i) s1 = s + 2
1
print(s1)
(ii) s.add(1,fill_value=0)
print(s)
28 The ORDER BY keyword is used to sort the result-set in ascending or descending 2
order.
The ORDER BY keyword sorts the records in ascending order by default. To sort the
records in descending order, use the DESC keyword.
1 mark for correct explanation.
1 mark for appropriate example.
29 (i) select mid(“AS YOU know MORE”,8,4); 2
(ii) select length(“AS YOU know MORE”);
OR
(i) YOU GROW MORE

(ii) Grow
30 (i) print(sales_df.sum().loc[‘July’]) 2
(ii) sales_df.insert(4,column="August",value=[70,94,80,93] )
1 mark each for above or any other correct line(s) of code
31 1 mark each for any two correct advantages 2
12 of
32 ½ mark each for any four correct nettiquettes
33 Cyberbullying is bullying with the use of digital technologies. It can take place on 2
social media, messaging platforms, gaming platforms and mobile phones. It is repeated
behaviour, aimed at scaring, angering or shaming those who are targeted.
1 mark for correct definition of the term

½ mark each for any two steps to be taken in case you are victim of cyber bullying.
Section -II
34 one a 3
two b
three
c
dtype:
object 3
1 mark for correct index labeling
½ mark for correct elements
½ mark for correct dtype
35 Phishing is a method of trying to gather personal information using deceptive e-mails 3
and websites.
1 mark for correct definition of the term
½ mark each for any two steps to be taken to avoid being victim of phishing.
36 x = emp_df['Name'] 3
y = emp_df['Age']
pl.plot(x,y)
pl.xlabel("Name")
pl.ylabel("Age")
pl.title("Name vs
Age") pl.show()
½ mark for getting correct values of x and y axis
1 mark for correct plot function with minimum necessary parameters
½ mark for giving correct axis labels
½ mark for correct title
½ mark for show() functioning
13 of
OR
x = emp_df['Name']
y = emp_df['Salary']
pl.bar(x,y)
pl.xlabel("Name")
pl.ylabel("Salary")
pl.title("Name vs Salary")
pl.show()
½ mark for getting correct values of x and y axis

1 mark for correct plot function with minimum necessary parameters
½ mark for giving correct axis labels
½ mark for correct title
½ mark for show() functioning
37 (i) SELECT MAX(PROD_QTY) FROM product; 3
(ii) SELECT PROD_PRICE*PROD_QTY AS ‘Value’ FROM product;
(iii) SELECT AVG(PROD_PRICE) FROM product;

38 import pandas as pd # ½ mark 5
housing_df = pd.read_csv("housing.csv") # 1 Mark
print(housing_df) # ½ mark
print(housing_df[housing_df.loc[:,'bath']==2]) # 1Mark
print(housing_df.loc[:,'price']*100000) # 1 Mark
print(housing_df.head(3)) # 1 Mark
Give marks for any alternate method or the task done in multiple steps.
14 of
39
(i) SELECT LEFT(DESCRIPTION,3) FROM CLOTH; 5
(ii) SELECT LTRIM(DESCRIPTION) FROM CLOTH;
(iii) SELECT LEN(DESCRIPTION) FROM CLOTH;
(iv) SELECT COUNT(DISTINCT MCODE) FROM CLOTH;
(v) SELECT DAYNAME(LAUNCHDATE) FROM CLOTH;
OR
(i) SELECT SUM(PRICE) FROM CLOTH WHERE YEAR(LAUNCHDATE) = 2008;
(ii) SELECT MIN(PRICE) FROM CLOTH GROUP BY MCODE;
(iii) SELECT MAX(LAUNCHDATE) FROM CLOTH;
(iv) SELECT LOWER(DESCRIPTION) FROM CLOTH;
(v) SELECT MOD(PRICE,10) FROM CLOTH;
1 mark each for correct query. Any alternate method also should be given due credit.
40 (i) Block C would be the most suitable place to house server as it has maximum 5
number of computers.
(ii) The most suitable layout would be
A B
C D
Topology : Star/Tree
(iii) Modem should be placed where the server is therefore it should be placed at Block C.
Dynamic website as the dynamic website can alter based on customer’s choice.
(iv) WAN as the distance between the two locations would be more than 100KM
1 mark for correct answer.

12 SM Ip

Uploaded by

Copyright:

Available Formats

12 SM Ip

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

12 SM Ip

Uploaded by

Copyright:

Available Formats

KENDRIYA VIDYALAYA SANGATHAN

REGIONAL OFFICE RAIPUR

Mrs. Sandhya Lakra,

1. Mr. Arvind Bhatpahare, PGT CS, KV No. 2 Raipur

Mr. Sumit Kumar Choudhary, PGT CS, KV No. 2 Korba NTPC

Data Handling using Pandas

Know the Terms:

Ans: print(S1+S2) or S1.sum(s2)

Code to create the above data frame:

import pandas as ____________ #Statement 1

Answer: (ii) DataFrame

i. df.index ii.df.shape() iii.df.shape iv. df.size

Answer: (iii) df.shape

Answer: (iii) df.index=["Air India","Indigo","Spicejet","Jet","Emirates"]

Write Python commands to do the following:

Answer: c. df['Name'][(df['Second']>=12) & (df['Second']<=20)]

Rollno Name Class Section CGPA Stream

St1 1 Aman IX E 8.7 Science

St2 2 Preeti X F 8.9 Arts

St3 3 Kartikey IX D 9.2 Science

St4 4 Lakshay X A 9.4 Commerce

Q. Name any three attributes along with example.

i. Display the columns Sal and Bonus

ii. Display the details of employee Kavita.

iii. Display the details of the last employee.

Large Answer Questions

Q.Create a dataframe named booking with the following data:

TCode Name Tickets Amount

T0001 Anuj Maheta 5 1355

T0002 Sandeep Oza 2 1169

T0003 Manvi Sharma 6 1988

Item Material Colour Price

Sofa Wooden Maroon 25000

Dining Table Plywood Yellow 20000

Chair Plastic Red 1500

Chair Wooden Light Blue 2500

Dining Table Aluminum Golden 65000

a) Display the details of the chair and sofa.

Q. Create a dataframe using the 2D dictionary to store the following records:

House Activity1 Activity2 Activity3

Red Green Blue Yellow

Term1 200 350 400 380

Term2 189 250 375 300

Database Query using SQL

 Mind Map MySQL Aggregate function

Single Row Functions Aggregate functions

1. Math Function Count() Function

2. String Function Sum() Function

3. Date and Time Avg() Function

1. Single Row Functions

Single Row Function

Numeric Function Date Function

 POWER(X,Y) OR POW(X,Y)- returns the number X raised to the power of another

S.NO QUERY OUTPUT

S.N QUERY OUTPUT

ii) SELECT MOD(16,4); 0

iii) SELECT MOD (45,0); null

iv) SELECT MOD(9,0.4); 0.2