EMPLOYEE(ESSN,Ename,Sex,Salary,Dnumber),其中ESSN是主键, 存储员工的信息,包括社会保险号、姓名、工资、所属部门号
DEPARTMENT(Dnumber,Dname,MgrSSN) 其中Dnumber是主键,存储部门信息,包括部门编号、名称、经理社会保险号
PROJECT(Pnumber,Pname,Pcity) 其中Pnumber是主键,存储项目的信息,包括项目编号、名称、所在城市
WORKS_ON(Pnumber,ESSN,Wdate,Hours) 其中Pnumber、ESSN、Wdate是主键,存储某员工在某日为某项目工作的时间请用SQL语句实现下面计算:
(1)求工资高于其所在部门平均工资的职工姓名
(2)求至少在三个不同项目中工作过的职工姓名
(3)建立视图DeptPro(Dnumber,Pnumber,Thours),表示每个部门的所有职工在每个项目中的工作总时数。并利用该视图,求在项目'Middleware'中工作时数最多的部门名称。
DEPARTMENT(Dnumber,Dname,MgrSSN) 其中Dnumber是主键,存储部门信息,包括部门编号、名称、经理社会保险号
PROJECT(Pnumber,Pname,Pcity) 其中Pnumber是主键,存储项目的信息,包括项目编号、名称、所在城市
WORKS_ON(Pnumber,ESSN,Wdate,Hours) 其中Pnumber、ESSN、Wdate是主键,存储某员工在某日为某项目工作的时间请用SQL语句实现下面计算:
(1)求工资高于其所在部门平均工资的职工姓名
(2)求至少在三个不同项目中工作过的职工姓名
(3)建立视图DeptPro(Dnumber,Pnumber,Thours),表示每个部门的所有职工在每个项目中的工作总时数。并利用该视图,求在项目'Middleware'中工作时数最多的部门名称。
第一问,没有指出工资大于'其所在部门'的平均工资。select avg(Salary) from Salary group by Dnumber这个语句得到的是各部门的平均工资。直接在where中用Salary>(select avg(Salary) from Salary group by Dnumber)好像有点问题第二问,没有考虑到同一职工在‘不同时期’参与‘同一个项目’的情形。
这样对瓦。第二个有疑问哎,Pnumber、ESSN都是主键不能重复吧?
第二问我把你的那个改了下:
SELECT Ename
FROM EMPLOYEE
WHERE ESSN IN (SELECT ESSN
FROM (SELECT DISTINCT Pnumber,ESSN FROM WORKS_ON)
GROUP BY ESSN
HAVING COUNT(Pnumber)>=3)
FROM EMPLOYEE
WHERE ESSN IN (SELECT ESSN
FROM (SELECT DISTINCT Pnumber,ESSN FROM WORKS_ON)
GROUP BY ESSN
HAVING COUNT(Pnumber)>=3)
declare @WORKS_ON table(Pnumber int,ESSN varchar(50),Wdate datetime,Hours int)insert into @EMPLOYEE
select 'IS01','张一','男',5000,3 union
select 'IS02','张二','女',5200,2 union
select 'IS03','张三','男',8000,1 union
select 'IS04','张四','男',4500,2 union
select 'IS05','张五','男',4000,2 union
select 'IS06','张六','男',6000,2 union
select 'IS07','张七','男',10000,3 union
select 'IS08','张八','女',8500,3 union
select 'IS09','张九','女',7000,1 union
select 'IS10','张十','女',7500,1insert into @WORKS_ON
select '1','IS03','2009-10-2 10:12:23',20 union
select '2','IS10','2003-10-2 10:12:23',20 union
select '2','IS10','2009-11-2 10:12:23',20 union
select '2','IS10','2009-11-2 10:12:23',20 union
select '2','IS06','2006-10-2 10:12:23',20 union
select '2','IS06','2009-10-3 10:12:23',20 union
select '2','IS05','2009-10-3 10:12:23',20 union
select '3','IS01','2010-10-2 10:12:23',20 union
select '1','IS07','2009-10-8 10:12:23',20 union
select '2','IS07','2007-10-2 10:12:23',20 union
select '3','IS07','2001-10-2 10:12:23',20 union
select '4','IS07','2005-10-2 10:12:23',20 union
select '3','IS08','2005-8-2 10:12:23',20--求工资高于其所在部门平均工资的职工姓名
select * from @EMPLOYEE a
inner join
(select Dnumber, avg(salary) as AvgSalary from @EMPLOYEE group by Dnumber ) b
on a.Dnumber = b.Dnumber and a.Salary>AvgSalary--求至少在三个不同项目中工作过的职工姓名select ESSN,count(*) as times from
(select distinct ESSN,Pnumber from @WORKS_ON) a
group by ESSN having count(*)>=3
--每个部门的所有职工在每个项目中的工作总时数
select Dnumber,Pnumber,sum(hours) as totalHours from
(
select b.Dnumber,a.Pnumber,a.Hours from @WORKS_ON a inner join @EMPLOYEE b on a.ESSN = b.ESSN
) v
group by Dnumber,Pnumber order by Dnumber,Pnumber