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
   192   193   194   195   196   197   198   199   200   201   202