数据查询与表的创建作业.doc

图1用两种方法建立一个新数据库HRM（也可任意取名）,如上图所示(图1);鼠标右击数据库HRM- 数据查询,然后输入如下SQLServer语句:DepartmentID CHAR(3) NOT NULL PRIMARY KEY,DepartmentName CHAR(20) NOT NULL,Note TEXT)CREATE TABLE Employee(EmployeeID CHAR(6) NOT NULL PRIMARY KEY,NAME CHAR(10) NOT NULL,Birthday Datetime NOT NULL,SEX char(2) NOT NULL,Address CHAR(20),Zip CHAR(6),PhoneNumber CHAR(12),EmailAddress CHAR(30),DepartmentID CHAR(3) NOT NULL REFERENCES Departments(DepartmentID)ON DELETE NO ACTION)CREATE TABLE Salary(EmployeeID CHAR(6) NOT NULL REFERENCES Employee(EmployeeID)ON DELETE NO ACTION,Income FLOAT(8) NOT NULL,OutCome FLOAT(8) NOT NULL)在数据库HRM中，建立如下所示的三个表：Employee表, Departments表, Salary表对上节建立的表输入数据：Departments表：Employee表Salary表 如下图:练习下面简单的查询语句：a) 查询每个雇员的所有信息:输入语句select *from Employeeb) 查询每个雇员的地址和电话SELECT PhoneNumber,Address from Employeec) 查询EmployeeID为000001的雇员的地址和电话SELECT PhoneNumber,Address from Employeewhere EmployeeID=1001d) 查询女雇员地址和电话，并用AS子句将结果中各列的标题分别指定为“地址”和“电话”。SELECT 电话=PhoneNumber,地址=Address from Employeewhere SEX=0e) 计算每个雇员的实际收入。select 实际收入=Income-OutCome from Salaryselect DepartmentID from Employeewhere NAME like 王%f) 找出所有姓王的雇员的部门号3a) 查询每个雇员的情况及工资情况（工资=Income - Outcome）select Income-outcome,Employee.*from Employee,salarywhere Employee.EmployeeID=Salary.EmployeeIDb) 查询财务部工资在2200元以上的雇员姓名及工资情况select Name,Income-outcome as工资,DepartmentName from Departments,Salary,Employeewhere Departments.DepartmentID=Employee.DepartmentID and Salary.EmployeeID=Employee.EmployeeID and DepartmentName=财务部Group by DepartmentName,Income,Outcome,Employee.NAMEhaving (Income-outcome)2200c) 查询人力资源部雇员的最高和最低工资select MAX(Income-Outcome)as最高工资,min(Income-Outcome)as最低工资 from Salarywhere EmployeeID like 3%d) 将各雇员的情况按工资由低到高排列select income,employee.*from employee,salarywhere salary.employeeid=employee.employeeid order by income asce) 求各部门的雇员数select departments.departmentname,count(*)as员工数 from employee,departmentswhere employee.departmentid=departments.departmentidgroup by departments.departmentnamef找出所有在财务部和人力资源部工作的雇员的编号select DepartmentName,EmployeeID from Departments,Employeewhere Departments.DepartmentID=Employee.DepartmentIDgroup by DepartmentName,EmployeeIDhaving DepartmentName=财务部or DepartmentName=人力资源部g.和统计人力资源部工资在2500以上雇员的人数select count(*)as人力资源部以上的人数from departments,employee,salarywhere departments.departmentid=employee.departmentidand salary.employeeid=employee.departmentidand departmentname in(人力资源部)and income =2500h.求财务部雇员的总人数select count(*)as 财务部员工人数 from employee where departmentid in(1)i求财务部雇员的平均工资select Avg(Income-outcome)as平均工资,DepartmentName from Departments,Salary,Employeewhere Departments.DepartmentID=Employee.DepartmentID and Salary.EmployeeID=Employee.EmployeeIDgroup by DepartmentName,Departments.DepartmentIDhaving Departments.DepartmentName=财务部j查找比所有财务部的雇员工资都高的雇员的姓名 正确的为：select namefrom employee,salarywhere employee.employeeid=salary.employeeidand incomeall(select income from salary where employeeid in (select employeeid from employee,departments where departments.departmentid=employee.departmentidand departmentname=财务部)k查找财务部年龄不低于研发部所有雇员年龄的雇员的姓名select namefrom employee,departmentswhere birthdayall(select birthdayfrom employee where departmentid=2)and departments.departmentid=employee.departmentidand