May 13, 2010

Sql relational division example

By Mohammed Abualrob Database Systems 0 Comments

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

SELECT PROJECT_NAME
FROM PROJECT
WHERE BUDGET < ( SELECT AVG(BUDGET)
FROM PROJECT
WHERE CATEGORY = 'Test Category' );

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

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
      )
   )
);

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

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
);

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.

8 BIT AVENUE

Sql relational division example

More from my site

About Author

Mohammed Abualrob

Add a Comment