实例(工资).txt

经典的sql资料

-- 25. 查询每年的实发工资总额、平均实发工资


-- 26. 查询每年、每月的实发工资总额、平均实发工资


-- 27. 查询 姓名为张小虎的工资记录（使用多表查询）


-- 28. 查询 姓名为张小虎的平均实发工资（使用多表查询）


-- 29. 删除记录张小虎的工资记录(使用子查询)


-- 30. 创建一个视图v_g1，查询2007年工资记录，显示emp_id,g_date,g_jiben字段,并查看


-- 31. 创建一个视图v_g2，查询基本工资大于平均的基本工资记录，显示emp_id,g_date,g_jiben字段


-- 32. 创建一个唯一索引in_g，在deptment表的dept字段上,并验证


-- 33. 创建一个存储过程p_g1，查询显示每个部门（实发工资）合计工资、平均工资（不带参数的存储过程）


-- 34. 创建一个存储过程p_g2，查询显示某指定部门（实发工资）合计工资、平均工资（带参数的存储过程）


-- 35. 创建一个触发器t_g，当插入或修改数据的基本工资<1000，拒绝插入，并验证






-- 练习参考答案：

-- 1. 创建一个数据库test
create database test

-- 2. 创建3个表：
   -- 部门表 deptment(dept_id int 主键,dept varchar(20))
   -- 员工表 emp(emp_id int 主键,emp_name varchar(10),e_deptid int)
   -- 工资表 gongzi(empid_id int not null,g_jiben decimal(10,2) 基本工资,
             -- g_gangwei decimal(10,2) 岗位工资,g_jixiao decimal(10,2) 绩效工资,g_koufa char(1)，
             -- g_shifa decimal(10,2) 实发工资,g_beizhu char(100)，g_date datetime not null)
   
create table deptment(
dept_id int primary key,
dept varchar(20)
)

create table emp(
emp_id int primary key,
emp_name varchar(20),
e_deptid int
)

create table gongzi(
	emp_id   int not null,         --员工号
        g_jiben  decimal(10,2),        --基本工资
        g_gangwei  decimal(10,2),      --岗位工资
        g_jixiao  decimal(10,2),       --绩效工资
	g_koufa  char(1), 	       --扣发工资
        g_shifa  decimal(10,2),        --实发工资
        g_beizhu char(20),
        g_date datetime not null
        )


-- 3. 修改工资表:删除g_beizhu字段;修改g_koufa字段为decimal(10,2)；加一个部门号字段g_deptid int

alter table gongzi drop column g_beizhu

alter table gongzi alter column g_koufa decimal(10,2)

alter table gongzi add g_deptid int

-- 4. 为gongzi表设置名为pk_g的主键，主键包含emp_id、g_date字段
alter table gongzi add constraint pk_g primary key(emp_id,g_date)

-- 5. 为gongzi表设置名为con_deptid的外键约束，参照deptment表的dept_id字段；
    --为gongzi表设置名为con_empid的外键约束，参照emp表的emp_id字段
alter table gongzi add constraint con_deptid foreign key(g_deptid) references deptment(dept_id)
alter table gongzi add constraint con_empid foreign key(emp_id) references emp(emp_id)

-- 6. 为gongzi表g_deptid字段设置名为df_deptid的默认约束，默认值为1
alter table gongzi add constraint df_deptid default 1 for g_deptid

-- 7. 为gongzi表g_date字段设置名为df_date的默认约束，默认值为系统当前日期
alter table gongzi add constraint df_deptid default getdate() for g_date

-- 8. 为gongzi表g_jiben字段设置名为chk_jiben的检查约束，使基本工资不能小于1000
alter table gongzi add constraint chk_jiben check(g_jiben>1000)

-- 9. 用系统存储过程查看表的信息、表的约束
use exam
go
sp_help gongzi
sp_helpconstraint gongzi


-- 10. 插入记录,并查看

    insert into deptment values(1,'销售部')
    insert into deptment values(2,'采购部')
    insert into deptment values(3,'财务部')

    insert into emp values(1,'王晓明',1)
    insert into emp values(2,'张小虎',1)
    insert into emp values(3,'黄佳佳',1)
    insert into emp values(4,'李莉莉',2)
    insert into emp values(5,'赵蕊',2)
    insert into emp values(6,'黄菲',2)
    insert into emp values(7,'李宇',3)
    insert into emp values(8,'赵新',3)

    insert into gongzi values(1,1100,1000,1000,500,0,'20070101',1)
    insert into gongzi values(2,1500,1500,1500,700,0,'20070101',1)
    insert into gongzi values(3,2000,2000,2000,1000,0,'20070101',1)
    insert into gongzi values(4,1200,1200,500,550,0,'20070101',2)
    insert into gongzi values(5,3000,3000,3000,1500,0,'20070101',2)
    insert into gongzi values(6,2000,2000,2000,1000,0,'20070101',2)
    insert into gongzi values(7,1200,1200,500,550,0,'20070101',3)
    insert into gongzi values(8,3000,3000,3000,1500,0,'20070101',3)
    insert into gongzi values(1,1100,1000,1000,500,0,'20070201',1)
    insert into gongzi values(2,1500,1500,1500,700,0,'20070201',1)
    insert into gongzi values(3,2000,2000,2000,1000,0,'20070201',1)
    insert into gongzi values(4,1200,1200,500,550,0,'20070201',2)
    insert into gongzi values(5,3000,3000,3000,1500,0,'20070201',2)
    insert into gongzi values(6,2000,2000,2000,1000,0,'20070201',2)
    insert into gongzi values(7,1200,1200,500,550,0,'20070201',3)
    insert into gongzi values(8,3000,3000,3000,1500,0,'20070201',3)
    insert into gongzi values(1,1100,1000,1000,500,0,'20060101',1)
    insert into gongzi values(2,1500,1500,1500,700,0,'20060101',1)
    insert into gongzi values(3,2000,2000,2000,1000,0,'20060101',1)
    insert into gongzi values(4,1200,1200,500,550,0,'20060101',2)
    insert into gongzi values(5,3000,3000,3000,1500,0,'20060101',2)
    insert into gongzi values(6,2000,2000,2000,1000,0,'20060101',2)
    insert into gongzi values(7,1200,1200,500,550,0,'20060101',3)
    insert into gongzi values(8,3000,3000,3000,1500,0,'20060101',3)
    insert into gongzi values(1,1100,1000,1000,500,0,'20060201',1)
    insert into gongzi values(2,1500,1500,1500,700,0,'20060201',1)
    insert into gongzi values(3,2000,2000,2000,1000,0,'20060201',1)
    insert into gongzi values(4,1200,1200,500,550,0,'20060201',2)
    insert into gongzi values(5,3000,3000,3000,1500,0,'20060201',2)
    insert into gongzi values(6,2000,2000,2000,1000,0,'20060201',2)
    insert into gongzi values(7,1200,1200,500,550,0,'20060201',3)
    insert into gongzi values(8,3000,3000,3000,1500,0,'20060201',3)


select * from deptment
select * from emp
select * from gongzi

-- 11. 计算员工的实发工资，等于（基本工资+绩效工资+岗位工资-扣发工资）
update gongzi set g_shifa=g_jiben+g_gangwei+g_jixiao-g_koufa 
selelct * from gongzi 

-- 12. 查询2007年2月所有职工工资 按gongzi_jiben 降序排序
    select * from gongzi where year(g_date)=2007 and month(g_date)=2  order by g_jiben desc
    select * from gongzi where g_date='20070201' order by g_jiben desc

-- 13. 用COMPUTE计算2007年2月所有职工实发工资总额
    select * from gongzi where year(g_date)=2007 and month(g_date)=2 compute sum(g_shifa) 

-- 14. 查询 工号为2记录
    select * from gongzi where emp_id=2

-- 15. 查询 工号为2的员工的平均实发工资
    select avg(g_shifa) from gongzi where emp_id=2

-- 16. 查询部门为1的所有员工的平均实发工资
    select emp_id,avg(g_shifa) as '平均工资' from gongzi where g_deptid=1 group by emp_id

-- 17. 查询部门为1的所有员工的平均实发工资大于3000的记录
    select emp_id,avg(g_shifa) as '平均工资' from gongzi where g_deptid=1 group by emp_id having avg(g_shifa)>3000

-- 18. 查询 gongzi_jiben小于2000 而且 gongzi_jixiao 小于1500 的记录
    select * from gongzi where gongzi_jiben<2000 and gongzi_jixiao<1500

-- 19. 工号为5的员工2007年2月工资的扣款增加100元，重新算实发工资,并查看
update gongzi set g_koufa=g_koufa+100  where g_id=5 and year(g_date)=2007 and month(g_date)=2 
update gongzi set g_shifa=g_jiben+g_gangwei+g_jixiao-g_koufa where g_id=5 and year(g_date)=2007 and month(g_date)=2 

-- 20. 删除工号为2的员工的工资记录
delete from gongzi where g_id=2

-- 21. 查询每个部门的实发工资总额、平均实发工资额，按总额升序排序
select g_deptid,sum(g_shifa) as '总额',avg(g_shifa) as '平均额' from gongzi group by g_deptid order by sum(g_shifa)

-- 22. 查询每个部门的实发工资额、平均实发工资额，部门最高工资，部门最低工资,按部门排序
select g_deptid,sum(g_shifa) as '总额',avg(g_shifa) as '平均额',max(g_shifa) as '最高工资',min(g_shifa) as '最低工资' from gongzi group by g_deptid order by g_deptid

-- 23. 查询2007年2月实发工资在前三名的职工工资记录
select top 3 * from gongzi where year(g_date)=2007 and month(g_date)=2  order by g_shifa desc

-- 24. 查询实发工资总额在前三名的职工工资记录
select top 3 emp_id,sum(g_shifa) as '总额' from gongzi group by emp_id order by sum(g_shifa) desc

-- 25. 查询每年的实发工资总额、平均实发工资
select year(g_date) as 年,sum(g_shifa) as 总额,avg(g_shifa) as 平均工资 from gongzi group by year(g_date)

-- 26. 查询每年、每月的实发工资总额、平均实发工资
select year(g_date) as 年,month(g_date) as 月,sum(g_shifa) as 总额,avg(g_shifa) as 平均工资 from gongzi group by year(g_date),month(g_date) as 月

-- 27. 查询 姓名为张小虎的工资记录（使用多表查询）
select g.emp_id,e.emp_name,g.g_gangwei,g.g_jixiao,g.g_koufa,g.g_shifa from gongzi as g,emp as e where g.emp_id=e.emp_id and e.emp_name='张小虎'

-- 28. 查询 姓名为张小虎的平均实发工资（使用多表查询）
select avg(g.g_shifa) as '平均实发工资' from gongzi as g,emp as e where g.emp_id=e.emp_id and e.emp_name='张小虎'

-- 29. 删除记录张小虎的工资记录(使用子查询)
delete from gongzi where emp_id=(select emp_id from emp where emp_name='张小虎')

-- 30. 创建一个视图v_g1，查询2007年工资记录，显示emp_id,g_date,g_jiben字段,并查看
create view v_g1 as select emp_id,g_date,g_jiben from gongzi where year(g_date)=2007
select * from v_g

-- 31. 创建一个视图v_g2，查询基本工资大于平均的基本工资记录，显示emp_id,g_date,g_jiben字段
create view v_g2 as select emp_id,g_date,g_jiben from gongzi where g_jiben>(select avg(g_jiben) from gongzi) 

-- 32. 创建一个唯一索引in_g，在deptment表的dept字段上,并验证
create unique index in_g on deptment(dept)
insert into deptment values(4,'销售部')

-- 33. 创建一个存储过程p_g1，查询显示每个部门（实发工资）合计工资、平均工资（不带参数的存储过程）
create procedure p_g1 as select g_deptid,sum(g_shifa) as '总额',avg(g_shifa) as '平均额' from gongzi group by g_deptid order by g_deptid
p_g1

-- 34. 创建一个存储过程p_g2，查询显示某指定部门（实发工资）合计工资、平均工资（带参数的存储过程）
create procedure p_g2 @dept int as select g_deptid,sum(g_shifa) as '总额',avg(g_shifa) as '平均额' from gongzi where g_deptid=@dept group by g_deptid 
p_g2 1

-- 35. 创建一个触发器t_g，当插入或修改数据的基本工资<1000，拒绝插入，并验证
create trigger t_g on gongzi for insert,update as
if update(g_jiben) 
begin
  if(select g_jiben from inserted)<1000
    begin
    print '分数不能小于 1000'
    rollback transaction
    end
end
insert into gongzi values(8,1000,3000,3000,1500,0,'20060301',3)  -- 提示出错信息，拒绝插入
insert into gongzi values(8,1100,3000,3000,1500,0,'20060301',3)  -- 正常插入