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

