OXALISS INTERNATIONAL SCHOOL (CBSE), THATCHUR.

Computer Science (083)

Class: XII Session: 2024-25
WORKSHEET - 2

SECTION – A

1 Which operator performs pattern matching ? 1

a) BETWEEN operator b)LIKE operator c)EXISTS operator d) None of these
2 Which command in SQL is used to display structure of a table? 1
3 Re-write the following DML query after removing the error: 1
UPDATE student MODIFY age = age+1 WHERE class=12;
4 Which keyword eliminates the redundant data from a query result ? 1
5 Consider following SQL statement. What type of statement is this? 1
SELECT * FROM employee;
a) DML b) DDL c) DCL d) Integrity constraint
6 Say True / False: 1
Constraints can be defined with CREATE TABLE command.
7 The ............... clause of SELECT query allows us to select only those rows in the result 1
that satisfy a specified condition.
a) Where b) from c) having d) like
8 Which of the following queries contains an error? 1
a) Select * from emp where empid = 10003;
b) Select empid from emp where empid = 10006;
c)Select empid from emp;
d) Select empid where empid = 1009 and lastname = 'GUPTA';
Q9 and 10 are ASSERTION AND REASONING based questions. Mark the correct choice
as
(a) Both A and R are true and R is the correct explanation for A
(b)Both A and R are true and R is not the correct explanation for A
(c) A is True but R is False
(d)A is false but R is True
9 Assertion. The PRIMARY KEY and UNIQUE constraints are the same. 1
Reason. The columns with PRIMARY KEY or UNIQUE constraints have unique values
for each row.
10 Assertion. Both DELETE and DROP TABLE carry out the same thing — deletion in 1
tables.
Reason. The DELETE command deletes the rows and DROP TABLE deletes the whole
table.

SECTION – B

11 Shanya Khanna is using a table EMPLOYEE. It has the following columns: 2

Admno, Name, Agg, Stream [column Agg contains Aggregate marks]
She wants to display highest Agg obtained in each Stream.
She wrote the following statement:
SELECT Stream, MAX(Agg) FROM EMPLOYEE;
But she did not get the desired result. Rewrite the above query with necessary changes to
 help her get the desired output.
12 State difference between date functions NOW() and SYSDATE() of MySql. 2
13 Categorize the following commands as DDL or DML: INSERT, UPDATE, ALTER, DROP 2
14 Compare Char and Varchar datatypes. 2
15 Write a query to display EName and Sal of employees whose salary is greater than or 2
equal to 2200 from table Empl.
SECTION – C

16 Consider the table CLUB given below and write the output of the SQL queries that follow. 3

CID CNAME AGE GENDER SPORTS PAY DOAPP

5246 AMRITA 35 FEMALE CHESS 900 3/27/2006
4687 SHYAM 37 MALE CRICKET 1300 4/15/2004
VOLLEY
1245 MEENA 23 FEMALE 1000 6/18/2007
BALL
1622 AMRIT 28 MALE KARATE 1000 9/5/2007
1256 AMINA 36 FEMALE CHESS 1100 8/15/2003
1720 MANJU 33 FEMALE KARATE 1250 4/10/2004
2321 VIRAT 35 MALE CRICKET 1050 4/30/2005
(i) SELECT COUNT(DISTINCT SPORTS) FROM CLUB;

(ii) SELECT CNAME, SPORTS FROM CLUB WHERE DOAPP<"2006-04-30" AND

CNAME LIKE "%NA";

(iii) SELECT CNAME, AGE, PAY FROM CLUB WHERE GENDER = "MALE" AND PAY
BETWEEN 1000 AND 1200;
17 Consider the table Personal given below: Table: Personal 3

P_ID Name Desig Salary Allowance

P01 Rohit Manager 89000 4800
P02 Kashish Clerk NULL 1600
P03 Mahesh Supervisor 48000 NULL
P04 Salil Clerk 31000 1900
P05 Ravena Supervisor NULL 2100

Based on the given table, write SQL queries for the following:
(i) Increase the salary by 5% of personals whose allowance is known.
(ii) Display Name and Total Salary (sum of Salary and Allowance) of all personals. The
column heading ‘Total Salary’ should also be displayed.
(iii) Delete the record of personals who have salary greater than 25000
18 (b) Write the output of the queries (i) to (iv) based on the table, TECH_COURSE given 3
below: Table: TECH_COURSE
 (i) SELECT DISTINCT TID FROM TECH_COURSE;
(ii) SELECT TID, COUNT(*), MIN(FEES) FROM TECH_COURSE ;
(iii) SELECT CNAME FROM TECH_COURSE WHERE FEES>15000 ORDER BY
CNAME;
(iv) SELECT AVG(FEES) FROM TECH_COURSE WHERE FEES BETWEEN 15000
AND 17000;
19 Write the outputs of the SQL queries (I) to (iii) based on the relations Emp and dept given 3
below:

i) SELECT dno, MAX(salary) FROM emp GROUP BY dno;

ii) SELECT COUNT(DISTINCT gender) from emp;
iii)SELECT ename,dname,salary FROM emp,dept WHERE emp.dno = dept.deptno AND
location='B1';
20 Given the following table : 6*1/2
=3
Table : STUDENT

No. Name Stipend Stream AvgMark Grade Class

1 Karan 400 Medical 78.5 B 12B
2 Divakar 450 Commerce 89.2 A 11C
3 Divya 300 Commerce 68.6 C 12C
4 Arun 350 Humanities 73.1 B 12C
5 Sabina 500 Nonmedical 90.6 A 11A
6 John 400 Medical 75.4 B 12B
7 Robert 250 Humanities 64.4 C 11A
8 Rubina 450 Nonmedical 88.5 A 12A
9 Vikas 500 Nonmedical 92 A 12A
10 Mohan 300 Commerce 67.5 C 12C
 Give the output of following SQL statements :

1. SELECT MIN(AvgMark) FROM STUDENT WHERE AvgMark < 75 ;

2. SELECT MAX(AvgMark) FROM STUDENT;
3. SELECT SUM(Stipend) FROM STUDENT WHERE Grade = 'B' ;
4. SELECT AVG(Stipend) FROM STUDENT WHERE Class = '12A' ;
5. SELECT COUNT(DISTINCT) FROM STUDENT ;
6. SELECT COUNT(*) FROM STUDENT ;

21 i) Create table Employee as per following Table Instance Chart. 2+1

EmpAddr
Column Name EmpID EmpName EmpSal DeptID
ess
Key Type Primary Foreign

Nulls/Unique NOT NULL

VARCH NUMB
Datatype NUMBER VARCHAR VARCHAR
AR ER
Length 6 20 30 9, 2 2

ii) Write the difference between DROP and DELETE command.

SECTION – D

22 Consider the following table named SBOP with details of account holders. Write 1*4=4
commands of MySql for (i) to (iv) and output for (v) to (vii).
TABLE: SHOP

(i) To display Accountno, Name and DateOfopen of account holders having

transactions more than 8.
(ii) To display all information of account holders whose transaction value is not
mentioned.
(iii) To add another column Address with datatype and size as VARCHAR(25).
(iv) To display the month day with reference to DateOfopen for all the account
holders.
23 Consider the following table named EXAM with details of marks. Write command 1*4=4
of MySQL for (i) to (iv) and output for (v) to (vii).
Table EXAM
 (i) To display all information of the students of humanities in descending order of
percentage.
(ii) To display Adno, Name, Percentage and Stream of those students whose name is
less than 6 characters long.
(iii) To add another column Bus_Fees with datatype and size as Decimal(8,2).
(iv) To increase percentage by 2% of all the Humanities students.
24 Navdeep creates a table RESULT with a set of records to maintain the marks 2+2=4
secured by students in Sem 1, Sem2, Sem3 and their division. After creation of the
table, he has entered data of 7 students in the table.
Table: RESULT

Based on the data given above answer the following questions:

i. Identify the most appropriate column, which can be considered as Primary key.
Justify your answer.
ii) Differentiate between Grand and Revoke command.