一、实验步骤
(列出上机实现步骤。)
1.单表查询:运用SQL完成以下数据查询,并将查询SQL语句按照题号保存在query1.txt文件中。
(1)查询每个雇员的所有信息
SELECT EmployeeID,Name,Birthday,Sex,Address,Zip,PhoneNumber,EmailAddress,DepartmentID
FROM Employee;
(2)查询每个雇员的地址和电话
SELECT Address,PhoneNumber
FROM Employee;
(3)查询EmployeeID为1001的雇员的地址和电话。
SELECT Address,PhoneNumber
FROM Employee
where EmployeeID='1001';
(4)查询女雇员地址和电话,并用AS子句将结果中各列的标题分别指定为“地址”和“电话”。
SELECT Address AS 地址,PhoneNumber AS 电话
FROM Employee
where Sex='0';
(5)计算每个雇员的实际收入。
SELECT EmployeeID ,(income-outcome)
AS 实际工资
FROM Salary;
(6)找出所有姓王的雇员的部门号。
SELECT DepartmentID
FROM Employee
WHERE Name LIKE '王%';
2、练习多表连接查询和嵌套查询:运用SQL完成以下数据查询,并将查询SQL语句按照题号保存在query1.txt文件中。
(1)查询每个雇员的情况及工资情况(工资=Income - Outcome)
SELECT *,(income-outcome)
as 工资
FROM Employee,Salary;
(2)查询财务部工资在2200元以上的雇员姓名及工资情况
SELECT Name,Income
FROM Employee,Salary
WHERE DepartmentID='1' AND income>2200;
(3)查询研发部在1966年以前出生的雇员姓名及其工资详情
SELECT Income,Name
from Employee,Salary
where DepartmentID='2'AND Birthday<'1966-1-1 0:0:0';
(4)查询人力资源部雇员的最高和最低工资
SELECT MAX(Income)as 最高工资,
MIN(Income)as 最低工资
FROM Salary,Employee
where DepartmentID='3';
(5)将各雇员的情况按工资由低到高排列
SELECT *
FROM Salary,Employee
ORDER BY Income ASC;
(6)求各部门的雇员数
SELECT DepartmentID,COUNT(*)
as 总人数
FROM Employee
Group By DepartmentID;
(7)找出所有在财务部和人力资源部工作的雇员的编号
SELECT EmployeeID
from Employee,Departments
where Employee.DepartmentID=Departments.DepartmentID;
(8)统计人力资源部工资在2500以上雇员的人数
Select COUNT(Name)
from Salary,Employee
where DepartmentID='3' AND Income>2500;
(9)求财务部雇员的总人数
select COUNT(Name)
from Employee
where DepartmentID='1';
(10)求财务部雇员的平均工资
select AVG(Income)
as 平均工资
from salary,Employee
where Salary.EmployeeID=Employee.EmployeeID AND Employee.DepartmentID='1';
(11)查找比所有财务部的雇员工资都高的雇员的姓名
select Name
from Employee,Salary
where Employee.EmployeeID=Salary.EmployeeID and Salary.Income>all
(
select MAX(Income)
from Salary,Employee
where Employee.DepartmentID='1'and Employee.EmployeeID=Salary.EmployeeID
);
(12)查找财务部年龄不低于研发部所有雇员年龄的雇员的姓名
select Name
from Employee
where Employee.Birthday<all
(
select Min(Birthday)
from Employee
where Employee.DepartmentID='2'
)
and DepartmentID='1';
(13)查找在财务部工作的雇员的情况
select *
from Employee,Salary,Departments;