Page 208 - Informatics_Practices_Fliipbook_Class12
P. 208

Query: Display salary of all employees in the Accounts department.
        Solution:

        SELECT Salary, Dept_Name
        FROM EMPLOYEE AS E, DEPARTMENT AS D

        WHERE E.Dept_No = D.Dept_No AND Dept_Name = 'Accounts ';
        Table 5.10 shows the result on executing the above SELECT statement.


                                                 Salary     Dept_Name

                                                 85000        Accounts
                                                 100000       Accounts


                   Table 5.10: Details from EMPLOYEE and DEPARTMENT tables for the Dept_Name = 'A'
        Query: Compute the average salary of all employees in the Accounts department.
        Solution: To answer this query, we apply the AVG function to the salary of  employees in the Accounts department::

        SELECT AVG(Salary) AS Average_Salary, Dept_Name
        FROM EMPLOYEE AS E, DEPARTMENT AS D
        WHERE E.Dept_No = D.Dept_No AND Dept_Name = 'Accounts ';
                                Table 5.11 shows the result on executing the above SELECT statement.



                                             Average_Salary     Dept_Name
                                               92500.0000         Accounts


                             Table 5.11: Average salary of employees for Dept_Name = 'Accounts'
        UPDATE Statement

        As discussed in the previous chapter, an UPDATE statement is used to update the tuples, typically based on one or
        more conditions. Now we will demonstrate the use of the UPDATE operation based on a condition involving more
        than one table. For example,
        Query: Increase the salary of employees who belong to the Accounts department by Rs. 1000.
        Solution:

        UPDATE EMPLOYEE AS E, DEPARTMENT AS D
        SET SALARY = SALARY + 1000
        WHERE E.Dept_No = D.Dept_No AND Dept_Name = 'Accounts';
        Query: Change the manager of Accounts department to Raj Reddy.
        Solution:

        UPDATE EMPLOYEE AS E, DEPARTMENT AS D
        SET Mgr_Id = (SELECT ID FROM EMPLOYEE WHERE FName='Raj' and LName='Reddy')
        WHERE E.Dept_No = D.Dept_No AND Dept_Name = 'Accounts';










          194  Touchpad Informatics Practices-XII
   203   204   205   206   207   208   209   210   211   212   213