SET-4

Series GBM Code No. 90

Candidates must write the Code on the
Roll No.
title page of the answer-book.

 Please check that this question paper contains 9 printed pages.

 Code number given on the right hand side of the question paper should be
written on the title page of the answer-book by the candidate.
 Please check that this question paper contains 7 questions.
 Please write down the Serial Number of the question before
attempting it.
 15 minute time has been allotted to read this question paper. The question
paper will be distributed at 10.15 a.m. From 10.15 a.m. to 10.30 a.m., the
students will read the question paper only and will not write any answer on the
answer-book during this period.

INFORMATICS PRACTICES
Time allowed : 3 hours Maximum Marks : 70

Instructions :

(i) All questions are compulsory.

(ii) Answer the questions after carefully reading the text.

1. (a) Identify the odd one out of the following : 2

Optical Fiber/Coaxial Cable/Bluetooth/Twisted Pair Cable
Give reason for your answer.

(b) How is it easier to diagnose fault in Star topology than in Bus

topology ? 2

90 1 P.T.O.
 (c) What is the purpose of logical address of computer ? 2

(d) Does open source software only mean that the software is free of
cost ? Give reason for your answer. 2

(e) Which of the following are valid IP addresses ? Give reason(s) if

invalid. 2
(i) 121.23.1.45
(ii) 192.168.0.254
(iii) 192.168.0.1
(iv) 198.-1.1.1

2. (a) Write the value that will be assigned to variable x after executing
the following statement : 1
x = 20 – 5 + 3 * 20/5;

(b) Consider the statement :

choice = ‘Y’;

What is the datatype of variable choice ? Write a Java statement to

declare the variable ‘choice’. 1

(c) While working in Netbeans IDE, Amit wants that the text in a Text
area should move to the next line without breaking the word. Which
properties would help him to do that ? 1

(d) Write a Java statement to : 1

Append a string ‘‘ABC’’ to the text that is already in the text area
named jTextArea1.

(e) Write the output of the following HTML code : 2

<ol start="4">
<li>Bake the mixture in oven for an hour</li>
<li>Remove from oven</li>
<li>Allow the mixture to stand for ten minutes</li>
<li>Serve</li>
</ol></code>

90 2
 (f) Given below is a code.
<message>
<text>Hello, world!</text>
</message>

Which language is used to write the above code ? 2

What are <message>, </message>, <text> and </text> ?

(g) Rewrite the following code using if else if statement instead of

switch : 2
switch (success) {
case –1: x1 = "No result" ;
break;
case 0 : x1 = "Final result- Not successful";
break;
default : x1 = "result NOT known";
break;
}

3. (a) How is a database related to a table ? 1

(b) Ariya wants to add another column ‘Gender’ in the already existing
table ‘CUSTOMERS’. She has written the following statement.
However, it has errors. Rewrite the correct statement. 1
[MODIFY TABLE CUSTOMERS GENDER char(1);

(c) In a hospital, the patients are allocated to wards. A database named

‘Hospital’ is created. One table in this database is : Ward with
WardId, WardName, NumOfBeds as columns and WardId as the
primary key.
Write another suitable table you could expect to see in the ‘Hospital’
database, with 3 suitable columns identifying Primary key and
Foreign key in the table that you expect. 2

(d) Explain the following statement with the help of example : 2

‘‘In a transaction either all the SQL statements be committed or all

rolled back.’’

90 3 P.T.O.
 (e) Given below is the ‘Department’ table : 2

DEPCODE DEPNAME
101 ADMIN
102 RECEPTION
103 PERSONNEL
SET AUTOCOMMIT = 0;
UPDATE Department SET DEPNAME = ‘OFFICE’ WHERE
DEPNAME = ‘ADMIN’;
INSERT INTO Department VALUES(104,‘HRD’);
UPDATE Department SET DEPNAME = ‘FRONT OFFICE’ WHERE
DEPNAME = ‘RECEPTION’;
COMMIT;
DELETE FROM Department WHERE DEPNAME = ‘FRONT
OFFICE’;
ROLLBACK;
SELECT * FROM Department;

What will be the output of the above given SELECT statement ?

(f) How is HAVING clause similar to WHERE clause ? How is HAVING

clause different from WHERE clause ? Explain with the help of
examples of each. 2

4. (a) Write the values of r and s after the execution of the following code : 1
int p = 11;
int q = 21;
int r;
int s;
r = ++q;
s = p++;
r++;

90 4
 (b) What will be displayed in jTextField1 and jTextField2 after the
following code is executed : 2
int ndigits = 0;
int N = 35;
while (N > 12) {
ndigits = ndigits + 1;
N = N–10;
}
jTextField1.setText(" "+ndigits);
jTextField2.setText(" "+N);

(c) Write the value of C after executing the following code : 2

int P;
int R = 8;
int oddNum;
int C = 0;
for (P = 1; P < R; P=P+3)
{
oddNum = P %2;
if (oddNum == 1)
{
C= C+1;
}
}

(d) Write the value that will be stored in the variable t after the
execution of the following code. How many times will the loop
execute ? 2
int sum = 0;
int score = 0;
double t;
do
{

score = score +1;

sum = sum + score;
}
while (score <=3);
t = sum / 3;
90 5 P.T.O.
 (e) The following code has error(s). Rewrite the correct code underlining
all the corrections made : 2
int j;
int i =15;
int flag = 1;
while( j = 2 j < i; j++)
{
if(i % j = 0)
{
flag == 0;
break;
}
}

(f) Ms. Fauzia works as a programmer in ‘‘TelTel Mobile Company’’

where she has designed a software to compute charges to be paid by
the mobile phone user. A screenshot of the same is shown below :

90 6
 Each Call is charged at < 1·00.
Each SMS is charged at < 0·50.
Users can also opt for Mobile Data Plan. Charges for Mobile Data
Plan are flat < 50·00.

Help Ms. Fauzia in writing the code to do the following :

(i) When the ‘Calculate Charges’ button is clicked, ‘Calls and
SMS Charges’, ‘Mobile Data Plan Charges’ and ‘Amount to
Pay’ should be calculated and displayed in the respective text
fields.
‘Amount to Pay’ is calculated as :
Calls and SMS Charges + Mobile Data Plan Charges (if any) 4
(ii) When the ‘Clear’ button is clicked, all the textfields and
checkbox should be cleared. 1
(iii) When the ‘Exit’ button is clicked, the application should close. 1

5. Consider the Table ‘‘Infant’’ shown below :

Table : Infant
ItemCode Item DatePurchase UnitPrice Discount
101 Frock 2016-01-23 700 10
102 Cot 2015-09-23 5000 25
103 Soft Toy 2016-06-17 800 10
104 Baby Socks 2014-10-16 100 7
105 Baby Suit 2015-09-20 500 5

Note : Discount column stores discount %.

Write the commands in SQL for (i) to (viii) and output for (ix) and (x).
(i) To display the details about the Cot. 1
(ii) To list the names of items and their unit price that have unit price
less than 800 and discount more than 5%. 1
(iii) To list the names of items and their date of purchase that were
purchased after 31st December, 2015. 1
(iv) To display the number of items that have more than 10% as discount. 1
(v) To display item code and unit price in decreasing order of unit price. 1
90 7 P.T.O.
 (vi) To increase the unit price of each item by 10% of their unit price. 1

(vii) To display the highest unit price of items. 1

(viii) To display the names of items that have ‘Baby’ anywhere in their
item names. 1

(ix) SELECT MID (Item,1,2) FROM Infant; 1

(x) SELECT AVG(UnitPrice) FROM Infant WHERE DATEPURCHASE

>‘2015–01–01’; 1

6. (a) ‘‘XYZ’’ Company conducts workshops for employees of organizations.

The company requires data of workshops that are organized. Write
SQL query to create a table ‘Workshop’ with the following structure : 2
Field Type Constraint
WorkshopId Integer Primary key
Title Varchar(50)
DateWorkshop Date
NumSpeakers Integer

(b) Consider the tables given below and answer the questions that follow : 2
Table : Event
EventId Event NumPerformers CelebrityID
101 Birthday 10 C102
102 Promotion Party 20 C103
103 Engagement 12 C102
104 Wedding 15 C104

Table : Celebrity
CelebrityID Name Phone FeeCharged
C101 Faiz Khan 99101956 200000
C102 Sanjay Kumar 893466448 250000
C103 Neera Khan Kapoor 981166568 300000
C104 Reena Bhatia 65877756 100000

90 8
 (i) Name the Primary keys in both the tables and Foreign key in
‘Event’ table.
Can NumPerformers (Number for performers) be set as the
Primary key ? Give reason.
(ii) How many rows will be present in the Cartesian join of the
above mentioned two tables ?
In the table ‘Event’, the CelebrityID 102 is present twice in
the column ‘‘CelebrityId’’. Is there any discrepancy ? Give
reason.
(c) With reference to the above given tables (in Q6 b), write the
commands in SQL for (i) to (iii).
(i) To display EventId, Event name, Celebrity Id and Names of
celebrities for only those events that have more than 10
performers. 2
(ii) To display Event name, Celebrity Id and Names of celebrities
who have ‘‘Khan’’ anywhere in their names. 2
(iii) To display Event name, Names of celebrities and Fee charged
for those celebrities who charge more than 200000. 2

7. (a) List two disadvantages of e-governance to a disabled person. 2

(b) How do online retailers usually offer customers a wider range of
products at prices that are lesser than those of traditional stores ? 1
(c) Ms. Cathy is creating a form for Vidya University Sports Council
application. Help her to choose the most appropriate controls from
ListBox, ComboBox, TextField, TextArea, RadioButton, CheckBox,
Label and Command Button for the following entries : 2
S.No. Function
1 To let the user enter NAME
2 To let the user enter MOBILE NUMBER
To let the user choose one PROFESSION out of the
3
categories : Teaching/Non-Teaching/Research Scholar
To let the user select facilities out of Gym, Yoga, Table
4 Tennis, Badminton and Aerobics. More than one facility
may be chosen.

90 9 70,700
P.T.O.
 CBSE AISSCE 2017 Marking Scheme for Informatics Practices
(Sub Code:065 Paper Code 90/Outside Delhi)
General Instructions:
● Marking scheme is the final document for all references with regard to evaluation
and cannot be altered under any circumstances.
● The answers given in the marking scheme are SUGGESTIVE, Examiners are
requested to award marks for all alternative correct Solutions/Answers conveying
the similar meaning.
● All programming questions have to be answered with respect to Java Language only.
● In Java, ignore case sensitivity for identifiers (Variable / Functions ).
● In SQL related questions – both ways of text/character entries should be acceptable
for Example: “AMAR” and ‘amar’ both are correct.
● In SQL related questions – semicolon should be ignored for terminating the SQL
statements.
● In SQL related questions, ignore case sensitivity.
● In SQL related questions, ignore column headers in outputs.

1 (a) Identify odd one out of the following: 2

Optical Fiber/Coaxial Cable/ Bluetooth/Twisted Pair Cable.
Give reason for your answer.
Ans Odd one : Bluetooth
Reason : Bluetooth is a wireless/unguided communication media while others
are wired/guided communication media.
(1 mark for each part)
(b) How is it easier to diagnose fault in Star topology than in Bus topology? 2
Ans In Star topology each node is directly connected to a central hub / switch,
hence fault diagnosis becomes easy.
In bus topology all the nodes are connected to the backbone cable. The signal
travels through the entire length of the backbone and is received by the node
for which it is intended. Hence, fault diagnosis is difficult.
(2 marks for correct answer)
NOTE : ½ mark each for both topologies if are explained using either
diagrams / text.
(c) What is the purpose of logical address of computer? 2
NOTE : Full 2 marks to be given if any part of question no. 1 is attempted
correctly.
(d) Does Open source software only mean that the software is free of cost? Give 2
reason for your answer.
Ans No , it does not only mean that the software is free of cost.
Reason: It also means that the source code is available to user with freedom
to use , modify and distribute.
(1 mark for each part)
(e) Which of the following are valid IP addresses? Give reason(s) if invalid. 2

Page #1/15
 CBSE AISSCE 2017 Marking Scheme for Informatics Practices
(Sub Code:065 Paper Code 90/Outside Delhi)

i)​ ​121.23.1.45
ii)​ ​192.168.0. 254
iii)​ ​192.168.0.1
iv)​ ​198.-1.1.1
Ans Valid IP addresses: (i) , (ii) , (iii)
Invalid IP address: (iv) 198.-1.1.1 because an IP address is a group of four
bytes; each of which can be a number from 0 to 255.
(1 mark for stating all the three valid IP addresses
OR
½ mark for stating any two valid IP addresses)
(½ mark for mentioning invalid address , ½ mark for the reason)
2 (a) Write the value that will be assigned to variable x after executing the 1
following statement:
x = 20 -5 + 3 * 20/5;
Ans 27
(1 Mark for correct answer)
(b) Consider the statement: 1
choice = ‘Y’;
What is the datatype of variable choice? Write Java statement to declare the
variable ‘choice’.
Ans The data type of the variable choice is ​char​.
Declaration : ​char choice;
(½ mark for stating datatype as char)
(½ mark for declaration)
NOTE : ​char choice = ‘Y’ ​; to be accepted as correct answer and 1
Mark to be allotted.
(c) While working in Netbeans IDE, Amit wants that the text in a Text area 1
should move to the next line without breaking the word. Which properties
would help him do that?
Ans wrapStyleWord property, lineWrap property
( 1 mark for correct answer)
NOTE: Full 1 mark to be allotted if any one of the above properties is
mentioned
(d) Write Java statement to: 1
Append a string "ABC" to the text that is already in the text area named
jTextArea1.
Ans jTextArea1.append("" +"ABC");
OR
jTextArea1.append("ABC");
OR

Page #2/15
CBSE AISSCE 2017 Marking Scheme for Informatics Practices
(Sub Code:065 Paper Code 90/Outside Delhi)

jTextArea1.append("ABC"+" ");
( 1 mark for correct answer )
NOTE : (½ mark to be allotted if setText is mentioned instead of append)
(e) Write the output of the following HTML code. 2
<ol start="4">
<li>Bake the mixture in oven for an hour</li>
<li>Remove from oven</li>
<li>Allow the mixture to stand for ten minutes</li>
<li>Serve</li>
</ol></code>
Ans 4 Bake the mixture in the oven for an hour
5 Remove from oven
6 Allow the mixture to stand for ten minutes
7 Serve
(½ mark for each line of output)
NOTE: Full 2 marks to be allotted if :
</code>​ mentioned as an error
OR
‘Error’ as ​<li>​ is an empty tag is mentioned
OR
‘No output’ / ‘Error’ is mentioned
(f) ​ iven below is a code.
G 2
<message>
<text>Hello, world!</text>
</message>
Which language is used to write the above code?
What are ​<message>, </message>, <text> and </text>?
Ans Language used is XML

<message>, </message>,<text > and </text > are user defined tags
OR tags ​OR container tags ​OR ​<message>, </message> ​is the root
element and ​ <text>, </text> ​is the child element.
(1 mark for each part)
(g) Rewrite the following code using if else if statement instead of switch : 2
switch (success) {
case -1: x1 = "No result" ;
break;
case 0: x ​ 1 = ​"Final result- Not successful";
break;
default: x1 = "result NOT known";
break;
}

Page #3/15
 CBSE AISSCE 2017 Marking Scheme for Informatics Practices
(Sub Code:065 Paper Code 90/Outside Delhi)

Ans if (success == -1)

x1= “No result”;
else if (success == 0)
x1= “Final result -Not successful”;
else
x1= “result NOT known”;
(2 marks for correct ​if-else-if​ construct and conditions)
OR
(1 mark for correct use of ​if-else-if​ construct)
(1 mark for correct conditions)
3 (a) How is a database related to a table ? 1
Ans Database contains related tables.
OR
Database contains tables.
( 1 mark for any correct answer )
(b) Ariya wants to add another column ‘Gender’ in the already existing table 1
‘CUSTOMERS’. She has written the following statement. However , it has
errors . Rewrite the correct statement.
[​MODIFY TABLE CUSTOMERS GENDER char(1);
Ans ALTER TABLE CUSTOMERS
ADD GENDER CHAR(1);
OR
ALTER TABLE CUSTOMERS
ADD GENDER CHAR;
OR
ALTER TABLE CUSTOMERS
ADD COLUMN GENDER CHAR;
OR
ALTER TABLE CUSTOMERS
ADD COLUMN GENDER CHAR(1);
( ½ mark for ​ALTER TABLE​ )
( ½ mark for ​ADD​ clause)
NOTE:​ ​VARCHAR(1)​ ​in place of ​CHAR​ should be accepted
(c) In a hospital, the patients are allocated to wards. A database named 2
‘Hospital’ is created. One table in this database is: WARD with ​WardId​,
WardName, NumOfBeds​ as columns and ​WardId​ as the primary key.
Write another suitable table you could expect to see in the ‘Hospital’
database, with 3 suitable columns identifying Primary key and Foreign key in
the table that you expect.
Ans Example:
Table - Patient
Columns - PatientId, PatientName, WardId
Patient Id - Primary Key
WardId - Foreign Key

Page #4/15
CBSE AISSCE 2017 Marking Scheme for Informatics Practices
(Sub Code:065 Paper Code 90/Outside Delhi)

OR
Any other suitable table mentioning its primary key and foreign key.
( 1 mark for writing any three suitable column names
OR ½ mark for writing any two suitable column names)
( ½ mark for mentioning the ​Primary Key​)
( ½ mark for mentioning the ​Foreign Key​)
NOTE : Tabular representation also to be accepted
(d) Explain the following statement with the help of example: 2
“In a transaction either all the SQL statements be committed or all rolled
back.”
Ans Any MySQL example showing ​COMMIT, ROLLBACK, AUTOCOMMIT ​to be
accepted OR definition of transaction
(2 marks for the correct answer)
NOTE : Full 2 marks to be given if 3(e) or 3(f) is attempted.
(e) Given below is the ‘Department’ table: 2

DEPCODE DEPNAME
101 ADMIN
102 RECEPTION
103 PERSONNEL

SET AUTOCOMMIT = 0;
UPDATE DEPARTMENT SET DEPNAME = ‘OFFICE’ WHERE DEPNAME =
‘ADMIN’;
INSERT INTO DEPARTMENT VALUES(104,’HRD’);
UPDATE DEPARTMENT SET DEPNAME = ‘FRONT OFFICE’ WHERE
DEPNAME = ‘RECEPTION’;
COMMIT;
DELETE FROM DEPARTMENT WHERE DEPNAME = ‘FRONT OFFICE’;
ROLLBACK;
SELECT * FROM DEPARTMENT;
What will be the output of the above given SELECT statement?
Ans
DEPCODE DEPNAME
101 OFFICE
102 FRONT OFFICE
103 PERSONNEL
104 HRD

(½ mark for each line of output )

(f) How is ​HAVING clause similar to ​WHERE clause? How is ​HAVING clause 2
different from ​WHERE​ clause? Explain with the help of examples of each.
Ans Similarity : Both ​HAVING​ and ​WHERE​ clause are used for checking condition(s)
Difference : ​WHERE clause is used for simple condition(s). ​HAVING clause is
Page #5/15
 CBSE AISSCE 2017 Marking Scheme for Informatics Practices
(Sub Code:065 Paper Code 90/Outside Delhi)

used for conditions with group of values.

e.g.
WHERE​ clause:
SELECT * FROM EMP WHERE DEPT = “PHYSICS “ ;

HAVING​ clause :
SELECT SUM(SALARY), DEPT FROM EMP
GROUP BY DEPT HAVING COUNT(*) > 3;

OR
Any other valid example
(½ mark for Similarity)
(½ mark for Difference)
(½ mark for any correct example of ​WHERE​ clause)
(½ mark for any correct example of ​HAVING​ clause)
NOTE: 1 mark each for Similarity and Difference to be allotted if
explained with the help of valid example.
4 (a) Write the values of r and s after execution of following code: 1
int p = 11;
int q = 21;
int r;
int s;
r = ++q;
s = p++;
r++;
Ans r = 23
s = 11
(½ mark for each correct answer)
(b) What will be displayed in jTextField1 and jTextField2 after the following 2
code is executed:
​int ndigits = 0;
int N = 35;
while (N > 12) {
ndigits = ndigits + 1;
N = N-10;
}
jTextField1.setText(" "+ndigits);
jTextField2.setText(" "+N);
Ans jTextField1​ will display 3
jTextField2​ will display 5
(1 mark for each correct answer)
(c) Write the value of C after executing the following code: 2
int P;
int R = 8;

Page #6/15
CBSE AISSCE 2017 Marking Scheme for Informatics Practices
(Sub Code:065 Paper Code 90/Outside Delhi)

int oddNum;
int C = 0;
for (P = 1; P < R; P=P+3)
{
oddNum = P %2;
if (oddNum == 1)
{
C= C+1;
}
}
Ans 2
(2 marks for correct value of C)
(d) Write the value that will be stored in variable ​t after the execution of the 2
following code . How many times will the loop execute ?
int sum = 0;
int score = 0;
double t;
do
{
score = score +1;
sum = sum + score;
}
while (score <=3);
t = sum / 3;
Ans Value of t will be 3.0
Loop executes 4 times
NOTE: 3 and 3.3 for value of t should also be accepted
(1 mark for correct value of ‘t’)
(1 mark for correct number of iterations)
(e) The following code has error(s). Rewrite the correct code underlining all the 2
corrections made :
int j;
int i = 15;
int flag = 1;
while( j = 2 j < i; j++)
{
if(i % j = 0)
{
flag == 0;
break;
}
}
Ans Corrected code :
int j;

Page #7/15
CBSE AISSCE 2017 Marking Scheme for Informatics Practices
(Sub Code:065 Paper Code 90/Outside Delhi)

int i = 15;
int flag = 1;
for​(j = 2​;​ j < i ; j++)
{
if ( i % j ​= =​ 0)
{
​flag = 0​;
break ;
}
}
OR
int j;
int i = 15;
int flag = 1;
j=2;
while(​j < i​)
{
if ( i % j ​= =​ 0)
{
​flag = 0​;
break ;
}
​j++;
}
(½ mark each for correcting any four errors)
OR
(1 mark for only identifying any four errors – without making any
corrections)
(f) Ms. Fauzia works as a programmer in “TelTel Mobile Company” where she has
designed a software to compute charges to be paid by the mobile phone
user. A screenshot of the same is shown below:

Page #8/15
CBSE AISSCE 2017 Marking Scheme for Informatics Practices
(Sub Code:065 Paper Code 90/Outside Delhi)

Each Call is charged at Rs.1.00 .

Each SMS is charged at Rs. 0.50.
Users can also opt for Mobile Data Plan. Charges for Mobile Data Plan are flat
Rs.50.00.
Help Ms. Fauzia in writing the code to do the following:
(i) When the ‘Calculate Charges’ button is clicked, ‘Calls and SMS Charges’,
‘Mobile Data Plan Charges’ and ‘Amount to Pay’ should be calculated and
displayed in the respective text fields.
​‘Amount to Pay’ is calculated as:
Calls and SMS Charges + Mobile Data Plan Charges(if any) 4
Ans int Calls, Sms;
double Total,dataAmt = 0, grandTot, callsChg ,smsChg;
Calls = Integer.parseInt(jTextField3.getText());
Sms = Integer.parseInt(jTextField4.getText());
callsChg = Calls * 1.00 ;
smsChg = Sms * 0.5 ;
Total = callsChg + smsChg;//Total=(Calls*1.00)+(Sms*0.5);
if (jCheckBox1.isSelected())
dataAmt = 50.00;
grandTot = Total + dataAmt;
jTextField5.setText(“”+ Total);
jTextField6.setText(“”+dataAmt);
jTextField7.setText(“”+grandTot);
(1 mark for calculating ‘Amount to Pay’ )
(1 mark for correct use of if statement)
(1 mark for calculating and displaying ‘Grand Total’)
(½ mark for displaying ‘Calls and SMS Charges’)
(½ mark for displaying ‘Mobile Plan charges’)
(ii) When ‘Clear’ button is clicked, all the textfields and checkbox should be 1
cleared.
jTextField1.setText(“”);
jTextField2.setText(“”);
jTextField3.setText(“”);
jTextField4.setText(“”);
jTextField5.setText(“”);
jTextField6.setText(“”);
jTextField7.setText(“”);
jCheckBox1.setSelected(false);
(½ mark for clearing any TextField and ½ mark for clearing the
CheckBox)
NOTE : NULL in place of “” to be accepted for clearing text field.

Page #9/15
 CBSE AISSCE 2017 Marking Scheme for Informatics Practices
(Sub Code:065 Paper Code 90/Outside Delhi)

(iii) When the ‘Exit’ button is clicked, the application should close. 1
System.exit(0);
(1 mark for correct answer)
5 Consider the Table “Infant” shown below.
Table: Infant
ItemCode Item DatePurchase UnitPrice Discount
101 Frock 2016-01-23 700 10
102 Cot 2015-09-23 5000 25
103 Soft Toy 2016-06-17 800 10
104 Baby Socks 2014-10-16 100 7
105 Baby Suit 2015-09-20 500 5

NOTE : Discount column stores discount %.

Write the commands in SQL for (i) to (viii) and output for (ix) and (x)
(i) To display the details about the Cot. 1
Ans SELECT * FROM Infant WHERE Item=’Cot’;
OR
SELECT * FROM Infant WHERE Item IN(’Cot’);
(½ mark for ​SELECT​)
(½ mark for ​WHERE)
(ii) To list the names of items and their unit price that have unit price less than 1
800 and discount more than 5%.
Ans SELECT Item,UnitPrice FROM Infant
WHERE UnitPrice<800 AND Discount>5;

NOTE: ​&&​ should be accepted in place of ‘AND’

( ½ mark for ​SELECT​)
( ½ mark for W​ HERE)
(iii) To list the names of items and their date of purchase that were purchased 1
after 31​st​ December , 2015.
Ans SELECT Item, DatePurchase FROM Infant
WHERE DatePurchase>’2015-12-31’;
OR
SELECT Item, DatePurchase FROM Infant
WHERE DatePurchase> 20151231;
( ½ mark for ​SELECT​)
( ½ mark for W​ HERE​)
(iv) To display the number of items that have more than 10% as discount 1
Ans SELECT COUNT(Item) FROM Infant
WHERE Discount > 10;
( ½ mark for ​SELECT​)

Page #10/15
CBSE AISSCE 2017 Marking Scheme for Informatics Practices
(Sub Code:065 Paper Code 90/Outside Delhi)

( ½ mark for ​WHERE)

(v) To display Item code and unit price in decreasing order of unit price. 1
Ans SELECT ItemCODE, UnitPrice FROM Infant
ORDER BY UnitPrice DESC;
( ½ mark for ​SELECT​)
( ½ mark for O​ RDER BY​)
(vi) To increase the Unit price of each item by 10% of their unit price. 1
Ans UPDATE Infant
SET UnitPrice = 1.10 * UnitPrice;
OR
UPDATE Infant
SET UnitPrice = UnitPrice + .10 * UnitPrice;
OR
UPDATE Infant
SET UnitPrice = UnitPrice + 10/100 * UnitPrice;
( ½ mark for ​UPDATE​)
( ½ mark for S​ ET​)
(vii) To display the highest unit price of items. 1
Ans SELECT MAX(UnitPrice)
FROM Infant;
( ½ mark for ​SELECT​)
( ½ mark for M​ AX()​)
(viii To display the names of items that have ‘Baby’ anywhere in their item names 1
)
Ans SELECT Item FROM Infant
WHERE Item LIKE ‘%Baby%’;
( ½ mark for S​ ELECT​)
( ½ mark for​ L​ IKE​ Clause​)
(ix) SELECT MID(Item,1,2) FROM Infant; 1
Ans MID(Item,1,2)
​ r
F
Co
So
Ba
Ba
(1 mark for correct output)
(x) SELECT AVG(UnitPrice)FROM Infant 1
WHERE DATEPURCHASE >’2015-01-01’;
Ans AVG(UnitPrice)
1750.0

Page #11/15
 CBSE AISSCE 2017 Marking Scheme for Informatics Practices
(Sub Code:065 Paper Code 90/Outside Delhi)

OR
AVG(UnitPrice)
1925.0
NOTE : Option 2 to be accepted if updation is taken into consideration.
(1 mark for correct output)

6 (a) “XYZ” Company conducts workshops for employees of organizations. The 2

company requires data of workshops that are organized. Write SQL query to
create a table ‘Workshop’ with the following structure:

Field Type Constraint

WorkshopId integer Primary Key
Title Varchar(50)
DateWorkshop Date
NumSpeakers Integer

Ans CREATE TABLE WORKSHOP

(
WorkshopId INTEGER PRIMARY KEY,
Title VARCHAR(50),
DateWorkshop DATE,
Numspeakers INTEGER
);
( ½ mark for ​CREATE TABLE​)
( 1 mark for Column Names with Data Types)
( ½ mark for ​PRIMARY KEY ​Constraint)
(b) Consider the tables given below and answer the questions that follow : 2
​ ​ Table: Event
EventId Event NumPerformers CelebrityID
101 Birthday 10 C102
102 Promotion Party 20 C103
103 Engagement 12 C102
104 Wedding 15 C104

Table: Celebrity
CelebrityID Name Phone FeeCharged
C101 Faiz Khan 99101956 200000
C102 Sanjay Kumar 893466448 250000
C103 Neera Khan Kapoor 981166568 300000
C104 Reena Bhatia 65877756 100000

(i) Name the Primary keys in both the tables and Foreign key in ‘Event’ table.
Can NumPerformers(Number for performers) be set as the Primary key? Give
reason.

Page #12/15
CBSE AISSCE 2017 Marking Scheme for Informatics Practices
(Sub Code:065 Paper Code 90/Outside Delhi)

Ans Table: Event

PRIMARY KEY​ – EventId
Table: Celebrity
PRIMARY KEY​ – CelebrityID
Table : Event
FOREIGN KEY​ – CelebrityID

No, NumPerformers cannot be set as Primary key because

It may not be unique in every row (when more rows are added to the table
later)
NOTE: Yes, should also be considered as in the given table
NumPerformers contains ​UNIQUE​ and ​NOT NULL​ values.
(½ mark for ​PRIMARY KEY of both tables and ​FOREIGN KEY of Event
table)
(½ mark for stating Yes / No and/or with correct reason)
(ii) How many rows will be present in the Cartesian join of the above mentioned
2 tables?
In the table ‘Event’, the CelebrityID 102 is present twice in the column
“CelebrityId”. Is there any discrepancy? Give reason.
Ans Number of rows in the Cartesian join = 16
No discrepancy. Because the same Celebrity may perform in more than one
events.
NOTE:
“It is a foreign key column, hence it can store duplicate values” may also
be accepted.
(½ mark for stating the numbers of rows )
(½ mark for stating no discrepancy without stating reason)
(c) With reference to the above given tables (in Q6 b), Write commands in SQL
for (i) to (iii)
(i) To display EventId, Event name, Celebrity Id and Names of celebrities for 2
only those events that have more than 10 performers.
Ans SELECT EventId, Event, Event.CelebrityId, NAME
(i) FROM Event, Celebrity
WHERE Event.CelebrityId = Celebrity.CelebrityId AND
NumPerformers>10;
OR
SELECT EventId, Event, E.CelebrityId, Name
FROM Event E, Celebrity C
WHERE E.CelebrityId = C.CelebrityId AND NumPerformers>10;
(½ mark for ​SELECT​)
(½ mark for ​FROM​)
(½ mark for correct use of Join)

Page #13/15
 CBSE AISSCE 2017 Marking Scheme for Informatics Practices
(Sub Code:065 Paper Code 90/Outside Delhi)

(½ mark for ​NumPerformers>10​ condition)

(ii) To display Event name, Celebrity Id and Names of celebrities who have 2
“Khan” anywhere in their names.
Ans SELECT Event, Event.CelebrityId, Name
(ii) FROM Event, Celebrity
WHERE Event.CelebrityId = Celebrity.CelebrityId AND Name
LIKE ‘%Khan%’;
OR
SELECT Event, E.CelebrityId, Name
FROM Event E, Celebrity C
WHERE E.CelebrityId = C.CelebrityId AND Name LIKE
‘%Khan%’;
OR
SELECT E.Event, E.CelebrityId, C.Name
FROM Event E, Celebrity C
WHERE E.CelebrityId = C.CelebrityId AND C.Name LIKE
‘%Khan%’;
(½ mark for ​SELECT​)
(½ mark for ​FROM​)
(½ mark for correct use of Join)
(½ mark for ​Name LIKE ‘%Khan%’​ condition)
(iii) To display Event name, Names of celebrities and Fee charged for those 2
celebrities who charge more than 200000 .
Ans SELECT Event, Name, FeeCharged
(iii) FROM Event, Celebrity
WHERE Event.CelebrityId = Celebrity.CelebrityId AND
FeeCharged > 200000;
OR
SELECT Event, Name, FeeCharged
FROM Event E, Celebrity C
WHERE E.CelebrityId = C.CelebrityId AND FeeCharged >
200000;
OR
SELECT E.Event, C.Name, C.FeeCharged
FROM Event E, Celebrity C
WHERE E.CelebrityId = C.CelebrityId AND C.FeeCharged >
200000;
(½ mark for ​SELECT​)
(½ mark for ​FROM​)
(½ mark for correct use of Join)
(½ mark for ​FeeCharged > 200000​ condition)
7 (a) List two disadvantages of e-governance to a disabled person. 2

Page #14/15
CBSE AISSCE 2017 Marking Scheme for Informatics Practices
(Sub Code:065 Paper Code 90/Outside Delhi)

Ans 1. There is usually a lack of internet/computer literacy among the disabled.

2. Personal interaction is limited in e-governance, so the human element
bond is missing which disabled people need.
(1 mark each for any two disadvantages )
(b) How can online retailers usually offer customers a wider range of products at 1
prices that are lesser than those of traditional stores?

Ans Online retailers usually offer customers a wider range of products at prices
that are lesser than those of traditional stores ​because they have fewer
infrastructural and operational costs.
(1 mark for stating any valid reason)
(c) ​Ms. Cathy is creating a form for Vidya University Sports Council application. 2
Help her to choose the most appropriate controls from ListBox, ComboBox,
TextField, TextArea, RadioButton, CheckBox, Label and Command Button for
the following entries.
S.NO. FUNCTION
1. To let the user enter NAME
2. To let the user enter MOBILE NUMBER
3. To let the user choose one PROFESSION out of the categories :
Teaching / Non -Teaching/Research Scholar
4. To let the user select facilities out of Gym, Yoga, Table Tennis,
Badminton and Aerobics. More than one facility may be chosen

Ans
S.NO. FUNCTION CONTROL

1. To let the user enter NAME TextField

2. To let the user enter MOBILE NUMBER TextField

To let the user choose one PROFESSION out of the RadioButton
3. categories : Teaching / Non -Teaching/Research / Combo
Scholar Box

To let the user select facilities out of Gym, Yoga,

CheckBox /
4. Table Tennis, Badminton and Aerobics. More than
List Box
one facility may be chosen

( ½ mark for each correct answer)

Page #15/15