HOLY KRISHNA’S COLLEGE
PERIODIC TEST-IV (2024-25)
CLASS-XI SUBJECT- COMPUTER SCIENCE MM : 20
Name…………......………….…….………….Roll No….………..……..Section……………………
Note:
a. The question numbers 1 to 10 carry ½ mark each.
b. Question number 11 carry 1 mark.
c. Question number 12 carry 2 marks.
d. The question numbers 13 to 15 carry 4 marks each.
1. Which SQL operator performs pattern matching?
a. BETWEEN operator c. LIKE operator
b. EXISTS operator d. =
2. Write the name of the command to display the structure of a table in a
database.
3. In which datatype the value stored is padded with spaces to fit the
specified length.
a. DATE c. VARCHAR
b. CHAR d. FLOAT
4. Which aggregate function can be used to find the cardinality of a table?
a. sum() c. count()
b. max() d. avg()
5. Assertion (A): A SELECT command in SQL can have both WHERE and
HAVING clauses.
Reasoning (R): WHERE and HAVING clauses are used to check conditions,
therefore, these can be used interchangeably
6. What constraint should be applied on a table column so that duplicate
values are not allowed in that column, but NULL is allowed.
7. What constraint should be applied on a table column so that NULL is not
allowed in that column, but duplicate values are allowed
8. Write an SQL command to remove the Primary Key constraint from a table,
named MOBILE. M_ID is the primary key of the table.
9. Write an SQL command to make the column M_ID the Primary Key of an
already existing table, named MOBILE.
10. Explain candidate key.
11. Consider the following two commands with reference to a table,
named Employee having a column named Department:
a. Select count(Department) from Employee;
b. Select count(*) from Employee;
If these two commands are producing different results,
(i) What may be the possible reason?
(ii) Which command (a) or (b) might be giving a higher value?
12.Consider the table, BOOK and MEMBER given below
TABLE : BOOK
CODE BNAME TYPE
F101 The priest Fiction
L102 Easy Python Programming
C101 Juman Ji Thriller
F102 Untold Story Fiction
C102 War Stories Comic
Table: MEMBER
MNO MNAME CODE ISSUEDATE
M101 SNEH SINHA L102 2022-10-13
M103 SARTHAK F102 2021-02-23
M102 SARA KHAN C101 2022-06-12
What will be the output of the following statement?
SELECT * FROM BOOK NATURAL JOIN MEMBER;
13.Write the output of the queries (i) to (iv) based on the table
Table: Employee
EID Name DOB DOJ Salary Project
E01 Mridul 1990-07-12 2015-01-21 150000 P01
E02 Kanika 1992-06-21 2015-02-01 125000 P02
E03 Anushka 1996-11-15 2018-08-19 135000 P04
E04 Sumit 1991-10-25 2018-08-19 100000 P02
E05 Sarojini 1993-12-16 2018-10-19 85000 P01
i SELECT NAME, PROJECT FROM EMPLOYEE ORDER BY NAME DESC;
ii SELECT NAME, SALARY FROM EMPLOYEE WHERE NAME LIKE 'A%';
iii SELECT NAME, DOJ FROM EMPLOYEE WHERE SALARY BETWEEN 100000
AND 200000;
iv. SELECT * FROM EMPLOYEE WHERE PROJECT = 'P01';
14.Consider the following tables – FACULTY and COURSES :
Table: FACULTY
FID FNAME LNAME JOINDATE SALARY
F01 Anishma Garg 2000-12-14 32000
F03 Bhumi Goel 2001-08-10 15000
F04 Neha Verma 2000-05-17 27000
F05 Meenu Sharma 2006-07-11 30000
Table: COURSES
C_ID FID CNAME FEES
C11 F01 Grid Computing 40000
C12 F04 Python 17000
C13 F03 C++ 8000
C14 F04 Computer Network 15000
C15 F01 HTML 12000
C16 F05 Data Science NULL
What will be the output of the following statement?
a. SELECT FID, MIN(FEES), MAX(FEES) FROM COURSES GROUP BY FID;
b. SELECT AVG(SALARY) FROM FACULTY WHERE FNAME LIKE '%a';
c. SELECT FNAME, CNAME FROM FACULTY F, COURSES C WHERE
F.FID=C.FID AND COURSES.FID='F04';
d. SELECT FNAME, CNAME , FEES FROM FACULTY F , COURSES C WHERE
F.FID = C.FID AND FEE>15000;
15. Navdeep creates a table RESULT with a set of records
to maintain marks secured in Sem1, Sem2, Sem3 and
their divisions. After the creation of the table, he
entered data of 7 students in the table as follows:
ADNO ROLLNO SNAME SEM1 SEM2 DIVISION
123 101 KARAN 366 410 I
245 102 NAMAN 300 350 I
128 103 ISHA 400 410 I
129 104 RENU 350 357 I
234 105 ARPIT 100 75 IV
187 106 SABINA 100 205 II
181 107 NEELAM 470 450 I
Based on the data given above answer the following questions:
i. Identify the columns which can be considered as candidate
Keys
ii. If 2 more columns are added and 3 rows are deleted from the
table result, what will be the new degree and cardinality of the
above table?
iii. Write a statement to increase the SEM2 marks by 3% for the
students securing marks between 70 to 100.
