实验二_查询语句_ngc

最新推荐文章于 2022-10-24 17:39:13 发布

kar_w

最新推荐文章于 2022-10-24 17:39:13 发布

阅读量805

点赞数 1

文章标签： sql

本文链接：https://blog.csdn.net/qq_44593584/article/details/106005548

版权

这篇博客展示了多个SQL查询语句的实例，包括选取特定条件的数据、部门薪资汇总、平均奖金排序以及创建视图等操作。内容涵盖员工基本信息、工资详情、市场部门工资以及学生选课情况等，强调了SQL在数据查询和处理中的应用。

摘要由CSDN通过智能技术生成

use pay

select distinct professor from person;
–男员工信息
select no,name,sex,birthday,professor,deptno from person where sex = ‘男’;
–00102女员工信息
select no,name,sex,birthday,professor,deptno from person where deptno=00102 and sex = ‘女’;
–1.5倍工资后实际收入
select base*1.5+bonus-deduct as real_fact from pay where no = ‘000001’;
–00102基本信息
select person.no,name,sex,birthday,professor,deptno,base,bonus,deduct,fact
from person join pay on person.no = pay.no
where person.deptno = 00102
order by fact desc;

select deptno as ‘部门’,sum(fact) as ‘总额’
from pay,person
where pay.no = person.no and year = 2005
group by deptno;
–平均奖金升序排序
select deptname,avg(bonus) as 平均工资 from dept,pay,person
where dept.deptno = person.DeptNo and person.no = pay.no and year = 2005 and month = 1
group by dept.deptno,deptname
having avg(bonus) >