Page 197 - Informatics_Practices_Fliipbook_Class12
P. 197
(iii) Display the total number of students in each Club where number of students are more than 1.
OR
(Option for part (iii) only)
Display the maximum and minimum marks secured by each gender.
Ans. (i) SELECT gender, AVG(marks) FROM schooldata GROUP BY gender;
(ii) SELECT grade, MIN(marks) FROM schooldata WHERE grade = 10;
(iii) SELECT club, COUNT(*) FROM schooldata GROUP BY club HAVING COUNT(*) > 1;
OR
SELECT MAX(marks), MIN(marks) FROM schooldata GROUP BY gender;
13. Find the output of the following SQL Queries: [2022]
(i) SELECT ROUND (7658.345,2);
(ii) SELECT MOD(ROUND (13.9,0),3);
OR
Give any two differences between the POWER() and SUM() SQL functions.
Ans. (i) 7658.35
(ii) 2
OR
POWER(n,p)
It is used to compute power of given number by specified digit.
It accepts two parameters: n is number and p is power of n.
Sum(range1, range2-------- range n)
It is used to do sum of specified numbers or range of numbers.
It accepts the range of numbers or bunch of values.
14. Find the output of the following SQL queries: [2022]
(i) SELECT SUBSTR("FIT INDIA MOVEMENT",5);
(ii) SELECT INSTR("ARTIFICIAL INTELLIGENCE", "IA");
Ans. (i) INDIA MOVEMENT
(ii) 8
15. Srikanth created the following table STUDENT in his database.
Table : Student
Rollno NAME CLASS MARKS
1 Ritika 12 40
2 Angad 12 35
3 Kaveri 11 42
4 Lalitha 12 21
5 Daniel 11 44
6 Rabindra 11 39
7 Rabia 11 28
He now wants to count number of students in each class where the number of students is more than 3. He has executed
the following query:
SELECT MAX (Marks) FROM STUDENT WHERE COUNT (*) >3 GROUP BY Class. But, he got an error. Identify the error(s) and
rewrite the query. Also underline the correction(s) done.
Ans. Select count(*) from student group by class having count(*)>3;
Database Query using SQL 183

