《实验4 数据库的查询和视图.doc》由会员分享，可在线阅读，更多相关《实验4 数据库的查询和视图.doc（9页珍藏版）》请在金锄头文库上搜索。
1、实验4 数据库的查询和视图select * from Employeesselect * from Departmentsselect * from Salaryselect Address,PhoneNumber from Employeesselect distinct departmentid, sex from Employeesselect address,phonenumber from Employeeswhere EmployeeID = 000001select employeeid from Salarywhere InCome 2000select name addres
2、s from Employeeswhere YEAR(Birthday) 1970select Employeeid,name from Employeeswhere DepartmentID in(select DepartmentID from Departmentswhere DepartmentName = 财务部)select address as 地址, phonenumber as 电话from Employeeswhere Sex = 0select name 姓名,birthday 出生日期from Employeeswhere Sex = 1select name as 姓
3、名,case when sex =1 then 男when sex = 0 then 女end 性别from Employeesselect name 姓名,address 住址,casewhen income 3000 then 高收入end 收入水平from Employees join Salaryon Employees.EmployeeID=Salary.EmployeeIDselect employeeid,income-outcome 实际收入from salaryselect COUNT(*)from Employeesselect AVG(income) from salar
4、yselect MAX(EmployeeID) from Employeesselect sum(outcome) from Salaryselect MAX(income),MIN(income) from Salaryselect Departmentid from employeeswhere name like 王%select employeeid,departmentid from Employeeswhere Address like %中山%select name,address,education from Employeeswhere EmployeeID like %0_
5、select Employeeid from Salarywhere InCome between 2000 and 3000select Employeeid from Employeeswhere DepartmentID = 1 or DepartmentID = 2select employeeid as 编号,income as 收入into 收入在1500元以上的员工from Salarywhere InCome 1500select employeeid as 编号,name as 姓名into 男员工from Employeeswhere Sex = 1select * fro
6、m Employeeswhere DepartmentID =(select DepartmentID from Departmentswhere DepartmentName = 财务部)select * from Employeeswhere EmployeeID in(select EmployeeID from Salarywhere InCome 2500)select name from Employeeswhere DepartmentID in(select DepartmentID from Departmentswhere DepartmentName = 财务部)and
7、Birthday all(select income from Employees join Salary on Employees.EmployeeID = Salary.EmployeeIDwhere DepartmentID in(select DepartmentID from Departmentswhere DepartmentName = 财务部)select name from Employees join Salary on Employees.EmployeeID = Salary.EmployeeIDwhere income all(select income from
8、Employees join Salary on Employees.EmployeeID = Salary.EmployeeIDwhere DepartmentID in(select DepartmentID from Departmentswhere DepartmentName = 财务部)select name from Employees join Departmentson Employees.DepartmentID = Departments.DepartmentIDwhere Birthday 2000select name,income,outcomefrom Emplo
9、yees,Salary,Departmentswhere Employees.EmployeeID = Salary.EmployeeIDand Employees.DepartmentID = Departments.DepartmentIDand YEAR(Birthday) 2500select sex,COUNT(sex)from Employeesgroup by Sexselect DepartmentName,COUNT(DepartmentName) from Employees join Departmentson Employees.DepartmentID = Departments.DepartmentIDgroup by DepartmentNameselect education,COUNT(Education) from Employeesgroup by Educationselect departmentname,COUNT(*) from Employees join Departmentson Employe
