2022年武汉理工大学数据库系统综合实验1.3数据查询实验

一,基本查询任务
1.查询每个雇员的姓名、地址和联系电话
select name,address,phone from employee;

2.查询employee表中部门号和性别，要求使用DISTINCT消除重复行
select distinct departmentID,gender from employee;

3.查询所有财务部的员工的姓名和联系电话
select name,phone from employee,department where employee.departmentID=department.departmentID and departname='财务部';

4.查询employee表中女雇员的地址和电话，使用AS子句将结果中各列
的标题指定为“地址”和“电话”
select address as '地址',phone as '电话' from employee where gender='0';

5.查询财务部雇员的最高和最低实际收入
select max(income-outcome),min(income-outcome) from employee,salary where employee.employeeID=salary.employeeID and departmentID='1';

6.找出所有收入在2000到3000元之间的员工编号
select employeeID from salary where income between 2000 and 3000;

二,子查询的使用
1.查找在财务部工作的员工的情况
select *from employee,department where employee.departmentID=department.departmentID and departName='财务部';

2.用子查询方法查找所有收入在2500元以下的雇员的情况
select *from employee,salary where employee.employeeID=salary.employeeID and income<2500;

三,连接查询的使用
1.查询每个雇员的情况及其薪水情况
select employee.*,salary.* from employee,salary where employee.employeeID=salary.employeeID;

2.使用内连接查询名字为“王林”的员工所在部门
select departName from employee inner join department on employee.departmentID=department.departmentID where employee.name='王林';

3.查找财务部收入在2000元以上的雇员姓名和薪水详情
select name,income,outcome from employee inner join salary on employee.employeeID=salary.employeeID inner join department on employee.departmentID=department.departmentID where salary.income>2000 and departName='财务部';

四,GROUP BY、ORDER BY和LIMIT子句的使用
1.查询employee中男性和女性的人数
select count(*) 人数 from employee group by employee.gender;

2.查找员工数超过2人的部门名称和员工数量
 select departName,count(*) 员工数量 from employee,department where employee.departmentID=department.departmentID group by departName having count(*)>2;

3.将employee表中的员工号码由大到小排列
select employeeID from employee order by employee.employeeID desc;

4.返回employee表中前5位员工的信息
select *from employee limit 0,5;