Class XI - Informatics Practices - 3 - MS

Practice Paper

TERM II (2021 – 2022)

Class – XI

Informatics Practices (065)

Time: 2 hours Maximum Marks: 35

General Instructions:

(i) The question paper is divided into 3 sections – A, B and C

(ii) Section A, consists of 7 questions (1 – 7). Each question carries 2 marks

(iii) Section B, consists of 3 questions (8 – 10). Each question carries 3 marks

(iv) Section C, consists of 3 questions (11 – 13). Each question carries 4 marks

(v) Internal choices have been given for question numbers – 1, 8 and 13.

SECTION – A

1. What do you mean by constraints?

Constraints are restriction/rules for data in a tables – Primary key, not null, unique, check, default

What do you mean by foreign key? Explain with suitable example?

Foreign key – enables referential integrity

2. What do you mean by Data Redundancy? How this can be solved?

Data Redundancy – repeated data stored in different places

3. Directions – In the question given below there are two statements marked as Assertion (A) and Reason (R). Read the statements and choose the correct option

a) Both (A) and (R) are True, and (R) is the correct explanation of (A)

b) Both (A) and (R) are True, and (R) is not the correct explanation of (A)

c) (A) is true, but (R) is false

d) (A) is false, but (R) is true

a) Assertion: The SQL keyword Like only is used with wildcards

Reason: ‘_’ underscore and “%” percent are two symbols used in wildcards.

b) Write the use of “_” and “%” symbols in SQL?

“_” means atleast single alphabet, “%” either no character or multiple character

a) Assertion (A): The qualifier DISTINCT must be used in an SQL statement when we want to eliminate duplicate rows.

Reason (R): DISTINCT only works with numeric datatype only.

a) Both (A) and (R) are True, and (R) is the correct explanation of (A)

b) Both (A) and (R) are True, and (R) is not the correct explanation of (A)

c) (A) is true, but (R) is false

d) (A) is false, but (R) is true

b) Is Distinct and Unique keywords functionality are same?

Not distinct and unique are not same.

4. What do you mean by DDL and DML? Give some examples?

DDL – Data Definition Language

DML – Data Manipulation Language

5. The symbol Asterisk (*) in a select query retrieves ________

i) All data from the table ii) Data of primary key only

iii) NULL data iv) None of the mentioned

How to replace this Asterisk (*) symbol in query?

by use Select all query

6. ‘All primary keys are candidate keys but all candidate keys are not primary’.

State True or False and Justify your answer

True

7. a) ________ is the cloud based services that you are using at present.

i) Google drive ii) Microsoft Azure iii) iCloud iv) All of these

b) The process of encrypting and decrypting information: –

i) Decentralized Application ii) Cryptocurrency iii) Block iv) Cryptography

SECTION – B

8. What do mean by Degree and Cardinality? How is Tuple different from Attribute?

Degree is the number of attributes or columns present in a table. Cardinality is the number of tuples or rows present in a table.

Tuple – Rows

Attributes – Columns

What do you mean by datatype? How char is different from varchar?

A particular kind of data item as defined by the values it can take , the programming language used or to operations that can be performed on it.

The basic difference between Char and Varchar is that: char stores only fixed-length character string data types whereas varchar stores variable-length string where an upper limit of length is specified

Create the table salary based on the above given instance chart.

Create table salary (sid int(4) primary key, basic decimal (5,2), allowance decimal (5,2), comm_per int(2));

10. What do mean by Big Data? Mention five characteristics of Big Data?

Big data not only represents voluminous data, it also involves various challenges like integration, storage, analysis, searching, processing. Transfer, querying and visualisation of such data. Big data sometimes hold rich information and knowledge which is of high business value, and therefore there is a keen effort in developing software and methods to process and analyse big data.

Characteristics:

Volume
Velocity
Variety
Veracity
Value

SECTION – C

11. An organisation wants to create a database EMP-DEPENDENT to maintain following details about its employees and their dependent.

EMPLOYEE(AadharNumber, Name, Address, Department, EmployeeID)

DEPENDENT(EmployeeID, DependentName, Relationship)

a) Name the attributes of EMPLOYEE, which can be used as candidate keys.

candidate keys – AadharNumber, EmployeeID

b) The company wants to retrieve details of dependent of a particular employee. Name the tables, and find primary key of that table.

Table – Dependent, Primary key – EmployeeID

c) What is the degree of EMPLOYEE and DEPENDENT relation?

Employee – 5

Dependent – 3

d) How to change data of column ‘Relationship’ from char to varchar.

12. What do you mean by alter command? Explain with syntax.

How Alter is different from Update command?

Alter – DDL (change schema of table and degree gets change if used for add, drop columns)

Update – DML (Schema does not change)

13. Consider the following table employee: –

Write SQL statement for the queries (i) and (ii) and output for (iii) to (vi)

(i) To display all details of those employees whose name starts from “D”

Select * from employee where name like ‘D%’;

(ii) To insert a record in employee table with value:

(11, sita, 45000, Accounts, 2000-02-19, 35, F)

Insert into employee values (11, “sita”, 45000,, “2000-02-19”, 35, “F”);

(iii) Select department, name from employee where department not in (“HR”, “Computer”);

Output

(iv) Select age from employees order by age desc;

(v) Select max(basic) from employee;

114000

(vi) Select count (distinct department) from employee;

Your school management has decided to organise a cricket match between students of class 11^th and 12^th. the students are divided into 4 teams: team rockstars, team big gamers, team magnet and team current.

During the summer vacation various matches are to be held between these teams. Help your sports teacher to do the following:

I. Create a table team with the following consideration

a) It should have a column team ID for storing integer with the value between 1 and 9 which refers to unique identification of a team.

Create table Team(TeamID int(9) primary key, teamName varchar (10));

b) Each team ID have its associated names (team name) which should be an integer of length not less than 10 characters.

II. Give the statement to make team id as a primary key.

Alter table Team add primary key (TeamID);

III. Show the structure of a table team using SQL command

Desc Team;

IV. As for the performance of the student four teams will be formed as given below

Insert these 4 rows in the team table

Row 1 – (1, team rockstar)

Row 2 – (2, team big gamers)

Row 3 – (3, team magnet)

Row 4 – (4, team current)

Insert into Team values(1, “Team Rockstar”);

Insert into Team values(2, “Team Big Gamers”);

Insert into Team values(3, “Team Magnet”);

Insert into Team values(4, “Team Current”);

Class XI – Informatics Practices – 3 – MS