Question

Consider the following two Relational Schema describing the EMPLOYEES and DEPARTMENTS of some organization (assume all names are unique).

EMPLOYEES (EmployeeName, Salary, DeptName)

DEPARTMENTS (DeptName, ManagerName, City)

Define at schema level, a constraint (SQL Assertion) which specifies that none of the salaries of the employees of any department located in ‘San Francisco’ is more than the salary of any employee in the ‘Management’ department.

Answer 1

If you have any doubts, please give me comment...

CREATE ASSERTION myConstraint CHECK(

NOT EXISTS(

SELECT *

FROM EMPLOYEES, DEPARTMENTS

WHERE EMPLOYEES.DeptName = DEPARTMENTS.DeptName AND City = 'San Fransisco' AND

Salary > ANY(

SELECT Salary

FROM EMPLOYEES, DEPARTMENTS

WHERE EMPLOYEES.DeptName = DEPARTMENTS.DeptName AND City = 'Management'

)

)

);