软件测试-MySQL语言基础-3数据演练

数据演练

1. 数据说明

• 部门表departments；
  • 部门表的deptid 字段 与员工表的deptid字段关联。
• 员工表 employees；
  • 员工表的empid字段与工资表的empid字段关联。
  • leader 字段为空代表领导岗。
• 工资表salary。

部门表：departments。

员工表：employees

工资表：salary

练习

1.列出男职工的总数和女职工的总数

• select sex,count(*) from employees group by sex;

• 

2.列出非党员职工的总数

• select politicalstatus as po,count(*) from employess group by po having po != ‘党员’;

• 

3.列出所有职工工号，姓名以及所在部门名称。

• select empid,empname,deptname from employees as em inner join departments as de
  on em.deptid = de.deptid

• 

4.列出所有职工工号，姓名和对应工资

• select em.empid,empname,salary from employees as em inner join salary as sa on em.empid = sa.empid order by em.empid;

• 

5，列出领导岗的姓名及所在名称

• select empname,deptname from employees as em
  inner join departments as de on em.deptid = de.deptid
  where leader is null;

• 

 6，列出职工总人数大于4的部门号和总人数

• select deptid,count(*) from employees group by deptid having count(*) > 4;

•