SQL : SQL constraint (check) which specifies that the ‘Marketing’ department has

Question

SQL :SQL constraint (check) which specifies that the ‘Marketing’ department has fewer than 250 employees, with an average salary higher than 75,000.

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)

Assume that the CREATE TABLE statement for the EMPLOYEES table is already specified. Add to this declaration, an SQL constraint (check) which specifies that the ‘Marketing’ department has fewer than 250 employees, with an average salary higher than 75,000.

SQL

Explanation / Answer

// You can use below query for it. I hope it helps

select count(1), d.DeptName from EMPLOYEES e inner join
DEPARTMENTS d on d.DeptName = e.DeptName
where d.DeptName = 'Marketing'
group by d.DeptName
having count(d.DeptName) < 250 and
avg (e.salary) >75000;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.