ST.

XAVIER’S SENIOR SECONDARY SCHOOL, DELHI – 110 054

Class 11 Time: 3 hrs.
12-9-2024 MID TERM EXAMINATION - INFORMATICS PRACTICES Max. Marks : 70

General Instructions:
i) This question paper contains five sections, Section A to E.
ii) All questions are compulsory.
iii) Section A has 18 questions carrying 01 mark each.
iv) Section B has 07 Very Short Answer type questions carrying 02 marks each.
v) Section C has 05 Short Answer type questions carrying 03 marks each.
vi) Section D has 02 questions carrying 04 marks each.
vii) Section E has 03 questions carrying 05 marks each.

SECTION – A
1. What will be the output of the following query?
select MOD(17, 3);
i. 5 ii. 2 iii. 0 v. 4

2. Which of the following is not an aggregate function in MySQL?

i. AVG( ) ii. MAX( ) iii. LCASE( ) iv. MIN( )

3. What will be the output of the following query?

select substr(“G20 2023 INDIA”, 5, 4);
i. G20 2 ii. 2023 iii. INDI iv. 023

4. Which of the following clause cannot work with select statement in MySQL?
i. FROM ii. INSERT INTO iii. WHERE iv. ORDER BY

5. Which MySQL string function in used to extract a substring from a given string based on specified
startup position and no of character given?
i. SUBSTRING_INDEX( ) ii. LENGTH( )
iii. MID( ) iv. TRIM( )

6. The select statement when combined with __________clause, returns records without repetition.
i. DISTINCT ii. SECRIBE iii. UNIQUE iv. NULL

7. In SQL, the aggregate function which will display the cardinality of the table is _______________.
i. sum( ) ii. count(*) iii. avg( ) iv. sum(*)

8. Which of the following is not a DDL command in SQL?

i. DROP ii. CREATE iii. UPDATE iv. ALTER

9. A table consists of 5 rows and 7 columns Later on 3 columns were added and 2 rows deleted. After
some time 5 new rows were added. What will be the degree and cardinality?

10. State True or False

Delete is a DDL command

11. Which SQL operator is used to check values based on its pattern specified.
i. LIKE ii. BETWEEN iii. IN iv. IS
Std. 11 -2- INFORMATICS PRACTICE

12. _____________ clause is used to arrange the records in ascending order or descending order.

13. What do you mean by NULL?

14. How many types of relational operators are there in MySQL.

i. 6 ii. 7 iii. 8 iv. 9

15. Which command shows the structure of table in MySQL?

16. Write any one difference between AND and OR operator?

17. Consider the following statements

Statement A: A table is also known as Relation in RDBMS
Statement B: Table can have only one primary key.
(choose the correct option)
i. Statement A is correct and statement B is correct explanation of A
ii. Statement A is correct but statement B is incorrect
iii. Statement A is incorrect but statement B is correct.
iv. Statement A and B both are correct.

18. Consider the following statements:

Statement A: count(columnname) function ignore distinct values.
Statement B: Distinct clause ignores distinct values.
(choose the correct option)
i. Statement A is correct and statement B is correct explanation of A
ii. Statement A is correct but statement B is incorrect
iii. Statement A is incorrect but statement B is correct.
iv. Statement A and B both are correct.

Section B

19. What will be the output of the following SQL queries:

i. Select RIGHT(“CHANDRAYAN 3”, 4);
ii. Select ROUND(76345.456, 2);

20. Write any two differences between Delete and Drop TABLE commands in MySQL.

21. Create a table sports with the following structure

Columnname Datatype Size
Gameid int
Gamename varchar 20

22. Write any two differences between single row and multi row functions.

23. Write the output produced by following SQL commands.

i. Select Length (“Informatics Practices”)
ii. Select year (“1979-11-26”), month (“1979-11-26”), monthname (“1979-11-26”);
Std. 11 -3- INFORMATICS PRACTICE

24. Consider a table A with 4 rows and 5 columns and table B with 6 rows and 3 columns. What will be
degree and cardinality of the new relation after Cartesian product

25. Answer the questions based on the data entered in STOCK table:
No ItemName Price Discount
101 DVD 170 10
102 Speaker 250 20
103 DVD 170 100
i. Identify the primary key.
ii. Write the domain of attribute Price.
Section C
26. Consider the given table and write the following queries in MySQL:
Table: Sports

Sid SName Fees DateofPlay Coachid

1 Karate 1200 2024-08-24 S1
2 Football 1800 2024-09-13 S2
3 Cricket 1500 2024-06-14 S3
4 Lawn Tennis 2500 2024-09-25 S4
5 Badminton 1800 2024-10-24 S5
i. To display Sid and name of these sports which are to be played in the month of
September.
ii. To display all sports names in lowercase
iii. To display last two characters of all sports names whose fees is less than 1500.

27. Predict the output of the following queries based on the table sports given in Questions 26.
i. Select Ucase (Sname) from sports where sid=5;
ii. Select left (Sname, 3) from sports where DateofPlay> “2024-09-13”;
iii. Select substr(coachid,1,1) from sports where Sname=”Cricket” or Fees>=2000;

28. Answer the following questions based on the table Salesman given below.
Table: Salesman
salesman_id name city commission
5001 Neil Bhatt Delhi 0.15
5002 Rohan Malik Mumbai 0.13
5003 Ravi Mohan Delhi 0.11
5004 Mehak Rai Delhi 0.14
5005 Paul Lal Bangalore 0.13
5006 Raman Sen Kolkata 0.12
i. How many tuples does the given table have?
ii. Write MySQL query to display name concatenated with city.
iii. Write the MySQL query to display all the records in descending order of commission.
Std. 11 -4- INFORMATICS PRACTICE

29. Consider the table Stationery given below and write the output of the SQL queries that follow.
Table: Stationery

i) SELECT SUM(QTY), AVG(QTY) FROM STATIONERY;

ii) SELECT ITEMNO, ITEM FROM STATIONERY WHERE DISTRIBUTOR = "Classic Plastics"
AND PRICE > 10;
iii) SELCET ITEM, QTY * PRICE FROM STATIONERY WHERE ITEMNO = 402;

30 Consider the table Rent_cab, given below:

Table: Rent_cab
Vcode VName Make Color Charges
101 Big car Carus White 15
102 Small car Polestar Silver 10
103 Family car Windspeed Black 20
104 Classic Studio White 30
105 Luxury Trona Red 9

Based on the given table, write SQL queries for the following:
i) Display vehicle name starting with B or ending with r.
ii) Increase the charges of all the cabs by 10.
iii) Delete all the cabs whose maker name is “Carus”.

SECTION D
31. Ms. Sridevi is a placement head in a reputed engineering institute and has created the following table
to store the records of students getting placement in various companies:

Based on the given table, help Ms. Sridevi to write SQL queries for performing the following tasks:
i) To list names of those companies where department is either Marketing or Networking.
ii) To display the joining month name for Rising Star company.
Std. 11 -5- INFORMATICS PRACTICE

Ms. Sridevi has written following queries. Write the output of each query:
iii) SELECT INSTR(CompanyName,"R") FROM Placement where vacancies
>=20;
iv) SELECT CompanyName FROM Placement WHERE Vacancies < 20 AND Appeared >300;

32. Consider the tables GAMES and PLAYERS given below:

Table: GAMES
GCode GameName Type Number PrizeMoney
101 Carrom Board Indoor 2 5000
102 Badminton Outdoor 2 12000
103 Table tennis Indoor 4 NULL
104 Chess Indoor 2 9000
105 Lawn tennis Outdoor 4 25000
Table: PLAYERS
PCode Name GCode
1 Nabi Ahmad 101
2 Ravi Sahai 108
3 Jatin 101
4 Nazneen 103
Write SQL queries for the following :
i) Display the maximum and minimum prize money from the table GAMES.
ii) Display prize money, name of the game, and name of the players from the tables Games
and Players.
iii) Display the types of games without repetition from table GAMES.
iv) Display the name of the game and prize money of those games whose prize money is
known.
SECTION E
33. Consider the tables Faculty and Batch given below:
Table: Faculty
F_Id FacName DoJ Qualification Salary
Emp01 Neeta Khanna 2013-07-01 MCA 85000
Emp0 Sonia Chawla 2023-05-05 MA 35000
Emp0 Sheetal 2015-06-28 MSc 90000
Emp0 Bindu 2016-03-30 M.Com 80000
Emp0 Sunidhi 2002-06-28 BA 100000
Emp0 Ashish 1999-07-01 B.Com 120000
Std. 11 -6- INFORMATICS PRACTICE

Write SQL queries for the following:

(i) Display name and salary of all faculties in alphabetical order of their names.
(ii) Display details of faculties who joined on Monday.
(iii) Display names of faculties, their salary and BatchName from both the tables.
(iv) Display the details of all faculties whose salary is more than 60000 and have joined
before the year 2007.
(v) Display the name of faculty who is taking TXAlpha Batch.

34. Write the following queries based on the table Faculty and Batch table given in Q33.
(i) Display maximum days per week for each F_Id from the table Batch.
(ii) Display names of faculties after removing leading and trailing spaces.
(iii) Display total number of records in the table Faculty.
(iv) Increase the salary by 25% of those employees whose qualification is MCA.
(v) Delete the records of batches whose subject is English.

35. Consider the following tables DRESS. Write outputs of SQL commands for the statements (i) to (v)
Table :DRESS

(i) SELECT * FROM DRESS WHERE MCODE= ‘M001’;

(ii) SELECT DESCRIPTION,PRICE FROM DRESS WHERE PRICE>=1250;
(iii) SELECT * FROM DRESS WHERE DESCRIPTION LIKE "%T";
(iv) SELECT DISTINCT PRICE FROM DRESS;
(v) SELECT DCODE, PRICE+100 FROM DRESS WHERE MCODE LIKE”%1”;

**************