Page 207 - Informatics_Practices_Fliipbook_Class12
P. 207

Query: Arrange the employees' details (ID, FName, Dept_Name, and Salary) in ascending order of their
            department names by default the order is ascending.
            Solution:

            SELECT ID, FName, Dept_Name, Salary
            FROM EMPLOYEE AS E, DEPARTMENT AS D
            WHERE E.Dept_No = D.Dept_No
            ORDER BY Dept_Name ASC;
            The result of executing the above query is shown in Table 5.8.

                                      ID          FName        Dept_Name     1      Salary
                                     10002         Dhiraj         Accounts          85000
                                     10005        Anshul          Accounts         100000
                                     10003        Muskan        Administration     100000

                                     10001          Raj         Administration     100000
                                     10008         Naval        Administration      70000
                                     10006         Rajit         Automobile         60000
                                     10009        Naveen         Automobile         60000
                                     10004         Hiten         Automobile        100000
                                     10007         Taran           Textile          70000
                                     10010         Savita          Textile          50000

                                    Table 5.8: Employee details in ascending order of department names
            Query: Arrange the employees' details (ID, FName, Dept_Name, and Salary ) in ascending order of their
            department names. However, within a department, arrange the employees in descending order of their Salary.
            Solution:

            SELECT ID, FName, Dept_Name, Salary
            FROM EMPLOYEE AS E, DEPARTMENT AS D
            WHERE E.Dept_No = D.Dept_No
            ORDER BY Dept_Name ASC, Salary DESC;
            The result of executing the above query is shown in Table 5.9.

                                      ID          FName        Dept_Name     1    Salary     2
                                     10005        Anshul          Accounts         100000
                                     10002         Dhiraj         Accounts          85000
                                     10003        Muskan        Administration     100000

                                     10001          Raj         Administration     100000
                                     10008         Naval        Administration      70000
                                     10004         Hiten         Automobile        100000
                                     10006         Rajit         Automobile         60000
                                     10009        Naveen         Automobile         60000
                                     10007         Taran           Textile          70000
                                     10010         Savita          Textile          50000

                   Table 5.9: Employee details in ascending order of department names and descending order salary of employees
                                                        within a department

                                                                                     SQL: Working with Two Tables  193
   202   203   204   205   206   207   208   209   210   211   212