Page 296 - Information_Practice_Fliipbook_Class11
P. 296

18.  Consider the table STUDENT given below and answer the questions that follow:

                                                      Table: STUDENT
                     +----------+-----------------+--------------------+------------------+-------+--------------+
                     | Roll_Num | Student_Name    | Course_Name        | Duration(months) | 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)  Write a CREATE TABLE statement for table STUDENT.
              (ii)  Write the SELECT statements to retrieve records from the STUDENT table for the following:
                  a.  List the names of students in each course.
                  b.  Display the course name along with their duration for the courses whose fee is more than 30000.
                  c.  Display the names of students in alphabetical order.
                  d.  Display the count of students who prefer the Evening batch.
                  e.  Display the average duration of courses from the table.
                  f.  Increase the fee by 10% for Mobile App course.
                  g.  Delete rows from table STUDENT where duration of the course is not known.
              (iii)  For each of the following SQL queries, write the output that will be produced on its execution:
                  a. SELECT * FROM STUDENT
                    WHERE Student_Name LIKE '%h' AND Fee < 25000;
                  b. SELECT MIN(Fee) FROM STUDENT
                    GROUP BY Batch_Prefer;
                  c. SELECT Roll_Num, Course_Name, Duration(months) FROM STUDENT
                    ORDER BY Duration DESC;
          19.  Consider the table SALESPERSON given below and answer the questions that follow:
                                                   Table: SALESPERSON
                                    +------+--------------+--------+------------+-----------+
                                    | Code | Name         | Salary | Product    | City      |
                                    +------+--------------+--------+------------+-----------+
                                    | 1001 | SANDEEP JHA  |  60000 | Stationary | New Delhi |
                                    | 1002 | YOGRAJ SINHA |  70000 | Footwear   | Gurugram  |
                                    | 1003 | TENZIN JACK  |  45000 | Books      | Noida     |
                                    | 1004 | TARANA SEN   |  55000 | Toys       | Gurugram  |
                                    | 1005 | ANOKHI RAJ   |  45000 | Footwear   | Noida     |
                                    +------+--------------+--------+------------+-----------+
              (i)  Write the SELECT statement to retrieve records from SALESPERSON table for the following:
                  a.  List the names of salespersons who are dealing with Books in Noida.
                  b.  Display the names of cities without any repetition.
                  c.  Display the count of salespersons in each city.
                  d.  Display the name and salary of salespersons in descending order of Salary.
                  e.  Delete the sales person whose salary is more than 60000.
                  f.  Add a field Contact_No in the table SALESPERSON to store the mobile number of salespersons.
              (ii)  For each of the following SQL queries, write the output that will be produced on its execution:
                  a.  SELECT Product, SUM(Salary) FROM SALESPERSON

                    GROUP BY Product;
                  b.  SELECT Name, Salary FROM SALESPERSON
                    WHERE Product IN ('Toys','Footwear');
                  c. SELECT Code, Name FROM SALESPERSON
                    WHERE Salary>50000 and Name LIKE '%E%';
                  d.  SELECT AVG(Salary) FROM SALESPERSON
                    WHERE CITY='Gurugram';
          282  Touchpad Informatics Practices-XI
   291   292   293   294   295   296   297   298   299   300   301