Class: All Sr INTERNAL EXAMINATION Date: 18-03-2026
Time: 03:00 Hr Revision Test -2 SQL Answer Key Max. Marks: 70 M
====================================================================================================
Informatics Practices(IP)
SECTION – A
I. Answer the following questions: 21 X 1 = 21 M
1. Which of the following clauses is used to sort the result set in a SQL statement?
a) SORT BY b) ORDER BY c) GROUP BY d) ARRANGE BY
2. Identify the SQL command used to create a relation (table) in relational database.
a) DROP TABLE b) CREATE TABLE
c) ALTER TABLE d) GENERATE TABLE
3. State whether the following statement is True or False:
In SQL, the aggregate functions AVG, MIN and MAX functions only work on numeric
data. True
4. Which one of the following functions is used to count the no of rows from the given table in
MySQL?
a) COUNT(* ) b) CARDINALITY( ) c) COUNT(Column name ) d) All the above
5. Match the following SQL functions/clauses with their descriptions:
a) P-2, Q-4, R-3, S-1 b) P-2, Q-4, R-1, S-3
c) P-4, Q-3, R-2, S-1 d) P-4, Q-2, R-1, S-3
6. The purpose of distinct clause in a SQL statement is to:
a) show all the rows in a column of table
b) show all duplicate values in a column
c) Remove all duplicate values in a column
d) sort all the results based on a column
7. SQL function to find the lowest among group of values
a) minimum( ) b) min( ) c) low( ) d) lowest( )
8. Which clause is used with aggregate functions?
a) GROUP BY b) ORDER BY c) BETWEEN d) IN
9. If a column Fee contains the following data set (75, null, null, 100, 200), what will be the
output of the following query.
select count(Fee) from emp;
a) 5 b) 2 c) 3 d) output cannot be predicted
[Link] column “Margin “contains the data set(4,NULL,NULL,12). What will be the output of
after the execution of the given query?
SELECT AVG(Margin) FROM SHOP;
a) 4.0 b) 8.0 c) 16.0 d)12.0
11._____ command is used to modify records in the MySQL table.
a) ALTER b) UPDATE c) MODIFY d) SELECT
[Link] whether the following statement is True or False:
In SQL, The HAVING clause is used to filter data on groups formed by GROUP BY
clause. True
[Link] one of the following is a string function?
a) COUNT() b) SUBSTR() c) MOD() d) MAX()
[Link] MySQL function returns the week day name corresponding to Date value supplied
as argument.
a) weekday() b) dayname() c) dayofweek() d) weekofday()
[Link] the output of the following query: SELECT MOD (6, 0);
a). 0 b) Null c) NaN d) 4
16. In SQL, LEFT ( ) function is equivalent to :
a) MID ( ) b) TRIM( ) c)INSTR( ) d) Both i and ii
[Link] one of the following functions is used to count the no. of rows from the given table
in MySQL?
a) COUNT(* ) b) CARDINALITY( )
c) COUNT(Column name ) d) All the above
18. With reference to SQL, identify the invalid data type for TEXT values.
a) CHAR b) VARCHAR c) FIXCHAR None of the above
19. Expand TCL:
Q20 and 21 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
20. Assertion ( A ) : Constraints are some restrictions enforced on the columns of a relation.
Reason ( R ) : They ensure the accuracy and reliability of the data in a database. It may be a
Column Constraint or a Table Constraint.
(A) Both A and R are true and R is the correct explanation for A
21. Assertion (A): SUM() is an Aggregate function.
Reason (R) : Multiple row functions work upon group of rows and return one result for the
complete set of rows. (A) Both A and R are true and R is the correct explanation for A
SECTION – B
II. Answer the following questions 7 x 2 = 14 M
22. i) Select instr(“ComputerScience”, “put”);
ii) Select left(“ComputerScience”, 4);
23. A) A composite primary key is a primary key that's made up of multiple fields in a table.
This is used when no single field is unique enough to be the primary key by itself.
OR
B)
CHAR() VARCHAR()
i)fixed length character string,if the value is i)variable length character string, if the
shorter that the given length,blank space are value is shorter that the given length,no
added in the end but the size of column blank space is added each value stores
remains the same exactly the given length
[Link] char(30) [Link] varchar(30)
If the given input iss book the size of If the given input is book then the size of
column name is 30 column name is 4
[Link] PNAME, AVG(PRICE) From product group by PNAME having AVG(Price)>150;
25.A) ALTER TABLE EMP add PRIMARY KEY(ENO);
B) UPDATE EMP SET ENAME=’ARAVIND’
WHERE SALARY<40000 AND ENO =’E103’;
26.A "WHERE clause" in a SQL query is used to filter data within a table, allowing you to
retrieve only the rows that meet specific conditions.
eg: select * from students where marks>75;
This query will show only those students records who scored above 75 marks.
(OR)
In SQL, wildcard characters are special symbols used in pattern matching—mainly with
the LIKE operator—to search for data that matches a specific format rather than an exact
value.
% represents zero,one or many characters
Underscore (_) represents exactly one character
[Link] a relational database system, degree refers to the number of attributes or columns in a table.
For instance, a table with attributes like Name, Age, and Address has a degree of 3.
On the other hand, cardinality refers to the number of rows or tuples in a table. For example,
if a table contains 10 records, its cardinality is 10.
[Link] is because the column commission contains a NULL value and the aggregate functions
do not take into account NULL values. Thus, Command1 returns the total number of records
in the table whereas Command2 returns the total number of non-NULL values in the column
commission.
SECTION – C
III. Answer the following questions 4 x 3 = 12 M
29.(A) i) CREATE TABLE cricket
( Batsman_ID varchar ( 5 ) Primary key ,
Batsman_name varchar ( 12 ) ,
innings int ( 3 ) ,
Runs_scored int ( 6 ) ,
average float ) ;
ii) insert into cricket values ( ‘C201’ , ‘Rahul Dravid’ , 50 , 2800 , 62.22 ) ;
iii)Alter table Cricket Drop column innings;
OR
B) i) CREATE DATABASE SCTS;
(ii) CREATE TABLE ERODE
(SchoolID int(5) Primary key,
SchoolName Varchar(50),
No_of_St int(3),
DateOfEstablish date);
(iii) INSERT INTO ERODE VALUES (100,’SCTS’,200,2026-03-13);
30.(A) i. Select max(price), year from VAHAN group by year;
ii. Select company, year from VAHAN order by price desc;
iii. Select sum(qty), count (*), year from VAHAN group by year;
31.i. Select company, count (*) from flights group by company;
ii. Select upper(passenger) from Booking where destination=’MUM’;
iii. Select passenger, source,destination,model from Flights F, Booking B
where [Link] = [Link];
32.i. MONTH() GIVES MONTH IN Numbers.
Example: select MONTH(‘2021-05-11’);
gives 05 whereas
select MonthName(‘2021-05-11’); gives ‘MAY’.
ii. DAYNAME(date)-Returns the name of the weekday for date
Ex: SELECT DAYNAME('2022-02-23');
It returns the output as 'Wednesday’;
iii. February
SECTION – D
IV. Answer the following question 2x4=8M
33. (a) SELECT Type, avg(Price) FROM vehicle
GROUP BY Type
HAVING SUM(Quantity) > 20 ;
(b) SELECT Company, count ( distinct Type ) FROM vehicle GROUP BY company ;
(c) SELECT Type, sum (Price * Quantity ) FROM vehicle GROUP BY Type ;
(d) SELECT distinct Type FROM vehicle ;
34.
SECTION – E
V. Answer the following question 3 x 5 = 15
35.(A) ( a. ) SELECT emp_name , salary FROM employee ORDER BY salary desc ;
( b. ) SELECT MONTHNAME (‘2024-07-01’);
( c. ) SELECT trim (Leading ‘@’ from ‘@@@Science@@@’);
( d. ) SELECT concat ( name , class ) FROM student WHERE age = 20 or age = 22 ;
( e. ) SELECT YEAR ( ‘2020–04–12’ ) ;
OR
(B)( a. ) SELECT MOD ( 232 , 13 ) ;
( b. ) SELECT LENGTH (‘Informatics Practices’ ) ;
( c. ) SELECT ROUND ( 105.243 , –1) ;
( d. ) SELECT substr ( ‘Informatics Practices’ , 5 , 7 ) ;
( e. ) SELECT instr (‘Information is for knowledge’, ‘for’);
36. i. SELECT LCASE(LEFT(NAME,3)) FROM SALESPERSON;
ii. SELECT NAME FROM SALESPERSON
WHERE DOB=(Select MAX(DOB) from salesperson);
iii. SELECT RIGHT(PHONENO,4) FROM SALESPERSON;
iv. SELECT ZONE, COUNT(*) FROM SALESPERSON GROUP BY ZONE;
v. SELECT * FROM SALESPERSON ORDER BY NAME DESC;
37. A) a. Select Mod(75 , 45);
b. Select now();
c. Select left(‘Jaipur Region’,5);
d. Select pow(17,2);
e. Select round(1789.897,2);
OR
B) i) SELECT PR_NAME,AVG(QTY) FROM PRODUCT GROUP BY PR_NAME;
ii) SELECT LEFT(MANUFACTURER,5) FROM PRODUCT;
iii) SELECT PR_NAME, PRICE, C_NAME, CITY FROM PRODUCT P, CLIENT C
WHERE P.PR_ID=C.PR_ID;
iv)SELECT PRODUCT.*FROM PRODUCT ,CLIENT
WHERE PRODUCT.PR_ID=CLIENT.PR_ID AND CITY =”DELHI”;
v)DEGREE=9 CARDINALITY=4*7=28
OR
PR_ID OTY C_NAME
BS101 25 DREAM MART
TB310 15 SHOPRIX
SP235 15 BIG BAZZAR
FW422 10 LIVE LIFE