Page 330 - Informatics_Practices_Fliipbook_Class12
P. 330

(vii)  Remove the attribute Remarks from the table.

            Ans.  ALTER TABLE Schedule

                 DROP Remarks;

         Program 24: Consider the table Student and write queries for performing the following tasks:
        Table: Student
                    +---------------+-----------------------+------------------------------+------------+---------+------------------+
                    | Roll_Num | Student_Name   | Course_Name              | Duration | Fee     | Batch_Prefer |
                    +--------------+-----------------------+------------------------------+-------------+----------+-------------------+
                    |        1         | Bhaskar Dhyani   | Web Development      |              3 | 20000 | Morning         |
                    |        2         | Dakshinesh          | Machine Learning        |              4 | 25000 | Evening           |
                    |        3         | Saumit Raj           | Office Tools                  |       NULL | 50000 | Evening           |
                    |        4         | Lovepreet Singh  | Mobile App                  |              3 | 18000 | Morning          |
                    |        5         | Aditya Jayant       | Python Programming |              6 | 45000 | Evening           |
                    |        6         | Jenny Fernandis  | Office Tools                  |              4 | 40000 | Evening          |
                    +--------------+------------------------+------------------------------+-------------+----------+-------------------+
              (i)  Display the total number of records in the table.

            Ans.  SELECT COUNT(*) FROM student;

              (ii)  Display the average fee given by students.
            Ans.  SELECT AVG(Fee) FROM student;

             (iii)  Display the total duration of all courses.

            Ans.  SELECT SUM(Duration) FROM Student;
             (iv)  Display the count of records for each batch preference.

            Ans.  SELECT COUNT(*), Batch_prefer FROM student

                 GROUP BY Batch_Prefer;
              (v)  Display the minimum and maximum fee paid by the student.

            Ans.  SELECT MIN(Fee), MAX(Fee) FROM student;

             (vi)  Display the average fee of records whose duration is known.
            Ans.  SELECT AVG(Fee) FROM student


                  WHERE Duration IS NOT NULL;

         Program 25: Consider the tables Passengers and Trains given below:
        Table: Passengers
                                +---------+----------+-------------+-------------+--------+------------------+
                                | PNR   | TNO      | PNAME    | GENDER  |  AGE  | TRAVELDATE |
                                +--------+-----------+--------------+------------+---------+-----------------+
                                | P001 | 112300 | AVIRAJ     |       M       |   21    | 2023-10-10   |
                                | P002 | 918273 | PRAGYA   |        F       |   49    | 2023-09-30   |
                                | P003 | 650912 | RAMESH  |       M       |   75    | 2023-09-20   |
                                | P004 | 301582 | SOUMIL   |       M       |   25    | 2023-11-07   |
                                | P005 | 403822 | SNEHA     |        F       |   33    | 2023-12-25   |
                                | P006 | 135790 | HEMA      |        F       | NULL | 2024-01-18   |
                                +--------+-----------+--------------+-------------+--------+-----------------+


          316  Touchpad Informatics Practices-XII
   325   326   327   328   329   330   331   332   333   334   335