四、SQL语句：查询_2

最新推荐文章于 2022-10-03 00:51:48 发布
xibuzhihun
最新推荐文章于 2022-10-03 00:51:48 发布
阅读量981
点赞数
分类专栏： SQL 文章标签： sql insert 工作 table null
本文链接：https://blog.csdn.net/xibuzhihun/article/details/6877033
版权
SQL 专栏收录该内容
4 篇文章 0 订阅
订阅专栏
一、查询的综合练习
/*1.建表*/

/*职工表 worker*/
create table worker 
(
 职工号 int primary key 
,姓名 char(8)
,性别 char(2)
,出生日期 datetime
,党员否 varchar(2) default('否') not null
,参加工作 datetime
,部门号 int 
)

/*部门表 depart */
create table depart
(
 部门号 int  
,部门名 char(10)
,primary key(部门号)
)

/*工资表 salary*/
create table salary
(
 职工号 int not null
,姓名 char(8)
,日期 datetime not null  
,工资 decimal(6,1)
,primary key(职工号,日期)
)

/*插入数据*/
insert into depart(部门号,部门名) values(1,'财务处')
insert into depart(部门号,部门名) values(2,'人事处')
insert into depart(部门号,部门名) values(3,'市场部')


insert into worker(职工号,姓名,性别,出生日期,党员否,参加工作,部门号) values(1,'孙华','男','1952-03-01','是','1970-10-10',1)
insert into worker(职工号,姓名,性别,出生日期,党员否,参加工作,部门号) values(2,'陈明','男','1948-08-05',default,'1965-01-01',2)
insert into worker(职工号,姓名,性别,出生日期,党员否,参加工作,部门号) values(3,'程西','男','1980-10-06',default,'2002-10-07',1)
insert into worker(职工号,姓名,性别,出生日期,党员否,参加工作,部门号) values(4,'孙天奇','女','1965-10-03','是','1987-10-07',3)
insert into worker(职工号,姓名,性别,出生日期,党员否,参加工作,部门号) values(5,'刘夫文','男','1942-03-03','是','1969-09-10',2)
insert into worker(职工号,姓名,性别,出生日期,党员否,参加工作,部门号) values(6,'刘天','男','1942-03-03','是','1969-09-10',2)
insert into worker(职工号,姓名,性别,出生日期,党员否,参加工作,部门号) values(10,'刘夫文','女','1942-09-21','是','1965-09-11',3)


insert into salary(职工号,姓名,日期,工资) values(1,'孙华','2004-04-01',1201.5)
insert into salary(职工号,姓名,日期,工资) values(2,'陈明','2004-04-01',1350.6)
insert into salary(职工号,姓名,日期,工资) values(3,'程西','2004-04-01',780.5)
insert into salary(职工号,姓名,日期,工资) values(4,'孙天奇','2004-04-01',900.0)
insert into salary(职工号,姓名,日期,工资) values(5,'刘夫文','2004-04-01',2006.8)
insert into salary(职工号,姓名,日期,工资) values(1,'孙华','2004-05-01',690.5)
insert into salary(职工号,姓名,日期,工资) values(2,'陈明','2004-05-01',3100.6)
insert into salary(职工号,姓名,日期,工资) values(3,'程西','2004-05-01',1563.2)
insert into salary(职工号,姓名,日期,工资) values(4,'孙天奇','2004-05-01',2310.4)
insert into salary(职工号,姓名,日期,工资) values(5,'刘夫文','2004-05-01',180.2)
insert into salary(职工号,姓名,日期,工资) values(10,'刘夫文','2004-05-01',5000.0)


/*1.显示所有职工的年龄*/
select 职工号,姓名,(year(getdate())-year(出生日期)) as 年龄
from worker

/*2.求出各部门的党员人数*/
select d.部门名,count(党员否) as 党员人数
from worker w,depart d
where  党员否 = '是'
   and w.部门号 = d.部门号
group by d.部门名

/*3.显示所有职工的姓名和2004年1月份的工资*/
select 姓名,工资
from salary
where 日期 = '2004-04-01'

/*4.显示所有职工的职工号,姓名和平均工资*/

/*  --当职工号与姓名一一对应时,即无同名存在时,可使用该方法
select s.职工号,s.姓名,avg(工资) as 平均工资
from worker w,salary s
where  w.职工号 = s.职工号
group by s.职工号,s.姓名
*/

select w.姓名,s.职工号,s.平均工资     /*子查询*/
from worker w ,(
	select s.职工号,avg(工资) as 平均工资
	from worker w,salary s
	where  w.职工号 = s.职工号
	group by s.职工号) s
where w.职工号 = s.职工号

/*5.显示所有职工的职工号,姓名,部门名和2004年5月的工资,并按部门名顺序排序*/
select  w.职工号, w.姓名,d.部门名,s.工资 as '2004年5月_工资'
from worker w,depart d,salary s
where w.职工号 = s.职工号 
  and w.部门号 = d.部门号
  and s.日期  = '2004-05-01'
order by d.部门名

/*6.显示各部门名和该部门所有职工的平均工资*/
select d.部门名,avg(工资) as '该部门平均工资'
from worker w,depart d,salary s
where w.职工号 = s.职工号 
  and w.部门号 = d.部门号
group by d.部门名

/*7.显示所有平均工作高于1200的部门名和对应的平均工资 ---聚合函数不能放在where字句后*/
select d.部门名,avg(工资) as '该部门平均工资'
from worker w,depart d,salary s
where w.职工号 = s.职工号 
  and w.部门号 = d.部门号
group by d.部门名
having avg(工资) > 1200

/*8.显示各部门的职工号,姓名和部门类型,其中财务处和人事处属于管理部门*/
select w.职工号,姓名,
      case 部门名
	when '财务处' then '管理部'
	when '人事处' then '管理部'
        when '市场部' then '市场部'
      else '其他'
      end as '部门名'
from worker w, depart d
where w.部门号 = d.部门号


/*9.如果存在职工号为10的职工,则显示其工作部门名字,否则显示相应的提示信息*/
BEGIN
	declare @woker_name int
	set @woker_name = 10
	
	if (select 职工号 from worker where 职工号 = @woker_name) = @woker_name
   		BEGIN
			select '存在职工号为10的员工!'
			select '他的信息为:'

			select w.姓名,d.部门名
			from worker w,depart d
			where w.部门号 = d.部门号 
          		  and w.职工号 = @woker_name
   		END
	else
   		select '对不起,不存在职工号为10的员工!' 
END

/*10.比较男职工和女职工的平均工资*/
BEGIN
	Declare @avg_male int
	Declare @avg_female int 
	
	set @avg_male = (
		select avg(工资) as '平均工资'
		from worker w,salary s
		where w.职工号 = s.职工号
		group by 性别
		having 性别 = '男')
	
	set @avg_female = (
		select avg(工资) as '平均工资'
		from worker w,salary s
		where w.职工号 = s.职工号
		group by 性别
		having 性别 = '女')


	print '男职工的平均工资:'
	print  @avg_male

	print '女职工的平均工资:'
	print  @avg_female
	

	if  (@avg_male > @avg_female)
		print '男职工比女职工的工资高多了!' 
	else if @avg_male / @avg_female between 0.8 and 1.5
		print '男职工比女职工的工资差不多!'
	else	
		print '女职工比男职工的工资高多了!'
END