Sql relational division example
Problem
Given the following database tables
Employee (SSN, name, location)
Assigned (SSN, project_name, duration)
Project (project_name, category, budget, location)
Write SQL statements to find the following:
- Project names with a budget smaller than the average budget of projects in a given category let us say “Test Category”
- Employees who are assigned to all projects with a budget > 10000 assuming an employee can be assigned to one or more projects
- Project name with most employees
Solution
The first SQL is straight forward. We just use the aggregate function AVG to calculate the average budget of all projects with category Test Category. Once we calculate that average value we select only those projects with budget less than this value
Sql query
1 2 3 4 5 |
SELECT PROJECT_NAME FROM PROJECT WHERE BUDGET < ( SELECT AVG(BUDGET) FROM PROJECT WHERE CATEGORY = 'Test Category' ); |
Solution
The second SQL is a little bit tricky because it involves the concept of SQL division. We group employees in the assigned table then calculate the number of assigned projects for each employee. Out of those we filter out entries with project budget less than or equal to 10000. So far we calculated the number of assigned projects with with Budget greater than 10000 for each employee but we are only interested in those employees whose number of assigned projects is the same as that of all projects with budget greater than 10000.
Sql relational division query
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SELECT NAME FROM EMPLOYEE WHERE EMPLOYEE_SSN IN ( SELECT EMPLOYEE_SSN FROM ( SELECT A.EMPLOYEE_SSN, COUNT(A.PROJECT_NAME) FROM ASSIGNED A, PROJECT P WHERE P.PROJECT_NAME = A.PROJECT_NAME AND P.BUDGET > 10000 GROUP BY A.EMPLOYEE_SSN HAVING COUNT(A.PROJECT_NAME) = ( SELECT COUNT(PROJECT_NAME) FROM PROJECT WHERE BUDGET > 10000 ) ) ); |
Solution
The third one is also tricky. SQL division is involved as well. We Create a view that contains project name and the number of employees assigned to it for later use. Then we repeat the same
SQL used in the view but only including those entries having a count that is greater than or equal to all counts in the view.
Sql relational division query
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE OR REPLACE FORCE VIEW "EMPLOYEE_CNT" ("PROJECT_NAME", "EMPLOYEE_CNT") AS ( select project_name, count (employee_ssn) from assigned group by project_name ); SELECT PROJECT_NAME, COUNT (EMPLOYEE_SSN) FROM ASSIGNED GROUP BY PROJECT_NAME HAVING COUNT (EMPLOYEE_SSN) >= ALL ( SELECT EMPLOYEE_CNT FROM EMPLOYEE_CNT ); |
That is it for toady. If you have a question or comment, please use the comments section below. Thanks for visiting.