Page 211 - Informatics_Practices_Fliipbook_Class12
P. 211
Solution:
SELECT SUM(Salary), MAX(Salary), MIN(Salary), AVG(Salary)
FROM EMPLOYEE AS E, DEPARTMENT AS D
WHERE E.Dept_No = D.Dept_No AND Dept_Name = 'Accounts';
Output (Table 5.14):
SUM(Salary) MAX(Salary) MIN(Salary) AVG(Salary)
185000 100000 85000 92500.0000
Table 5.14: Sum of the salaries, maximum salary, minimum salary, and the average salary of all employees of the Accounts
department.
Query 3: Find the number of employees in the Administration department.
Answer:
SELECT COUNT(*) AS NumOfEmployees
FROM EMPLOYEE AS E, DEPARTMENT AS D
WHERE E.Dept_No = D.Dept_No AND Dept_Name = 'Administration';
Output (Table 5.15):
NumOfEmployees
3
Table 5.15: Number of employees in the Administration department.
Query 4: Retrieve the names of all the departments and their managers' names.
Answer:
SELECT Dept_Name, Fname, LName
FROM EMPLOYEE AS E, DEPARTMENT AS D
WHERE E.ID = D.Mgr_Id;
Output (Table 5.16):
Dept_Name FName LName
Account Dhiraj Bora
Administration Anshul Verma
Home Goods Muskan Taneja
Automobile Hiten Oberoi
Textile Taran Adarsh
Table 5.16: Names of all the departments and their manager's name.
Query 5: Retrieve the names of all employees who work in the department located in the same city where they live.
Answer:
SELECT FName, LName
FROM EMPLOYEE AS E, DEPARTMENT AS D
WHERE (E.Dept_No = D.Dept_No) AND (E.City = D.Location);
SQL: Working with Two Tables 197

