sql实验四参考代码写实验报告利器.doc

实验四 实验数据库的查询和视图-3.1实验内容.use yggl-3.(1)select * from Employees【思考与练习】aselect * from Salary 【思考与练习】bselect * from Departments-3.(1)select Name as 姓名,Address as 地址, PhoneNumber as 电话号码 from Employees【思考与练习】aselect 编号,收入 from Salary【思考与练习】bselect 部门名称 from Departments【思考与练习】cselect Department as 部门号,Sex from Employees【思考与练习】dselect distinct Department,sex from Employees-3.(1)select EmployeeID,Address,sex from Employees where EmployeeID=000001【思考与练习】aselect 编号 from Salary where 收入2000【思考与练习】bselect Name,Address from Employees where Birthday1970-01-01【思考与练习】cselect EmployeeID,Name from Employees where Department=1-3.(1)select Address as 地址,PhoneNumber as 电话 from Employees where Sex=0【思考与练习】aselect Name as 姓名,Birthday as 出生日期 from Employees where Sex=1-3.(1)select Name,case when Sex=1 then 男when Sex=0 then 女end as 性别from Employees【思考与练习】aselect Name,Address,Casewhen 收入3000 then 高收入end as 收入水平from Employees,Salarywhere Employees.EmployeeID=Salary.编号-3.(1)select Name,(收入-支出)as 实际收入from Employees,Salarywhere Employees.EmployeeID=Salary.编号-3.(1)select COUNT(EmployeeID)as 员工总数 from Employees【思考与练习】aselect AVG(收入)as 平均收入 from Salary【思考与练习】bselect MAX(EmployeeID)as 最大员工编号 from Employees【思考与练习】cselect SUM(支出)as 总支出 from Salary【思考与练习】dselect max(收入)as 最高收入,min(收入)as 最低收入 from Salary-3.(1)select Name,Department from Employees where Name like 王%【思考与练习】aselect EmployeeID,PhoneNumber,Department from Employees where Address like %中山%【思考与练习】bselect Name,Education,Address from Employees where EmployeeID like %0-3.(1)select 编号 from Salary where 收入 between 2000 and 3000【思考与练习】aselect EmployeeID from Employees where Department in (1,2)-3.(1)select 编号,收入 into newT from Salary where 收入1500select * from newT【思考与练习】aselect EmployeeID,Name into men from Employees where Sex=1select * from men-3.(2)select * from Employees where Department=(select 部门号 from Departments where 部门名称=财务部)【思考与练习】aselect * from Employees where EmployeeID in(select 编号 from Salary where 收入2500)-3.(2)/*方法一*/select Name from Employees where Birthday(select min(Birthday) from Employees where Department =(select 部门号 from Departments where 部门名称 =研发部) and Department=(select 部门号 from Departments where 部门名称 =财务部)/*首先求研发部年龄最大的员工MaxYear=min(研发部.birthday),再把财务部员工的年龄与之相比较,最后查找财务部.BirthdayMaxYeay的记录并在表中投影.*/*方法二*/select Name from Employees where (Department in(select 部门号 from Departments where 部门名称=财务部)and Birthdayall(select 收入 from Salary where 编号 in(select EmployeeID from Employees where Department=(select 部门号 from Departments where 部门名称=财务部)-3.(2)select Name from Employees where EmployeeID in(select 编号 from Salary where 收入 all(select 收入 from Salary where 编号 in(select EmployeeID from Employees where Department in(select 部门号 from Departments where 部门名称=财务部)【思考与练习】aselect Name from Employees where Birthday2000【思考与练习】aselect Name,收入 from Employees,Salary,Departments where Employees.EmployeeID=Salary.编号 and Employees.Department=Departments.部门号 andBirthday2500 and 编号 in(select EmployeeID from Employees where Department=(select 部门号 from Departments where 部门名称=财务部)-3.5.select casewhen Sex=1 then男when Sex=0 then女end as 性别,COUNT(EmployeeID) as 人数from Employees group by Sex【思考与练习】aselect 部门名称,COUNT(*) as 人数 from Employees,Departments where Employees.Department=Departments.部门号 group by 部门名称【思考与练习】bselect Education ,COUNT(*) as 人数 from Employees group by Education-3.5.select 部门名称,COUNT(*)as 数量 from Departments,Employees where Employees.Department=Departments.部门号 group by 部门名称 having COUNT(*)=2【思考与练习】aselect WorkYear as 工作年限,COUNT(*) as 员工人数 from Employees group by WorkYear-3.5.select Employees.*,收入 from Employees,Salary where Employees.EmployeeID=Sa