Class 11 IP Sample Paper Term 2 Set 3

TERM II – 2021-22
Time Duration – 2 hours Maximum Marks : 35

General Instructions:-
1.This question paper contains three parts A,B,C Each part is compulsory .
2.Section A – contains 7 questions each of 2 marks
3. Section B- contains 3 questions each of 3 marks
4. Section C -contains 3 questions each of 4 marks
5. In 4 questions (1,3,8,13) internal choices have been given
6. All questions are compulsory

SECTION A

(2 marks each)

1. What do you mean by constraints ?

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

Or

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

Ans: A Foreign Key is a field (or collection of fields) in one table, that refers to the Primary Key in another table.

The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.

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

Ans:

Duplication of data.

Data redundancy arises when the same data piece is stored in two or more separate places, either by accident or intentionally.

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.

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

ii) Both (A) and (R) are True, but (R) is not the correct explanation of (A).

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

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

a) Assertion (A )- The SQL keyword Like only used with wildcards
Reason ( R ):- ‘_’ underscore and “%” percent are two symbols used in wildcards .

Ans: Both (A) and (R) are True, but (R) is not the correct explanation of (A).

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

Ans: _ means atleast single alphabet , % either no character or multiple character .

Or

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 .

Ans:(A) is true, but (R) is false 

b) Is Distinct and Unique keywords functionality are same ?

Ans: Not distinct and unique are not same

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

Ans:

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 (*) symbolin query ?

Ans: i) All data from the table

6. ‘All primary keys are candidate keys but all candidate keys are not primary.’ State True or False and Justify your answer.

Ans: True

Justification: The primary key uniquely identifies a record or row in the table and it follows one key per table approach. The candidate key also uniquely identifies a record or row in the table but it can have one key per table or many keys per table

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

i) Google drive

ii) Microsoft Azure

iii) i Cloud

iv) All of these These

Ans: iv) All of these These

b) The process of encrypting and decrypting information :-

i) Decentralized Application

ii) Cryptocurrency

iii) Block

iv) Cryptography

Ans: iv) Cryptography

SECTION B

(3 marks each)

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

Ans:

Degree – No. of columns

Cardinality – No. of columns

Tuple –  Rows

Attribute – column

Or

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

Ans: A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, date and time data, binary strings, and so on. 

Char – fixed length

Varchar – variable length

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

Ans:

Create table salary( sidint(4) primary key,basic decimal(5,2),allowance decimal(5,2) ,comm_perint(2) );

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

Ans: 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

1. Volume

2. Velocity

3. Variety

4. Veracity

5. Value

SECTION C

(4 marks each)

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.

Ans: candidate keys – AadhaarNumber,EmployeeID

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

Ans: Table – Dependant , Primary key – EmployeeID

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

Ans: EMPLOYEE-5

DEPENDENT -3

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

Ans: Alter —- modify

12. What do you mean by alter command ?Explain with syntax. How alter is different from Update command?

Ans:

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 :-

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”;

Ans: select * from employee where name like “D%”;

ii) To insert a record in employee table with the values :

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

Ans: insert into employee values(11,”sita”,45000,”2000-02-19”,35,”F”);

iii) select department , name from employee where department not in( “HR”, “computer”);

Ans: 

iv) select age from employee order by age desc;

Ans: 

v) select max(basic) from employee;

Ans: 114000

vi)select count(distinct department) from employee;

Ans: 5

Or

Your school management has decided to organise a cricket match between students of class 11th and 12th. All the students are divided into 4 teams: team rockstars, team big gamers, team magnet and team current.
During the summer vacation various matches or to be held between these teams help your sports teacher to do the following

i) Create a table team with the following consideration

• It should have a column team ID for storing an integer with the value between 1 and 9 which refers to unique identification of a team
• Each team ID have its associated names (team name) which should be a integer of length not less than 10 characters.

Ans: Create table Team(TeamIDint(9) primary key,TeamName varchar(10));

ii) Give the statement to make team id as a primary key,

Ans: Alter table Team add primary key(TeamID);

iii) Show the structure of a table team using SQL command

Ans: 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 )

Ans:

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”)