Page 213 - Informatics_Practices_Fliipbook_Class12
P. 213
Query 8: Retrieve the department name, location, number of employees, and the average salary for all employees in
that department.
Answer:
SELECT Dept_Name, Location, COUNT(ID) AS "NoofEmployees", AVG(Salary) AS "AverageSalary"
FROM EMPLOYEE AS E, DEPARTMENT AS D
WHERE (E.Dept_No = D.Dept_No)
GROUP BY Dept_Name;
Output (Table 5.20):
Dept_Name Location NoofEmployees Average Salary
Account Noida 2 92500.0000
Adminstration Delhi 3 90000.0000
Automobile Jamshedpur 3 73333.3333
Textile Mumbai 2 60000.0000
Table 5.20: Department name, location, number of employees, and the average salary for all employees in that department.
b. EQUI JOIN vs NATURAL JOIN
Suppose we wish to retrieve the details of all the employees along with their department details. The following SQL
query joins the tables EMPLOYEE and DEPARTMENT to achieve this:
SELECT *
FROM DEPARTMENT, EMPLOYEE
WHERE EMPLOYEE.Dept_No = DEPARTMENT.Dept_No;
Note that the result of executing the above query yields the column Dept_No twice, once for each table (Table 5.21).
As the above query joins only those tuples which have identical values of Dept_No, such a join is called equijoin.
Equijoin is a type of join operation in relational database systems that combines tables based on matching values
in specified columns. In an equijoin, the join condition is an equality comparison between corresponding columns
in the joined tables.
Pin_ Dept_
ID FName LName Gender Address City Code DOB Salary No Dept_Name Location Mgr_Id
10001 Raj Reddy M West Godavari Andhra 534197 1980-06-13 100000 2 Administration Delhi 10005
Pradesh
10002 Dhiraj Bora M Dispur, Kamrup, Guwahati 781005 1975-09-30 85000 1 Account Noida 10002
Assam
10003 Muskan Taneja F 8/33, Geeta Delhi 110031 1990-01-25 100000 2 Administration Delhi 10005
Colony
10004 Hiten Oberoi M 15, Dimna Road, Jamshedpur 831018 1985-06-24 100000 4 Automobile Jamshedpur 10007
Mango
10005 Anshul Verma M House 10, Sector Noida 201304 1990-01-01 100000 1 Accounts Noida 10002
16, Gautum Budh
Nagar
10006 Rajit Gadh M 12, Beldih Jamshedpur 831001 1960-05-07 60000 4 Automobile Jamshedpur 10007
Triangle, Bistupur
10007 Taran Adarsh M B-76, CST Road, Mumbai 400098 1965-01-13 70000 5 Textile Mumbai 10004
Kalina, Santacruz
East
10008 Naval Dhingra M E-14 Vivek Delhi 110095 1975-08-04 70000 2 Administration Delhi 10005
Vihar
10009 Naveen Basra F 28, Aambagan Jamshedpur 831001 1980-09-24 60000 4 Automobile Jamshedpur 10007
Road, Sakechi
10010 Savita Ambedkar F C-49, G-Block, Mumbai 400051 1987-07-11 50000 5 Textile Mumbai 10004
Bandra Kurla,
Bandra East
Table 5.21: EQUIJOIN of EMPLOYEE and DEPARTMENT
SQL: Working with Two Tables 199

