Question

I have already turned in my HW, I need some understanding of the below SQL queries.

Consider the following relational schema. An employee can work in more than on department; also, the percentTime field of the Works relations shows the percentage of time that a give employee works in a given department. Employees (elD integer, fName string, IName string, age integer, salary real) Locations (ID integer, address string, city string, state string, zip string) Departments (diD integer, dName string, budget real, managerEID integer, locationlD integer) Worksln (elD integer, diD integer, percentTime integer, startDate date, endDate date) The primary key fields are underlined, and the domain of each field is listed after the field name. Thus, elD is the key for Employees, lID is the key for Locations, diD is the key for Departments and elD, and dID together form the key for Worksln Write the following queries in SQL statements. The query answers must not contain duplicates, but you should use the SQL keyword DISTINCT only when necessary.

1. Find the number of employees in each department.

2. List the names of departments that have more than 5 employees working there.

3. Retrieve the lowest and highest salary in each department. Output the department name in alphabetical order.

Answer 1

1.select dname, count(*) from Departments D inner join WorksIn W on D.dID = W.dID group by dname

2.select dname from Departments D inner join WorksIn W on D.dID = W.dID group by dname having count(*) > 5

3.Select D.dname, max(E.salary) as Max_Salary,min(E.salary) as Min_salary from departments D inner join WorksIn W on D.dID = W.dID inner join employees E on W.eID = E.eID group by d.dname order by D.dname