数据库练习（二）

   Employees表结构

列名	数据类型	长度	是否允许为空	说明
EmployeeID	Char	6	否	员工编号，主键
Name	Char	10	否	姓名
Birthday	Datetime	8	否	出生日期
Sex	Bit	1	否	性别，默认值为1
Address	varChar	40	是	地址
Zip	Char	6	是	邮编
PhonNumber	Char	12	是	电话号码
Email	varChar	30	是	电子邮件
DepartmentID	Char	3	否	员工部门号，外键

Departments表结构

列名	数据类型	长度	是否允许为空	说明
DepartmentID	Char	3	否	部门编号，主键
DepartmentName	Char	20	否	部门名，要求唯一
Note	文本（text）	16	是	备注

Salary表结构

列名	数据类型	长度	是否允许为空	说明
EmployeeID	Char	6	否	员工编号，主键
Income	Flaot	8	否	收入，要求输入的数据大于0
Outcome	flaot	8	否	支出

 

 

查询操作：

一、简单查询

1、查询每个员工的所有数据。

select *

from Employees,Departments,Salary

where Employees.EmployeeID=Salary.EmployeeID and Employees.DepartmentID=Departments.DepartmentID;

2、查询Departments表中的所有记录。

select *

from Departments;

3、查询Salary表中的所有记录。

select *

from Salary;

4、查询每个员工的地址和电话。

select Name,Address,PhoneNumber

from Employees

5、查询每个部门的部门号与部门名。

select DepartmentID,DepartmentName

from Departments

6、查询每个员工的员工编号与收入。

select Employees.EmployeeID,Name,Income

from Employees,Salary

where Employees.EmployeeID=Salary.EmployeeID

7、查询所有女员工的姓名和地址及部门号，并用as子句将结果中各列的标题分别指定为姓名和地址及部门号。

select Name as '姓名',Address as '地址',DepartmentID as '部门号'

from Employees

where Sex='false'

8、计算每个员工的实际收入。

select Name as '姓名',(Income-Outcome) as '实际收入'

from Employees,Salary

where Employees.EmployeeID=Salary.EmployeeID;

9、找出所有收入在2000~3000之间的员工编号。

select Employees.EmployeeID as '员工编号',Name as '姓名',(Income-Outcome) as '实际收入'

from Employees,Salary

where Employees.EmployeeID=Salary.EmployeeID and 2000<(Income-Outcome)and (Income-Outcome)<3000;

10、查询员工的基本信息并按出生时间的先后排序。

select *

from Employees,Departments,Salary

where Employees.EmployeeID=Salary.EmployeeID and Employees.DepartmentID=Departments.DepartmentID

order by Birthday;

11、找出所有在部门‘001’或部门‘002’工作的员工的部门号及姓名。

select DepartmentID,Name

from Employees

where DepartmentID='001' or DepartmentID='002';

12、找出所有姓胡的员工的姓名及部门号。

select DepartmentID,Name

from Employees

where Name like '胡%';

13、找出所在其地址中含有“湖北”的员工的姓名及地址。

select Name,Address

from Employees

where Address like '%湖北%';

二、复杂查询

 

一、子查询

1、查找所有在开发部工作的员工的基本信息。

select *

from Employees,Departments,Salary

where Employees.EmployeeID=Salary.EmployeeID and Employees.DepartmentID=Departments.DepartmentID and DepartmentName='开发部';

2、查找所有收入在2500以下的员工的姓名及部门号。

select DepartmentID,Name,(Income-Outcome) as '实际收入'

from Employees,Salary

where Employees.EmployeeID=Salary.EmployeeID and (Income-Outcome)<2500;

3、查找开发部年龄低于营销部员工年龄的员工的姓名。

select Name

from Employees,Departments

where Employees.DepartmentID=Departments.DepartmentID and DepartmentName='开发部' and Birthday

    >all(select Birthday from Employees,Departments where Employees.DepartmentID=Departments.DepartmentID and DepartmentName='营销部');

三、连接查询

1、查询每个员工的基本信息及其薪水情况。
select *,(Income-Outcome) as '薪水'

from Employees,Salary

where Employees.EmployeeID=Salary.EmployeeID;

2、查询每个员工的情况及其工作部门的情况。

select EmployeeID,Name,Sex,Departments.DepartmentID,DepartmentName,Note

from Employees,Departments

where Employees.DepartmentID=Departments.DepartmentID;

3、查找财务部收入在2200以上的员工的姓名及其薪水详情。

select Name,(Income-Outcome) as '薪水'

from Employees,Salary

where Employees.EmployeeID=Salary.EmployeeID and (Income-Outcome)>2200;

4、查找开发部在1999年以前出生的员工的姓名及其薪水情况。

select Name,(Income-Outcome) as '薪水'

from Employees,Departments,Salary

where Employees.EmployeeID=Salary.EmployeeID and Employees.DepartmentID=Departments.DepartmentID and Birthday<'1999' and DepartmentName='开发部';

5、将各员工的情况按收入由低到高排序。

select *

from Employees,Departments,Salary

where Employees.EmployeeID=Salary.EmployeeID and Employees.DepartmentID=Departments.DepartmentID

order by Income;

四、数据的分类与汇总

1、求所有女员工的平均收入。

select AVG(Income) as '所有女员工的平均收入'

from Employees,Salary

where Employees.EmployeeID=Salary.EmployeeID and Sex='false';

2、求开发部员工的平均收入。

select AVG(Income) as '开发部员工的平均收入'

from Employees,Departments,Salary

where Employees.EmployeeID=Salary.EmployeeID and Employees.DepartmentID=Departments.DepartmentID and DepartmentName='开发部';

3、查询营销部员工的最高和最低收入。

select max(Income) as '营销部员工的最高收入',min(Income) as '营销部员工的最高收入'

from Employees,Departments,Salary

where Employees.EmployeeID=Salary.EmployeeID and Employees.DepartmentID=Departments.DepartmentID and DepartmentName='营销部';

4、求所有男员工的平均实际收入。

select avg(Income-Outcome) as '所有男员工的平均实际收入'

from Employees,Salary

where Employees.EmployeeID=Salary.EmployeeID and Sex='true';

5、求开发部员工的平均实际收入。

select AVG(Income-Outcome) as '开发部员工的平均实际收入'

from Employees,Departments,Salary

where Employees.EmployeeID=Salary.EmployeeID and Employees.DepartmentID=Departments.DepartmentID and DepartmentName='开发部';

6、查询营销部员工的最高和最低实际收入。

select max(Income-Outcome) as '营销部员工的最高收入',min(Income-Outcome) as '营销部员工的最高收入'

from Employees,Departments,Salary

where Employees.EmployeeID=Salary.EmployeeID and Employees.DepartmentID=Departments.DepartmentID and DepartmentName='营销部';

7、求年龄在20以上的总人数。

select COUNT(EmployeeID) as '年龄在岁以上的总人数'

from Employees

where YEAR(getdate())-YEAR(Birthday)>20;

8、求开发部员工的总人数。

select COUNT(EmployeeID) as '开发部总人数'

from Employees,Departments

where Employees.DepartmentID=Departments.DepartmentID and DepartmentName='开发部';

9、求营销员收入在4000以上的总人数。

select COUNT(Employees.EmployeeID) as '收入大于的营销员总人数'

from Employees,Salary,Departments

where Employees.EmployeeID=Salary.EmployeeID and Employees.DepartmentID=Departments.DepartmentID and DepartmentName='营销部' and (Income-Outcome)>4000;

10、求各部门的员工人数。

select Departments.DepartmentName as '部门',COUNT(Employees.DepartmentID) as '人数'

from Employees,Departments

where Employees.DepartmentID=Departments.DepartmentID

group by Departments.DepartmentName;

11、统计各部门收入在6000以上的员工的人数。

select DepartmentName,COUNT(*) as '部门收入以上的总人数'

from Employees,Departments,Salary

where Employees.EmployeeID=Salary.EmployeeID and Employees.DepartmentID=Departments.DepartmentID and Income>6000

group by DepartmentName;

文章为作者自己练习作业，如若有错误，欢迎留言讨论，博主很喜欢和大家共同进步哟~~~~

另外，文章为博主自己电脑所做，上传目的只是为了可以帮助到需要帮助的人，电脑信息以及数据库中信息均未打码，还望各位大侠不要恶意攻击哈~~~~~~~~~~~