These are just a few more questions I've been working on. I know my way around db2 fairly well, but I'm still having a few troubles with some of its features tough. Any advice would be greatly appreciated. Thanks!
Write SQL statements to create the above tables with appropriate primary keys and foreign keys enforced.
I know how to create the above tables, but I can not figure out in db2 how to make a table with a double primary key. I keep getting errors when I try to do so. Also I'm failing to find any documentation on how to enforce the proper foreign keys.
Create a VIEW, called RichDeptStat, that has the schema:
RichDeptStat (did:integer, avgSal:real)
where, DeptID is the ID of *rich* departments (i.e., a department is called "rich" if the total sum of all employees' salaries is greater than $10,000), and avgSal is the average salary of all employees working for that rich department.
I know the syntax would be like this but I'm having trouble with the last condition.
Create view RichDeptStat as select DID, sum(emp.salary) as AVGSAL from emp, works join dept on dept.did=works.did where sum(emp.salary)>10,000
How do you enforce the condition that "all departments must have a manager"?
I was thinking there must be a way in DB2 to declare that the attribute manager in dept must be equal to one or greater than or equal to one?
Thanks again.
sounds very suspiciously like homework, but you seem to be trying, instead of just asking for the answer, so here goes...
create table Works
( eid integer not null
, did integer not null
, work_time integer
, primary key (eid, did)
, foreign key eid references Emp (eid)
, foreign key did references Dept (did)
)
as far as enforcing the foreign keys is concerned, you do not have to do that, the database takes care of it for you -- that's why it's called declarative referential integrity
to find rich departments,
select did, sum(salary)/count(eid) as avgSal
from Dept
inner join Works on Dept.did=Works.did
inner join Emp on Works.eid=Emp.eid
group by did having sum(salary)>10000
the condition that "all departments must have a manager" is a rather sneaky way, typical of homework questions, of finding out whether you understand foreign keys
if you declare the manager column a NOT NULL foreign key, then each row in the Dept table must have a value in the manager column which can be found in an existing row of Emp