PB23IP03

KENDRIYA VIDYALAYA SANGATHAN

ERNAKULAM REGION
FIRST PRE BOARD EXAMINATION 2023-24
CLASS XII
INFORMATICS PRACTICES (065)
MARKING SCHEME
TIME: 03 HOUR M.M.: 70

SECTION A

1. ______________is a device which convert analog signal into digital signal and vice versa: 1
a. Modem
b. Gateway
c. Repeater
d. Hub
Ans: a .Modem
2. 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. good maintenance
3. You were not able to create the IP project, therefore you downloaded a project from the internet and 1
submitted it to your teacher by your name. This wrong/unethical act is considered as ___________ .
a. Copyright
b. Hacking
c. Plagiarism
d. Trademark
Ans : c. Plagiarism
4. Which aggregation function does not give useful information when applied on non numeric 1
attribute?
a. Count
b. Min
c. Avg
d. Max
Ans : c. Avg
5 If column “EmpName ” of students table contains values (“Amal”,NULL,”Thanu”). What will be 1
output after execution of given query.
Select count(EmpName) from students;
a. 2
b. Error
c. 3
d. Amal NULL Thanu
Ans: a. 2
6 Which among the following is not an example of FLOSS? 1
a. Google Chrome
b. Python
c. Linux
d. MySQL
Ans:a. Google Chorme
7 Which of the following functions is used to import a dataframe from csv file ? 1
a. read_csv()
b. import_csv()
c. to_csv()
d. export_csv()
Ans: a. read_csv()
8 Ms.Pooja is trying to write a query, but missing a keyword in the query given below. 1
Which of the following keywords / operator will you suggest her to complete the following query:-
SELECT ____________ dept_name FROM Office.
a. Unique
b. From
c. DISTINCT
d. *
Ans: c. DISTINCT
9 Predict the output of the following query: 1
select pow( day ('2023/04/02'), 2);
a. 2
b. 4
c. 20
d. None of these
Ans: b.4

10 How many elements will be there in the series named “A1”? 1

>>> A1 = pd.Series(range(5))
>>> print(A1)
a. 5
b. 4
c. 6
d. None of the above
Ans:a.5
11 Which of the following function is used to find the largest value from the given data in MYSQL? 1
a. MAXIMUM ()
b. MAX ()
c. LARGEST ()
d. BIG ()
Ans:b.MAX()
12 You can create a Python pandas series using? 1
a. sequence
b. ndarray
c. tuple
d. all of the above
Ans: d) all of the above

13 Nirmal received an email warning him of closure of his bank accounts if he did not update his 1
anking information as soon as possible. He clicked the link in the email and entered his banking
information. Next he got to know that he was duped. This is an example of __________ .
a. Phishing
b. Identity Theft
c. Digital footprint
d. All of Above
Ans: a.Phishing
14 In SQL, which function is used to extract a part of string from a specific location of a string. 1
a. MID()
b. SUBSTR()
c. SUBSTRING()
d. All of the above
Ans:d All of the above

15 _____________is a software used for accessing and viewing any information on World Wide Web. 1
a. Webpage
b. Website
c. Web browser
d. None of these
Ans:c.Web browser

16 Which among the following will create a digital footprint? 1

a. Accessing websites
b. Sending e-mails
c. Posting social media updates on Facebook/Instagram
d. All the above
Ans: d. All the above

17 Assertion (A):- Router is a device which is used to connect similar types of networks. 1
Reasoning (R): -Router performs the necessary translation so that the connected networks can
communicate properly.
a. Both A and R are true and R is the correct explanation for A
b. Both A and R are true and R is not the correct explanation for A
c. A is True but R is False
d. A is false but R is True
Ans: d. A is false but R is True
18 Assertion (A):- The attribute ‘T’ (For eg: if df is the dataframe then, df.T ) returns the DataFrame 1
in transposed form.
Reasoning (R):- When a DataFrame is transposed ,only the rows and columns labels are
interchanged but the elements remain in their position.
a. Both A and R are true and R is the correct explanation for A
b. Both A and R are true and R is not the correct explanation for A
c. A is True but R is False
d. A is false but R is True
Ans: c. A is True but R is False

SECTION B

19 Explain the terms Static and Dynamic Webpage. 2

OR
Natasha wants to play a video in her browser but she is not able to do so. A message on screen
instructs her to install the Adobe Flash Player plugin. Help her to understand the term plugin and its
uses.
Ans:
Static webpages are simple in terms of complexity, takes less time to get load, information gets
changed rarely, require less work and low cost in designing them.
Dynamic webpages are complex in terms of complexity, take more time to get load, information
changed frequently, requires more work and more cost in designing them.
(1 mark each for each correct explanation (minimum 2 points for each term 1/2 marks each)
OR
A Plugin is also called Add-on or extensions. It is a software add-on installed in a program which
adds a specific feature to the existing program without altering the host program itself. It enhances
the basic feature of the software and makes the software either more powerful or better targeted to
specific requirements. Widely used in digital audio, video and web browsing.
(2 marks for correct explanation)
20 The python code written below has syntactical errors. Rewrite the correct code 2
and underline the corrections made.
Import pandas as pd
S1=pd.Series({1:'Varun',2:'Suma',3:'Rakhi'})
S2=pd.Series({1:93,2:87,3:90})
df=p.dataframe({'Name':s1,'Marks':S2})
print(DF)
Ans:
import pandas as pd
S1=pd.Series({1:'Varun',2:'Suma',3:'Rakhi'})
S2=pd.Series({1:93,2:87,3:90})
df=pd.DataFrame({'Name':S1,'Marks':S2})
print(df)
(1/2 mark for each correction)
21 Consider the given SQL string: 2
“FIRST PREBOARD EXAM IP 2023”
Write suitable SQL queries for the following:
a. To display the substring ‘PREBOARD’ from the given string
 b. To display last four characters from the given string.
Ans:
a. Select substr(“FIRST PREBOARD EXAM IP 2023”,7,8);
b. Select right(“FIRST PREBOARD EXAM IP 2023”,4);
(1 mark for each correct query)
22 Predict the output of the given Python code: 2
import pandas as pd
s1=pd.Series([1,2,3,4])
print(s1**3)
Ans:

(2 marks for correct output)

23 Differentiate between the Plagiarism and Copyright 2
Ans:
Plagiarism Copyright
 It occurs when we use  It is basically used for making
someone else’s work as and distributing copies of the
ours without giving work commercially.
credits to the owner or  Penalties for copyright are
source. governed by state
 It is violation of laws.Copyright violation is
moral,ethical or illegal
organizational norms and
described as illegal
activity, but not a criminal
or civil offence.
(2 marks for correct differentiation)
24 Complete the given Python code to display the temperature in Delhi ie 38. 2
import ________ as p
D={'Jammu':24,'Srinagar':23,'Delhi':38,'Agra':34,'Bangalore':32}
temperature=_______.Series(_______)
print(____________)

Ans:
import pandas as P
D={'Jammu':24,'Srinagar':23,'Delhi':38,'Agra':34,'Bangalore':32}
temperature=P.Series(D)
print(temperature['Delhi'])
(½ mark for each correct fill-up)
25 Differentiate the purpose of ‘HAVING’ clause and Where clause in SQL with example. 2
Ans:
Having clause is used to specifying filtering conditions for groups. Having clause is applied to each
 group of values. Where as whereas, the where clause is applied to an individual value.
Eg: Select title,sum(price) from heathdrinks group by title having avg(price)>200;
Here condition applied on groups (aggregate function) therefore having clause to be used.
Select Select title,sum(price) from heathdrinks where price>500;
Here condition applied on individual row therefore where clause to be used
(1 mark for correct explanation .½ mark for each for correct examples)
SECTION C

26 Based on the SQL table EMPLOYEE, write suitable queries for the following: 3

a. Display gender wise total salary.

b. Display number of city excluding NULL values, designation (design) wise.
c. Display employee details in descending order of Salary.
OR
Predict the output of the following queries based on the table EMPLOYEE given above:
a. SELECT LEFT(NAME,4) FROM EMPLOYEE WHERE GEN = "F";
b. SELECT SALARY+10000 "UPDATED SALARY " FROM EMPLOYEE WHERE
CITY= "MYSORE";
c. SELECT SUM(SALARY) "TOTAL SALARY HRA" FROM EMPLOYEE WHERE
DESIG= "HR";
Ans:
a. SELECT GEN,SUM(SALARY) FROM EMPLOYEE GROUP BY GEN;
b. SELECT DESIGN,COUNT(CITY) FROM EMPLOYEE GROUP BY DESIGN;
c. SELECT * FROM EMPLOYEE ORDER BY SALARY DESC;
(1 mark for each correct query)
OR
a.
+---------------------+
| LEFT(NAME,4) |
+--------------------+
| Vibh |
| Dhri |
| Adit |
| Moni |
+---------------------+

b.
+-----------------------------+
 | UPDATED SALARY |
+-----------------------------+
| 93324 |
| 94429 |
+------------------------------+
c.
+-------------------------------+
| TOTAL SALARY HRA |
+-------------------------------+
| 151932 |
+--------------------------------+
(1 mark each correct output. Reduce ½ mark if heading not mentioned in Qn b and c)
27 Create a DataFrame in Python from the given list: 3
[['S101','Vaishnav',70],['S102','Pooja',69],['S103','Priya',75],['S104','Jonathan',82]]
Also give appropriate index and column headings as shown below:

Ans:
import pandas as p #Statement 1
l=[['S101','Vaishnav',70],['S102','Pooja',69],['S103','Priya',75],['S104','Jonathan',82]] #Statement 2
d=p.DataFrame(l,index=[1,2,3,4],columns=['ADM NO','NAME','MARKS']) #Statement 3
print(d) #Statement 4
(#Statement 1 & 4 – ½ mark each)
(#Statement 2 & 3 – 1 mark each)
28 Write MySQL statements for the following: 3
a. To create a database named GAMES.
b. To create a table named FOOTBALL based on the following specification:

Column Name Data Type Constraints

PlayerID Varchar(10) Primary Key
PlayerName Varchar(30) NOT NULL
Age Integer
Ans:
a. Create database games;
(1 mark for correct answer)
b. Create table football(PlayerID varchar(10) Primary Key,PlayerName varchar(30) NOT
NULL,Age integer);
(½ mark for CREATE TABLE Football
½ mark each for correctly specifying columns
½ mark for correctly specifying primary key & not null )
29 Ruhi has recently shifted to a new city and school. She does not know many people in her new city 3
and school. But suddenly, someone is posting negative, demeaning comments on her social
 networking profile etc. She is also getting repeated mails from unknown people. Every time she
goes online, she finds someone chasing her online.
a. What is this happening to Ruhi?
b. What immediate action should she take to handle it?
c. Is there any law in India to handle such issues? If so, mention the same.
OR

What precautions (mention any three) will you take to protect your data while accessing the
Internet?

Ans:
a. She is a victim of Cyber Bullying and Cyber Stalking
b. Take parental or teacher’s assistance and can solve the issue legally by lodging a complaint
to the relevant authorities
c. Yes. Cyber Law implemented through Information Technology Act, 2000 (also known as IT
Act).
(1 mark for each correct answer)
OR

We must take care of the following to protect our data on internet

 Do not disclose passwords to anyone (including your friends).They may open different
websites and misuse the account
 Do not download unwanted software/free software from the web.It may corrupt our
computer system
 Do not open untrusted link or do not accept unknown friend requests while using social
medias.
(1 mark each for writing any 3 correct precaution method)

30 Consider the given DataFrame BOOK 3

Write suitable Python statements for the following:

a. Add a column called 'Offer_Price' with the following data:
[130,160,210,450]
b. Add a new book named 'The Secret Graden' having price 200.
c. Remove the column Offer_Price.

Ans:
a. BOOK['Offer_Price'] = [130,160,210,450]
b. BOOK.loc[4,:]=[ 'The Secret Graden',200]
OR
BOOK.loc[4]=[ 'The Secret Graden',200]
c. del BOOK['Offer_Price']
 OR
BOOK.pop('Offer_Price')
OR
BOOK.drop('Offer_Price', axis=1,inplace=True)
OR
BOOK=BOOK.drop('Offer_Price', axis=1)
(1 mark for each correct statement)
SECTION D

31 Ravi database administrator has designed a database for a Medical shop. Help him by writing 4
queries of the following questions based on the given table:

a. Write a query to display the year of medicine with oldest expire date.
b. Write a query to display the month of medicine with latest expire date.
c. Write a query to display all the medicine details with date of expire in the month of
'December'.
d. Write a query to count total number of medicines with year of expire in 2021.
Ans:
a. SELECT YEAR(MIN(DOE)) FROM MED_SHOP;
b. SELECT MONTH(MAX(DOE)) FROM MED_SHOP;
c. SELECT * FROM MED_SHOP WHERE MONTH(DOE)=12;
OR
SELECT * FROM MED_SHOP WHERE MONTHNAME(DOE)=’DECEMBER’;
d. SELECT COUNT(MCODE) FROM MED_SHOP WHERE YEAR(DOE)=2021;

32 Consider the dataframe SHOP given below: 4

a. Predict the output of the following python statement:

i. print(SHOP.shape)
ii. print(SHOP.iloc[3,2])
b. Rename the column Price to Amount
c. Write Python statement to add a new column ‘Net_Price which is the product of Qty and
Price and can be calculated as Qty* Price
OR
 (Option for part c only)
Write Python statement to export the DataFrame SHOP to a CSV file named shopdata.csv stored at
F: drive.
Ans:
a.
i. (5,4)
ii. Mumbai
( ½ mark for each correct output/statement)
b. SHOP=SHOP.rename(columns={"Price": "Amount"})
OR
SHOP=SHOP.rename({"Price": "Amount"},axis=1)
OR
SHOP=SHOP.rename({"Price": "Amount"}, axis="columns")
(1 mark for correct statement)
c. SHOP ["Net_Price"] = SHOP ["Qty"]* SHOP["Price"]

OR
SHOP.to_csv(“F:\\ shopdata.csv”)
(2 mark for correct statement)
SECTION E

33 Write suitable SQL query for the following: 5

a. Display the number of characters present in the string ‘INDIA SHINING’.
b. Display the position of occurrence of string ‘ME’ in the string ‘WELCOME WORLD’.
c. Round off the value 23.7853 to 2 decimal place.
d. Display the remainder of 100 divided by 9.
e. Remove all the expected leading and trailing spaces from a column userid of
the table ‘USERS’.
OR
Ankith has created following table named STATIONERY:

Help him in writing SQL queries to the perform the following task:
a. Insert a new record in the table having following values:
['SP03','Sharpner','PQR',5.25]
b. To change the value 'Dot Pen' to 'Ball Point Pen' in StationaryName column.
c. To remove the records of those items with price is less than 7.
d. To add a new column 'Quantity' with datatype float.
 e. To display records of 'ABC' Company.
Ans:
a. SELECT LENGTH(‘INDIA SHINING’);
b. SELECT INSTR(‘WELCOME WORLD’,’ME’);
c. SELECT ROUND(23.7853,2);
d. SELECT MOD(100,9);
OR
SELECT 100 %9;
OR
SELECT 100 MOD 9;
e. SELECT TRIM(USERID) FROM USERS;
(1 mark for each correct query)
OR
a. INSERT INTO STATIONARY VALUES ('SP03','Sharpner','PQR',5.25);
b. UPDATE STATIONARY SET STATIONARYNAME='Ball Point Pen' WHERE
STATIONARYNAME='Dot Pen';
c. DELETE FROM STATIONARY WHERE PRICE <7;
d. ALTER TABLE STATIONARY ADD COLUMN QUANTITY FLOAT;
e. SELECT * FROM STATIONARY WHERE COMPANY='ABC';
(1 mark for each correct query)
34 Hi-Tech University is an international educational organization. It is planning to set up its India 5
campus at Pune 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.

Distance between different blocks

ADMIN TO ACCOUNTS 55M
ADMIN TO EXAMINATION 90M
ADMIN TO RESULT 50M
ACCOUNTS TO EXAMINATION 55M
ACCOUNTS TO RESULT 50M
EXAMINATION TO RESULT 45M
DELHI HEAD OFFICE TO PUNE CAMPUS 1452KM

Number of computers installed at various buildings

 ADMIN 110
ACCOUNTS 75
EXAMINATION 40
RESULT 12
DELHI HEAD OFFICE 20

a. Suggest the most appropriate location of the server inside the PUNE campus out of the
four buildings. Justify your answer.
b. Suggest and draw cable layout to efficiently connect various buildings within the PUNE
campus for a wired connectivity.
c. Which networking device will you suggest to be procured by the company to interconnect
all the computers of various buildings of PUNE campus?
d. University is intending to link its Pune campus and Delhi Head office. Out of LAN, MAN,
or WAN, what kind of network will be created?
e. Justify your answer. Which of the following will you suggest to establish the online face to
face communication between the people in the ADMIN office of PUNE campus and Delhi
head office?
a) Cable TV b) Email c) Video conferencing d) Textchat

Ans:
a. ADMIN BUILDING as it has maximum number of computers.
b. Star Topology

c. Hub/Switch
d. WAN as distance between Delhi and Pune is more than 40kms.
e. c) Video conferencing
(1 mark for each correct answer)
35 The price details of 10 books of ‘Lets Read Book Shop’ are given below: 5
Price_inRs=[110,114,112,115,116,109,113,117,118,119]
Write suitable Python code to generate a histogram based on the given data, along with bins value
5, chart title ‘Lets Read Book Shop’, x axis label "Price in Rs" and y axis label "Number of
Books". Also give suitable python statement to save this chart.
OR
Write suitable Python code to create 'MOBILE SALES PER QUATER' Bar Chart as shown below:
Also give suitable python statement to save this chart.
Ans:
import matplotlib.pyplot as plt #1
Price_inRs=[110,114,112,115,116,109,113,117,118,119] #2
plt.title("Lets Read Book Shop") #3
plt.xlabel("Price in Rs") #4
plt.ylabel("Number of Books") #5
plt.hist(Price_inRs,bins=5) #6
plt.show() #7
(½ mark each for each correct statement 1,2,4,5,6,7)
(1 mark for correct statement 3)
plt.savefig("PriceOfBooks.jpg")
(1 mark for the correct statement)

OR
import matplotlib.pyplot as plt #1
x=['Qtr1','Qtr2','Qtr3','Qtr4'] #2
y=[40,35,55,90] #3
plt.title("MOBILE SALES PER QUATER") #4
plt.xlabel("Quaters") #5
plt.ylabel("Sales") #6
plt.bar(x,y) #7
plt.show() #8

( ½ mark for each correct statement)

plt.savefig("MobileSales.jpg")
(1 mark for the correct statement)