二、操作题(用SQL或Oracle)
1.某公司的员工工资表和部门情况表的表结构如下:
员工号 姓名 工资 工资月份 部门号
表A 员工工资表
部门号 部门名称 部门经理
表B 部门情况表
现以200604一个月的工资情况为例,用查询语句完成下列问题:
1)找出当月工资最高的员工的员工号、姓名、部门名称和工资;
2)找出当月工资最高的员工所在的部门的所有员工的平均工资;
3)找出公司各部门的部门号、部门名称和其员工当月的平均工资。
2.表C中只有一列COL001,每条记录由字符构成。现在知道每条记录中字符内容由8项组成,每项之间用‘;’隔开,8项之中有的项可能没有。试将COL001分成8列生成一张新表,原表中的每项为新表中的一列。
COL001
86130004;000100971;01060;;
8613000219;000100122;00110;8613000219
8613000310;000100122;0AAAA;;1013089;214
表C片断
1)画出你的解决方法的流程图、
2)写主要的代码
1.某公司的员工工资表和部门情况表的表结构如下:
员工号 姓名 工资 工资月份 部门号
表A 员工工资表
部门号 部门名称 部门经理
表B 部门情况表
现以200604一个月的工资情况为例,用查询语句完成下列问题:
1)找出当月工资最高的员工的员工号、姓名、部门名称和工资;
2)找出当月工资最高的员工所在的部门的所有员工的平均工资;
3)找出公司各部门的部门号、部门名称和其员工当月的平均工资。
2.表C中只有一列COL001,每条记录由字符构成。现在知道每条记录中字符内容由8项组成,每项之间用‘;’隔开,8项之中有的项可能没有。试将COL001分成8列生成一张新表,原表中的每项为新表中的一列。
COL001
86130004;000100971;01060;;
8613000219;000100122;00110;8613000219
8613000310;000100122;0AAAA;;1013089;214
表C片断
1)画出你的解决方法的流程图、
2)写主要的代码
2不会,没在SQL中拆分过,都是用java来实现的
SQL SERVER:
GO
CREATE TABLE D
(
DID INT IDENTITY(1,1),
COLUMN1 VARCHAR(20),
COLUMN2 VARCHAR(20),
......
COLUMN8 VARCHAR(20)
)
GO
CREATE FUNCTION GETCHARBYID
(@INPUTCHAR VARCHAR(1000),@ID INT)
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @INDEX INT,@INDEX1 INT
DECLARE @I INT
SET @I=1
SET @INDEX=0
WHILE(@I<=@ID) --'1;2;
BEGIN
SET @INDEX1=@INDEX
SET @INDEX=CHARINDEX(';',@INPUTCHAR,@INDEX1+1)
IF(@INDEX=0) SET @INDEX=LEN(@INPUTCHAR)+1
SET @I=@I+1
END
IF(@INDEX=0 AND @INDEX1=0)
RETURN @INPUTCHAR
IF(@INDEX=@INDEX1)
RETURN ''
RETURN SUBSTRING(@INPUTCHAR,@INDEX1+1,@INDEX-@INDEX1-1)
END
GOCREATE PROCEDURE PP
AS
DECLARE @MYCURSOR CURSOR
DECLARE @STRTEMP VARCHAR(1000)
DECLARE @I INT
DECLARE @ID INT
DECLARE @TEMP VARCHAR(20)
DECLARE @SQL VARCHAR(100)
SET @MYCURSOR=CURSOR FOR SELECT * FROM C
OPEN @MYCURSOR
FETCH NEXT FROM @MYCURSOR INTO @STRTEMP
WHILE(@@FETCH_STATUS=0)
BEGIN
SET @I=1
SET @SQL=''
INSERT INTO D(COLUMN1) VALUES('')
SET @ID=@@IDENTITY
WHILE(@I<=8)
BEGIN
SELECT @TEMP=dbo.GETCHARBYID(@STRTEMP,@I)
IF(@TEMP<>'')
BEGIN
SET @SQL='UPDATE D SET COLUMN'+CAST(@I AS VARCHAR(1))+'='+@TEMP+' WHERE DID='+CAST(@ID AS VARCHAR(2))
EXEC(@SQL)
END
ELSE BREAK
SET @I=@I+1
END
FETCH NEXT FROM @MYCURSOR INTO @STRTEMP
END
CLOSE @MYCURSOR
DEALLOCATE @MYCURSOR
2)
1. select t1.员工号, t1.姓名 , t2.部门名称, t1.工资 from 表A t1, 表B t2 where t1.员工号 in (select max(工资) from 表A) and t1.部门号 = t2.部门号2. select t2.部门号, avg(t1.工资) 平均工资 from 表A t1, 表B t2 where t1.员工号 in (select max(工资) from 表A) and t1.部门号 = t2.部门号 group by t1.部门号3. select b.部门号, b.部门名称, avg(a.平均工资) 平均工资 from (select 部门号, avg(工资) 平均工资 from 表A group by 部门号) a, 表B b where a.部门号 = b.部门号4. create table 表C (...);
insert into 表C select substr('8613000219;000100122;00110;8613000219' || ';',1,instr('8613000219;000100122;00110;8613000219' || ';',';',1,1) - 1) a,
substr('8613000219;000100122;00110;8613000219' || ';',instr('8613000219;000100122;00110;8613000219' || ';',';',1,1) + 1, instr('8613000219;000100122;00110;8613000219' || ';',';',1,2) - instr('8613000219;000100122;00110;8613000219' || ';',';',1,1) -1) b,
substr('8613000219;000100122;00110;8613000219' || ';',instr('8613000219;000100122;00110;8613000219' || ';',';',1,2) + 1, instr('8613000219;000100122;00110;8613000219' || ';',';',1,3) - instr('8613000219;000100122;00110;8613000219' || ';',';',1,2) -1) c,
substr('8613000219;000100122;00110;8613000219' || ';',instr('8613000219;000100122;00110;8613000219' || ';',';',1,3) + 1, instr('8613000219;000100122;00110;8613000219' || ';',';',1,4) - instr('8613000219;000100122;00110;8613000219' || ';',';',1,3) -1) d,
substr('8613000219;000100122;00110;8613000219' || ';',instr('8613000219;000100122;00110;8613000219' || ';',';',1,4) + 1, instr('8613000219;000100122;00110;8613000219' || ';',';',1,5) - instr('8613000219;000100122;00110;8613000219' || ';',';',1,4) -1) e,
substr('8613000219;000100122;00110;8613000219' || ';',instr('8613000219;000100122;00110;8613000219' || ';',';',1,5) + 1, instr('8613000219;000100122;00110;8613000219' || ';',';',1,6) - instr('8613000219;000100122;00110;8613000219' || ';',';',1,5) -1) f,
substr('8613000219;000100122;00110;8613000219' || ';',instr('8613000219;000100122;00110;8613000219' || ';',';',1,6) + 1, instr('8613000219;000100122;00110;8613000219' || ';',';',1,7) - instr('8613000219;000100122;00110;8613000219' || ';',';',1,6) -1) g,
substr('8613000219;000100122;00110;8613000219' || ';',instr('8613000219;000100122;00110;8613000219' || ';',';',1,7) + 1, instr('8613000219;000100122;00110;8613000219' || ';',';',1,8) - instr('8613000219;000100122;00110;8613000219' || ';',';',1,7) -1) h from dual另外, 这不是什么非常查询, 都是一些基本SQL。
from a_bmqkb a, a_yggzb b
where a.部门号 = b.部门号
and 工资 || '_' || 工资月份 in
(select max(工资) || '_' || 工资月份
from a_bmqkb a, a_yggzb b
where a.部门号 = b.部门号
group by 工资月份)
and 工资月份='200701'
select sum(工资) / count(*)
from a_yggzb
where 部门号 = (select a.部门号
from a_yggzb a, a_bmqkb b
where a.部门号 = b.部门号
and 工资 || '_' || 工资月份 in
(select max(工资 || '_' || 工资月份)
from a_yggzb a, a_bmqkb b
where 工资月份 = '200701'
group by 工资月份))