Page 26 - IP

CBSE AISSCE 2018 Marking Scheme for Informatics Practices
(​Sub Code: 065 Paper Code 90​)

Ans CREATE TABLE Inventory
(
MaterialId INTEGER PRIMARY KEY,

Material Varchar(50)NOT NULL,
Category Char,
DatePurchase Date
);
(½ mark for​ ​CREATE TABLE​ ​)
(½ mark for ​PRIMARY KEY​ ​constraint)
(½ mark for ​NOT NULL​ constraint)
(½ mark for Column Names with Data Types)

(b) Consider the following tables PATIENT and TEST and answer the questions that
follow:
​Table : ​PATIENT
PCODE NAME PHONE DTADMIT TESTID
6473 Amit Sharma 912356899 19-JUN-2017 T102
7134 Rose Mathew 886744536 12-NOV-2017 T101
8786 Tina Sharma Arora 889088765 06-DEC-2017 T102
6477 Vijay Shah 714567445 07-DEC-2017 T502

7658 Venkat Fazal 865545343 31-DEC-2017 T101

Note :​NAME​ holds the Names of patients.
​DTADMIT​ holds Dates on which patient was admitted to hospital.
​TESTID​ holds Ids of Medical tests done on patients.
Table: ​TEST

TESTID TESTNAME COST
T101 Platelet Count 200.00
T102 Hemogram 190.00
T301 Malaria Detection 350.00
T502 Glucose Test 150.00
Name the Primary keys in both the tables and foreign key in​ ‘​PATIENT​’​ table​.
State the reason for your choice. 2
Ans Primary key (Table : ​PATIENT​ ) - ​PCODE​ as it is unique to each row of table

Primary key (Table : ​TEST​) – ​TESTID​ as it is unique to each row of table
Foreign key in table​ PATIENT​ : ​TESTID​ ​as it links the two tables and is Primary key in
table ​TEST​.
(½ mark each for identifying ​PRIMARY KEY​ of both the tables)
(½ mark for ​FOREIGN KEY​ ​identification)
(½ mark for mentioning ANY one reason for ​PRIMARY​ or ​FOREIGN KEY​)

(c) With reference to the above given tables (in Q6 b), Write commands in ​SQL for (i) to
(iii)
(i) To display Names of Patients, ​TESTID and Test names for those Patients who were 2
admitted between ​‘01-DEC-2017’​ and ​‘15-DEC-2017’​ (both dates inclusive).

Page 11 of 13