把部门表中的部门id,在员工表中设为外键。两个表用部门id相连。并且同时你删除部门表的某个部门时还能把相应部门下的员工数据删了。保证了数据的一致性。
create table employee(
emp_id number not null,
emp_name varchar2(50) not null,
CONSTRAINT pk_emp_id PRIMARY KEY (emp_id)
)
create table student
(
stu_id number not null,
stu_name varchar2(100),
emp_id number not null,
constraint fk_studentr foreign key (emp_id) references employee(emp_id) on delete cascade
)
如下:
1
select b.部门号,b.部门名,count(*) as 职工人数,avg(工资) as 平均工资
from
职工表 as a,部门表 as b
where a.部门号=b.部门号
group by b.部门号,b.部门名
2
delete from 部门表 where 部门号 in (select 部门号 from 职工表 group by 部门号 having count(*)<10)
3
update 职工表 set 部门经理 is null where 年龄>55
4
create view v_职工
as
select 部门号,姓名,年龄,工资 from 职工表
SELECTmain.dept_name as 部门名称,
count(*) as 在职员工数,
sum(acu_salary) as 实发工资总额
FROM
dept main
INNER JOIN emp a ON main.dept_code = a.dept_code
INNER JOIN salary b ON a.emp_id = b.emp_id
WHERE
-- 1为有效标识
main.alive_flag = '1'
AND a.alive_flag = '1'
AND b.alive_flag = '1'
AND b.`month` = '201101'
GROUP BY
a.dept_code
HAVING
count(*) >5
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)