练功，顺便求SQL语句
*为Primary Key
Foreign Keys如下:
员工主档.部门 --- > 部门代码.代码
眷属.员工编号 --- > 员工主档.员工编号
眷属学历.员工编号+眷属学历.身分证号 --- > 眷属.员工编号+眷属.身分证号
QA. 请利用LEFT OUTER JOIN设计以下SQL Statement
QA-1. 列出系统日期当天, 每一个部门的部门名称及其各自的在职员工数量
QA-2. 列出各部门各种学历之眷属人数统计, 并依部门名称+学历排序
QA-3. 列出部门代码 ‘A01’ 中有眷属的学历是 ‘大学’ 的所有员工姓名
QB. 请以Pseudo-Code 写出要将员工编号 ‘0001’ 之员工数据完整删除之程序
thank you!
[解决办法]
--1. 列出系统日期当天, 每一个部门的部门名称及其各自的在职员工数量
select t2.名称 as 部门名称,count(*) from 员工主档 t1
inner join 部门代码 t2 on t1.部门=t2.代码
where convert(varchar(10),t1.到职日,120)<=convert(varchar(10),getdate(),120)
and 离职日 is null
group by t2.名称
--2. 列出各部门各种学历之眷属人数统计, 并依部门名称+学历排序
select t2.代码,t2.名称 as 部门名称,t4.学历,count(t4.员工编号) 眷属人数 from 员工主档 t1
inner join 部门代码 t2 on t1.部门=t2.代码
inner join 眷属 t3 on t3.员工编号=t1.员工编号
inner join 眷属学历 t4 on t3.员工编号=t4.员工编号 and t3.身分证号=t4.身分证号
group by t2.代码,t2.名称,t4.学历
order by t2.名称,t4.学历
---3. 列出部门代码 ‘A01’ 中有眷属的学历是 ‘大学’ 的所有员工姓名
select t1.姓名 from 员工主档 t1
inner join 部门代码 t2 on t1.部门=t2.代码
inner join 眷属 t3 on t3.员工编号=t1.员工编号
inner join 眷属学历 t4 on t3.员工编号=t4.员工编号 and t3.身分证号=t4.身分证号
where t2.代码='A01' and t4.学历='大学'
--QB. 请以Pseudo-Code 写出要将员工编号 ‘0001’ 之员工数据完整删除之程序
delete from 眷属学历 where 员工编号='0001'
delete from 眷属 where 员工编号='0001'
delete from 员工主档 where 员工编号='0001'
--QA. 请利用LEFT OUTER JOIN设计以下SQL Statement
--QA-1. 列出系统日期当天, 每一个部门的部门名称及其各自的在职员工数量
select b.名称 '部门名称',
count(1) '在职员工数量'
from 员工主档 a
inner join 部门代码 b on a.部门=b.代码
where a.离职日<getdate()
group by a.部门,b.名称
--QA-2. 列出各部门各种学历之眷属人数统计, 并依部门名称+学历排序
select b.名称 '部门名称',c.学历,count(1) '人数'
from 员工主档 a
inner join 部门代码 b on a.部门=b.代码
inner join 眷属学历 c on a.员工编号=c.员工编号
group by b.名称,c.学历
order by b.名称,c.学历
--QA-3. 列出部门代码 ‘A01’ 中有眷属的学历是 ‘大学’ 的所有员工姓名
select distinct b.姓名
from 部门代码 a
inner join 员工主档 b on a.代码=b.部门
inner join 眷属学历 c on b.员工编号=c.员工编号
where a.代码='A01' and c.学历='大学'
--QB. 请以Pseudo-Code 写出要将员工编号 ‘0001’ 之员工数据完整删除之程序
delete from 眷属学历 where 员工编号='0001'
delete from 眷属 where 员工编号='0001'
delete from 员工主档 where 员工编号='0001'
