Page 274 - Information_Practice_Fliipbook_Class11
P. 274

Table 9.21: ID, FName, Salary in descending order of Salary
                                                +-------+--------+--------+
                                                | ID    | FName  | Salary |
                                                +-------+--------+--------+
                                                | 10001 | Raj    | 100000 |
                                                | 10003 | Muskan | 100000 |
                                                | 10004 | Hiten  | 100000 |
                                                | 10005 | Anshul | 100000 |
                                                | 10002 | Dhiraj |  85000 |
                                                | 10007 | Taran  |  70000 |
                                                | 10008 | Naval  |  70000 |
                                                | 10009 | Naveen |  60000 |
                                                | 10006 | Rajit  |  60000 |
                                                | 10010 | Savita |  50000 |
                                                +-------+--------+--------+
        We can also display the records arranged according to the values of two or more attributes in a table. For example, to
        display ID, first name, last name, and the Salary of all employees ordered by their Salary in descending order. Further,
        for the two employees  having the same salary, the tuples must appear in the ascending order of their last names.

        SELECT ID, FName, Lname, Salary
        FROM EMPLOYEE

        ORDER BY Salary DESC, LName;
        Executing the above statement will produce output shown in Table 9.22. Note that the table appears in descending
        order of Salary. However, due to the ORDER BY clause's default setting (ascending order), the employees having

        the same salary are arranged in ascending order of their last name (LNAME).
                Table 9.22: Salary in descending order, LName in ascending order for the employees having the same salary
                                             +-------+--------+----------+--------+
                                             | ID    | FName  | LName    | Salary |
                                             +-------+--------+----------+--------+
                                             | 10004 | Hiten  | Oberoi   | 100000 |
                                             | 10001 | Raj    | Reddy    | 100000 |
                                             | 10003 | Muskan | Taneja   | 100000 |
                                             | 10005 | Anshul | Verma    | 100000 |
                                             | 10002 | Dhiraj | Bora     |  85000 |
                                             | 10007 | Taran  | Adarsh   |  70000 |
                                             | 10008 | Naval  | Dhingra  |  70000 |
                                             | 10009 | Naveen | Basra    |  60000 |
                                             | 10006 | Rajit  | Gadh     |  60000 |
                                             | 10010 | Savita | Ambedkar |  50000 |
                                             +-------+--------+----------+--------+
        9.8 BETWEEN Operator

        The BETWEEN operator checks whether a value is within a specified range (inclusive of both lower limit and upper
        limit values). It may be applied to numbers, text, and dates. Syntax of an SQL query using  BETWEEN operator is given
        below:
        SELECT attribute1, [attribute2, …]

        FROM table_name
        WHERE attributeJ [NOT] BETWEEN value1 AND value2;
        The above WHERE clause serves as an abbreviation for the following clause:
        WHERE [NOT](attributeJ >= value1 AND attributeJ <= value2);
        To display ID, FName, and Salary of those employees whose salary is between 85000 to 100000, both inclusive,
        we may use the following query: .

        SELECT ID, FName, Salary
        FROM EMPLOYEE


          260  Touchpad Informatics Practices-XI
   269   270   271   272   273   274   275   276   277   278   279